Tune PostgreSQL configuration for performance

Choose PostgreSQL parameters for tuning

You can set PostgreSQL parameters in postgresql.conf, with ALTER SYSTEM, or for a database, role, or session when the parameter supports that scope. For persistent tuning, use a configuration method that can be reviewed, version controlled, and applied consistently when PostgreSQL restarts. See the PostgreSQL configuration settings documentation for details.

Connections

The max_connections parameter sets the maximum number of concurrent connections PostgreSQL accepts. It does not directly improve query performance.

    

        
        
# Example high connection limit. Size this for your expected peak connection count.
max_connections = 1000

    

Each PostgreSQL connection uses memory and operating system resources. Size this value for expected peak demand, and review the kernel resource limits when you increase it. When an application needs many client connections, use a connection pooler such as PgBouncer to reuse a smaller number of PostgreSQL server connections. This can reduce memory use and process overhead, but it does not make individual queries faster.

Memory configuration

Shared buffers

The shared_buffers parameter controls the shared memory cache that PostgreSQL uses for table and index data.

For a dedicated database server with at least 1 GB of memory, set shared_buffers to 25% to 40% of system memory as a starting range. PostgreSQL also relies on the operating system page cache, so values above 40% are unlikely to help. Larger shared_buffers settings often need a corresponding increase in max_wal_size.

    

        
        
# Example for a dedicated server with 128 GiB of memory.
shared_buffers = 32GB

    

Query and maintenance memory

The work_mem parameter is the base memory limit for each sort or hash operation before PostgreSQL writes temporary files. A single complex query can use multiple operations, and many sessions can run them concurrently. Treat increases as a measured change, not a global performance setting.

    

        
        
# Example values. Test with realistic query concurrency.
work_mem = 32MB
maintenance_work_mem = 2GB

    

The maintenance_work_mem parameter controls memory for maintenance operations such as VACUUM and CREATE INDEX. A larger value can improve maintenance performance, especially for databases with frequent updates or deletes, but account for concurrent autovacuum workers. Set autovacuum_work_mem separately if you need to limit each worker.

Huge pages

The huge_pages setting requests huge pages for PostgreSQL shared memory. The default value, try, falls back to regular pages if allocation fails. Set it to on when you want PostgreSQL to fail at startup instead of using regular pages.

    

        
        
huge_pages = on

    

Huge pages must also be configured at the Linux kernel level. For setup instructions and sizing guidance, see the system, kernel, compiler, and library settings section of this Learning Path.

Write-ahead log and durability configuration

PostgreSQL uses the write-ahead log (WAL) to protect committed data and support crash recovery. The following settings affect checkpoint behavior and transaction durability.

Checkpoint and WAL capacity

The max_wal_size parameter is a soft limit on WAL size during automatic checkpoints. Increasing it can reduce checkpoint frequency and smooth write I/O for write-heavy workloads.

Under sustained high load, WAL can fill quickly enough to trigger frequent checkpoints and checkpoint warnings. Increasing max_wal_size gives PostgreSQL more WAL space before a checkpoint is required. The following value is an example, not a universal recommendation:

    

        
        
# Example for a write-heavy workload. Test against storage capacity and recovery requirements.
min_wal_size = 1GB
max_wal_size = 20GB

    

The min_wal_size parameter keeps a minimum amount of WAL available for recycling. Increasing it can help absorb bursts of WAL activity and reduce the need to create new WAL files during a spike.

Larger WAL settings use more storage and can increase crash recovery time. PostgreSQL logs a checkpoint warning when WAL-triggered checkpoints occur more often than checkpoint_warning, which can indicate that max_wal_size is too small for the workload.

Transaction durability settings

Use the following setting when transaction durability is required. It is the PostgreSQL default:

    

        
        
# Keep the default when transaction durability is required.
synchronous_commit = on

    

The synchronous_commit parameter controls whether PostgreSQL waits for WAL records to reach durable storage before reporting commit success to the client.

When synchronous_commit is on, PostgreSQL prioritizes transaction durability over peak write throughput. Setting synchronous_commit=off lets PostgreSQL acknowledge commits before the WAL data is flushed to durable storage. This can improve parallel transaction execution and write throughput, especially for write-heavy workloads, but it increases the time window where acknowledged transactions exist only in memory or operating system cache.

Query planner configuration

The PostgreSQL query planner selects an execution plan using table and index statistics, along with cost estimates for operations such as sequential scans and index scans. Use EXPLAIN and EXPLAIN ANALYZE with representative queries to understand a plan before changing planner-related settings.

Effective cache size

The effective_cache_size parameter tells the planner how much memory it can expect to be available for caching PostgreSQL data. Set it to an estimate of the total cache available to PostgreSQL, including shared_buffers and the operating system page cache. It does not allocate memory or reserve page cache.

For a dedicated PostgreSQL server, 80% of total memory is a useful starting estimate. Use free while PostgreSQL is running to understand page-cache use, then refine the value for the memory available to PostgreSQL and the expected number of concurrent queries.

When the page cache is large, data that misses shared_buffers is more likely to be read from memory instead of storage. A larger effective_cache_size makes index scans look less expensive to the planner, while a smaller value makes sequential scans more likely. This can help the planner choose an index scan when the index is likely to be cached, rather than assuming the access requires a slow storage read.

Random page cost

The random_page_cost parameter estimates the relative cost of a non-sequential page read. The default is 4.0. Lowering it relative to seq_page_cost makes index scans more likely; raising it makes them less likely.

The default can be a better fit for storage with a high random-read penalty, such as hard disks. On SSD-backed systems, or when a large part of the database is likely to be cached, a lower value such as 1.1 can be worth testing. Storage latency, cache behavior, and network latency all affect the right value, so compare the resulting plans and execution times with representative queries instead of applying a fixed value to every system.

random_page_cost and effective_cache_size both influence how the planner values index scans, but from different directions. effective_cache_size estimates how likely the data is to be cached. random_page_cost estimates the penalty when PostgreSQL has to access a page that is not already in cache.

Parallel execution and I/O configuration

PostgreSQL uses worker processes for parallel queries, maintenance, and extensions. The max_worker_processes setting is the overall limit. The max_parallel_workers setting limits workers available to parallel operations, and must not exceed max_worker_processes.

Set max_worker_processes and max_parallel_workers high enough to allow useful parallel work, but leave CPU capacity for connection handling, background processes, and other applications. A starting point for a dedicated test system is the number of available logical CPUs. PostgreSQL can launch fewer workers than the configured limits when capacity is unavailable.

Use max_parallel_workers_per_gather and max_parallel_maintenance_workers to limit workers used by an individual query or maintenance operation. A value of 4 is a reasonable test starting point for each on a sufficiently large system. Increase the values only when suitable queries or maintenance operations have unused CPU capacity. More workers can increase coordination overhead or reduce performance when CPU, memory bandwidth, or storage is already saturated.

The effective_io_concurrency parameter controls how many storage I/O operations a PostgreSQL session expects to issue in parallel. A value in the low hundreds, such as 300, can be a useful starting point for high-IOPS SSD or cloud storage. Higher values can help high-latency storage with enough I/O capacity, but unnecessarily high values can increase I/O latency. Its default has changed across PostgreSQL versions, so check the value on the version you deploy before overriding it.

What you’ve learned

You’ve explored PostgreSQL parameters that affect connection handling, memory use, WAL behavior, query planning, and parallel execution.

Use the guidance in this Learning Path to design measured tuning experiments for your PostgreSQL workload on Arm.

Next, use the HammerDB TPROC-C workflow to measure the effect of your tuning changes.