ย้าย schema PostgreSQL ไม่ต้องปิดระบบ: expand, migrate, contract

migration ที่ทำให้เว็บล่มส่วนใหญ่ไม่ใช่เพราะคำสั่งผิด แต่เพราะคำสั่งถูกที่รันผิดจังหวะ คือ schema เปลี่ยนไปแล้วแต่โค้ดเก่ายังรันอยู่ หรือ ALTER ตัวเดียวถือล็อกจนทุก request ต่อคิว บทความนี้เล่าวิธีเปลี่ยน schema PostgreSQL ขณะระบบยังรับงาน ด้วยแบบ expand → migrate → contract ล็อกที่ต้องรู้ และการ backfill ข้อมูลทีละก้อน

ตรวจกับเอกสาร PostgreSQL 18 (ALTER TABLE, CREATE INDEX, explicit locking, release notes 11, 12, 18) พฤติกรรมที่ใช้ในบทนี้มีตั้งแต่ PostgreSQL 12 ขึ้นไป ยกเว้นจุดที่ระบุ

ทำไม migration ถึงทำเว็บล่ม

ระหว่าง deploy มีช่วงที่โค้ดสองเวอร์ชันรันพร้อมกัน (rolling deploy, หลาย worker, หรือแค่ช่วงไม่กี่วินาทีระหว่าง migrate กับ restart) ถ้า migration ทำให้ schema ไม่เข้ากับโค้ดเวอร์ชันใดเวอร์ชันหนึ่ง เวอร์ชันนั้นจะ error ทันที

ปัญหาที่สองคือล็อก ALTER TABLE ส่วนใหญ่ต้องได้ล็อก ACCESS EXCLUSIVE ซึ่งชนกับทุกอย่าง รวมถึง SELECT ธรรมดา ถ้ามี query ยาวถือตารางอยู่ ALTER ต้องรอ และ ระหว่างที่ ALTER รอ request ที่มาทีหลังก็ต่อคิวหลัง ALTER แม้ ALTER ยังไม่ได้เริ่มทำงานเลย ตารางจึงค้างทั้งตารางเพราะ query ยาวตัวเดียวบวก ALTER ที่รออยู่

กฎข้อแรก: ทุก migration ต้องเข้ากันได้กับโค้ดทั้งเวอร์ชันก่อนและเวอร์ชันหลัง

expand → migrate → contract

แยกการเปลี่ยนแปลงที่ไม่เข้ากันออกเป็นสามจังหวะ แต่ละจังหวะ deploy ได้ และย้อนกลับได้

จังหวะ	schema	โค้ด
expand	เพิ่มของใหม่ (คอลัมน์, ตาราง, index) ไม่แตะของเก่า	เขียนทั้งเก่าและใหม่ อ่านจากเก่า
migrate	backfill ข้อมูลเก่าเข้าที่ใหม่ แล้วใส่ constraint	อ่านจากใหม่ ยังเขียนทั้งสอง
contract	ลบของเก่า	ใช้ของใหม่อย่างเดียว

จังหวะ expand ไม่ทำให้โค้ดเก่าพัง เพราะโค้ดเก่าไม่รู้จักคอลัมน์ใหม่ จังหวะ contract ไม่ทำให้โค้ดใหม่พัง เพราะโค้ดใหม่เลิกใช้ของเก่าไปแล้วตั้งแต่ deploy ก่อนหน้า ถ้า deploy ไหนพัง ย้อนกลับไปเวอร์ชันก่อนหน้าได้ทันทีเพราะ schema ยังรองรับ (เข้ากับการ deploy แบบสลับโฟลเดอร์และย้อนกลับได้พอดี)

ตัวอย่าง: เปลี่ยนชื่อคอลัมน์ใน 3 deploy

ตาราง users มีคอลัมน์ name อยากเปลี่ยนเป็น full_name แบบไม่มี downtime

Deploy 1 (expand) เพิ่มคอลัมน์ใหม่แบบ nullable ไม่มี default งานนี้แก้แค่ metadata

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN full_name text;

โค้ดเวอร์ชันนี้ เขียนทั้ง name และ full_name ทุกครั้งที่ insert/update แต่ยังอ่านจาก name จากนั้นรัน backfill (หัวข้อถัดไป) ให้แถวเก่ามี full_name ครบ

Deploy 2 (migrate) เมื่อ backfill เสร็จ ใส่ NOT NULL โดยไม่สแกนตารางขณะถือล็อกใหญ่

SET lock_timeout = '2s';
ALTER TABLE users ADD CONSTRAINT users_full_name_nn
  CHECK (full_name IS NOT NULL) NOT VALID;          -- ล็อกสั้น ไม่สแกน
ALTER TABLE users VALIDATE CONSTRAINT users_full_name_nn;  -- สแกน แต่ล็อกเบา
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;     -- ข้ามการสแกน เพราะมี CHECK ที่ valid แล้ว
ALTER TABLE users DROP CONSTRAINT users_full_name_nn;

โค้ดเวอร์ชันนี้ อ่านจาก full_name ยังเขียนทั้งสองคอลัมน์ เผื่อต้องย้อนกลับไปเวอร์ชัน 1

Deploy 3 (contract) โค้ดเลิกแตะ name ทั้งหมด deploy ให้เสร็จ แล้วค่อยลบ

SET lock_timeout = '2s';
ALTER TABLE users DROP COLUMN name;

DROP COLUMN ไม่ได้ลบข้อมูลจริงจากดิสก์ แค่ทำให้มองไม่เห็น จึงเร็ว พื้นที่จะค่อย ๆ คืนเมื่อแถวเดิมถูก update หรือคืนทันทีเมื่อตารางถูกเขียนใหม่ทั้งตาราง

บน PostgreSQL 18 มีทางลัดเพิ่ม คือใส่ NOT NULL constraint แบบ NOT VALID ได้โดยตรง แล้ว SET NOT NULL จะ validate ให้ แต่วิธี CHECK ข้างบนใช้ได้ตั้งแต่ 12 จึงยังเป็นค่าเริ่มต้นที่ปลอดภัย

ล็อกที่ต้องรู้

คำสั่ง	ล็อก	บล็อกอะไร	เขียนตารางใหม่ไหม
ADD COLUMN nullable หรือ default คงที่ (11+)	ACCESS EXCLUSIVE ชั่วครู่	ทุกอย่าง แต่สั้น	ไม่
ADD COLUMN default volatile เช่น clock_timestamp()	ACCESS EXCLUSIVE	ทุกอย่าง ตลอดการเขียนใหม่	ใช่ ทั้งตารางและ index
CREATE INDEX	SHARE	การเขียน (อ่านได้)	ไม่
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	ไม่บล็อกอ่าน/เขียน	ไม่
ADD CHECK ... NOT VALID	ACCESS EXCLUSIVE ชั่วครู่	ทุกอย่าง แต่สั้น	ไม่ สแกนก็ไม่
ADD FOREIGN KEY ... NOT VALID	SHARE ROW EXCLUSIVE ทั้งสองตาราง ชั่วครู่	การเขียน แต่สั้น	ไม่
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	ไม่บล็อกอ่าน/เขียน	ไม่ (สแกนอย่างเดียว)
SET NOT NULL	ACCESS EXCLUSIVE	ทุกอย่าง	ไม่ แต่สแกนทั้งตาราง ยกเว้นมี CHECK ที่ valid
DROP COLUMN	ACCESS EXCLUSIVE ชั่วครู่	ทุกอย่าง แต่สั้น	ไม่

สรุปง่าย ๆ งานที่ ต้องสแกนหรือเขียนทั้งตาราง ให้ไปทำในคำสั่งที่ถือล็อกเบา (CONCURRENTLY, VALIDATE) ส่วนคำสั่งที่ต้องถือล็อกหนักต้องเป็นงานแก้ metadata ที่เสร็จในเสี้ยววินาที

CREATE INDEX CONCURRENTLY

CREATE INDEX CONCURRENTLY users_full_name_idx ON users (full_name);

ข้อแลก: สแกนตารางสองรอบ และรอ transaction ที่ค้างอยู่ให้จบ จึงช้ากว่าแบบปกติมาก รันใน transaction block ไม่ได้ และถ้าล้มกลางทาง (deadlock, ค่าซ้ำใน unique index) จะเหลือ index สถานะ INVALID ที่ query ไม่ใช้แต่ยังต้องอัปเดตทุกครั้งที่เขียน ดูด้วย \d users ใน psql แล้ว DROP INDEX สร้างใหม่ หรือ REINDEX INDEX CONCURRENTLY

lock_timeout: ยอมล้มดีกว่าทำให้ทั้งตารางค้าง

ค่าเริ่มต้นของ lock_timeout คือ 0 คือรอไปเรื่อย ๆ ตั้งค่าไว้ก่อน DDL ทุกตัว ถ้ารอเกินกำหนด คำสั่งจะ error แทนที่จะขวางคิว เวลานับแยกต่อการขอล็อกแต่ละครั้ง

ตัวอย่างคำนวณ: มีรายงานรัน SELECT นาน 40 วินาทีบน users แล้วเราสั่ง ALTER

• ไม่ตั้ง lock_timeout: ALTER รอ 40 วินาที request ใหม่ทุกตัวที่แตะ users ต่อคิวหลัง ALTER ด้วย ถ้าเว็บรับ 50 request/วินาที จะมี request ค้างราว 50 × 40 = 2,000 ตัว connection pool เต็มก่อนนั้นนานแล้ว
• ตั้ง lock_timeout = '2s': ALTER ยอมแพ้ที่ 2 วินาที request ค้างสูงสุดราว 50 × 2 = 100 ตัว แล้วไหลต่อ migration error แล้วเราลองใหม่ทีหลัง

ตัวเลข 2 วินาทีเป็นค่าที่พบบ่อย ปรับตาม timeout ของแอปเอง เครื่องมือ migration ต้อง retry ได้ (PostgreSQL ไม่ retry ให้) ถ้าตั้ง statement_timeout ไว้ด้วย ให้ lock_timeout น้อยกว่า ไม่อย่างนั้นไม่มีผล

backfill ทีละก้อน

UPDATE users SET full_name = name คำสั่งเดียวบนตาราง 20 ล้านแถว เป็น transaction ยาวก้อนเดียว ล็อกทุกแถวที่แตะ สร้าง dead tuple ทีเดียว 20 ล้าน และถ้าล้มตอน 90% ก็ rollback ทั้งหมด ให้แบ่งตาม primary key ก้อนละไม่กี่พันแถว แต่ละก้อน commit เอง

#!/usr/bin/env bash
# รันจากเครื่องที่ต่อ DB ได้, DB="postgres://app@db.example.com/app"
set -euo pipefail
BATCH=5000
MAX=$(psql "$DB" -Atc "SELECT coalesce(max(id),0) FROM users")
for ((start=1; start<=MAX; start+=BATCH)); do
  psql "$DB" -qc "UPDATE users SET full_name = name
                  WHERE id >= $start AND id < $start + $BATCH
                    AND full_name IS NULL"
  sleep 0.1   # เว้นจังหวะให้ replica และ autovacuum ตามทัน
done

full_name IS NULL ทำให้รันซ้ำได้ ถ้าหยุดกลางทางก็เริ่มใหม่ได้โดยไม่ทับข้อมูลที่โค้ดเขียนไปแล้ว

คำนวณเวลาก่อนเริ่ม (ตัวเลขต่อก้อนต้องวัดเองบน staging ที่ข้อมูลใกล้จริง ตัวอย่างนี้สมมติ)

	ค่า
จำนวนแถว	20,000,000
ขนาดก้อน	5,000
จำนวนก้อน	20,000,000 ÷ 5,000 = 4,000
เวลาต่อก้อน (วัดได้ สมมติ)	150 ms + พัก 100 ms = 250 ms
เวลารวม	4,000 × 0.25 s = 1,000 s ≈ 17 นาที

ถ้าวัดแล้วก้อนละ 150 ms นาน ให้ลดขนาดก้อน เป้าคือแต่ละ transaction สั้นพอที่ไม่มีใครรู้สึก ส่วนเวลารวมจะยาวแค่ไหนไม่สำคัญ เพราะเว็บยังรับงานปกติตลอด

ข้อผิดพลาดที่เจอบ่อย

• ลืมว่าเครื่องมือห่อ transaction ให้ CREATE INDEX CONCURRENTLY จะ error ทันที และถ้าใส่หลาย DDL ในไฟล์เดียว ล็อกจากคำสั่งแรกจะถือไว้จนจบไฟล์
• ลบคอลัมน์ใน deploy เดียวกับที่เลิกใช้ โค้ดเก่าที่ยังรันอยู่จะ error ต้องให้โค้ดที่เลิกใช้ deploy เสร็จก่อน
• ORM จำรายชื่อคอลัมน์ บาง ORM โหลดรายชื่อคอลัมน์ตอนเริ่ม process แล้ว SELECT หรือ INSERT ทุกคอลัมน์ที่จำไว้ ลบคอลัมน์แล้วจะพังทั้งที่โค้ดไม่ได้อ้างชื่อนั้น ต้องสั่ง ORM ให้มองข้ามคอลัมน์นั้นก่อนลบ
• เปลี่ยนชนิดคอลัมน์ตรง ๆ ALTER COLUMN ... TYPE ที่ชนิดใหม่ไม่ binary-compatible จะเขียนตารางใหม่ทั้งตารางใต้ ACCESS EXCLUSIVE ใช้ expand/contract แบบเดียวกับ rename แทน
• ทดสอบบนตารางเปล่า migration ที่ใช้ 50 ms บนเครื่อง dev อาจล็อก 10 นาทีบนข้อมูลจริง ทดสอบกับสำเนาขนาดจริงเสมอ (ปัญหาเครื่องเราใช้ได้ฉบับฐานข้อมูล)
• backfill ไม่ idempotent ไม่มีเงื่อนไข IS NULL รันซ้ำแล้วทับค่าที่โค้ดใหม่เขียนไว้

คำสั่งที่ถือล็อกหนักต้องเสร็จในเสี้ยววินาที งานหนักไปทำในคำสั่งที่ล็อกเบา และตั้ง lock_timeout ทุกครั้ง

แหล่งอ้างอิง

• ALTER TABLE — PostgreSQL 18 Documentation
• CREATE INDEX (Building Indexes Concurrently) — PostgreSQL 18 Documentation
• Explicit Locking — PostgreSQL 18 Documentation
• Client Connection Defaults (lock_timeout) — PostgreSQL 18 Documentation
• PostgreSQL 11 Release Notes — postgresql.org และ PostgreSQL 18 Release Notes
• Schema changes and the Postgres lock queue — Xata