PostgreSQL Performance Tweak

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 tuned

2. 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
EOF

3. Activate the Profile

tuned-adm profile postgresql-custom
tuned-adm active

---

Part 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 at 32MB–64MB). Watch logs for temporary file creation:

  work_mem = 64MB

• maintenance_work_mem: Memory used for maintenance operations such as VACUUM, CREATE INDEX, and foreign key checks. Set to ~10% of system RAM (up to 1GB–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 = on

---

Part 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 inside IN queries prevents efficient index scans; join against a temporary table or use unnest() 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 pgBackRest or native WAL shipping to maintain byte-for-byte read replicas and enable point-in-time recovery (PITR).

```