PostgreSQL in Production

PostgreSQL is the world's most advanced open-source relational database. Its default configuration is conservative, optimized for compatibility rather than performance. Production workloads require tuning — configuration adjustments, query optimization, indexing strategy, and proper monitoring. At Nexis Limited, PostgreSQL powers all four SaaS products and runs workloads ranging from simple CRUD to complex analytics queries.

Configuration Tuning

Memory Settings

  • shared_buffers: Set to 25% of total RAM. This is PostgreSQL's main data cache.
  • effective_cache_size: Set to 75% of total RAM. This tells the query planner how much OS cache is available.
  • work_mem: Memory for sort operations and hash tables. Start at 64MB and adjust based on query patterns. Higher values improve sort performance but multiply by the number of concurrent connections.
  • maintenance_work_mem: Memory for VACUUM, CREATE INDEX, and ALTER TABLE. Set to 512MB-1GB for faster maintenance operations.

Write Ahead Log (WAL)

  • wal_buffers: Set to 64MB for write-heavy workloads.
  • checkpoint_completion_target: Set to 0.9 to spread checkpoint I/O over time.
  • max_wal_size: Set to 4GB-8GB to reduce checkpoint frequency.

Query Optimization

EXPLAIN ANALYZE

The most important tool for query optimization. EXPLAIN ANALYZE runs the query and shows the actual execution plan — which indexes were used, how many rows were scanned, and where time was spent. Look for sequential scans on large tables, nested loops with high row counts, and sort operations that spill to disk.

Common Optimizations

  • Add indexes on columns used in WHERE clauses and JOIN conditions.
  • Use covering indexes (INCLUDE columns) to avoid table lookups.
  • Rewrite correlated subqueries as JOINs.
  • Use LIMIT early in queries to reduce the working set.
  • Avoid SELECT * — select only the columns you need.

Indexing Strategies

  • B-tree indexes: The default and most versatile. Use for equality and range queries.
  • GIN indexes: For full-text search, JSONB queries, and array operations.
  • BRIN indexes: For large tables where data is naturally ordered (timestamps, sequential IDs). Very small index size.
  • Partial indexes: Index only rows matching a condition (WHERE status = 'active'). Reduces index size and improves query performance for filtered queries.
  • Composite indexes: Cover multiple columns frequently queried together. Column order matters — put the most selective column first.

Connection Pooling

PostgreSQL creates a process per connection, making it expensive to handle many connections. Use a connection pooler like PgBouncer or Pgpool-II between your application and PostgreSQL. PgBouncer in transaction mode reuses connections efficiently, allowing hundreds of application threads to share a few dozen database connections.

VACUUM and Autovacuum

PostgreSQL's MVCC implementation requires regular VACUUM operations to reclaim space from dead rows. Autovacuum handles this automatically, but high-update tables may need tuned autovacuum settings:

  • Lower autovacuum_vacuum_scale_factor for large tables.
  • Increase autovacuum_max_workers for databases with many tables.
  • Monitor table bloat and dead tuple counts.

Monitoring

Essential PostgreSQL metrics to monitor:

  • Query latency (p50, p95, p99) using pg_stat_statements.
  • Connection count and connection pool utilization.
  • Cache hit ratio (should be above 99%).
  • Dead tuples and table bloat.
  • Replication lag for read replicas.
  • Lock wait times and deadlocks.

Conclusion

PostgreSQL performance tuning is a combination of proper configuration, query optimization, indexing strategy, and monitoring. Start with configuration tuning (it is the lowest effort), then use EXPLAIN ANALYZE to find and fix slow queries, and maintain indexes that match your query patterns. Monitor continuously and tune iteratively.

Need database optimization? Our team has deep PostgreSQL expertise across multiple production systems.