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

What is PostgreSQL performance tuning?

To optimize PostgreSQL, you must modify its default configuration parameters, which are intentionally set conservative to run on minimal hardware, and optimize your indexes and query structures.

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.

Tune the critical memory parameters:

• shared_buffers: dedicated memory PostgreSQL uses for caching data; set to 25% to 40% of total system RAM.
• work_mem: memory for internal sort operations and hash tables; start between 4MB and 16MB, since it is allocated per query operation.
• maintenance_work_mem: memory for maintenance tasks like VACUUM and CREATE INDEX; set to about 10% of RAM, up to 2GB to 4GB.
• effective_cache_size: tells the planner how much total memory is available for disk caching; set to 50% to 75% of total system RAM.

Apply high-impact architectural wins:

• Connection pooling: use an external pooler like PgBouncer to cap maximum connections while serving thousands of application threads.
• Index optimization: inspect slow queries with EXPLAIN (ANALYZE, BUFFERS), watch for sequential scans on large tables, and add targeted B-tree or partial indexes.

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 resources 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.
• max_worker_processes: set this to the total number of CPU cores available on your system, since the PostgreSQL defaults otherwise underutilize a multi-core machine.
• max_parallel_workers_per_gather: sets the number of workers that can cooperate on a single parallel query; set it to roughly 25% to 50% of your core count.

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. A reasonable starting point is between 4MB and 16MB per operation.
• maintenance_work_mem: determines the maximum memory used for maintenance tasks like VACUUM, CREATE INDEX, and ALTER TABLE. Set it to about 10% of RAM, up to 2GB to 4GB.
• effective_cache_size: tells the query planner how much total memory, including the OS page cache, is available for disk caching. Set it to 50% to 75% of total system 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 optimization

Query optimization, or 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. Operating system optimization

Optimizing the operating system ensures PostgreSQL can use system resources effectively. Performance gains often come from fine-tuning file system parameters, memory management, and process scheduling.

• Choose an appropriate file system: Ext4 and XFS are generally recommended for PostgreSQL due to their stability and performance.
• Disable access time updates: Mount the PostgreSQL data directory with the noatime option to avoid unnecessary write operations from atime updates.
• Tune kernel parameters:
  • Increase vm.dirty_background_bytes and vm.dirty_bytes to control when dirty pages are flushed to disk, smoothing I/O spikes.
  • Lower vm.swappiness (e.g., 1–10) to prevent PostgreSQL memory from being swapped out unnecessarily.
• Optimize I/O scheduling: For SSDs, use the noop or deadline scheduler to reduce latency compared to the default cfq.
• Adjust readahead settings: Lower readahead for PostgreSQL data devices to avoid unnecessary prefetching and improve cache efficiency.
• Disable transparent huge pages (THP): THP can introduce unpredictable latency and should be turned off for database workloads.

8. Vacuum

Vacuuming is essential in PostgreSQL to reclaim space from deleted or updated rows and to maintain table statistics for the query planner. Without regular vacuuming, table bloat increases, causing slower queries and higher disk usage.

PostgreSQL provides two main vacuum options:

• VACUUM reclaims space and makes it available for reuse but does not shrink the physical file size.
• VACUUM FULL reclaims space and physically reduces the file size, but it requires an exclusive table lock and is slower.

Autovacuum automates this process but should be tuned for workload patterns. Parameters like autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor determine when a table is vacuumed. Reducing these values triggers vacuuming earlier, preventing excessive bloat in high-write tables.

Running ANALYZE as part of the vacuum process updates statistics that help the query planner choose the most efficient execution paths. For performance-critical tables, scheduling manual vacuuming during low-traffic periods can maintain responsiveness without impacting users.

For heavy transactional workloads, frequent autovacuuming on hot tables ensures indexes remain efficient and prevents transaction ID wraparound issues, which can lead to database downtime if not addressed.

9. Indexing strategies

Indexing is one of the most powerful tools for improving query performance in PostgreSQL, but it must be applied strategically to avoid excessive storage use and slow write performance.

• B-Tree indexes are the default and best suited for equality and range queries on sortable data. They work well for columns frequently used in WHERE, JOIN, and ORDER BY clauses.
• Hash indexes are optimized for equality comparisons but are less versatile than B-Tree indexes. They can be useful in specific workloads with high volumes of equality lookups.
• GIN (Generalized Inverted Index) indexes are ideal for full-text search and array columns, enabling fast lookups of multiple matching values. GiST (Generalized Search Tree) indexes handle more complex data types, such as geometric and network data.
• BRIN (Block Range Index) indexes are lightweight and suitable for very large tables with naturally ordered data, such as time-series records. They require less storage but may return more rows that still need filtering.
• Partial indexes can speed up queries that only target a subset of rows by indexing only the relevant portion of data. Covering indexes, which include extra columns with INCLUDE, can reduce table lookups by storing additional needed values directly in the index.

Index maintenance is also critical—unused indexes add write overhead and waste space. Regularly reviewing index usage with pg_stat_user_indexes and removing redundant ones helps maintain write performance.

10. Regular maintenance

Keeping PostgreSQL in peak condition requires ongoing maintenance tasks beyond vacuuming.

• Running ANALYZE updates table and index statistics, ensuring the query planner makes optimal execution choices. This should be run after bulk inserts, updates, or deletes to prevent suboptimal query plans.
• Reindexing can restore performance on heavily updated tables where index bloat reduces lookup efficiency. Use REINDEX periodically or on indexes identified as bloated via pg_stat_all_indexes.
• Checking for and removing unused indexes prevents unnecessary write overhead. Over time, application changes may make certain indexes obsolete.
• Monitoring database size, table growth, and transaction throughput helps detect performance issues early. Tools like pg_stat_activity and pg_stat_io provide insights into query performance and resource usage.
• Regular backups are also part of performance maintenance, ensuring recovery readiness avoids downtime from unexpected failures. Scheduling these tasks during low-load periods minimizes user impact.

Related content: Read our guide to Postgres management

PostgreSQL performance tuning tools and utilities

PostgreSQL provides several performance tuning utilities that can help monitor, analyze, and optimize database performance by providing visibility into system metrics, query execution, and configuration parameters. Here are a few useful utilities:

1. EXPLAIN and EXPLAIN ANALYZE
   These built-in commands show how PostgreSQL executes a query.
   • EXPLAIN displays the planned execution steps without running the query.
   • EXPLAIN ANALYZE executes the query and shows the actual execution time, allowing comparison between estimated and real costs.
     They are critical for identifying inefficient query plans and missing indexes.
2. pg_stat_activity
   This system view lists active queries and session states. It helps detect slow-running queries, idle transactions, and blocking processes that can affect performance.
3. pg_stat_statements
   An extension that tracks execution statistics for all SQL statements, including total runtime, average runtime, and call frequency. It is useful for finding queries that consume the most resources over time.
4. Auto-Explain
   This extension logs execution plans for slow queries automatically. It is especially valuable in production, where reproducing slow queries may not be feasible.
5. pgBadger
   A log analysis tool that processes PostgreSQL log files to produce detailed performance reports. It can highlight slow queries, lock waits, checkpoint activity, and connection patterns.
6. pgTune
   A configuration tuning tool that generates recommended PostgreSQL settings based on system hardware and workload type. While it cannot replace manual tuning, it provides a solid starting point.
7. pg_profile
   An extension for historical performance analysis that collects snapshots of database activity, making it easier to compare performance trends over time.
8. pg_top
   Similar to the Unix top command but focused on PostgreSQL processes. It displays active queries, CPU usage, memory usage, and transaction activity in real time.

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?