Colunas de identidade, sequences e por que os IDs gerados têm lacunas

O que é

id bigint GENERATED ALWAYS AS IDENTITY (ou BY DEFAULT) declara uma coluna de identidade. A documentação a descreve como apoiada por uma sequence implícita: com ALWAYS, um valor informado pelo usuário só é aceito se o INSERT usar OVERRIDING SYSTEM VALUE; com BY DEFAULT, o valor informado tem precedência. nextval avança a sequence, e a documentação das funções de sequence afirma que um valor obtido por nextval não é recuperado se a transação que o chamou for abortada depois, que um INSERT com ON CONFLICT calcula a linha, incluindo as chamadas a nextval, antes de detectar o conflito, e que, por isso, sequences não conseguem fornecer numeração sem lacunas. A página de CREATE SEQUENCE acrescenta que um valor de CACHE maior que um pré-aloca valores por sessão, de modo que valores não utilizados se perdem quando a sessão termina e os valores entre sessões podem ficar fora de ordem.

Por que importa

Desenvolvedores e auditores percebem as lacunas e suspeitam de linhas perdidas. Código que assume que max(id) + 1 é o próximo valor, ou que count(*) é igual a max(id), está errado. Regras de numeração que exigem números contíguos não podem ser atendidas pela chave primária.

Como aplicar

• Prefira bigint GENERATED ALWAYS AS IDENTITY; uma coluna integer tem como limite 2^31 - 1 (2.147.483.647), e alterar o tipo depois reescreve a tabela.
• Trate os IDs como opacos: nunca tire conclusões das lacunas ou da magnitude dos valores e, se for importante não revelar o volume de linhas, não exponha os IDs externamente; use UUIDs ou tokens aleatórios.
• Quando um número contíguo for necessário (numeração de documentos), atribua-o no momento em que o documento se torna definitivo, a partir de uma linha de contador atualizada na mesma transação (UPDATE counters SET last = last + 1 WHERE name = 'invoice' RETURNING last); a documentação de CREATE SEQUENCE descreve esse contador com bloqueio como bem mais custoso que uma sequence, e esse é o preço da garantia.
• Após cargas em massa com IDs explícitos em uma coluna de identidade BY DEFAULT, redefina a sequence (SELECT setval(pg_get_serial_sequence('t', 'id'), max(id)) FROM t); caso contrário, o próximo valor gerado colidirá.
• Deixe CACHE no valor padrão 1, a menos que a sequence seja um gargalo comprovado por medição.

Armadilhas

Alterações feitas com setval ficam visíveis imediatamente para as outras sessões e não são desfeitas por rollback. Colunas serial (serial, bigserial) são uma sequence mais um valor padrão de coluna, e não uma coluna de identidade, portanto valores explícitos nunca são rejeitados. A documentação de CREATE SEQUENCE afirma que NO CYCLE é o padrão: ao atingir o máximo, nextval retorna um erro em vez de reiniciar a contagem.