PostgreSQL at Scale: Indexing, Partitioning and Connection Pooling

Most Postgres scaling pain is self-inflicted: missing indexes found in production, one giant table doing all the work, and a thousand idle connections. Fix those three and Postgres carries you further than expected.

Index for the query, not the table

Read EXPLAIN ANALYZE before adding anything. Composite indexes must match the WHERE order and sort direction of your hot queries, and partial indexes shrink the structure to just the rows you actually filter.

1CREATE INDEX CONCURRENTLY idx_orders_user_status
2  ON orders (user_id, status, created_at DESC)
3  WHERE status IN ('pending', 'processing');

Partition the hot tables

Time-series and event tables grow forever. Range-partition by month so old partitions detach cheaply, vacuums stay fast, and queries prune everything they do not need.

1CREATE TABLE events (
2  id BIGINT GENERATED ALWAYS AS IDENTITY,
3  occurred_at TIMESTAMPTZ NOT NULL,
4  payload JSONB NOT NULL
5) PARTITION BY RANGE (occurred_at);
6
7CREATE TABLE events_2025_08 PARTITION OF events
8  FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');

Pool connections like they cost money

Every connection is memory and context switches. Run a transaction-mode pooler between the app and the database, cap pool size near core count, and set statement timeouts so one bad query cannot hold the pool hostage.

Because they do. Size the pool for the database you have, not the traffic you wish for.

Measure before and after

Turn on pg_stat_statements and let production tell you which queries deserve attention. Capture EXPLAIN ANALYZE output before each change and compare after, because an index that looks right can still lose to the planner on skewed data.

Keep these alerts on

• •Sequential scans on large tables, the classic missing-index signal.
• •Connection count against pool limits, with headroom for deploys.
• •Replication lag on read replicas, so stale reads never surprise you.
• •Backup age plus a tested restore, since an untested backup is a rumor.

Postgres rewards operators who watch it. An hour a week with these four signals prevents most of the scaling emergencies teams otherwise meet at 3am.