ข้อผิดพลาดในการเปลี่ยนแปลงสคีมา DB ที่พบบ่อยใน Postgres

 (postgres.ai)
5 คะแนน โดย GN⁺ 2024-04-29 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • การไมเกรตสคีมาของ Postgres มีความเสี่ยงสูงเป็นพิเศษในสภาพแวดล้อม OLTP ขนาดใหญ่ เพราะการล็อก การเขียนตารางใหม่ และความล่าช้าในการ replication อาจนำไปสู่ปัญหาในระบบปฏิบัติการจริงได้
  • ความเสี่ยงมักกระจุกตัวอยู่กับงานที่ทำให้เกิด การสแกนทั้งตารางและการล็อกที่ยาวนาน เช่น การเพิ่ม DEFAULT และ NOT NULL พร้อมกัน, การสร้าง index โดยไม่มี CONCURRENTLY, การลบคอลัมน์ทันที, การเปลี่ยนชนิดข้อมูลที่ไม่ปลอดภัย, และการเพิ่ม foreign key โดยไม่ตรวจสอบก่อน
  • ตั้งแต่ PostgreSQL 11 เป็นต้นมา ค่าใช้จ่ายของการเพิ่มคอลัมน์บางกรณีลดลงแล้ว แต่ยังต้องมีขั้นตอนเพื่อลดผลกระทบต่อระบบจริง เช่น ใช้ CREATE INDEX CONCURRENTLY สำหรับ index และเพิ่ม foreign key ด้วย NOT VALID แล้วค่อย VALIDATE CONSTRAINT
  • การเปลี่ยนแปลงปริมาณมากควรแบ่งเป็น batch ขนาดเล็ก และต้องตรวจสอบ read replica, replication lag, อ็อบเจ็กต์ที่พึ่งพาอยู่ รวมถึงว่าอินสแตนซ์แอปพลิเคชันเดิมยังอ้างถึงคอลัมน์นั้นหรือไม่
  • ควรทดสอบล่วงหน้ากับข้อมูลขนาดเท่าระบบ production และสำหรับงานที่ทำลายข้อมูลหรือย้อนกลับยาก ควรดำเนินการหลังจากมี การ deploy หลายขั้นตอน และแผน rollback ที่ตรวจสอบแล้วเท่านั้น

ข้อสมมติของการไมเกรตสคีมา

  • ในที่นี้ การไมเกรต DB หมายถึง การเปลี่ยนแปลงสคีมา DB ไม่ใช่การย้ายไปใช้ DBMS อื่น
  • การเปลี่ยนแปลงเป้าหมายมีลักษณะสามประการ
    • การเปลี่ยนแปลงแบบมีการจัดการเวอร์ชัน ซึ่งแต่ละรายการมีตัวระบุเฉพาะและขั้นตอนการปรับใช้แบบอัตโนมัติ
    • การเปลี่ยนแปลงแบบไม่แก้ย้อนหลัง คือหลังนำไปใช้กับ production แล้วจะไม่แก้ไขรายการเดิม แต่เพิ่มรายการเปลี่ยนแปลงใหม่เท่านั้น
    • การเปลี่ยนแปลงแบบเพิ่มทีละส่วน ที่สคีมาฐานข้อมูลค่อย ๆ วิวัฒน์ไปเป็นขั้นตอน
  • โฟกัสอยู่ที่ กรณีใช้งาน OLTP เช่น แอปพลิเคชันมือถือและเว็บ โดยปกติการรันคิวรีที่เกิน 1 วินาทีถือว่าช้าเกินไป
  • ในฐานข้อมูลขนาดเล็กและมีกิจกรรมน้อย ปัญหาบางอย่างอาจไม่ค่อยปรากฏ แต่เมื่อมีขนาดประมาณ 10TiB และโหลด 10⁴~10⁵ ธุรกรรมต่อวินาที ปัญหาส่วนใหญ่สามารถเกิดขึ้นได้
  • Database Lab Engine ใช้ thin clone สำหรับการพัฒนาและทดสอบ และสามารถ clone ฐานข้อมูล 10TiB ได้ภายใน 10 วินาที เพื่อช่วยตรวจสอบความเสี่ยงของการเปลี่ยนแปลงสคีมาก่อน deploy
  • GitLab Migration Style Guide เป็นเอกสารอ้างอิงที่รวบรวมประสบการณ์ในการ deploy การเปลี่ยนแปลงสคีมา Postgres จำนวนมากแบบอัตโนมัติ

การเพิ่มคอลัมน์และการเขียนตารางใหม่

  • การเพิ่มคอลัมน์ที่มี DEFAULT และ NOT NULL พร้อมกันมีความเสี่ยงเป็นพิเศษใน PostgreSQL เวอร์ชันเก่า
    • ก่อน PostgreSQL 11 ต้องเขียนตารางใหม่ทั้งตาราง
    • สำหรับตารางขนาดใหญ่ อาจใช้เวลาหลายชั่วโมงหรือหลายวัน และระหว่างนั้นจะเกิดการล็อกการเขียน
  • ตัวอย่างที่เสี่ยงมีดังนี้
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • ขั้นตอนที่ปลอดภัยกว่าคือแยกการเพิ่มคอลัมน์ การอัปเดตข้อมูล และการเพิ่ม constraint ออกจากกัน
    • ขั้นแรก เพิ่มคอลัมน์โดยไม่มี NOT NULL
    • หากจำเป็น ให้อัปเดตแถวที่มีอยู่
    • หลังจากนั้นจึงเพิ่ม constraint NOT NULL
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • ตั้งแต่ PostgreSQL 11 ขึ้นไป การเพิ่มคอลัมน์ที่มีค่า DEFAULT แบบ non-volatile ไม่จำเป็นต้อง เขียนตารางใหม่ อีกต่อไป

การสร้าง index และการเพิ่ม foreign key

  • หากสร้าง index โดยไม่มี CONCURRENTLY การสร้าง index แบบมาตรฐานจะจับ exclusive lock บนตาราง
    • การเขียนทั้งหมดและการอ่านบางส่วนอาจถูกบล็อกจนกว่าการสร้าง index จะเสร็จ
  • ตัวอย่างที่เสี่ยงมีดังนี้
CREATE INDEX idx_users_email ON users(email);
  • ในระหว่างที่ระบบใช้งานจริง การใช้ CREATE INDEX CONCURRENTLY ปลอดภัยกว่า
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY มีข้อจำกัด
    • ใช้เวลานานกว่า แต่ไม่บล็อกการเข้าถึงตาราง
    • ใช้ภายใน transaction block ไม่ได้
    • หากล้มเหลว อาจทิ้ง index ที่ไม่ valid ไว้ ซึ่งจำเป็นต้องลบออก
  • การเพิ่ม foreign key constraint โดยตรงในตารางขนาดใหญ่จะสแกนทั้งตารางเพื่อตรวจสอบข้อมูลเดิม และก่อให้เกิดการล็อกที่ยาวนาน
  • ขั้นตอนที่ปลอดภัยกว่าคือเพิ่ม constraint ด้วย NOT VALID ก่อน แล้วค่อยตรวจสอบในช่วงที่ทราฟฟิกต่ำ
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

การลบคอลัมน์และการเปลี่ยนชนิดข้อมูล

  • หากลบคอลัมน์ทันทีใน production อาจเกิด ข้อผิดพลาดของแอปพลิเคชัน เมื่อโค้ดแอปพลิเคชันยังอ้างถึงคอลัมน์นั้นอยู่
  • การลบคอลัมน์ควรทำแบบหลายขั้นตอน
    • deploy โค้ดแอปพลิเคชันที่ไม่ใช้คอลัมน์นั้นก่อน
    • รอจนกว่าอินสแตนซ์แอปพลิเคชันเก่าทั้งหมดจะถูกแทนที่
    • ลบคอลัมน์ในการไมเกรตแยกต่างหาก
  • การเปลี่ยนชนิดข้อมูลของคอลัมน์อาจทำให้เกิด การเขียนตารางใหม่ หรือปัญหาความเข้ากันได้
    • อาจนำไปสู่ downtime, การสูญเสียข้อมูล หรือข้อผิดพลาดของแอปพลิเคชัน
  • ตัวอย่างที่เป็นปัญหามีดังนี้
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • เมื่อต้องเปลี่ยนจาก integer เป็น bigint จำเป็นต้องใช้ ขั้นตอนหลายระยะ โดยอาศัยคอลัมน์ใหม่
  • เมื่อจะลดความยาวของ varchar ควรตรวจสอบข้อมูลก่อน และพิจารณาว่าการเปลี่ยนแปลงนั้นจำเป็นจริงหรือไม่

การเปลี่ยนแปลงปริมาณมาก, replication, และอ็อบเจ็กต์ที่พึ่งพา

  • ควรหลีกเลี่ยงการไมเกรตที่แก้ไขข้อมูลมากเกินไปใน transaction เดียว
    • lock contention และการใช้หน่วยความจำจะเพิ่มขึ้น
    • เมื่อเกิดปัญหา เวลากู้คืนจะยาวนานขึ้น
    • replication lag อาจเพิ่มขึ้น
  • การไมเกรตข้อมูลขนาดใหญ่ควรแบ่งเป็น batch ขนาดเล็ก จะปลอดภัยกว่า
  • ควรดูผลกระทบของการไมเกรตต่อ read replica และ replication lag ด้วย
    • การไมเกรตขนาดใหญ่อาจทำให้เกิด replication lag อย่างมาก
    • อาจส่งผลต่อประสิทธิภาพของ read replica
  • ต้องตรวจสอบอ็อบเจ็กต์ที่พึ่งพาคอลัมน์หรือตารางที่จะถูกแก้ไขด้วย
    • หากพลาดอ็อบเจ็กต์ที่พึ่งพา เช่น view, function, trigger อาจเกิดความล้มเหลวต่อเนื่องหรือต้องแทรกแซงด้วยมือเพิ่มเติม

การทดสอบและแผน rollback

  • หากทดสอบการไมเกรตเฉพาะกับ dataset สำหรับพัฒนาขนาดเล็ก จะตรวจสอบ ลักษณะด้านประสิทธิภาพ ของ dataset ขนาดใหญ่ได้ยาก
  • ควรทดสอบกับ clone ของข้อมูลขนาดเท่า production และสามารถใช้เครื่องมืออย่าง Database Lab Engine ได้
  • หากไม่มีวิธีย้อนกลับการไมเกรตเมื่อเกิดปัญหา ประเด็นใน production อาจนำไปสู่ downtime ระยะยาวได้
  • โดยเฉพาะงานที่ทำลายข้อมูลหรือย้อนกลับยาก ต้องมี แผน rollback ที่ผ่านการตรวจสอบแล้ว
  • พื้นฐานของการเปลี่ยนแปลงสคีมาอย่างปลอดภัยมีดังนี้
    • ทดสอบกับข้อมูลขนาดเท่า production
    • ใช้วิธีหลายขั้นตอนสำหรับงานที่มีความเสี่ยง
    • ใช้ฟีเจอร์ของ PostgreSQL เช่น CONCURRENTLY และ NOT VALID
    • มอนิเตอร์ผลกระทบด้านประสิทธิภาพและ replication
    • เตรียมแผน rollback ไว้เสมอ

บทความที่เกี่ยวข้อง

1 ความคิดเห็น

 
GN⁺ 2024-04-29
ความเห็นบน Hacker News

  • ฉันชอบ Postgres มาก แต่เนื้อหาส่วนใหญ่ในบทความนี้เป็นสิ่งที่หลีกเลี่ยงได้และควรระวัง อย่างไรก็ตาม สิ่งที่แย่ที่สุดของ Postgres ในมุมมองฉันคือ การจัดการ role
    ฟีเจอร์มันทรงพลัง และถ้าใช้เป็นก็คงยอดเยี่ยม แต่กระบวนการทำให้มันใช้งานได้จริงให้ความรู้สึกเหมือนมนตร์ดำ อินเทอร์เฟซหลายจุดเหมือนคาถาลึกลับที่ไม่รู้ว่าจะทำงานตามที่คาดหรือเปล่า และมันเป็นวิธีที่แย่มากสำหรับการจัดการสิ่งสำคัญขนาดนี้
    คู่มือในส่วนนี้ก็อธิบายไว้บางมาก บอกแค่คร่าว ๆ ว่ามันควรทำงานอย่างไรในกรณีใช้งานแคบ ๆ ถ้ามันไม่เป็นไปตามคาด ก็ต้องลองผิดลองถูกเพื่อหาว่าทำอะไรพลาดไป และก็ยังไม่ค่อยเข้าใจว่าวิธีที่ถูกต้องจริง ๆ คืออะไร ถ้าต้อง migrate ฐานข้อมูลที่มีสิทธิ์ผู้ใช้ซับซ้อน นี่คือเรื่องทรมานมาก
    รู้สึกเลยว่าควรใช้เวลาสักเดือนเขียน cookbook ขึ้นมา ถ้ามันช่วยให้มีสักคนไม่ต้องร้องไห้จนหลับไปเพราะเรื่องนี้ ก็คุ้มแล้ว

    • เห็นด้วยว่า IAM ของ PostgreSQL ซับซ้อน สาเหตุที่ซับซ้อนคือมันมีลำดับชั้นของ object 3 ระดับคือ Database, Schema, Tables และยังมีสิทธิ์ที่ถูกให้โดยปริยายกับเจ้าของ object ใน DB ด้วย
      ถ้าจะ SELECT จากตาราง ต้องมี CONNECT ของ Database, USAGE ของ Schema ซึ่งจะถูกให้โดยปริยายกับเจ้าของ Schema และยังต้องมี SELECT ของ Table ซึ่งถูกให้โดยปริยายกับเจ้าของตารางด้วย
      ถ้าจะดูสิทธิ์ ต้องเข้าใจรายการ ACL ในรูปแบบ grantee=privilege-abbreviation[]/grantor: สิทธิ์ของ Database ดูได้ด้วย \l+, สิทธิ์ของ Schema ดูได้ด้วย \dn+, และสิทธิ์ของ Table ดูได้ด้วย \dp+
      รายการสิทธิ์อยู่ ที่นี่ ตัวอย่างเช่น user=arwdDxt/postgres หมายถึง role postgres มอบสิทธิ์ทั้งหมดให้ผู้ใช้
      ถ้าคอลัมน์ grantee ของ object ใดว่างอยู่ อาจหมายถึงสิทธิ์เริ่มต้นของเจ้าของ คือสิทธิ์ทั้งหมด หรืออาจหมายถึงสิทธิ์สำหรับ role PUBLIC ซึ่งครอบคลุมทุก role ที่มีอยู่ก็ได้ ตัวอย่างคือ =r/postgres
      ถ้าใช้ Schema public จะยิ่งสับสนขึ้นไปอีก เพราะ Schema นั้นมีสิทธิ์ CREATE อยู่ ดังนั้นถ้าสร้างตารางด้วยผู้ใช้คนเดียวกับที่ใช้ query ข้อมูล สิทธิ์เจ้าของจะติดมาด้วยโดยอัตโนมัติและ query ได้ทันที
    • เอกสารของ postgREST ที่พึ่งพา role ในการยืนยันตัวตนก็ดูจะไม่ได้ละเอียดนักเช่นกัน: https://postgrest.org/en/v12/explanations/db_authz.html
      ถ้าคุณจะเขียน cookbook เรื่อง Postgres roles แบบจริงจัง แล้วเปิดอะไรอย่าง Kickstarter ฉันน่าจะเป็นคนแรก ๆ ที่สนับสนุนเลย
    • เห็นด้วยกับคำว่า “การทำให้มันใช้งานได้จริงเหมือนมนตร์ดำ” ปีที่แล้วฉันทำ postgREST server แบบง่าย ๆ ที่เปิดใช้ row-level security และเส้นทางกว่าจะไปถึงจุดนั้นค่อนข้างยาก
      แต่พอมันทำงานได้แล้วก็เหมือนเวทมนตร์จริง ๆ และกลไกที่เกี่ยวข้องเองกลับเรียบง่ายกว่าที่คิดมาก
    • ถ้ามีบทความแบบนั้น ฉันน่าจะอ่าน การจัดการ role เต็มไปด้วยการเดา และผลก็คือ role มักจะได้ สิทธิ์มากเกินไป บ่อยมาก
    • ช่วยเขียนทีเถอะ เนื้อหาระดับนั้นฉันยินดีจ่าย สัก 20 ดอลลาร์ เลย

  • ถ้าจะรัน Schema migration ในระบบ production ควรใช้ lock_timeout
    การเปลี่ยนแปลงที่ดูไม่มีพิษภัย เช่น การลบตารางที่มี foreign key หรือการลบ foreign key ซึ่งในสภาพแวดล้อมทดสอบมักเสร็จแทบจะทันที ก็อาจชนกับ lock ได้ในฐานข้อมูล production ที่มีทราฟฟิกสูง เพราะมี transaction เดิมหรือ autovacuum อยู่
    ALTER นั้นจะรอ lock จาก transaction แรกพร้อมกับต้องการ ACCESS EXCLUSIVE lock และผลคือ query ทั้งหมดที่เข้าถึงตารางที่ถูกล็อกจะถูกบล็อก
    ถ้าคุณดูแล Postgres ที่มีขนาดใหญ่ เรื่องชนกันแบบนี้เป็นแค่เรื่องของเวลา การตั้ง lock_timeout จะทำให้ migration ล้มเหลวเมื่อเกินเวลาที่กำหนด แทนที่จะรอไปเรื่อย ๆ พร้อมบล็อก query อื่นทั้งหมด

    • statement_timeout นับรวมเวลาที่รอ lock ด้วย จึงช่วยประเมินผลกระทบต่อ table ที่ยุ่งมากได้ดีกว่า
      ถ้าตั้ง timeout ไว้ที่ 5 วินาที ก็จะรู้ว่าเวลาหยุดชะงักรวมสูงสุดคือ 5 วินาที แล้วหลังจากนั้น transaction ก็จะดำเนินต่อไป ถ้าใช้แค่ lock_timeout จะควบคุมไม่ได้ว่าหลังจากได้ lock แล้วงานจะใช้เวลานานแค่ไหน และมันอาจเร็วหรือช้าก็ได้ตามทราฟฟิกพร้อมกันในขณะนั้น
    • ขึ้นอยู่กับเวอร์ชันของ Postgres ด้วยว่าบาง DML query จะจับ exclusive lock หรือไม่ ซึ่งต่างกันพอสมควร
      เลยสงสัยว่ามีวิธีดี ๆ ไหมที่จะวิเคราะห์ query แล้วบอกได้ว่ามันจะจับ lock ประเภทไหน ปกติถ้าไม่มั่นใจก็ต้องกลับไปอ่านเอกสารทุกครั้ง
    • เป็นคำแนะนำที่ดี แต่ในเชิงเทคนิคผมเข้าใจว่าจริง ๆ แล้วมันยังไม่ได้ถือ ACCESS EXCLUSIVE lock แล้วค่อยรอ แต่กำลังรอเพราะ lock queue มากกว่า
      ALTER กำลังรอให้ lock ที่มีระดับต่ำกว่า ACCESS EXCLUSIVE ถูกปล่อยออกไป
    • ถ้าทำแบบนั้น ALTER อาจไม่มีวันได้รันเลยก็ได้ ถ้าตารางนั้นมีทราฟฟิกมากพอ
      ในกรณีแบบนี้ ถ้าแอปสามารถฟื้นตัวได้ ผมคิดว่าวิธีที่ดีที่สุดคือ kill query อื่นที่กำลังรันอยู่ซึ่งขวาง ALTER ไว้

  • ฉันอ้างอิงคู่มือ Safe Migrations in Ecto ของ Fly.io หลายครั้งต่อสัปดาห์ Ecto คือ DB adapter ของ Elixir
    มันเป็นแหล่งอ้างอิงที่มีประโยชน์มากสำหรับเช็กอย่างรวดเร็วว่า migration แบบปกติเพียงพอหรือไม่ หรือว่าต้องใช้ขั้นตอนที่ซับซ้อนกว่านั้น
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • สิ่งที่ทำให้ประหลาดใจที่สุดอย่างหนึ่งตอนเริ่มใช้ Postgres คือ ดัชนี UNIQUE สามารถส่งผลต่อผลลัพธ์ของคิวรีที่รันพร้อมกันได้ เพราะมีการล็อกเพิ่มเข้ามา
    คิวรีอย่าง INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo); ถ้ารันพร้อมกันในโหมดปกติ อาจใส่ค่า bar ซ้ำได้ เพราะทรานแซกชันหนึ่งอาจมองไม่เห็นค่าสูงสุดใหม่ที่อีกทรานแซกชันสร้างไว้
    ถ้าเพิ่มดัชนี UNIQUE ดูเหมือนว่าทรานแซกชันที่ “แพ้” น่าจะได้ข้อผิดพลาดเรื่อง constraint แต่ในความเป็นจริง ทั้งสองทรานแซกชันจะไม่สำเร็จพร้อมกัน และ race condition ก็จะหายไป

    • นั่นไม่จริง ทรานแซกชันย่อยที่ แพ้ ในการแข่งกันที่ดัชนีจะถูกยกเลิก
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • ถ้าหมายถึงว่าแม้มีดัชนี UNIQUE แล้ว การแทรกทั้งสองครั้งยังสำเร็จ และสุดท้ายได้ ค่าซ้ำ จริง ถ้าเป็นแบบนั้นก็คือบั๊ก
    • ถ้าไม่ได้จำผิด ก็สามารถทำแบบไร้ downtime ได้โดยสร้างดัชนีธรรมดาด้วย CONCURRENTLY แล้วค่อยสร้าง UNIQUE constraint ที่ยังไม่ตรวจสอบ
      constraint นั้นจะมีผลกับ INSERT/UPDATE ใหม่เท่านั้น หลังจากนั้นถ้ารัน VALIDATE กับ constraint ก็จะกลายเป็น UNIQUE constraint แบบสมบูรณ์
    • ถ้ามันฟังดูน่าประหลาดใจ ก็อาจเป็นเพราะคุ้นกับภาษาแบบ imperative มากเกินไป
      เห็นด้วยว่านี่เป็นเรื่องที่เจอบ่อย แต่ปัญหาไม่ได้อยู่ที่ Postgres เท่านั้น แต่อยู่ในงานพัฒนาซอฟต์แวร์โดยรวม
    • เกิดขึ้นที่ isolation level ไหน?

  • เพราะกับดักพวกนี้เลยสร้าง Reshape [0] ขึ้นมา โดยมีเป้าหมายเพื่อทำ Schema migration แบบไร้ downtime ให้เป็นอัตโนมัติ
    ไม่กล้าบอกว่ามันหลีกเลี่ยงปัญหาได้ทุกอย่าง แต่กำลังสร้างผลิตภัณฑ์ใหม่ที่มีเป้าหมายแบบนั้นอยู่ ถ้าสนใจพื้นที่นี้ โดยเฉพาะ Postgres อยากให้ติดต่อมา: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • มีโอกาสที่จะใช้กับ crdb ได้ด้วยไหม?

  • อีกความผิดพลาดที่เห็นบ่อยคือคัดลอกตารางแต่ ลืมดัชนี
    CREATE TABLE SELECT * FROM WHERE <> ไม่ได้ทำงานแบบนั้น ผู้คนมักทำแบบนี้เวลาสร้างตารางสำรองหรือเตรียมลบข้อมูลจำนวนมาก

    • ถ้าเป็นกรณีสร้างตารางสำรอง คือกำลังจะทำงานที่ซับซ้อน คลุมเครือ และอาจพังในแบบที่คาดไม่ถึงได้ทันที ผมจะไม่สนใจดัชนีหรือ constraint เลย
      เป้าหมายคืออยากได้สำเนาข้อมูลที่มีอยู่ตรงนั้นทันที แม้อาจไม่เคยต้องใช้ เพื่อจะได้ไม่ต้องกู้คืนจาก DB backup และ WAL การสร้างดัชนีมีแต่จะเปลืองเวลาของเซิร์ฟเวอร์และพื้นที่ดิสก์
      ถ้างานพังหรือจำเป็นจริง ๆ ค่อยสร้างดัชนีพวกนั้นทีหลังได้
    • งั้นช่วยบอกวิธีที่เหมาะสมด้วยได้ไหม?

  • ส่วน “Case 2. การใช้ IF [NOT] EXISTS ผิด” ไม่ได้ยกตัวอย่างการใช้ผิดที่ดีนัก
    และจริง ๆ แล้วการใช้งานแบบนั้นก็ถูกต้องอยู่แล้ว มันสะอาด เรียบง่าย และไม่มีหลุมพรางแอบแฝง ถ้ามีแค่ไม่กี่ตาราง เครื่องมือ Schema migration ก็เป็นภาระเกินจำเป็น

    • หลุมพรางนั้นง่ายมาก คือ “ซ่อนปัญหาด้วยตรรกะ จนเพิ่มความเสี่ยงของสถานะที่ผิดปกติ”
      การเอาพลาสเตอร์ไปแปะบนข้อมูลที่ไม่ดีไม่ได้แก้ปัญหา มีแต่ซ่อนมันไว้ ปัญหาอาจระเบิดในภายหลังในรูปแบบที่คาดไม่ถึง และในเวลาที่แย่ที่สุด ขึ้นอยู่กับชนิดของปัญหา
      ในกรณีนี้ “ข้อมูลที่ไม่ดี” คือมีตาราง คอลัมน์ หรือวิวที่ควรมีหรือไม่ควรมี แต่กลับเป็นตรงกันข้าม ทำไมตารางที่ไม่ควรยังอยู่ถึงยังมีอยู่? การลบล้มเหลวหรือเปล่า? Schema ของตารางเดิมถูกต้องไหม? migration เดียวกันถูกรันซ้ำโดยบังเอิญหรือเปล่า?
      หลังจบแต่ละ migration แล้ว Schema ควรอยู่ในสถานะที่ถูกต้องอย่างแน่นอน ถ้าใน migration มี IF [NOT] EXISTS แปลว่า Schema หลัง migration ก่อนหน้านั้นไม่ได้ถูกทิ้งไว้ในสถานะที่ถูกต้องแน่ชัด การไม่มั่นใจในสถานะของ Schema ไม่ใช่เรื่องดี
    • ผมว่าบทความก็อธิบายการใช้ผิดได้ค่อนข้างดีแล้ว ประเด็นหลักคือการเปลี่ยน Schema ผ่านเส้นทางอื่นเป็นปัญหาเรื่องกระบวนการและ workflow จึงต้องแก้ที่ต้นเหตุโดยตรง
      ถ้าคอลัมน์ของตารางที่มีอยู่แล้วไม่ตรงกับสิ่งที่ migration กำลังจะสร้าง จะทำอย่างไร? IF EXISTS จะทำให้ migration ผ่านสำเร็จ แต่ Schema จะยังอยู่ในสถานะที่ไม่ดี แบบนี้ให้ migration ล้มเหลวอย่างรวดเร็ว จะดีกว่า

  • ขอทักท้วงเล็กน้อยเรื่องการใช้ int4 เป็น surrogate primary key
    สิ่งสำคัญไม่ใช่ ขนาดของดัชนี มากกว่าหรือ? สำหรับขนาดตารางเอง มันมี header 23 ไบต์และ alignment padding อยู่แล้ว ดังนั้นความต่าง 4 ไบต์จึงแทบไม่มีผล แต่ถ้าทำให้ใส่ดัชนีในหน่วยความจำได้มากขึ้น ก็อาจมีข้อดีได้ และ index entry เองก็มี header 8 ไบต์
    อีกอย่าง ตัวอย่าง 1 พันล้านแถวที่ยกมาดูใกล้กับค่าสูงสุดของ int4 มากจนรู้สึกน่ากังวล
    ถึงอย่างนั้นบทความก็ยอดเยี่ยม

    • ใช่ มีทั้งเรื่องขนาดดัชนีและขนาดบนดิสก์ Postgres แพ็กแถวของตารางบนดิสก์ได้แน่น แต่ใน RAM ไม่ได้เป็นแบบนั้น
      ถ้าอย่างนั้นหมายความว่าหน้า 8KB บนดิสก์อาจมีขนาดใหญ่กว่า 8KB ตอนอยู่ใน RAM ใช่ไหม?
      ดูเหมือนจะกระทบแค่ working memory ของข้อมูลแถวในตารางเท่านั้น ถึงอย่างนั้นก็ยังสำคัญ โดยเฉพาะเมื่อ Postgres จัดแถวแบบสุ่ม ทำให้ locality ของ range query แย่มาก เพียงแต่ยังไม่ถึงขั้นเป็นข้อสรุปชี้ขาด

  • เป็นนักพัฒนาที่ส่วนใหญ่ได้รับการปกป้องจากปัญหาฝั่ง DB มาตลอด ใน Django ผมพอรู้วิธีสร้าง migration สร้างตารางจาก model และคิวรีผ่าน ORM แต่หลายอย่างที่เกิดขึ้นข้างในยังรู้สึกเหมือน มนตร์ดำ
    ตอนนี้กำลังเริ่มบริษัทเอง เลยกังวลว่าจะต้องเจอปัญหาแบบนี้และต้องแก้เองคนเดียว ควรเริ่มเรียนรู้เรื่องที่ต้องทำในสภาพแวดล้อมจริงอย่างไรดี?

    • ก็ล้มเหลว แล้วเรียนรู้จากความผิดพลาด หรือไม่ก็จ้างนักพัฒนามา แล้วล้มเหลวและเรียนรู้ไปด้วยกัน

  • ผมชอบ Postgres แต่เกลียดมากที่มันไม่มีวิธี batch update/delete ในตัว
    นี่เป็นส่วนที่น่าหงุดหงิดที่สุด และแทบทุกเดือนที่เจอทางตัน ก็ต้องเขียน batcher ใหม่อีกครั้ง