Postgres Optimizer

2. Indexing Strategy

• Index-Only Scans: Aim for this. Include the payload columns in the index using INCLUDE.

  CREATE INDEX idx_users_active_email ON users (status) INCLUDE (email) WHERE status = 'active';
  

• Partial Indexes: Index only what you query.
  • Example: CREATE INDEX idx_orders_unshipped ON orders (created_at) WHERE shipped_at IS NULL;

3. JSONB: The Trap

• Rule: Use JSONB only for truly unstructured data or rarely-queried metadata.
• Anti-Pattern: Dumping core business logic into a JSONB column. It kills join performance and type safety.
• Gin Indexes: If querying JSONB, a GIN index is mandatory.

4. Query Analysis

• Mandatory Tool: EXPLAIN (ANALYZE, BUFFERS).
• Red Flags:
  • Seq Scan on large tables (missing index).
  • High Buffers: shared hit (query pulling too much data).
  • N+1 queries (detect and switch to IN clauses or LATERAL joins).

5. Connection Pooling

• Requirement: In serverless/edge environments (like Next.js), you MUST use a transaction pooler (PgBouncer, Supabase Pooler) to avoid exhausting connections.