Performance Tuning Key Parameters for PostgreSQL

Tuning PostgreSQL for optimal performance requires configuring several parameters in the postgresql.conf file. The most critical parameters depend on factors such as the hardware setup, workload, and the type of queries being executed (OLTP vs OLAP). Below are some of the most important configuration parameters that you would typically tune for performance:

---

1. Memory-Related Settings

1.1 shared_buffers

• Description: This parameter defines how much memory PostgreSQL will allocate for caching data in memory. It is one of the most important settings because it determines how much data can be cached in PostgreSQL’s memory rather than reading from disk.
• Default: Typically low (e.g., 128MB).
• Tuning Recommendation: Set this to approximately 25-40% of the total system memory.
• For example, on a server with 16GB of RAM, you might set this to 4GB or 6GB.
• Formula:

  shared_buffers = 25% to 40% of total RAM

1.2 work_mem

• Description: Memory used for operations such as sorting and hashing within a query. Each operation within a query can allocate its own work_mem, so it’s essential to set this based on the complexity and number of concurrent queries.
• Default: Low (e.g., 4MB).
• Tuning Recommendation: Increase this based on the needs of the workload. For complex queries or large joins, increase work_mem per query. Be cautious not to set this too high because it’s allocated per operation, per query.
• Example setting: 64MB or higher for complex workloads, but needs to be balanced carefully if you have a lot of concurrent queries.
• Formula:

  work_mem = (Total RAM - shared_buffers) / (Max number of concurrent connections * number of operations per query)

1.3 maintenance_work_mem

• Description: This memory is used for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE. Since these operations are less frequent but potentially more resource-intensive, this can be set higher than work_mem.
• Default: 64MB.
• Tuning Recommendation: Set to 512MB or more if frequent VACUUM or CREATE INDEX operations are expected.
• Example setting: 512MB – 2GB, depending on available resources.

---

2. I/O and Disk-Related Settings

2.1 effective_cache_size

• Description: This parameter represents the estimated size of the operating system’s file system cache available to PostgreSQL. It is not memory allocated directly by PostgreSQL, but rather the memory PostgreSQL expects the OS to use for caching disk pages. This setting helps the query planner make more accurate decisions.
• Default: Varies.
• Tuning Recommendation: Set to around 50-75% of total system memory.
• Example: On a server with 16GB of RAM, you might set this to 12GB or 14GB.
• Formula:

  effective_cache_size = 50% to 75% of total RAM

2.2 checkpoint_completion_target

• Description: Controls how PostgreSQL writes data to disk by spreading the work of writing dirty pages to disk over time. It helps avoid sudden performance drops by distributing disk I/O.
• Default: 0.5.
• Tuning Recommendation: Set this to 0.7 or 0.9 to smooth out disk I/O and avoid sudden spikes in write operations.
• This tells PostgreSQL to aim to complete 70% to 90% of the checkpoint work before the next checkpoint, spreading out the load.

2.3 wal_buffers

• Description: This parameter defines the amount of memory allocated for the Write-Ahead Log (WAL) buffers, which store transactional data before writing it to disk. Sufficiently large wal_buffers can help with reducing WAL-related I/O.
• Default: 16MB.
• Tuning Recommendation: Set this to at least 16MB to 32MB or higher, depending on your workload.
• Example setting: 16MB to 64MB for high-write environments.

2.4 checkpoint_segments / max_wal_size

• Description: Controls the size and frequency of checkpoints. More frequent checkpoints can slow down writes, but too infrequent checkpoints increase recovery time after a crash. checkpoint_segments has been replaced by max_wal_size in PostgreSQL 9.5+.
• Tuning Recommendation:
• For PostgreSQL 9.4 and below: Increase checkpoint_segments from the default (3) to 16 or 32.
• For PostgreSQL 9.5+: Increase max_wal_size from the default to 1GB to 2GB.

---

3. Connection-Related Settings

3.1 max_connections

• Description: Controls the maximum number of concurrent connections to the database. This is a critical setting for environments with a lot of users or services that connect to the database.
• Default: 100.
• Tuning Recommendation: Increase this based on the expected number of concurrent connections, but be careful not to over-allocate. Too many active connections can cause resource contention.
• For high workloads, you might set this to 200–500, depending on server resources.
• In high-traffic environments, consider connection pooling (e.g., using pgbouncer).

3.2 max_worker_processes

• Description: Specifies the maximum number of background worker processes. This is useful for parallel query execution and other background tasks.
• Default: 8.
• Tuning Recommendation: Increase this if your workload involves parallelism (e.g., parallel queries or background workers).
• Example setting: 16 or more for complex queries with parallelism.

---

4. Write-Ahead Logging (WAL) Settings

4.1 wal_level

• Description: Controls the level of information written to the WAL. Higher levels provide more information for replication or point-in-time recovery, but they also generate more I/O.
• Options:
• minimal (minimal logging, no replication),
• replica (for read-replica setups),
• logical (for logical replication).
• Tuning Recommendation: Set this to replica if using streaming replication or logical for logical replication. Keep it at minimal for non-replicated setups to reduce write overhead.

4.2 synchronous_commit

• Description: Determines when PostgreSQL considers a transaction committed. If set to on, PostgreSQL waits for WAL data to be written to disk before reporting success. If set to off, PostgreSQL reports success after writing to the WAL buffers, without waiting for the data to be flushed to disk.
• Default: on.
• Tuning Recommendation:
• For performance-critical systems where slight data loss can be tolerated (e.g., some OLAP systems), you can set this to off or local to reduce transaction latency.
• For critical data, keep it as on.

---

5. Autovacuum Settings

5.1 autovacuum_max_workers

• Description: Controls the maximum number of autovacuum workers that can run concurrently. Autovacuum helps clean up dead tuples and maintain healthy performance, but it can create I/O contention if too aggressive.
• Default: 3.
• Tuning Recommendation: Increase to 5 or more if there is heavy write activity that generates many dead tuples.

5.2 autovacuum_vacuum_cost_limit

• Description: Limits the amount of work done by autovacuum to prevent it from consuming too many resources.
• Default: 200.
• Tuning Recommendation: Increase this to 500 or 1000 if autovacuum is too slow in cleaning up dead tuples in a write-heavy environment.

---

6. Parallel Query Settings

6.1 max_parallel_workers_per_gather

• Description: Controls how many worker processes are used for parallel queries.
• Default: 2.
• Tuning Recommendation: Increase this to 4 or more for systems that perform complex, large analytical queries.
• Example setting: 4 or 8 for OLAP workloads.

---

Summary of Key Parameters

Parameter	Description	Tuning Recommendation
shared_buffers	Memory for data caching	25-40% of system RAM
work_mem	Memory per query operation	4MB-64MB (or more, depending on query complexity)
effective_cache_size	Estimated OS cache size	50-75% of system RAM
maintenance_work_mem	Memory for maintenance tasks	512MB – 2GB
checkpoint_completion_target	Smoothing out checkpoint I/O	0.