เมื่อลบคอลัมน์ใน Postgres แล้ว จริง ๆ แล้วเกิดอะไรขึ้น

• ใน PostgreSQL แม้จะ DROP คอลัมน์ ข้อมูลก็ไม่ได้ถูกลบจริง — เพียงแค่ถูก "ซ่อนไว้" ในเมทาดาทา
• เนื่องจาก หลัง DROP COLUMN คอลัมน์ยังคงมีอยู่ภายใน จึงอาจไปถึง ขีดจำกัด 1600 คอลัมน์ ได้
• หากต้องการลบข้อมูลออกอย่างสมบูรณ์ จำเป็นต้องใช้ VACUUM FULL หรือเขียนตารางใหม่ด้วยตนเอง
• นี่เป็นการออกแบบเพื่อเพิ่มประสิทธิภาพ แต่ ต้องระวังในมุมมองด้านคอมพลายแอนซ์อย่าง GDPR
• การเข้าใจว่า "จริง ๆ แล้วเกิดอะไรขึ้น" จะช่วยในด้าน การแก้ปัญหา การปรับแต่งประสิทธิภาพ และการจัดการข้อมูล

---

การทำงานจริงของ DROP COLUMN ใน PostgreSQL

สถานการณ์ปัญหา: ถ้าเพิ่ม/ลบคอลัมน์ซ้ำ ๆ จะเกิดอะไรขึ้น?

• เพิ่มแล้วลบคอลัมน์ 2000 ครั้งด้วยโค้ดลักษณะนี้:

  ALTER TABLE t ADD COLUMN c1 int;  
  ALTER TABLE t DROP COLUMN c1;  
  ...  
  

• สุดท้ายแม้ในตารางจะเหลือเพียง 2 คอลัมน์ แต่ PostgreSQL ก็ยังแสดง ข้อผิดพลาดขีดจำกัด 1600 คอลัมน์
• ทำไม? เพราะ คอลัมน์ที่ลบไปแล้วยังคงมีอยู่ภายในระบบ

ภายใน PostgreSQL เกิดอะไรขึ้น?

การลบคอลัมน์ไม่ใช่การ "ลบจริง"

• PostgreSQL จัดเก็บข้อมูลเป็นหน่วย เพจขนาด 8KB
• หากจะลบคอลัมน์ออกทางกายภาพ จำเป็นต้อง เขียนทั้งตารางใหม่ ซึ่งไม่มีประสิทธิภาพ
• ดังนั้นระบบจึง ทำเครื่องหมายคอลัมน์ในเมทาดาทาเป็นสถานะ 'dropped' แล้วละเลยมัน แทน

ตรวจสอบได้จากตารางระบบ pg_attribute

SELECT attnum, attname, attisdropped FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attnum > 0;  

• ตัวอย่างผลลัพธ์:

  attnum | attname                  | attisdropped  
  --------+--------------------------+--------------  
        1 | a                        | f  
        2 | ........pg.dropped.2.... | t  
        3 | c                        | f  
  

• คอลัมน์ที่มี attisdropped = t จะ ถูกมองข้ามในการ query แต่ยังคงอยู่ภายใน

ตรวจสอบจากไฟล์ข้อมูล (pg_filedump)

• เมื่อวิเคราะห์ไฟล์ข้อมูลของ PostgreSQL จะพบว่า ค่าของคอลัมน์ที่ถูกลบยังคงอยู่จริง
• ข้อมูลเก่า (Item 1) จะยังมีค่าของ 3 คอลัมน์อยู่
• ข้อมูลที่แทรกหลังการลบ (Item 3) จะไม่มีค่าของคอลัมน์นั้น และถูกจัดการเป็น NULL

วิธีลบคอลัมน์ที่ถูกลบไปแล้วออกจริง ๆ

1. VACUUM FULL

• จะเขียนทั้งตารางใหม่และ ลบข้อมูลของคอลัมน์ที่ถูกลบออกไปด้วย
• ข้อเสีย: ตัวคอลัมน์เองยังคงอยู่ใน pg_attribute โดยมีสถานะเป็น 'dropped'

2. เขียนตารางใหม่ด้วยตนเอง

• สร้างตารางใหม่ แล้วใช้ SELECT เฉพาะคอลัมน์ที่ต้องการเพื่อคัดลอกข้อมูล

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• ต้องสร้าง constraint, index, trigger ฯลฯ ขึ้นใหม่ด้วยตนเอง
• อีกวิธีคือสำรองข้อมูลด้วย pg_dump → แก้ไขไฟล์ดัมป์ → กู้คืนกลับมา

ปัญหาเรื่อง GDPR และ 'สิทธิในการถูกลืม' กับการ DROP คอลัมน์

• บางคนกังวลว่า "ถ้าคอลัมน์ไม่ได้ถูกลบจริง จะถือว่าผิด GDPR หรือไม่?"
• แต่โดยทั่วไป การลบข้อมูลส่วนบุคคลมักทำในระดับแถว (row)

  DELETE FROM users WHERE id = <user_id>; -- หรือลบรวมถึงตารางที่เกี่ยวข้อง  
  

• การ DROP คอลัมน์ไม่ได้เกี่ยวข้องกับ GDPR โดยตรง และ หัวใจสำคัญคือการออกแบบโมเดลข้อมูลส่วนบุคคลและการลบให้ถูกต้อง

ข้อควรระวัง

• PostgreSQL ใช้ MVCC ดังนั้นแม้ลบแถวแล้ว ข้อมูลก็ยังคงอยู่จนกว่า VACUUM จะทำงานเสร็จ
• ในระดับระบบปฏิบัติการเองก็อาจเป็นเพียง การตั้งค่าสถานะว่าลบแล้ว ไม่ใช่การลบทางกายภาพ
• ในทางกฎหมาย สิ่งสำคัญคือ “ความพยายามในการลบอย่างสมเหตุสมผล” โดยส่วนใหญ่ไม่ได้กำหนดให้ต้องลบข้อมูลจากดิสก์ทางกายภาพจนหมดสิ้น

สรุป: DROP COLUMN เป็นเพียง “การซ่อน” ไม่ใช่ “การลบ”

• เป็นการออกแบบเพื่อประสิทธิภาพ แต่หากคอลัมน์สะสมมากขึ้นก็อาจ ชนขีดจำกัด 1600 คอลัมน์ ได้
• หากจำเป็นต้องล้างข้อมูลจริง ควรใช้ VACUUM FULL หรือ เขียนตารางใหม่เพื่อจัดระเบียบข้อมูล
• ในมุมมองของการออกแบบระบบและคอมพลายแอนซ์ การเข้าใจการทำงานภายในของ PostgreSQL มีประโยชน์อย่างมาก

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

• Postgres File Layout
• Postgres Page Layout
• Internal Layout of Heap Tables
• เอกสารอธิบาย VACUUM
• pg_filedump

[ohyecloudy]

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

[click]

แม้ช่วงนี้ postgresql จะได้รับความนิยมมาก แต่สำหรับการทำ MVCC ผมยังชอบแนวทางที่มีพื้นที่ redo/undo แยกต่างหากมากกว่า
พื้นที่ redo/undo สามารถยอมเสียความเป็นเรียลไทม์ไปได้ระดับหนึ่ง จึงมีช่องให้ใช้สตอเรจเกรดต่ำกว่าเพื่อปรับต้นทุนให้เหมาะสมได้
อีกทั้งการที่ในสักช่วงหนึ่งอาจจำเป็นต้องล็อกทั้งฐานข้อมูลแล้วค่อยทำ VACUUM FULL ก็เป็นจุดที่ไม่ชอบเช่นกัน

[salsa]

จริงหรือไม่ที่สักวันหนึ่งจำเป็นต้องทำ VACUUM FULL? เอกสารส่วนใหญ่ที่ผมเคยเห็นกลับบอกว่าไม่ควรทำ

หนึ่งในแหล่งข้อมูลที่ผมเคยอ่าน:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/