ในฐานข้อมูลเชิงสัมพันธ์แบบดั้งเดิม การค้นหาช่วงเวลาโดยใช้สองคอลัมน์แยกกัน (start_date และ date_end) มักพบข้อจำกัดทางสถาปัตยกรรมของ B-Tree Index เพราะ B-Tree ทำงานแบบ 1 มิติ (One-dimensional) เมื่อมีเงื่อนไข Inequality ซ้อนกันสองคอลัมน์ (start_date >= ... AND date_end <= ... หรือเคส Overlap) Index จะทำ Seek ได้เพียงคอลัมน์แรก ส่วนคอลัมน์ที่สองต้องนำมาไล่กรอง (Filter) ทีละแถว

PostgreSQL แก้ปัญหานี้ด้วยการรวมช่วงเวลาให้เป็น ข้อมูลมิติเดียวแบบช่วง (Range Type) และใช้ดัชนีโครงสร้างต้นไม้หลายมิติอย่าง GiST Index

1. Range Types คืออะไร?

แทนที่จะแยกเก็บ 2 คอลัมน์ PostgreSQL มี Data Type สำหรับเก็บ “ช่วง” (Interval) ให้อยู่ในคอลัมน์เดียว โดยมี Built-in Types ที่นิยมใช้งาน ดังนี้

• daterange: เก็บช่วงของวันที่ (DATE)
• tsrange: เก็บช่วงของวันและเวลา (TIMESTAMP WITHOUT TIME ZONE)
• tstzrange: เก็บช่วงของวันและเวลาพร้อม Timezone (TIMESTAMP WITH TIME ZONE)

สัญลักษณ์ขอบเขต (Bounds)

• [ หรือ ] = รวมจุดนั้น (Inclusive / Closed)
• ( หรือ ) = ไม่รวมจุดนั้น (Exclusive / Open)

-- ตัวอย่างการสร้างตารางโดยใช้ daterange
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT,
    period DATERANGE
);

-- บันทึกข้อมูล: เริ่ม 2006-03-01 ถึง 2026-03-15 (รวมหัวและท้าย)
INSERT INTO bookings (room_id, period)
VALUES (101, daterange('2026-03-01', '2026-03-15', '[]'));

2. ทำไม B-Tree จึงแก้ปัญหาช่วงเวลาไม่ได้ แต่ GiST ทำได้?

• ข้อจำกัดของ B-Tree
  เรียงลำดับค่าเป็นเส้นตรงตามระนาบแกนเดียว หากสร้าง Index (start_date, date_end) ระบบจะเรียงตาม start_date ก่อน เมื่อเจอเงื่อนไขช่วงของ start_date กิ่งของต้นไม้จะแตกกระจาย ทำให้ไม่สามารถใช้คุณสมบัติการข้ามกิ่ง (Branch Pruning) เพื่อจำกัดช่วงของ date_end ต่อได้
• การทำงานของ GiST (Generalized Search Tree)
  สำหรับ Range Type ตัว GiST Index จะจัดกลุ่มข้อมูลในรูปแบบคล้ายกับ R-Tree (Bounding Boxes) โดยมองว่าแต่ละช่วงเวลาคือ “กล่องมิติเดียว” กิ่งแม่ของ Index จะเก็บขอบเขตครอบคลุม (Bounding Range) ของกิ่งลูกทั้งหมด เมื่อมีคิวรีค้นหา ไม่ว่าจะเป็นการหาจุดตัด (Overlap) หรือการบรรจุอยู่ภายใน (Containment) Engine สามารถตัดกิ่งที่ไม่แตะต้องกับช่วงเวลานั้นทิ้งได้ทันทีทั้งช่วงหัวและช่วงท้ายพร้อมกัน

3. การสร้าง GiST Index และการเขียน Query

การใช้งานจริงร่วมกับ GiST Index ทำได้ง่ายและกระชับกว่าการเขียน SQL แบบดั้งเดิม

การสร้าง Index

CREATE INDEX idx_bookings_period ON bookings USING GIST (period);

ตัวอย่างการคิวรี

1. หาช่วงเวลาที่บรรจุอยู่ภายใน (Contained)

   -- หาการจองที่เริ่มและจบอยู่ภายในเดือนมีนาคม 2026
   SELECT *
   FROM bookings
   WHERE period <@ daterange('2026-03-01', '2026-03-31', '[]');
   

2. หาช่วงเวลาที่ทับซ้อนกัน (Overlap)

   -- หาการจองที่คาบเกี่ยวกับช่วง 2026-03-10 ถึง 2026-03-20
   SELECT *
   FROM bookings
   WHERE period && daterange('2026-03-10', '2026-03-20', '[]');
   

4. กรณีที่ตารางเดิมมี start_date และ date_end อยู่แล้ว

หากมีระบบเดิมที่ยังต้องคงโครงสร้างสองคอลัมน์ไว้ ไม่จำเป็นต้องเปลี่ยน Schema ตารางทันที สามารถสร้าง Expression Index (Functional Index) ด้วย GiST ได้โดยตรง

-- สร้าง Functional GiST Index จาก 2 คอลัมน์เดิม
CREATE INDEX idx_projects_period ON projects 
USING GIST (daterange(start_date, date_end, '[]'));

-- เวลาค้นหา ให้ครอบฟังก์ชัน daterange เข้าไปในเงื่อนไข
SELECT *
FROM projects
WHERE daterange(start_date, date_end, '[]') && daterange('2026-01-01', '2026-12-31', '[]');

5. โบนัส: การป้องกันข้อมูลทับซ้อนด้วย Exclusion Constraint

จุดเด่นสำคัญของ GiST Index ใน PostgreSQL คือสามารถนำมาทำ Exclusion Constraint เพื่อป้องกันไม่ให้ข้อมูลบันทึกเวลาทับซ้อนกันในระดับฐานข้อมูล (เช่น ห้องพักห้องเดียวกัน ห้ามจองเวลาชนกัน) แทนการเขียน Trigger หรือ Application Lock

-- ต้องเปิด extension btree_gist ก่อนเพื่อใช้คอลัมน์แบบ scalar (room_id) ร่วมกับ range
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservations (
    reservation_id SERIAL PRIMARY KEY,
    room_id INT,
    reserved_period DATERANGE,
    -- ป้องกันไม่ให้ห้องเดียวกัน (room_id =) มีช่วงเวลาทับซ้อนกัน (reserved_period &&)
    CONSTRAINT exclude_overlapping_reservations 
        EXCLUDE USING GIST (room_id WITH =, reserved_period WITH &&)
);