เกิดอะไรขึ้น
ในโปรเจกต์หนึ่งของเรา ตารางหลักใช้ soft-delete โดยตั้งค่า deleted_at แทนการลบแถวจริง ผ่านไปสักพักมีแถวที่ถูก soft-delete สะสมอยู่กว่า 2 ล้านแถว ขณะที่ query ของแอปทั้งหมดสนใจแค่แถวที่ยังใช้งาน
index ปกติบนตารางนี้ครอบคลุมทุกแถว รวมแถวที่ไม่เคยมีใครถามถึง query ที่ควรเร็วจึงใช้เวลาราว 800ms
ทำไมถึงเป็นแบบนั้น
index ปกติเก็บรายการของทุกแถวในตาราง แถวที่ถูก soft-delete จึงทำให้ index ใหญ่ขึ้นโดยไม่มีประโยชน์ index ที่ใหญ่กว่าจะอยู่ใน memory ได้ยากกว่า ทำให้ต้องอ่านจาก disk บ่อยขึ้น
partial index เก็บเฉพาะแถวที่ตรงกับเงื่อนไข WHERE ที่ระบุตอนสร้าง ในกรณีนี้คือ deleted_at IS NULL index จึงเล็กลงมากและมีโอกาสอยู่ใน memory ได้เต็ม ๆ สิ่งที่ต้องระวังคือ planner จะเลือกใช้ index นี้ก็ต่อเมื่อ WHERE ของ query รับประกันได้ว่าเข้ากับเงื่อนไขของ index ดังนั้น query ต้องมี deleted_at IS NULL อยู่ด้วย
ตอนนี้เราทำอย่างไร
เราสร้าง partial index ด้วย CREATE INDEX CONCURRENTLY เพื่อไม่ให้ล็อกการเขียนบนตารางที่ใช้งานอยู่ แล้วเช็กด้วย EXPLAIN ว่า query ใช้ index ตัวใหม่จริง ในเคสนี้เวลา query ลดจาก 800ms เหลือ 20ms หรือเร็วขึ้นราว 40 เท่า ตัวเลขนี้ขึ้นกับข้อมูลและ workload ของเรา ไม่ได้การันตีว่าจะเท่ากันทุกที่
วิธีนี้ใช้ได้กับตารางที่ query ส่วนใหญ่แตะแค่แถวกลุ่มเล็ก ๆ ที่ระบุเงื่อนไขได้ชัด เช่น แถวที่ยังไม่ถูกลบ หรือแถวที่สถานะเป็น active เมื่อมีแถวหลุดออกจากเงื่อนไข มันก็หลุดออกจาก index ไปด้วย
CREATE INDEX CONCURRENTLY idx_orders_customer_live
ON orders (customer_id)
WHERE deleted_at IS NULL;
-- query ต้องมีเงื่อนไขเดียวกัน planner ถึงเลือกใช้ index นี้
SELECT * FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;เช็กลิสต์สั้น ๆ
- 1ดูว่า query หลักกรองแถวด้วยเงื่อนไขอะไรเสมอ เช่น deleted_at IS NULL
- 2สร้าง partial index ด้วย CREATE INDEX CONCURRENTLY และใส่ WHERE เงื่อนไขเดียวกัน
- 3ตรวจด้วย EXPLAIN ว่า query ใช้ index ตัวใหม่
- 4เทียบขนาด index เก่ากับใหม่ แล้วค่อยลบตัวเก่าที่ไม่จำเป็น
จำไว้สั้น ๆ
ถ้า query ส่วนใหญ่แตะแค่แถวกลุ่มเล็ก ๆ ที่ระบุได้ชัด ให้ใช้ partial index แทน index ที่ครอบคลุมทุกแถว