postgres-patterns

Padrões PostgreSQL

Referência rápida para as melhores práticas do PostgreSQL. Para orientação detalhada, use o agente database-reviewer.

Quando Ativar

• Escrevendo consultas SQL ou migrações
• Projetando esquemas de banco de dados
• Diagnosticando consultas lentas
• Implementando Segurança em Nível de Linha (RLS)
• Configurando pool de conexões

Referência Rápida

Folha de Dicas de Índices

Padrão de Consulta	Tipo de Índice	Exemplo
WHERE col = valor	B-tree (padrão)	CREATE INDEX idx ON t (col)
WHERE col > valor	B-tree	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	Composto	CREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}'	GIN	CREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ consulta	GIN	CREATE INDEX idx ON t USING gin (col)
Intervalos de séries temporais	BRIN	CREATE INDEX idx ON t USING brin (col)

Referência Rápida de Tipos de Dados

Caso de Uso	Tipo Correto	Evite
IDs	bigint	int, UUID aleatório
Strings	text	varchar(255)
Carimbos de data/hora	timestamptz	timestamp
Moeda	numeric(10,2)	float
Flags	boolean	varchar, int

Padrões Comuns

Ordem do Índice Composto:

-- Colunas de igualdade primeiro, depois colunas de intervalo
CREATE INDEX idx ON pedidos (status, criado_em);
-- Funciona para: WHERE status = 'pendente' AND criado_em > '2024-01-01'

Índice Coberto:

CREATE INDEX idx ON usuarios (email) INCLUDE (nome, criado_em);
-- Evita consulta à tabela para SELECT email, nome, criado_em

Índice Parcial:

CREATE INDEX idx ON usuarios (email) WHERE deleted_at IS NULL;
-- Índice menor, inclui apenas usuários ativos

Política RLS (Otimizada):

CREATE POLICY politica ON pedidos
  USING ((SELECT auth.uid()) = user_id);  -- Encapsule em SELECT!

UPSERT:

INSERT INTO configuracoes (user_id, chave, valor)
VALUES (123, 'tema', 'escuro')
ON CONFLICT (user_id, chave)
DO UPDATE SET valor = EXCLUDED.valor;

Paginação por Cursor:

SELECT * FROM produtos WHERE id > $ultimo_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET que é O(n)

Processamento de Filas:

UPDATE tarefas SET status = 'processando'
WHERE id = (
  SELECT id FROM tarefas WHERE status = 'pendente'
  ORDER BY criado_em LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Detecção de Antipadrões

-- Encontrar chaves estrangeiras não indexadas
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Encontrar consultas lentas
SELECT query, mean_exec_time, chamadas
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Verificar inchaço da tabela
SELECT relname, n_dead_tup, ultima_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Modelo de Configuração

-- Limites de conexão (ajuste conforme RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- Timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- Monitoramento
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Padrões de segurança
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

Relacionados

• Agente: database-reviewer - Fluxo completo de revisão de banco de dados
• Skill: clickhouse-io - Padrões de análise do ClickHouse
• Skill: backend-patterns - Padrões de API e backend

---

Baseado em Agent Skills do Supabase (créditos: equipe Supabase) (Licença MIT)