PostgreSQL Partial Index trong Multi-Tenant Production: giảm write amplification mà không tự bẫy planner

Trong nhiều hệ multi-tenant, bảng lớn nhất không chết vì thiếu index. Nó chết vì có quá nhiều index chung chung mà application chỉ thực sự query một phần nhỏ dữ liệu theo những predicate lặp đi lặp lại như tenant_id, status, deleted_at is null, archived = false, processed_at is null, hoặc created_at >= now() - interval '30 days'.

Team nhìn thấy query chậm, thêm composite index. Query khác chậm, thêm index nữa. Sau vài tháng, write latency tăng, autovacuum phải đụng vào nhiều page index hơn, cache hit ratio của index xấu đi, và planner đôi khi vẫn chọn seq scan hoặc bitmap heap scan vì index quá to hoặc predicate không đủ selective.

Đó là lúc partial index đáng để bàn nghiêm túc. Đây không phải mẹo “tối ưu PostgreSQL” kiểu thuộc lòng. Nếu dùng đúng, partial index giúp bạn cắt nhỏ index footprint, tập trung tối ưu cho traffic nóng, giảm write amplification và giữ working set nằm gọn hơn trong RAM. Nếu dùng sai, bạn tạo ra index nhìn rất đẹp trên giấy nhưng planner không dùng, hoặc tệ hơn là che đi vấn đề query shape và parameterization.

Bài này đi sâu vào partial index trong PostgreSQL production cho hệ multi-tenant: khi nào nó đáng giá, cách thiết kế predicate, interaction với soft delete và workflow trạng thái, bẫy với prepared statement/generic plan, cách đọc EXPLAIN, và cách rollout an toàn mà không biến production thành phòng thí nghiệm.

Partial index thực chất giải bài toán gì?

Partial index là index chỉ bao phủ một tập con row thỏa một predicate nhất định.

Ví dụ thay vì:

create index idx_invoices_tenant_status_created_at
on invoices (tenant_id, status, created_at desc);

bạn có thể tạo:

create index idx_invoices_open_by_tenant_created_at
on invoices (tenant_id, created_at desc)
where status in ('pending', 'overdue') and deleted_at is null;

Sự khác biệt không chỉ là “index nhỏ hơn”. Nó là thay đổi chiến lược:

• chỉ index phần dữ liệu application đọc thường xuyên nhất;
• tránh mang row đã archived, soft-deleted, completed hoặc cold vào cùng một index nóng;
• giảm cost cho insert/update nếu phần lớn row không thuộc predicate;
• tăng khả năng index nóng nằm trong shared buffers hoặc OS cache;
• giúp planner có access path sắc hơn cho một nhóm query rất cụ thể.

Trong hệ multi-tenant, điều này đặc biệt hữu ích vì traffic thường không phân bố đều:

• một số tenant rất nóng;
• một số status được query liên tục trong UI và worker;
• phần lớn dữ liệu là lịch sử nhưng vẫn chiếm dung lượng index nếu bạn dùng full index.

Dấu hiệu production cho thấy partial index đáng cân nhắc

Tôi thường chưa đụng vào partial index nếu chưa thấy ít nhất vài dấu hiệu sau:

1. Query luôn có predicate business ổn định

Ví dụ query gần như luôn có:

• deleted_at is null
• status in ('queued', 'retry')
• processed_at is null
• archived = false
• tenant_id = ? and active = true

Nếu predicate này là invariant ở layer ứng dụng chứ không phải ngẫu hứng theo từng endpoint, partial index có cơ sở tồn tại.

2. Full index quá to so với working set thật

Bảng có thể chứa 200 triệu row, nhưng dashboard hoặc worker chỉ động vào 3% row đang active. Nếu bạn index toàn bảng cho cùng access pattern, bạn đang bắt write path và buffer cache gánh dữ liệu lạnh không cần thiết.

3. Write amplification bắt đầu đau

Mỗi index thêm vào làm insert/update/delete đắt hơn. Với bảng event, jobs, invoices, notifications, order states hoặc session-like records, index thừa có thể trở thành bottleneck âm thầm.

4. Query phục vụ queue, dashboard hoặc reconciliation có rõ hot/cold split

Đây là case partial index thường thắng rất đẹp. Ví dụ worker chỉ poll status = 'queued' and available_at <= now(); dashboard chỉ xem order chưa archive; reconciliation chỉ đọc payment chưa settled.

Multi-tenant làm partial index thú vị hơn nhưng cũng dễ sai hơn

Trong hệ single-tenant, bạn chủ yếu nghĩ về predicate theo trạng thái hoặc soft delete. Trong hệ multi-tenant, bạn phải nghĩ thêm về data skew.

Ví dụ:

• 5 tenant lớn chiếm 80% traffic;
• phần lớn tenant nhỏ gần như không query bảng này nhiều;
• một query shape giống nhau nhưng cardinality khác rất xa giữa tenant nhỏ và tenant lớn.

Nhiều team lập tức nghĩ tới index kiểu:

create index ... where tenant_id in (1, 2, 3, 4, 5);

Đây đôi khi hợp lý cho tình huống cực kỳ đặc biệt, nhưng thường là lựa chọn khó sống lâu vì tenant hot thay đổi theo thời gian, rollout khó maintain, và logic business bị cứng hóa vào schema. Tôi thích partial index theo trạng thái nghiệp vụ ổn định hơn là theo danh sách tenant cụ thể, trừ khi bạn thật sự đang tối ưu cho một dedicated shard giả-lập hoặc premium tenant có SLA riêng.

Cách bền hơn thường là:

• tenant_id nằm trong key index;
• predicate partial bám vào trạng thái hoạt động;
• query shape giữ nhất quán để planner hiểu rằng đây là access path hot.

Ví dụ:

create index concurrently idx_jobs_runnable_by_tenant
on jobs (tenant_id, available_at, priority desc)
where status = 'queued' and deleted_at is null;

Index này không hard-code tenant nào, nhưng vẫn cực hợp với workload multi-tenant nơi worker lấy job runnable theo tenant.

Soft delete là một trong những use case partial index có ROI cao nhất

Rất nhiều bảng production có deleted_at hoặc archived_at. Nếu application mặc định chỉ làm việc với row active, full index thường lãng phí.

Ví dụ query phổ biến:

select id, customer_id, total_amount
from orders
where tenant_id = $1
  and deleted_at is null
  and status = 'open'
order by created_at desc
limit 50;

Thay vì index toàn bảng:

create index idx_orders_tenant_status_created_at
on orders (tenant_id, status, created_at desc);

một lựa chọn production-friendly hơn thường là:

create index concurrently idx_orders_open_active_by_tenant_created_at
on orders (tenant_id, created_at desc)
where deleted_at is null and status = 'open';

Lợi ích:

• index nhỏ hơn đáng kể nếu phần lớn order đã đóng hoặc đã archive;
• update các row không nằm trong predicate không phải duy trì index này;
• query đọc “open active orders” có access path rõ và hẹp hơn.

Nhưng nhớ một điều quan trọng: partial index không thay thế việc thiết kế query đúng. Nếu lúc thì query status = 'open', lúc thì status in ('open', 'pending_review'), lúc lại bỏ deleted_at is null, planner không thể ổn định dùng cùng access path.

Partial index và access pattern kiểu queue/poller

Một trong những pattern tôi thích nhất là bảng queue nội bộ hoặc jobs table.

Ví dụ worker chạy:

select id, payload
from jobs
where tenant_id = $1
  and status = 'queued'
  and available_at <= now()
  and deleted_at is null
order by priority desc, available_at asc
limit 100;

Nếu bảng chứa cả job done, failed, cancelled, archived, retry history và payload lớn, full index sẽ nhanh phình to. Partial index lúc này rất hợp:

create index concurrently idx_jobs_dispatchable_by_tenant
on jobs (tenant_id, priority desc, available_at asc)
where status = 'queued' and deleted_at is null;

Điểm hay ở đây là bạn đang index đúng phần worker cần dispatch, thay vì ép PostgreSQL giữ path tối ưu cho toàn bộ lịch sử xử lý job.

Pattern này có quan hệ gần với các bài về queue-based load leveling và consumer lag Kafka: queue chỉ khỏe khi path đọc và cập nhật trạng thái không bị index bloat kéo chậm dần theo thời gian.

Planner chỉ dùng partial index khi nó chứng minh được predicate phù hợp

Đây là chỗ nhiều team tạo partial index rồi thất vọng vì EXPLAIN không dùng.

PostgreSQL không “đoán giúp” rằng query của bạn gần giống predicate index. Nó cần thấy rằng điều kiện WHERE của query hàm ý predicate của partial index.

Ví dụ partial index:

create index idx_tasks_active_pending
on tasks (tenant_id, created_at desc)
where deleted_at is null and status = 'pending';

Query này có thể dùng index:

select *
from tasks
where tenant_id = 42
  and deleted_at is null
  and status = 'pending'
order by created_at desc
limit 20;

Nhưng query này có thể không dùng như bạn kỳ vọng:

select *
from tasks
where tenant_id = 42
  and coalesce(deleted_at, now()) = now()
  and status in ('pending');

Về mặt logic business, bạn có thể nghĩ hai query tương đương. Với planner, shape biểu thức khác đi đáng kể. Kinh nghiệm production ở đây là:

• predicate trong partial index nên phản ánh đúng query shape chuẩn ở application;
• tránh bọc cột trong function nếu không cần thiết;
• giữ điều kiện WHERE đơn giản, tường minh và ổn định.

Bẫy lớn: prepared statement và generic plan

Một partial index rất đẹp có thể vẫn bị bỏ qua nếu application dùng prepared statement theo cách khiến planner chọn generic plan quá sớm.

Ví dụ bạn có query:

select id, created_at
from invoices
where tenant_id = $1
  and status = $2
  and deleted_at is null
order by created_at desc
limit 100;

Nếu status = 'pending' là path nóng có partial index riêng, nhưng prepared statement được tái sử dụng cho nhiều status khác nhau, PostgreSQL có thể chọn generic plan bảo thủ hơn thay vì plan tối ưu cho 'pending'.

Hệ quả là:

• partial index tồn tại nhưng planner không luôn dùng;
• tenant lớn và tenant nhỏ cùng bị nhét vào một plan trung bình;
• query “đúng lý thuyết” vẫn chậm không ổn định.

Khi debug, đừng chỉ nhìn DDL. Hãy xem:

• application đang dùng prepared statement thế nào;
• có dấu hiệu generic plan không;
• parameter sensitivity có cao không;
• EXPLAIN (ANALYZE, BUFFERS) với giá trị thực tế khác nhau ra sao.

Điểm này nối rất gần với bài PostgreSQL query plan regression trong production: một phần lớn “index không hoạt động” thật ra là planner behavior và parameter sensitivity, không chỉ vì index definition.

Chọn key columns trong partial index thế nào?

Đừng vì partial index nhỏ hơn mà quên nguyên tắc cơ bản của composite index.

Bạn vẫn phải nghĩ về:

• equality columns trước hay sau;
• column dùng cho sort;
• column có giúp index-only scan không;
• query có cần covering thêm field nào không.

Ví dụ query thường gặp:

select id, created_at, amount
from invoices
where tenant_id = $1
  and deleted_at is null
  and status = 'pending'
order by created_at desc
limit 50;

Một index hợp lý có thể là:

create index concurrently idx_invoices_pending_active_by_tenant_created_at
on invoices (tenant_id, created_at desc)
include (amount)
where deleted_at is null and status = 'pending';

Vì sao:

• tenant_id là equality key quan trọng;
• created_at desc hỗ trợ order/limit;
• amount có thể giúp giảm heap access nếu workload phù hợp;
• predicate loại bỏ dữ liệu không cần đọc.

Nhưng nếu query còn filter mạnh theo customer_id, hoặc sort theo due_at, index này có thể lại không phù hợp. Không có “mẫu partial index chuẩn” cho mọi bảng.

Partial index không cứu được query shape tệ

Tôi thấy một anti-pattern lặp lại: query dùng OR tràn lan, join không cần thiết, predicate không nhất quán giữa các endpoint, rồi mong partial index giải quyết hết.

Ví dụ:

where tenant_id = $1
  and (
    status = 'pending'
    or retry_at <= now()
    or manual_review = true
  )

Một partial index cho biểu thức này thường khó mang lại ROI rõ ràng, vì predicate business quá “trộn”. Thường tốt hơn là:

• tách query theo intent;
• materialize trạng thái dispatchable rõ hơn;
• đưa rule vào một cột trạng thái nhất quán hơn;
• tránh bắt planner giải bài toán mơ hồ ở runtime.

Rollout partial index trong production nên đi như thế nào?

Đây không phải loại thay đổi nên làm kiểu “thêm index rồi cầu nguyện”. Tôi thích rollout theo playbook khá bảo thủ.

Bước 1: xác nhận workload thật

Dùng pg_stat_statements, log query hoặc APM để biết:

• query nào thật sự nóng;
• tần suất bao nhiêu;
• p95/p99 ra sao;
• có sort/limit nào cố định không;
• predicate có ổn định không.

Bước 2: đo cardinality của predicate

Hỏi rất rõ:

• bao nhiêu phần trăm row thỏa deleted_at is null and status = 'pending';
• phân bố theo tenant ra sao;
• dữ liệu này có tăng nhanh không;
• predicate có ổn định theo thời gian không.

Nếu predicate match 85% bảng, partial index thường không còn hấp dẫn như bạn tưởng.

Bước 3: tạo index CONCURRENTLY

Trên production, đa số case nên dùng CREATE INDEX CONCURRENTLY để giảm lock impact. Nhưng nhớ rằng:

• build concurrent lâu hơn;
• vẫn tốn I/O;
• có thể cạnh tranh tài nguyên với workload thật;
• cần theo dõi replication lag nếu cluster nhạy cảm.

Bước 4: so sánh EXPLAIN (ANALYZE, BUFFERS) trước và sau

Đừng kết luận bằng cảm giác. Hãy kiểm:

• planner có dùng partial index không;
• row estimate lệch bao nhiêu;
• heap fetch và shared read/hit thay đổi thế nào;
• latency cải thiện ở tenant lớn lẫn tenant nhỏ ra sao.

Bước 5: cân nhắc bỏ index cũ nếu partial index thay thế thật sự

Nhiều team thêm partial index nhưng không dám xóa full index cũ. Kết quả là write path vẫn gánh cả hai. Chỉ giữ cả hai khi bạn có lý do rõ ràng rằng chúng phục vụ access pattern khác nhau.

Nên đo gì ngoài latency query?

Nếu partial index thật sự tạo giá trị, bạn thường sẽ thấy thêm vài tín hiệu tốt khác ngoài query_time:

• kích thước index tổng giảm;
• tỉ lệ block read của index nóng giảm;
• write throughput ổn hơn do ít index maintenance hơn;
• autovacuum hoặc checkpoint pressure đỡ tệ hơn ở bảng lớn;
• worker throughput tăng vì scan path gọn hơn.

Đây là lý do partial index là bài toán của production engineering, không chỉ của database syntax.

Khi nào không nên dùng partial index?

1. Predicate không ổn định ở application

Nếu endpoint lúc có deleted_at is null, lúc không; lúc query pending, lúc in ('pending', 'retry'); partial index dễ bị dùng chập chờn.

2. Tỷ lệ row match quá cao

Nếu phần lớn bảng vẫn thỏa predicate, lợi ích footprint có thể không đáng kể.

3. Query chủ lực thay đổi liên tục theo sản phẩm

Nếu domain đang thay đổi nhanh, bạn có thể đang tối ưu quá sớm vào predicate chưa ổn định.

4. Team chưa có discipline để audit index lifecycle

Index nào cũng là nợ vận hành. Nếu thêm xong không đo, không revisit, không drop cái cũ, hệ thống chỉ ngày càng nặng.

Một ví dụ thực chiến: invoice dashboard cho tenant lớn

Giả sử SaaS của bạn có bảng invoices 120 triệu row. Dashboard kế toán chỉ xem invoice chưa archive, chủ yếu là pending, overdue, requires_action. Mỗi lần mở dashboard của tenant lớn, query phải sort top invoice mới nhất.

Nếu bạn dùng full composite index cho toàn bộ status, mọi invoice đã paid, cancelled, archived vẫn nằm trong index. Working set phình to nhanh.

Một cách thực dụng hơn là tách access path hot:

create index concurrently idx_invoices_attention_by_tenant_created_at
on invoices (tenant_id, created_at desc)
where deleted_at is null
  and status in ('pending', 'overdue', 'requires_action');

Sau đó verify:

• dashboard query có thực sự luôn dùng đúng predicate đó không;
• worker reconciliation có cần index khác không;
• full index cũ có còn phục vụ query nào quan trọng không.

Điểm mấu chốt: partial index hoạt động tốt nhất khi bạn xem nó như một access path riêng cho trạng thái nghiệp vụ nóng, chứ không phải phiên bản “tiết kiệm dung lượng” của full index.

Checklist trước khi tạo partial index mới

Trước khi thêm một partial index vào production, tôi thường muốn trả lời được 8 câu hỏi:

1. Query nào đang đau thật sự?
2. Predicate business có ổn định và lặp lại không?
3. Bao nhiêu phần trăm row match predicate đó?
4. Workload theo tenant có skew mạnh không?
5. Application có dùng prepared statement/generic plan khiến planner bỏ index không?
6. Full index cũ có đang che đi việc query shape chưa sạch không?
7. Sau khi thêm partial index, index nào có thể drop?
8. Cách đo thành công là gì: p95, buffers, index size, write cost hay queue throughput?

Nếu chưa trả lời được các câu này, thêm index mới thường chỉ là trì hoãn lúc phải hiểu workload thật.

Kết luận

Partial index trong PostgreSQL không phải trick nhỏ, mà là công cụ rất mạnh để đưa business predicate nóng vào chiến lược access path của hệ multi-tenant. Nó đặc biệt hữu ích khi bảng lớn chứa nhiều dữ liệu lạnh, soft delete phổ biến, workload tập trung vào một số trạng thái active, và write amplification đã bắt đầu ăn vào headroom của production.

Nhưng partial index chỉ thắng khi ba thứ khớp nhau:

• predicate business ổn định;
• query shape nhất quán;
• planner thật sự nhìn thấy và chọn access path đó.

Nếu thiếu một trong ba, bạn rất dễ có một index “đúng cú pháp nhưng sai thực tế”. Hãy bắt đầu từ workload nóng, dùng EXPLAIN (ANALYZE, BUFFERS) với parameter thật, rollout bảo thủ, và coi mỗi index mới là một quyết định vận hành phải tự chứng minh ROI.

Đọc tiếp trong cluster PostgreSQL và backend production

• PostgreSQL Query Plan Regression trong Production
• PostgreSQL Transaction Isolation trong Production
• N+1 Query trong Backend Production
• API Pagination: Cursor vs Offset trong Production
• Queue-Based Load Leveling trong Backend Production
• Consumer Lag trong Kafka Production