Optimize PostgreSQL Read-Heavy Workloads

PostgreSQL is an excellent choice for read-heavy applications like analytics dashboards, content management systems, and reporting tools. However, the default configuration is conservative and designed for compatibility rather than performance. This guide covers the key tuning parameters and architectural decisions that can improve read throughput by 5-10x on a VPS.

Memory Configuration

shared_buffers

The most impactful PostgreSQL setting — controls how much memory is dedicated to caching data pages:

# postgresql.conf
# Set to 25% of total RAM (up to ~8GB, diminishing returns beyond)
# 4GB VPS: 1GB, 8GB VPS: 2GB, 16GB VPS: 4GB
shared_buffers = '2GB'

effective_cache_size

Tells the query planner how much memory is available for caching (shared_buffers + OS page cache):

# Set to 75% of total RAM
# 4GB VPS: 3GB, 8GB VPS: 6GB
effective_cache_size = '6GB'

work_mem and maintenance_work_mem

# Memory per sort/hash operation (per query, per operation!)
# Be careful: 20 concurrent queries × 4 operations × 64MB = 5GB
work_mem = '64MB'

# Memory for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = '512MB'

Parallel Query Execution

# Enable parallel queries for large table scans
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 500

# Force parallel for testing
SET force_parallel_mode = on;

Indexing Strategies

Partial Indexes

-- Only index rows you actually query
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status = 'active';
-- Much smaller than indexing all orders

Covering Indexes (Index-Only Scans)

-- Include columns needed in the query to avoid table lookups
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name, created_at);
-- SELECT name, created_at FROM users WHERE email = 'x' → index-only scan

Expression Indexes

-- Index computed values
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Now WHERE LOWER(email) = 'user@example.com' uses the index

BRIN Indexes for Time-Series

-- Block Range INdex — tiny index for naturally ordered data
CREATE INDEX idx_events_created ON events USING brin(created_at);
-- 1000x smaller than B-tree for append-only tables

Query Optimization

-- Always analyze queries with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123;

-- Key things to look for:
-- Seq Scan on large tables → needs an index
-- Nested Loop with high rows → consider Hash Join
-- Buffers: shared read (high) → data not cached, increase shared_buffers
-- Sort Method: external merge → increase work_mem

-- Common optimization: replace subqueries with JOINs
-- SLOW
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
-- FAST
SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 100;

Connection Pooling with PgBouncer

PostgreSQL forks a process per connection (~5-10MB each). For high-connection applications, PgBouncer is essential:

# Install
sudo apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

# Connect your application to port 6432 instead of 5432

Read Replicas

# For truly read-heavy workloads, add streaming replicas
# On primary (postgresql.conf)
wal_level = replica
max_wal_senders = 5
wal_keep_size = '1GB'

# On replica
primary_conninfo = 'host=primary_ip port=5432 user=replication password=secret'
hot_standby = on

# In your application, route reads to replicas:
# Writes → primary (port 5432)
# Reads → replica (port 5433 or different host)

Materialized Views for Complex Queries

-- Pre-compute expensive aggregations
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', created_at) AS month,
    product_id,
    SUM(quantity) AS total_qty,
    SUM(amount) AS total_amount
FROM orders
GROUP BY 1, 2;

CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month, product_id);

-- Refresh periodically (can be concurrent to avoid locking)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Statistics and Autovacuum

# Better statistics = better query plans
default_statistics_target = 200  # Default 100, increase for complex queries

# More aggressive autovacuum for read-heavy tables
# Keeps statistics fresh and prevents bloat
autovacuum_max_workers = 4
autovacuum_naptime = '30s'
autovacuum_vacuum_scale_factor = 0.05  # Vacuum at 5% dead tuples (default 20%)
autovacuum_analyze_scale_factor = 0.02

# Per-table override for hot tables
ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01);

Monitoring Read Performance

-- Cache hit ratio (should be >99%)
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_ratio
FROM pg_statio_user_tables;

-- Index usage ratio (should be >95% for read-heavy)
SELECT
    relname,
    idx_scan / (seq_scan + idx_scan)::float AS idx_ratio,
    seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY idx_ratio ASC;

-- Slowest queries (requires pg_stat_statements)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    total_exec_time::numeric(10,2) AS total_ms,
    left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Summary

PostgreSQL read optimization follows a hierarchy: first tune memory settings (shared_buffers at 25% RAM, effective_cache_size at 75%), then optimize queries and indexes using EXPLAIN ANALYZE, add connection pooling with PgBouncer for high-concurrency workloads, and consider read replicas when a single server is insufficient. Materialized views pre-compute expensive aggregations, and pg_stat_statements identifies the queries most worth optimizing. With these changes, a well-tuned 4GB VPS can serve thousands of read queries per second.