postgres-patterns

Padrões PostgreSQL

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

Quando Ativar

• Escrevendo consultas SQL ou migrações
• Projetando schemas de banco de dados
• Diagnosticando consultas lentas
• Implementando Row Level Security
• Configurando connection pooling

Referência Rápida

Folha de Dicas de Índices

Padrão de Consulta	Tipo de Índice	Exemplo
WHERE col = value	B-tree (padrão)	CREATE INDEX idx ON t (col)
WHERE col > value	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 @@ query	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)
Timestamps	timestamptz	timestamp
Dinheiro	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 orders (status, created_at);
-- Funciona para: WHERE status = 'pending' AND created_at > '2024-01-01'

Índice de Cobertura:

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Evita consulta à tabela para SELECT email, name, created_at

Índice Parcial:

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

Política RLS (Otimizada):

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- Encapsular em SELECT!

UPSERT:

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

Paginação por Cursor:

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

Processamento de Fila:

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Detecção de Anti-Padrõ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, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Verificar bloat de tabela
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Template 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
• Habilidade: clickhouse-io - Padrões de análise ClickHouse
• Habilidade: backend-patterns - Padrões de API e backend

---

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