Surprisingly, AWS RDS for PostgreSQL uses the default PostgreSQL configurations with minimal customization. Here, I highlight fifteen postgresql “server” parameters that you can adjust to improve your database’s performance.

work_mem

This parameter determines the amount of memory allocated for sorting operations during queries. Insufficient work_mem causes operations to spill onto disk, slowing down processes, whereas excessively high settings can deplete memory available for new connections.

log_temp_files

Enabling this setting helps track queries that are spilling to disk, indicated by log messages about temporary files or “external merge Disk”. For web applications with administrative backend or reporting functionality, starting with 8MB is advisable, adjusting as needed based on workload demands. Values larger than 64MB suggest conflicting workload requirements. Consider using a read replica for queries that require more memory.

maintenance_work_mem

Configuring this parameter optimizes the memory allocated for database maintenance tasks like vacuuming and index building. Most (OLTP) databases will benefit from increasing maintenance_work_mem to 5% of the total memory.

Checkpoint Parameters

• checkpoint_completion_target
• checkpoint_timeout
• max_wal_size
• min_wal_size
• log_checkpoints

The default settings in PostgreSQL lead to excessive checkpointing, creating additional write loads. The aim is to have regular checkpoints, guided by the checkpoint_timeout parameter. Increase max_wal_size gradually if the system hits the limit often, and increase checkpoint_timeout for databases with high I/O during checkpoints. For checkpoint_timeout, values up to one hour are sensible.

Here is a good “starter” configuration:

checkpoint_completion_target    = 0.97
checkpoint_timeout              = 25m
max_wal_size                    = 4GB
min_wal_size                    = 512MB
log_checkpoints                 = on

wal_buffers

Important for accommodating the write-ahead log data of transactions, especially in systems with high concurrency. Initial allocation recommendations include 16MB plus an additional 4MB for each CPU core. A restart is required for the new setting to come into effect. Adjust wal_buffers along with max_connections to minimize downtime.

max_connections

Increase the default to 200 to allow for more simultaneous connections. Consider connection pooling for higher demands.This requires a restart, but restarting now affects fewer customers, making it a preferable option.

effective_io_concurrency

This setting determines the maximum number of concurrent operations that the underlying system can support. Adjusting this parameter can lead to significant performance improvements, although identifying the optimal value might require extensive experimentation. I recommend starting with a value of 10 * # of cpus, with possibilities extending up to 1000. Databases that often perform Bitmap Heap Scans will particularly benefit from higher values.

Logging Parameters

• log_statement
• log_min_duration_statement
• log_lock_waits

To prevent the potential exposure of Personally Identifiable Information (PII), set log_statement to none to disable it and change the slow_query_logging setting to -1 to turn slow query logging off. To identify slow queries, consider using the auto_explain extension for brief periods. Enable log_lock_waits to identify whether extended lock waits occur. Lock wait timeouts lead to suboptimal performance.

statement_timeout

Long queries consume resources, slow down queries, block new connections, and cause conflicts with other transactions. Setting the statement_timeout parameter to a maximum of 80% of the client timeout is recommended to prevent this scenario. For instance, if using Puma with a default timeout of 60 seconds, set the statement_timeout parameter to a maximum of 48 seconds.

Conclusion

Adjusting these fifteen parameters can significantly improve your database’s performance helping you get more from your existing database server without upsizing.