postgresql-table-design

name	postgresql-table-design
description	Orienta o design e modelagem de schemas para PostgreSQL. Use ao criar ou alterar tabelas, definir tipos de dados, criar índices ou configurar restrições (constraints) no banco de dados.

Design de Tabelas no PostgreSQL (PostgreSQL Table Design)

Regras Principais (Core Rules)

• Defina uma PRIMARY KEY para tabelas de referência (usuários, pedidos, etc.). Nem sempre é necessário para dados de log/eventos/séries temporais. Quando usado, prefira BIGINT GENERATED ALWAYS AS IDENTITY; use UUID apenas quando opacidade ou unicidade global for necessária.
• Normalize primeiro (para 3NF) para eliminar redundância de dados e anomalias de atualização; desnormalize apenas para leituras medidas de alto ROI onde a performance de join foi provada como problemática. A desnormalização prematura cria carga de manutenção.
• Adicione NOT NULL em todos os lugares em que for semanticamente exigido; use DEFAULT para valores comuns.
• Crie índices para caminhos de acesso que você realmente consulta: PK/unique (automático), colunas FK (manual!), filtros/ordenações frequentes e chaves de join.
• Prefira TIMESTAMPTZ para horário de eventos; NUMERIC para dinheiro; TEXT para strings; BIGINT para valores inteiros, DOUBLE PRECISION para floats (ou NUMERIC para aritmética decimal exata).

"Pegadinhas" do PostgreSQL (PostgreSQL “Gotchas”)

• Identificadores (Identifiers): sem aspas → convertidos para minúsculas. Evite nomes com aspas ou com letras maiúsculas/minúsculas misturadas. Convenção: use snake_case para nomes de tabelas/colunas.
• Unique + NULLs: UNIQUE permite múltiplos NULLs. Use UNIQUE (...) NULLS NOT DISTINCT (PG15+) para restringir a apenas um NULL.
• Índices de FK: PostgreSQL não indexa automaticamente colunas FK. Adicione-os.
• Sem coerções silenciosas: estouros (overflows) de tamanho/precisão causam erro (sem truncamento). Exemplo: inserir 999 em NUMERIC(2,0) falha com erro, diferente de alguns bancos de dados que silenciosamente truncam ou arredondam.
• Sequências/identity possuem lacunas (normal; não tente "consertar"). Rollbacks, crashes e transações concorrentes criam lacunas nas sequências de IDs (1, 2, 5, 6...). Este é o comportamento esperado — não tente tornar os IDs consecutivos.
• Armazenamento em Heap: sem PK clusterizada por padrão (diferente de SQL Server/MySQL InnoDB); CLUSTER é uma reorganização pontual (one-off), não mantida nas inserções subsequentes. A ordem das linhas no disco é a ordem de inserção a não ser que seja explicitamente clusterizada.
• MVCC: updates/deletes deixam tuplas mortas (dead tuples); o vacuum lida com elas — projete para evitar alto churn (alta rotatividade) em linhas muito largas.

Tipos de Dados (Data Types)

• IDs: BIGINT GENERATED ALWAYS AS IDENTITY preferencial (GENERATED BY DEFAULT também serve); UUID quando fundindo/federando/usando num sistema distribuído ou para IDs opacos. Gere com uuidv7() (preferido se usando PG18+) ou gen_random_uuid() (se usando uma versão mais antiga do PG).
• Inteiros: prefira BIGINT a menos que espaço de armazenamento seja crítico; INTEGER para intervalos menores; evite SMALLINT a menos que seja restrito.
• Floats: prefira DOUBLE PRECISION sobre REAL a menos que espaço de armazenamento seja crítico. Use NUMERIC para aritmética decimal exata.
• Strings: prefira TEXT; se limites de tamanho forem necessários, use CHECK (LENGTH(col) <= n) em vez de VARCHAR(n); evite CHAR(n). Use BYTEA para dados binários. Strings/binários grandes (limite padrão >2KB) são armazenados automaticamente no TOAST com compressão. Estratégia de armazenamento TOAST: PLAIN (sem TOAST), EXTENDED (comprime + out-of-line), EXTERNAL (out-of-line, sem compressão), MAIN (comprime, mantém in-line se possível). O padrão EXTENDED geralmente é o ideal. Controle com ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy e ALTER TABLE tbl SET (toast_tuple_target = 4096) para o limiar. Case-insensitive (insensível a maiúsculas/minúsculas): para lidar com locales/acentos use collations não-determinísticas; para ASCII puro, use índices de expressão em LOWER(col) (preferido, a não ser que a coluna exija PK/FK/UNIQUE case-insensitive) ou CITEXT.
• Dinheiro (Money): NUMERIC(p,s) (nunca float).
• Tempo: TIMESTAMPTZ para timestamps; DATE para apenas data; INTERVAL para durações. Evite TIMESTAMP (sem fuso horário). Use now() para o horário de início da transação, clock_timestamp() para o tempo atual do relógio (wall-clock time).
• Booleanos: BOOLEAN com constraint NOT NULL a menos que valores tri-state (três estados) sejam necessários.
• Enums: CREATE TYPE ... AS ENUM para conjuntos pequenos e estáveis (ex: estados dos EUA, dias da semana). Para valores orientados por regras de negócio e evolutivos (ex: status de pedidos) → use TEXT (ou INT) + CHECK ou tabela de lookup (consulta).
• Arrays: TEXT[], INTEGER[], etc. Use para listas ordenadas onde você faz consulta nos elementos. Indexe com GIN para consultas de contenção (containment) (@>, <@) e sobreposição (overlap) (&&). Acesso: arr[1] (baseado em 1), arr[1:3] (fatiamento). Bom para tags, categorias; evite para relações — use tabelas de junção em vez disso. Sintaxe literal: '{val1,val2}' ou ARRAY[val1,val2].
• Tipos de intervalo (Range types): daterange, numrange, tstzrange para intervalos. Suportam overlap (&&), contenção (@>), operadores. Indexe com GiST. Bom para agendamentos, versionamento, intervalos numéricos. Escolha um esquema de limites (bounds) e use de forma consistente; prefira [) (inclusivo/exclusivo) por padrão.
• Tipos de rede: INET para endereços IP, CIDR para intervalos de rede, MACADDR para endereços MAC. Suportam operadores de rede (<<, >>, &&).
• Tipos geométricos: POINT, LINE, POLYGON, CIRCLE para dados espaciais em 2D. Indexe com GiST. Considere PostGIS para recursos espaciais avançados.
• Busca de texto (Text search): TSVECTOR para documentos de busca de texto completo (full-text search), TSQUERY para consultas (queries). Indexe tsvector com GIN. Sempre especifique o idioma: to_tsvector('english', col) e to_tsquery('english', 'query'). Nunca use versões de argumento único. Isso se aplica tanto a expressões de índice quanto a consultas.
• Tipos de Domínio (Domain types): CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') para tipos customizados reutilizáveis com validação. Impõe restrições entre tabelas.
• Tipos Compostos (Composite types): CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) para dados estruturados dentro de colunas. Acesse com a sintaxe (col).field.
• JSONB: preferido em relação a JSON; indexe com GIN. Use apenas para atributos opcionais/semiestruturados. SOMENTE use JSON se a ordenação original dos conteúdos DEVE ser preservada.
• Tipos de vetor: tipo vector via pgvector para pesquisa de similaridade de vetores para embeddings.

Não use os seguintes tipos de dados

• NÃO use timestamp (sem fuso horário); FAÇA o uso de timestamptz.
• NÃO use char(n) ou varchar(n); FAÇA o uso de text.
• NÃO use o tipo money; FAÇA o uso de numeric.
• NÃO use o tipo timetz; FAÇA o uso de timestamptz.
• NÃO use timestamptz(0) ou qualquer outra especificação de precisão; FAÇA o uso de timestamptz.
• NÃO use o tipo serial; FAÇA o uso de generated always as identity.

Tipos de Tabela

• Regular: padrão; totalmente durável, registra logs.
• TEMPORARY: escopo de sessão, auto-deletada (auto-dropped), não registra logs. Mais rápida para trabalhos de rascunho (scratch).
• UNLOGGED: persistente mas não crash-safe (não segura contra travamentos). Escritas mais rápidas; boa para caches/staging.

Segurança a Nível de Linha (Row-Level Security - RLS)

Habilite com ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Crie políticas: CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()). Controle de acesso baseado em usuário embutido no nível da linha.

Restrições (Constraints)

• PK: implícito UNIQUE + NOT NULL; cria um índice B-tree.
• FK: especifique ação ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Adicione índice explícito na coluna de referência — acelera joins e previne problemas de lock em deleções/atualizações no pai. Use DEFERRABLE INITIALLY DEFERRED para dependências circulares de FK checadas ao final da transação.
• UNIQUE: cria um índice B-tree; permite múltiplos NULLs a menos que NULLS NOT DISTINCT (PG15+). Comportamento padrão: (1, NULL) e (1, NULL) são permitidos. Com NULLS NOT DISTINCT: apenas um (1, NULL) é permitido. Prefira NULLS NOT DISTINCT a menos que você especificamente precise de NULLs duplicados.
• CHECK: constraints locais da linha; valores NULL passam na checagem (lógica de três valores). Exemplo: CHECK (price > 0) permite preços NULL. Combine com NOT NULL para impor: price NUMERIC NOT NULL CHECK (price > 0).
• EXCLUDE: previne valores sobrepostos usando operadores. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) previne reservas duplas para o mesmo quarto. Requer tipo de índice apropriado (frequentemente GiST).

Indexação (Indexing)

• B-tree: padrão para igualdade/consultas de intervalo (=, <, >, BETWEEN, ORDER BY).
• Composto (Composite): a ordem importa — o índice é usado se a igualdade estiver no prefixo mais à esquerda (WHERE a = ? AND b > ? usa índice em (a,b), mas WHERE b = ? não). Coloque colunas mais seletivas/frequentemente filtradas primeiro.
• De cobertura (Covering): CREATE INDEX ON tbl (id) INCLUDE (name, email) - inclui colunas não-chave para index-only scans (scans só em índice) sem visitar a tabela.
• Parcial: para subconjuntos quentes (WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Qualquer query com status = 'active' pode usar este índice.
• Expressão (Expression): para chaves de busca computadas (CREATE INDEX ON tbl (LOWER(email))). A expressão deve corresponder exatamente na cláusula WHERE: WHERE LOWER(email) = '[e-mail]'.
• GIN: contenção/existência de JSONB, arrays (@>, ?), busca de texto completo (full-text search) (@@).
• GiST: ranges (intervalos), geometria, exclusion constraints (restrições de exclusão).
• BRIN: dados muito grandes, ordenados naturalmente (séries temporais) — sobrecarga de armazenamento mínima. Efetivo quando a ordem da linha no disco se correlaciona com a coluna indexada (ordem de inserção ou após CLUSTER).

Particionamento (Partitioning)

• Use para tabelas muito grandes (>100M linhas) onde as queries filtram consistentemente pela chave de partição (geralmente tempo/data).
• Uso alternativo: use para tabelas onde tarefas de manutenção de dados ditam que os dados, por ex., sejam podados (pruned) ou substituídos em massa periodicamente.
• RANGE: comum para time-series (PARTITION BY RANGE (created_at)). Criar partições: CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). TimescaleDB automatiza particionamento baseado em tempo ou ID com políticas de retenção e compressão.
• LIST: para valores discretos (PARTITION BY LIST (region)). Exemplo: FOR VALUES IN ('us-east', 'us-west').
• HASH: para distribuição uniforme quando não há chave natural (PARTITION BY HASH (user_id)). Cria N partições com módulo.
• Exclusão de restrição (Constraint exclusion): requer constraints CHECK nas partições para que o planejador de consultas (query planner) possa fazer pruning. Auto-criado para particionamento declarativo (PG10+).
• Prefira particionamento declarativo ou hypertables. NÃO use herança de tabela (table inheritance).
• Limitações: sem restrições UNIQUE globais — inclua a chave de partição no PK/UNIQUE. Foreign keys com tabelas particionadas são suportadas em versões modernas do PostgreSQL; verifique o comportamento apenas para versões legadas durante migrações.

Considerações Especiais

Tabelas com Muitos Updates (Update-Heavy Tables)

• Separe colunas quentes/frias (hot/cold) — coloque colunas atualizadas frequentemente em uma tabela separada para minimizar o inchaço (bloat).
• Use fillfactor=90 para deixar espaço para HOT updates (Heap-Only Tuples) que evitam a manutenção de índices.
• Evite atualizar colunas indexadas — previne benefício dos HOT updates.
• Particione por padrões de update — separe linhas frequentemente atualizadas em uma partição diferente dos dados estáveis.

Cargas com Muitas Inserções (Insert-Heavy Workloads)

• Minimize índices — só crie o que você consulta; cada índice atrasa as inserções.
• Use COPY ou INSERT de múltiplas linhas em vez de inserções linha a linha.
• Tabelas UNLOGGED para dados de staging reconstruíveis — escritas muito mais rápidas.
• Adie (Defer) a criação de índices para cargas em massa (bulk loads) —> faça o drop do índice, carregue os dados, recrie os índices.
• Particione por tempo/hash para distribuir a carga. TimescaleDB automatiza o particionamento e compressão de dados com muitas inserções.
• Use uma chave natural para a primary key como por exemplo um (timestamp, device_id) se impor a unicidade global for importante; muitas tabelas com muitas inserções não precisam de uma chave primária de forma alguma.
• Se você precisar de uma surrogate key, Prefira BIGINT GENERATED ALWAYS AS IDENTITY em vez de UUID.

Design Favorável a Upsert (Upsert-Friendly Design)

• Requer um índice UNIQUE nas colunas alvo de conflito — ON CONFLICT (col1, col2) precisa de um índice único com correspondência exata. Índices únicos parciais podem ser inferidos quando o conflict_target inclui um index_predicate correspondente.
• Use EXCLUDED.column para referenciar valores que seriam inseridos; atualize apenas colunas que realmente mudaram para reduzir a sobrecarga (overhead) de escrita.
• DO NOTHING é mais rápido do que DO UPDATE quando nenhuma atualização real é necessária.

Evolução Segura de Schema (Safe Schema Evolution)

• DDL Transacional: a maioria das operações DDL pode rodar em transações e sofrer rollback — BEGIN; ALTER TABLE...; ROLLBACK; para testes seguros.
• Criação de índice concorrente: CREATE INDEX CONCURRENTLY evita o bloqueio de escritas, mas não pode rodar dentro de transações.
• Defaults voláteis causam reescritas: adicionar colunas NOT NULL com defaults voláteis (ex., now(), gen_random_uuid()) reescreve a tabela inteira. Defaults não-voláteis são rápidos.
• Drop constraints antes de columns: ALTER TABLE DROP CONSTRAINT e depois DROP COLUMN para evitar problemas de dependência.
• Mudanças na assinatura da função: CREATE OR REPLACE com argumentos diferentes cria sobrecargas (overloads), não substituições. Faça DROP na versão antiga se nenhuma sobrecarga for desejada.

Colunas Geradas (Generated Columns)

• ... GENERATED ALWAYS AS (<expr>) STORED para campos computados e indexáveis. PG18+ adiciona colunas VIRTUAL (computadas na leitura, não armazenadas).

Extensões (Extensions)

• pgcrypto: crypt() para hashing de senha.
• uuid-ossp: funções UUID alternativas; prefira pgcrypto para novos projetos.
• pg_trgm: busca difusa de texto (fuzzy text search) com o operador %, função similarity(). Indexe com GIN para aceleração do LIKE '%pattern%'.
• citext: tipo de texto insensível a maiúsculas/minúsculas. Prefira índices de expressão em LOWER(col) a não ser que você precise de constraints case-insensitive.
• btree_gin/btree_gist: permite índices de tipo misto (ex: índice GIN em ambas colunas, JSONB e texto).
• hstore: pares chave-valor; majoritariamente superado pelo JSONB, mas útil para mapeamentos de string simples.
• timescaledb: essencial para time-series — particionamento automatizado, retenção, compressão, agregações contínuas.
• postgis: suporte geoespacial abrangente além dos tipos geométricos básicos — essencial para aplicações baseadas em localização.
• pgvector: pesquisa de similaridade de vetor para embeddings.
• pgaudit: registro de auditoria (audit logging) para toda a atividade do banco de dados.

Orientação sobre JSONB

• Prefira JSONB com índice GIN.
• Padrão: CREATE INDEX ON tbl USING GIN (jsonb_col); → acelera:
  • Contenção (Containment) jsonb_col @> '{"k":"v"}'
  • Existência de chave jsonb_col ? 'k', qualquer/todas as chaves ?|, ?&
  • Contenção de caminho (Path containment) em documentos aninhados
  • Disjunção jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
• Workloads com muito @>: considere a opclass jsonb_path_ops para índices menores/mais rápidos apenas de contenção:
  • CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
  • Trade-off: perde suporte para consultas de existência de chave (?, ?|, ?&) — só suporta contenção (@>)
• Igualdade/range em um campo escalar específico: extraia e indexe com B-tree (coluna gerada ou expressão):
  • ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
  • CREATE INDEX ON tbl (price);
  • Prefira queries como WHERE price BETWEEN 100 AND 500 (usa B-tree) do que WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 sem índice.
• Arrays dentro do JSONB: use GIN + @> para contenção (ex: tags). Considere jsonb_path_ops se for fazer apenas contenção.
• Mantenha relações principais (core) em tabelas; use JSONB para atributos opcionais/variáveis.
• Use constraints para limitar os valores JSONB permitidos em uma coluna. Ex: config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')

Exemplos (Examples)

Usuários (Users)

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);

Pedidos (Orders)

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);

JSONB

CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);