DEV Community

AJAYA SHRESTHA
AJAYA SHRESTHA

Posted on

PostgreSQL Database Tuning Guide

#postgres #database #webdev #devops

PostgreSQL is a powerful, open-source relational database management system renowned for its stability, versatility, and efficiency. Optimizing PostgreSQL can dramatically improve your database performance.
Let's Optimize PostgreSQL for a Server with:

  • 4 Core CPU
  • 4 GB RAM
  • 100 GB SSD

Why Tune PostgreSQL?

The default PostgreSQL configuration is conservative and intended to run safely on almost any hardware. To get maximum performance from your database, you must tailor PostgreSQL's settings to your specific hardware and workload. Correctly tuning your database can significantly improve read/write operations, reduce latency, and improve query performance.

Step 1: Edit PostgreSQL Configuration File

Open your PostgreSQL config file. Usually found at:

# Adjust according to your installation path
sudo vi /etc/postgresql/16/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Step 2: Update These Settings

Paste these recommended settings directly into your postgresql.conf file:

# Recommended PostgreSQL Settings (for 4GB RAM, 4-core, SSD)

# Connections
max_connections = 200

# Memory
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
work_mem = 5140kB

# WAL & Checkpoint settings
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB

# Query Planning
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200

# Parallelism & Workers
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

# Huge Pages
huge_pages = off
Enter fullscreen mode Exit fullscreen mode

Step 3: Restart PostgreSQL

Save your changes and restart PostgreSQL with the following command:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Understanding Key Parameters

Here's a breakdown of essential PostgreSQL parameters:

  • max_connections = 200 Number of simultaneous database connections allowed. Limits the number of concurrent database connections. Higher values need more RAM. 200 is suitable for medium workloads.
  • shared_buffers = 1GB Memory PostgreSQL uses to cache data in RAM. Typically, 25-40% of total RAM is a good rule.
  • effective_cache_size = 3GB PostgreSQL's query planner uses this to estimate available OS cache. Estimate of memory available for disk caching (PostgreSQL + OS cache combined).
  • maintenance_work_mem = 256MB Memory for maintenance tasks (vacuuming, indexing). More memory allows these operations to run faster.
  • work_mem = 5140kB (~5MB) Memory allocated for each sorting and hashing in query operations. Small enough for many parallel queries. Set higher if you expect large joins or sorts; avoid excessive temporary disk usage.
  • checkpoint_completion_target = 0.9 Controls how evenly the checkpoint writes are spread out. 0.9 reduces I/O spikes by spreading writes over more time.
  • wal_buffers = 16MB Temporary storage for WAL (Write-Ahead Log) data before it's written to disk. A higher value can improve write performance.
  • min_wal_size = 1GB and max_wal_size = 4GB Controls frequency of checkpoints (log flushing). Controls WAL file disk space usage. Helps balance between disk usage and how often checkpoints occur. Balanced values help SSD lifespan.
  • default_statistics_target = 100 Affects the quality of table statistics. Higher values mean more accurate query plans, but slower ANALYZE times.
  • random_page_cost = 1.1 Tells the planner the cost of reading a random disk page. SSDs handle random access quickly. Lower value tells PostgreSQL to optimize accordingly.
  • effective_io_concurrency = 200 Indicates how many concurrent I/O operations the system can handle. Higher is better for SSDs or fast storage. SSDs manage multiple I/O operations simultaneously.
  • max_worker_processes = 4 The total number of background worker processes PostgreSQL can run.
  • max_parallel_workers_per_gather = 2 Max parallel workers for a single parallel query (gather node). Controls parallel query execution.
  • max_parallel_workers = 4 The total parallel workers that can be running across all queries.
  • max_parallel_maintenance_workers = 2 Max workers for parallel maintenance tasks like CREATE INDEX.
  • huge_pages = off Whether to use huge pages (larger memory pages for better performance). Off by default, useful in high-performance setups. Keep off on small RAM systems (4GB or less).

With the right tuning, PostgreSQL can deliver significantly better performance tailored to your server’s resources and workload. The configuration outlined above is a solid starting point that optimizes memory usage, connection handling, query planning, and parallelism.

However, database performance tuning is not a one-time task. It’s essential to continuously monitor key performance metrics such as CPU usage, disk I/O, query times, and cache hit ratios, to ensure your settings remain effective as your data grows or your application load changes.

Be prepared to adjust configurations as needed, based on real-world usage and evolving demands. Regularly revisiting your PostgreSQL settings will help maintain peak performance and application responsiveness over time.

Top comments (0)