Postgres tuning for small SaaS — settings that matter

Default Postgres configuration assumes nothing about the machine it runs on. Conservative limits prevent crashes on tiny servers. On a dedicated 16GB box for a small SaaS, those defaults leave 50-200% performance unrealized.

Tuning Postgres for small SaaS isn't black magic. A focused set of settings makes most of the difference.

Memory settings

shared_buffers

Memory Postgres uses for its page cache. Default: 128MB.

Rule of thumb: 25% of RAM for dedicated database server. On 16GB box: 4GB.

shared_buffers = 4GB

effective_cache_size

Hint to query planner about how much memory is available for caching (Postgres + OS). Default: 4GB.

Rule: 50-75% of RAM. On 16GB: 12GB.

effective_cache_size = 12GB

work_mem

Memory per query operation (sort, hash). Default: 4MB.

Tricky — multiplied by concurrent operations. Too high and you exhaust memory. Rule: (RAM - shared_buffers) / (max_connections * 2).

On 16GB with max_connections=100: ~50MB.

work_mem = 50MB

maintenance_work_mem

Memory for maintenance ops (VACUUM, CREATE INDEX). Default: 64MB.

For 16GB box: 512MB-2GB. Big VACUUMs and index builds get fast.

maintenance_work_mem = 1GB

WAL (write-ahead log)

wal_buffers

Default: 16MB. Usually fine. Leave alone.

checkpoint_timeout

How often Postgres forces a checkpoint. Default: 5min. For write-heavy workloads, increase to 15-30min to reduce checkpoint I/O spikes.

checkpoint_timeout = 15min
max_wal_size = 4GB

checkpoint_completion_target

Default: 0.9. Spreads checkpoint I/O over 90% of the interval. Good default.

Connections

max_connections

Default: 100. Tempting to crank up. Don't.

Each connection has overhead. Many apps work fine with 100 connections + connection pooler (pgBouncer).

If you genuinely need more (>200), put pgBouncer in front. Apps connect to pgBouncer; pgBouncer multiplexes onto fewer real Postgres connections.

Autovacuum

Default autovacuum is too lazy for high-write workloads. Tables bloat, performance degrades over weeks.

autovacuum_vacuum_scale_factor = 0.05  # default 0.2
autovacuum_analyze_scale_factor = 0.02 # default 0.1
autovacuum_naptime = 30s               # default 1min
autovacuum_max_workers = 4             # default 3

For specific high-churn tables, set per-table overrides.

Random page cost

Default: 4.0. Assumes spinning disks. For SSDs, this overestimates index lookup cost — planner picks sequential scans when index would be faster.

random_page_cost = 1.1  # SSD default
effective_io_concurrency = 200  # SSD

Logging

Log slow queries to identify hotspots:

log_min_duration_statement = 1000  # log queries > 1 second
log_lock_waits = on
log_temp_files = 0  # log temp file usage
log_checkpoints = on

Combine with pg_stat_statements extension for query-level analysis.

Indexes — the biggest win

Configuration matters. Indexes matter more. Common gaps:

• Missing index on foreign keys.
• No composite index for common WHERE+ORDER BY combinations.
• Index on columns with low selectivity (status='active' when 95% are active).
• Unused indexes consuming write performance.

Use pg_stat_user_indexes to find unused indexes:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Connection pooling — pgBouncer

For any production SaaS, run pgBouncer between apps and Postgres:

• Transaction-level pooling for most apps.
• Session-level if you use prepared statements heavily.
• Limits effective connections to Postgres, prevents overload.

Monitoring

Even tuned Postgres needs visibility. Minimum:

• Connection count — should stay well under max_connections.
• Cache hit ratio — aim for >99% on shared_buffers.
• Slow queries — from pg_stat_statements.
• Replication lag if you have replicas.
• Disk I/O — sustained high I/O suggests indexing or tuning gaps.
• WAL size — large WAL accumulation indicates checkpoint issues.

What not to bother with

• Tuning every setting. Stick to the dozen above.
• Premature partitioning. Most tables don't need it until 100M+ rows.
• Switching storage engines. Postgres only has one.
• NoSQL because "Postgres can't scale." It can.

Verdict

For a small SaaS, default Postgres is left a lot on the table. Tune shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, autovacuum aggressiveness, random_page_cost for SSD, and slow query logging. Add pgBouncer. Fix missing indexes. Postgres handles surprising scale when configured right.