การแก้ไข schema

schema ไม่มีวันเสร็จสมบูรณ์ ฟีเจอร์ใหม่ต้องการ column ใหม่ ฟีเจอร์เก่าล้าสมัย และกฎเข้มงวดขึ้นเมื่อคุณเรียนรู้ว่าข้อมูลมีหน้าตาอย่างไรจริง ๆ คำสั่ง ALTER TABLE เปลี่ยนรูปทรงของ table ที่มีอยู่แบบ in-place โดยเก็บ row เดิมไว้ครบ เช่นเดียวกับ CREATE TABLE คำสั่งนี้เป็น DDL และมักใช้ผ่านขั้นตอน migration เพื่อให้การเปลี่ยนแปลงนั้นมีเวอร์ชันกำกับและทำซ้ำได้

เราจะพัฒนา table products ต่อจากช่วงต้นของโมดูล

เพิ่มและ drop column

ADD COLUMN ผนวก column ใหม่เข้ากับทุก row ที่มีอยู่ หากคุณไม่ให้ default row ที่มีอยู่จะได้ null ใน column ใหม่นั้น ส่วน DROP COLUMN ลบ column พร้อมข้อมูลข้างในทิ้งถาวร

ALTER TABLE products ADD COLUMN description text;

ALTER TABLE products DROP COLUMN description;

คุณสามารถรวมการเปลี่ยนแปลงหลายอย่างไว้ในคำสั่งเดียวได้โดยคั่นด้วยจุลภาค ซึ่งทำให้การแก้ไขที่เกี่ยวข้องกันอยู่ในขั้นตอนเดียวที่เป็น atomic

ALTER TABLE products
  ADD COLUMN sku text,
  ADD COLUMN in_stock boolean NOT NULL DEFAULT true;

ใน pgAdmin: หลังจากแก้ไข table ให้ refresh โหนด Columns ของ table นั้นในแผนผัง browser เพื่อดูการเปลี่ยนแปลง หรือใช้ dialog Properties ของ table เพื่อเพิ่มและลบ column ผ่านฟอร์ม

เปลี่ยนชนิดของ column

ALTER COLUMN ... TYPE แปลง column ไปเป็นชนิดใหม่ PostgreSQL สามารถแปลงได้หลายอย่างโดยอัตโนมัติ แต่เมื่อการเปลี่ยนแปลงไม่ชัดเจน คุณต้องใส่นิพจน์ USING เพื่อบอก PostgreSQL ว่าจะแปลงค่าเดิมแต่ละค่าอย่างไร

ALTER TABLE products
  ALTER COLUMN sku TYPE varchar(32);

ALTER TABLE products
  ALTER COLUMN price TYPE numeric(12, 2) USING price::numeric(12, 2);

การแปลงจะรันผ่านทุก row ดังนั้นบน table ขนาดใหญ่สิ่งนี้อาจใช้เวลาสักพักและถือ lock ไว้ ส่วนบทเรียนสุดท้ายครอบคลุมวิธีทำให้ปลอดภัย

เพิ่มและ drop constraint

คุณเพิ่ม constraint เข้า table ที่มีอยู่แล้วได้ และ drop ออกด้วยชื่อก็ได้ การตั้งชื่อ constraint ตั้งแต่ตอนสร้าง อย่างที่บทเรียนก่อนแนะนำ คือสิ่งที่ทำให้ drop ทีหลังได้สะอาด

ALTER TABLE products
  ADD CONSTRAINT products_price_positive CHECK (price > 0);

ALTER TABLE products
  DROP CONSTRAINT products_price_positive;

การเพิ่ม constraint จะสแกน row ที่มีอยู่เพื่อยืนยันว่าทุก row ผ่านเงื่อนไข ถ้ามี row ไหนไม่ผ่าน PostgreSQL จะปฏิเสธทั้งคำสั่งและไม่มีอะไรเปลี่ยนแปลง

การเปลี่ยนชื่อ

RENAME เปลี่ยนชื่อของ table หรือ column ตัวเดียวโดยไม่แตะต้องข้อมูล การเปลี่ยนชื่อมีผลทันที แต่ทุกอย่างที่อ้างถึงชื่อเดิม — view, query ของแอปพลิเคชัน, รายงานที่บันทึกไว้ — คุณต้องตามไปแก้เองด้วย

ALTER TABLE products RENAME COLUMN sku TO product_code;

ALTER TABLE products RENAME TO catalog_items;

migration ที่ปลอดภัย: กับดักของ NOT NULL

อุบัติเหตุ migration ที่พบบ่อยที่สุดคือการเพิ่ม column แบบ NOT NULL ลงใน table ที่มี row อยู่แล้ว row ที่มีอยู่เหล่านั้นไม่มีค่าสำหรับ column ใหม่ ดังนั้น constraint จึงถูกละเมิดทันทีและคำสั่งล้มเหลว มีเส้นทางที่ปลอดภัยสองทาง: ให้ DEFAULT กับ column เพื่อให้ทุก row ที่มีอยู่ถูกเติมในคราวเดียว หรือเพิ่ม column แบบ nullable, backfill ค่า แล้วจึงทำให้เข้มงวดขึ้นเป็น NOT NULL

-- Path 1: a default fills every existing row in one step
ALTER TABLE products
  ADD COLUMN currency text NOT NULL DEFAULT 'USD';

-- Path 2: add nullable, backfill, then enforce
ALTER TABLE products ADD COLUMN region text;
UPDATE products SET region = 'global' WHERE region IS NULL;
ALTER TABLE products ALTER COLUMN region SET NOT NULL;

เส้นทางที่สองเป็นตัวเลือกมาตรฐานเมื่อไม่มี default เดียวที่สมเหตุสมผล เพราะเปิดให้คุณคำนวณค่าของแต่ละ row ให้เสร็จก่อน แล้วค่อย lock column นั้นไว้

รัน migration ใน transaction

คำสั่ง ALTER TABLE ส่วนใหญ่ใน PostgreSQL เป็น transactional ดังนั้นคุณห่อ migration หลายขั้นตอนไว้ใน transaction เดียวได้ ให้ทำงานแบบทั้งหมดหรือไม่ทำเลย หากขั้นตอนใดล้มเหลว ROLLBACK จะทิ้ง table ไว้เหมือนเดิมทุกประการ

psql

BEGIN;
ALTER TABLE products ADD COLUMN region text;
UPDATE products SET region = 'global' WHERE region IS NULL;
ALTER TABLE products ALTER COLUMN region SET NOT NULL;
COMMIT;

Node.js

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('ALTER TABLE products ADD COLUMN region text');
  await client.query("UPDATE products SET region = 'global' WHERE region IS NULL");
  await client.query('ALTER TABLE products ALTER COLUMN region SET NOT NULL');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Python

with conn.transaction():
    cur.execute("ALTER TABLE products ADD COLUMN region text")
    cur.execute("UPDATE products SET region = 'global' WHERE region IS NULL")
    cur.execute("ALTER TABLE products ALTER COLUMN region SET NOT NULL")
# commits if the block succeeds, rolls back on exception

Go

tx, err := conn.Begin(ctx)
if err != nil {
    return err
}
steps := []string{
    "ALTER TABLE products ADD COLUMN region text",
    "UPDATE products SET region = 'global' WHERE region IS NULL",
    "ALTER TABLE products ALTER COLUMN region SET NOT NULL",
}
for _, s := range steps {
    if _, err := tx.Exec(ctx, s); err != nil {
        tx.Rollback(ctx)
        return err
    }
}
err = tx.Commit(ctx)

Rust

let mut tx = pool.begin().await?;
sqlx::query("ALTER TABLE products ADD COLUMN region text")
    .execute(&mut *tx)
    .await?;
sqlx::query("UPDATE products SET region = 'global' WHERE region IS NULL")
    .execute(&mut *tx)
    .await?;
sqlx::query("ALTER TABLE products ALTER COLUMN region SET NOT NULL")
    .execute(&mut *tx)
    .await?;
tx.commit().await?;

เนื่องจากสามขั้นตอนนี้รันภายใน transaction เดียว table จึงไม่เคยถูกมองเห็นโดย session อื่นในสถานะที่ migrate ไปได้ครึ่งทาง

เคล็ดลับและข้อควรระวัง

• ALTER TABLE ถือ lock บน table เสมอ และหลายรูปแบบถือ lock แรงพอที่จะปิดกั้นการอ่านและการเขียนชั่วครู่ ดังนั้นบน table production ที่ใช้งานหนัก ให้เลือกคำสั่งที่สั้นและตรงเป้า แล้วรันในช่วงที่ traffic เงียบ
• การเพิ่ม column แบบ NOT NULL ที่มี DEFAULT เป็นค่าคงที่นั้นเร็วใน PostgreSQL สมัยใหม่ เพราะไม่ได้เขียนทุก row ใหม่ แต่การเปลี่ยนชนิดของ column ยังคงสแกนและเขียน table ใหม่
• ห่อ migration หลายขั้นตอนไว้ใน transaction เพื่อให้ความล้มเหลวกลางคันถูก roll back อย่างสะอาดและไม่ทิ้ง schema ไว้ในสภาพเปลี่ยนไปบางส่วน
• การเพิ่ม constraint จะตรวจสอบทุก row ที่มีอยู่ บน table ขนาดใหญ่การสแกนนี้อาจช้า ลองพิจารณาเพิ่ม constraint แบบ NOT VALID ก่อน แล้วค่อย validate แยกต่างหากเมื่อคุณต้องการลดเวลา lock ให้น้อยที่สุด
• DROP COLUMN และ DROP CONSTRAINT ย้อนกลับไม่ได้เมื่อ commit แล้ว ยืนยันว่าไม่มีอะไรขึ้นต่อสิ่งที่คุณกำลังจะลบ และใช้ transaction ที่ roll back ได้ระหว่างที่คุณตรวจสอบ

🐘 migrate ไปข้างหน้า แต่อย่ามืดบอด

มองทุกการเปลี่ยน schema เป็น migration คือขั้นตอนเล็ก ๆ ที่มีชื่อกำกับ ย้อนกลับได้ และทดสอบได้ก่อนขึ้น production เพิ่ม column พร้อม default หรือ backfill ทีหลัง ทำ constraint ให้เข้มงวดขึ้นหลังข้อมูลสะอาดแล้วเท่านั้น และรันการเปลี่ยนแปลงหลายขั้นตอนใน transaction เดียว migration ที่ระมัดระวังจะไม่มีใครรู้สึก ส่วน migration ที่สะเพร่าคือเหตุระบบล่ม

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
migration แบบ online/non-blocking	ปลอดภัยแม้รันตอนช่วงเวลาทำงานปกติ ไม่กระทบ traffic	ต้องเขียนหลายขั้นตอนและวางแผนมากขึ้น
ALTER TABLE แบบตรงไปตรงมา	เขียนและรันได้เร็ว โค้ดสั้น	อาจถือ lock แบบเข้มงวดจน block production traffic บน table ใหญ่
เพิ่ม column พร้อม DEFAULT เป็นค่าคงที่ (PostgreSQL รุ่นใหม่)	เป็น metadata-only ไม่เขียนทุก row ใหม่ จึงเร็วมาก	ใช้ได้เฉพาะ default ที่เป็นค่าคงที่ ไม่ใช่ expression ที่คำนวณต่อ row
เปลี่ยนชนิดของ column ด้วย ALTER COLUMN ... TYPE	ทำได้ตรง ๆ ในคำสั่งเดียว	ทำให้เกิด full table rewrite สแกนและเขียนทุก row ใหม่

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

• รัน ALTER TABLE ... TYPE บน table ขนาดใหญ่ตอน peak traffic — คำสั่งนี้ถือ exclusive lock ระหว่าง rewrite table ทั้งก้อน ถ้ารันตอนระบบมีโหลดสูงจะ block ทั้ง read และ write เป็นเวลานาน
• เปลี่ยนชนิด column โดยไม่ตรวจ USING ให้ถูกต้อง — ถ้านิพจน์แปลงค่าไม่ตรงกับข้อมูลเดิมจริง ๆ อาจได้ค่าที่ผิดเพี้ยนหรือคำสั่งล้มเหลวกลางทาง
• เพิ่ม column แบบ NOT NULL โดยไม่มี default แล้วลืม backfill — row ที่มีอยู่แล้วไม่มีค่าให้ column ใหม่ ทำให้ constraint ถูกละเมิดทันทีและคำสั่งล้มเหลว ต้อง backfill ก่อนค่อยตั้ง NOT NULL

💡 ตัวอย่างจากของจริง

GitLab — ใช้แนวทาง migration หลายขั้นตอนอย่างระมัดระวังบนฐานข้อมูล PostgreSQL ขนาดใหญ่ เพื่อเลี่ยงการถือ lock นาน ๆ ระหว่าง deploy เช่น เพิ่ม column แบบ nullable ก่อนแล้วค่อย backfill แยกจากการตั้ง NOT NULL

ทีมที่ดูแลระบบระดับ Figma-scale — เลือกใช้ pattern แบบ pg_online_schema_change-style หรือแบ่ง migration เป็นหลายขั้นตอนเล็ก ๆ โดยเฉพาะกับ table ขนาดใหญ่ เพื่อไม่ให้การเปลี่ยน schema กระทบ traffic การใช้งานจริงระหว่าง deploy

เหตุใดการเพิ่ม column แบบ NOT NULL โดยไม่มี default จึงล้มเหลวบน table ที่มี row อยู่แล้ว?

column แบบ NOT NULL ไม่ได้รับอนุญาตหลังการสร้าง row ที่มีอยู่จะไม่มีค่า จึงละเมิด constraint ALTER TABLE เพิ่ม column ไม่ได้ ALTER TABLE จะ lock table ไว้ตลอดไป

อะไรคือรูปแบบที่ปลอดภัยเมื่อไม่มี default เดียวที่สมเหตุสมผลสำหรับ column แบบ NOT NULL ใหม่?

drop และสร้าง table ใหม่ เพิ่ม column แบบ nullable ก่อน, backfill ค่า แล้วค่อยตั้ง NOT NULL ปิดใช้งาน constraints ชั่วคราว ใช้ CHECK แทน

เหตุใดจึงห่อ migration ของ ALTER TABLE หลายขั้นตอนไว้ใน transaction?

เพื่อให้รันเร็วขึ้น เพื่อให้ roll back ได้สะอาดเมื่อล้มเหลว และไม่ทิ้ง schema ไว้ครึ่ง ๆ กลาง ๆ เพื่อหลีกเลี่ยงการถือ lock ใด ๆ เพื่อข้ามการ validate row ที่มีอยู่

Submit

Mark as complete