Memory and the settings that matter

conceptThe concept

shared_buffers: the shared page cache, typically 25% of RAM; changes need a restart. effective_cache_size: not an allocation, just a hint to the planner about how much the OS plus shared_buffers can cache (often 50-75% of RAM); higher values favour index scans.

work_mem (4 MB default) limits memory for each sort or hash operation in a query; one complex query can use several multiples, and every connection can do this at once. Hash operations may use work_mem * hash_mem_multiplier (2.0). When exceeded, sorts become "external merge" on disk and hashes split into batches. maintenance_work_mem (64 MB) is for VACUUM, CREATE INDEX and ALTER TABLE ADD FOREIGN KEY.

Planner cost settings: random_page_cost (4.0) models random I/O; on SSDs 1.1 to 1.5 is common and makes index scans more attractive. pg_settings shows each value, its unit, where it came from (source) and its context: postmaster needs a restart, sighup a reload, user can be set per session.

whyWhy it matters

work_mem set too low makes analytics sort on disk; set too high globally it lets 500 connections each use gigabytes and the kernel kills the server. Setting it per role or per session is the professional middle ground.

Use caseReal-world scenario

The reporting role gets ALTER ROLE reporting SET work_mem = '256MB', the web role keeps 8 MB, and a nightly index build runs with SET maintenance_work_mem = '2GB' in its own session.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'hash_mem_multiplier', 'maintenance_work_mem', 'effective_cache_size',
               'random_page_cost', 'max_connections', 'max_wal_size', 'autovacuum_max_workers', 'max_parallel_workers_per_gather')
ORDER BY name;

BEGIN;
SET LOCAL work_mem = '64kB';
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM order_items ORDER BY unit_price DESC, order_id;
SET LOCAL work_mem = '16MB';
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM order_items ORDER BY unit_price DESC, order_id;
ROLLBACK;

BEGIN;
SET LOCAL work_mem = '64kB';
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM order_items ORDER BY unit_price DESC LIMIT 10;
ROLLBACK;

BEGIN;
SET LOCAL random_page_cost = 1.1;
SHOW random_page_cost;
SELECT name, setting, source FROM pg_settings WHERE name = 'random_page_cost';
ROLLBACK;
SHOW random_page_cost;

internalsUnder the hood

Linux-specific: enable huge pages for large shared_buffers, avoid swap, and set vm.overcommit_memory = 2 on dedicated servers so the OOM killer does not target the postmaster. Tools such as PGTune give sane starting values from RAM, CPU and workload type.

Avoid / preferDo this, not that

ALTER SYSTEM SET work_mem = '1GB';

BEGIN;
SET LOCAL work_mem = '256MB';  -- only for the big report
-- run it
COMMIT;

shared_buffers = 48GB  # 75% of a 64 GB server

shared_buffers = 16GB  # about 25%, the rest serves the OS page cache

PitfallsCommon mistakes

• Raising work_mem globally to fix one report. Set it for that role, session or transaction.
• Leaving random_page_cost at 4 on SSD storage, which makes the planner avoid useful index scans.
• Treating effective_cache_size as memory PostgreSQL allocates. It is only a planner hint.

LimitsWhen not to use it

Do not tune settings without a measured problem. Defaults plus shared_buffers, effective_cache_size and sensible work_mem cover most servers; random tuning advice often makes things worse.

SecuritySecurity notes

ALTER SYSTEM needs superuser, or since PostgreSQL 15 an explicit GRANT ALTER SYSTEM ON PARAMETER. Grant individual parameters to platform roles rather than superuser.

ChallengeTry it yourself

SELECT name, setting, unit, source
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Resource consumption
• Query planning settings