Design de Tabelas PostgreSQL

Use esta skill quando

• Projetar um schema para PostgreSQL
• Selecionar tipos de dados e constraints
• Planejar indexes, partições ou políticas RLS
• Revisar tabelas para escala e manutenibilidade

Não use esta skill quando

• Você está direcionando um banco de dados não-PostgreSQL
• Você precisa apenas de ajuste de query sem mudanças de schema
• Você requer um guia de modelagem agnóstico a BD

Instruções

1. Capture entidades, padrões de acesso e metas de escala (linhas, QPS, retenção).
2. Escolha tipos de dados e constraints que reforçam invariantes.
3. Adicione indexes para caminhos reais de query e valide com EXPLAIN.
4. Planeje particionamento ou RLS onde exigido por escala ou controle de acesso.
5. Revise o impacto de migração e aplique mudanças com segurança.

Segurança

• Evite DDL destrutivo em produção sem backups e um plano de rollback.
• Use migrations e validação em staging antes de aplicar mudanças de schema.

Regras Principais

• Defina uma PRIMARY KEY para tabelas de referência (usuários, pedidos, etc.). Nem sempre necessário para dados de série temporal/eventos/logs. Quando usado, prefira BIGINT GENERATED ALWAYS AS IDENTITY; use UUID apenas quando unicidade global/opacidade é necessária.
• Normalize primeiro (até 3NF) para eliminar redundância de dados e anomalias de atualização; denormalize apenas para leituras de alto ROI medidas onde desempenho de join é comprovadamente problemático. Denormalização prematura cria carga de manutenção.
• Adicione NOT NULL em todo lugar semanticamente necessário; use DEFAULTs para valores comuns.
• Crie indexes 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 tempo de evento; NUMERIC para moeda; TEXT para strings; BIGINT para valores inteiros, DOUBLE PRECISION para floats (ou NUMERIC para aritmética decimal exata).

"Gotchas" do PostgreSQL

• Identificadores: sem aspas → minúsculas. Evite nomes aspados/com maiúsculas mistas. 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 um NULL.
• Indexes em FK: PostgreSQL não faz auto-index em colunas FK. Adicione-os.
• Sem coercões silenciosas: extrapolação de comprimento/precisão gera erro (sem truncamento). Exemplo: inserir 999 em NUMERIC(2,0) falha com erro, diferente de alguns bancos que silenciosamente truncam ou arredondam.
• Sequences/identity têm lacunas (normal; não "corrija"). Rollbacks, crashes e transações concorrentes criam lacunas em sequências de ID (1, 2, 5, 6...). Este é comportamento esperado—não tente tornar IDs consecutivos.
• Armazenamento em heap: sem PK clusterizado por padrão (diferente de SQL Server/MySQL InnoDB); CLUSTER é reorganização única, não mantida em inserts subsequentes. Ordem de linha em disco é ordem de inserção a menos que explicitamente clusterizado.
• MVCC: updates/deletes deixam tuplas mortas; vacuum as manipula—projete para evitar churn de linhas largas em hot spots.

Tipos de Dados

• IDs: BIGINT GENERATED ALWAYS AS IDENTITY preferido (GENERATED BY DEFAULT também aceitável); UUID quando mesclando/federando/usado em sistema distribuído ou para IDs opacos. Gere com uuidv7() (preferido se usar PG18+) ou gen_random_uuid() (se usar versão mais antiga de PG).
• Inteiros: prefira BIGINT a menos que espaço em disco seja crítico; INTEGER para ranges menores; evite SMALLINT a menos que restringido.
• Floats: prefira DOUBLE PRECISION sobre REAL a menos que espaço em disco seja crítico. Use NUMERIC para aritmética decimal exata.
• Strings: prefira TEXT; se limites de comprimento forem necessários, use CHECK (LENGTH(col) <= n) em vez de VARCHAR(n); evite CHAR(n). Use BYTEA para dados binários. Strings grandes/binários (>2KB limiar padrão) automaticamente armazenados em TOAST com compressão. Armazenamento TOAST: PLAIN (sem TOAST), EXTENDED (comprime + fora-de-linha), EXTERNAL (fora-de-linha, sem compressão), MAIN (comprime, mantém em linha se possível). Padrão EXTENDED geralmente ótimo. Controle com ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy e ALTER TABLE tbl SET (toast_tuple_target = 4096) para limiar. Case-insensitive: para tratamento de locale/acento use collações não-determinísticas; para ASCII simples use expression indexes em LOWER(col) (preferido a menos que coluna precise PK/FK/UNIQUE case-insensitive) ou CITEXT.
• Moeda: NUMERIC(p,s) (nunca float).
• Tempo: TIMESTAMPTZ para timestamps; DATE para apenas data; INTERVAL para durações. Evite TIMESTAMP (sem timezone). Use now() para hora de início de transação, clock_timestamp() para hora de parede atual.
• Booleanos: BOOLEAN com constraint NOT NULL a menos que valores tri-estado 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 lógica de negócio e em evolução (ex: statuses de pedido) → use TEXT (ou INT) + CHECK ou tabela de lookup.
• Arrays: TEXT[], INTEGER[], etc. Use para listas ordenadas onde você consulta elementos. Index com GIN para containment (@>, <@) e overlap (&&) queries. Acesso: arr[1] (1-indexado), arr[1:3] (slicing). Bom para tags, categorias; evite para relações—use tabelas de junção. Sintaxe literal: '{val1,val2}' ou ARRAY[val1,val2].
• Tipos de range: daterange, numrange, tstzrange para intervalos. Suportam overlap (&&), containment (@>), operadores. Index com GiST. Bom para agendamento, versionamento, ranges numéricos. Escolha um esquema de bounds e use consistentemente; prefira [) (inclusivo/exclusivo) por padrão.
• Tipos de rede: INET para endereços IP, CIDR para ranges de rede, MACADDR para endereços MAC. Suportam operadores de rede (<<, >>, &&).
• Tipos geométricos: POINT, LINE, POLYGON, CIRCLE para dados espaciais 2D. Index com GiST. Considere PostGIS para recursos espaciais avançados.
• Text search: TSVECTOR para documentos de busca full-text, TSQUERY para queries de busca. Index tsvector com GIN. Sempre especifique idioma: to_tsvector('english', col) e to_tsquery('english', 'query'). Nunca use versões com um único argumento. Isto se aplica tanto a expressões de index quanto queries.
• Tipos de domínio: CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') para tipos customizados reutilizáveis com validação. Reforça constraints entre tabelas.
• Tipos compostos: CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) para dados estruturados dentro de colunas. Acesso com sintaxe (col).field.
• JSONB: preferido sobre JSON; index com GIN. Use apenas para attrs opcionais/semi-estruturados. APENAS use JSON se a ordenação original do conteúdo DEVE ser preservada.
• Tipos de vetor: tipo vector de pgvector para busca de similaridade de vetor para embeddings.

Não use os seguintes tipos de dados

• NÃO use timestamp (sem time zone); USE timestamptz em vez disso.
• NÃO use char(n) ou varchar(n); USE text em vez disso.
• NÃO use tipo money; USE numeric em vez disso.
• NÃO use tipo timetz; USE timestamptz em vez disso.
• NÃO use timestamptz(0) ou qualquer outra especificação de precisão; USE timestamptz em vez disso.
• NÃO use tipo serial; USE generated always as identity em vez disso.

Tipos de Tabela

• Regular: padrão; totalmente durável, logged.
• TEMPORARY: escopo de sessão, auto-dropped, não logged. Mais rápido para work scratch.
• UNLOGGED: persistente mas não crash-safe. Escritas mais rápidas; bom para caches/staging.

Row-Level Security

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 de linha.

Constraints

• PK: UNIQUE implícito + NOT NULL; cria um index B-tree.
• FK: especifique ação ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Adicione index explícito na coluna referenciadora—acelera joins e previne problemas de locking em deletes/updates do pai. Use DEFERRABLE INITIALLY DEFERRED para dependências FK circulares verificadas no final da transação.
• UNIQUE: cria um index 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 de linha; valores NULL passam no check (lógica tri-valorada). Exemplo: CHECK (price > 0) permite preços NULL. Combine com NOT NULL para reforçar: price NUMERIC NOT NULL CHECK (price > 0).
• EXCLUDE: previne valores sobrepostos usando operadores. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) previne double-booking de salas. Requer tipo de index apropriado (geralmente GiST).

Indexação

• B-tree: padrão para queries de igualdade/range (=, <, >, BETWEEN, ORDER BY)
• Compostos: ordem importa—index é usado se igualdade no prefixo esquerdo (WHERE a = ? AND b > ? usa index em (a,b), mas WHERE b = ? não). Coloque colunas mais seletivas/frequentemente filtradas primeiro.
• Covering: CREATE INDEX ON tbl (id) INCLUDE (name, email) - inclui colunas não-chave para index-only scans sem visitar tabela.
• Parcial: para hot subsets (WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Qualquer query com status = 'active' pode usar este index.
• Expression: para chaves de busca computadas (CREATE INDEX ON tbl (LOWER(email))). Expression deve corresponder exatamente em cláusula WHERE: WHERE LOWER(email) = 'user@example.com'.
• GIN: containment/existência JSONB, arrays (@>, ?), busca full-text (@@)
• GiST: ranges, geometria, constraints de exclusão
• BRIN: dados muito grandes, naturalmente ordenados (série temporal)—overhead mínimo de armazenamento. Efetivo quando ordem de linha em disco correlaciona com coluna indexada (ordem de inserção ou após CLUSTER).

Particionamento

• Use para tabelas muito grandes (>100M linhas) onde queries consistentemente filtram na chave de partição (geralmente tempo/data).
• Uso alternativo: use para tabelas onde tarefas de manutenção de dados ditam ex: dados podados ou substituídos em bulk periodicamente
• RANGE: comum para série temporal (PARTITION BY RANGE (created_at)). Crie 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 nenhuma chave natural (PARTITION BY HASH (user_id)). Cria N partições com módulo.
• Constraint exclusion: requer constraints CHECK em partições para planner de query podar. Auto-criado para particionamento declarativo (PG10+).
• Prefira particionamento declarativo ou hypertables. NÃO use herança de tabela.
• Limitações: sem constraints UNIQUE globais—inclua chave de partição em PK/UNIQUE. FKs de tabelas particionadas não suportadas; use triggers.

Considerações Especiais

Tabelas Update-Heavy

• Separe colunas hot/cold—coloque colunas frequentemente atualizadas em tabela separada para minimizar bloat.
• Use fillfactor=90 para deixar espaço para HOT updates que evitam manutenção de index.
• Evite atualizar colunas indexadas—previne HOT updates benéficos.
• Particione por padrões de atualização—separe linhas frequentemente atualizadas em partição diferente de dados estáveis.

Workloads Insert-Heavy

• Minimize indexes—crie apenas o que você consulta; cada index desacelera inserts.
• Use COPY ou INSERT multi-linha em vez de inserts de linha única.
• Tabelas UNLOGGED para dados de staging reconstruíveis—escritas muito mais rápidas.
• Adie criação de index para bulk loads—drop index, carregue dados, recrie indexes.
• Particione por tempo/hash para distribuir carga. TimescaleDB automatiza particionamento e compressão de dados insert-heavy.
• Use chave natural para primary key tal como (timestamp, device_id) se reforçar unicidade global é importante muitas tabelas insert-heavy não precisam de primary key.
• Se você precisa de chave substituta, Prefira BIGINT GENERATED ALWAYS AS IDENTITY sobre UUID.

Design Amigável a Upsert

• Requer index UNIQUE nas colunas de conflito target—ON CONFLICT (col1, col2) precisa de index unique exato (indexes parciais não funcionam).
• Use EXCLUDED.column para referenciar valores que seriam inseridos; atualize apenas colunas que realmente mudaram para reduzir overhead de escrita.
• DO NOTHING mais rápido que DO UPDATE quando nenhuma atualização real é necessária.

Evolução Segura de Schema

• DDL Transacional: a maioria das operações DDL podem rodar em transações e ser rolled back—BEGIN; ALTER TABLE...; ROLLBACK; para teste seguro.
• Criação de index concorrente: CREATE INDEX CONCURRENTLY evita bloquear escritas mas não pode rodar em transações.
• Defaults voláteis causam rewrites: adicionar colunas NOT NULL com defaults voláteis (ex: now(), gen_random_uuid()) reescreve tabela inteira. Defaults não-voláteis são rápidos.
• Drop constraints antes de colunas: ALTER TABLE DROP CONSTRAINT então DROP COLUMN para evitar problemas de dependência.
• Mudanças de assinatura de função: CREATE OR REPLACE com argumentos diferentes cria overloads, não replacements. DROP versão antiga se nenhum overload desejado.

Generated Columns

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

Extensions

• pgcrypto: crypt() para hashing de senha.
• uuid-ossp: funções UUID alternativas; prefira pgcrypto para novos projetos.
• pg_trgm: busca de texto fuzzy com operador %, função similarity(). Index com GIN para aceleração de LIKE '%pattern%'.
• citext: tipo de texto case-insensitive. Prefira expression indexes em LOWER(col) a menos que você precise de constraints case-insensitive.
• btree_gin/btree_gist: habilite indexes de tipos misto (ex: index GIN em colunas JSONB e texto).
• hstore: pares chave-valor; geralmente supersedido por JSONB mas útil para mapeamentos simples de string.
• timescaledb: essencial para série temporal—particionamento automatizado, retenção, compressão, aggregates contínuos.
• postgis: suporte geoespacial compreensivo além de tipos geométricos básicos—essencial para aplicações baseadas em localização.
• pgvector: busca de similaridade de vetor para embeddings.
• pgaudit: audit logging para toda atividade de banco de dados.

Orientação JSONB

• Prefira JSONB com index GIN.
• Padrão: CREATE INDEX ON tbl USING GIN (jsonb_col); → acelera:
  • Containment jsonb_col @> '{"k":"v"}'
  • Existência de chave jsonb_col ? 'k', qualquer/todas as chaves ?\|, ?&
  • Path containment em docs aninhados
  • Disjunção jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
• Workloads pesados @>: considere opclass jsonb_path_ops para indexes menores/mais rápidos apenas containment:
  • CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
  • Trade-off: perde suporte para queries de existência de chave (?, ?|, ?&)—apenas suporta containment (@>)
• Igualdade/range em campo scalar específico: extraia e index com B-tree (coluna gerada ou expression):
  • 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) sobre WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 sem index.
• Arrays dentro de JSONB: use GIN + @> para containment (ex: tags). Considere jsonb_path_ops se apenas fazer containment.
• Mantenha relações core em tabelas; use JSONB para attrs opcionais/variáveis.
• Use constraints para limitar valores JSONB permitidos em coluna ex: config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')

Exemplos

Usuários

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

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);