PostgreSQL

Open-source relational database ที่ Hussein Nasser แนะนำเป็น default choice ในปี 2024+ — รองรับทั้ง ACID, JSONB (flexible schema), partitioning, logical replication ครบ

Architecture

Heap-Organized Table

• ตารางเก็บใน heap ต่างหาก — primary index เป็น B+Tree ปกติชี้ไป heap
• Secondary index ชี้ไป tuple id (ctid) ตรง ๆ ใน heap → 1 lookup เร็วกว่า MySQL
• แต่ทุก UPDATE สร้าง tuple ใหม่ → ctid เปลี่ยน → secondary index ทุกอันต้อง update → write amplification

MVCC (Multi-Version Concurrency Control)

• เก็บหลาย version ของแถวใน heap — writer ไม่ block reader
• ไม่ใช้ undo log แบบดั้งเดิม — row เก่ายังอยู่ใน heap จนกว่า VACUUM มาเก็บ
• rollback เร็วมาก (แค่ mark transaction abort)
• ข้อเสีย: heap bloat → ต้อง VACUUM บ่อย

Page Size

• 8 KB ต่อ page (ค่า default)

HOT Update (Heap Only Tuple)

• ถ้าไม่มี indexed column ถูกแก้ + tuple ใหม่ fit ใน page เดียวกัน → secondary indexes ไม่ต้อง update
• ต้องตั้ง fillfactor < 100 เพื่อเผื่อพื้นที่ (default = 100)
• CREATE TABLE ... WITH (fillfactor = 80);

คำสั่งสำคัญ

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT ... ;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

• cost ใน EXPLAIN เป็น arbitrary unit ไม่ใช่ millisecond — ใช้เปรียบเทียบ query plans เท่านั้น

Index Management

CREATE INDEX CONCURRENTLY idx_name ON tbl(col);   -- ไม่ lock table
DROP INDEX CONCURRENTLY idx_name;
REINDEX INDEX CONCURRENTLY idx_name;              -- แก้ index bloat (PG 12+)

Maintenance

VACUUM (VERBOSE, ANALYZE) tbl;        -- reclaim space + update stats
VACUUM FULL tbl;                       -- aggressive (lock!)
ANALYZE tbl;                           -- update statistics only

Locking

SELECT * FROM tbl WHERE id=$1 FOR UPDATE;            -- X-lock
SELECT * FROM tbl WHERE id=$1 FOR SHARE;             -- S-lock
SELECT * FROM tbl WHERE id=$1 FOR UPDATE NOWAIT;     -- error if locked
SELECT * FROM tbl WHERE id=$1 FOR UPDATE SKIP LOCKED; -- ข้าม locked rows

Replication

SELECT * FROM pg_stat_replication;  -- check status (master)
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));  -- check lag (replica)

Configuration สำคัญ

Parameter	ค่าแนะนำ	หมายเหตุ
shared_buffers	25% ของ RAM	ตั้งใหญ่เกิน → swap → ช้ายิ่งกว่า disk
wal_level	replica	จำเป็นสำหรับ replication
synchronous_commit	on (production)	off = async เร็วแต่อาจหาย
log_statement	mod	log INSERT/UPDATE/DELETE

Isolation Levels

Postgres ใช้ Snapshot Isolation สำหรับ Repeatable Read → ป้องกัน phantom read ได้ด้วย (เกินมาตรฐาน SQL)

Authentication

• SCRAM-SHA-256 (default PG 14+) — ปลอดภัยกว่า MD5, server ไม่เคยเห็น plain password
• SSL modes: disable → allow → prefer → require → verify-ca → verify-full
• Production ต้องใช้ verify-full เสมอ

เปรียบเทียบกับ MySQL

ด้าน	PostgreSQL	MySQL
Table storage	Heap-organized	Clustered index (InnoDB)
Secondary index ชี้ไป	tuple id (ctid)	primary key
Read performance	1 lookup	2 lookups (secondary → PK → row)
Write amplification	สูงกว่า (ทุก index update)	ต่ำกว่า (secondary ชี้ PK ไม่เปลี่ยน)
Engine เปลี่ยนได้	ไม่ได้	ได้ (InnoDB, MyISAM, RocksDB)
Page size	8 KB	16 KB

เมื่อไหร่ควรใช้ Postgres

• Default choice สำหรับทุก project ใหม่
• ต้องการ ACID + SQL power เต็มรูปแบบ
• ต้องการ flexible schema → ใช้ JSONB column
• ต้องการ time-series → เพิ่ม TimescaleDB extension
• ต้องการ full-text search → built-in tsvector หรือ trigram index

Key Points

• Postgres เป็น default choice ปี 2024+ ตามคำแนะนำของ Hussein Nasser
• ใช้ heap-organized table + MVCC — writer ไม่ block reader
• VACUUM สำคัญมาก — ลืม VACUUM → stats outdated → planner ผิด → query ช้า
• HOT update ช่วยลด write amplification แต่ต้องตั้ง fillfactor
• เปลี่ยน storage engine ไม่ได้ (ต่างจาก MySQL)

Related

• MySQL — คู่เปรียบเทียบหลักตลอด course
• ACID — คุณสมบัติพื้นฐานที่ Postgres รองรับครบ
• Database Indexing — B+Tree index, EXPLAIN ANALYZE
• Database Replication — Master/Backup, sync/async
• Database Internals — Pages, Heap, IO
• WAL — Write-Ahead Log ที่ Postgres ใช้สำหรับ durability + recovery
• Connection Pooling — PgBouncer สำหรับ Postgres