Buffer cache, I/O and memory
Should this memory change ship?
The idea in 30 seconds
Memory settings are tempting to raise because one slow query got faster. Before approving, multiply: work_mem times the sort and hash steps in a query, times the connections that can run at once, must still fit in the machine alongside shared buffers and the operating system cache. Prefer raising limits for the one job that needs them.
Helps to know first: How a table is stored on disk
On this page
The situation
A nightly finance report started spilling sorts to disk and now takes several minutes. The database server has 16 GB of RAM, max_connections is 200, and the application keeps about 100 connections open. An AI assistant opened a pull request.
Review taskRaise memory for the report
Pull request: “The finance report spills to disk. Give PostgreSQL more memory.”
# postgresql.conf
shared_buffers = 12GB # was 4GB
work_mem = 1GB # was 4MBWould you approve it? Write the review you would leave.
Your call
Model review
Request changes. Both lines are set for the one report and applied to everything.
1. work_mem = 1GB can exhaust the machine. It is a limit per sort or hash step, not per query or per server. We ran one three-table query with work_mem at 64 MB: its two hash tables alone used 100 MB, and its sort spilled on top of that. At 1 GB, a handful of such queries in parallel can claim more memory than the server has, and with 100 open connections there is no reason only one would run at a time. When memory runs out, Linux may kill PostgreSQL’s server process outright, which the documentation warns about: a much worse failure than a slow report.
2. shared_buffers = 12GB is 75% of RAM. The documentation suggests about 25% as a starting point and says more than 40% is unlikely to help, because PostgreSQL also relies on the operating system’s cache, and the remaining 4 GB must hold that cache, every connection’s own memory and the operating system itself.
3. Measure what the spill costs first. On our test machine, letting an 800,000-row sort run in memory instead of spilling made it about 10% faster. If the report takes minutes, check its plan for something bigger: a missing index, a misestimated join, or a slow text collation.
What I would ask for instead: raise memory only for the report.
BEGIN;
SET LOCAL work_mem = '512MB'; -- only for this transaction
-- the finance report query
COMMIT;Or ALTER ROLE finance_report SET work_mem = '512MB'; if the report runs as its own role. Leave shared_buffers at 4 GB, 25% of RAM, unless measurements show a cache problem.
Compare it with yours. Did your review catch the main problems?
How to decide
Prefer the narrowest scope: a setting for one transaction (SET LOCAL), one session, one role or one database before the whole server.
Do the multiplication for any work_mem change: value × sort and hash steps per query × connections that may run them at once. Compare with the memory left after shared buffers.
Leave room for the operating system cache. PostgreSQL depends on it; shared_buffers above about 40% of RAM rarely helps.
Measure the spill before fixing it. EXPLAIN (ANALYZE, BUFFERS) shows how much was spilled and how long it took; often the real cost is elsewhere.
Check yourself
Why is
work_mem = 1GBdangerous even if each report query only needs that much once?Show answer
It is a per-operation limit applied to every connection. Any query with several sort or hash steps, in any of the 100 connections, may claim up to that much per step, and they can all run at the same time.
How would you give only the report more sort memory?
Show answer
SET LOCAL work_meminside the report’s transaction,SET work_memin its session, orALTER ROLE ... SET work_memfor the role it runs as.Why is
shared_buffersat 75% of RAM likely to make things worse rather than better?Show answer
PostgreSQL relies on the operating system’s page cache too. Giving shared buffers most of the RAM squeezes that cache, every connection’s own memory and the operating system, which can cause swapping.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 19.4 Resource ConsumptionPrimary· §19.4.1 Memory
work_mem applies per sort or hash operation and total use can be many times its value; shared_buffers of 25% of RAM is a reasonable start and over 40% is unlikely to help; maintenance_work_mem is used by VACUUM and CREATE INDEX.
- 2.PostgreSQL documentation: SETPrimary· Parameters: LOCAL
SET LOCAL takes effect only for the current transaction.
- 3.PostgreSQL documentation: ALTER ROLEPrimary· Description
ALTER ROLE ... SET changes a role's session default for a setting.
- 4.PostgreSQL documentation: 18.4 Managing Kernel ResourcesPrimary· §18.4.4 Linux Memory Overcommit
When memory runs out on Linux, the kernel may terminate the PostgreSQL postmaster; memory-related settings such as shared_buffers, work_mem and hash_mem_multiplier can contribute.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 19.4 Resource ConsumptionPrimary· §19.4.1
Every memory setting with its default and the caveats that go with it.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.