Schedule demo

Tech Topics Home

Tuning your PostgreSQL Server for performance

PostgreSQL is an open-source, enterprise-level relational database management system known for its robustness. Various organizations choose PostgreSQL as it supports advanced data types, complies with the ACID model, is easily scalable and has strong security features. Since many mission-critical applications use PostgreSQL, database administrators have to ensure they have a monitoring system in place to help identify pain points and tune the database as and when necessary to enhance database performance.

Lets look in to some of the most important configuration parameters in tuning the PostgreSQL database server:
Memory Tuning
Settings shared_buffers
Description Shared buffers are a specific section of memory designated to cache frequently accessed index blocks and data. By utilizing shared buffers, the database system can enhance its performance by minimizing the need to read data from disk, which is typically slow.
Note: Increasing this configuration requires an increase in max_wal_size in order to process the larger amount of data when a checkpoint occurs.
Default 128 MB
Recommended 25% of the RAM
Settings work_mem
Description Work memory in PostgreSQL is the memory allocated for executing individual operations, like sorting or hashing. This memory holds temporary data structures and intermediate results generated during the operation. The amount of work memory required by an operation depends on the complexity and size of input data, as well as available system memory. Inadequate work memory can cause operations to use disk-based temporary files, resulting in slower performance.
Note: Postgres 13 presents a fresh configuration named hash_mem_multiplier, which serves as an extra option to optimize the allocation of memory.
Default 4 MB
Recommended A reasonable value can be chosen based on the amount of data that is queried and brought into memory. You might also have to consider the max_connections parameter before setting a value, as several connections may be running in parallel. (Total RAM used = work_mem * concurrent connections). Finding the optimal value for work_mem can be challenging, but a reasonable default value that could work universally is around 64 MB.
Settings maintenance_work_mem
Description The maintenance_work_mem parameter controls the amount of memory used for maintenance operations such as VACUUM, index creation, and adding foreign keys. Since only one of these operations can be executed at a time in one database session it is safe to set this value larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.
Default 64 MB
Recommended One thing to be considered here is that when autovacuum runs, memory consumed = autovacuum_max_workers x maintenance_work_mem. The value of this can be 512 MB.
Cache Tuning
Settings effective_cache_size

The effective cache size parameter specifies the amount of memory that the database server can use for caching data and index blocks in memory. This parameter represents the estimated amount of memory used by the operating system file system cache and any other processes that may be running on the same system.

The effective cache size parameter is utilized by the query planner to calculate the cost of various query execution plans. This setting allows the planner to make an informed estimate of the amount of data that is expected to be cached in memory, which is then used to make optimal decisions regarding query execution plan selection.

Default 4 GB
Recommended 50% of the memory
Worker Parameters
Settings max_worker_processes
Description The max_worker_processes parameter in PostgreSQL sets a limit on the number of concurrent background worker processes that can be running simultaneously. These worker processes perform various functions such as parallel query execution, handling connections, and background tasks.
Default 8
Recommended It is recommended to use 50% of the processors so that the system can run normally.
Settings max_parallel_workers
Description The max_parallel_workers parameter in PostgreSQL limits the total count of parallel worker processes that can be active simultaneously. These worker processes are employed to enhance system performance during parallel query execution and other operations. Careful configuration of this parameter is crucial because it can significantly impact the resource utilization and overall performance of the system.
Default 2
Recommended It is recommended to use 50% of the processors so that the system can run optimally.
Settings max_parallel_workers_per_gather
Description The max_parallel_workers_per_gather parameter in PostgreSQL manages the maximum count of parallel worker processes that can be engaged in a single operation. This parameter sets an upper boundary on the number of workers that can execute parallel table scans and query plans. It can significantly impact the performance of parallel queries, and requires careful configuration that considers the system resources and workload characteristics.
Default 2
Recommended Parallel workers are taken from the pool of processors established by the max_worker_processes and limited by max_parallel_workers.
Settings max_wal_size

The max_wal_size parameter in PostgreSQL sets an upper limit on the size of the write-ahead log (WAL) that the database system can use. The WAL is a critical part of PostgreSQL's transaction management mechanism, responsible for maintaining data consistency and durability even in the event of system crashes or failures.

Configuring this parameter allows you to set the maximum size for WAL grow during automatic checkpoints. WAL size can exceed max_wal_size under special circumstances, such as heavy load, a failing archive_command, or a high wal_keep_size setting.

Default 1 GB
Recommended Increasing this parameter can increase the amount of time needed for crash recovery.
Settings default_statistics_target
Description The default_statistics_target configuration parameter specifies the level of detail used by the query optimizer when collecting statistics about the database. the number of rows that are examined by the optimizer to determine the most efficient query execution plan is governed by this parameter . It determines the sample size used by the optimizer when analyzing a table for this purpose.
Default Sample size = 300 * default_statistics_target
Recommended Larger values increase the time needed to work on ANALYZE, but might improve the quality of the planner's estimates.

Caution: Changing a configuration will have an impact on the system. It is recommended to test the system with a test load before making the modifications to the production setup.

Applications Manager is a highly effective tool for monitoring PostgreSQL database performance. With its extensive monitoring capabilities, it helps database administrators ensure that the database operates smoothly with minimal downtime and predictable overhead costs in the long term. You can configure alarms on important metrics and get notified when thresholds are violated. This can serve as a heads-up for you to tune your database to enhance performance and ensure hassle-free user experiences!

For a hands-on experience of using Applications Manager to tune PostgreSQL performance

Download 30-day free trial now!

Loved by customers all over the world

"Standout Tool With Extensive Monitoring Capabilities"

It allows us to track crucial metrics such as response times, resource utilization, error rates, and transaction performance. The real-time monitoring alerts promptly notify us of any issues or anomalies, enabling us to take immediate action.

Reviewer Role: Research and Development

"I like Applications Manager because it helps us to detect issues present in our servers and SQL databases."
Carlos Rivero

Tech Support Manager, Lexmark

Trusted by over 6000+ businesses globally