Notes

Appearance

PostgreSQL — Partial & Expression Index ​

Hai kỹ thuật giúp index nhỏ hơn và chính xác hơn so với index thông thường.

1. Partial Index ​

Index thông thường index toàn bộ row. Partial index chỉ index những row thỏa mãn điều kiện WHERE.

sql
-- Index thông thường: index cả triệu row
CREATE INDEX idx_users_email ON users (email);

-- Partial index: chỉ index ~10k user đang active
CREATE INDEX idx_active_users_email ON users (email)
WHERE is_active = true;

Tại sao hữu ích ​

Bảng users: 1 triệu row
  is_active = true:  10,000 row  (1%)
  is_active = false: 990,000 row (99%)

Query thực tế hầu hết chỉ tìm active users:
  WHERE email = 'foo@bar.com' AND is_active = true

Index thông thường: 1 triệu entries → lớn, tốn RAM
Partial index:      10,000 entries → nhỏ hơn 100x, fit vào cache tốt hơn

Planner tự động dùng partial index khi query condition khớp với WHERE của index.

Các pattern hay dùng ​

sql
-- Chỉ index orders chưa xử lý
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';

-- Chỉ index row chưa bị xóa mềm — [generic pattern]
-- Schema này không có deleted_at; thay bằng column soft-delete tương ứng
CREATE INDEX idx_active_records ON your_table (user_id, created_at)
WHERE deleted_at IS NULL;  -- [generic pattern]

-- Unique constraint chỉ áp dụng cho row active
-- (cho phép nhiều row inactive có cùng email, nhưng active thì phải unique)
CREATE UNIQUE INDEX idx_unique_active_email ON users (email)
WHERE is_active = true;

2. Expression Index ​

Index thông thường index giá trị column. Expression index index kết quả của một expression (function, phép tính, concatenation...).

sql
-- Index thông thường — không giúp được query dưới
CREATE INDEX idx_email ON users (email);

-- Query này KHÔNG dùng index vì phải tính lower() trên mỗi row
WHERE lower(email) = 'user@example.com'

-- Fix: expression index — index lưu kết quả lower(email)
CREATE INDEX idx_email_lower ON users (lower(email));

-- Giờ query này dùng index
WHERE lower(email) = 'user@example.com'

Quan trọng: Query phải dùng đúng expression như trong index. Nếu index là lower(email) mà query dùng email = '...' → không match.

Các pattern hay dùng ​

sql
-- Case-insensitive search (users.full_name)
CREATE INDEX idx_full_name_lower ON users (lower(full_name));
WHERE lower(full_name) = 'nguyen van a'

-- Tính toán từ column
CREATE INDEX idx_order_year ON orders (EXTRACT(year FROM created_at));
WHERE EXTRACT(year FROM created_at) = 2024

-- Lấy field từ jsonb — index giá trị bên trong JSON (events.payload)
CREATE INDEX idx_event_page ON events ((payload->>'page'));
WHERE payload->>'page' = '/checkout'

-- Concat nhiều column — [generic pattern]
-- Schema này dùng full_name thay vì split first/last name
-- Nếu project có split name: thay users bằng bảng tương ứng
CREATE INDEX idx_split_name ON your_table ((first_name || ' ' || last_name));  -- [generic pattern]
WHERE (first_name || ' ' || last_name) = 'John Doe'

Expression index và generated column (PG 12+) ​

Cách khác để giải quyết cùng vấn đề — thêm column tính sẵn vào bảng:

sql
-- Generated column: PostgreSQL tự tính và lưu giá trị
ALTER TABLE users ADD COLUMN email_lower text
  GENERATED ALWAYS AS (lower(email)) STORED;

CREATE INDEX idx_email_lower ON users (email_lower);
WHERE email_lower = 'user@example.com'

Khác với expression index ở chỗ: generated column lưu giá trị thật vào bảng → SELECT email_lower được, expression index không.

3. Covering Index — INCLUDE ​

Khi index scan tìm được row, nó có ctid rồi phải quay lại đọc heap để lấy thêm column → thêm 1 lần I/O.

INCLUDE nhét thêm column vào index để tránh bước đó:

sql
CREATE INDEX idx_orders_covering ON orders (user_id)
INCLUDE (status, total_amount, created_at);

-- Query này dùng Index-Only Scan — không cần đọc heap
SELECT user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 123;

Key column vs INCLUDE column ​

Key column (user_id):
  → dùng để tìm kiếm và sort
  → tham gia vào B-tree structure

INCLUDE column (status, total_amount, created_at):
  → chỉ lưu ở leaf node, không tham gia B-tree
  → không thể dùng trong WHERE, ORDER BY, JOIN condition
  → chỉ dùng để tránh heap lookup
sql
-- ĐÚNG — user_id là key column, có thể filter
WHERE user_id = 123

-- SAI — status là INCLUDE column, không thể filter bằng index này
WHERE user_id = 123 AND status = 'pending'

-- Muốn filter cả status → phải đưa status vào key
CREATE INDEX idx_orders ON orders (user_id, status)
INCLUDE (total_amount, created_at);

Khi nào nên dùng INCLUDE ​

  • Query thường xuyên select thêm một vài column ngoài các filter column
  • Các column đó lớn (text, jsonb) → tránh random I/O vào heap tốn kém
  • Visibility map đã all-visible (autovacuum chạy tốt) → Index-Only Scan hoạt động được

4. Kết hợp các kỹ thuật ​

sql
-- Partial + Expression + Covering
-- Chỉ index active users, index lower(email), bao gồm thêm full_name + tier để tránh heap lookup
CREATE INDEX idx_active_users_search ON users (lower(email))
INCLUDE (full_name, tier)
WHERE is_active = true;

-- Query này dùng Index-Only Scan trên partial index
SELECT full_name, tier
FROM users
WHERE lower(email) = 'user@example.com'
  AND is_active = true;

Personal notes by thanhlt