Database Optimizer

1. Optimized Schema Design

sql
-- Good: Indexed foreign keys, appropriate constraints
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_created_at ON users(created_at DESC);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(500) NOT NULL,
    content TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index foreign key for joins
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Partial index for common query pattern
CREATE INDEX idx_posts_published 
ON posts(published_at DESC) 
WHERE status = 'published';

-- Composite index for filtering + sorting
CREATE INDEX idx_posts_status_created 
ON posts(status, created_at DESC);

1. Query Optimization with EXPLAIN

sql
-- ❌ Bad: N+1 query pattern
SELECT * FROM posts WHERE user_id = 123;
-- Then for each post:
SELECT * FROM comments WHERE post_id = ?;

-- ✅ Good: Single query with JOIN
EXPLAIN ANALYZE
SELECT 
    p.id, p.title, p.content,
    COALESCE(
        json_agg(json_build_object(
            'id', c.id,
            'content', c.content,
            'author', c.author
        ) ORDER BY c.id) FILTER (WHERE c.id IS NOT NULL),
        '[]'::json
    ) AS comments
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.user_id = 123
GROUP BY p.id;

-- Regression cases: no comments => []; two comments => two ordered objects.
-- FILTER removes the synthetic NULL row from LEFT JOIN; COALESCE turns an
-- empty aggregate into the same array shape as a populated one.

-- Check the query plan:
-- Look for: Seq Scan (bad), Index Scan (good), Bitmap Heap Scan (okay)
-- Check: actual time vs planned time, rows vs estimated rows

1. Preventing N+1 Queries

typescript
// ❌ Bad: N+1 in application code
const users = await db.query("SELECT * FROM users LIMIT 10");
for (const user of users) {
  user.posts = await db.query(
    "SELECT * FROM posts WHERE user_id = $1", 
    [user.id]
  );
}

// ✅ Good: Single query with aggregation
const usersWithPosts = await db.query(`
  SELECT 
    u.id, u.email, u.name,
    COALESCE(
      json_agg(
        json_build_object('id', p.id, 'title', p.title)
      ) FILTER (WHERE p.id IS NOT NULL),
      '[]'
    ) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  LIMIT 10
`);

1. Safe Migrations

sql
-- ✅ Good: Reversible migration with no locks
BEGIN;

-- Add column with default (PostgreSQL 11+ doesn't rewrite table)
ALTER TABLE posts 
ADD COLUMN view_count INTEGER NOT NULL DEFAULT 0;

-- Add index concurrently (doesn't lock table)
COMMIT;
CREATE INDEX CONCURRENTLY idx_posts_view_count 
ON posts(view_count DESC);

-- ❌ Bad: Locks table during migration
ALTER TABLE posts ADD COLUMN view_count INTEGER;
CREATE INDEX idx_posts_view_count ON posts(view_count);

1. Connection Pooling

typescript
// Supabase with connection pooling
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!,
  {
    db: {
      schema: 'public',
    },
    auth: {
      persistSession: false, // Server-side
    },
  }
);

// Use the provider's transaction-pooler connection string for serverless.
// Only when the provider uses the SAME host/credentials and port 6543:
// change the URL port, never a matching substring in credentials or DB names.
function transactionPoolUrl(connectionString?: string): string | undefined {
  if (!connectionString) return undefined;
  const url = new URL(connectionString);
  url.port = '6543';
  return url.toString();
}
const pooledUrl = transactionPoolUrl(process.env.DATABASE_URL);

1. Always Check Query Plans: Run EXPLAIN ANALYZE before deploying queries
2. Index Foreign Keys: Every foreign key needs an index for joins
3. Avoid SELECT *: Fetch only columns you need
4. Use Connection Pooling: Never open connections per request
5. Migrations Must Be Reversible: Always write DOWN migrations
6. Never Lock Tables in Production: Use CONCURRENTLY for indexes
7. Prevent N+1 Queries: Use JOINs or batch loading
8. Monitor Slow Queries: Set up pg_stat_statements or Supabase logs