Memory Tuning
Verify shared_buffers with a restart, then compare disk and memory sorts using real query plans.
shared_buffers sets aside a chunk of memory PostgreSQL manages directly for caching table and index pages — too small, and the same pages get re-read from the OS cache (or disk) far more than necessary; too large, and it starves the OS's own filesystem cache, which PostgreSQL also depends on for everything shared_buffers itself does not hold. Roughly 25% of real available memory is the standard starting point precisely because it leaves the OS enough room to do useful caching of its own. Changing shared_buffers requires a genuine restart — it is fixed at postmaster startup, not reloadable — which this lab performs for real, not just edits a config file and hopes.\n\nwork_mem governs memory for a single sort or hash operation; get it too low and a query that could fit comfortably in memory spills to temporary files on disk instead, genuinely slower. effective_cache_size is different in kind from both: it never allocates anything at all — it only tells the planner how much total caching (shared_buffers plus OS cache) to assume exists when estimating a plan's cost, which is why this lab proves its effect shows up entirely in EXPLAIN's cost numbers, never in real memory usage or actual execution behavior.
This VM's Real Memory, Confirmed Directly
$ dmesg | grep 'Memory:'
dmesg: read kernel buffer failed: Function not implemented
Kernel RAM cannot be inspected with dmesg in cxemu. This lab uses 64MB as an exercise setting and verifies it with SHOW after a restart. Size production shared_buffers using the actual server memory and workload; do not infer a RAM measurement from this emulator.
shared_buffers: A Genuine Restart
sed -i 's/^shared_buffers = 16MB/shared_buffers = 64MB/' /var/lib/postgresql/18/data/postgresql.conf
pg_ctl restart -D /var/lib/postgresql/18/data -w
SHOW shared_buffers; -- 64MB
shared_buffers cannot be changed with a plain SET or even a config reload — it is fixed at postmaster startup, which is exactly why this required a real pg_ctl restart, not just an edit to the file.
work_mem: A Real Sort, Genuinely Fixed
SET work_mem = '4MB'; -- the previous default
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort ORDER BY val;
Sort Method: external merge Disk: 7400kB
Execution Time: 7139.965 ms
SET work_mem = '16MB';
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort ORDER BY val;
Sort Method: quicksort Memory: 14218kB
Execution Time: 4242.965 ms
The identical sort of 300,000 rows, roughly 40% faster once given enough memory to complete without touching disk at all.
effective_cache_size: Advisory, Proven Directly
SET effective_cache_size = '128kB'; -- "assume almost nothing is cached"
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort WHERE val BETWEEN 100 AND 150;
-- cost=0.42..59390.45, Execution Time: 203.422 ms
SET effective_cache_size = '4GB'; -- "assume everything is cached"
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort WHERE val BETWEEN 100 AND 150;
-- cost=0.42..7032.46, Execution Time: 141.500 ms
The cost estimate swings by more than 8x between the two settings. The real execution time and real buffer access barely move at all — because effective_cache_size never allocates a single byte of memory; it only changes what the planner assumes when estimating cost.
shared_buffers
A block of memory PostgreSQL allocates at startup and manages directly for caching table and index pages, shared across every backend process. It requires a full server restart to change — unlike most GUCs, it is fixed for the lifetime of the running postmaster, since the shared memory segment it reserves is sized once at startup.
effective_cache_size
A GUC that allocates nothing at all — it only tells the query planner how much total caching capacity (shared_buffers plus whatever the OS is likely caching) to assume when estimating the cost of repeated random-access reads, such as an index scan. Setting it does not reserve, allocate, or reduce any real memory; it only changes the planner's cost model, which is why its entire measurable effect shows up in EXPLAIN's cost numbers rather than in real execution behavior.
🧠 Check Real Memory and Restart With a Tuned shared_buffers
Confirm this VM's actual usable memory directly, then change shared_buffers and restart PostgreSQL for real.
dmesg | grep 'Memory:'as-postgres sh -c "sed -i 's/^shared_buffers = 16MB/shared_buffers = 64MB/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w'psql -U postgres -d beer_db -c "SHOW shared_buffers;"student@lab:~$ dmesg | grep 'Memory:' dmesg: read kernel buffer failed: Function not implemented student@lab:~$ as-postgres sh -c "sed -i 's/^shared_buffers = 16MB/shared_buffers = 64MB/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w' waiting for server to shut down.... done server stopped waiting for server to start....2026-06-26 16:02:37.365 UTC [138] LOG: starting PostgreSQL 18.4 on i686-buildroot-linux-gnu, compiled by i686-buildroot-linux-gnu-gcc.br_real (Buildroot 2024.02.3) 12.3.0, 32-bit 2026-06-26 16:02:37.366 UTC [138] LOG: listening on IPv4 address "0.0.0.0", port 5432 2026-06-26 16:02:37.367 UTC [138] LOG: listening on IPv6 address "::", port 5432 2026-06-26 16:02:37.374 UTC [138] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2026-06-26 16:02:37.441 UTC [141] LOG: database system was shut down at 2026-06-26 16:02:37 UTC 2026-06-26 16:02:37.478 UTC [138] LOG: database system is ready to accept connections done server started student@lab:~$ psql -U postgres -d beer_db -c "SHOW shared_buffers;" SET shared_buffers ---------------- 64MB (1 row)
💾 Measure work_mem Genuinely Fixing a Disk-Spilling Sort
Sort 300,000 rows with a small work_mem, then with a larger one, and measure the real difference.
psql -U postgres -d beer_db -c "CREATE TABLE sql_cfg_sort (id int PRIMARY KEY, val numeric);" -c "INSERT INTO sql_cfg_sort SELECT g, random()*1000 FROM generate_series(1,300000) g;" -c "ANALYZE sql_cfg_sort;"psql -U postgres -d beer_db -c "SET work_mem = '4MB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort ORDER BY val;"psql -U postgres -d beer_db -c "SET work_mem = '16MB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort ORDER BY val;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_cfg_sort (id int PRIMARY KEY, val numeric);" -c "INSERT INTO sql_cfg_sort SELECT g, random()*1000 FROM generate_series(1,300000) g;" -c "ANALYZE sql_cfg_sort;" SET CREATE TABLE INSERT 0 300000 ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "SET work_mem = '4MB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort ORDER BY val;" SET SET QUERY PLAN ----------------------------------------------------------------------------------------------------------- Sort (cost=37053.40..37803.40 rows=300000 width=15) (actual rows=300000.00 loops=1) Sort Key: val Sort Method: external merge Disk: 7400kB Buffers: shared hit=1637, temp read=925 written=928 -> Seq Scan on sql_cfg_sort (cost=0.00..4634.00 rows=300000 width=15) (actual rows=300000.00 loops=1) Buffers: shared hit=1634 Planning: Buffers: shared hit=69 read=3 Planning Time: 7.952 ms Execution Time: 7139.965 ms (10 rows) student@lab:~$ psql -U postgres -d beer_db -c "SET work_mem = '16MB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort ORDER BY val;" SET SET QUERY PLAN ----------------------------------------------------------------------------------------------------------- Sort (cost=31925.90..32675.90 rows=300000 width=15) (actual rows=300000.00 loops=1) Sort Key: val Sort Method: quicksort Memory: 14218kB Buffers: shared hit=1637 -> Seq Scan on sql_cfg_sort (cost=0.00..4634.00 rows=300000 width=15) (actual rows=300000.00 loops=1) Buffers: shared hit=1634 Planning: Buffers: shared hit=77 Planning Time: 14.921 ms Execution Time: 4242.965 ms (10 rows)
📐 Prove effective_cache_size Is Advisory, Directly
Force a plain Index Scan and compare its cost estimate and real execution at two very different effective_cache_size settings.
psql -U postgres -d beer_db -c "CREATE INDEX ON sql_cfg_sort(val);" -c "ANALYZE sql_cfg_sort;"psql -U postgres -d beer_db -c "SET enable_bitmapscan = off;" -c "SET enable_seqscan = off;" -c "SET effective_cache_size = '128kB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort WHERE val BETWEEN 100 AND 150;"psql -U postgres -d beer_db -c "SET enable_bitmapscan = off;" -c "SET enable_seqscan = off;" -c "SET effective_cache_size = '4GB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort WHERE val BETWEEN 100 AND 150;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX ON sql_cfg_sort(val);" -c "ANALYZE sql_cfg_sort;" SET CREATE INDEX ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "SET enable_bitmapscan = off;" -c "SET enable_seqscan = off;" -c "SET effective_cache_size = '128kB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort WHERE val BETWEEN 100 AND 150;" SET SET SET SET QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Index Scan using sql_cfg_sort_val_idx on sql_cfg_sort (cost=0.42..59390.45 rows=14814 width=15) (actual rows=14955.00 loops=1) Index Cond: ((val >= '100'::numeric) AND (val <= '150'::numeric)) Index Searches: 1 Buffers: shared hit=14950 read=52 Planning: Buffers: shared hit=75 read=3 Planning Time: 13.679 ms Execution Time: 203.422 ms (8 rows) student@lab:~$ psql -U postgres -d beer_db -c "SET enable_bitmapscan = off;" -c "SET enable_seqscan = off;" -c "SET effective_cache_size = '4GB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM sql_cfg_sort WHERE val BETWEEN 100 AND 150;" SET SET SET SET QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Scan using sql_cfg_sort_val_idx on sql_cfg_sort (cost=0.42..7032.46 rows=14814 width=15) (actual rows=14955.00 loops=1) Index Cond: ((val >= '100'::numeric) AND (val <= '150'::numeric)) Index Searches: 1 Buffers: shared hit=15002 Planning: Buffers: shared hit=83 Planning Time: 8.891 ms Execution Time: 141.500 ms (8 rows)
🔧 Bump maintenance_work_mem for an Index Build, Session-Only
Raise maintenance_work_mem with a plain SET for a large index build, confirming it needs no restart.
psql -U postgres -d beer_db -c "SET maintenance_work_mem = '64MB';" -c "SHOW maintenance_work_mem;" -c "CREATE INDEX ON sql_cfg_sort(id, val);"student@lab:~$ psql -U postgres -d beer_db -c "SET maintenance_work_mem = '64MB';" -c "SHOW maintenance_work_mem;" -c "CREATE INDEX ON sql_cfg_sort(id, val);" SET maintenance_work_mem ----------------------- 64MB (1 row) CREATE INDEX
Lab 3.5.1 complete. Memory tuning through effective settings and measured query plans:\n\n\n shared_buffers restarted and verified : ✅ 16MB -> 64MB\n work_mem fixing a real disk spill : ✅ 7140ms -> 4243ms, ~40% faster\n effective_cache_size proven advisory : ✅ cost 8x, execution unchanged\n maintenance_work_mem, session-only : ✅ no restart needed\n
Enable JavaScript to run the live terminal and track your progress.