######## 0. 실습 환경 $ 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] ######## 1. 테이블은 파일이다 $ 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] ######## 2. 페이지 헤더 $ 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] ######## 3. 파일의 실제 바이트 $ 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] ######## 4. line pointer와 튜플 헤더 $ 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] ######## 5. 열 순서와 정렬 패딩 $ 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] ######## 6. NULL은 비트맵 한 비트 $ 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] ######## 7. 한 페이지에 들어가는 행 수와 fillfactor $ 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] ######## 8. 큰 값은 TOAST로 $ 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 < 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] done