PostgreSQL Partitioning thực chiến: Thiết kế bảng lớn cho production

PostgreSQL partitioning chia một bảng logic lớn thành nhiều bảng vật lý nhỏ hơn, nhưng không tự biến mọi truy vấn thành nhanh. Thiết kế tốt phải chọn partition key xuất hiện tự nhiên trong điều kiện lọc và vòng đời dữ liệu, tạo partition trước hạn, giữ số lượng hợp lý, kiểm chứng pruning bằng plan thật và vận hành attach/detach với lock được đo lường.

Bài viết bám theo tài liệu PostgreSQL hiện hành về table partitioning, CREATE TABLE và ALTER TABLE. Khả năng DDL concurrent và mức lock thay đổi theo major version; luôn kiểm tra tài liệu đúng phiên bản production.

1. Partitioning giải quyết bài toán nào?

Partitioning không thay index, schema tốt, vacuum, query tuning hay capacity planning. Một bảng vừa phải có index đúng thường đơn giản và nhanh hơn một cây hàng nghìn partition.

2. Range, list hay hash?

• Range: phù hợp timestamp, ngày, ID tăng dần hoặc khoảng số; hỗ trợ retention theo cửa sổ.
• List: phù hợp tập giá trị rời rạc như region hoặc business unit, khi danh sách được quản lý rõ.
• Hash: phân tán tương đối đều theo key khi không có ranh giới tự nhiên; khó detach theo thời gian.

Subpartition chỉ nên dùng khi một cấp không giải quyết được cả pruning và lifecycle. Ví dụ range theo tháng rồi hash theo tenant có thể tăng số leaf rất nhanh. Mỗi lớp thêm catalog object, index, DDL, monitoring và failure mode.

3. Ví dụ bảng events theo tháng

CREATE TABLE events (
    tenant_id  bigint      NOT NULL,
    occurred_at timestamptz NOT NULL,
    event_id   uuid        NOT NULL,
    event_type text        NOT NULL,
    payload    jsonb       NOT NULL,
    PRIMARY KEY (tenant_id, occurred_at, event_id)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_10 PARTITION OF events
FOR VALUES FROM ('2026-10-01 00:00:00+00')
             TO ('2026-11-01 00:00:00+00');

CREATE TABLE events_2026_11 PARTITION OF events
FOR VALUES FROM ('2026-11-01 00:00:00+00')
             TO ('2026-12-01 00:00:00+00');

Range bound bao gồm đầu dưới và loại trừ đầu trên. Nhờ dùng khoảng nửa mở, đúng thời điểm đầu tháng chỉ thuộc một partition. Với timestamptz, ghi offset rõ ràng và quyết định ranh giới nghiệp vụ theo UTC hay timezone cụ thể; DST có thể làm “một ngày địa phương” không luôn dài 24 giờ.

4. Partition key phải khớp query và retention

Chọn key theo predicate xuất hiện trong workload thật, không theo cột “có vẻ phân bố đẹp”. Nếu hầu hết query luôn lọc tenant_id nhưng partition chỉ theo thời gian và giữ nhiều năm, mỗi query tenant dài hạn vẫn chạm nhiều partition. Ngược lại, partition theo tenant có thể làm retention thời gian thành hàng nghìn câu DELETE.

Đánh giá bằng trace/query statistics:

• tỷ lệ query có điều kiện trên candidate key;
• số partition còn lại sau pruning;
• độ lệch kích thước và write rate giữa partitions;
• độ dài retention và đơn vị dữ liệu cần xóa cùng lúc;
• planning time, execution time và số session đồng thời.

5. Pruning dựa vào bound, không dựa vào index

EXPLAIN (ANALYZE, BUFFERS)
SELECT tenant_id, event_id, event_type
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-10-10 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-10-11 00:00:00+00'
  AND tenant_id = 42;

PostgreSQL chứng minh partition nào không thể chứa row dựa trên partition bounds. Index chỉ quyết định cách đọc bên trong partition còn lại. Predicate half-open trực tiếp trên occurred_at thường dễ prune hơn việc bọc cột bằng function hoặc cast không tương thích.

Pruning có thể diễn ra lúc plan và lúc execution khi giá trị parameter chỉ biết sau. Đọc Subplans Removed, partition xuất hiện, (never executed), loops, planning time và buffers. Đừng chỉ nhìn tổng execution time của dataset nhỏ.

6. Index trên parent là “virtual”

CREATE INDEX events_tenant_time_idx
ON events (tenant_id, occurred_at DESC);

CREATE INDEX events_type_time_idx
ON events (event_type, occurred_at DESC);

Index khai báo trên partitioned parent tạo/điều phối index tương ứng trên leaf partitions; dữ liệu index thực nằm ở từng leaf. Partition mới tạo hoặc attach cần được kiểm tra có index hợp lệ tương ứng theo phiên bản và quy trình DDL.

Không bắt buộc index partition key chỉ để pruning hoạt động. Tạo index khi query sau pruning chỉ đọc phần nhỏ của partition. Nếu báo cáo đọc phần lớn một partition tháng, sequential scan có thể hợp lý hơn.

7. Unique constraint có ranh giới quan trọng

Để PostgreSQL bảo đảm uniqueness trên toàn partitioned table, unique/primary key phải chứa tất cả cột partition key của hierarchy. Vì vậy UNIQUE (event_id) trên parent partition theo occurred_at không thể tự bảo đảm uniqueness toàn cục; ví dụ dùng composite primary key chứa occurred_at.

Nếu nghiệp vụ bắt buộc một ID duy nhất toàn hệ thống nhưng key không chứa partition key, lựa chọn gồm:

• sinh UUID đủ mạnh và chấp nhận xác suất thay vì constraint toàn cục—phải ghi rõ trade-off;
• giữ registry table không partition với unique ID và tham chiếu trong cùng transaction;
• đổi partition key/schema để constraint tự nhiên chứa key;
• kiểm soát ở ứng dụng, nhưng hiểu rằng check-then-insert có race nếu thiếu cơ chế nguyên tử.

8. Tạo partition trước hạn, đừng đợi insert lỗi

Insert vào parent mà không có partition phù hợp sẽ lỗi. Automation nên tạo trước nhiều kỳ, idempotent và alert khi coverage tương lai giảm dưới ngưỡng.

SELECT
  parent.relname AS parent,
  child.relname  AS partition
FROM pg_inherits i
JOIN pg_class parent ON parent.oid = i.inhparent
JOIN pg_class child  ON child.oid  = i.inhrelid
WHERE parent.relname = 'events'
ORDER BY child.relname;

Generator DDL cần dùng calendar library đúng timezone, khóa chống hai scheduler tạo cùng bảng và verify bound trong catalog. Không ghép raw identifier từ input không tin cậy.

9. Default partition: safety net có giá

Default partition nhận mọi row chưa khớp bound khác, giúp ingestion không dừng khi automation trễ. Nhưng nó dễ trở thành “bãi rác” che lỗi routing. Khi thêm partition mới, row trong default có thể overlap bound mới và phải được di chuyển; validation có thể scan/lock bảng lớn.

• Alert ngay khi default có row mới.
• Quarantine và di chuyển row theo batch ngắn.
• Không coi default là retention partition.
• Đo lock/scan khi tạo hoặc attach bound mới, đặc biệt trên phiên bản đang chạy.
• Nếu availability quan trọng hơn fallback, có thể không tạo default để fail fast.

10. Chuẩn bị bảng ngoài rồi ATTACH

Bulk load vào bảng độc lập cho phép validate, tạo index và analyze trước khi đưa vào cây. Một quy trình khái quát:

1. Tạo staging table cùng columns/storage.
2. Thêm CHECK constraint chính xác với bound tương lai.
3. Load và validate dữ liệu; mọi row phải nằm trong bound.
4. Tạo index tương ứng, dùng phương thức concurrent nếu phù hợp với release/workflow.
5. Analyze và kiểm tra constraint/index validity.
6. Attach trong maintenance guardrail với lock_timeout phù hợp.

ALTER TABLE events
ATTACH PARTITION events_2026_12
FOR VALUES FROM ('2026-12-01 00:00:00+00')
             TO ('2027-01-01 00:00:00+00');

Constraint tương thích có thể giúp PostgreSQL tránh scan validation không cần thiết. Tuy vậy ATTACH vẫn liên quan lock trên parent/partition và có thể tương tác với default partition. Test bằng bản sao schema/data representative trước rollout.

11. Retention bằng DETACH rồi mới DROP

ALTER TABLE events DETACH PARTITION events_2025_10;

# Sau khi backup/đối soát và ngoài SQL transaction phù hợp
DROP TABLE events_2025_10;

Detach tách dữ liệu khỏi query parent nhưng giữ bảng để export, audit hoặc rollback. Một số release hỗ trợ DETACH PARTITION ... CONCURRENTLY với restriction riêng; không giả định nó luôn dùng được, nhất là khi có default partition hoặc khi chạy trong transaction block.

Drop partition tránh tạo lượng dead tuple và vacuum debt như DELETE hàng triệu row. Nhưng đây là thao tác phá dữ liệu: manifest retention phải xác minh bound, legal hold, backup, replica lag và đúng environment trước khi drop.

12. Update partition key có thể di chuyển row

Khi update làm row không còn thỏa bound cũ, PostgreSQL có thể route row sang partition mới. Điều này không phải update tại chỗ đơn giản: trigger, foreign key, concurrency, replication và write amplification đều cần test. Với event bất biến, thường nên cấm sửa occurred_at hoặc xử lý correction bằng event mới.

13. Migration từ bảng hiện hữu

Không thể biến thiết kế partition trên một bảng khổng lồ thành an toàn chỉ bằng một DDL tùy tiện. Kế hoạch thường gồm:

1. tạo cây partition mới và index/constraint;
2. backfill theo range nhỏ, có checkpoint và throttle;
3. giữ delta bằng dual-write, trigger tạm hoặc CDC đã kiểm chứng;
4. đối soát count, checksum nghiệp vụ và missing/duplicate keys;
5. cutover trong lock budget định trước;
6. giữ rollback window trước khi xóa bảng cũ.

Measure WAL, replica lag, autovacuum và cache eviction trong backfill. Dual-write không nguyên tử qua hai code path có thể làm lệch dữ liệu; ưu tiên một transaction/database mechanism có semantics rõ.

14. Monitoring production

Lưu một bộ query đại diện và so sánh EXPLAIN (ANALYZE, BUFFERS) sau upgrade, tăng số partition hoặc đổi ORM. Prepared statement, cast và predicate do ORM sinh có thể làm plan khác query thủ công.

Checklist production

• Partitioning có mục tiêu đo được: pruning, retention, bulk load hoặc locality.
• Partition key khớp predicate và lifecycle trong workload thật.
• Granularity giữ số leaf hợp lý; không mặc định “càng nhiều càng tốt”.
• Range dùng half-open bound, timezone được định nghĩa rõ.
• Unique/primary key đã tính đến yêu cầu chứa partition key.
• Partition tương lai được tạo tự động, idempotent và có alert coverage.
• Default partition được giám sát hoặc cố ý không dùng.
• Mỗi leaf có index/constraint/statistics hợp lệ.
• Attach/detach/drop có lock timeout, dry run, backup và legal-hold guardrail.
• Plan thực chứng minh pruning; planning time và buffers nằm trong SLO.

Kết luận

Declarative partitioning mạnh nhất khi ranh giới vật lý trùng với cách hệ thống truy vấn và xóa dữ liệu. Thiết kế đúng không dừng ở câu PARTITION BY: cần uniqueness khả thi, partition tương lai, default policy, index leaf, DDL lock budget, telemetry và runbook retention. Hãy dùng partitioning để loại bỏ phần dữ liệu chắc chắn không cần đọc và thao tác lifecycle theo khối—không dùng nó để che một schema hay query chưa được tối ưu.