PostgreSQL Performance Tuning: Query Optimization and Indexing Strategies

PostgreSQL performance tuning isn't magic—it's a systematic process of understanding how the query planner works, choosing the right index type for your data access patterns, and keeping the database healthy with proper maintenance. Most performance problems boil down to missing indexes, bad query patterns, or neglected VACUUM schedules.

Reading EXPLAIN Plans

Before optimizing anything, learn to read EXPLAIN ANALYZE output. It's the single most important skill for PostgreSQL performance:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 50;

Key metrics to look for:

• Seq Scan on large tables—almost always means a missing index
• Actual time vs estimated time—wild discrepancies indicate stale statistics
• Buffers: shared hit/read—high read counts suggest insufficient shared_buffers or cold cache
• Rows Removed by Filter—excessive removal suggests a poor WHERE clause order or missing partial index

Run ANALYZE after bulk data loads to keep statistics fresh. PostgreSQL's autovacuum handles routine maintenance, but it needs proper configuration for large or write-heavy databases.

Choosing the Right Index Type

PostgreSQL offers several index types, each optimized for different access patterns:

B-tree (default) is the jack of all trades. Use it for equality and range queries on columns used in =, <, >, BETWEEN, IN, ORDER BY, and LIKE 'prefix%':

CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
  WHERE status != 'cancelled';

The partial index above includes only non-cancelled orders, keeping the index small and fast for active order queries.

GIN (Generalized Inverted Index) excels at composite-type lookups: JSONB queries, full-text search, arrays. A GIN index on a JSONB column can dramatically accelerate containment and key-exists operators:

CREATE INDEX idx_users_preferences ON users USING GIN (preferences jsonb_path_ops);

GiST (Generalized Search Tree) handles full-text search ranking, geometric data, range types, and nearest-neighbor (ORDER BY ... <->) queries. Use it for geospatial queries with PostGIS or for tsvector full-text search.

BRIN (Block Range Index) is the unsung hero of large, append-only tables. It stores min/max values per disk block range, requiring minimal storage:

CREATE INDEX idx_events_created_at ON events USING BRIN (created_at)
  WITH (pages_per_range = 32);

A BRIN index on a 100-million-row table of timestamped events takes kilobytes, not gigabytes. It's ideal for time-series data where queries always filter by a time range.

Connection Pooling

PostgreSQL forks a process per connection. At 200 connections, context switching starts degrading performance. At 500+, the database fights itself.

Always use a connection pooler in front of PostgreSQL:

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20

Transaction-level pooling means an application connection can serve multiple database transactions sequentially. Twenty pooled connections behind PgBouncer can handle hundreds of application connections without hitting PostgreSQL's backend limit.

VACUUM Configuration and Bloat Management

PostgreSQL's MVCC architecture creates dead tuples on every UPDATE or DELETE. VACUUM reclaims that space. Autovacuum handles this automatically, but default settings favor safety over responsiveness for write-heavy workloads:

autovacuum_vacuum_scale_factor = 0.01      # was 0.2
autovacuum_vacuum_threshold = 50            # was 50
autovacuum_vacuum_cost_limit = 2000         # was 200

These tighter settings trigger vacuums more frequently with smaller work per session, preventing massive bloat buildup. Monitor pg_stat_user_tables.n_dead_tup and alert when dead tuples exceed 20% of live tuples.

Query-Level Optimizations Beyond Indexes

Sometimes indexes aren't the answer. Restructuring queries and using materialized views can yield bigger wins:

-- Instead of this (counts every time)
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- Use a materialized view refreshed periodically
CREATE MATERIALIZED VIEW pending_order_count AS
SELECT COUNT(*) as count FROM orders WHERE status = 'pending';
CREATE UNIQUE INDEX ON pending_order_count(1);

For complex aggregation queries that return stale-enough results, materialized views offload compute from the hot query path.

Tune PostgreSQL Performance with SoniNow

PostgreSQL can handle enormous workloads with proper configuration and query design. SoniNow's database engineering team helps optimize PostgreSQL deployments—from index strategy to connection pooling to vacuum tuning—so your queries stay fast as your data grows.