กรณีศึกษาการค้นพบการทำดัชนี NULL ใน PostgreSQL และทวงคืนพื้นที่ได้ 20GB

 (hakibenita.com)
3 คะแนน โดย GN⁺ 2023-08-30 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • ระหว่างจัดระเบียบ PostgreSQL หลังได้รับการแจ้งเตือนว่าพื้นที่จัดเก็บใกล้หมด ได้กู้คืนพื้นที่ที่ไม่ได้ถูกปรับให้เหมาะสมและไม่ได้ใช้งานกลับมาได้มากกว่า 70GB โดยไม่ต้องลบดัชนีหรือข้อมูล
  • ในตอนแรกได้ตรวจสอบวิธีเรียกคืนพื้นที่แบบทั่วไป เช่น ดัชนีที่ไม่ได้ใช้งาน, bloat ของตารางและดัชนี, REINDEX CONCURRENTLY, pg_repack และ B-Tree deduplication ของ PostgreSQL 13
  • จุดต่างที่ตัดสินผลคือ PostgreSQL ทำดัชนีค่า NULL ด้วย ต่างจาก Oracle และเมื่อเปลี่ยนดัชนีเต็มของ cancelled_by_user_id ซึ่งส่วนใหญ่เป็น NULL ให้เป็น partial index ขนาดก็ลดจาก 769MB เหลือต่ำกว่า 5MB
  • ใช้ pg_stats.null_frac เพื่อหาดัชนีคอลัมน์เดียวที่มีสัดส่วน NULL สูง แล้วคัดเลือกเป็น ตัวเลือกสำหรับ partial index ด้วยเงื่อนไข WHERE column IS NOT NULL จากนั้นรีเซ็ตตัวนับสถิติและใช้ pg_stat_all_indexes เพื่อตรวจสอบการใช้งานจริง
  • ใน Django นั้น ForeignKey ที่ nullable อาจสร้างดัชนี B-Tree แบบค่าเริ่มต้นได้ จึงต้องระบุ db_index=False และกำหนด partial index เองด้วย models.Index(..., condition=Q(...__isnull=False))

เริ่มจัดระเบียบจากการแจ้งเตือนพื้นที่จัดเก็บใกล้หมด

  • ทุก ๆ สองสามเดือน ระบบมอนิเตอร์ฐานข้อมูลจะส่งการแจ้งเตือนว่าพื้นที่จัดเก็บใกล้หมด และปกติก็รับมือด้วยการเพิ่มสตอเรจ
  • ตอนนั้นเป็นช่วงกักตัวทำให้ภาระของระบบต่ำ จึงเป็นจังหวะที่เหมาะจะลองทำ งานจัดระเบียบ ที่มีภาระมากกว่าปกติ
  • ผลลัพธ์คือสามารถกู้คืนพื้นที่ที่ไม่ได้ถูกปรับให้เหมาะสมและไม่ได้ใช้งานกลับมาได้มากกว่า 70GB โดยไม่ต้องลบดัชนีหรือลบข้อมูล
  • แค่การจัดโครงสร้างดัชนีและตารางใหม่แบบทั่วไปก็ช่วยลดพื้นที่ได้มากแล้ว และหลังจากนั้นยังมีการค้นพบที่ไม่คาดคิดซึ่งช่วยลบค่าดัชนีที่ไม่ได้ใช้งานออกไปได้เพิ่มอีก ประมาณ 20GB

สาเหตุที่สงสัยในตอนแรก

  • ดัชนีที่ไม่ได้ใช้งาน

    • ดัชนีที่ไม่ได้ใช้งานถูกสร้างขึ้นเพื่อประสิทธิภาพการค้นหา แต่กินพื้นที่และอาจทำให้ INSERT กับ UPDATE ช้าลง
    • ใน pg_stat_all_indexes ได้ค้นหาดัชนีที่มี idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0 เพื่อตรวจสอบ ดัชนีที่ไม่มีการสแกนหรือ fetch นับตั้งแต่รีเซ็ตสถิติครั้งล่าสุด
    • แม้ภายนอกจะดูเหมือนไม่ได้ใช้งาน แต่ก็มีกรณีที่ไม่ควรลบ
      • กรณีที่ PostgreSQL optimizer ใช้ metadata ไม่ใช่ตัวดัชนีโดยตรง
      • ดัชนีที่รับประกันข้อจำกัดแบบ unique หรือ primary key ของตารางที่ไม่ได้มีการอัปเดตมานาน
    • ต้องตรวจสอบรายการทีละตัวว่าลบได้จริงหรือไม่ และจัดการตัวนับสถิติด้วยการรีเซ็ตหลังการตรวจสอบ
    • ในกรณีนี้มีการทำงานลักษณะนี้เป็นประจำอยู่แล้ว จึงไม่มี ดัชนีที่ไม่ได้ใช้งาน ให้ลบ

  • bloat ของดัชนีและตาราง

    • PostgreSQL จะทำเครื่องหมาย tuple เดิมเป็น dead และเพิ่ม tuple ใหม่ไปยังพื้นที่อื่นเมื่อมีการอัปเดตแถว จึงอาจเกิด bloat ได้
    • bloat ไม่ได้กระทบเฉพาะตาราง แต่กระทบดัชนีด้วย ทำให้ใช้พื้นที่จัดเก็บมากกว่าขนาดที่จำเป็นจริง
    • การประเมิน bloat ไม่ใช่เรื่องง่าย จึงใช้คิวรีจาก PostgreSQL wiki และ pgsql-bloat-estimation เพื่อประเมิน bloat ของตารางและดัชนี B-Tree

วิธีลด bloat ของดัชนี

  • สร้างหรือจัดโครงสร้างดัชนีใหม่

    • หากต้องการลบ bloat ของดัชนี จำเป็นต้องสร้างดัชนีใหม่
    • ถ้า drop แล้ว create ใหม่โดยตรง ก็จะได้ดัชนีใหม่ในรูปแบบที่เหมาะสมกว่า
    • REINDEX ของ PostgreSQL จะจัดโครงสร้างดัชนีเดิมใหม่ในตำแหน่งเดิม
    • วิธีสร้างใหม่ทั่วไปจะล็อกตารางและป้องกันการเปลี่ยนแปลง จึงเหมาะน้อยกว่าสำหรับ production เมื่อเทียบกับ REINDEX INDEX CONCURRENTLY index_name
    • REINDEX CONCURRENTLY จะสร้างดัชนีใหม่ที่มี suffix เป็น _ccnew ซิงก์การเปลี่ยนแปลงที่เกิดขึ้น แล้วเมื่อเสร็จจึงสลับแทนดัชนีเดิมและลบดัชนีเก่า
    • หากการจัดโครงสร้างใหม่หยุดกลางทาง ดัชนีใหม่อาจค้างอยู่ในสถานะ invalid และยังกินพื้นที่อยู่ จึงต้องค้นหาและลบดัชนีที่เป็น _ccnew และ indisvalid เป็นเท็จ

  • B-Tree deduplication ของ PostgreSQL 13

    • PostgreSQL 13 เพิ่ม B-Tree Deduplication เพื่อเก็บค่าซ้ำในดัชนี B-Tree ให้มีประสิทธิภาพมากขึ้น
    • จนถึง PostgreSQL 12 แม้ค่าจะซ้ำกันมาก ค่าที่ซ้ำแต่ละค่าก็ยังถูกเก็บซ้ำใน leaf ของดัชนี ทำให้ใช้พื้นที่มาก
    • เมื่อเปิด deduplication ใน PostgreSQL 13 จะเก็บค่าซ้ำเพียงครั้งเดียว จึงส่งผลมากกับขนาดดัชนีที่มีค่าซ้ำจำนวนมาก
    • ใน PostgreSQL 13 ค่าเริ่มต้นคือ deduplicate_items = ON และหากอัปเกรดมาจากเวอร์ชันก่อนหน้า ต้อง REINDEX เพื่อให้ได้ผลเต็มที่
    • ในตัวอย่าง 1 ล้านแถว ดัชนีของคอลัมน์ non-unique ที่มีค่าซ้ำมากลดจาก 21MB เหลือ 6840kB เมื่อใช้ deduplication ส่วนดัชนีของคอลัมน์ unique ยังอยู่ที่ 21MB เท่าเดิม
    • ในกรณีนี้ PostgreSQL 13 ยังเป็นเวอร์ชันใหม่ และผู้ให้บริการคลาวด์ยังไม่รองรับ จึงไม่สามารถลดพื้นที่ด้วย deduplication ได้

bloat ของตารางและ pg_repack

  • ข้อจำกัดของการลบ table bloat

    • ตารางเองก็เกิด bloat และการกระจัดกระจายได้จาก dead tuple
    • ต่างจากดัชนี ตารางไม่สามารถสร้างใหม่ได้ง่าย ๆ เพราะต้องสร้างตารางใหม่ ย้ายข้อมูล ซิงก์ข้อมูล และสร้างดัชนี ข้อจำกัด และ referential constraint ใหม่
    • VACUUM FULL table_name สามารถเรียกคืนพื้นที่ที่ bloat และ dead tuple ใช้อยู่ได้ แต่ต้องล็อกตาราง
    • ทั้งการสร้างตารางใหม่และ VACUUM FULL อาจนำไปสู่งานพัฒนาขนาดใหญ่หรือ downtime

  • การใช้ pg_repack

    • pg_repack เป็น extension ของ PostgreSQL ที่ใช้จัดโครงสร้างตารางและดัชนีใหม่โดยไม่ต้องมี downtime
    • หากต้องการใช้งาน ต้องสร้าง extension ด้วย CREATE EXTENSION pg_repack; และรันจากคอนโซลในรูปแบบ pg_repack -k --table table_name db_name
    • pg_repack จะสร้างตารางใหม่ โหลดข้อมูลต้นฉบับลงไปพร้อมซิงก์ข้อมูลใหม่ จากนั้นจัดโครงสร้างดัชนีใหม่ และสลับสองตารางในขั้นตอนสุดท้าย
    • เมื่อนำไปใช้กับระบบจริง มีข้อจำกัดบางอย่าง
      • โดยประมาณต้องมี พื้นที่จัดเก็บเพิ่ม เท่ากับขนาดของตารางและดัชนีที่กำลังจัดโครงสร้างใหม่
      • หาก repack ล้มเหลวหรือถูกหยุดด้วยมือ วัตถุชั่วคราวอาจค้างอยู่และต้อง ล้างด้วยมือ
      • หากระบบมีพื้นที่ใกล้หมดอยู่แล้ว ก็อาจไม่เหมาะเพราะต้องใช้พื้นที่เพิ่ม จึงต้องมอนิเตอร์พื้นที่ว่างและวางแผนล่วงหน้า

การค้นพบที่ไม่คาดคิด: PostgreSQL ทำดัชนี NULL ด้วย

  • แม้จะลบดัชนีที่ไม่ได้ใช้งานและจัดการ bloat ของตารางกับดัชนีด้วยวิธีทั่วไปแล้ว ก็ยังมีพื้นที่ที่ลดได้อีก
  • หนึ่งในตารางที่ใหญ่ที่สุดเก็บข้อมูลธุรกรรม และบันทึกกรณีที่ผู้ใช้ยกเลิกหลังชำระเงินแล้วได้รับเงินคืนด้วย
  • ในตารางธุรกรรมมี foreign key สำหรับทั้งผู้ใช้ที่ซื้อและผู้ใช้ที่ยกเลิก และมีการกำหนดดัชนี B-Tree ให้กับแต่ละฟิลด์
    • ผู้ใช้ที่ซื้อมีข้อจำกัด NOT NULL จึงมีค่าในทุกแถว
    • ผู้ใช้ที่ยกเลิกเป็น nullable และการยกเลิกเกิดไม่บ่อย ทำให้ค่าส่วนใหญ่เป็น NULL
  • เดิมคาดว่าดัชนีของผู้ใช้ที่ยกเลิกน่าจะเล็กกว่าดัชนีของผู้ใช้ที่ซื้อมาก แต่ขนาดจริงกลับเท่ากัน
  • เคยเรียนจาก Oracle ว่า NULLs are not indexed แต่ใน PostgreSQL นั้นค่า NULL ก็ถูกทำดัชนีด้วย
  • ดัชนีเต็มเดิมมีดังนี้
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
  • จึงเปลี่ยนเป็น partial index ที่ตัดค่า NULL ออกเพื่อทดสอบสมมติฐาน
DROP INDEX transaction_cancelled_by_ix;

CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
  • หลัง reindex ขนาดของดัชนีเต็มอยู่ที่ 769MB และมากกว่า 99% ของค่าทั้งหมดเป็น NULL
  • partial index ที่ตัด NULL ออกมีขนาด ต่ำกว่า 5MB และเท่ากับลดขนาดดัชนีลงได้มากกว่า 99%
  • หลังรีเซ็ตสถิติตารางและเฝ้าดูช่วงเวลาหนึ่ง พบว่าดัชนีใหม่ถูกใช้งานเหมือนดัชนีเดิม และสามารถลบ indexed tuple ที่ไม่ได้ใช้งานออกไปได้มากกว่า 760MB โดยไม่มีผลต่อประสิทธิภาพ

การหาตัวเลือก partial index

  • หลังได้ผลลัพธ์ที่ดีจาก partial index หนึ่งตัว ก็เริ่มค้นหาดัชนีคล้ายกันโดยใช้ pg_stats.null_frac กับคอลัมน์ที่มีสัดส่วน NULL สูง
  • null_frac คือสัดส่วน ค่า NULL ในคอลัมน์นั้นที่ PostgreSQL ประเมินไว้
  • คิวรีสำหรับค้นหาตัวเลือกใช้เงื่อนไขต่อไปนี้
    • ตัด primary key ออก เพราะไม่สามารถเป็น partial ได้
    • ตัดกรณีที่เป็น partial index อยู่แล้วออก
    • ตัด composite index ออก
    • รวมเฉพาะดัชนีที่มีขนาด เกิน 10MB
  • ในตัวอย่างผลลัพธ์ tx_cancelled_by_ix มีขนาด 1418MB และ null_frac เท่ากับ 96.15% จึงเป็นตัวเลือกที่อาจลดได้ประมาณ 1363MB
  • การตัด NULL ทั้งหมดออกจากดัชนีไม่ได้คุ้มเสมอไป
    • คิวรีที่ค้นหา NULL ด้วยเงื่อนไข IS NULL อาจยังได้ประโยชน์จากดัชนีของค่า NULL
    • นอกจากค่าที่ไม่เป็น NULL แล้ว ยังสามารถตัดค่าบางค่าอื่นที่แทบไม่ถูกค้นหาออกจาก partial index ได้ด้วย
  • ตัวเลข 20GB ในชื่อบทความเกิดขึ้นได้เพราะเมื่อฐานข้อมูลหลักลดลงประมาณ 10GB พื้นที่จัดเก็บใน replica ก็ลดลงใกล้เคียงกันด้วย

การ migrate ไปใช้ partial index ใน Django ORM

  • nullable ForeignKey และดัชนีที่ถูกสร้างแบบ implicit

    • หากไม่ระบุ db_index=False Django จะ สร้างดัชนี B-Tree ให้กับฟิลด์ models.ForeignKey แบบ implicit
    • หากปล่อย nullable ForeignKey ที่เก็บผู้ใช้ที่ยกเลิกไว้โดยไม่ตั้งค่าอะไร Django จะสร้างดัชนีเต็มให้
    • หากต้องการสร้าง partial index ต้องกำหนด db_index=False ให้กับฟิลด์ FK และเพิ่ม models.Index แบบมีเงื่อนไขใน Meta.indexes
    class Transaction(models.Model):
    cancelled_by_user = models.ForeignKey(
        to=User,
        null=True,
        on_delete=models.CASCADE,
        db_index=False,
    )

    class Meta:
        indexes = (
            models.Index(
                fields=('cancelled_by_user_id', ),
                name='%(class_name)s_cancelled_by_part_ix',
                condition=Q(cancelled_by_user_id__isnull=False),
            ),
        )
    • nullable foreign key เหมาะมากที่จะเป็น ตัวเลือกสำหรับ partial index
    • เพื่อไม่ให้ความสามารถแบบ implicit สร้างดัชนีโดยไม่รู้ตัว จึงสร้าง Django check เพื่อบังคับให้ระบุ db_index กับ foreign key เสมอ

  • เปลี่ยนจากดัชนีเต็มเป็น partial index โดยไม่มี downtime

    • หากต้องการแทนที่ดัชนีเต็มด้วย partial index โดยไม่ให้เกิด downtime หรือประสิทธิภาพตก ควรทำแบบเป็นขั้นตอน
    • แก้ไข Django model ที่เกี่ยวข้องเพื่อเปลี่ยนดัชนีเต็มเป็น partial index แต่ไม่รัน migration ที่ Django สร้างให้นั้นตรง ๆ
    • migration ดังกล่าวอาจปิดการทำงานของข้อจำกัด FK, drop ดัชนีเต็มเดิม และสร้าง partial index ใหม่ ซึ่งอาจทำให้เกิด downtime และประสิทธิภาพลดลง
    • ใช้ ./manage.py sqlmigrate เพื่อสร้าง SQL แล้วดึงเฉพาะ CREATE INDEX ออกมา แก้เป็น CONCURRENTLY และรันด้วยมือในฐานข้อมูล
    • เนื่องจากดัชนีเต็มเดิมยังคงอยู่ คิวรีจึงยังใช้ดัชนีเดิมต่อไปได้ระหว่างที่สร้าง partial index
    • เพื่อตรวจสอบว่ามีการใช้ partial index ใหม่หรือไม่ ให้รีเซ็ตตัวนับสถิติของดัชนีเต็มด้วย pg_stat_reset_single_table_counters(<full index oid>)
    • จากนั้นเฝ้าดู idx_scan, idx_tup_read, idx_tup_fetch ใน pg_stat_all_indexes เพื่อเปรียบเทียบการใช้งานของดัชนีเต็มกับ partial index
    • หากมั่นใจว่า partial index ถูกใช้งานแล้ว จึงค่อย drop ดัชนีเต็ม และตรวจสอบพื้นที่จัดเก็บที่ได้คืนจริง
    • เมื่อสถานะของฐานข้อมูลตรงกับสถานะของโมเดลแล้ว ให้ใช้ ./manage.py migrate --fake เพื่อบันทึกว่า Django migration ถูกนำไปใช้แล้ว
    • ในสภาพแวดล้อมอย่าง dev, QA, staging ที่ไม่กังวลเรื่อง downtime มาก Django migration สามารถรันตามปกติและแทนที่ดัชนีเต็มด้วย partial index ได้

สรุปสุดท้าย

  • การปรับปรุงเฉพาะดิสก์ พารามิเตอร์จัดเก็บ และการตั้งค่า มีข้อจำกัดทั้งด้านประสิทธิภาพและการประหยัดพื้นที่
  • ช่องทางปรับปรุงสุดท้ายอยู่ที่ตัววัตถุของฐานข้อมูลเอง ซึ่งในกรณีนี้คือการเปลี่ยน นิยามของดัชนี
  • แนวทางที่ใช้สรุปได้เป็นสามขั้นตอน
    • ลบดัชนีที่ไม่ได้ใช้งาน
    • repack ตารางและดัชนี และถ้าเป็นไปได้ให้เปิดใช้ B-Tree deduplication
    • ใช้ partial index เพื่อทำดัชนีเฉพาะค่าที่จำเป็น
  • การจัดระเบียบแบบนี้อาจเป็นทางเลือกที่ช่วยยืดเวลาใช้งานออกไปได้อีกหลายวันก่อนจำเป็นต้อง provision สตอเรจเพิ่ม

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

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

 
GN⁺ 2023-08-30
ความคิดเห็นจาก Hacker News

  • แม้จะมีพื้นที่จัดเก็บเยอะ การ ทำให้อินเด็กซ์มีขนาดเล็ก ก็เป็นเรื่องดี เพราะจะทำให้มีข้อมูลเข้าไปอยู่ใน hot set ได้มากขึ้น
    อย่างไรก็ตาม จากมุมของคนที่ดูแลฐานข้อมูลระดับ TB การ “แค่เพิ่มพื้นที่จัดเก็บเข้าไป” ก็ยังเป็นตัวเลือกที่ใช้ได้เสมอ โดยเฉพาะถ้าอยู่นอกคลาวด์ และถ้าเป็นฮาร์ดแวร์ของตัวเอง SSD NVMe ระดับองค์กรอยู่ราว ๆ $80/TB ส่วน DDR4 RAM อยู่ราว ๆ $1.20/GB ถ้าคิดค่าเวลาวิศวกรรม 4 ชั่วโมงประมาณ $1000 ก็ซื้อ RAM ได้ 800GB หรือพื้นที่จัดเก็บ 12TB

    • ระหว่างความจุตามสเปกกับความจุที่ต้องซื้อจริงทั้งระบบมี ผลคูณทวี อยู่ ไม่ใช่ซื้อแค่ 1TB แต่ต้องมีเซิร์ฟเวอร์ live failover อย่างน้อย 2 เครื่องขึ้นไป และยังต้องมีชั้นสำรองข้อมูลกับกู้คืนภัยพิบัติหลายชั้นด้วย
      แค่แบ็กอัปรายสัปดาห์เก็บไว้ 1 ปี 1TB ก็กลายเป็น 50TB แล้ว แม้จะเป็นสตอเรจออฟไลน์ก็เช่นกัน บริษัทเราก็เพิ่มพื้นที่จัดเก็บให้ live DB ได้ง่าย เลยทำแบบนั้นมาหลายปี แต่การย้ายแบ็กอัปหลาย TB เริ่มยุ่งยากขึ้นเรื่อย ๆ จนอยู่ในสถานการณ์ที่ต้องยอมจ่ายต้นทุนวิศวกรรมเพื่อลดข้อมูลในโปรดักชัน
    • วิธีเปรียบเทียบแบบนี้ไม่ค่อยดีนัก เวลาวิศวกรรม จ่ายครั้งเดียวแล้วอาจแก้ปัญหาได้ แต่ SSD 10 ลูกต้องใช้พื้นที่แร็ก ไฟฟ้า สล็อต PCIe การเปลี่ยนให้ทันเวลา และซอฟต์แวร์บริหารจัดการ ซึ่งส่วนใหญ่กลายเป็นต้นทุนซ้ำ ๆ
      ถ้าเป็นครั้งเดียว โครงสร้างพื้นฐานเดิมอาจรับต้นทุนอย่างพื้นที่แร็กว่างหรือสล็อต PCIe สำรองได้ แต่การเฉลี่ยต้นทุนแบบนี้ใช้ได้ดีแค่ในขนาดเล็ก อีกทั้งยิ่งระบบใหญ่ขึ้น ประสิทธิภาพต่อหน่วยอุปกรณ์ย่อมลดลงจากต้นทุนการจัดการและ latency ที่เพิ่มขึ้น ถ้าแก้ด้วยการขยายระบบไปเรื่อย ๆ ทั้งระบบจะอืดลงเรื่อย ๆ จนอาจดำเนินงานไม่ได้ ในทางกลับกัน วิธีแก้ที่ลดทรัพยากรระบบที่ต้องใช้จะเพิ่มประสิทธิภาพต่อหน่วยทรัพยากร และตัวมันเองก็ทำให้ได้สินทรัพย์ที่มีคุณภาพดีกว่า
    • ขึ้นอยู่กับสเกลและความซับซ้อน แต่ถ้าไม่ควบคุม การใช้ทรัพยากร ต้นทุนอาจเพิ่มแบบทวีคูณ ได้ แม้ธุรกิจจะไม่ได้เติบโต เพียงแค่ requirement ซับซ้อนขึ้นก็อาจเป็นแบบนั้นได้
      การปรับแต่งบางอย่างที่วันนี้ประหยัดได้ 1TB ในอีกไม่กี่ปีอาจกลายเป็นประหยัดได้ 2TB และการตัดสินใจแบบนี้แค่ไม่กี่อย่างก็อาจทำให้ต่างกันเกินหนึ่งหลักได้ โดยเฉพาะในสเกลที่ใหญ่ขึ้น สุดท้ายก็ต้องหาจุดสมดุลเสมอ
    • เป็นเรื่องยากที่จะใช้ $1000 ซื้อ RAM เพิ่มอีก 800GB ที่น่าจะใส่ในเซิร์ฟเวอร์ปัจจุบันได้ RAM แบบนั้นมักอยู่ราว $3~6/GB โดยของที่ไม่ได้รับการรับรองอยู่ที่ $3 ส่วน RAM ที่ผู้ผลิตเมนบอร์ดรับรองอยู่ราว $6 และเมื่อเทียบกับคอนฟิก RAM พื้นฐาน ยังมีต้นทุนสล็อต DIMM เพิ่มอีกประมาณ $0.50~$1/GB
      ทางเลือกแบบ “โยนฮาร์ดแวร์ใส่เข้าไป” บางครั้งอาจฉลาด แต่โดยส่วนใหญ่แล้ว ผมคิดว่ายากที่จะเปลี่ยนเซิร์ฟเวอร์ RAM 128GB วันนี้ให้เป็น RAM 1TB ในวันพรุ่งนี้ด้วยเงิน $1K
    • แม้บนคลาวด์ ถ้าไม่มีไอเดียเฉพาะเจาะจงว่าจะใช้พื้นที่จัดเก็บให้น้อยลงอย่างไร โดยทั่วไปคำตอบก็คือ โปรวิชันพื้นที่จัดเก็บเพิ่ม
      เพียงแต่ไม่ใช่รายจ่ายครั้งเดียว แต่เป็นค่าใช้จ่ายรายเดือน เลยคำนวณซับซ้อนขึ้นเล็กน้อย แน่นอนว่าถ้าฐานข้อมูลเติบโตด้วยอัตราคงที่ การโปรวิชันแบบครั้งเดียวใน on-premises ก็แทบจะใกล้เคียงกับค่าใช้จ่ายรายเดือนอยู่ดี

  • ในบทความพูดถึง การกำจัดค่าซ้ำใน B-tree ซึ่งมีใน PostgreSQL 13 แต่ไม่มีใน 12 ที่ใช้อยู่ และบอกว่าค่าส่วนใหญ่ของอินเด็กซ์ foreign key บางตัวเป็น NULL
    กรณีนี้สงสัยว่าการกำจัดค่าซ้ำใน B-tree จะช่วยได้หรือไม่ ดูจากเอกสาร PostgreSQL 13 ข้อ 63.4.2 แล้วน่าจะเป็นเช่นนั้น: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” แน่นอนว่าคงไม่ได้มีประสิทธิผลเท่ากับ partial index ที่บทความใช้ แต่ก็น่าสนใจ
    https://www.postgresql.org/docs/13/btree-implementation.html

    • ในการถกเถียงครั้งก่อน มีคนคำนวณไว้ว่า pg12 ใช้ 16 ไบต์ต่อ NULL หนึ่งค่า ส่วน pg13 ใช้ 6.32 ไบต์ ต่อ NULL หนึ่งค่า ถือว่าประหยัดได้พอสมควร
      https://news.ycombinator.com/item?id=25989467

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

    • คำแนะนำนี้ดี ช่วงแรกโดยทั่วไปควร ทำให้เรียบง่ายที่สุดเท่าที่เป็นไปได้
      สมัยก่อนตอนทำงานที่สตาร์ทอัพที่โตเร็ว ผู้ก่อตั้งคนหนึ่งยืนกรานให้เราอัปเกรดเซิร์ฟเวอร์เครื่องเดียวต่อไปเรื่อย ๆ แม้จะมี redundancy และแบ็กอัปครบ แต่สถาปัตยกรรมเรียบง่าย ทำให้จัดการ ดีบัก และกู้คืนได้ง่าย ช่วง 5 ปีแรกของบริษัท ทั้งระบบรันอยู่บนเซิร์ฟเวอร์เครื่องเดียวแต่ยังเติบโตแบบเอ็กซ์โพเนนเชียลและรองรับผู้ใช้หลายล้านคนทั่วโลกได้ หลังจากเห็นแบบนั้น ผมก็มั่นใจว่าควรอัปเกรดเฉพาะเมื่อจำเป็น และด้วยวิธีที่เรียบง่ายตรงไปตรงมาที่สุด
    • มีการเน้นไปมากว่าแค่เพิ่มพื้นที่จัดเก็บก็พอ แต่การทำอินเด็กซ์ที่ไม่จำเป็นก็ส่งผลต่อ ประสิทธิภาพการเขียนและการอ่าน ด้วย และในบางกรณีก็หนักพอสมควร
      การใช้ partial index ที่ชัดเจนว่าเหมาะกับ use case เช่นกรณีที่ค่ามากที่สุดเป็น NULL นั้นใกล้เคียงกับการโมเดลข้อมูลที่ถูกต้องมากกว่า ไม่ควรมองว่าเป็นการปรับแต่งก่อนเวลาอันควรหรือการเสียเวลานักพัฒนา

  • ในบริบทคล้ายกัน สคริปต์เหล่านี้ช่วยได้มากทีเดียว
    https://github.com/NikolayS/postgres_dba
    การจัดลำดับคอลัมน์ของตารางขนาดใหญ่ใหม่ทำให้กู้พื้นที่จัดเก็บได้ประมาณ 10% หรือราว 100GB

  • สัปดาห์ที่แล้ว แค่ใช้ reindex กับ pg_repack ก็ กู้พื้นที่ได้ 200GB จาก DB ขนาด 600GB ตารางที่หนักที่สุดมีข้อมูล 17GB แต่อินเด็กซ์ 142GB และหลังทำ reindex ก็ลดเหลือ 21GB
    ตารางนี้ทำอินเด็กซ์มากเกินไป มีอินเด็กซ์หลายตัวสำหรับชุดคอลัมน์หลายแบบ หนึ่งในสาเหตุของอินเด็กซ์ขนาดมหึมาน่าจะเป็นการกระจายตัวของข้อมูล มีการ insert, update, delete ต่อเนื่องมาตั้งแต่ปี 2015 และยิ่งข้อมูลเก่าก็ยิ่งมีแนวโน้มถูกลบมากกว่า ทำให้ข้อมูลปีล่าสุดมีมากกว่า แต่ก็ยังมีข้อมูลปี 2015 อยู่ประมาณ 0.1% ผมคิดว่าการกระจายตัวแบบ skew ที่มีหางยาวเช่นนี้อาจทำให้ vacuum จัดการ index bloat ได้ยากขึ้น

  • มีการพูดคุยกันตั้งแต่ตอนนั้นแล้ว: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - กุมภาพันธ์ 2021, 78 ความคิดเห็น

  • ขอแนะนำ pganalyze.com อย่างยิ่ง สำหรับการหาอินเด็กซ์ที่ไม่ได้ใช้ โอกาสในการปรับแต่ง และคิวรีที่มี latency สูง

  • คำว่า “ทริกแปลก ๆ เพียงอย่างเดียวช่วยประหยัดได้ 20GB” ไม่ได้เกินจริงเลย ในไซต์งาน Oracle / SQL Server เก่า ๆ แทบไม่เคยได้ยินเรื่องนี้ แต่มีคำขอจำนวนมากที่อยากใช้ รูปแบบอินเด็กซ์เฉพาะของ PostgreSQL และไม่รู้ว่า PostgreSQL ก็ทำอินเด็กซ์ค่า NULL ด้วย

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

  • ในโปรเจกต์ขนาดใหญ่ครั้งแรกที่เข้าร่วม ทีมไม่เข้าใจว่าทำไมงานถึงช้าลงเมื่อชุดข้อมูลใหญ่ขึ้น
    อินเด็กซ์มีเวลา insert ต่อเรกคอร์ดเป็น log(n) ถ้า DB ทดสอบมีเรกคอร์ดจาก 1,000 รายการเพิ่มเข้าใกล้ 65k เวลา insert จะเพิ่มขึ้น 60% (2^10 เทียบกับ 2^16) ความสำเร็จทำให้ทุกอย่างช้าลง และการอัปเกรดเซิร์ฟเวอร์ก็มีขีดจำกัด ถ้าเพิ่มอินเด็กซ์อีกสองสามตัวเพราะฟีเจอร์คลุมเครือที่ธุรกิจต้องการ ตอนนี้ก็กลายเป็นสองเท่าแล้ว

    • มันก็แค่ตัวเลขเล็กมาก ๆ ที่เพิ่มเป็นสองเท่าเท่านั้น ถ้านั่นเป็นปัญหาจริง ๆ ก็ใช้ดิสก์ที่เร็วขึ้น มีหลายกรณีเกินไปที่พยายามรัน DB บน EBS ซึ่งมี IOPS ต่ำกว่าพีซีของฉันในปี 2015
      ฉันยังดูแล DB จำนวนมากที่มีหลายร้อยล้านเรกคอร์ด และมีอินเด็กซ์ 40 ตัวขึ้นไปต่อ table/collection
    • สำหรับอินเด็กซ์เล็ก ๆ แบบนั้น insert ก็ควรยังเร็วมากไม่ใช่หรือ
      การค้นหาแบบไบนารีในหน้า B-tree ใช้ต่ำกว่า 100 cycle การเดิน B-tree สำหรับ 100 ล้านเรกคอร์ดก็ควรยังอยู่ในระดับไมโครวินาที และการค้นหาแบบไบนารีด้านบนก็น่าจะอยู่ระดับไมโครวินาทีเช่นกัน แม้จะไม่ใช่ระดับหลายร้อยนาโนวินาทีก็ตาม

  • หลักการนี้ใช้ได้ไหมในกรณีที่ค่าหนึ่งหรือไม่กี่ค่าพบบ่อยมาก เช่น 90% ของคอลัมน์จำนวนเต็มเป็น 0

    • ใช่ Postgres ติดตาม ค่าที่พบบ่อยที่สุด ถ้ารู้ว่า 0 พบบ่อย และ where val = 0 จะเหลือแถวไว้ 90% ก็อาจเลือกสแกนตารางแทนใช้อินเด็กซ์
    • อินเด็กซ์มีประสิทธิภาพสูงสุดเมื่อแทนชุดของค่าที่ไม่ซ้ำกัน มันยังมีประโยชน์กับการจัดกลุ่มด้วย แต่ถ้ากลุ่มนั้นคิดเป็นสัดส่วนที่น้อยมากอย่างสุดโต่ง ก็จะเปลืองพื้นที่และ cycle จำนวนมากไปกับการไล่อินเด็กซ์
    • ทำ อินเด็กซ์แยก สำหรับกรณีที่เป็น 0 กับไม่เป็น 0 ไม่ได้หรือ?
    • ใช่เฉพาะกรณีที่ใช้ NULL แทนค่าทั่วไปเท่านั้น และโดยทั่วไปไม่ใช่ความคิดที่ดี