SQL Tuning

SQL tuning is fundamentally about two things: giving the database the data structures it needs to answer your query without scanning the whole table, and writing queries the planner can understand. The planner is smart but not psychic — it works from statistics, indexes, and the literal shape of your SQL.

The core toolkit, in order of leverage:

1. Indexes — B-tree (default), composite, partial, expression. The single biggest performance lever. A sequential scan becomes an index lookup: O(N) → O(log N).
2. EXPLAIN and EXPLAIN ANALYZE — read the query plan the planner generates. Without this, you're tuning blind.
3. Query rewriting — think in sets, not loops. Replace correlated subqueries with JOINs; replace N+1 patterns with single queries.
4. Connection pooling — PgBouncer, Pgpool, or pool inside your app. Eliminates connection setup cost.
5. Schema and statistics — ANALYZE keeps planner statistics fresh; partitioning large tables limits scan scope.
6. Configuration — work_mem, shared_buffers, effective_cache_size matter, but only after the above are right.

Most SQL tuning 'secrets' are the first two: have the right index, and read EXPLAIN.

Indexes are the dominant SQL performance lever. PostgreSQL (and most databases) support several flavors:

• B-tree (default) — equality and range lookups. WHERE id = 42, WHERE created_at > '2025-01-01'.
• Composite — multi-column. Order matters: INDEX(a, b) serves WHERE a=? AND b=? and WHERE a=? but not WHERE b=? alone. Put the most selective column first OR the one used in equality first, depending on the planner.
• Partial — index only matching rows. CREATE INDEX … WHERE deleted_at IS NULL — smaller index, faster scans, automatic in queries that match the predicate.
• Expression — index on a function. CREATE INDEX ON users(lower(email)) makes case-insensitive email lookups fast.
• GIN/GIST — for full-text search, JSONB containment, geospatial.
• Covering (INCLUDE) — CREATE INDEX … INCLUDE (col1, col2) lets the query be answered from the index alone (an 'index-only scan').

The cost of indexes: writes get slower (every INSERT/UPDATE must update every index), disk usage grows, and the planner can choose the wrong index if statistics are stale. Indexes are a read/write trade-off.

Golden rules:

1. Index your foreign keys — JOIN and WHERE child.parent_id = ? are the most common slow paths.
2. Index the columns in your WHERE, JOIN, ORDER BY, and GROUP BY clauses — but only the ones with high selectivity.
3. Don't index low-selectivity columns (a boolean is_active on a table where 99% are active).
4. Run ANALYZE after large data loads to refresh planner statistics.

Query patterns that bite:

• Sargability — WHERE DATE(created_at) = '2025-01-15' can't use an index on created_at because the function wraps the column. Rewrite as WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16'. A 'sargable' predicate is one the planner can use with an index.

• SELECT * — fetching all columns when you need two wastes I/O and defeats covering indexes. SELECT id, name FROM … lets the planner use an index-only scan.

• OFFSET pagination — LIMIT 100 OFFSET 100000 still scans 100,100 rows. Use keyset pagination: WHERE id > last_seen_id ORDER BY id LIMIT 100. Constant time per page.

• COUNT(*) on big tables — PostgreSQL's COUNT(*) is a full scan; there's no row count cached. Maintain a counter table or use approximate counts.

• Correlated subqueries — WHERE x IN (SELECT y FROM … WHERE … = outer.z) may execute per-row. Rewrite as a JOIN or EXISTS.

• OR across columns — WHERE a=? OR b=? may force a full scan. Use UNION of two indexed queries, or composite indexes.

• Implicit type casts — WHERE varchar_col = 42 may cast every row instead of using an index. Match the column type.

• LIKE '%foo%' — leading wildcard can't use an index. Use full-text search (GIN/GIST) or trigram indexes (pg_trgm) for substring search.

Every one of these shows up clearly in EXPLAIN ANALYZE as a Seq Scan or a slow node. The planner tells you the problem; the fix is usually an index or a query rewrite.

Connection pooling is invisible until it isn't. PostgreSQL forks a process per connection — a few thousand connections can exhaust memory and cause the 'too many connections' death spiral. Each new connection also pays a TCP handshake, TLS handshake, and Postgres startup cost (~5-10ms).

A pooler (PgBouncer in transaction mode is the standard) keeps a small set of long-lived connections to Postgres and multiplexes application requests across them. The math: 5000 app connections → 50 Postgres connections, with the pooler handling queueing.

Application-side pooling (HikariCP for Java, sqlx for Rust, asyncpg for Python, the pg pool for Node) reduces connection setup cost but doesn't cap Postgres connections unless paired with a server-side pooler. Best practice: app-side pool of 10-30 connections per instance, plus PgBouncer in front of Postgres.

Signs you need a pooler:

• 'Too many connections' errors under load.
• p99 latency spikes on connection setup.
• Postgres memory growing linearly with app instances.
• A new service instance can't connect because the limit is hit.

Mental model. Think of SQL Tuning as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does SQL Tuning mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose SQL Tuning, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.