Master PostgreSQL Performance: From Hardware to Query Tuning

Hardware Resources

Insufficient CPU, memory, or I/O leads to performance degradation. Monitor run‑queue length versus CPU core count, IOPS, and latency to ensure the hardware can sustain the workload.

Operating System Configuration

Adjust sysctl parameters for optimal PostgreSQL performance. Example settings:

vm.swappiness = 1
vm.dirty_background_ratio = 10
vm.dirty_ratio = 40
vm.dirty_expire_centisecs = 3000
vm.dirty_writeback_centisecs = 500
kernel.shmmax = 18446744073692700000
kernel.shmall = 18446744073692700000
kernel.shmmni = 4096
kernel.sem = 250 512000 100 2048
fs.file-max = 312139770
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 2048 65499
net.ipv4.tcp_tw_reuse = 1
net.core.netdev_budget = 1024
net.core.netdev_max_backlog = 2048
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.panic_on_oops = 1
kernel.numa_balancing = 0
net.core.somaxconn = 4096
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_window_scaling = 1
net.netfilter.nf_conntrack_max = 250000
net.ipv4.tcp_max_syn_backlog = 4096

Filesystem Configuration

Use journaling filesystems such as XFS or EXT4 with logging enabled. Apply mount options like nobarrier, noatime, nodiratime, and ensure proper sector alignment. Separate WAL, data files, and tablespaces to isolate hot and cold data.

SQL and Schema Optimization

Optimize queries, create appropriate indexes, avoid excessive single‑column indexes, and limit costly joins.

Design tables to prevent over‑normalization, use partitioning wisely, and consider TimescaleDB for time‑series workloads.

PostgreSQL Parameter Tuning

Set shared_buffers, work_mem, maintenance_work_mem, checkpoint_segments, wal_buffers, and related parameters according to the workload.

Adjust SHARED_BUFFERS and WAL writer settings to suit the double‑buffering mechanism.

Concurrency Control

Use the appropriate transaction isolation level (typically read committed) and avoid unnecessary changes.

Configure max_worker_processes, max_parallel_workers, max_parallel_maintenance_workers, and max_parallel_workers_per_gather to match hardware capabilities.

Beware of setting these values too low (wasting resources) or too high (causing latency).

Cache Hit Rate

Low cache hit rates increase disk I/O; tune shared_buffers and related settings to improve buffer efficiency.

Cold Data Access

Data not present in shared buffers or OS cache incurs high I/O latency; consider tiered storage strategies for cold data.

Automation Task Conflicts

Concurrent backup, VACUUM, and bulk‑load jobs can clash; schedule them separately or stagger their execution to prevent performance degradation.