PostgreSQL

Purpose

Operate PostgreSQL well: understand what MVCC costs you, why the table is bloated, why the connection count matters more than you think, and how to change a large table without locking it.

When to Use

• Designing schemas or indexes for Postgres.
• Diagnosing bloat, slow queries, or lock contention.
• Configuring connection pooling and replication.
• Running a migration on a large table without downtime.

Capabilities

• Index types: B-tree, GIN, GiST, BRIN, partial, expression, covering.
• MVCC, dead tuples, autovacuum tuning, and transaction ID wraparound.
• Connection pooling with PgBouncer and the pooling modes.
• Partitioning: declarative range and list partitions.
• JSONB indexing and query patterns.
• Streaming replication, replicas, and replication lag.

Inputs

• The schema, the table sizes, and the query patterns.
• pg_stat_statements output for the slow-query question.
• Server configuration and Postgres version.

Outputs

• Indexes that match the access patterns, with no unused ones.
• Autovacuum settings appropriate to the write volume.
• Migrations that acquire only brief locks.

Workflow

1. Find the real slow queries — pg_stat_statements ordered by total time, not by mean. A 30ms query executed a million times costs more than a 4-second query run once.
2. Index for the access pattern — Equality columns first in a composite index, then the range or sort column. Add partial indexes for the filters that dominate.
3. Watch the dead tuples — Every UPDATE writes a new row version and leaves the old one dead. A hot table with default autovacuum settings will bloat and slow down.
4. Pool the connections — Each Postgres connection is a process with real memory cost. Beyond a few hundred, performance degrades. PgBouncer in transaction mode is the standard answer.
5. Migrate without locking — CREATE INDEX CONCURRENTLY. Add columns nullable, backfill in batches, then set defaults and constraints with NOT VALID followed by VALIDATE.
6. Set a lock timeout — Before any DDL: SET lock_timeout = '3s'. A migration that waits behind a long transaction will queue every subsequent query behind itself.

Best Practices

• ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT <value> is safe on Postgres 11+ (no table rewrite). Adding a CHECK constraint or a foreign key still requires a scan — use NOT VALID, then VALIDATE CONSTRAINT separately.
• An unused index is a write-amplification tax paid on every insert and update. pg_stat_user_indexes shows which have never been scanned. Drop them.
• Autovacuum defaults are tuned for a small database. On a high-write table, lower autovacuum_vacuum_scale_factor for that table specifically.
• idle in transaction connections hold locks and block vacuum indefinitely. Set idle_in_transaction_session_timeout.
• PgBouncer in transaction mode breaks prepared statements, LISTEN/NOTIFY, and session-level SET. Know this before adopting it, not after.
• Replicas serve read traffic but lag. A read-after-write on a replica will see stale data — route the read to the primary or accept it.

Examples

A migration on a large table that takes no meaningful lock:

-- Adding a NOT NULL column with a default: safe and instant on PG 11+.
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'USD';

-- A foreign key normally takes a lock and scans the whole table. Split it:
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  NOT VALID;                              -- instant: only new rows are checked

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
                                          -- scans, but takes only a SHARE UPDATE
                                          -- EXCLUSIVE lock: writes continue.

-- An index without blocking writes:
SET lock_timeout = '3s';                  -- do not queue behind a long transaction
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC)
  WHERE deleted_at IS NULL;

Finding the queries and the indexes that matter:

-- The queries that actually consume the database, by total time.
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(total_exec_time::numeric, 0) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows / GREATEST(calls, 1) AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Indexes that have never been used: pure write overhead.
SELECT relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (
  SELECT conindid FROM pg_constraint WHERE contype IN ('p','u')
)
ORDER BY pg_relation_size(indexrelid) DESC;

Notes

• CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and it can fail leaving an invalid index behind. Check pg_index.indisvalid afterwards and drop any invalid index before retrying.
• Transaction ID wraparound is the failure mode that takes a Postgres cluster fully offline. It only happens when autovacuum has been failing for a long time and being ignored — monitor age(datfrozenxid).
• JSONB is excellent for genuinely schemaless data and a poor substitute for columns. A JSONB field that every query filters on should be a column with an index.