ดัชนี PostgreSQL สำหรับแอป SaaS: เลือกระหว่าง btree, GIN และ GiST โดยอิงกับรูปแบบคิวรีจริง เช่น ฟิลเตอร์, การค้นหา, JSONB และอาเรย์.

ดัชนีเปลี่ยนวิธีที่ PostgreSQL หาแถว หากไม่มีดัชนี ฐานข้อมูลมักต้องอ่านส่วนมากของตาราง (sequential scan) แล้วทิ้งส่วนใหญ่ไป แต่ด้วยดัชนีที่เหมาะสม มันสามารถข้ามไปยังแถวที่ตรงเงื่อนไขได้ทันที (index lookup) แล้วดึงเฉพาะข้อมูลที่ต้องการ
คุณจะสังเกตเห็นเรื่องนี้เร็วใน SaaS เพราะหน้าจอประจำวันมักมีคิวรีหนัก ๆ เพียงคลิกเดียวอาจกระตุ้นการอ่านหลายครั้ง: หน้ารายการ, นับทั้งหมด, การ์ดแดชบอร์ดไม่กี่ชิ้น และกล่องค้นหา เมื่อโตจากหลักพันเป็นหลักล้าน แถวเดียวกันที่เคยเร็วอาจเริ่มหน่วง
ตัวอย่างทั่วไปคือหน้าคำสั่งซื้อ (Orders) ที่กรองโดยสถานะและวันที่ เรียงจากใหม่ไปเก่า และมีการแบ่งหน้า ถ้า PostgreSQL ต้องสแกนทั้งตารางเพื่อหาคำสั่งจ่ายเงินใน 30 วันที่ผ่านมา ทุกหน้าการโหลดจะทำงานเกินจำเป็น ดัชนีที่ดีจะเปลี่ยนให้เป็นการกระโดดไปยังช่วงข้อมูลที่ถูกต้องอย่างรวดเร็ว
ดัชนีไม่ฟรี ทุกดัชนีช่วยให้การอ่านเร็วขึ้นสำหรับคิวรีเฉพาะ แต่ก็ทำให้การเขียนช้าลง (INSERT/UPDATE/DELETE ต้องอัปเดตดัชนี) และใช้พื้นที่จัดเก็บมากขึ้น (พร้อมกับแรงกดในแคช) ดังนั้นควรเริ่มจากรูปแบบคิวรีจริง ไม่ใช่จากชนิดดัชนี
กฎง่าย ๆ ที่ป้องกันงานเปล่า: เพิ่มดัชนีเมื่อคุณชี้ได้ชัดว่ามันจะเร่งคิวรีที่เกิดบ่อย ถ้าคุณสร้างหน้าจอด้วยตัวสร้างแบบ chat-driven อย่าง Koder.ai มันช่วยเก็บ SQL เบื้องหลังหน้ารายการและแดชบอร์ดเพื่อนำมาใช้เป็นรายการความต้องการดัชนี
ความสับสนเรื่องดัชนีส่วนใหญ่หายไปถ้าคุณเลิกคิดเป็นฟีเจอร์ (JSON, search, arrays) และเริ่มคิดเป็นรูปแบบคิวรี: WHERE ทำอะไร และคุณคาดหวังผลลัพธ์ให้เรียงอย่างไร
ใช้ B-tree เมื่อคิวรีของคุณเป็นการเปรียบเทียบปกติและคุณใส่ใจการเรียง เป็นเครื่องมือหลักสำหรับความเท่ากับ, ช่วง และการ join
รูปแบบตัวอย่าง: การกรองด้วย tenant_id = ?, status = 'active', created_at >= ?, การ join users.id = orders.user_id หรือการแสดง “ใหม่สุดก่อน” ด้วย ORDER BY created_at DESC
GIN (Generalized Inverted Index) เหมาะเมื่อคอลัมน์หนึ่งประกอบด้วยสมาชิกหลายตัวและคุณถามว่า “มี X ไหม?” นั่นพบได้บ่อยกับคีย์ JSONB, อีลีเมนต์อาเรย์, และเวกเตอร์การค้นหาข้อความ
รูปแบบตัวอย่าง: metadata @> {'plan':'pro'} บน JSONB, tags @> ARRAY['urgent'], หรือ to_tsvector(body) @@ plainto_tsquery('reset password')
GiST (Generalized Search Tree) เหมาะกับคำถามเกี่ยวกับระยะทางหรือการทับซ้อน ที่ค่าทำงานเหมือนช่วงหรือรูปทรง มักใช้กับชนิด range, ข้อมูลเชิงเรขาคณิต และการค้นหา “ใกล้ที่สุด” บางแบบ
รูปแบบตัวอย่าง: ช่วงเวลาที่ทับซ้อนกับคอลัมน์ range, การค้นหาแบบความคล้ายคลึงบางแบบ (เช่นด้วย trigram operators), หรือคิวรีเชิงพื้นที่ (ถ้าใช้ PostGIS)
แนวทางปฏิบัติในการเลือก:
ดัชนีเร่งการอ่าน แต่มีต้นทุนเรื่องเวลาการเขียนและพื้นที่ ใน SaaS ข้อนี้สำคัญที่สุดกับตารางที่ร้อนแรงเช่น events, sessions, activity logs
หน้ารายการ SaaS ส่วนใหญ่มีรูปแบบเดียวกัน: ขอบเขต tenant, ฟิลเตอร์ไม่กี่ตัว, และการเรียงที่คาดได้ B-tree มักเป็นตัวเลือกเริ่มต้นที่ถูกที่สุดในการบำรุงรักษา
รูปแบบทั่วไปคือ WHERE tenant_id = ? พร้อมฟิลเตอร์อย่าง status = ?, user_id = ?, และช่วงเวลาเช่น created_at >= ? สำหรับดัชนีคอมโพสิต ให้วางฟิลเตอร์แบบ equality ก่อน (คอลัมน์ที่จับด้วย =) แล้วเพิ่มคอลัมน์ที่คุณเรียงตาม
กฎที่ใช้ได้ดีในหลายแอป:
tenant_id ถ้าคิวรีทั้งหมดจำกัดด้วย tenant.= ต่อไป (มักเป็น status, user_id).ORDER BY ไว้ท้ายสุด (มักเป็น created_at หรือ id).INCLUDE เพื่อครอบคลุมหน้ารายการโดยไม่ทำให้คีย์กว้างขึ้น.ตัวอย่างจริง: หน้า Tickets แสดงรายการใหม่สุดก่อน กรองโดยสถานะ
-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 50;
-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);
ดัชนีนี้รองรับทั้งการกรองและการเรียง ดังนั้น Postgres จะหลีกเลี่ยงการเรียงชุดผลขนาดใหญ่ ส่วน INCLUDE (title) ช่วยให้หน้ารายการแตะตารางน้อยลง ในขณะที่คีย์ของดัชนียังคงเน้นการกรองและการเรียง
สำหรับช่วงเวลา แนวคิดเดียวกันใช้ได้
SELECT id, created_at
FROM events
WHERE tenant_id = $1
AND created_at >= $2
AND created_at < $3
ORDER BY created_at DESC
LIMIT 100;
CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);
การแบ่งหน้าเป็นที่ที่หลายแอป SaaS ช้าลง การแบ่งหน้าแบบ OFFSET (OFFSET 50000) บังคับให้ฐานข้อมูลเดินผ่านแถวจำนวนมาก Seek pagination คงเร็วด้วยการใช้คีย์การเรียงที่เห็นครั้งสุดท้าย
SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;
ด้วย B-tree ที่เหมาะสม นี่จะคงเร็วแม้ตารางจะโตขึ้น
แอป SaaS ส่วนใหญ่เป็นมัลติเทนแนนท์: ทุกคิวรีต้องอยู่ภายในเทนแนนท์เดียว หากดัชนีของคุณไม่มี tenant_id PostgreSQL ยังอาจหาแถวได้เร็ว แต่บ่อยครั้งต้องสแกนรายการดัชนีมากกว่าที่ควร ดัชนีที่คำนึงถึงเทนแนนท์จะทำให้ข้อมูลของแต่ละเทนแนนท์อยู่รวมกันในดัชนี ทำให้หน้าจอทั่วไปเร็วและคาดเดาได้
กฎง่าย: ใส่ tenant_id เป็นคอลัมน์แรกในดัชนีเมื่อคิวรีมักกรองด้วย tenant แล้วค่อยเพิ่มคอลัมน์ที่คุณกรองหรือเรียงบ่อยที่สุด
ดัชนีที่มีผลสูงและน่าเบื่อมักหน้าตาเป็น:
(tenant_id, created_at) สำหรับรายการล่าสุดและ cursor pagination(tenant_id, status) สำหรับฟิลเตอร์สถานะ (Open, Paid, Failed)(tenant_id, user_id) สำหรับหน้าจอ “ไอเท็มของผู้ใช้คนนี้”(tenant_id, updated_at) สำหรับมุมมอง admin ที่เปลี่ยนล่าสุด(tenant_id, external_id) สำหรับการมองขึ้นจาก webhook หรือการนำเข้าการเพิ่มดัชนีเกินความจำเป็นเกิดเมื่อคุณเพิ่มดัชนีใหม่สำหรับทุกหน้าจอที่ต่างกันเล็กน้อย ก่อนสร้างใหม่ ให้เช็กว่าดัชนีคอมโพสิตที่มีอยู่ครอบคลุม leftmost columns ที่คุณต้องการหรือไม่ เช่น ถ้าคุณมี (tenant_id, created_at) โดยทั่วไปคุณไม่ต้องมี (tenant_id, created_at, id) อีกเว้นแต่คุณจะกรองตาม id หลังจากคอลัมน์เหล่านั้นจริง ๆ
ดัชนีแบบพาร์เชียลช่วยลดขนาดและต้นทุนการเขียนเมื่อแถวส่วนใหญ่ไม่เกี่ยวข้อง เหมาะกับ soft deletes และ “active only” เช่น ดัชนีเฉพาะที่ deleted_at IS NULL หรือเฉพาะ status = 'active'
ดัชนีเพิ่มเติมทุกชิ้นทำให้การเขียนหนักขึ้น Inserts ต้องอัปเดตดัชนีทุกตัว และ updates อาจแตะหลายดัชนีแม้คุณเปลี่ยนคอลัมน์ตัวเดียว หากแอปของคุณรับเหตุการณ์จำนวนมาก ให้โฟกัสดัชนีไว้ที่รูปแบบคิวรีที่ผู้ใช้ใช้ทุกวัน
JSONB ใช้สะดวกเมื่อแอปต้องการฟิลด์ยืดหยุ่นอย่าง feature flags, คุณลักษณะผู้ใช้, หรือการตั้งค่าต่อเทนแนนท์ ข้อควรระวังคือโอเปอเรเตอร์ JSONB ต่างกัน ดังนั้นดัชนีที่ดีที่สุดขึ้นกับวิธีที่คุณคิวรี
สองรูปแบบที่สำคัญ:
@>-> / ->> (มักเปรียบเทียบด้วย =)ถ้าคุณกรองด้วย @> บ่อย ๆ ดัชนี GIN บนคอลัมน์ JSONB มักคุ้มค่า
-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata @> '{"region":"eu","plan":"pro"}';
-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);
ถ้าโครงสร้าง JSON ของคุณคาดเดาได้และคุณใช้ @> บนคีย์ระดับบนเป็นส่วนใหญ่ jsonb_path_ops อาจเล็กกว่าและเร็วกว่า แต่รองรับ operator น้อยกว่า
ถ้า UI ของคุณกรองซ้ำ ๆ บนฟิลด์หนึ่ง (เช่น plan) การดึงฟิลด์นั้นออกมาแล้วทำดัชนีมักเร็วและถูกกว่าการทำ GIN กว้าง ๆ
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata->> 'plan' = 'pro';
CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));
กฎปฏิบัติ: เก็บ JSONB สำหรับแอตทริบิวต์ที่ยืดหยุ่นและกรองไม่บ่อย แต่โปรโมตฟิลด์ที่นิ่งและใช้งานบ่อย (plan, status, created_at) เป็นคอลัมน์จริง ถ้าคุณพัฒนาเร็วบนแอปที่สร้างด้วย Koder.ai เป็นเรื่องง่ายที่จะปรับสคีมาเมื่อเห็นว่าฟิลเตอร์ไหนปรากฏในทุกหน้าจอ
ตัวอย่าง: ถ้าคุณเก็บ {"tags":["beta","finance"],"region":"us"} ใน JSONB ให้ใช้ GIN เมื่อคุณกรองโดยกลุ่มแอตทริบิวต์ (@>), และเพิ่มดัชนี expression สำหรับคีย์ไม่กี่ตัวที่ขับเคลื่อนรายการส่วนใหญ่ (plan, region)
อาเรย์น่าสนใจเพราะเก็บง่ายและอ่านง่าย คอลัมน์ users.roles text[] หรือ projects.labels text[] ทำงานได้ดีเมื่อคำถามหลักคือ: แถวนี้มีค่าหนึ่งค่าหรือไม่? นั่นคือจุดที่ GIN ช่วย
GIN เป็นตัวเลือกหลักสำหรับการสอบถามการเป็นสมาชิกในอาเรย์ มันแตกอาเรย์ออกเป็นไอเท็มเดี่ยวและสร้าง lookup ที่รวดเร็วไปยังแถวที่มีไอเท็มเหล่านั้น
รูปแบบคิวรีอาเรย์ที่ได้ประโยชน์บ่อย ๆ:
@> (array contains)&& (array shares any items)= ANY(...), แต่ @> มักคาดเดาได้มากกว่าตัวอย่างทั่วไปการกรองผู้ใช้ตามบทบาท:
-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];
CREATE INDEX users_roles_gin ON users USING GIN (roles);
และการกรองโปรเจกต์ที่มีชุดป้ายกำกับ (ต้องมีทั้งสอง):
SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];
CREATE INDEX projects_labels_gin ON projects USING GIN (labels);
ที่คนมักประหลาดใจ: บางรูปแบบจะไม่ใช้ดัชนีตามที่คิด หากคุณแปลงอาเรย์เป็นสตริง (array_to_string(labels, ',')) แล้วใช้ LIKE ดัชนี GIN จะไม่ช่วย และถ้าคุณต้องการ “starts with” หรือการจับคู่ fuzzy ภายใน labels คุณควรใช้การค้นหาข้อความ ไม่ใช่การเป็นสมาชิกอาเรย์
อาเรย์ยังยากจะดูแลเมื่อมันกลายเป็นฐานข้อมูลจิ๋ว: อัปเดตบ่อย ต้อง metadata ต่อไอเท็ม (ใครเพิ่มเมื่อไหร่ ทำไม) หรือต้องการการวิเคราะห์ต่อ label ในกรณีนั้น ตารางเชื่อมอย่าง project_labels(project_id, label) มักง่ายกว่าทั้งเรื่องตรวจสอบและการค้นหา
สำหรับกล่องค้นหา สองรูปแบบที่พบบ่อยคือ full-text search (ค้นหาเอกสารตามหัวข้อ) และ fuzzy matching (รองรับพิมพ์ผิด, ชื่อบางส่วน, และรูปแบบ ILIKE) ดัชนีที่เหมาะสมต่างกันระหว่าง “ทันที” กับ “หมดเวลาเมื่อผู้ใช้มากขึ้น”
ใช้ full-text เมื่อลูกค้าพิมพ์คำจริงและคุณต้องการผลตามความเกี่ยวข้อง เช่น ค้นหาตั๋วโดยหัวข้อและรายละเอียด การตั้งค่าปกติคือเก็บ tsvector (มักเป็น generated column) และใส่ดัชนี GIN คุณค้นหาด้วย @@ และ tsquery
-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;
CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);
-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');
-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);
SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';
สิ่งที่ควรเก็บในเวกเตอร์: เก็บเฉพาะฟิลด์ที่คุณค้นหาจริง ๆ หากคุณใส่ทุกอย่าง (notes, internal logs) คุณจะจ่ายในขนาดดัชนีและต้นทุนการเขียน
ใช้ trigram similarity เมื่อผู้ใช้ค้นหาชื่อ, อีเมล หรือวลีสั้น ๆ และคุณต้องการ partial matches หรือการทนพิมพ์ผิด Trigram ช่วยกับ ILIKE '%term%' และตัวดำเนินการ similarity GIN มักเร็วกว่าเมื่อต้องการ “ตรงกับไหม?”; GiST อาจเหมาะกว่าเมื่อคุณต้องการเรียงตามความคล้ายคลึงด้วย
กฎสั้น ๆ:
tsvector สำหรับการค้นหาแบบ relevanceจุดที่ต้องระวัง:
ILIKE '%abc') จะบังคับให้สแกนถ้าคุณส่งหน้าค้นหาเร็ว ให้ถือว่าดัชนีเป็นส่วนหนึ่งของฟีเจอร์: UX การค้นหาและการเลือกดัชนีต้องออกแบบร่วมกัน
เริ่มจากคิวรีที่แอปของคุณรันจริง ไม่ใช่การเดา “หน้าช้า” มักเป็นคำสั่ง SQL เดียวที่มี WHERE และ ORDER BY ชัดเจน คัดลอกมันจาก logs, ORM debug output, หรือที่คุณจับคิวรีไว้
เวิร์กโฟลว์ที่ใช้ได้ในงานจริง:
EXPLAIN (ANALYZE, BUFFERS) บนคิวรีเดียวกัน=, >=, LIKE, @>, @@), ไม่ใช่แค่ชื่อคอลัมน์EXPLAIN (ANALYZE, BUFFERS) อีกครั้งกับข้อมูลสมจริงนี่คือตัวอย่างที่จับต้องได้ หน้า Customers กรองด้วย tenant และ status เรียงใหม่สุดและแบ่งหน้า
SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;
ถ้า EXPLAIN แสดง sequential scan และ sort ดัชนี B-tree ที่ตรงกับฟิลเตอร์และการเรียงมักจะแก้ได้
CREATE INDEX ON customers (tenant_id, status, created_at DESC);
ถ้าส่วนที่ช้าคือการกรอง JSONB เช่น metadata @> '{"plan":"pro"}' นั่นชี้ไปที่ GIN ถ้าเป็น full-text แบบ to_tsvector(...) @@ plainto_tsquery(...) ก็ชี้ไปที่ดัชนีการค้นหาที่ใช้ GIN ถ้าเป็นการค้นหา “ใกล้ที่สุด” หรือ operator แบบ overlap นั่นคือที่ GiST มักเหมาะ
หลังเพิ่มดัชนี วัดผลการแลกเปลี่ยนเชิงปฏิบัติ ตรวจสอบขนาดดัชนี, เวลา insert/update, และว่ามันช่วยคิวรีช้า ๆ หลัก ๆ หรือแค่เคสขอบเขต ในโปรเจกต์ที่เคลื่อนไหวเร็ว (รวมโปรเจกต์ที่สร้างด้วย Koder.ai) การตรวจซ้ำนี้ช่วยหลีกเลี่ยงการสะสมดัชนีที่ไม่ได้ใช้
ปัญหาดัชนีส่วนใหญ่ไม่ใช่เรื่องเลือก B-tree vs GIN vs GiST แต่เป็นการสร้างดัชนีที่ดูถูกต้องแต่ไม่ตรงกับวิธีที่แอปคิวรีตาราง
ข้อผิดพลาดที่มักทำให้เสียหาย:
tenant_id และ created_at แต่ดัชนีเริ่มด้วย created_at planner อาจข้ามมันstatus, is_active, หรือ boolean มักช่วยน้อยเพราะจับแถวได้มาก ควรจับคู่กับคอลัมน์ที่มีความเลือกมากขึ้น (เช่น tenant_id หรือ created_at) หรือข้ามไปANALYZE ไม่ได้รันเมื่อเร็ว ๆ นี้ planner อาจเลือกแผนไม่ดีแม้ว่าดัชนีที่ถูกต้องจะมีอยู่ตัวอย่างชัดเจน: หน้าจอ Invoices ของคุณกรองโดย tenant_id และ status แล้วเรียงตาม created_at DESC ดัชนีแค่ status แทบไม่ช่วย ดัชนีที่ดีกว่าคือคอมโพสิตที่เริ่มด้วย tenant_id, ตามด้วย status, แล้ว created_at (กรองก่อน เรียงทีหลัง) การเปลี่ยนแค่นี้มักเหนือกว่าการเพิ่มสามดัชนีแยก
มองว่าดัชนีทุกตัวมีต้นทุน ต้องพิสูจน์ตัวเองในคิวรีจริง ไม่ใช่ในทางทฤษฎี
การเปลี่ยนแปลงดัชนีส่งได้ง่ายแต่ยากแก้ถ้ามันเพิ่มต้นทุนการเขียนหรือล็อคตารางที่ใช้งานหนัก ก่อน merge ให้ถือว่าเป็นการปล่อยเล็ก ๆ
เริ่มจากตัดสินใจว่าคุณจะเพิ่มประสิทธิภาพอะไร ดึงสองอันดับสั้น ๆ จาก logs หรือมอนิเตอร์: คิวรีที่รันบ่อยที่สุด และคิวรีที่มี latency สูงที่สุด สำหรับแต่ละคิวรี ให้จดรูปแบบที่แน่ชัด: คอลัมน์กรอง, การเรียง, การ join, และ operator ที่ใช้ (=, range, IN, ILIKE, operator JSONB, array contains) นี่ช่วยป้องกันการเดาและช่วยเลือกชนิดดัชนี
เช็คลิสต์ก่อนส่ง:
หลังเพิ่มดัชนี ยืนยันว่ามันช่วยในแผนจริง รัน EXPLAIN (ANALYZE, BUFFERS) บนคิวรีเดิมและเทียบก่อน-หลัง แล้วดูพฤติกรรม production หนึ่งวัน:
ถ้าคุณสร้างด้วย Koder.ai ควรเก็บ SQL ที่สร้างโดยเครื่องมือไว้เคียงกับการเปลี่ยนแปลง เพื่อให้ดัชนีตรงกับสิ่งที่แอปรันจริง
นึกภาพหน้าจอ admin ทั่วไป: รายการ Users ที่มีการจำกัด tenant, ฟิลเตอร์ไม่กี่ตัว, เรียงตาม last active, และกล่องค้นหา ที่นี่ดัชนีหยุดเป็นทฤษฎีและเริ่มประหยัดเวลา
สามรูปแบบคิวรีที่คุณมักเห็น:
-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;
-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;
-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
AND metadata @> '{"plan":"pro"}'::jsonb;
ชุดดัชนีเล็ก ๆ แต่ตั้งใจสำหรับหน้านี้:
(tenant_id, status, last_active_at DESC).tsvector ที่สร้างเป็น generated column พร้อมดัชนี GIN.GIN (metadata) เมื่อใช้ @> มาก หรือตัวดัชนี expression B-tree เช่น ((metadata->> 'plan')) เมื่อกรองคีย์เดียวเป็นหลักความต้องการผสมเป็นเรื่องปกติ หากหน้าเดียวทำทั้งกรอง + ค้นหา + JSON อย่าอัดทุกอย่างลงในดัชนีเดียวขนาดยักษ์ เก็บ B-tree สำหรับการเรียง/แบ่งหน้า แล้วเพิ่มดัชนีเฉพาะเรื่องหนึ่งชิ้น (มักเป็น GIN) สำหรับส่วนที่แพงที่สุด
ขั้นตอนต่อไป: เลือกหน้าช้า 1 หน้า จด 2-3 รูปแบบคิวรีหลัก และทบทวนดัชนีแต่ละอันตามจุดประสงค์ (filter, sort, search, JSON) ถ้าดัชนีไม่ตรงกับคิวรีจริง ให้ตัดออก หากคุณพัฒนาเร็วบน koder.ai ทำการทบทวนนี้เมื่อเพิ่มหน้าจอใหม่เพื่อป้องกันการฟุ้งของดัชนีขณะสคีมายังเปลี่ยนอยู่
ดัชนีช่วยให้ PostgreSQL หาแถวที่ตรงเงื่อนไขได้โดยไม่ต้องอ่านทั้งตาราง สำหรับหน้าจอ SaaS ทั่วไปอย่างรายการ, แดชบอร์ด และการค้นหา ดัชนีที่เหมาะสมสามารถเปลี่ยนการสแกนเชิงลำดับที่ช้าให้เป็นการค้นหาแบบทันทีที่ขยายได้ดีกว่าเมื่อข้อมูลโตขึ้น.
เริ่มจาก B-tree สำหรับคิวรีทั่วไป เพราะมันเหมาะกับฟิลเตอร์ =, ฟิลเตอร์ช่วง, การเชื่อม (JOIN) และ ORDER BY ถ้าคิวรีของคุณเป็นเรื่องการครอบคลุม/การเป็นสมาชิก (JSONB, อาเรย์) หรือการค้นหาข้อความเต็ม ฟังดูที่ GIN; GiST เหมาะกับกรณี overlap หรือการค้นหา “ใกล้ที่สุด” มากกว่า.
วางคอลัมน์ที่ใช้ = ไว้ก่อน แล้วค่อยใส่คอลัมน์ที่ใช้สำหรับ ORDER BY ไว้ท้ายสุด การเรียงแบบนี้ช่วยให้ planner เดินดัชนีได้อย่างมีประสิทธิภาพทั้งการกรองและการคืนผลโดยไม่ต้อง sort เพิ่มเติม.
ถ้าทุกคิวรีถูกกรองด้วย tenant_id การใส่ tenant_id เป็นคอลัมน์แรกจะช่วยรวมข้อมูลของแต่ละเทนแนนท์ไว้ใกล้กันในดัชนี ทำให้ PostgreSQL ต้องแตะข้อมูลน้อยลงสำหรับหน้ารายการทั่วไป.
INCLUDE ช่วยเพิ่มคอลัมน์เพิ่มเติมในดัชนีเพื่อให้สามารถอ่านจากดัชนีอย่างเดียว (index-only reads) ได้ โดยไม่ทำให้คีย์ของดัชนีกว้างขึ้น ใช้เมื่อคุณกรองและเรียงด้วยไม่กี่คอลัมน์ แต่ต้องแสดงฟิลด์เสริมบางช่องบนหน้าจอ.
ดัชนีแบบพาร์เชียลดีเมื่อคุณสนใจเฉพาะ subset ของแถว เช่น “ยังไม่ถูกลบ” หรือ “เฉพาะสถานะ active” มันทำให้ดัชนีเล็กลงและคงค่าบำรุงรักษาถูกลง ซึ่งสำคัญกับตารางที่มีการเขียนบ่อย.
ถ้าคุณค้นหาบ่อยด้วยการครอบคลุมแบบ @> บน JSONB ดัชนี GIN บนคอลัมน์ JSONB มักจะคุ้มค่า แต่ถ้าคุณกรองบนคีย์ JSON เจาะจงเพียงหนึ่งหรือสองคีย์ การสร้างดัชนี expression แบบ B-tree บน (metadata->> 'plan') มักจะเล็กและเร็วกว่าการใช้ GIN กว้าง ๆ.
GIN เหมาะเมื่อคำถามหลักคือ “อาเรย์นี้มี X ไหม?” โดยใช้ operator เช่น @> หรือ && แต่ถ้าคุณต้องการ metadata ต่อไอเท็ม, การแก้ไขบ่อย หรือการวิเคราะห์ต่อ label/role ในระยะยาว ให้พิจารณาทำ normalization เป็นตารางเชื่อม (join table) แทน.
สำหรับ full-text ให้เก็บ tsvector (บ่อยครั้งเป็น generated column) และใส่ดัชนี GIN แล้วค้นหาโดยใช้ @@ เพื่อการค้นหาแบบ relevance สำหรับ fuzzy/partial match (เช่น ILIKE '%name%' หรือ typo) ให้ใช้ trigram index (มักเป็น GIN) เพื่อให้ค้นหาแบบไม่เคร่งครัดได้.
คัดลอก SQL ที่แอปของคุณรันจริง แล้วรัน EXPLAIN (ANALYZE, BUFFERS) ดูจุดที่ใช้เวลาจริง ๆ ว่าเป็นการสแกน, การเรียง, หรือตัวดำเนินการแบบพิเศษ (=, >=, LIKE, @>, @@) แล้วเพิ่มดัชนีที่เล็กที่สุดซึ่งตรงกับตัวดำเนินการและการเรียงนั้น จากนั้นรัน EXPLAIN เดิมอีกครั้งเพื่อยืนยันว่ามันช่วยจริง.