This blog was originally published in August 2018 and was updated in May 2023.

Out of the box, the default PostgreSQL configuration isn’t tuned for any specific workload. Default values prioritize compatibility and minimal resource usage over peak performance. Therefore, PostgreSQL performance tuning is a crucial responsibility for database administrators and developers to tailor the database to their system’s workload and hardware. In this blog post, we’ll explore the world of PostgreSQL performance tuning, focusing on establishing guidelines for setting key database parameters to enhance performance.

What is PostgreSQL performance tuning? (And key areas)

PostgreSQL performance optimization aims to improve the efficiency and responsiveness of your database system. This involves adjusting configurations, optimizing queries, and implementing best practices to resolve bottlenecks and maximize throughput.

Key areas often addressed during PostgreSQL performance tuning include:

  • Configuration Parameter Tuning: Adjusting settings in postgresql.conf related to memory allocation (shared_buffers, work_mem), disk I/O, WAL management, and concurrency based on hardware and workload. (This post focuses heavily on this area).

  • Query Optimization: Analyzing slow queries using EXPLAIN, improving them through better indexing strategies, rewriting inefficient SQL, or using advanced query features.

  • Index Tuning: Selecting appropriate index types (B-tree, GIN, GiST, Hash), creating effective composite indexes, and maintaining indexes (analyzing, reindexing).

  • Hardware Optimization: Ensuring CPU, RAM, and storage subsystems (SSDs recommended) meet workload demands.

  • Monitoring and Statistics: Using PostgreSQL statistics and tools like Percona Monitoring and Management (PMM) to identify bottlenecks, track performance trends, and validate tuning efforts.

  • Schema Design: Optimizing table structures, data types, partitioning large tables, and appropriate normalization/denormalization.

  • Connection Pooling: Using external connection poolers (like PgBouncer) to manage connections efficiently and reduce overhead.

  • Replication and Load Balancing: Scaling reads using read replicas or distributing load across multiple servers.

PostgreSQL performance optimization is an ongoing process involving monitoring, benchmarking, and adjustments to maintain high-performing PostgreSQL databases. In addition, staying up-to-date on PostgreSQL releases and improvements can also help.

Why is PostgreSQL performance tuning important?

Optimizing your PostgreSQL database significantly impacts application effectiveness and business outcomes:

  • Responsiveness & User Experience: Slow queries lead to frustrating delays. Tuning ensures quick response times, improving user satisfaction.

  • Throughput: Faster query execution allows the application to handle more concurrent requests, improving overall throughput.

  • Scalability: A well-tuned database handles increasing data volumes and user loads more effectively, ensuring the application remains performant as it grows.

  • Efficient Resource Utilization: Optimization ensures better use of CPU, memory, and I/O, potentially leading to cost savings on hardware or cloud resources.

  • Data Integrity & Reliability: While not direct tuning, a healthy, performant database often correlates with better stability and easier maintenance, supporting data integrity features like ACID compliance.

  • Competitive Advantage: Superior application performance driven by a tuned database enhances user retention and provides a competitive edge.

PostgreSQL Enterprise

The critical impact of queries on PostgreSQL performance

While optimizing PostgreSQL server configuration is vital, poorly written queries can negate tuning efforts. A database developer must be diligent:

  • Avoid full table scans where an index could efficiently locate data.

  • Minimize complex or unnecessary joins.

  • Optimize expensive aggregate operations.

  • Use EXPLAIN and EXPLAIN ANALYZE to understand query plans.

Even with perfectly tuned parameters, inefficient queries will lead to poor performance. Query optimization is paramount.

Ensure your databases are performing their best — today and tomorrow — with proactive database optimization and query tuning. Book a database assessment

Key PostgreSQL parameters for performance tuning

Now, let’s examine specific PostgreSQL parameters within postgresql.conf that often yield significant performance improvements when adjusted correctly.

shared_buffer

PostgreSQL uses its own memory buffer (shared_buffers) and relies on the operating system’s kernel cache (leading to potential “double buffering”). shared_buffers allocates dedicated memory for PostgreSQL’s data cache. This is often the most impactful parameter to tune.

  • Default: Very low (e.g., 128MB) for broad compatibility.

  • Recommendation: Start with 25% of total system RAM. Benchmark higher or lower values. If your working dataset fits in RAM, increasing shared_buffers significantly can be beneficial, but leave ample memory for the OS and other processes. Large values often improve performance in production, but testing is essential.

  • Caution: Some OS kernels (especially older Windows versions) may have limitations on large shared memory segments.

Note: Be careful as some kernels do not allow a bigger value, specifically in Windows, there is no use of a higher value.

wal_buffers

PostgreSQL writes Write-Ahead Log (WAL) records to these buffers before flushing them to disk.

  • Default: Relatively small (e.g., 16MB).

  • Recommendation: For systems with high write activity or many concurrent connections writing data, increasing wal_buffers (e.g., to 64MB, 128MB, or higher, up to the size of one WAL segment – typically 16MB by default, but check wal_segment_size) may improve write performance by allowing larger WAL writes per flush. Test the impact.

effective_cache_size

This parameter estimates the total memory available for disk caching by both PostgreSQL (shared_buffers) and the operating system kernel cache. It’s a hint for the query planner.

  • Default: Moderate (e.g., 4GB).

  • Recommendation: Set this to a realistic estimate of available caching memory, often 50% to 75% of total system RAM. Setting it too low might cause the planner to incorrectly avoid using indexes it thinks won’t fit in cache. Setting it aggressively high is generally safe as it doesn’t allocate memory.

work_mem

Specifies the amount of memory used for internal sort operations (like ORDER BY, DISTINCT), hash joins, and hash-based aggregation before spilling to temporary disk files. In-memory operations are much faster.

  • Default: Low (e.g., 4MB).

  • Recommendation: Increase this cautiously. Crucially, this memory can be allocated per operation within a query, and multiple operations might run concurrently per backend/connection. Setting it too high globally can quickly exhaust server memory with many concurrent users/queries. Start with modest increases (e.g., 16MB, 32MB, 64MB) and monitor memory usage. Consider setting it higher at the session level (SET work_mem = ‘…’;) for specific, known heavy queries or reporting sessions rather than globally.

  • Example Impact: Increasing work_mem can significantly reduce the estimated cost in EXPLAIN plans for sort-heavy queries, indicating a potential performance gain. 

The initial query’s sort node has an estimated cost of 514431.86. A cost is an arbitrary unit of computation. For the above query, we have a work_mem of only 2MB. For testing purposes, let’s increase this to 256MB and see if there is any impact on cost.

The query cost is reduced to 360617.36 from 514431.86 — a 30% reduction.

maintenance_work_mem

Specifies the maximum amount of memory used for maintenance operations like VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, RESTORE.

  • Default: Moderate (e.g., 64MB).

  • Recommendation: Increase this significantly (e.g., 128MB, 256MB, 1GB or more, depending on available RAM) to speed up these maintenance tasks, especially CREATE INDEX and VACUUM on large tables. Since these tasks are typically run less frequently or during off-peak hours, allocating more memory here is usually safe.

The index creation time is 170091.371ms when maintenance_work_mem is set to only 10MB, but that is reduced to 111274.903 ms when we increase maintenance_work_mem setting to 256MB.

synchronous_commit

Controls whether a transaction commit waits for its WAL records to be written to disk before returning success to the client.

  • Default: on (safest, ensures durability).

  • Options:

    • on: Waits for WAL flush to disk (fully durable).

    • off: Returns success immediately after WAL is written to buffers (fastest, but risks data loss on crash if WAL buffer hasn’t flushed).

    • local: Waits for WAL flush to local disk, but not necessarily to standbys in synchronous replication.

    • remote_write, remote_apply: Used for synchronous replication control.

  • Recommendation: Keep on unless you have specific application requirements where performance outweighs the small risk of losing the very last transactions in a crash scenario. Turning it off provides a significant commit speedup but sacrifices durability guarantees.

checkpoint_timeout, checkpoint_completion_target

Checkpoints are points where all dirty data buffers are flushed from shared memory to disk, ensuring data file consistency. Frequent or poorly managed checkpoints can cause I/O storms.

  • checkpoint_timeout: Maximum time between automatic checkpoints.

    • Default: 5 minutes.

    • Recommendation: Increasing this (e.g., 15-30 minutes or more) spreads out the I/O load but increases crash recovery time. Find a balance based on recovery time requirements and I/O capacity.

  • checkpoint_completion_target: Fraction of time between checkpoints over which the checkpoint flush should be spread out.

    • Default: 0.5 (increased to 0.9 in recent versions).

    • Recommendation: A higher value (e.g., 0.9) spreads the I/O over a longer period, reducing I/O spikes. This is generally preferred on modern systems.

Tuning these together helps smooth out checkpoint I/O. Avoid very frequent checkpoints (checkpoint_timeout too low) combined with a low checkpoint_completion_target, which can cause constant, heavy I/O pressure.

Improve PostgreSQL performance tuning with Percona

While these are some of the most impactful parameters, many others can be tuned. Remember, optimal settings depend heavily on your specific application workload, data size, hardware, and operating system. PostgreSQL performance tuning requires understanding these interactions.

Resources for PostgreSQL performance tuning

If your PostgreSQL database is running on a Linux OS, be sure to read my post about tuning Linux parameters to optimize PostgreSQL database performance.

After tuning your PostgreSQL database to improve its performance, the next step is to put your optimized database to the test. With sysbench, you can quickly evaluate your database’s performance. This process is especially important if you plan to run your database under an intensive workload. Our blog article, Tuning PostgreSQL for sysbench-tpcc, can guide you through the benchmarking process.

See why running open source PostgreSQL in-house demands more time, expertise, and resources than most teams expect — and what it means for IT and the business.

 

PostgreSQL in the Enterprise: The Real Cost of Going DIY

 

PostgreSQL performance tuning FAQs

Q1: What is the most important parameter for PostgreSQL performance tuning?
A: While it depends on the workload, shared_buffers is often considered the most impactful single parameter, as it controls the size of PostgreSQL’s main memory cache for data pages. Correctly sizing it (often 25% of system RAM as a starting point) is crucial.

Q2: How can I improve slow query performance in PostgreSQL?
A: Improving slow queries involves query optimization. Use EXPLAIN ANALYZE to understand the query plan, then focus on index tuning (adding missing indexes, removing unused ones, choosing the right index type), rewriting the query logic for efficiency, ensuring statistics are up-to-date (ANALYZE), and potentially adjusting work_mem for large sorts or joins.

Q3: What does work_mem control in PostgreSQL?
A: work_mem sets the amount of memory PostgreSQL can use for internal operations like sorting (ORDER BY, window functions), hash joins, and hash aggregation before resorting to slower temporary disk files. Tuning it can significantly speed up complex queries but must be done cautiously due to its per-operation allocation.

Q4: Why should I tune checkpoint parameters (checkpoint_timeout, checkpoint_completion_target)?
A: Checkpoints flush dirty data buffers to disk. Tuning these parameters helps manage the I/O impact. Increasing checkpoint_timeout reduces checkpoint frequency (less I/O disruption, longer recovery time). Increasing checkpoint_completion_target spreads the I/O over a longer duration within the timeout period, reducing I/O spikes.

Q5: What tools can help with PostgreSQL performance tuning and monitoring?
A: Built-in tools like EXPLAIN, pg_stat_statements, and various statistics views are essential. External monitoring tools like Percona Monitoring and Management (PMM) provide comprehensive dashboards and query analytics. Benchmarking tools like pgbench or sysbench help test the impact of tuning changes.

Subscribe
Notify of
guest

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
fpuga

I understad that most of this suggestions like using 25% of RAM for shared_buffer is for servers that are only running PostgreSQL and not for servers that also run a web server or other services. Is this true?

0
Reply
Ibrar Ahmed

Most of the tuning advices are for the dedicated “database” server. In case of shared system where you are running database and some other server on a single machine need to be tuned accordingly.

0
Reply
Glyn

That shared_buffer advice isn’t great, especially if applied to modern systems with large volumes of ram.

Generally there’s a tipping point, and it’s much lower than you’d think. A better approach is to use pg_buffercache extension to inspect the system under typical load and tune down.

0
Reply
joshuamills235

In this post you are very thoughtful . I am very happy to read this post. It’s so motivational post for me. I would like to very thankful for this best and most important information. For more info :-https://www.autoupgrades.co.nz/

0
Reply