Supabase / Postgres Patterns

Skill de banco de dados destilada do guia oficial supabase-postgres-best-practices (8 categorias priorizadas por impacto). Usada por dev-data-engineer, sites-data (schema, migrations, RLS) e dev-bi (queries analíticas), com dev-qa/sites-qa em review de migrations. Segue o protocolo de segurança da squad: snapshot → dry-run → apply → smoke-test.

1. Query Performance (CRITICAL)

1.1 — Index every column used in WHERE, JOIN, and ORDER BY of hot queries. Missing indexes are the #1 cause of slow queries.

-- ❌ seq scan on 1M rows
SELECT * FROM orders WHERE customer_id = $1;
-- ✅
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

1.2 — Composite indexes: equality columns first, then range/sort.

-- query: WHERE status = 'paid' AND created_at > now() - interval '30 days'
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

1.3 — Partial indexes for skewed data (e.g., only 2% of rows are pending):

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

1.4 — Never wrap an indexed column in a function — it disables the index. Index the expression instead:

-- ❌ WHERE lower(email) = $1  (index on email unused)
CREATE INDEX idx_users_email_lower ON users (lower(email)); -- ✅

1.5 — Covering indexes (INCLUDE) for index-only scans on hot read paths.

1.6 — Keyset pagination, not OFFSET, for deep pages:

-- ❌ OFFSET 100000 scans and discards 100k rows
-- ✅ WHERE (created_at, id) < ($last_created_at, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20

1.7 — SELECT only needed columns — SELECT * breaks index-only scans and inflates payloads (especially over PostgREST).

1.8 — Always EXPLAIN (ANALYZE, BUFFERS) before and after optimizing. Look for Seq Scan on large tables, misestimated rows, and high buffer reads.

2. Connection Management (CRITICAL)

2.1 — Serverless/edge functions MUST use the transaction-mode pooler (Supavisor, port 6543), never direct connections — each lambda invocation would otherwise hold a Postgres backend.

2.2 — Transaction mode does not support session state: no prepared statements at protocol level (disable in the client, e.g. pgbouncer=true / prepare: false), no SET without LOCAL, no advisory locks held across transactions.

2.3 — Long-lived servers: direct connection (5432) with a small app-side pool. Rule of thumb: pool size ≈ cores × 2–4, not hundreds.

2.4 — Set statement timeouts (statement_timeout, idle_in_transaction_session_timeout) so runaway queries and abandoned transactions don't exhaust the pool.

3. Security & RLS (CRITICAL)

3.1 — Enable RLS on every table in exposed schemas (public is exposed via PostgREST):

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

3.2 — Wrap auth functions in a subselect so they're evaluated once per query, not per row — the single biggest RLS performance fix:

-- ❌ auth.uid() re-evaluated for every row
CREATE POLICY p ON documents USING (auth.uid() = user_id);
-- ✅ initPlan: evaluated once
CREATE POLICY p ON documents USING ((SELECT auth.uid()) = user_id);

3.3 — Index the columns referenced by policies (user_id, org_id, team_id). RLS turns every query into a filtered query.

3.4 — Specify the role in policies (TO authenticated) so anon traffic short-circuits without evaluating the expression.

3.5 — Avoid joins inside policies; prefer a SECURITY DEFINER helper function (e.g. private.user_org_ids()) with a stable result, and keep it in a non-exposed schema.

3.6 — Functions: pin search_path (SET search_path = '', use schema-qualified names) and grant EXECUTE deliberately. Never put SECURITY DEFINER functions in exposed schemas without need.

3.7 — Never string-interpolate SQL — parameterized queries only, including inside plpgsql (format() + %I/%L when dynamic SQL is unavoidable).

4. Schema Design (HIGH)

• bigint GENERATED ALWAYS AS IDENTITY (ou UUIDv7) para PKs; evite UUIDv4 como PK clusterizada de tabelas gigantes (index bloat / cache locality).
• text em vez de varchar(n) + CHECK constraint quando precisar de limite — mesmo desempenho, migração mais fácil.
• timestamptz sempre, nunca timestamp.
• FKs sempre indexadas — Postgres não cria índice em FK automaticamente; deletes/updates no pai fazem seq scan no filho sem ele.
• NOT NULL + defaults em tudo que a regra de negócio permitir; constraints são documentação executável.
• Enum de domínio: prefira tabela de lookup ou CHECK a CREATE TYPE enum quando os valores mudam com frequência.

5. Migrations seguras (HIGH)

5.1 — Zero-downtime playbook:

-- Adicionar coluna NOT NULL em tabela grande: 3 passos
ALTER TABLE t ADD COLUMN c text;                      -- 1. nullable, instantâneo
UPDATE t SET c = '...' WHERE c IS NULL;               -- 2. backfill em batches
ALTER TABLE t ALTER COLUMN c SET NOT NULL;            -- 3. depois de validado

5.2 — CREATE INDEX CONCURRENTLY em tabelas em produção (fora de transaction block).

5.3 — Constraints em duas fases: ADD CONSTRAINT ... NOT VALID seguido de VALIDATE CONSTRAINT — valida sem lock longo.

5.4 — Nunca DROP COLUMN/RENAME no mesmo deploy que o código — expand/contract: adicionar novo → migrar código → remover velho.

6. Concurrency & Locking (MEDIUM-HIGH)

• Transações curtas. Nunca segure uma transação aberta durante chamadas de rede/API externa.
• SELECT ... FOR UPDATE SKIP LOCKED para filas e workers concorrentes.
• Ordene updates multi-linha por chave estável para evitar deadlocks entre transações concorrentes.
• lock_timeout curto em DDL (SET LOCAL lock_timeout = '2s') — melhor falhar e repetir do que enfileirar atrás de um lock e travar o sistema.

7. Diagnóstico (quando algo está lento)

1. pg_stat_statements — top queries por total_exec_time e mean_exec_time.
2. EXPLAIN (ANALYZE, BUFFERS) na query suspeita.
3. pg_stat_activity — conexões idle in transaction, queries longas, waits.
4. pg_stat_user_indexes — índices nunca usados (candidatos a drop: cada índice custa em escrita).
5. Em Supabase: Query Performance report + Index Advisor no dashboard; supabase inspect db na CLI.

Checklist de review (migrations/queries)

• Toda tabela nova em schema exposto tem RLS habilitada + policies com (SELECT auth.uid())
• Colunas de policy e FKs indexadas
• Índices criados com CONCURRENTLY em produção
• Sem SELECT * em código de aplicação
• Paginação keyset em listagens profundas
• timestamptz, text, identity PKs
• Plano testado com EXPLAIN (ANALYZE, BUFFERS) antes do merge

Adaptado de supabase/agent-skills → supabase-postgres-best-practices (skills.sh) — 2026-08-26. O SKILL.md fonte é um índice de ~50 regras em arquivos de referência; as regras acima foram destiladas das categorias oficiais (query/conn/security/schema/lock/data/monitor) complementadas com conhecimento próprio dos mesmos tópicos.

Skill relacionada

• Otimização SQL genérica (EXPLAIN profundo, indexing, HA, multi-plataforma) → ../data-sql-optimization/SKILL.md.