######## 0. 실습 환경

$ docker run -d --init --name pglab --hostname pglab pg-internals:rel18-lab sleep infinity
122a918323ad4e53b3b4e5700996f2c3664a92f539b385d6fff8116219b0b13a
[exit=0]

$ postgres --version
$ initdb -D $PGDATA  > /home/postgres/initdb.log 2>&1 && echo "initdb ok"
postgres (PostgreSQL) 18.6
initdb ok
[exit=0]

$ pg_ctl -D $PGDATA -l /home/postgres/server.log start
$ psql -X -q -c "CREATE EXTENSION pg_buffercache"
waiting for server to start.... done
server started
[exit=0]


######## 1. 메모리 관련 기본 설정

$ psql -X <<'SQL'
$ SELECT name, setting, unit, short_desc
$ FROM pg_settings
$ WHERE name IN ('shared_buffers', 'wal_buffers', 'work_mem', 'hash_mem_multiplier',
$                'maintenance_work_mem', 'temp_buffers', 'block_size', 'shared_memory_size')
$ ORDER BY name;
$ SQL
         name         | setting | unit |                                       short_desc                                       
----------------------+---------+------+----------------------------------------------------------------------------------------
 block_size           | 8192    |      | Shows the size of a disk block.
 hash_mem_multiplier  | 2       |      | Multiple of "work_mem" to use for hash tables.
 maintenance_work_mem | 65536   | kB   | Sets the maximum memory to be used for maintenance operations.
 shared_buffers       | 16384   | 8kB  | Sets the number of shared memory buffers used by the server.
 shared_memory_size   | 150     | MB   | Shows the size of the server's main shared memory area (rounded up to the nearest MB).
 temp_buffers         | 1024    | 8kB  | Sets the maximum number of temporary buffers used by each session.
 wal_buffers          | 512     | 8kB  | Sets the number of disk-page buffers in shared memory for WAL.
 work_mem             | 4096    | kB   | Sets the maximum memory to be used for query workspaces.
(8 rows)

[exit=0]


######## 2. 공유 메모리는 무엇으로 채워져 있나

$ psql -X <<'SQL'
$ SELECT name, pg_size_pretty(allocated_size) AS size
$ FROM pg_shmem_allocations
$ ORDER BY allocated_size DESC
$ LIMIT 10;
$ SELECT count(*) AS entries, pg_size_pretty(sum(allocated_size)) AS total
$ FROM pg_shmem_allocations;
$ SQL
        name        |  size   
--------------------+---------
 Buffer Blocks      | 128 MB
 <anonymous>        | 4637 kB
 XLOG Ctl           | 4110 kB
 AioHandleIOV       | 2784 kB
                    | 2222 kB
 AioHandle          | 1566 kB
 AioHandleData      | 1392 kB
 Buffer Descriptors | 1024 kB
 transaction        | 517 kB
 Checkpointer Data  | 512 kB
(10 rows)

 entries | total  
---------+--------
      73 | 150 MB
(1 row)

[exit=0]


######## 3. 테이블을 읽으면 shared buffers에 페이지가 올라온다

$ psql -X <<'SQL'
$ CREATE TABLE small AS SELECT g AS id, repeat('x', 100) AS pad FROM generate_series(1, 100000) g;
$ SELECT pg_size_pretty(pg_relation_size('small')) AS size, pg_relation_size('small') / 8192 AS pages;
$ SELECT pg_buffercache_evict_relation('small');
$ SELECT * FROM pg_buffercache_summary();
$ SELECT count(*) FROM small;
$ SELECT * FROM pg_buffercache_summary();
$ SELECT count(*) AS buffers_of_small
$ FROM pg_buffercache
$ WHERE relfilenode = pg_relation_filenode('small');
$ SQL
SELECT 100000
 size  | pages 
-------+-------
 14 MB |  1728
(1 row)

 pg_buffercache_evict_relation 
-------------------------------
 (1728,1725,0)
(1 row)

 buffers_used | buffers_unused | buffers_dirty | buffers_pinned |  usagecount_avg   
--------------+----------------+---------------+----------------+-------------------
          300 |          16084 |            64 |              0 | 3.183333333333333
(1 row)

 count  
--------
 100000
(1 row)

 buffers_used | buffers_unused | buffers_dirty | buffers_pinned |   usagecount_avg   
--------------+----------------+---------------+----------------+--------------------
         2034 |          14350 |          1789 |              0 | 1.3421828908554572
(1 row)

 buffers_of_small 
------------------
             1728
(1 row)

[exit=0]


######## 4. 같은 페이지를 다시 읽으면 hit, 처음 읽으면 read

$ psql -X <<'SQL'
$ SELECT pg_buffercache_evict_relation('small');
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM small;
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM small;
$ SQL
 pg_buffercache_evict_relation 
-------------------------------
 (1728,1725,0)
(1 row)

                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1.00 loops=1)
   Buffers: shared read=1728
   ->  Gather (actual rows=2.00 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared read=1728
         ->  Partial Aggregate (actual rows=1.00 loops=2)
               Buffers: shared read=1728
               ->  Parallel Seq Scan on small (actual rows=50000.00 loops=2)
                     Buffers: shared read=1728
 Planning:
   Buffers: shared hit=27
(12 rows)

                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1728
   ->  Gather (actual rows=2.00 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared hit=1728
         ->  Partial Aggregate (actual rows=1.00 loops=2)
               Buffers: shared hit=1728
               ->  Parallel Seq Scan on small (actual rows=50000.00 loops=2)
                     Buffers: shared hit=1728
(10 rows)

[exit=0]


######## 4-1. read는 디스크일까, 운영체제 캐시일까

$ psql -X <<'SQL'
$ SET track_io_timing = on;
$ SET max_parallel_workers_per_gather = 0;
$ SELECT pg_buffercache_evict_relation('small');
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM small;
$ SQL
$ grep -E '^(MemTotal|Cached):' /proc/meminfo
SET
SET
 pg_buffercache_evict_relation 
-------------------------------
 (1728,0,0)
(1 row)

                       QUERY PLAN                        
---------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared read=1728
   I/O Timings: shared read=0.267
   ->  Seq Scan on small (actual rows=100000.00 loops=1)
         Buffers: shared read=1728
         I/O Timings: shared read=0.267
 Planning:
   Buffers: shared hit=24
(8 rows)

MemTotal:       32810480 kB
Cached:          2181352 kB
[exit=0]


######## 5. usage count: 자주 읽는 페이지일수록 오래 살아남는다

$ psql -X <<'SQL'
$ SELECT pg_buffercache_evict_relation('small');
$ SELECT count(*) FROM small;
$ SELECT usagecount, count(*) FROM pg_buffercache
$ WHERE relfilenode = pg_relation_filenode('small') GROUP BY 1 ORDER BY 1;
$ SELECT count(*) FROM small;
$ SELECT count(*) FROM small;
$ SELECT count(*) FROM small;
$ SELECT count(*) FROM small;
$ SELECT count(*) FROM small;
$ SELECT usagecount, count(*) FROM pg_buffercache
$ WHERE relfilenode = pg_relation_filenode('small') GROUP BY 1 ORDER BY 1;
$ SQL
 pg_buffercache_evict_relation 
-------------------------------
 (1728,0,0)
(1 row)

 count  
--------
 100000
(1 row)

 usagecount | count 
------------+-------
          1 |  1728
(1 row)

 count  
--------
 100000
(1 row)

 count  
--------
 100000
(1 row)

 count  
--------
 100000
(1 row)

 count  
--------
 100000
(1 row)

 count  
--------
 100000
(1 row)

 usagecount | count 
------------+-------
          5 |  1728
(1 row)

[exit=0]


######## 6. 큰 테이블 순차 스캔은 ring buffer만 쓴다

$ psql -X <<'SQL'
$ CREATE TABLE big AS SELECT g AS id, repeat('x', 100) AS pad FROM generate_series(1, 600000) g;
$ SELECT pg_size_pretty(pg_relation_size('big')) AS size,
$        pg_relation_size('big') / 8192 AS pages,
$        current_setting('shared_buffers') AS shared_buffers,
$        (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers') / 4 AS bulkread_threshold_pages;
$ SELECT pg_buffercache_evict_relation('big');
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM big;
$ SELECT count(*) AS buffers_of_big
$ FROM pg_buffercache
$ WHERE relfilenode = pg_relation_filenode('big');
$ SQL
SELECT 600000
 size  | pages | shared_buffers | bulkread_threshold_pages 
-------+-------+----------------+--------------------------
 81 MB | 10368 | 128MB          |                     4096
(1 row)

 pg_buffercache_evict_relation 
-------------------------------
 (2048,2025,0)
(1 row)

                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1.00 loops=1)
   Buffers: shared read=10368 dirtied=10345 written=10086
   ->  Gather (actual rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared read=10368 dirtied=10345 written=10086
         ->  Partial Aggregate (actual rows=1.00 loops=3)
               Buffers: shared read=10368 dirtied=10345 written=10086
               ->  Parallel Seq Scan on big (actual rows=200000.00 loops=3)
                     Buffers: shared read=10368 dirtied=10345 written=10086
 Planning:
   Buffers: shared hit=12
(12 rows)

 buffers_of_big 
----------------
            282
(1 row)

[exit=0]

$ psql -X <<'SQL'
$ SELECT current_setting('max_connections')::int
$      + current_setting('autovacuum_worker_slots')::int
$      + current_setting('max_worker_processes')::int
$      + current_setting('max_wal_senders')::int + 2 AS max_backends,
$        (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers')
$        / (current_setting('max_connections')::int
$           + current_setting('autovacuum_worker_slots')::int
$           + current_setting('max_worker_processes')::int
$           + current_setting('max_wal_senders')::int + 2 + 38) AS pin_limit_buffers;
$ SHOW io_combine_limit;
$ SHOW effective_io_concurrency;
$ SQL
 max_backends | pin_limit_buffers 
--------------+-------------------
          136 |                94
(1 row)

 io_combine_limit 
------------------
 128kB
(1 row)

 effective_io_concurrency 
--------------------------
 16
(1 row)

[exit=0]


######## 7. 수정된 페이지(dirty)는 체크포인트가 디스크로 내보낸다

$ psql -X <<'SQL'
$ CHECKPOINT;
$ SELECT buffers_dirty FROM pg_buffercache_summary();
$ UPDATE small SET pad = repeat('y', 100) WHERE id % 10 = 0;
$ SELECT buffers_dirty FROM pg_buffercache_summary();
$ SELECT count(*) FILTER (WHERE isdirty) AS dirty_of_small
$ FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('small');
$ SELECT buffers_written FROM pg_stat_checkpointer;
$ CHECKPOINT;
$ SELECT buffers_dirty FROM pg_buffercache_summary();
$ SELECT buffers_written FROM pg_stat_checkpointer;
$ SQL
CHECKPOINT
 buffers_dirty 
---------------
             0
(1 row)

UPDATE 10000
 buffers_dirty 
---------------
          1899
(1 row)

 dirty_of_small 
----------------
           1899
(1 row)

 buffers_written 
-----------------
             328
(1 row)

CHECKPOINT
 buffers_dirty 
---------------
             0
(1 row)

 buffers_written 
-----------------
            2227
(1 row)

[exit=0]


######## 8. work_mem을 넘는 정렬은 임시 파일로 간다

$ psql -X <<'SQL'
$ SET log_temp_files = 0;
$ SET work_mem = '4MB';
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT * FROM big ORDER BY pad, id DESC;
$ SET work_mem = '256MB';
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT * FROM big ORDER BY pad, id DESC;
$ SQL
$ grep 'temporary file' /home/postgres/server.log | tail -3
SET
SET
                             QUERY PLAN                              
---------------------------------------------------------------------
 Sort (actual rows=600000.00 loops=1)
   Sort Key: pad, id DESC
   Sort Method: external merge  Disk: 67576kB
   Buffers: shared hit=288 read=10086, temp read=16888 written=16910
   ->  Seq Scan on big (actual rows=600000.00 loops=1)
         Buffers: shared hit=282 read=10086
 Planning:
   Buffers: shared hit=49
(8 rows)

SET
                      QUERY PLAN                       
-------------------------------------------------------
 Sort (actual rows=600000.00 loops=1)
   Sort Key: pad, id DESC
   Sort Method: quicksort  Memory: 99577kB
   Buffers: shared hit=376 read=9992
   ->  Seq Scan on big (actual rows=600000.00 loops=1)
         Buffers: shared hit=376 read=9992
(6 rows)

2026-09-24 03:09:26.736 UTC [140] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp140.0", size 69197824
[exit=0]


######## 9. 해시 테이블은 work_mem x hash_mem_multiplier까지 쓴다

$ psql -X <<'SQL'
$ SET max_parallel_workers_per_gather = 0;
$ SET enable_mergejoin = off;
$ SET work_mem = '1MB';
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
$ SELECT count(*) FROM big b1 JOIN big b2 USING (id);
$ SET work_mem = '64MB';
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
$ SELECT count(*) FROM big b1 JOIN big b2 USING (id);
$ SQL
SET
SET
SET
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1037 read=19699, temp read=3526 written=3526
   ->  Hash Join (actual rows=600000.00 loops=1)
         Hash Cond: (b1.id = b2.id)
         Buffers: shared hit=1037 read=19699, temp read=3526 written=3526
         ->  Seq Scan on big b1 (actual rows=600000.00 loops=1)
               Buffers: shared hit=564 read=9804
         ->  Hash (actual rows=600000.00 loops=1)
               Buckets: 65536  Batches: 64  Memory Usage: 840kB
               Buffers: shared hit=473 read=9895, temp written=1700
               ->  Seq Scan on big b2 (actual rows=600000.00 loops=1)
                     Buffers: shared hit=473 read=9895
 Planning:
   Buffers: shared hit=130 read=3
(14 rows)

SET
                              QUERY PLAN                              
----------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1413 read=19323
   ->  Hash Join (actual rows=600000.00 loops=1)
         Hash Cond: (b1.id = b2.id)
         Buffers: shared hit=1413 read=19323
         ->  Seq Scan on big b1 (actual rows=600000.00 loops=1)
               Buffers: shared hit=752 read=9616
         ->  Hash (actual rows=600000.00 loops=1)
               Buckets: 2097152  Batches: 1  Memory Usage: 37478kB
               Buffers: shared hit=661 read=9707
               ->  Seq Scan on big b2 (actual rows=600000.00 loops=1)
                     Buffers: shared hit=661 read=9707
(12 rows)

[exit=0]


######## 9-1. work_mem은 쿼리 하나가 아니라 노드마다 잡힌다

$ psql -X <<'SQL'
$ SET max_parallel_workers_per_gather = 0;
$ SET enable_hashjoin = off;
$ SET work_mem = '32MB';
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
$ SELECT count(*) FROM big b1 JOIN big b2 USING (id);
$ SQL
SET
SET
SET
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1790 read=18950
   ->  Merge Join (actual rows=600000.00 loops=1)
         Merge Cond: (b1.id = b2.id)
         Buffers: shared hit=1790 read=18950
         ->  Sort (actual rows=600000.00 loops=1)
               Sort Key: b1.id
               Sort Method: quicksort  Memory: 24577kB
               Buffers: shared hit=850 read=9522
               ->  Seq Scan on big b1 (actual rows=600000.00 loops=1)
                     Buffers: shared hit=846 read=9522
         ->  Materialize (actual rows=600000.00 loops=1)
               Storage: Memory  Maximum Storage: 17kB
               Buffers: shared hit=940 read=9428
               ->  Sort (actual rows=600000.00 loops=1)
                     Sort Key: b2.id
                     Sort Method: quicksort  Memory: 24577kB
                     Buffers: shared hit=940 read=9428
                     ->  Seq Scan on big b2 (actual rows=600000.00 loops=1)
                           Buffers: shared hit=940 read=9428
 Planning:
   Buffers: shared hit=139
(22 rows)

[exit=0]


######## 10. backend 개인 메모리: 메모리 컨텍스트

$ psql -X <<'SQL'
$ SELECT name, level, pg_size_pretty(total_bytes) AS total, pg_size_pretty(used_bytes) AS used
$ FROM pg_backend_memory_contexts
$ ORDER BY total_bytes DESC
$ LIMIT 8;
$ SELECT count(*) AS contexts, pg_size_pretty(sum(total_bytes)) AS total
$ FROM pg_backend_memory_contexts;
$ SQL
          name           | level | total  |   used    
-------------------------+-------+--------+-----------
 CacheMemoryContext      |     2 | 512 kB | 449 kB
 Timezones               |     2 | 102 kB | 99 kB
 TopMemoryContext        |     1 | 97 kB  | 91 kB
 MessageContext          |     2 | 64 kB  | 32 kB
 WAL record construction |     2 | 49 kB  | 42 kB
 ExecutorState           |     4 | 48 kB  | 39 kB
 TupleSort main          |     5 | 32 kB  | 25 kB
 TransactionAbortContext |     2 | 32 kB  | 240 bytes
(8 rows)

 contexts |  total  
----------+---------
      124 | 1495 kB
(1 row)

[exit=0]


######## 11. 임시 테이블은 backend 개인 버퍼(temp_buffers)를 쓴다

$ psql -X <<'SQL'
$ CREATE TEMP TABLE tmp AS SELECT g AS id FROM generate_series(1, 100000) g;
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM tmp;
$ SELECT count(*) AS shared_buffers_used_by_tmp
$ FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('tmp');
$ SQL
SELECT 100000
                      QUERY PLAN                       
-------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: local hit=448
   ->  Seq Scan on tmp (actual rows=100000.00 loops=1)
         Buffers: local hit=448
 Planning:
   Buffers: shared hit=22
(6 rows)

 shared_buffers_used_by_tmp 
----------------------------
                          0
(1 row)

[exit=0]


######## 12. WAL buffers가 작으면 backend가 직접 WAL을 써야 한다

$ psql -X -c "SHOW wal_buffers"
$ psql -X -q -c "SELECT pg_stat_reset_shared('wal')"
$ psql -X -q -c "INSERT INTO big SELECT g, repeat('z', 100) FROM generate_series(1, 300000) g"
$ psql -X -c "SELECT wal_records, pg_size_pretty(wal_bytes) AS wal_bytes, wal_buffers_full FROM pg_stat_wal"
 wal_buffers 
-------------
 4MB
(1 row)

 pg_stat_reset_shared 
----------------------
 
(1 row)

 wal_records | wal_bytes | wal_buffers_full 
-------------+-----------+------------------
      300003 | 46 MB     |             5368
(1 row)

[exit=0]

$ psql -X -q -c "ALTER SYSTEM SET wal_buffers = '64kB'"
$ pg_ctl -D $PGDATA -l /home/postgres/server.log restart -m fast > /dev/null
$ psql -X -c "SHOW wal_buffers"
$ psql -X -q -c "SELECT pg_stat_reset_shared('wal')"
$ psql -X -q -c "INSERT INTO big SELECT g, repeat('z', 100) FROM generate_series(1, 300000) g"
$ psql -X -c "SELECT wal_records, pg_size_pretty(wal_bytes) AS wal_bytes, wal_buffers_full FROM pg_stat_wal"
 wal_buffers 
-------------
 64kB
(1 row)

 pg_stat_reset_shared 
----------------------
 
(1 row)

 wal_records | wal_bytes | wal_buffers_full 
-------------+-----------+------------------
      300004 | 46 MB     |             5874
(1 row)

[exit=0]

done