เทคนิคการปรับแต่ง PostgreSQL แบบนอกกรอบ

 (hakibenita.com)
6 คะแนน โดย GN⁺ 2026-01-21 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • แนะนำ 3 แนวทางสร้างสรรค์ สำหรับ เพิ่มประสิทธิภาพคิวรี PostgreSQL โดยหลุดจากกรอบเดิม แทนการเพิ่มดัชนีหรือเขียนคิวรีใหม่
  • ได้แก่ การตัด full table scan ด้วย CHECK constraint, การปรับแต่งข้อมูล cardinality ต่ำด้วย function-based index, และ การทำ unique constraint ด้วย Hash index
  • 1. ใช้การตั้งค่า constraint_exclusion เพื่อป้องกันการสแกนที่ไม่จำเป็นในคิวรีที่มีเงื่อนไขผิด
  • 2. ใช้ function-based index และ virtual generated column เพื่อลดขนาดดัชนีและทำให้คิวรีมีความสม่ำเสมอ
  • 3. ผสาน Hash index กับ exclusion constraint เพื่อทำ unique constraint สำหรับคอลัมน์ข้อความขนาดใหญ่ได้อย่างมีประสิทธิภาพ และประหยัดพื้นที่จัดเก็บอย่างมาก

ตัด full table scan ด้วย CHECK constraint

  • แม้คอลัมน์ plan จะมี CHECK constraint ที่ยอมรับเฉพาะค่า 'free', 'pro' เท่านั้น แต่ถ้ารันคิวรีผิดเป็น 'Pro' PostgreSQL ก็ยังสแกนทั้งตาราง
    • ใน execution plan จะอ่านครบ 100,000 แถว ทั้งที่ผลลัพธ์จริงมี 0 แถว
  • หากตั้งค่าพารามิเตอร์ constraint_exclusion เป็น 'on' PostgreSQL จะพิจารณา constraint และ ข้ามการสแกนทั้งหมด
    • เวลารันลดจาก 7.4ms เหลือ 0.008ms
  • ค่าเริ่มต้นคือ 'partition' และในคิวรีง่าย ๆ อาจมี planning overhead เพิ่มขึ้นแทน
    • แต่ในสภาพแวดล้อมแบบ BI และรายงาน ที่ผู้ใช้มักใส่เงื่อนไขผิดบ่อย การตั้งเป็น 'on' มีประโยชน์

ปรับแต่งข้อมูล cardinality ต่ำด้วย function-based index

  • ในตาราง sale ที่มีข้อมูลการขาย 10 ล้านรายการ เมื่อต้องรัน คิวรีสรุปยอดขายรายวัน จะใช้เวลา 627ms หากสแกนทั้งตาราง
  • หากเพิ่ม B-Tree index ที่คอลัมน์ sold_at เวลาจะลดเหลือ 187ms แต่ดัชนีมีขนาดใหญ่ถึง 214MB
  • หากสร้าง function-based index บน expression date_trunc('day', sold_at) ขนาดจะลดเหลือ 66MB และเวลาเหลือ 145ms ซึ่งเร็วขึ้นอีก
    • เป็นไปได้เพราะข้อมูล cardinality ต่ำทำให้เกิด index deduplication ได้
  • แต่ expression ในคิวรีต้องตรงกับนิยามดัชนีแบบเป๊ะ จึงต้อง รักษาความสม่ำเสมอของ expression
    • วิธีหนึ่งคือสร้าง VIEW ที่มี expression เดียวกันไว้ หรือ
    • เพิ่ม virtual generated column ที่รองรับตั้งแต่ PostgreSQL 18 เพื่อทำให้ความสม่ำเสมอเป็นอัตโนมัติ
  • เมื่อใช้ virtual generated column ดัชนีจะถูกใช้งานโดยอัตโนมัติ ทำให้ได้ทั้ง ดัชนีขนาดเล็ก คิวรีที่เร็ว และ expression ที่สม่ำเสมอ
  • อย่างไรก็ตาม ใน PostgreSQL 18 ยัง ไม่รองรับการสร้างดัชนีบน virtual column และคาดว่าจะรองรับในเวอร์ชัน 19

ทำ unique constraint ด้วย Hash index

  • ในตาราง urls ที่เก็บ URL ยาว ๆ หากสร้าง unique index แบบ B-Tree เพื่อ ป้องกัน URL ซ้ำ ดัชนีจะมีขนาดถึง 154MB
  • Hash index มีขนาดเล็กกว่ามาก เพราะไม่ได้เก็บค่าจริง แต่เก็บเฉพาะค่าแฮช
    • โดยปกติ PostgreSQL ไม่รองรับ unique Hash index โดยตรง แต่
    • สามารถใช้ exclusion constraint ในรูปแบบ EXCLUDE USING HASH (url WITH =) เพื่อ อ้อมไปทำ unique constraint ได้
  • วิธีนี้ยังคงเกิดข้อผิดพลาดเมื่อพยายาม insert ข้อมูลซ้ำ และ ประสิทธิภาพคิวรีก็เร็วกว่า B-Tree ด้วย (0.022ms vs 0.046ms)
  • ดัชนีมีขนาด 32MB ซึ่ง เล็กกว่า B-Tree มากกว่า 5 เท่า
  • ข้อเสีย:
    • อ้างอิงด้วย foreign key ไม่ได้ (ใช้ constraint REFERENCES ไม่ได้)
    • มีข้อจำกัดด้านความเข้ากันได้กับคำสั่ง INSERT ... ON CONFLICT
    • สามารถใช้ ON CONFLICT ON CONSTRAINT หรือ MERGE แทนได้
  • Hash index เหมาะกับการ รับประกันความไม่ซ้ำในคอลัมน์ข้อความขนาดใหญ่ และเป็น ทางเลือกที่ประหยัดพื้นที่ เมื่อไม่ต้องใช้ foreign key

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

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

 
GN⁺ 2026-01-21
ความคิดเห็นบน Hacker News

  • ดัชนีมีขนาด 214MB ซึ่งประมาณครึ่งหนึ่งของขนาดทั้งตาราง
    จากมุมมองนักวิเคราะห์ถือว่าดี แต่ในแง่ประสิทธิภาพการเขียนจะเกิดปัญหา write amplification
    การออกแบบดัชนีจะแตกต่างกันไปตามสัดส่วนการอ่าน/เขียน และนี่จึงเป็นเหตุผลที่มี data warehouse หรือ read replica
    ถ้าต้องรองรับผู้ใช้จำนวนมากมาก ก็ควรหลีกเลี่ยงการมีดัชนี BI/OLAP บน OLTP DB

    • คิดว่าน่าจะดีถ้า PostgreSQL รองรับ clustered index (Index Organized Table ของ Oracle)
      ถ้าแพตเทิร์นการเข้าถึงตารางคงที่ ตัวตารางเองก็อาจกลายเป็นดัชนีและได้ประสิทธิภาพโดยไม่มี write amplification

  • คิดว่าตัวอย่างแรกน่าจะนิยาม Plan เป็น enum type จะดีกว่า
    มันเบากว่าข้อความ และเมื่อมีการใส่ตัวกรองผิดก็จะให้ error แทนที่จะคืนผลลัพธ์ว่าง ทำให้ปลอดภัยกว่า

  • เป็นบทความที่ยอดเยี่ยม ใช้ PostgreSQL กับ MySQL มาหลายสิบปีแล้ว แต่พออ่านบทความนี้ก็ยังรู้สึกว่าตัวเองรู้เพียง ส่วนเล็ก ๆ ของความเป็นไปได้ทั้งหมด

    • ฉันก็ใช้ Postgres มานานเกิน 10 ปีแล้ว แต่ทุกครั้งที่เปิดเอกสารก็ยังรู้สึกว่า เพิ่งแตะได้แค่ผิวเผิน มันเป็นระบบที่ทรงพลังมากจริง ๆ
    • PostgreSQL เหมือน Emacs ดูภายนอกเรียบง่าย แต่จริง ๆ แล้วมีความยืดหยุ่นระดับระบบปฏิบัติการ

  • สิ่งที่น่าสนใจที่สุดคือคำสั่ง MERGE ที่กล่าวถึงตอนท้ายบทความ
    ปกติจะใช้ INSERT ... ON CONFLICT DO UPDATE เพื่อทำ upsert แต่ MERGE ดูเหมือนจะทรงพลังกว่าและใช้ได้ในหลายสถานการณ์มากกว่า

    • MERGE อยู่ในมาตรฐาน SQL มานานแล้ว แต่ Postgres เลื่อนการนำมาใช้เพราะ ปัญหาความไม่เป็นอะตอมของโมเดล MVCC
      มีอธิบายไว้ใน บทความบล็อกของ pganalyze ด้วย
      ส่วนตัวฉันชอบ INSERT ... ON CONFLICT มากกว่า และจะใช้ MERGE เฉพาะตอนที่จำเป็นจริง ๆ พร้อมระวังเรื่อง การจัดการ error เป็นพิเศษ
    • ในแง่ concurrency แล้ว INSERT ... ON CONFLICT คาดเดาพฤติกรรมได้มากกว่า
      ดู บทความเปรียบเทียบบน modern-sql.com
    • ถ้าเป็นการแทรกข้อมูลแบบแบตช์จำนวนมาก การใช้ COPY INTO ในรูปแบบ binary format จะเร็วที่สุด แทบไม่มี overhead ฝั่งเซิร์ฟเวอร์เลย

  • BRIN index ที่บทความไม่ได้พูดถึงก็น่าสนใจ
    ถ้าข้อมูลเพิ่มขึ้นแบบโมโนโทนิก มันเป็นดัชนีที่เล็กและเร็วมากอย่างเหมาะสม

    • ไม่จำเป็นต้องเป็นข้อมูลโมโนโทนิกแบบสมบูรณ์ก็ได้ ถ้าส่วนใหญ่เป็นแบบนั้นก็ทำงานได้ดีพอ
      เช่น ข้อมูล timestamp ที่เซิร์ฟเวอร์รับเข้ามา ซึ่งอาจมีลำดับคลาดเคลื่อนเล็กน้อยก็ยังเหมาะ
      สำหรับ UUIDv7 อาจต้องปรับ pages_per_range

  • รู้สึกเสียดายเสมอที่ hash index ตั้ง unique constraint ไม่ได้
    ดูเหมือนถ้ามีแค่ glue code สำหรับแปลงเป็น exclusion constraint ก็น่าจะแก้ได้ เลยสงสัยว่าทำไมยังไม่มี

  • การตรวจสอบความเป็นเอกลักษณ์ด้วยแฮชไม่ได้รองรับในดัชนีเพราะ จัดการ collision ไม่ได้
    วิธีแก้ที่เสนอมาก็เจอปัญหาเดียวกัน

    • ไม่จริงเลย ดัชนีเก็บแค่แฮชก็จริง แต่ในตารางยังเก็บ ค่าทั้งหมด อยู่
      Postgres จะถือว่าซ้ำก็ต่อเมื่อทั้งแฮชและค่าจริงตรงกัน
    • ดูได้จาก ตัวอย่าง dbfiddle

  • เนื้อหาในบทความสดใหม่ดี virtual column กับ hash index น่าสนใจ แต่ยังให้ความรู้สึกว่ายังไม่ถูกรวมเข้ากับ ecosystem อย่างสมบูรณ์

    • virtual column แทบจะเสร็จสมบูรณ์แล้ว โดย PostgreSQL 18 มีการทำไว้เกือบทั้งหมด
      ส่วน hash index มีข้อจำกัดมานาน แต่ก็ค่อย ๆ ดีขึ้น และ automatic unique constraint คือโจทย์ที่ยังเหลืออยู่

  • คิดว่าใช้ stored generated column แล้วสร้างดัชนีได้ทันทีไม่ใช่หรือ

    • ในบทความอธิบายเหตุผลที่พยายามหลีกเลี่ยงวิธีนี้ไว้
      แม้จะรองรับตั้งแต่ PostgreSQL 14 แต่ผลลัพธ์จะถูก เก็บไว้จริงในเชิงกายภาพและกินพื้นที่จัดเก็บเพิ่ม
    • สงสัยว่าอาจสร้าง partial index แบบอิง expression ได้หรือไม่
    • สุดท้ายก็เพิ่มการใช้พื้นที่อยู่ดี ดังนั้นในตัวอย่างของบทความจึงเป็นแนวทางที่พยายามหลีกเลี่ยง

  • หลังย้ายขึ้นคลาวด์แล้ว ก็แทบไม่ได้ จัดการ pgsql โดยตรง ในสภาพแวดล้อมเซิร์ฟเวอร์คงที่อีกเลย
    เลยสงสัยว่า การไฮไลต์ไวยากรณ์ SQL ที่อยู่ในบทความเป็นฟีเจอร์ในตัวหรือเป็นเครื่องมือแยก

    • ฉันใช้ pgcli มันมีฟีเจอร์อำนวยความสะดวกเยอะ เช่น การแสดงสถานะทรานแซกชัน การเติมคำอัตโนมัติ การไฮไลต์ ฯลฯ
      แต่เวลาคัดลอกคิวรียาว ๆ แล้วมีการขึ้นบรรทัดใหม่ มันจะเติมช่องว่างให้อัตโนมัติ ซึ่งค่อนข้างน่ารำคาญ
    • ถ้าใช้ IDE อย่าง IntelliJ ก็จะได้ทั้ง syntax highlighting และ autocomplete ด้วย