PostgreSQL Storage Internals: เข้าใจ Page, Tuple, TOAST และโครงสร้าง Data Directory

PostgreSQL storage internals สรุปได้เป็นหน่วยซ้อนกันไม่กี่ชั้น ทุก table ถูกเก็บเป็นลำดับของ page ขนาด 8KB แต่ละ page บรรจุ tuple (เวอร์ชันของ row) ได้จำนวนหนึ่ง page เหล่านี้ถูกเก็บใน segment file ที่ใหญ่ได้ไม่เกิน 1GB ส่วนค่าที่ใหญ่เกินกว่าจะอยู่ใน page ได้สบาย ๆ จะถูกย้ายไปเก็บในตารางข้าง ๆ ด้วย TOAST ไฟล์ทั้งหมดนี้อยู่ใน data directory เดียวที่เรียกว่า database cluster ซึ่งสร้างด้วย initdb

โครงสร้างนี้ส่งผลตรงต่อ performance และการวางแผน capacity เพราะ PostgreSQL อ่านและเขียนทีละทั้ง page ไม่เคยอ่านทีละ row จำนวน row ต่อ page จึงเป็นตัวกำหนดว่าการ scan หนึ่งครั้งต้องทำ I/O เท่าไร บทความนี้ไล่ทีละชั้น คำนวณขนาดของ table 5 ล้าน row ให้ดูแบบละเอียด แล้วปิดด้วยแผนผังของ data directory ตัวอย่างทั้งหมดใช้ PostgreSQL 16

Page: หน่วย 8KB ของ PostgreSQL storage

Page (หรือ block) คือหน่วยเล็กที่สุดที่ PostgreSQL ย้ายไปมาระหว่าง disk กับ memory ขนาดคือ 8KB (8,192 bytes) ซึ่งกำหนดตายตัวตอน compile แต่ละ page เป็นของ relation เดียวเท่านั้น page ของตาราง orders จะไม่มี row ของ customers ปนอยู่ และ page ของ index ก็แยกจาก page ของ table

Heap (table) page มีหน้าตาแบบนี้:

+-------------+----------------------------+-------------------+---------------------------+
| Page header | Line pointers (4 B each) ->|    free space     |<- tuples (from the end)   |
|  24 bytes   |  grow forward              |                   |   grow backward           |
+-------------+----------------------------+-------------------+---------------------------+

• Page header (24 bytes) เก็บ LSN ของ WAL record ล่าสุดที่แก้ page นี้, checksum และ offset ที่บอกว่า free space เริ่มและจบตรงไหน
• Line pointer (item identifier) คือช่องขนาด 4 bytes ที่ชี้ไปยังตำแหน่งของ tuple แต่ละตัวใน page
• Tuple ถูกเขียนจากท้าย page ย้อนมาด้านหน้า
• Free space คือพื้นที่ที่เหลือตรงกลาง เมื่อหมด row ใหม่จะไปลง page อื่น

Page ของ index จะมี "special space" ท้าย page ไว้เก็บข้อมูลเฉพาะของ index ส่วน heap page ไม่ได้ใช้

เพราะ tuple ถูกอ้างถึงผ่าน line pointer ที่อยู่จริงของ row จึงเป็นคู่ (หมายเลข page, หมายเลข line pointer) ซึ่งดูได้จากคอลัมน์ซ่อน ctid index เก็บที่อยู่แบบนี้ไว้ และใช้มันกระโดดไปหา row ใน heap ถ้าอยากรู้ว่าเรื่องนี้มีผลต่อการออกแบบ index อย่างไร ดูกลยุทธ์การทำ index ที่ใช้ได้จริง

Tuple และคอลัมน์ที่ซ่อนอยู่

Tuple คือหนึ่งเวอร์ชันของ row นอกจากข้อมูลของเราแล้ว ทุก tuple ยังมี header ขนาด 23 bytes ซึ่งถูก pad เป็น 24 บนระบบ 64-bit ฟิลด์ที่สำคัญคือ:

ดูคอลัมน์ซ่อนได้ตรง ๆ:

SELECT ctid, xmin, xmax, id, status
FROM orders
LIMIT 3;

xmin และ xmax คือสิ่งที่ PostgreSQL ใช้ตัดสินว่า transaction ไหนเห็น row เวอร์ชันไหน กลไกนี้คือ MVCC ซึ่งอธิบายไว้ในACID และ MVCC ฉบับเข้าใจง่าย ในมุมของ storage สิ่งที่ต้องจำคือ UPDATE เขียน tuple ใหม่และทิ้งตัวเก่าไว้จนกว่า vacuum จะมาเก็บ table ที่ถูก update บ่อยจึงกิน page มากกว่าที่จำนวน row ที่ยังมีชีวิตอยู่บอก

ถ้าอยากเปิดดูข้างใน page ให้ใช้ extension pageinspect (ต้องเป็น superuser):

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('orders', 0));

ผลลัพธ์แต่ละแถวคือ line pointer หนึ่งตัว บอก offset ใน page, ความยาวของ tuple และฟิลด์ใน header ของ tuple

Segment file: table บน disk

แต่ละ table ถูกเก็บในไฟล์ที่ตั้งชื่อตาม relfilenode ซึ่งเป็นตัวเลขที่ตอนแรกมักเท่ากับ OID ของ table ไฟล์จะโตไปจนถึง 1GB แล้ว PostgreSQL จะเริ่ม segment ถัดไป:

16402      first 1GB of the table
16402.1    next 1GB
16402.2    ...
16402_fsm  free space map: which pages have room for new tuples
16402_vm   visibility map: which pages contain only rows visible to everyone

การจำกัดไว้ที่ 1GB ทำให้ไฟล์จัดการได้ง่ายบนทุก filesystem ที่ PostgreSQL รองรับ index ก็ใช้รูปแบบเดียวกันโดยมี relfilenode ของตัวเอง

อย่าเดา path เอง ให้ถาม PostgreSQL:

SELECT pg_relation_filepath('orders');
-- base/16384/16402

16384 คือ OID ของ database และ 16402 คือ relfilenode ค่า relfilenode เปลี่ยนได้หลัง TRUNCATE, VACUUM FULL หรือ CLUSTER เพราะคำสั่งเหล่านี้เขียน table ใหม่ลงไฟล์ใหม่ จึงควรถามทุกครั้ง ไม่ควร hard-code

TOAST: เก็บค่าขนาดใหญ่ไว้นอก page

Tuple ต้องอยู่ใน page 8KB เดียวให้ได้ text ยาว ๆ, jsonb ก้อนใหญ่ หรือ bytea จึงใส่ไม่พอ PostgreSQL แก้ด้วย TOAST (The Oversized-Attribute Storage Technique)

เมื่อ row ใหญ่เกินประมาณ 2KB (ค่า TOAST_TUPLE_THRESHOLD ราวหนึ่งในสี่ของ page) PostgreSQL จะไล่จัดการคอลัมน์ที่มีความยาวแปรผัน:

1. บีบอัด ค่านั้นไว้ในที่เดิม (ค่าเริ่มต้นคือ pglz หรือเลือก lz4 ได้)
2. ถ้า row ยังใหญ่อยู่ ให้ ย้าย ค่านั้นออกไปไว้ใน TOAST table ของ table นั้น
3. หั่น ค่าที่ย้ายออกไปเป็น chunk ละประมาณ 2KB แล้วเก็บเป็น row ใน TOAST table

Row หลักจะเหลือแค่ pointer ขนาด 18 bytes ที่ชี้ไปยัง chunk:

Main table row:  [ id | title | body -> toast pointer ]
TOAST table:     [ chunk_id, chunk_seq=0, data ][ chunk_id, chunk_seq=1, data ] ...

เฉพาะ type ที่มีความยาวแปรผัน (text, varchar, jsonb, bytea, array ฯลฯ) เท่านั้นที่ TOAST ได้ ค่าหนึ่งค่าใหญ่ได้สูงสุด 1GB

แต่ละคอลัมน์มี storage strategy ที่เปลี่ยนได้:

EXTERNAL เหมาะกับ text หรือ bytea ขนาดใหญ่ที่มักอ่านด้วย substring() เพราะ PostgreSQL ดึงเฉพาะ chunk ที่ต้องใช้ได้ ส่วนการเปลี่ยน compression เป็น lz4 มักเร็วกว่า pglz:

-- find the TOAST table behind a table
SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'articles';

ALTER TABLE articles ALTER COLUMN body SET STORAGE EXTERNAL;
ALTER TABLE articles ALTER COLUMN payload SET COMPRESSION lz4;  -- applies to new values

ผลในทางปฏิบัติสองข้อ:

• SELECT * บน table ที่มีคอลัมน์ใหญ่ถูก TOAST ไว้ จะดึงและคลายการบีบอัดค่าพวกนั้นมาด้วย แม้แอปจะไม่ได้ใช้ ให้ระบุเฉพาะคอลัมน์ที่ต้องการ
• การ update คอลัมน์อื่นไม่ได้ copy ค่าที่ถูก TOAST ไว้และไม่ได้เปลี่ยน tuple ใหม่จะใช้ pointer เดิมต่อ

ตัวอย่างการคำนวณ: ขนาดของ table 5 ล้าน row

สมมติว่ากำลังจะ load ข้อมูล 5 ล้าน row ลง table ที่มีห้าคอลัมน์ a ถึง e คนละ data type และมีคอลัมน์ text อยู่ด้วย ให้ load ตัวอย่างบางส่วนแล้ววัดขนาดเฉลี่ยของ row ที่เก็บจริง:

SELECT avg(pg_column_size(t.*)) AS avg_row_bytes
FROM events t;

pg_column_size บนทั้ง row จะคืนขนาดที่รวม tuple header 24 bytes แล้ว สมมติได้ 849 bytes ซึ่งต่ำกว่า threshold ของ TOAST ที่ราว 2KB ทุก row จึงอยู่ใน table หลัก

ขั้นที่ 1: กี่ row ต่อ page ประมาณแบบเร็ว ๆ 8,192 / 849 = 9.6 ได้ 9 row คำนวณละเอียดขึ้นก็ได้คำตอบเดียวกันในกรณีนี้:

• พื้นที่ใช้ได้: 8,192 − 24 (page header) = 8,168 bytes
• Tuple แต่ละตัวถูก pad ให้เป็นพหุคูณของ 8: 849 กลายเป็น 856 บวก line pointer อีก 4 bytes รวม 860 bytes ต่อ row
• 8,168 / 860 = 9.5 ได้ 9 row ต่อ page เหลือพื้นที่ว่างราว 428 bytes

ขั้นที่ 2: จำนวน page 5,000,000 / 9 = 555,556 page

ขั้นที่ 3: แปลงเป็น bytes 555,556 × 8,192 = 4,551,114,752 bytes หรือประมาณ 4.24GB

ขั้นที่ 4: จำนวน segment file หนึ่ง segment คือ 1GB = 1,024 × 1,024 × 1,024 = 1,073,741,824 bytes หารด้วย 8,192 ได้ 131,072 page ต่อ segment แล้ว 555,556 / 131,072 = 4.24 table นี้จึงต้องใช้ 5 ไฟล์ คือ 16402, 16402.1, 16402.2 และ 16402.3 ที่เต็ม กับ 16402.4 ที่เก็บ 31,268 page ที่เหลือ (ราว 244MB)

หลัง load แล้วตรวจตัวเลขที่ประมาณไว้:

SELECT pg_size_pretty(pg_relation_size('events'))       AS heap,
       pg_size_pretty(pg_table_size('events'))          AS heap_toast_fsm_vm,
       pg_size_pretty(pg_total_relation_size('events')) AS with_indexes;

Overhead มีผลมากขึ้นกับ row ที่แคบ row ที่มี bigint, integer และ timestamptz มีข้อมูลแค่ 20 bytes ถ้าคิดแบบตรง ๆ จะได้ 409 row ต่อ page แต่เมื่อรวม padding (ข้อมูลกลายเป็น 24 bytes), header 24 bytes และ line pointer แต่ละ row ใช้จริง 52 bytes ใส่ได้แค่ 157 row ลำดับคอลัมน์ก็มีผลกับ padding ด้วย การวางคอลัมน์ 8 bytes ไว้ก่อนคอลัมน์ 4 bytes ช่วยลดช่องว่างได้

Database cluster และ initdb

ใน PostgreSQL คำว่า database cluster ไม่ได้หมายถึงกลุ่มของ server แต่หมายถึง data directory หนึ่งอันที่มี postmaster หนึ่งตัวดูแลบน port เดียว ข้างในมี database กี่ตัวก็ได้ที่ใช้ role, config และ WAL ชุดเดียวกัน ส่วน server จัดการ process และ memory อย่างไรอธิบายไว้ในPostgreSQL architecture

initdb ใช้สร้าง cluster ใหม่:

initdb -D /var/lib/postgresql/16/data --encoding=UTF8 --data-checksums

-D กำหนด data directory ส่วน --data-checksums เปิด checksum ระดับ page ซึ่งปิดอยู่โดยค่าเริ่มต้นใน PostgreSQL 16 และเปิดทีหลังไม่ได้ง่าย ๆ ต้องใช้เครื่องมือ pg_checksums และต้องหยุดระบบ บน Debian และ Ubuntu คำสั่ง pg_createcluster 16 main จะเรียก initdb ให้ และวางไฟล์ config ไว้ที่ /etc/postgresql/16/main

การ start, stop และ reload:

pg_ctl -D /var/lib/postgresql/16/data start
pg_ctl -D /var/lib/postgresql/16/data stop -m fast   # fast is the default mode
pg_ctl -D /var/lib/postgresql/16/data reload         # re-read config files
sudo systemctl restart postgresql                     # packaged installs on Linux

บน Windows ตัวติดตั้งจะลงทะเบียน service ที่สั่งได้ด้วย net start และ net stop (เช่น postgresql-x64-16) setting บางตัวอย่าง shared_buffers และ max_connections ต้อง restart ถึงจะมีผล ใช้ SELECT name FROM pg_settings WHERE pending_restart; ดูว่ามีค่าไหนรอ restart อยู่

โครงสร้างของ data directory

template0, template1 และ database ชื่อ postgres

initdb สร้าง database มาให้สามตัว:

• template1 คือต้นแบบที่ CREATE DATABASE copy โดยค่าเริ่มต้น อะไรที่เพิ่มลงไป เช่น extension จะไปโผล่ในทุก database ที่สร้างใหม่
• template0 คือสำเนาสะอาดที่ไม่อนุญาตให้ connect (datallowconn = false) จึงไม่มีใครแก้โดยไม่ตั้งใจ ใช้ตอน restore dump หรือเมื่อต้องการ encoding หรือ locale ที่ต่างออกไป
• postgres คือ database เปล่าที่เครื่องมือและ utility ต่าง ๆ ใช้ connect เป็นค่าเริ่มต้น ลบได้ แต่หลายเครื่องมือคาดว่าต้องมี

ตั้งแต่ PostgreSQL 15 OID ของทั้งสามตัวตายตัว template1 คือ 1, template0 คือ 4 และ postgres คือ 5 ดังนั้น base/5/ คือ database postgres เสมอ

CREATE DATABASE reports TEMPLATE template0 ENCODING 'UTF8';

ถ้า template1 ถูกใส่ของเกินมา สร้างใหม่จาก template0 ได้ โดยต้อง connect อยู่กับ database อื่น:

ALTER DATABASE template1 IS_TEMPLATE false;
DROP DATABASE template1;
CREATE DATABASE template1 TEMPLATE template0 IS_TEMPLATE true;

คำถามที่พบบ่อย

Page size ของ PostgreSQL คือเท่าไร?

ค่าเริ่มต้นคือ 8KB เปลี่ยนได้ด้วยการ compile PostgreSQL ใหม่ด้วย block size อื่นเท่านั้น ซึ่งแทบไม่คุ้ม

Table ใน PostgreSQL ใหญ่ได้สูงสุดเท่าไร?

32TB เมื่อใช้ page ขนาด 8KB table จะถูกแบ่งเป็น segment file ละ 1GB จึงไม่มีไฟล์ไหนใหญ่ขนาดนั้น

PostgreSQL ใช้ TOAST เมื่อไร?

เมื่อ row ใหญ่เกินประมาณ 2KB PostgreSQL จะบีบอัดค่าที่มีความยาวแปรผันก่อน แล้วค่อยย้ายออกไปเก็บใน TOAST table เป็น chunk ละราว 2KB

ทำไม table ใหญ่กว่าข้อมูลที่ insert เข้าไปมาก?

เพราะมี tuple header, padding, line pointer, row เวอร์ชันเก่าที่ update และ delete ทิ้งไว้ รวมถึง free space map และ visibility map เปรียบเทียบ pg_relation_size กับ pg_total_relation_size เพื่อดูว่าส่วนไหนเป็น index

ลบไฟล์เก่าใน pg_wal เพื่อคืนพื้นที่ได้ไหม?

ไม่ได้ การลบ WAL ด้วยมืออาจทำให้ cluster กู้คืนไม่ได้เลย ให้หาสาเหตุว่าทำไม WAL ถึงค้าง เช่น archive command ล้มเหลว หรือมี replication slot ที่ไม่ได้ใช้งานแล้ว

Checklist เรื่อง storage internals

• ประมาณจำนวน row ต่อ page จากขนาด row เฉลี่ยจริง รวม header 24 bytes, padding และ line pointer
• เรียงคอลัมน์ความยาวคงที่จากกว้างไปแคบ เพื่อลด padding
• เก็บค่าใหญ่ที่ไม่ค่อยได้อ่านไว้ในคอลัมน์ที่ไม่ได้ select เป็นปกติ และลองใช้ lz4
• หา path ของไฟล์ด้วย pg_relation_filepath แทนการเดา
• สร้าง cluster ใหม่ด้วย --data-checksums และอย่าแตะ pg_wal ด้วยมือ

ถ้ากำลังวางแผน capacity ให้ PostgreSQL ที่โตขึ้นเรื่อย ๆ Vectorkub ช่วยดูทั้งการออกแบบและตัวเลขได้