Good Memory Configuration is the Antidote to Bad Queries

Good Memory Configuration is the Antidote to Bad Queries

Shared Buffers

This parameter defines the memory area used as the database cache. It holds frequently accessed data and modified pages that haven’t yet been written to disk. Efficient use of memory in PostgreSQL largely depends on proper tuning of this parameter.

• Default Value: 128 MB (very low for modern systems)
• Recommended Setting: Between 15% and 35% of total RAM
• Example: On a server with 64 GB RAM, allocating 16 GB to shared_buffers is a good starting point.

Important: Setting shared_buffers too high can cause overlap with the operating system’s page cache, leading to double caching and inefficient memory usage. Values above 50% of total RAM are generally discouraged.

• For OLTP (transaction-heavy) workloads, around 15% of RAM is typically sufficient.
• For OLAP (analytics-heavy) workloads, values closer to 35% may be beneficial.
• Changes to this parameter require a PostgreSQL restart.

work_mem

Defines the amount of memory available for each query operation such as ORDER BY, DISTINCT, or MERGE JOIN.

• Basic Rule of Thumb: shared_buffers / max_connections

If this setting is too low, PostgreSQL will fall back to temporary disk files for intermediate results, which significantly impacts I/O performance.

You can override work_mem on a per-session or per-user basis, which is especially useful for analytic users such as data scientists and BI developers.

maintenance_work_mem

Memory allocated for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE.

• Recommended Range: 5% to 10% of total RAM

Setting this value higher can dramatically improve performance of operations on large tables, especially during vacuuming.

temp_buffers

This is the memory allocated per session for temporary tables.

• Default Value: 8 MB
• Suggested Starting Point: 16 MB

This memory is reserved as soon as a connection is established. If set too high, it may lead to excessive RAM consumption when many connections are active.

It’s best to adjust this based on actual usage patterns and monitoring.

autovacuum_work_mem

Memory allocated for autovacuum operations.

• Default: -1 (inherits from maintenance_work_mem)

To enhance the performance of automatic vacuuming processes, it is recommended to assign a dedicated value rather than relying on the default inheritance.

Conclusion

PostgreSQL configuration is not governed by fixed rules — it depends heavily on factors like workload type, database size, usage patterns, and available system resources. The settings discussed in this guide are based on real-world experience and serve as best practice starting points. Always measure, monitor, and adjust according to your system’s behavior in a live environment.

“A well-configured database can tolerate poorly written queries;
 but even the best queries will perform poorly on a badly configured system.”