sql-patterns

SQL Patterns

Best practices for writing safe, performant, and maintainable SQL. All examples use parameterized queries to prevent SQL injection.

Security-First Principles

Every query MUST use parameterized inputs. Never concatenate user input into SQL strings.

-- DANGEROUS: SQL injection vulnerability
SELECT * FROM users WHERE email = '" + email + "';

-- SAFE: Parameterized query
SELECT id, name, email FROM users WHERE email = $1;

Destructive Operation Safety

Never run destructive operations without a WHERE clause and explicit confirmation.

| Operation | Risk Level | Required Safeguards | |-----------|------------|---------------------| | DROP TABLE | CRITICAL | Backup first, use IF EXISTS, require approval | | TRUNCATE TABLE | CRITICAL | Backup first, confirm table name, check foreign keys | | DELETE without WHERE | CRITICAL | Always add WHERE clause, run SELECT first to preview | | UPDATE without WHERE | CRITICAL | Always add WHERE clause, run SELECT first to preview | | ALTER TABLE DROP COLUMN | HIGH | Backup first, check dependencies, use migration | | DROP INDEX | MEDIUM | Verify query plans won't degrade |

Always preview before modifying:

-- Step 1: Preview what will be affected
SELECT id, email, status FROM users WHERE status = 'inactive';

-- Step 2: Verify the count
SELECT COUNT(*) FROM users WHERE status = 'inactive';

-- Step 3: Only then modify (parameterized)
DELETE FROM users WHERE status = $1;  -- $1 = 'inactive'

Query Optimization

Using EXPLAIN

Always analyze query plans before deploying queries that touch large tables.

-- Basic explain
EXPLAIN SELECT id, name FROM users WHERE email = $1;

-- With execution stats (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > $1;

Reading EXPLAIN Output

| Term | Meaning | Action | |------|---------|--------| | Seq Scan | Full table scan | Add an index on filter columns | | Index Scan | Using an index | Good -- verify it's the right index | | Index Only Scan | Answered from index alone | Best case for read queries | | Nested Loop | Row-by-row join | Fine for small result sets, bad for large | | Hash Join | Hash-based join | Good for medium-large joins | | Sort | In-memory or disk sort | Add index if used with ORDER BY/DISTINCT | | Bitmap Heap Scan | Index + table lookup | Normal for multi-condition queries |

Selective Column Queries

-- BAD: SELECT * fetches unnecessary data, breaks if schema changes
SELECT * FROM users WHERE status = $1;

-- GOOD: Explicit columns, only what you need
SELECT id, name, email, status FROM users WHERE status = $1;

Pagination

-- BAD: OFFSET-based pagination degrades with large offsets
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 10000;

-- GOOD: Cursor-based pagination using the last seen ID
SELECT id, name FROM products
WHERE id > $1
ORDER BY id
LIMIT 20;

Indexing Guide

Index Types

| Type | Use Case | Example | |------|----------|---------| | B-tree (default) | Equality, range, sorting, LIKE 'prefix%' | CREATE INDEX idx_email ON users (email) | | Hash | Equality only (rare, B-tree usually better) | CREATE INDEX idx_code ON codes USING hash (code) | | GIN | Full-text search, JSONB, arrays | CREATE INDEX idx_tags ON posts USING gin (tags) | | GiST | Geometric, range types, full-text | CREATE INDEX idx_location ON places USING gist (coords) | | Partial | Subset of rows matching a condition | CREATE INDEX idx_active ON users (email) WHERE active = true |

Composite Indexes

Column order matters. Place equality columns first, then range columns.

-- Query pattern: WHERE status = $1 AND created_at > $2
-- Index matches the query pattern: equality first, range second
CREATE INDEX idx_status_created ON orders (status, created_at);

-- This index will NOT help the query above efficiently:
CREATE INDEX idx_created_status ON orders (created_at, status);

When to Add Indexes

| Signal | Action | |--------|--------| | Seq Scan on large table in EXPLAIN | Add index on filter/join columns | | Slow ORDER BY | Add index on sort columns | | Slow JOIN | Add index on foreign key columns | | Frequent WHERE on same columns | Add composite index | | Low selectivity column (e.g., boolean) | Partial index instead of full |

When NOT to Index

• Tables with fewer than ~1,000 rows (seq scan is faster)
• Columns that are rarely queried
• Columns with very low cardinality on large tables (unless partial index)
• Write-heavy tables where index maintenance cost exceeds read benefit

Schema Design

Normalization Quick Reference

| Normal Form | Rule | Example Violation | |-------------|------|-------------------| | 1NF | Atomic values, no repeating groups | tags: "a,b,c" in one column | | 2NF | No partial dependencies on composite key | Non-key column depends on part of key | | 3NF | No transitive dependencies | zip_code determines city in users |

When to Denormalize

Denormalize only when you have measured performance problems and understand the tradeoff.

| Scenario | Denormalization Strategy | Tradeoff | |----------|------------------------|----------| | Frequent join of 3+ tables | Materialized view | Stale data, refresh cost | | Read-heavy reporting | Summary table | Write complexity, eventual consistency | | Display name with foreign key | Cache column + trigger | Must keep in sync | | Nested JSON responses | JSONB column | Harder to query, larger rows |

Foreign Key Design

-- Always define foreign keys with explicit actions
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index foreign keys (PostgreSQL does NOT auto-index them)
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_product_id ON orders (product_id);

| ON DELETE | Behavior | Use When | |-----------|----------|----------| | RESTRICT | Block delete if referenced | Parent record must exist (orders -> users) | | CASCADE | Delete children too | Children are meaningless without parent (comments -> post) | | SET NULL | Set FK to NULL | Relationship is optional (assigned_to -> users) | | NO ACTION | Like RESTRICT but deferred | Checking at transaction commit |

Migration Best Practices

Reversible Migrations

Every migration should have a rollback strategy.

-- Migration: up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Migration: down (rollback)
ALTER TABLE users DROP COLUMN phone;

Zero-Downtime Migration Patterns

Adding a column (safe):

-- Step 1: Add nullable column (no lock, no rewrite)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Backfill in batches (avoid locking entire table)
UPDATE users SET phone = $1 WHERE id BETWEEN $2 AND $3;

-- Step 3: Add NOT NULL constraint after backfill (if needed)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Renaming a column (requires coordination):

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Backfill (batched)
UPDATE users SET full_name = name WHERE id BETWEEN $1 AND $2;

-- Step 3: Application reads from both, writes to both
-- Step 4: Switch application to new column only
-- Step 5: Drop old column in later migration
ALTER TABLE users DROP COLUMN name;

Migration Checklist

• [ ] Migration has a rollback script
• [ ] Large table changes use batched updates
• [ ] No ALTER TABLE ... ADD COLUMN ... DEFAULT on large tables (pre-PG 11)
• [ ] Index creation uses CONCURRENTLY on production tables
• [ ] Foreign key columns are indexed
• [ ] Migration tested on a copy of production data
• [ ] No data loss on rollback

-- SAFE: Concurrent ind