การย้ายข้อมูลแบบออนไลน์: เหตุผลที่ ALTER TABLE ล็อกตาราง

ปัญหาการปรับเปลี่ยนฐานข้อมูลในระบบจริง

ระดับการล็อก

ALTER TABLE ใช้การล็อกระดับใด

ALTER ที่รวดเร็ว (เปลี่ยนเฉพาะข้อมูลเมตา)

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

ALTER ที่ช้า (เขียนตารางใหม่)

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

การเพิ่ม NOT NULL อย่างปลอดภัย

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

การเพิ่มคีย์ต่างประเทศแบบออนไลน์

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

ปัญหาการรอการล็อก

การจำกัดเวลารอการล็อก

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

ลูปการลองใหม่

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

การใช้การจำกัดเวลาของคำสั่งเพื่อความปลอดภัย

SET statement_timeout = '30s';

เครื่องมือที่ช่วยได้

สรุป

แบบทดสอบสั้น ๆ