PostgreSQL Advanced Optimization: Expert-Level Performance

The Query That Took 47 Seconds

"The dashboard is timing out."

One query. 47 seconds. It had been 200ms last month. Same query, same data structure - just more data.

EXPLAIN ANALYZE revealed the truth: a sequential scan on 12 million rows. The index existed, but Postgres wasn't using it. Why? Stale statistics. The planner thought the table had 50,000 rows and chose a seq scan. After ANALYZE, the query dropped to 3ms.

PostgreSQL is remarkably capable, but it needs accurate information to make good decisions. Here's how to give it what it needs.

> If you only remember one thing: Run EXPLAIN ANALYZE before optimizing. Guessing is expensive.

Advanced Indexing Strategies

Partial indexes index only rows matching a condition. If 90% of queries filter for active users, a partial index on active users is smaller and faster than indexing everyone.

Covering indexes (using INCLUDE) store additional columns in the index itself. When all columns needed by a query are in the index, PostgreSQL can answer entirely from the index without touching the table - an index-only scan.

Expression indexes index the result of a function or expression. If you frequently query LOWER(email), index LOWER(email) directly.

JSONB indexes use GIN (Generalized Inverted Index) for containment queries or path operators. For specific key lookups, consider expression indexes on the specific paths you query.

Index selection tips:

B-tree: Default, great for equality and range queries

GIN: Full-text search, JSONB, arrays

GiST: Geometric data, full-text search

BRIN: Very large tables with naturally ordered data

Query Analysis Deep Dive

EXPLAIN ANALYZE shows the actual execution plan with real timings. Key things to look for:

Seq Scan on large tables: Usually means missing index

Nested Loop with high row counts: Can be slow, consider hash or merge joins

Sort with high memory: May spill to disk

Buffers (shared hit vs read): Reads mean cache misses

The cost numbers are relative estimates. Focus on actual time and row counts. When estimates differ wildly from actuals, statistics may be stale - run ANALYZE.

Table Partitioning

Partitioning splits a table into smaller physical pieces while maintaining a single logical table. Benefits include:

Faster queries that can skip irrelevant partitions (partition pruning)

More efficient bulk deletes (drop partition instead of DELETE)

Easier data lifecycle management (archive old partitions)

Parallel query execution across partitions

Choose partition keys based on your query patterns. Time-based partitioning works well for time-series data. Hash partitioning distributes data evenly for load balancing.

Connection Management

PostgreSQL forks a process per connection. Too many connections consume memory and can cause contention. Use connection pooling (PgBouncer or Pgpool-II) to multiplex many application connections onto fewer database connections.

Configure pool sizes based on your workload:

OLTP: Many short queries, more connections

OLAP: Few long queries, fewer connections

Mixed: Consider separate pools

Best Practices

Analyze after bulk changes: Keep statistics current

Vacuum regularly: Prevent table bloat and transaction ID wraparound

Use connection pooling: PgBouncer for connection efficiency

Monitor with pg_stat_statements: Identify slow queries

Right-size shared_buffers: Typically 25% of RAM

Test with production-like data: Query plans change with data distribution