data-supabase-patterns

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.