Padrões úteis ao usar PostgreSQL

• Um texto que reúne padrões práticos para usar o Postgres de forma mais produtiva e segura
• Cada padrão é pequeno, mas, acumulado, faz uma grande diferença

Uso de chave primária UUID

• UUID é aleatório, então tem desvantagens em termos de ordenação e desempenho de índices
• Ocupa mais espaço do que um ID numérico
• Mas tem as seguintes vantagens
  • É possível gerar UUID sem se conectar ao DB
  • Pode ser exposto externamente com segurança
• É possível gerar UUID automaticamente como chave primária usando gen_random_uuid()

Sempre adicionar campos created_at e updated_at

• Ao depurar, é muito útil saber quando um registro foi criado e alterado
• updated_at pode ser configurado para ser atualizado automaticamente por meio de um trigger
• A função é criada uma única vez, e o trigger deve ser aplicado a cada tabela

Definir on update/delete restrict em chaves estrangeiras

• Ao configurar restrições de chave estrangeira, é importante sempre usar on update restrict on delete restrict
• Isso evita que exclusões em cascata aconteçam por engano ao apagar dados
• Armazenamento é barato, mas recuperar dados é muito difícil, então vale a pena agir de forma conservadora

Recomenda-se usar schemas

• O schema padrão é public, mas, quando a aplicação cresce, é melhor separá-la em schemas próprios
• Schemas funcionam como namespaces, e joins entre schemas diferentes também são possíveis
• Quanto maior o número de tabelas, mais vantajoso é usar schemas para legibilidade e manutenção

Usar o padrão de tabela enum

• Em vez do tipo enum do PostgreSQL ou de check constraint, usar tabelas enum é mais flexível
• Ao gerenciar os valores enum em uma tabela separada, fica fácil adicionar metadados ou expandir os valores enum
• As restrições são mantidas referenciando os valores da tabela enum por chave estrangeira

Definir nomes de tabelas no singular

• É desejável definir nomes de tabelas no singular, e não no plural
• Ao escrever consultas, o singular é mais claro, enquanto o plural pode causar confusão semântica ou de posse

Nomear tabelas de junção de forma mecânica

• Para relações muitos-para-muitos, é seguro e claro nomear a tabela de junção concatenando os nomes das duas tabelas
• Exemplo: person_pet
• Adicione um índice único para a combinação a fim de evitar duplicações

Usar soft delete em vez de exclusão

• Em vez de apagar os dados de fato, é melhor usar um campo timestamp como revoked_at para indicar o momento da exclusão
• Assim, é possível rastrear não apenas se foi excluído, mas também quando isso aconteceu
• Um timestamp fornece mais informação do que um valor booleano

Representar status com uma tabela de log

• Em vez de representar o status com uma única coluna, armazene o histórico de mudanças de status em uma tabela separada
• O momento em que o status ocorreu é explicitado pela coluna valid_at
• Para consultar rapidamente o status mais recente, configure uma flag latest, além de índice único + trigger
• Isso é vantajoso em processamento de eventos assíncronos ou em situações em que a ordem pode se misturar

Adicionar system_id a linhas especiais

• Além de tabelas enum, às vezes também são necessárias determinadas "linhas de sistema"
• Adicione um campo de texto system_id que aceite null e configure um índice único
• Com system_id, é possível localizar com clareza uma linha específica

Usar views com moderação

• Views são úteis para abstrair consultas complexas, mas são difíceis de manter
  • Ao remover colunas, é necessário recriar a view
  • Criar views sobre views traz problemas de desempenho e legibilidade
• Use apenas com cuidado e na medida do necessário

Aproveitar bastante consultas JSON

• O Postgres é muito poderoso não só para armazenar JSON, mas também para consultas que retornam JSON
• É possível retornar relações aninhadas em formato JSON com uma única consulta
• Dá para buscar de uma vez todos os dados necessários sem o problema de N+1
• Desvantagens: perda de informação de tipos, necessidade de carregar todos os dados na memória de uma só vez
• As vantagens em desempenho ou estrutura são maiores

[jhj0517]

> Dar nomes às tabelas de junção de forma mecânica

Acho legal só o fato de existir uma regra dessas na hora de dar nomes~

[halfenif]

Se considerar o UUID7, não seria possível ordenar cronologicamente?

[winterjung]

Vale a pena conferir também o texto sobre usar UUID como chave primária no PostgreSQL.

[t7vonn]

O método de inserir um timestamp ao fazer soft delete é realmente bom. Se você usar UUID como chave primária, não dá para ordenar por tempo, então talvez também seja uma boa usar snowflake id ou ulid. Nesse caso, porém, cada servidor precisaria manter um sequence number.