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

Capture entidades, padrões de acesso e metas de escala (linhas, QPS, retenção).
Escolha tipos de dados e constraints que reforçam invariantes.
Adicione indexes para caminhos reais de query e valide com EXPLAIN.
Planeje particionamento ou RLS onde exigido por escala ou controle de acesso.
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);

postgresql

SKILL.mdskills/skills/database/postgresql/SKILL.md