PostgreSQL is renowned for ACID compliance, reliability, and high throughput, but out-of-the-box defaults are conservative. Achieving peak performance requires tuning both the database engine parameters and the underlying Linux operating system kernel.
Part 1: Linux OS Kernel Tuning with Tuned
Modern Linux distributions use tuned to dynamically manage system profiles, CPU governors, and virtual memory parameters without manual, error-prone edits to /etc/sysctl.conf.
1. Install and Enable Tuned
dnf -y install tuned
systemctl enable --now tuned2. Create a Dedicated PostgreSQL Profile
Default profiles (like virtual-guest or power-saving modes) throttle CPU frequency and mismanage dirty buffer flushing. Create a custom profile tailored for database workloads:
mkdir -p /etc/tuned/postgresql-custom
cat << 'EOF' > /etc/tuned/postgresql-custom/tuned.conf
[main]
summary=Tuned profile optimized for PostgreSQL workloads
[bootloader]
cmdline=transparent_hugepage=never
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
[sysctl]
# Reduce swappiness to keep DB pages in RAM
vm.swappiness = 10
# Flush dirty memory pages to disk continuously to avoid checkpoint I/O spikes
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
# Memory allocation & networking
vm.overcommit_memory = 0
net.ipv4.tcp_timestamps = 0
EOF3. Activate the Profile
tuned-adm profile postgresql-custom
tuned-adm activePart 2: Core PostgreSQL Configuration (postgresql.conf)
1. Memory Allocation
shared_buffers: Set to 25% of total system RAM for dedicated database servers.effective_cache_size: Estimate how much memory is available for disk caching by the OS and PostgreSQL (typically set to 50% to 75% of total RAM).work_mem: Memory allocated per sorting operation or complex hash table (start conservatively at32MB–64MB). Watch logs for temporary file creation:work_mem = 64MBmaintenance_work_mem: Memory used for maintenance operations such asVACUUM,CREATE INDEX, and foreign key checks. Set to ~10% of system RAM (up to1GB–2GB):maintenance_work_mem = 1GB
2. Structured Logging
PostgreSQL logging is lightweight and vital for identifying slow queries, checkpoint stalls, and temp file writes:
logging_collector = on
log_destination = 'csvlog'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 250ms
log_temp_files = 0
log_checkpoints = onPart 3: Schema Design & Query Anti-Patterns
- Don't Use the DB as a Queue or Cache: Avoid putting ephemeral task queues (like Celery), session data, or frequent heartbeat counters in transactional tables.
- Split Hot and Cold Columns: If a table contains frequently modified fields (e.g.,
last_active_at) alongside wide, static data, split the hot column into a separate 1-to-1 table to minimize table bloat and write amplifications. - Avoid Large
IN (...)Clauses: Passing massive lists insideINqueries prevents efficient index scans; join against a temporary table or useunnest()with an array parameter instead. - Beware of Unanchored Wildcards: Queries using
ILIKE '%pattern%'cannot use standard B-Tree indexes. Use Trigram (pg_trgm) GIN/GiST indexes for partial text search.
Part 4: Maintenance, Indexing & Replication
1. Monitor Table Bloat and Index Usage
Check for sequential scans and unused indexes using built-in statistics views:
-- Identify tables with heavy sequential scans
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC LIMIT 10;2. Safe Schema Migrations on Large Tables
Adding a column with a default value or adding non-concurrent indexes will take an exclusive table lock (ACCESS EXCLUSIVE), blocking all read and write traffic:
- Always build indexes concurrently:
CREATE INDEX CONCURRENTLY ... - Add nullable columns first, populate data in batches, and add constraints subsequently.
3. Backups vs. High Availability
pg_dump: Ideal for logical exports and development copies, but becomes too slow for multi-hundred gigabyte databases.- Physical Streaming Replication & WAL Archiving: Use tools like
pgBackRestor native WAL shipping to maintain byte-for-byte read replicas and enable point-in-time recovery (PITR).
