데이터 저장 구조

+----------------+---------------------------------+
| PageHeaderData | linp1 linp2 linp3 ...           |
+-----------+----+---------------------------------+
| ... linpN |                                      |
+-----------+--------------------------------------+
|           ^ pd_lower                             |
|                                                  |
|             v pd_upper                           |
+-------------+------------------------------------+
|             | tupleN ...                         |
+-------------+------------------+-----------------+
|       ... tuple3 tuple2 tuple1 | "special space" |
+--------------------------------+-----------------+
                                 ^ pd_special

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

1b13b5cd6424dd13a3b45ed580132f054e038746dcefb63050f615baafe0b06a
[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 pageinspect"

waiting for server to start.... done
server started
[exit=0]

psql -X <<'SQL'
CREATE TABLE fruit (id int, name text);
INSERT INTO fruit VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');
SELECT oid AS db_oid, datname FROM pg_database WHERE datname = current_database();
SELECT 'fruit'::regclass::oid AS table_oid, pg_relation_filenode('fruit') AS filenode,
       pg_relation_filepath('fruit') AS path;
SQL

CREATE TABLE
INSERT 0 3
 db_oid | datname  
--------+----------
      5 | postgres
(1 row)

 table_oid | filenode |     path     
-----------+----------+--------------
     16430 |    16430 | base/5/16430
(1 row)

[exit=0]

F=$(psql -X -At -c "SELECT pg_relation_filepath('fruit')")
ls -l $PGDATA/$F*
psql -X -q -c "VACUUM fruit"
ls -l $PGDATA/$F*
pg_controldata $PGDATA | grep -E 'Database block size|Blocks per segment|Data page checksum'

-rw------- 1 postgres postgres 8192 Sep 24 03:06 /var/lib/postgresql/data/base/5/16430
-rw------- 1 postgres postgres  8192 Sep 24 03:06 /var/lib/postgresql/data/base/5/16430
-rw------- 1 postgres postgres 24576 Sep 24 03:06 /var/lib/postgresql/data/base/5/16430_fsm
-rw------- 1 postgres postgres  8192 Sep 24 03:06 /var/lib/postgresql/data/base/5/16430_vm
Database block size:                  8192
Blocks per segment of large relation: 131072
Data page checksum version:           1
[exit=0]

psql -X <<'SQL'
SELECT * FROM page_header(get_raw_page('fruit', 0));
SQL

    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/17E5C60 |        0 |     4 |    36 |  8072 |    8192 |     8192 |       4 |         0
(1 row)

[exit=0]

psql -X -q -c "CHECKPOINT"
F=$(psql -X -At -c "SELECT pg_relation_filepath('fruit')")
xxd -l 40 $PGDATA/$F
xxd -s 8096 -l 96 $PGDATA/$F
psql -X -c "SELECT checksum AS checksum_in_buffer, page_checksum(get_raw_page('fruit', 0), 0) AS computed, to_hex(page_checksum(get_raw_page('fruit', 0), 0) & 65535) AS computed_hex FROM page_header(get_raw_page('fruit', 0))"

00000000: 0000 0000 605c 7e01 17d0 0400 2400 881f  ....`\~.....$...
00000010: 0020 0420 0000 0000 d89f 4400 b09f 4600  . . ......D...F.
00000020: 889f 4600 0000 0000                      ..F.....
00001fa0: 0300 0000 0f63 6865 7272 7900 0000 0000  .....cherry.....
00001fb0: f202 0000 0000 0000 0000 0000 0000 0000  ................
00001fc0: 0200 0200 0209 1800 0200 0000 0f62 616e  .............ban
00001fd0: 616e 6100 0000 0000 f202 0000 0000 0000  ana.............
00001fe0: 0000 0000 0000 0000 0100 0200 0209 1800  ................
00001ff0: 0100 0000 0d61 7070 6c65 0000 0000 0000  .....apple......
 checksum_in_buffer | computed | computed_hex 
--------------------+----------+--------------
                  0 |   -12265 | d017
(1 row)

[exit=0]

psql -X <<'SQL'
SELECT lp, lp_off, lp_flags, lp_len, t_xmin, t_xmax, t_ctid, t_infomask2, t_infomask, t_hoff, t_data
FROM heap_page_items(get_raw_page('fruit', 0));
SELECT lp, raw_flags, combined_flags
FROM heap_page_items(get_raw_page('fruit', 0)),
     LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2);
SQL

 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask | t_hoff |          t_data          
----+--------+----------+--------+--------+--------+--------+-------------+------------+--------+--------------------------
  1 |   8152 |        1 |     34 |    754 |      0 | (0,1)  |           2 |       2306 |     24 | \x010000000d6170706c65
  2 |   8112 |        1 |     35 |    754 |      0 | (0,2)  |           2 |       2306 |     24 | \x020000000f62616e616e61
  3 |   8072 |        1 |     35 |    754 |      0 | (0,3)  |           2 |       2306 |     24 | \x030000000f636865727279
(3 rows)

 lp |                        raw_flags                         | combined_flags 
----+----------------------------------------------------------+----------------
  1 | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
  2 | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
  3 | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
(3 rows)

[exit=0]

psql -X <<'SQL'
CREATE TABLE pad_bad  (a bool, b bigint, c bool, d bigint);
CREATE TABLE pad_good (b bigint, d bigint, a bool, c bool);
INSERT INTO pad_bad  VALUES (true, 1, true, 2);
INSERT INTO pad_good VALUES (1, 2, true, true);
SELECT 'pad_bad' AS tbl, lp_len, t_hoff, t_data FROM heap_page_items(get_raw_page('pad_bad', 0))
UNION ALL
SELECT 'pad_good', lp_len, t_hoff, t_data FROM heap_page_items(get_raw_page('pad_good', 0));
SELECT attname, typname, typlen, typalign
FROM pg_attribute a JOIN pg_type t ON t.oid = a.atttypid
WHERE attrelid = 'pad_bad'::regclass AND attnum > 0 ORDER BY attnum;
SQL

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
   tbl    | lp_len | t_hoff |                               t_data                               
----------+--------+--------+--------------------------------------------------------------------
 pad_bad  |     56 |     24 | \x0100000000000000010000000000000001000000000000000200000000000000
 pad_good |     42 |     24 | \x010000000000000002000000000000000101
(2 rows)

 attname | typname | typlen | typalign 
---------+---------+--------+----------
 a       | bool    |      1 | c
 b       | int8    |      8 | d
 c       | bool    |      1 | c
 d       | int8    |      8 | d
(4 rows)

[exit=0]

psql -X <<'SQL'
INSERT INTO fruit VALUES (4, NULL);
SELECT lp, lp_len, t_hoff, t_bits, t_infomask, t_data
FROM heap_page_items(get_raw_page('fruit', 0)) WHERE lp IN (3, 4);
SQL

INSERT 0 1
 lp | lp_len | t_hoff |  t_bits  | t_infomask |          t_data          
----+--------+--------+----------+------------+--------------------------
  3 |     35 |     24 |          |       2306 | \x030000000f636865727279
  4 |     28 |     24 | 10000000 |       2049 | \x04000000
(2 rows)

[exit=0]

psql -X <<'SQL'
CREATE TABLE narrow (id int);
INSERT INTO narrow SELECT generate_series(1, 10000);
CREATE TABLE narrow_ff (id int) WITH (fillfactor = 50);
INSERT INTO narrow_ff SELECT generate_series(1, 10000);
SELECT 'narrow' AS tbl, pg_relation_size('narrow') / 8192 AS pages,
       (SELECT count(*) FROM heap_page_items(get_raw_page('narrow', 0))) AS rows_in_page0,
       (SELECT lower || '/' || upper FROM page_header(get_raw_page('narrow', 0))) AS lower_upper
UNION ALL
SELECT 'narrow_ff', pg_relation_size('narrow_ff') / 8192,
       (SELECT count(*) FROM heap_page_items(get_raw_page('narrow_ff', 0))),
       (SELECT lower || '/' || upper FROM page_header(get_raw_page('narrow_ff', 0)));
SQL

CREATE TABLE
INSERT 0 10000
CREATE TABLE
INSERT 0 10000
    tbl    | pages | rows_in_page0 | lower_upper 
-----------+-------+---------------+-------------
 narrow    |    45 |           226 | 928/960
 narrow_ff |    89 |           113 | 476/4576
(2 rows)

[exit=0]

psql -X <<'SQL'
CREATE TABLE doc (id int, body text);
SELECT reltoastrelid::regclass AS toast_table FROM pg_class WHERE relname = 'doc';
INSERT INTO doc VALUES
  (1, repeat('a', 100)),
  (2, repeat('a', 100000)),
  (3, (SELECT string_agg(md5(g::text), '') FROM generate_series(1, 3125) g));
SELECT id, octet_length(body) AS original_bytes, pg_column_size(body) AS stored_bytes,
       pg_column_compression(body) AS compression
FROM doc ORDER BY id;
SELECT lp, lp_len FROM heap_page_items(get_raw_page('doc', 0));
SQL

CREATE TABLE
       toast_table       
-------------------------
 pg_toast.pg_toast_16447
(1 row)

INSERT 0 3
 id | original_bytes | stored_bytes | compression 
----+----------------+--------------+-------------
  1 |            100 |          101 | 
  2 |         100000 |         1156 | pglz
  3 |         100000 |       100000 | 
(3 rows)

 lp | lp_len 
----+--------
  1 |    129
  2 |   1184
  3 |     46
(3 rows)

[exit=0]

T=$(psql -X -At -c "SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'doc'")
psql -X <<SQL
SELECT chunk_id, count(*) AS chunks, min(chunk_seq) AS first_seq, max(chunk_seq) AS last_seq,
       max(octet_length(chunk_data)) AS max_chunk_bytes, sum(octet_length(chunk_data)) AS total_bytes
FROM $T GROUP BY chunk_id;
SQL

 chunk_id | chunks | first_seq | last_seq | max_chunk_bytes | total_bytes 
----------+--------+-----------+----------+-----------------+-------------
    16452 |     51 |         0 |       50 |            1996 |      100000
(1 row)

[exit=0]

psql -X <<'SQL'
CREATE TABLE edge (id int, body text);
ALTER TABLE edge ALTER COLUMN body SET STORAGE EXTERNAL;
INSERT INTO edge
SELECT n, left((SELECT string_agg(md5(g::text), '') FROM generate_series(1, 100) g), n)
FROM (VALUES (1990), (2000), (2001), (2010)) v(n);
SELECT e.id AS body_bytes, pg_column_size(e.body) AS stored_bytes, h.lp_len AS tuple_bytes
FROM edge e JOIN heap_page_items(get_raw_page('edge', 0)) h ON h.t_ctid = e.ctid
ORDER BY e.id;
SQL

CREATE TABLE
ALTER TABLE
INSERT 0 4
 body_bytes | stored_bytes | tuple_bytes 
------------+--------------+-------------
       1990 |         1994 |        2022
       2000 |         2004 |        2032
       2001 |         2001 |          46
       2010 |         2010 |          46
(4 rows)

[exit=0]

psql -X <<'SQL'
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'doc'::regclass AND attnum > 0;
CREATE TABLE doc_ext (id int, body text);
ALTER TABLE doc_ext ALTER COLUMN body SET STORAGE EXTERNAL;
INSERT INTO doc_ext VALUES (2, repeat('a', 100000));
SELECT 'doc (EXTENDED)' AS tbl, pg_column_size(body) AS stored_bytes, pg_column_compression(body) AS compression FROM doc WHERE id = 2
UNION ALL
SELECT 'doc_ext (EXTERNAL)', pg_column_size(body), pg_column_compression(body) FROM doc_ext;
SQL

 attname | attstorage 
---------+------------
 id      | p
 body    | x
(2 rows)

CREATE TABLE
ALTER TABLE
INSERT 0 1
        tbl         | stored_bytes | compression 
--------------------+--------------+-------------
 doc (EXTENDED)     |         1156 | pglz
 doc_ext (EXTERNAL) |       100000 | 
(2 rows)

[exit=0]