High-Performance PostgreSQL: A Guide to Kernel Tuning, Config Optimization, and Robust Backups

Optimizing a database isn't just about tweaking a few settings in postgresql.conf. True performance and reliability come from a holistic approach that starts at the operating system level, moves up through the database configuration, and ends with a rock-solid backup strategy.

In this guide, we'll walk through the specific optimizations we've applied for a PostgreSQL 16 instance running a NestJS workload on a server with 6GB RAM, 2 vCPUs, and SSD storage.

Part 1: Host & Kernel Optimization

Before PostgreSQL even starts, the Linux host needs to be tuned to support a database workload. Default Linux settings are often general-purpose and can lead to I/O spikes or memory issues under load.

1. Swappiness (vm.swappiness)

We set vm.swappiness to 10 (default is often 60).

• Why: We want the kernel to prefer keeping application memory in RAM rather than swapping it to disk. For a database, swapping is a performance killer.
• Reference: PostgreSQL Linux Kernel Resources

2. Dirty Memory Settings

• vm.dirty_background_ratio = 5
• vm.dirty_ratio = 15
• Why: These lower values force the kernel to flush dirty data to disk more frequently in smaller chunks, preventing the "stall" that happens when the kernel has to flush a massive amount of data at once. This smooths out I/O performance.

3. Transparent Huge Pages (THP)

We configure THP to madvise.

• Why: PostgreSQL manages its own memory efficiently. The default always setting for THP can cause high CPU usage during memory compaction. madvise allows PostgreSQL to use huge pages explicitly where beneficial without the overhead.
• Reference: PostgreSQL Documentation on Huge Pages

4. File Limits & Shared Memory

• Ulimits: Increased open file limit (nofile) to 10240 for the postgres user to handle high concurrency.
• Shared Memory: Set kernel.shmmax and kernel.shmall to cover the full 6GB RAM. This ensures the OS allows PostgreSQL to allocate the shared memory segments it needs.

Part 2: PostgreSQL 16 Configuration Tuning

With the host ready, we tune postgresql.conf specifically for our hardware constraints (6GB RAM, 2 vCPUs) and workload (NestJS with TypeORM).

1. Memory Allocation

• shared_buffers = 1536MB (25% of RAM)
  • This is the dedicated memory for PostgreSQL's cache.
• work_mem = 8MB
  • Calculated for ~200 concurrent connections. This controls memory for sorts and hash tables per operation.
• maintenance_work_mem = 256MB
  • Higher memory allocated specifically for maintenance tasks like VACUUM and CREATE INDEX.
• Reference: PostgreSQL Resource Consumption

2. Checkpoints & Write-Ahead Log (WAL)

• checkpoint_completion_target = 0.9
  • Spreads the checkpoint writes over 90% of the interval, significantly reducing I/O spikes.
• min_wal_size = 1GB / max_wal_size = 4GB
  • Sized to accommodate our transaction volume without excessive recycling.
• Reference: WAL Configuration

3. Storage Optimization (SSD)

• random_page_cost = 1.1
  • Tells the query planner that random seeks on our SSD are almost as cheap as sequential reads, encouraging index usage.
• effective_io_concurrency = 200
  • Allows PostgreSQL to issue multiple concurrent I/O requests, taking full advantage of SSD parallelism.

4. Autovacuum Tuning

• autovacuum_vacuum_scale_factor = 0.02
• autovacuum_analyze_scale_factor = 0.01
• Why: Standard settings often trigger autovacuum too late. These aggressive settings ensure dead tuples are cleaned up frequently, preventing table bloat in our high-transaction environment.

Part 3: Robust Backups with pgBackRest

Performance means nothing without data safety. We use pgBackRest for its reliability, S3 support, and Point-In-Time Recovery (PITR) capabilities.

1. Architecture

We configure a Stanza (cluster definition) that pushes backups directly to an S3-compatible object storage (e.g., Hetzner, AWS).

• Repo Type: S3
• Encryption: Enabled (via environment variables for keys)

2. Retention Policy

• Full Backups: Retain last 3
• Differential Backups: Retain last 3
• Strategy: Weekly Full backups + Daily Differential backups ensure we can restore quickly without storing excessive data.

3. Disaster Recovery

• Point-in-Time Recovery (PITR):

  pgbackrest --stanza=main --type=time --target="2023-11-23 14:30:00" restore
  

  This allows us to roll back the database to a precise second before a catastrophic event (like an accidental DROP TABLE).

• Reference: pgBackRest User Guide

Conclusion

By optimizing the kernel for database workloads, tuning PostgreSQL for specific hardware constraints, and implementing pgBackRest for enterprise-grade backups, we create a database environment that is not only fast but resilient. This setup ensures our NestJS application remains responsive under load while guaranteeing data safety.