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

24 มีนาคม 2567 เวลาอ่าน 1 นาที

ความสามารถอย่างหนึ่งใน 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 

Phattarachai Chaimongkol

เกี่ยวกับ phattarachai.dev

ผมอ๊อฟนะครับ เป็นผู้ประกอบการอิสระ ที่ปรึกษาทางด้าน Web Application Development ให้แก่องค์กร ธุรกิจ SME และหน่วยงานราชการ
Web Developer ผู้มีใจรักใน Laravel เป็นพาร์ทเนอร์บริษัท Digital Agency ชั้นนำทางด้าน UX/UI เพื่อพัฒนาโปรเจคให้แก่ลูกค้า ผมช่วยสร้างเครื่องมือทางด้าน Web ที่มีคุณภาพให้ผู้ประกอบการดำเนินธุรกิจได้ง่ายขึ้นใช้งานได้จริง เน้นประสบการณ์ ความชำนาญ ผลงานคุ้มค่าเทียบเท่าจ้างงานกับบริษัทใหญ่ ๆ

เรื่องที่เกี่ยวข้อง

มีอะไรใหม่ใน Pest V2 บ้าง
24 มีนาคม 2567
มีอะไรใหม่ใน Pest V2 บ้าง
วิธีการ fix คอลัมน์ในตาราง html table ให้ชิดซ้ายไว้เวลา Horizontal scrolling เหมือนใน Excel
24 มีนาคม 2567
วิธีการ fix คอลัมน์ในตาราง html table ให้ชิดซ้ายไว้เวลา Horizontal scrolling เหมือนใน Excel
วิธีการเคลียร์ไฟล์ Binary Log ใน MySQL
24 มีนาคม 2567
วิธีการเคลียร์ไฟล์ Binary Log ใน MySQL