PostgreSQL Tuning Tips for Better Performance

Introduction

PostgreSQL has evolved into one of the most powerful open-source relational databases, widely used in enterprise-scale systems. However, the default configuration prioritizes safety and portability over performance. As data volumes grow into hundreds of gigabytes or terabytes, tuning PostgreSQL becomes critical to ensure optimal query response times, reduced I/O pressure, and improved replication throughput.

This guide shares real-world performance tuning techniques for PostgreSQL, covering configuration parameters, query-level optimization, maintenance strategies, and monitoring examples. All recommendations are validated through actual workloads on large datasets.

System and Hardware Foundation

Effective PostgreSQL performance starts with a solid OS and hardware foundation. Default OS parameters and filesystem configurations prioritize compatibility and stability but may not be optimized for high-performance, large-scale PostgreSQL workloads. Custom tuning of kernel parameters, filesystem choices, and I/O topology like separating WAL on fast NVMe drastically reduce bottlenecks for heavy write loads.

Filesystem and I/O

• Use ext4 or XFS with noatime mount option to reduce metadata writes.
• Use RAID10 for write-heavy workloads; avoid RAID5/6 due to latency.
• Place WAL directory (pg_wal/) on fast NVMe or SSD separate from the data directory.

OS Parameters

Add the following to /etc/sysctl.conf (values depend on your hardware):

vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
kernel.shmmax = 68719476736   # 64 GB example
kernel.shmall = 4294967296
vm.overcommit_memory = 2

HugePages and NUMA

Transparent HugePages (THP) may be enabled or disabled by the OS. For PostgreSQL, set to never or madvise after benchmarking.
NUMA tuning mostly benefits multi-socket large servers. Binding PostgreSQL to specific CPUs/memory nodes can improve performance but is advanced and not needed for most setups.

Memory and Caching Optimization

PostgreSQL defaults use conservative memory settings for portability, which under-utilize modern server RAM. Proper memory tuning, especially shared_buffers and work_mem, directly impacts performance by caching more data in memory and allowing efficient sorting and hash operations.

Note: work_mem is allocated per operation, per query; setting it too high globally can cause excessive memory usage under concurrency.

Parameter	Purpose	Recommended Range
shared_buffers	Shared cache for data pages	25–40% of total RAM
effective_cache_size	OS + PostgreSQL cache estimate	50–75% of total RAM
work_mem	Per-sort memory	32–128MB (per session)
maintenance_work_mem	Used for VACUUM, CREATE INDEX	512MB–2GB

Example:

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 64MB
maintenance_work_mem = 1GB

Example
Before tuning:

Sort  (cost=100000.00..120000.00 rows=500000) (actual time=2314.235..2487.221)

After increasing work_mem from 4MB to 64MB:

Sort  (Memory: 56MB)  (actual time=143.231..146.022)

This reduced query time by 17x.

Cache Hit Ratio Example Query:

SELECT datname, blks_read, blks_hit,
ROUND(100.0 * blks_hit / (blks_hit + blks_read), 2) AS cache_hit_ratio
FROM pg_stat_database;
datname | blks_read | blks_hit | cache_hit_ratio
----------+-----------+----------+-----------------
mydb | 11049 | 424921 | 97.47
eventdb | 4093 | 60432 | 93.65
postgres | 3021 | 20124 | 86.96

WAL, Checkpoints & I/O Tuning

Write-Ahead Logging affects durability and write performance. PostgreSQL defaults target durability over throughput and can lead to checkpoint spikes under heavy loads. Tuning WAL buffers and checkpoint timings balances write performance with data safety.

Parameter	Purpose	Recommended Setting
wal_level	Log detail for replication	replica or logical
wal_buffers	Memory for WAL writes	16MB–64MB
checkpoint_timeout	Max time between checkpoints	15–30 min
checkpoint_completion_target	Smooth checkpoint writes	0.8–0.9
max_wal_size	WAL space before checkpoint	4–16GB

Example:

wal_buffers = 32MB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
max_wal_size = 8GB

The pg_stat_io system view provides detailed insights into the I/O operations in your PostgreSQL cluster, breaking down activity by backend type, object (tables, WAL files), and operational context. It helps pinpoint I/O bottlenecks and verify if tuning efforts (like adjusting shared_buffers or WAL settings) are effective.

For example, the query below shows I/O reads and writes on datafiles and WAL files:

SELECT backend_type, object, reads, writes
FROM pg_stat_io
WHERE object IN ('datafile', 'walfile');
 backend_type |  object  | reads | writes 
--------------+----------+-------+--------
 background   | walfile  |     0 |   3421
 checkpointer | datafile |  4002 |   1230

Understanding which backend types generate the most I/O and the breakdown between data and WAL files is essential for targeting optimizations precisely. For example, high WAL writes may merit tuning wal_buffers, while excessive datafile writes may highlight inefficient query patterns.

Query Optimization & Execution Plans

Query optimization is about making queries as efficient as possible to minimize database resource usage and maximize responsiveness. The default PostgreSQL query planner chooses execution plans based on statistics, but without indexes or tuning, large queries may use slow sequential scans.

Key steps:

• Use pg_stat_statements to find slow, frequently executed queries.
• Run EXPLAIN (ANALYZE, BUFFERS) to visualize execution plans—look for Seq Scan signs, high cost, or excessive runtime.
• Index columns often used in predicates (WHERE clauses), joins, ordering, or filtering.
• Rewrite queries to leverage indexes. Avoid unnecessary subqueries or functions in WHERE that prevent index usage.
• Regularly VACUUM and ANALYZE to refresh the engine’s view of the data.

Logs :

Before Index:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM event_log WHERE event_date >= '2025-07-01';
Seq Scan on event_log  (cost=0.00..1823423.50 rows=116000000)
(actual time=0.123..8321.456 rows=116000000 loops=1)
Buffers: shared hit=1200000 read=40000 dirtied=0 written=0

Add Index:

CREATE INDEX idx_event_date ON event_log(event_date);

After Index:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM event_log WHERE event_date >= '2025-07-01';
Index Scan using idx_event_date on event_log (cost=0.43..243.76 rows=110000000)
(actual time=0.120..243.765 rows=110000000 loops=1)
Buffers: shared hit=240000 read=6000 dirtied=0 written=0

Execution time reduced by 34x—proving that indexing massively improves performance, especially for large tables.

Table Partitioning & Maintenance

Partitioning organizes very large tables into “child” parts based on a chosen key, typically a date or ID range.
Why use partitioning?

• Scans, VACUUM, and ANALYZE only touch relevant partitions.
• Bloat and performance issues are reduced, especially for rolling data (like logs, metrics, or events).
• It enables easy archival and bulk deletion of old data.

Strategy: Monthly partitions are common for time-series and logs.

CREATE TABLE event_log (
    id BIGSERIAL,
    event_date DATE NOT NULL,
    payload JSONB
) PARTITION BY RANGE (event_date);

-- Create January partition
CREATE TABLE event_log_2025_01 PARTITION OF event_log
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

List partitions for event_log:

SELECT relname FROM pg_class WHERE relkind = 'r' AND relname LIKE 'event_log_%';

     relname       
--------------------
event_log_2025_01
event_log_2025_02
event_log_2025_03

Partitioning must be paired with proper indexing and autovacuum tuning:

autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 5

PostgreSQL improves partition pruning with parallel-awareness, so only relevant partitions are scanned, significantly optimizing query performance.

Connection Pooling & Concurrency

PostgreSQL’s process-per-connection model can limit scalability. Connection poolers like PgBouncer drastically reduce backend process count, optimize resource use, and increase throughput.
Use transaction pool mode for best scaling.

PgBouncer Example:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 100

Replication & High Availability

Replication is a cornerstone of PostgreSQL high availability (HA), ensuring data durability and continuous operation even in the event of hardware or software failures.
High availability setups minimize downtime, maintain service continuity, and distribute workload across primary and standby nodes. PostgreSQL supports multiple replication methods, with streaming replication being the most common for real-time physical replication and logical replication for fine-grained data replication. For detailed steps and best practices, see my previous blog.

Best Practices for High Availability Replication:

• Security: encrypt replication traffic using SSL and limit replication access.

• Use a combination of synchronous and asynchronous replications to balance safety and performance.

• Monitor replication lag to ensure standbys are sufficiently caught up to avoid stale reads.

• Enable wal_compression to reduce bandwidth usage for WAL transfers.

• Regular backups and restore drills remain essential as replication is not a backup.
  

Monitoring Replication Lag:

SELECT pid, state, sent_lsn, replay_lsn, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;

pid  |  state    | sent_lsn | replay_lsn |  lag
------+-----------+----------+------------+----------
3242 | streaming | 6/30DA8  | 6/30DA0    | 2 MB

Monitoring & Observability

Observability is pivotal for performance tuning and system health. PostgreSQL provides rich system views for monitoring resource usage, query performance, locking, replication, and I/O.

Without proper observability, tuning is guesswork. Metrics guide where bottlenecks arise and validate tuning impact. Good monitoring is proactive, surfacing issues before they affect users.

Key Monitoring Areas:

• Query performance: Use extensions like pg_stat_statements or pg_stat_monitor to analyze slow or expensive queries.
• Cache hit ratio: High cache hit signifies efficient memory usage.
• Locks and waits: Detect contention causing query waits that degrade performance.
• Replication health: Monitor lag and WAL shipping to validate HA status.
• Autovacuum activity: Ensures table bloat is controlled through timely cleanup.
• I/O statistics: Using pg_stat_io (PostgreSQL 16+) to check disk read/write intensity by backend type.

Cache Hit Ratio Query Example:

SELECT datname, blks_read, blks_hit,
ROUND(100.0 * blks_hit / (blks_hit + blks_read), 2) AS cache_hit_ratio
FROM pg_stat_database;

 datname  | blks_read | blks_hit | cache_hit_ratio 
----------+-----------+----------+-----------------
 mydb     |    11049  |  424921  |     97.47
 eventdb  |     4093  |   60432  |     93.65
 postgres |     3021  |   20124  |     86.96

Autovacuum Monitoring Query:

SELECT relname, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC
LIMIT 5;
    relname     |   last_autovacuum    | last_autoanalyze
----------------+---------------------+---------------------
 event_log      | 2025-11-10 17:41:00 | 2025-11-10 17:42:00
 user_login     | 2025-11-10 15:12:00 | 2025-11-10 15:15:21

Conclusion

PostgreSQL is an incredibly powerful and reliable database engine but out of the box, it’s tuned for safety and compatibility, not performance. Real-world systems demand more.

By tuning critical areas such as memory, WAL, checkpoints, queries, and autovacuum, you can unlock PostgreSQL’s full potential. The difference between a default setup and a tuned system can be dramatic queries that once took seconds can return in milliseconds, and heavy write workloads can run smoothly without I/O bottlenecks.

Performance tuning isn’t a one-time task it’s a continuous process of measure, test, and adjust. Tools like pg_stat_statements, EXPLAIN (ANALYZE), and monitoring platforms like Percona Monitoring and Management (PMM) make this process easier and data-driven.

Finally, remember that every workload is different. What works for analytics may not fit OLTP systems. Start with the fundamentals, benchmark regularly, and grow your configuration as your data scales.

When tuned right, PostgreSQL doesn’t just perform well, it performs like a database built for the future.

For further reading, refer to the PostgreSQL Official Performance Tips documentation.