Postgresql

---
name: postgresql
description: "Projete um schema específico para PostgreSQL. Abrange práticas recomendadas, tipos de dados, indexação, constraints, padrões de desempenho e recursos avançados"
risk: unknown
source: community
date_added: "2026-02-27"
---

# 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 **DEFAULT**s 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

```sql
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

```sql
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

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