PostgreSQL is one of the most powerful relational database engines in software engineering. However, as tables grow from tens of thousands of rows to tens of millions, un-indexed queries that executed in 2ms suddenly degrade to 10-second CPU-bound Sequential Scans.

Optimizing PostgreSQL requires more than just sprinkling random indexes on every column. In this practical database tuning guide, we examine index structures (B-Tree, Partial, GIN, BRIN), master reading EXPLAIN (ANALYZE, BUFFERS) execution plans, configure memory settings (shared_buffers, work_mem), and prevent connection exhaustion with PgBouncer.

1. Index Selection: B-Tree vs Partial vs GIN

A. Standard B-Tree Indexes

B-Tree (Balanced Tree) is the default index type in Postgres. It handles equality (=) and range queries (<, >, BETWEEN, IN) in $O(\log N)$ time.

Rule of Multi-Column Indexes: Always place the most selective equality columns first, followed by range columns.

B. Partial Indexes (Space & Write Optimization)

If you only query active rows in a table containing 10 million archived records, creating a full index wastes disk space and slows down UPDATE statements. Create a Partial Index instead:

-- Index only active accounts (Reduces index size by 90%) CREATE INDEX idx_users_active_email ON users (email) WHERE is_active = TRUE;

C. Generalized Inverted Index (GIN)

Use GIN indexes for querying JSONB documents, PostgreSQL array types, or Full-Text Search vectors.

2. Mastering EXPLAIN ANALYZE Execution Plans

To diagnose slow queries, prefix your SQL statement with EXPLAIN (ANALYZE, BUFFERS, VERBOSE):

EXPLAIN (ANALYZE, BUFFERS) SELECT u.id, u.username, o.total_amount FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at >= '2026-01-01' AND o.status = 'COMPLETED';

Key Execution Node Indicators:

  • Seq Scan (Sequential Scan): Reads every page on disk sequentially. If seen on a large table, a missing index is the root cause.
  • Index Scan vs Index Only Scan: Index Scan reads index pointers then fetches table heap pages. Index Only Scan fetches data directly from index leaf nodes without touching heap storage (10x faster).
  • Buffers: shared hit=452 read=12: Indicates 452 pages were served directly from RAM (shared_buffers), while 12 pages required physical disk reads.

3. Tuning Critical Memory Parameters

PostgreSQL ships with conservative default configuration settings designed to run on low-resource machines. Modify `postgresql.conf` for production servers:

  • shared_buffers: Set to 25% of total system RAM. (e.g. 16 GB RAM server $\rightarrow$ 4 GB shared_buffers). Dedicated to caching table and index pages in memory.
  • work_mem: Memory allocated per internal sort operation and hash table *per query step*. Setting work_mem = 64MB prevents queries from spilling sort operations to slow disk temporary files.
  • maintenance_work_mem: Memory used for VACUUM, CREATE INDEX, and ALTER TABLE (e.g. Set to 1 GB - 2 GB).

4. Preventing Connection Exhaustion with PgBouncer

Every direct connection to PostgreSQL forks a dedicated backend process consuming 5 MB to 10 MB of RAM. Attempting 1,000 direct database connections from container instances causes heavy CPU context switching and OOM crashes.

Deploy PgBouncer in front of PostgreSQL in Transaction Pooling Mode. PgBouncer multiplexes thousands of incoming application connections into a small pool of 50-100 real database connections, reducing CPU context switching and maintaining sub-millisecond connection checkout.

5. Frequently Asked Questions (FAQ)

Q1: How often does Autovacuum run?

Autovacuum triggers automatically when dead tuples exceed autovacuum_vacuum_scale_factor (default 20% of table rows). Tune this factor down to 5% on high-write tables to prevent bloat.

Q2: Should I use `CREATE INDEX CONCURRENTLY`?

Yes! Standard `CREATE INDEX` acquires an Exclusive Lock blocking table writes. `CONCURRENTLY` builds the index without locking concurrent `INSERT/UPDATE` operations.