PostgreSQL Performance Optimization in Production: A Practical Guide · Daniel Tinizaray

When your application starts growing, the bottleneck is almost always the database. It's not that PostgreSQL is slow — it's that the default configuration is designed for a laptop, not for production.

This article covers the optimizations that actually matter in production PostgreSQL, based on experience running Django-backed databases with hundreds of thousands of records and complex queries.

1. Connection Pooling: The Most Common Mistake

Each connection to PostgreSQL consumes 5-10 MB of RAM. With 100 direct connections from a Django app, that's ~1 GB just in connection overhead. The solution: PgBouncer in transaction mode. Application connections are pooled, and PgBouncer multiplexes them against PostgreSQL.

# pgbouncer.ini
[databases]
* = host=localhost port=5432

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 200
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3.0
server_idle_timeout = 300

This setup lets 25 server connections handle traffic from 200 application clients, drastically reducing memory consumption.

2. Indexes: Quality over Quantity

A poorly designed index costs more than it helps. PostgreSQL maintains it on every INSERT/UPDATE/DELETE, slowing writes even if it speeds reads.

Composite and partial indexes

The most effective indexes cover columns in the right order. If you filter by user_id then status, the index should start with user_id.

-- Composite vs individual indexes
-- ❌ This won't help much
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ This covers the actual filter
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
WHERE status IN ('pending', 'processing');

-- Partial index for hot data
CREATE INDEX idx_orders_last_30d ON orders(created_at)
WHERE created_at > now() - interval '30 days';

Partial indexes are especially useful when you only query a subset of data (e.g., active orders from the last 30 days). The index is smaller and faster to scan.

3. Diagnostics: pg_stat_statements

Don't optimize what you don't measure. The pg_stat_statements extension is the most valuable diagnostic tool for production PostgreSQL. It tells you exactly which queries consume the most time, which tables have low cache hit ratios, and where the bottlenecks are.

-- Quick slow query diagnosis
SELECT
  queryid,
  calls,
  mean_exec_time::numeric(10,2),
  total_exec_time::numeric(10,2),
  rows / calls AS avg_rows,
  shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read) * 100
    AS cache_hit_ratio
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 10;

Enable it by adding to shared_preload_libraries and restarting PostgreSQL. The 30-second downtime is worth it.

4. Autovacuum: Your Best Friend

PostgreSQL uses MVCC (Multi-Version Concurrency Control): when you update a row, it doesn't modify it — it creates a new version and marks the old one as a "dead tuple". Without vacuum, tables grow indefinitely, and queries scan unnecessary dead tuples.

Default autovacuum works, but on large tables it's not enough. The default scales with scale_factor: when 20% of rows are dead tuples, it triggers. On a 10M row table, that means 2M dead tuples before cleanup.

-- Per-table autovacuum tuning
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_limit = 1000
);

-- Dead tuple monitoring
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2)
    AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Monitor n_dead_tup weekly. If you see tables with more than 10% dead tuples, tune the autovacuum.

5. Memory Configuration

PostgreSQL with default config allocates only 128 MB of shared buffers. On an instance with 8 GB RAM, that's wasted cache capacity.

The most impactful change: random_page_cost = 1.1. The default (4) assumes spinning disks. On SSD/NVMe, the planner undervalues index scans. Reducing it to 1.1 makes PostgreSQL prefer indexes over sequential scans.

6. Django + PostgreSQL Anti-Patterns

• N+1 queries: The classic. select_related and prefetch_related are not optional in production.
• Unbounded SELECT *: Django .all() without pagination. Always use .iterator() for large datasets.
• Long transactions: Keeping a transaction open for seconds blocks vacuum and accumulates dead tuples.
• TEXT fields without index: LIKE '%term%' won't use B-tree indexes. You need pg_trgm or a search engine.
• JSONB without GIN index: Queries inside JSONB scan the entire table without a GIN index.

Summary: PostgreSQL Optimization Checklist

• ☑ PgBouncer in transaction mode for pooling
• ☑ Composite indexes in the right column order
• ☑ Partial indexes for data subsets
• ☑ pg_stat_statements enabled
• ☑ Autovacuum tuned per table
• ☑ shared_buffers = 25% of RAM
• ☑ random_page_cost = 1.1 on SSD
• ☑ Weekly dead tuple monitoring
• ☑ No N+1 queries in production

PostgreSQL is an incredibly capable engine, but its default configuration is designed for minimal environments. Applying these adjustments turns a database that "works" into one that performs.