PostgreSQL tuning: 10 things you can do to improve DB performance

What is PostgreSQL performance tuning?

PostgreSQL performance tuning is the process of optimizing the database to improve its responsiveness and efficiency. This involves adjusting configuration settings, resolving system bottlenecks, and optimizing resource usage. The goal is to ensure that the database meets performance requirements, often dictated by applications and transactional environments.

Performance tuning identifies areas where alterations can reduce query times and improve throughput. Key focus areas include database settings, system hardware, and SQL query optimization.

Editor’s note: Added 4 new performance tuning techniques relevant for PostgreSQL administration in 2026.

This is part of a series of articles about PostgreSQL

Why is PostgreSQL performance tuning important?

PostgreSQL performance tuning is important because the database’s performance directly affects the overall efficiency of applications that rely on it. A well-tuned PostgreSQL database ensures quick data retrieval and processing, which enhances the user experience by delivering responsive applications. Slow queries can lead to delays that frustrate users, so tuning is necessary to maintain fast response times and meet user expectations.

Performance tuning significantly impacts an application’s throughput, enabling it to handle more concurrent requests by reducing query execution times. As applications grow and handle larger datasets and more users, the ability of PostgreSQL to scale efficiently is crucial. Proper tuning allows the database to manage increased workloads without compromising performance.

Additionally, tuning helps in the efficient utilization of system resources like CPU, memory, and disk I/O. By optimizing these resources, businesses can reduce hardware costs and minimize cloud expenses while maintaining high database performance. Ensuring data integrity through performance tuning is also critical, as it prevents data corruption or loss.

Learn more in our detailed PostgreSQL tutorial

How to tune PostgreSQL performance

1. Database design

Effective database design is crucial for optimizing PostgreSQL performance:

• Normalization: It’s crucial to properly normalize your database schema to ensure data integrity and reduce redundancy. Normalization involves organizing data into tables so that each table focuses on a single subject or concept, eliminating data duplication. However, over-normalization can lead to excessive joins between tables, which may degrade performance.
• Denormalization: To balance normalization with performance, denormalization might sometimes be necessary. Denormalization involves intentionally adding redundancy to speed up query performance, especially for read-heavy databases where complex joins slow down access to data.
• Indexing: Indexes allow PostgreSQL to locate and retrieve data quickly without scanning the entire table. Choosing the right columns to index, particularly those frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements, can significantly improve query performance. However, indexes come with trade-offs: they consume additional storage space and create overhead during data writes.
• Partitioning: This is a way to divide a large table into smaller, more manageable pieces based on a specific criterion, such as a range of dates or a list of values. When a query targets a specific partition, PostgreSQL can skip other partitions, reducing the amount of data scanned and speeding up query execution.

2. Hardware and environment tuning

CPU

PostgreSQL is capable of utilizing multiple CPU cores, particularly for operations like sorting, aggregations, and complex queries:

• Adding cores: To leverage this, a modern, multi-core processor is essential. The more cores available, the more tasks PostgreSQL can execute in parallel, leading to faster query processing. For example, operations such as parallel query execution, index creation, and data aggregation can benefit significantly from additional cores.
• CPU speed: Faster clock speeds improve the performance of single-threaded tasks, which are common in PostgreSQL workloads.
• Hyper-threading: Enabling hyper-threading (if supported) can improve performance by allowing each core to handle multiple threads simultaneously, doubling the number of cores available to PostgreSQL.
• CPU performance tuning: This involves configuring the CPU for performance rather than energy efficiency. In many systems, you can adjust power settings in the BIOS to prioritize performance, ensuring that the CPU runs at higher frequencies under load.

RAM

RAM directly affects the database’s ability to cache data, reducing the need to access slower disk storage. The more RAM available, the more data PostgreSQL can keep in memory, which speeds up read operations. There are two key parameters that control how much RAM PostgreSQL uses for caching:

• Shared_buffers: As a rule of thumb, shared_buffers should be set to about 25% to 40% of the total system RAM, depending on the workload. However, the actual allocation needs to be balanced with the memory requirements of the operating system and other applications running on the same server. If shared_buffers is too small, PostgreSQL will frequently access the disk, leading to slower performance. If it’s too large, it can starve the system of memory, causing the OS to swap memory to disk, which is detrimental to performance.
• Work_mem: This parameter determines the amount of memory allocated for sorting operations and hash tables in queries. For complex queries, increasing work_mem allows PostgreSQL to perform these operations in memory rather than on disk, speeding up query execution. However, it’s essential to avoid setting work_mem too high globally, as it’s allocated per operation, and multiple operations running concurrently can quickly consume all available RAM.

Disk I/O

Disk I/O performance is a critical factor in PostgreSQL, particularly for write-heavy databases where data is frequently written to disk:

• Solid-state drives (SSDs): These offer performance advantages over traditional hard disk drives (HDDs). SSDs have faster read and write times, lower latency, and better throughput, crucial for databases with high transaction rates. The improved performance of SSDs is especially noticeable during operations that involve random reads and writes, such as querying indexes or handling concurrent transactions.
• Using RAID: Redundant array of independent disks (RAID) can enhance disk I/O performance. RAID 10 is often recommended for PostgreSQL because it combines the speed benefits of striping (RAID 0) with the redundancy of mirroring (RAID 1). This configuration improves read and write speeds while providing data redundancy.
• Using multiple disks: Separating different types of PostgreSQL data onto different disks can further improve performance. For example, placing the database’s data files, transaction logs (WAL files), and indexes on separate physical disks or SSDs can prevent I/O contention, where multiple processes compete for the same disk resources. This setup ensures that high-throughput operations, such as writing to WAL files, don’t interfere with other disk operations, like reading data for queries.

Network

Network performance is critical in distributed PostgreSQL environments, particularly when the database server and application servers are on different machines or in different locations. Here are best practices for optimizing network performance:

• Ensuring fast connections: The network connection between database and clients should be as fast and reliable as possible. This might involve upgrading network hardware, such as using gigabit Ethernet or faster, and ensuring that network interfaces are properly configured and not congested.
• Tuning TCP settings: Explore the configurations on the PostgreSQL server. For example, adjusting tcp_keepalives_idle controls how often the server sends TCP keepalive packets to maintain an idle connection.
• Using a dedicated network for database traffic: By isolating database traffic from other network traffic, you can reduce network congestion and improve the consistency of database communication.

3. PostgreSQL connections

Managing the number of active connections to PostgreSQL is crucial for maintaining performance and stability. Each connection to the PostgreSQL server consumes memory and CPU resources, and a high number of concurrent connections can lead to resource exhaustion and degraded performance.

By default, PostgreSQL handles connections on a one-process-per-connection basis, meaning each connection spawns a new process. This design is efficient for a moderate number of connections but becomes problematic when the number of connections grows too large. Here are two ways to mitigate this:

• Use a connection pooler like PgBouncer: PgBouncer sits between your application and PostgreSQL, maintaining a pool of active connections to the database while queuing excess requests.
• Configure maximum number of connections (max_connections): However, it’s important to find a balance. Setting this value too high can lead to excessive context switching and memory usage, while setting it too low might result in connection refusals when the limit is reached.

4. PostgreSQL buffer pool

The PostgreSQL buffer pool, controlled by the shared_buffers setting, is where data is temporarily stored before being written to disk or after being read from disk. Properly tuning the buffer pool size is crucial for optimal performance.

If the buffer pool is too small, PostgreSQL will frequently need to fetch data from disk, which is slower than reading it from memory. On the other hand, if the buffer pool is too large, it can consume too much RAM, leaving insufficient memory for the operating system and other processes.

In general, shared_buffers should be set to 25% to 40% of the total system RAM, though this can vary depending on the workload. For read-heavy databases, a larger buffer pool may be beneficial, allowing more data to be cached in memory. For write-heavy databases, it’s important to ensure that the buffer pool isn’t so large that it delays the writing of dirty pages to disk.

Another related setting is checkpoint_timeout, which controls how often PostgreSQL performs a checkpoint, flushing all dirty pages from the buffer pool to disk. Setting this value too low can result in frequent I/O spikes, while setting it too high can lead to long recovery times after a crash.

5. PostgreSQL log settings

Tuning PostgreSQL’s log settings is essential for diagnosing and resolving performance issues. Logs can provide valuable insights into how the database is functioning, where bottlenecks are occurring, and what queries are underperforming.

Here are three important log settings:

• log_min_duration_statement: Determines the minimum execution time for queries that should be logged. By setting this parameter, you can capture slow queries that exceed a specified duration, allowing you to analyze and optimize them.
• log_checkpoints: Enabling this can help you understand the impact of checkpoints on performance. When enabled, PostgreSQL logs information about each checkpoint, including how long it took and whether it triggered excessive I/O.
• log_lock_waits: Shows which logs any queries that have to wait for a lock for more than a specified duration. This can help identify locking issues, such as deadlocks or contention points.
• log_statement: Configuring this to log all statements (or specific types like ddl for data definition language commands) can provide a comprehensive view of database activity. However, this should be used with caution in production environments, as it can generate a large amount of log data.

6. Query performance tuning

Query performance tuning is one of the most impactful areas for improving PostgreSQL performance. It involves analyzing how SQL queries are executed and making adjustments to ensure they run as efficiently as possible.

Here are a few common query optimization strategies:

• Analyzing the execution plan of queries using the EXPLAIN command. EXPLAIN provides detailed information about how PostgreSQL executes a query, including which indexes are used, the join methods applied, and the estimated cost of each operation.
• Rewriting queries to use joins instead of subqueries, especially correlated subqueries, which can be significantly slower. For instance, a subquery that needs to be executed multiple times for each row in the outer query can often be replaced by a join, which PostgreSQL can execute more efficiently.
• Limiting the number of rows returned by queries using the LIMIT clause, especially in cases where only a subset of the data is needed. This reduces the amount of data PostgreSQL needs to process and transmit, speeding up query execution.
• Optimizing JOIN operations is also crucial. For example, ensuring that the columns used in joins are indexed can greatly reduce the time required to execute the join. Additionally, using the appropriate join type (e.g., INNER JOIN, LEFT JOIN) based on the specific needs of the query can avoid unnecessary processing.
• Reducing the number of columns returned by queries can also improve performance. By only selecting the columns you need, you reduce the amount of data that PostgreSQL must read, process, and send back to the client.

Related content: Read our guide to PostgreSQL management

7. Autovacuum and bloat management

PostgreSQL uses a multi-version concurrency control (MVCC) model, which means updates and deletes don’t immediately remove old rows; they create new row versions. Over time, this leads to “bloat” in tables and indexes: dead tuples that consume space and slow down queries. The autovacuum process handles cleanup, but it must be properly configured to prevent performance degradation.

Understanding autovacuum

Autovacuum is a background process that reclaims storage by removing dead tuples and updating statistics. It’s essential for keeping table sizes under control and ensuring the planner has accurate data. Autovacuum also prevents transaction ID wraparound, which can corrupt the database if not addressed.

Key autovacuum settings

• autovacuum_vacuum_threshold and autovacuum_analyze_threshold: These define base thresholds (in number of tuples) for triggering a vacuum or analyze on a table.
• autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor: These are multipliers based on the table size. A lower scale factor causes autovacuum to run more frequently.
• autovacuum_max_workers: Limits the number of concurrent autovacuum processes. Increasing this can help large databases maintain vacuum throughput.
• autovacuum_naptime: Defines how often the autovacuum launcher checks for tables to vacuum. Lower values lead to more frequent checks.
• log_autovacuum_min_duration: Logs autovacuum operations that exceed the specified duration, useful for identifying long-running vacuums that may impact performance.

Managing bloat

Even with autovacuum, some tables may experience excessive bloat due to high update/delete activity or inefficient vacuum settings. In such cases:

• Manual VACUUM FULL: This can reclaim space by rewriting the entire table, but it requires an exclusive lock.
• pg_repack: An extension that reclaims space online without significant downtime.
• Monitoring bloat: Querying the pg_stat_user_tables view or using extensions like pgstattuple can help identify bloated tables.

Proactive bloat management is critical for write-heavy databases. Tuning autovacuum to respond quickly and aggressively on high-churn tables reduces performance issues caused by bloated data structures.

8. Monitoring and observability

Effective monitoring helps identify performance issues before they affect users. PostgreSQL exposes extensive runtime statistics through system views, and integrating these with monitoring tools can provide real-time observability into database health.

Key built-in views

• pg_stat_activity: Shows current queries and connection states.
• pg_stat_user_tables: Provides statistics on table scans, index usage, and vacuum activity.
• pg_stat_bgwriter: Tracks background writer and checkpoint activity.
• pg_locks: Displays active locks, helping diagnose contention or deadlocks.
• pg_stat_statements: An extension that tracks execution statistics for all SQL statements, useful for identifying slow or frequently executed queries.

Metrics to monitor

• Query latency and throughput
• Buffer cache hit ratio (ideally >99%)
• Autovacuum frequency and duration
• Number of active and idle connections
• Lock wait times and deadlocks
• Checkpoint frequency and duration
• Disk I/O usage and replication lag (if applicable)

By combining internal PostgreSQL statistics with external monitoring systems, teams can proactively manage database performance, detect slowdowns, and make informed tuning decisions.

9. WAL and checkpoint tuning

PostgreSQL uses a Write-Ahead Logging (WAL) mechanism to ensure data durability and crash recovery. Every change to the database is first written to WAL before it’s written to the data files. Tuning WAL and checkpoint settings can significantly impact write performance, crash recovery time, and disk I/O patterns.

Key WAL settings

• wal_level: Controls the amount of information written to WAL. Higher levels (e.g., replica or logical) are needed for replication and logical decoding but increase WAL size.
• wal_buffers: Allocates memory for buffering WAL writes. Increasing this (typically 16MB–64MB) can improve performance on write-heavy workloads by reducing the frequency of writes to disk.
• commit_delay: Introduces a short delay before commit to allow more transactions to group their WAL writes, reducing disk I/O at the cost of slightly higher latency.
• synchronous_commit: Controls when commits wait for WAL records to be flushed. Setting it to off can improve throughput but risks data loss in a crash.

Checkpoint tuning

Checkpoints flush dirty pages from shared buffers to disk and write a special WAL record so recovery can resume from that point. While essential, checkpoints can cause I/O spikes and impact performance if not tuned properly.

Key parameters:

• checkpoint_timeout: Defines how often PostgreSQL performs a checkpoint. Increasing it reduces checkpoint frequency but increases recovery time after a crash.
• checkpoint_completion_target: A value between 0 and 1 that controls how evenly checkpoint writes are spread. Setting it to 0.7–0.9 helps reduce I/O spikes by spreading writes more smoothly.
• max_wal_size and min_wal_size: These control when a checkpoint is triggered based on WAL size. Larger values delay checkpoints, which can reduce overhead but increase recovery time.
• wal_compression: Enables compression of full-page writes, reducing WAL size at the cost of additional CPU usage.

For backup and high availability, WAL segments can be archived using archive_mode and archive_command. Ensure that archiving is fast and reliable to avoid WAL accumulation.

10. Advanced indexing strategies

While basic indexing (e.g., B-tree on primary keys) is standard, PostgreSQL offers several advanced indexing techniques that can dramatically improve performance for complex queries and specialized workloads.

Partial indexes

A partial index is created with a WHERE clause to index only a subset of rows. This reduces index size and improves performance for queries that filter on the indexed condition.

Use when queries frequently target a predictable subset of data.

Expression indexes

Indexes can be built on expressions, not just columns. This is useful when queries apply functions or transformations.

This avoids repeated computation and enables index use for matching case-insensitive values.

Multicolumn indexes

Indexes on multiple columns are efficient for queries that filter or sort on those columns in the same order. However, they only help if the leading column(s) are used in the query.

GIN and GiST indexes

For complex data types (e.g., arrays, JSONB, full-text search), PostgreSQL provides Generalized Inverted Index (GIN) and Generalized Search Tree (GiST):

• GIN: Efficient for indexing array values, JSONB fields, and full-text search. Ideal when queries use @>, ?, or text search operators.
• GiST: Useful for geometric data types, range types, and similarity search (e.g., pg_trgm extension).

BRIN indexes

Block Range Indexes (BRIN) are efficient for very large tables with naturally ordered data (e.g., timestamps). They use minimal storage and are fast to build, but offer less precise filtering than B-tree.

Covering indexes (INCLUDE clause)

PostgreSQL supports INCLUDE indexes that store extra columns in the index to satisfy queries without needing to access the heap.

This reduces I/O for read-heavy queries that need additional columns not part of the sort or filter criteria.

Boosting performance with Instaclustr for PostgreSQL

In today’s data-driven world, performance is a critical factor for any application relying on a PostgreSQL database. To address this need, Instaclustr offers a managed platform specifically designed to optimize the performance of PostgreSQL databases. With a range of powerful features and expert support, Instaclustr empowers developers and businesses to achieve exceptional performance levels and unlock the full potential of their applications.

• Advanced resource scaling: The Instaclustr managed platform has the ability to seamlessly scale PostgreSQL resources. Whether you need to handle sudden spikes in traffic or accommodate growing data volumes, Instaclustr allows you to effortlessly adjust your database resources. By dynamically allocating additional CPU, memory, and storage, you can ensure that your PostgreSQL database remains responsive and performs optimally under varying workloads.
• Automated performance monitoring: Instaclustr includes robust monitoring and alerting capabilities that provide real-time insights into the performance of your PostgreSQL database. Through comprehensive metrics and dashboards, you can track critical performance indicators such as query response times, throughput, and resource utilization. This proactive monitoring enables you to identify bottlenecks, optimize queries, and fine-tune your database configuration to maximize performance.
• Caching and query optimization: Instaclustr leverages advanced caching techniques to accelerate query execution and reduce database load. By intelligently caching frequently accessed data, Instaclustr minimizes the need for repetitive disk reads, resulting in significant performance gains. Additionally, the platform offers query optimization tools and techniques to fine-tune SQL queries, ensuring efficient execution plans and faster response times.
• Highly available architecture: Instaclustr is built on a highly available architecture. By deploying your database across multiple availability zones, Instaclustr ensures fault tolerance and minimizes the risk of downtime. This resilient infrastructure guarantees that your application remains accessible and responsive, even in the face of hardware failures or network disruptions.
• Expert support and database tuning: Instaclustr provides 24/7 expert support from experienced database administrators who specialize in PostgreSQL. Their team is available to assist with performance tuning, query optimization, and troubleshooting. With their guidance, you can fine-tune your PostgreSQL database configuration, leverage best practices, and implement performance-enhancing techniques to achieve optimal results.

For more information see:

• Instaclustr for PostgreSQL
• Creating a PostgreSQL Cluster with Azure NetApp Files
• What is Fast Forking for PostgreSQL® on Azure NetApp Files?