สิ่งที่ใครสักคนควรบอกเกี่ยวกับ Postgres

 (challahscript.com)
7 คะแนน โดย GN⁺ 2024-11-13 | 2 ความคิดเห็น | แชร์ทาง WhatsApp
  • เอกสารทางการของ Postgres ยอดเยี่ยมมาก แต่ Postgres 17 PDF ยาวถึง 3,200 หน้า ทำให้ผู้เริ่มต้นเรียนรู้ทั้งการออกแบบสคีมา การทำงานของ SQL และกับดักด้านการปฏิบัติการจากเอกสารอย่างเดียวก่อนใช้งานจริงได้ยาก
  • ถ้าไม่มีเหตุผลพิเศษ ควร ทำข้อมูลให้เป็น normalized และการ denormalize เพื่อเพิ่มประสิทธิภาพการอ่านด้วยการเก็บข้อมูลซ้ำ ต้องยอมรับต้นทุนเรื่องความไม่สอดคล้องกันของข้อมูลและความซับซ้อนในการเขียนที่เพิ่มขึ้น
  • คีย์เวิร์ด SQL ไม่สนตัวพิมพ์ใหญ่เล็ก แต่ NULL ใกล้เคียงกับ “ไม่ทราบค่า” มากกว่า การเปรียบเทียบแบบเดียวกับ null ในภาษาโปรแกรมทั่วไปจึงอาจให้ผลลัพธ์ไม่ตรงคาด
  • ถ้าใช้ psql เป็น ก็แค่ใช้ pager, \x, .psqlrc, \pset null, การเติมคำอัตโนมัติ, คำสั่ง backslash และ \copy ให้ดี ก็ช่วยให้การอ่านผลลัพธ์ การสำรวจข้อมูล และการส่งออก CSV สะดวกขึ้นมาก
  • index, lock, transaction และ JSONB ทรงพลังมาก แต่ถ้าไม่เข้าใจ query plan และข้อจำกัดด้านการปฏิบัติการ ก็อาจนำไปสู่ปัญหาด้านประสิทธิภาพหรือความพร้อมใช้งานได้

บริบทที่ควรรู้ก่อนลุยเอกสารทางการชุดใหญ่

  • เอกสารทางการของ Postgres สำหรับเวอร์ชันปัจจุบันคือ 17 ถ้าพิมพ์เป็น PDF ขนาด US letter จะมี 3,200 หน้า และถ้าพิมพ์เป็น A4 จะมี 3,024 หน้า
  • มีความรู้เชิงปฏิบัติเกี่ยวกับ Postgres หลายอย่างที่รู้ไว้ก่อนใช้งานจะช่วยได้ และบางส่วนก็ใช้กับ SQL DBMS อื่นได้ด้วย แต่ขอบเขตการใช้ไม่ได้ชัดเจนเสมอไป

โดยพื้นฐานแล้วควร normalize ข้อมูล

  • Normalization คือกระบวนการลบข้อมูลซ้ำซ้อนหรือข้อมูลที่ไม่จำเป็นออกจากสคีมาฐานข้อมูล
  • ถ้าเก็บ user_email ไว้ตรง ๆ ในตาราง documents เมื่อผู้ใช้เปลี่ยนอีเมล ก็ต้องอัปเดตทุกแถวเอกสารของผู้ใช้นั้น
    • ทางเลือกคือให้แต่ละแถวใน documents อ้างอิงแถวในตารางอื่น เช่น users ด้วย foreign key user_id
  • ไม่จำเป็นต้องท่องจำ normal form แต่ละระดับอย่าง “1st normal form” ทั้งหมด แต่กระบวนการ normalize โดยทั่วไปมักนำไปสู่สคีมาที่ดูแลรักษาง่ายกว่า
  • Denormalization คือการเก็บข้อมูลซ้ำเพื่อให้อ่านได้เร็ว โดยไม่ต้องคำนวณข้อมูลบางอย่างใหม่ทุกครั้ง
    • เช่น ในแอปจัดกะพนักงาน อาจไม่คำนวณชั่วโมงทำงานสะสมของปีนี้ทุกครั้งจากผลรวม shift duration ทั้งหมด แต่คำนวณและเก็บไว้เป็นระยะ ๆ หรือเมื่อมีการเปลี่ยนแปลงเวลาทำงาน
    • ข้อมูลนี้อาจเก็บไว้ใน Postgres เอง หรือเก็บไว้ในชั้น cache อย่าง Redis ก็ได้
  • การ denormalize แทบจะมีต้นทุนเสมอ โดยต้นทุนหลักคือ ความเป็นไปได้ที่ข้อมูลจะไม่สอดคล้องกัน และความซับซ้อนของการเขียนที่เพิ่มขึ้น

คำแนะนำแบบ “อย่าทำแบบนี้” จากโปรเจกต์ Postgres

  • ในวิกิทางการของ Postgres มีรายการ “Don’t do this”
  • ไม่เป็นไรถ้ายังไม่เข้าใจทุกข้อ และข้อที่ยังไม่เข้าใจก็มักเป็นข้อผิดพลาดที่มีโอกาสทำพลาดน้อยกว่า
  • โดยเฉพาะคำแนะนำต่อไปนี้ควรจำไว้

พฤติกรรมของ SQL ที่ทำให้งงได้ง่าย

  • คีย์เวิร์ด SQL ไม่จำเป็นต้องเป็นตัวพิมพ์ใหญ่

    • คีย์เวิร์ด SQL ไม่แยกตัวพิมพ์ใหญ่เล็ก
    • คิวรีต่อไปนี้มีความหมายเหมือนกัน
    SELECT * FROM my_table WHERE x = 1 AND y > 2 LIMIT 10;
select * from my_table where x = 1 and y > 2 limit 10;
SELECT * from my_table WHERE x = 1 and y > 2 LIMIT 10;
    • คุณสมบัตินี้ไม่ได้จำกัดเฉพาะ Postgres

  • NULL ไม่เหมือน null/nil ในภาษาทั่วไป

    • NULL ของ SQL ใกล้เคียงกับ “ไม่ทราบค่า” มากกว่า null หรือ nil ในภาษาโปรแกรมทั่วไป
    • NULL = NULL ไม่ได้คืนค่า true แต่คืนค่า NULL
    • การเปรียบเทียบที่มีฝั่งหนึ่งเป็น NULL ส่วนใหญ่ก็จะได้ผลลัพธ์เป็น NULL ด้วย
    • สำหรับการเปรียบเทียบ NULL ควรใช้โอเปอเรชันต่อไปนี้
      • x IS NULL: ถ้า x เป็น NULL จะได้ true
      • x IS NOT NULL: ถ้า x ไม่เป็น NULL จะได้ true
      • x IS NOT DISTINCT FROM y: คล้าย x = y แต่ปฏิบัติต่อ NULL เหมือนค่าแบบปกติ
      • x IS DISTINCT FROM y: คล้าย x != y/x <> y แต่ปฏิบัติต่อ NULL เหมือนค่าแบบปกติ
    • ในเงื่อนไข WHERE จะคืนแถวเฉพาะเมื่อเงื่อนไขเป็น true เท่านั้น
      • SELECT * FROM users WHERE title != 'manager' จะไม่คืนแถวที่ title เป็น NULL
      • เพราะผลของ NULL != 'manager' คือ NULL
    • COALESCE จะคืนค่าตัวแรกที่ไม่ใช่ NULL จากหลายอาร์กิวเมนต์
    COALESCE(NULL, 5, 10) = 5
COALESCE(2, NULL, 9) = 2
COALESCE(NULL, NULL) IS NULL

ใช้ psql ให้มีประโยชน์มากขึ้น

  • ปรับให้อ่านผลลัพธ์ง่ายขึ้น

    • ถ้า query ตารางที่มีหลายคอลัมน์หรือค่าที่ยาวแล้วผลลัพธ์อ่านยาก อาจเป็นเพราะปิด pager อยู่
    • pager ของเทอร์มินัลช่วยให้เลื่อนดูข้อความยาวหรือผลลัพธ์ตารางจาก psql ได้ภายใน viewport
    • ตารางที่มีหลายคอลัมน์สามารถเปิด expanded mode ด้วย \pset expanded หรือ \x
    • ถ้าอยากใช้เป็นค่าเริ่มต้น ก็เพิ่ม \x ใน ~/.psqlrc ในโฮมไดเรกทอรีได้

  • ทำให้การแสดงผล NULL ชัดเจนขึ้น

    • ค่าเริ่มต้นไม่ได้ทำให้เห็นชัดนักว่าค่าไหนเป็น NULL ในผลลัพธ์
    • ใน psql สามารถกำหนดสตริงสำหรับแสดง NULL ได้
    \pset null '[NULL]'
    • ใช้สตริง Unicode ก็ได้ และถ้าอยากให้เป็นค่าเริ่มต้นก็เพิ่มคำสั่งเดียวกันใน ~/.psqlrc

  • ใช้การเติมคำอัตโนมัติและคำสั่ง backslash ให้คุ้ม

    • psql รองรับ การเติมคำอัตโนมัติ แบบ interactive console
    • พิมพ์บางส่วนของคีย์เวิร์ดหรือชื่อตารางแล้วกด Tab เพื่อเติมส่วนที่เหลือได้
    • คำสั่ง backslash ที่มีประโยชน์มีดังนี้
      • \?: รายการ shortcut ทั้งหมด
      • \d: แสดงรายการ relation หรือก็คือตารางและ sequence พร้อม owner
      • \d+: เหมือน \d แต่เพิ่มขนาดและ metadata บางส่วน
      • \d table_name: แสดงสคีมาตาราง ชนิดคอลัมน์ การเป็น nullable ค่า default index และ foreign key constraint
      • \e: แก้ไข query ใน editor เริ่มต้นที่กำหนดไว้ในตัวแปรแวดล้อม $EDITOR
      • \h SQL_KEYWORD: แสดงไวยากรณ์ของคีย์เวิร์ด SQL นั้นและลิงก์ไปเอกสาร

  • ส่งออก CSV และใช้ alias ใน SELECT

    • ใช้ \copy เพื่อบันทึกผลลัพธ์ query เป็น CSV ได้
    \copy (select * from some_table) to 'my_file.csv' CSV
    • ถ้าต้องการให้มีชื่อคอลัมน์ในบรรทัดแรก ให้เพิ่มตัวเลือก HEADER
    \copy (select * from some_table) to 'my_file.csv' CSV HEADER
    • \copy ช่วยเลี่ยงการต้องใช้สิทธิ์ที่สูงขึ้นแบบที่คำสั่ง COPY มาตรฐานต้องการ
    • คอลัมน์ผลลัพธ์ของ SELECT สามารถตั้ง alias ด้วย AS ได้
    SELECT vendor, COUNT(*) AS number_of_backpacks
FROM backpacks
GROUP BY vendor
ORDER BY number_of_backpacks DESC;
    • ใน GROUP BY และ ORDER BY สามารถอ้างอิงหมายเลขคอลัมน์ตามลำดับที่ปรากฏหลัง SELECT ได้
    SELECT vendor, COUNT(*) AS number_of_backpacks
FROM backpacks
GROUP BY 1
ORDER BY 2 DESC;
    • รูปแบบย่อนี้มีประโยชน์ แต่ควรหลีกเลี่ยงใน query ที่จะ deploy ไป production

เพิ่ม index แล้วไม่ได้แปลว่าจะถูกใช้งานเสมอไป

  • index และ query plan

    • index คือโครงสร้างข้อมูลที่ทำหน้าที่เป็น ไดเรกทอรีทางลัด สำหรับค้นหาแถวในตารางตามฟิลด์ที่กำหนด
    • index ที่พบบ่อยที่สุดคือ B-tree และใช้ได้กับทั้งเงื่อนไขเท่ากันแบบตรงตัว เช่น WHERE a = 3 และเงื่อนไขช่วง เช่น WHERE a > 5
    • ไม่สามารถสั่ง Postgres โดยตรงให้ใช้ index ตัวใดตัวหนึ่งได้
    • Postgres จะอิงจากสถิติที่เก็บไว้ของแต่ละตารางเพื่อคาดการณ์ว่า index จะเร็วกว่าการอ่านทั้งตารางแบบ sequential scan หรือไม่
    • ถ้าเติม EXPLAIN ไว้หน้า SELECT ... FROM ... ก็จะเห็น query plan ว่า Postgres จะรัน query อย่างไร
    • เวลาอ่าน query plan สามารถดู คู่มือ EXPLAIN ANALYZE ของ thoughtbot, เอกสาร pganalyze, เอกสารทางการ, และ explain.depesz.com ได้

  • ตารางเล็กและ index หลายคอลัมน์

    • ในตารางที่มีแถวน้อย เช่นฐานข้อมูลสำหรับพัฒนาในเครื่อง local, index อาจไม่ได้ช่วยมากนัก
    • ถ้ามีราว 100 แถว Postgres อาจตัดสินว่า sequential scan เร็วกว่า index
    • Postgres รองรับ index แบบหลายคอลัมน์
    CREATE INDEX CONCURRENTLY ON tbl (a, b);
    • เงื่อนไขอย่าง WHERE a = 1 AND b = 2 อาจเร็วกว่าเมื่อใช้งาน index แยกกันคนละตัวสำหรับ a และ b
    • เพราะสามารถเดิน B-tree เดียวและรวมเงื่อนไขการค้นหาได้อย่างมีประสิทธิภาพ
    • index (a, b) ยังช่วยให้ query ที่กรองแค่ a เร็วได้พอ ๆ กับ index เดี่ยวของ a
    • สำหรับ query อย่าง WHERE b = 5 ก็อาจเร็วขึ้นได้ แต่ไม่จำเป็นต้องดีที่สุด
      • เพราะ key ของ index เรียงตาม a ก่อนแล้วค่อย b จึงต้องไล่ผ่านค่าของ a ทั้งหมดเพื่อหาค่า b
    • ถ้าต้อง query หลายชุดของคอลัมน์ร่วมกัน ก็มักจะมีทั้ง (a, b) และ index เดี่ยวของ b
    • หรืออาจพึ่ง index เดี่ยวของ a และ b แยกกันตามความเหมาะสม

  • ใช้ text_pattern_ops กับ prefix match

    • อาจเก็บไดเรกทอรีแบบลำดับชั้นด้วยวิธี materialized path และต้องหาลูกหลานทั้งหมดที่ขึ้นต้นด้วย prefix บางตัว
    SELECT * FROM directories WHERE path LIKE '/1/2/3/%'
    • แม้จะสร้าง B-tree index ปกติบนคอลัมน์ path แล้ว query นี้ก็อาจไม่ใช้มัน
    CREATE INDEX CONCURRENTLY ON directories (path);
    • ถ้าต้องการลำดับระดับอักขระที่จำเป็นสำหรับ prefix match หรือ pattern match ต้องกำหนด operator class
    CREATE INDEX CONCURRENTLY ON directories (path text_pattern_ops);

ปัญหาด้านการปฏิบัติการที่เกิดจาก lock และ transaction

  • lock ของ Postgres

    • lock หรือ mutex เป็นกลไกที่ทำให้งานที่เสี่ยงอันตรายทำได้พร้อมกันครั้งละไคลเอนต์เดียว
    • ในฐานข้อมูล การอัปเดต object อย่าง row, table, view ต้องสำเร็จทั้งหมดหรือไม่สำเร็จเลย และเพื่อป้องกันไม่ให้การทำงานพร้อมกันทำให้สำเร็จแค่บางส่วน จึงต้องขอ lock กับ object ที่เกี่ยวข้อง
    • ระดับ table lock ของ Postgres มีหลายระดับ ตั้งแต่จำกัดน้อยไปจนถึงจำกัดมาก
      • ACCESS SHARE: SELECT
      • ROW SHARE: SELECT ... FOR UPDATE
      • ROW EXCLUSIVE: UPDATE, DELETE, INSERT
      • SHARE UPDATE EXCLUSIVE: CREATE INDEX CONCURRENTLY
      • SHARE: CREATE INDEX ที่ไม่ใช้ CONCURRENTLY
      • ACCESS EXCLUSIVE: ALTER TABLE, ALTER INDEX หลายรูปแบบ
    • บนตารางเดียวกัน การทำงานต่อไปนี้อาจทำได้พร้อมกันหรือจำเป็นต้องรอ
      • UPDATE ระหว่าง SELECT: ทำได้
      • UPDATE ระหว่าง CREATE INDEX CONCURRENTLY: ทำได้
      • SELECT ระหว่าง CREATE INDEX: ทำได้
      • SELECT ระหว่าง ALTER TABLE: โดยทั่วไปต้องรอ
      • ALTER TABLE ระหว่าง SELECT: โดยทั่วไปต้องรอ
    • ALTER TABLE บางรูปแบบอาจต้องการ lock ที่อ่อนกว่านี้ และดูข้อมูลทั้งหมดได้จาก เอกสาร explicit locking ทางการ และ คู่มือ lock conflict ตาม operation

  • ALTER TABLE ที่ช้าและคิวของ lock

    • ถ้า ALTER TABLE ใช้เวลานาน แม้แต่ SELECT ที่อ่านตารางเดียวกันก็อาจถูกบล็อก
    • ถ้าเป็นตารางสำคัญอย่าง users ที่ทุก request ของเว็บแอปอ้างอิง คำขออาจค้างจน timeout และคืน 503
    • สาเหตุที่ทำให้ ALTER TABLE ช้าได้บ่อยมีดังนี้
      • การเพิ่มคอลัมน์ที่มี default แบบ non-constant
      • การเปลี่ยนชนิดของคอลัมน์
      • การเพิ่ม uniqueness constraint
    • หลัง Postgres 11 ปัญหาที่การเพิ่มคอลัมน์พร้อม default ทุกแบบทำให้ช้าถูกแก้ไปแล้ว และที่ยังเป็นปัญหาได้คือ default แบบ non-constant
    • แม้ตัว ALTER TABLE เองจะเป็นงานที่เร็ว ก็จะยังไม่เริ่มจนกว่าจะได้ lock
      • เช่น ถ้ามี SELECT ช้าจากแดชบอร์ดภายในที่มีมานานกำลังรันอยู่ ALTER TABLE ก็ต้องรอ
    • lock ของ Postgres สร้าง คิวรอ ได้ ดังนั้น query ถัด ๆ มาบนตารางเดียวกันที่เข้ามาหลัง ALTER TABLE ซึ่งกำลังรออยู่ ก็อาจต้องรอต่อด้วย
    • อ่านสถานการณ์แบบเดียวกันเพิ่มเติมได้ที่ Migrations and exclusive locks

  • transaction ที่เปิดนานก็อันตราย

    • transaction คือวิธีรวมหลายคำสั่งฐานข้อมูลให้เป็น all-or-nothing โดยเริ่มด้วย BEGIN และจบด้วย COMMIT
    • การเปลี่ยนแปลงระหว่าง transaction จะยังไม่เห็นจากไคลเอนต์อื่น และจะถูกเผยแพร่สู่ฐานข้อมูลเมื่อ COMMIT
    • เหมาะกับงานอย่างการโอนเงิน ที่การลดเงินจากบัญชีหนึ่งและเพิ่มอีกบัญชีหนึ่งต้องสำเร็จพร้อมกันหรือยกเลิกพร้อมกัน
    • ถ้า transaction ได้ lock มาแล้ว ก็จะถือ lock นั้นไว้จนถึง COMMIT
    • ถ้า BEGIN แล้ว UPDATE row บางแถวและลุกไปทำอย่างอื่น DELETE ของ row เดียวกันจากไคลเอนต์อื่นก็จะหยุดรอจนกว่า transaction จะ commit
    • transaction ที่เปิดค้างนานเกินจำเป็นอาจบล็อก query หรือการอัปเดตจากไคลเอนต์อื่นได้

JSONB เป็นเครื่องมือที่คมมาก

  • ปัญหาด้านประสิทธิภาพและสคีมาของ JSONB

    • JSONB ยืดหยุ่นมาก แต่ถ้าใช้ผิดทางก็มีข้อเสียมากเช่นกัน
    • Postgres ไม่ติดตามสถิติของคอลัมน์ JSONB ดังนั้น query แบบเท่ากันตรงตัวบนคอลัมน์ JSONB เดียวอาจช้ากว่ามากเมื่อเทียบกับ query บนคอลัมน์ปกติหลายคอลัมน์
    • มีกรณีตัวอย่างหนึ่งที่ ช้าลง 2000 เท่าเพราะ JSONB
    • คอลัมน์ JSONB ใส่อะไรก็ได้แทบทุกอย่าง จึงทรงพลังมาก แต่ก็แทบไม่มีการรับประกันเรื่องโครงสร้าง
    • กับตารางปกติ เราดูสคีมาแล้วพอคาดเดาผลลัพธ์ของ query ได้ แต่กับ JSONB เราอาจไม่แน่ใจว่าชื่อ key เป็น camelCase หรือ snake_case หรือสถานะเป็น boolean หรือ enum
    • คุณสมบัติแบบ static type ที่ข้อมูล Postgres ปกติมี จะไม่ใช้กับ JSONB ในลักษณะเดียวกัน

  • ความแปลกของการเปรียบเทียบชนิดใน JSONB

    • ถ้าต้องการหาแถวที่ฟิลด์ brand ในคอลัมน์ JSONB data ของตาราง backpacks มีค่าเป็น JanSport query ต่อไปนี้จะใช้ไม่ได้
    select * from backpacks where data['brand'] = 'JanSport';
    • Postgres คาดว่าชนิดของค่าด้านขวาในการเปรียบเทียบต้องตรงกับค่าด้านซ้าย และค่าด้านขวาต้องเป็นเอกสาร JSON ที่ถูกต้อง
    • เอกสาร JSON ต้องเป็น object, array, string, number, boolean หรือ null ดังนั้น JanSport เดี่ยว ๆ จึงไม่ใช่ JSON ที่ถูกต้อง
    • query ที่ถูกต้องคือต้องเปรียบเทียบกับ JSON string หรือแปลงค่าด้านซ้ายให้เป็น text ของ Postgres
    select * from backpacks where data['brand'] = '"JanSport"';

select * from backpacks where data['brand'] = '"JanSport"'::jsonb;

select * from backpacks where data->>'brand' = 'JanSport';
    • NULL ของ SQL และ null ของ JSONB ทำงานต่างกัน
      • 'null'::jsonb = 'null'::jsonb ให้ค่า true แต่ NULL = NULL ให้ค่า NULL
    • JSONB มี โอเปอเรเตอร์และฟังก์ชันเฉพาะ อยู่มาก จึงจำทั้งหมดในครั้งเดียวได้ยาก
    • ใน Postgres มีทั้ง JSON ซึ่งเก็บค่า JSON เป็นข้อความ และ JSONB ซึ่งแปลงเป็นรูปแบบไบนารีที่มีประสิทธิภาพ
    • JSONB มีข้อดีอย่างการทำ index ได้ ส่วนรูปแบบ JSON อาจมองว่าเหมาะกับกรณีพิเศษ

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

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

 
bbulbum 2024-11-19

สิ่งที่ไม่ควรทำ สักวันหนึ่งคงต้องหาเวลาอ่านดูสักครั้ง
 
GN⁺ 2024-11-13
ความเห็นจาก Hacker News

  • โดยทั่วไป PostgreSQL แยกตัวพิมพ์เล็ก-ใหญ่ แต่การเขียนคีย์เวิร์ด SQL เป็นตัวพิมพ์ใหญ่ก็มักเป็นความพยายามเพิ่มความอ่านง่ายด้วยการจับแพตเทิร์นด้วยสายตา
    ไม่ได้จำเป็นเสมอไป แต่ถ้าต้องดีบักคิวรีของคนอื่น ก็น่าจะโยนเข้า prettifier เพื่อไล่อ่านนิยามได้เร็วโดยไม่สะดุดกับรูปแบบไวยากรณ์จุกจิก
    เหมือนกับการจัดระเบียบโค้ดในภาษาอื่น โครงสร้างทางสายตาอย่างการเยื้องที่สม่ำเสมอช่วยลดเวลาที่ใช้ทำความเข้าใจส่วนที่เดาง่ายอยู่แล้ว และทำให้โฟกัสกับจุดสำคัญได้
    แต่ส่วนตัวเกลียดการผสมตัวพิมพ์เล็ก-ใหญ่ใน identifier แบบ actuallyUsingCaseInIdentifiers มาก และไม่อยากเห็น คอลัมน์ที่ต้องใส่เครื่องหมายอัญประกาศคู่ เวลาเช็กผ่าน CLI

    • identifier ตัวพิมพ์ใหญ่ดูเหมือนบล็อกที่สลับแทนกันได้ ทำให้อ่านช้ากว่ารูปร่างของ คำ ที่ตัวพิมพ์เล็กมี
    • เวลาทำงานกับ SQL แบบโต้ตอบ การรู้ความต่างนี้มีประโยชน์พอสมควร
      ถ้าเป็นคิวรีชั่วคราวที่พิมพ์เร็ว ๆ แล้วทิ้ง ไม่มีใครเห็น ก็ไม่สนตัวพิมพ์เล็ก-ใหญ่ แต่ SQL ที่จะ commit เข้า repository จะเขียนคำสั่งเป็น ALL CAPS
    • เข้าใจว่าตัวพิมพ์ใหญ่เคยทำหน้าที่เป็น syntax highlighting บนจอขาวดำ
      ตอนนี้มีสีแล้วเลยไม่จำเป็นอีกต่อไป แต่เป็นความทรงจำเก่า ๆ เลยไม่มีแหล่งอ้างอิง
    • PostgreSQL พับ identifier ไปเป็นตัวพิมพ์เล็ก ในขณะที่มาตรฐานพับเป็นตัวพิมพ์ใหญ่ จึงถือว่าผิดมาตรฐานในเรื่อง การจัดการตัวพิมพ์เล็ก-ใหญ่
      ถึงอย่างนั้นก็ไม่ควรผสม identifier ที่ใส่เครื่องหมายอัญประกาศกับแบบไม่ใส่ และการดูโครงสร้างภายในก็แทบไม่ได้เป็นมาตรฐานอยู่แล้ว จึงไม่ได้มีความหมายมากนัก
    • อยากได้คำแนะนำ prettifier หรือ linter สำหรับ SQL

  • เพิ่งเคยเห็นหัวข้อ “don’t do this” ในวิกิของ PostgreSQL แต่มีประโยชน์มาก: https://wiki.postgresql.org/wiki/Don%27t_Do_This

    • ถ้าฟีเจอร์พวกนี้เป็นกับดักที่พลาดกันง่ายขนาดนั้น ก็สงสัยว่าทำไมไม่ทำเป็น deprecated
      เช่น ในสคีมาใหม่อาจปิดฟีเจอร์อย่าง table inheritance ไปเลย และถ้าจะเปิดใหม่ก็ควรต้องตั้งค่าแบบจงใจให้ยุ่งยากหน่อย
    • ทำให้นึกถึง SQL Anti-patterns ซึ่งมองว่าเป็นหนังสือที่ทุกคนที่ทำงานกับฐานข้อมูลควรอ่าน
    • ทำให้กลับมาทบทวนนิสัยบางอย่างที่ติดมาจากฝั่ง MySQL

  • หลายอย่างที่พูดถึงที่นี่ไม่ได้ใช้เฉพาะกับ PostgreSQL
    เช่น พฤติกรรมแปลก ๆ ของ NULL หรือเรื่องลำดับคอลัมน์ในดัชนี และโดยเฉพาะปฏิสัมพันธ์ระหว่าง NULL กับดัชนี/ข้อกำหนด unique ที่แม้แต่ใน MySQL ก็ไม่ตรงสัญชาตญาณ
    ตัวอย่างเช่น ถ้ามีตารางผู้ใช้ที่ email ห้ามเป็น NULL แต่ username เป็น NULL ได้ แล้วตั้งข้อกำหนด unique ที่ (email, username) คุณก็ยังใส่ email เดิมซ้ำได้หลายครั้งเมื่อ username เป็น NULL เพราะ NULL ไม่เท่ากับ NULL อื่น

    • เพิ่มเติมคือ ตั้งแต่ PostgreSQL 15 เป็นต้นมา สามารถมีผลกับพฤติกรรมนี้ได้ใน constraint และ unique index ด้วย NULLS [NOT] DISTINCT
      https://www.postgresql.org/docs/devel/sql-createtable.html#S...
    • มองว่าค่าเริ่มต้นนี้ใช้ได้ดีในเชิงปฏิบัติ
      use case ที่ต้องการพฤติกรรมตรงกันข้ามมีน้อยกว่ามาก

  • ถ้าพูดแค่ว่า “ถ้าไม่มีเหตุผลที่ดี ก็ควรทำข้อมูลให้เป็น normalized” แล้วจบ แบบนั้นไม่ค่อยดี
    แม้แต่หน้าที่ผู้เขียนลิงก์ไว้ก็ยังมี รูปแบบ normalization รวม non-normal form ด้วยถึง 11 แบบ ซึ่งคนส่วนใหญ่ไม่รู้ด้วยซ้ำว่าคืออะไร และในนั้น 7 แบบก็แทบไม่มีโอกาสได้ใช้
    ไม่ควรทำให้คนไปไล่หาว่าต้องใช้ normal form ที่สูงขึ้นเรื่อย ๆ

    • ถึงอย่างนั้น ผู้เขียนก็มีเพิ่มย่อหน้าอธิบายว่าหมายถึงอะไรในภาพรวม ซึ่งมองว่ามาถูกทาง
      แม้แต่ในโปรเจกต์ที่เพิ่งย้ายไปไม่นาน ก็ยังต้องแก้ปัญหาแบบนี้อยู่หลายจุด เพราะแทบไม่มีเหตุผลให้เก็บข้อมูลซ้ำ
    • ถ้าบทความนี้ตั้งใจเขียนสำหรับมือใหม่ คำตอบตอนที่ไม่แน่ใจแทบจะเป็น Third Normal Form เสมอ
    • กฎทั่วไปคือ normalize ให้มากที่สุดก่อน แล้วค่อย denormalize จนกว่าจะได้ประสิทธิภาพที่ต้องการ

  • ทิปข้อแรกคือให้ทำ VACUUM ทุกวัน
    ตอนเริ่มต้นใหม่ ๆ ไม่รู้เรื่องนี้เลย จึงไม่ได้ทำ VACUUM ให้ฐานข้อมูลของ reddit เลย และวันหนึ่งพอจำเป็นต้องรันขึ้นมาก็ทำให้ reddit ล่มไปเกือบทั้งวันระหว่างรอมันทำเสร็จ

    • ดูเหมือนตอนนั้นจะยังไม่มี autovacuum
      ถ้าเป็นขนาดของ reddit ก็ยังน่าแปลกใจที่ transaction ID ไม่หมดไปก่อน

  • อยากให้เหล่านักพัฒนาใส่ใจกับ การทำให้เป็นบรรทัดฐาน (normalization) มากกว่านี้ และเลิกยัดทุกอย่างลงในคอลัมน์ JSONB

    • ตั้งแต่ก่อนที่ฐานข้อมูลจะเก็บ JSON แบบมีโครงสร้างได้อย่างสะดวก นักพัฒนามือใหม่ก็มักถกเถียงกันอย่างดุเดือดบนโต๊ะเรื่องระดับของการทำ normalization ที่เหมาะสมอยู่แล้ว
      ส่วนนักพัฒนาที่มีประสบการณ์มากกว่าจะรู้ว่าคำตอบที่ถูกคือ อย่าทำข้อมูลซ้ำเลยนอกจากคีย์ และทำ denormalization เฉพาะเมื่อจำเป็นจริง ๆ เท่านั้น
      ต่อมาฐานข้อมูลอย่าง Mongo ก็ปรากฏขึ้นและนำเสนอ “สิ่งคล้ายฐานข้อมูล” ที่ทำ normalization ได้ยากหรือแทบไม่มีความหมาย ซึ่งยิ่งไปส่งเสริมคนกลุ่มนั้น และผลลัพธ์ก็คือช่วงเวลาสั้น ๆ ที่งานออกแบบฐานข้อมูลอันเลวร้ายกับกองขยะที่บำรุงรักษาไม่ได้เติบโตงอกงาม
      ตอนนี้ลูกตุ้มเริ่มแกว่งกลับมา ผู้คนกลับมาค้นพบข้อดีของฐานข้อมูลแบบ normalized อีกครั้ง แต่ คอลัมน์ JSON ก็ยังคงเป็นช่องทางหนีที่เปิดโอกาสให้แนวปฏิบัติแย่ ๆ เติบโตได้
    • มีเหตุผลอยู่สองข้อในการใช้คอลัมน์ JSONB
      ข้อแรกคือเพื่อเก็บ JSON เอง หากเว็บเซิร์ฟเวอร์เรียกใช้ API ของบุคคลที่สามแล้วบันทึก API response ดิบลงใน คอลัมน์ JSONB ก่อนจะประมวลผลจากตรงนั้น ก็จะมีบันทึกที่ตรวจสอบย้อนหลังได้ไว้ใช้ตอนดีบักปัญหาที่มาจาก API นั้น
      ข้อสองคือเพื่อเก็บ sum type การที่ SQL ไม่รองรับ sum type อาจถือเป็นข้อบกพร่องใหญ่ที่สุดอย่างหนึ่งของการทำโมเดลข้อมูลในฐานข้อมูล SQL
      วิธีแก้เลี่ยงมีอยู่หลายแบบ และ “ก็แค่ใส่มันลงในคอลัมน์ JSONB แล้วตรวจสอบความถูกต้องในแอปพลิเคชัน” ก็เป็นหนึ่งในนั้น แต่ไม่มีวิธีไหนที่ยอดเยี่ยมเป็นพิเศษ
    • ต่อให้ใส่ใจกับ normalization สุดท้ายก็มักจะมีลิ้นชัก JSONB สารพัดของ โผล่มาอยู่ดี
      ตราบใดที่คุณไม่ได้เขียนคิวรีแย่ ๆ ลงไปในนั้นแทนที่จะดึงค่าใน JSONB ออกมาเป็นคอลัมน์แยก ผมก็ไม่คิดว่านั่นเป็นปัญหาใหญ่อะไรในตัวมันเอง
    • ทุกวันนี้นักพัฒนาส่วนใหญ่ที่ใช้เครื่องมือแบบนี้ แทบจะกำลังสร้าง ระบบจัดการฐานข้อมูล ของตัวเองขึ้นมา แล้วปล่อยให้ DBMS อื่นรับหน้าที่แค่เรื่อง persistence
      เพราะถ้าตอบโจทย์ด้าน persistence ได้สำเร็จ ก็แทบไม่มีแรงกดดันให้ต้องคิดเรื่องการออกแบบที่ดีอย่างจริงจัง
      จะเหมาะหรือไม่ที่จะสร้าง DBMS ซ้อนบน DBMS อีกชั้นก็ยังน่าสงสัย แต่อย่างน้อยตอนนี้สภาพก็เป็นแบบนั้น
    • ถ้าจะทำแนวทางนี้ให้ดีจริง จำเป็นต้องมี ขั้นตอน schema migration ที่รวมถึงความสามารถในการ rollback การเปลี่ยนแปลงสคีมาด้วย
      ถ้าคอลัมน์ใหม่ทำให้ประสิทธิภาพพังหรือก่อปัญหา ก็ควรย้อนกลับได้
      หากมีเครื่องมือ CLI เข้ามาเกี่ยวข้อง ก็ต้องจัดการด้วยว่ารับ downtime ได้มากแค่ไหน จะทำการอัปเดตเวอร์ชันแบบซิงก์พร้อมกันทั้งบริษัทได้หรือไม่ หรือจะต้องรองรับทั้งสคีมาเก่าและใหม่ไปอีกระยะหนึ่ง
      ถ้าฐานข้อมูลไม่ได้เป็นส่วนหนึ่งของผลิตภัณฑ์หลักของทีม เรื่องเหล่านี้อาจหายไปทั้งหมด

  • ผมเขียนบทความนี้ขึ้นมาเพื่อช่วยมือใหม่: https://tomcam.github.io/postgres/

  • บทความดีมากจริง ๆ และผมไม่เคยรู้มาก่อนว่าเอกสารของ PostgreSQL มีถึง 3200 หน้า
    ผมใช้มันมาสักพักแล้วและเรียนรู้ไปตามความจำเป็นเรื่อย ๆ ผมเองก็ชอบเอกสารทางการมาก และก็ชอบอ่านบทความเฉพาะเรื่องเมื่อถึงเวลาที่ต้องใช้หัวข้อนั้น
    ถ้าผู้เขียนเพิ่มไว้ที่ https://challahscript.com/what_i_wish_someone_told_me_about_... ว่าอินเด็กซ์คอลัมน์ (b, a) ก็ทำงานได้ดีเวลา query ด้วย b อย่างเดียว ก็น่าจะเป็นประโยชน์กับผู้อ่าน
    ตอนพูดถึงการ query ด้วย a อย่างเดียวก็มีการสื่อไว้ประมาณหนึ่งแล้ว แต่ถ้าจะเขียนให้ชัดกว่านี้ก็คงไม่เสียหาย
    ส่วนเรื่อง JSON/JSONB ผมแทบไม่ได้ใช้เลย เลยไม่ค่อยได้เห็นมากนัก

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

  • เกือบทั้งหมดของบทความนี้ใช้ได้กับ ฐานข้อมูล MVCC อื่น ๆ อย่าง MySQL ด้วย
    รายละเอียดอาจต่างกัน แต่ MySQL ก็เจอปัญหาคล้าย ๆ กัน เช่น ทรานแซ็กชันยาว ๆ และการถือ metadata lock ระหว่าง ALTER รวมถึงปัญหาสนุก ๆ อื่น ๆ แบบเดียวกัน