รู้จักกับ Partial Unique Index ใน Postgres SQL

ความสามารถอย่างหนึ่งใน Postgres Database คือ การทำ Partial Unique Index หมายความว่าในตารางที่เรามีข้อมูลที่มี Field ซ้ำกัน แล้วตอนหลังต้องการทำ Unique ในตารางนั้น เราสามารถระบุเงื่อนไขที่ต้องการแบ่ง Unique ได้ โดยสามารถปล่อยให้ข้อมูลที่ไม่เข้าเงื่อนไขมีข้อมูลซ้ำกันอยู่ได้

Partial Index

Partial Index คือ Index ที่ใช้กับข้อมูลส่วนหนึ่งในตาราง โดยส่วนของข้อมูลที่เราต้องการทำ Index นั้นใช้วิธีการกำหนดเงื่อนไขใน คำสั่ง WHERE เหมือนที่เราใช้ในคำสั่ง SELECT ทั่วไปได้เลย

เหตุผลข้อดีอย่างหนึ่งของการทำ Partial Index ก็คือการหลีกเลี่ยงการทำ Index บนข้อมูลที่เหมือน ๆ กันจำนวนมาก (มีข้อมูลซ้ำกันมากกว่า 2-3% ของข้อมูลในตารางทั้งหมด) เพราะว่า Index จะไม่ถูกใช้งานอยู่ดีถ้ามีข้อมูลซ้ำกันมากเกินไป เพราะฉะนั้นจึงไม่จำเป็นต้องมี Index บนข้อมูลพวกนั้นเลย ช่วยลดขนาดของ Index ในตารางของเราได้ และช่วยทำให้คำสั่ง query ของเราทำงานได้เร็วขึ้น รวมถึงการ update เร็วขึ้นด้วยเช่นกัน

CREATE INDEX orders_draft_index ON orders (status)
    WHERE billed is null;

Partial Unique Index

ยกตัวอย่างเช่นเรามีตาราง users ใน application ของเรา มี Schema ตามนี้

CREATE TABLE users
(
    id bigint NOT NULL DEFAULT,
    name character varying(255) ,
    email character varying(255) ,
    password character varying(255) ,
    deleted_at timestamp(0) without time zone,
)

ถ้าเราต้องการให้ตาราง users ของเรามีการ เช็ค unique email โดยไม่สนใจ user ที่ถูก mark ว่ามีการลบไปแล้วจาก application ของเรา (มี timestamp deleted_at)

ใน Postgres เราสามารถเพิ่มคำสั่ง Index แบบนี้ เพื่อให้ postgres เช็ค unique เฉพาะ user ที่ยังมีการใช้งานอยู่เท่านั้นได้

CREATE UNIQUE INDEX unique_users_email
ON users (email)
WHERE deleted_at IS NULL;

ต่อไปเวลาเรา Insert หรือ Update ข้อมูลลงตารางของเรา ถ้ามี email ซ้ำ โดยที่ยังไม่ถูกลบ (มี deleted_at is null) ก็จะมี error โดยข้อมูลไม่ถูกบันทึกลงในตารางของเรา แต่ถ้า email นั้นถูก mark ลบการใช้งานไปแล้วก็จะไม่ได้สนใจว่ามี email ซ้ำอีกมั้ยแล้วครับ

Partial Unique Index ก็จะมีประโยชน์มาก ๆ หลังจากที่เราพัฒนา application ของเราไปแล้วเช่นกัน เดิมเราอาจยังไม่ได้ทำ Unique Index ตั้งแต่ตอนแรกที่เราออกแบบฐานข้อมูล แต่พอ application ใช้งานไปแล้วมีข้อมูลใน database ของเรา แล้วต้องการเพิ่ม unique index บน field ข้อมูลเดิม เราก็ไม่จำเป็นต้องหา work around เคลียร์ข้อมูล หรือ ย้ายข้อมูลในตารางเราใหม่ สามารถทำ Partial Unique Index เพิ่มบน field ข้อมูลของเราได้เลย เช่น

CREATE UNIQUE INDEX unique_orders_order_no
ON orders (order_no)
WHERE created_at > '2024-01-01';

จากตัวอย่างนี้แปลว่า Order ที่ถูกสร้างตั้งแต่ปี 2024-01-01 จะถูกบังคับ unique order no ห้ามซ้ำ โดยไม่สนใจ ว่า order ตั้งแต่ปี 2023 ลงไปอาจมี ข้อมูล order_no ซ้ำกันบ้างอยู่ก่อนแล้วก็ไม่เป็นไรครับ

ที่มา

ดูคำอธิบายและตัวอย่างเพิ่มเติมใน Documentation ของ Postgres

https://www.postgresql.org/docs/current/indexes-partial.html