메모리 구조

docker run -d --init --name pglab --hostname pglab pg-internals:rel18-lab sleep infinity

122a918323ad4e53b3b4e5700996f2c3664a92f539b385d6fff8116219b0b13a
[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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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