Partitioning trong PostgreSQL: lợi ích thật nằm ở vận hành Trung cấp

Tóm tắt nhanh

Partitioning không phải công cụ tăng tốc query. Một B-tree index đúng chỗ thường nhanh ngang hoặc hơn. Giá trị thật của partition là vận hành: xoá dữ liệu cũ tức thì, vacuum nhỏ, index nhỏ, dữ liệu lạnh tách khỏi dữ liệu nóng.

Lợi ích	Có thật không?	Điều kiện
Retention: DETACH + DROP thay cho DELETE	Có, lớn nhất	Chia theo thời gian, xoá theo cả partition
Vacuum/bloat theo từng partition	Có	Dữ liệu cũ gần như không còn bị sửa
Index nhỏ hơn, vừa RAM hơn	Có, khi bảng lớn	Index thật sự quá lớn so với RAM
Query nhanh hơn một index tốt	Thường không	Pruning chỉ thay các tầng trên của index
Giảm I/O khi cần quét cả khoảng lớn	Có	Seq scan một partition thay cho quét cả bảng

Tài liệu PostgreSQL nêu rule of thumb: chỉ đáng partition khi bảng lớn hơn RAM của server.

Baseline công bằng: partition có nhanh hơn index không?

Bảng 300.000 dòng (payload 200 byte, khoảng 72 MB), dữ liệu append-only theo created_at như log thật, 3 tháng. Ba cấu hình, mọi số đo là lần chạy thứ ba (warm), bảng thật nên plan hiện shared:

• E1: bảng thường, không index.
• E2: bảng thường + B-tree trên created_at.
• E3: bảng partition theo tháng, mỗi partition có index.

SELECT sum(length(payload)) FROM event_xxx WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01'; -- Q1: 1 tháng
SELECT sum(length(payload)) FROM event_xxx WHERE created_at = '2026-02-15';                                -- Q2: 1 ngày

Cấu hình	Q1 (1/3 bảng): plan	Buffers	Thời gian	Q2 (1/90 bảng): plan	Buffers	Thời gian
E1 flat, không index	Parallel Seq Scan	9.152	13.2 ms	Parallel Seq Scan	9.152	6.4 ms
E2 flat + B-tree	Index Scan	2.913	22.4 ms	Index Scan	107	0.75 ms
E3 partition + index	Seq Scan 1 partition	2.829	19.9 ms	Index Scan 1 partition	107	0.77 ms

Đọc bảng này:

• Q2: E2 và E3 gần như y hệt (107 buffer, 0.75 ms so với 0.77 ms). Partition không thắng index.
• Q1: E1 thắng về thời gian nhờ parallel scan dù đọc nhiều buffer gấp 3. Đọc ít buffer chưa chắc nhanh hơn.
• Tổng kích thước index: 2.112 kB (flat) so với 736 + 664 + 736 = 2.136 kB (3 partition). Không tiết kiệm, chỉ chia nhỏ ra.

Kết luận thực dụng: nếu chỉ muốn query theo thời gian nhanh, hãy thêm index trước. Đừng partition vì lý do tốc độ.

Ghi chú: số liệu từ PostgreSQL 17.11 trong Docker, bảng nhỏ nằm gọn trong RAM. Với bảng lớn hơn RAM, lợi thế index nhỏ và dữ liệu nóng gọn sẽ rõ hơn. Script: examples/src/postgres-partitioning/partitioning-baseline-demo.sql.

Lợi ích thật: retention bằng DETACH/DROP

Xoá toàn bộ tháng 1 (103.334 dòng) trên cùng dữ liệu:

Cách	Thời gian	Hậu quả
DELETE ... WHERE created_at < '2026-02-01'	59 ms	Sinh 103.334 dead tuple; file vẫn 72 MB sau cả VACUUM thường
ALTER TABLE ... DETACH PARTITION + DROP TABLE	0.7 ms + 1.7 ms	Giải phóng file ngay, không có dead tuple, không cần vacuum

Trên bảng 5 tỷ dòng, DELETE chạy hàng giờ, sinh WAL khổng lồ và để lại bloat. DROP vẫn là vài mili-giây.

DETACH PARTITION thường lấy ACCESS EXCLUSIVE trên bảng cha. Từ PG14 có DETACH PARTITION ... CONCURRENTLY chỉ cần SHARE UPDATE EXCLUSIVE (có hạn chế, xem tài liệu ALTER TABLE). Dùng bản này trên hệ thống đang chạy.

Cách partition pruning hoạt động

Bảng partition là bảng logic không có storage riêng. Mỗi partition con là một bảng bình thường. Ba cách chia:

Kiểu	Khi nào dùng	Ví dụ
RANGE	Time-series, retention	created_at theo tháng
LIST	Tập giá trị hữu hạn	region, nhóm tenant
HASH	Chia đều tải ghi/dung lượng, không có range tự nhiên	tenant_id modulus 4

Pruning là việc planner loại partition không thể chứa dòng thoả WHERE, dựa vào bounds, trước khi đọc dữ liệu.

Partition pruning: chỉ 1 trong 3 partition được quét

Phóng to sơ đồ

Query → event_partWHERE created_at = 15/02

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/3

Query lọc trên partition key (created_at). Bảng cha chỉ là "mục lục", dữ liệu nằm ở các partition con.

Plan cho Q2 trên E3 chỉ có một node, không có dấu vết của hai partition kia:

Aggregate
  ->  Index Scan using event_2026_02_created_at_idx on event_2026_02 event_part
        Buffers: shared hit=107

Query không có điều kiện trên partition key (ví dụ WHERE payload LIKE 'nomatch%') phải bọc cả ba partition trong Append và quét hết. Chi phí y như bảng thường, cộng thêm Append.

Runtime pruning: giá trị chỉ biết lúc chạy

Khi dùng prepared statement với generic plan, planner chưa biết tham số, nên pruning dời sang lúc khởi tạo executor (và cả lúc thực thi, với subquery hoặc parameterized nested loop). Plan thật với plan_cache_mode = force_generic_plan:

Aggregate (actual rows=1 loops=1)
  ->  Append (actual rows=3334 loops=1)
        Subplans Removed: 2
        ->  Index Only Scan using event_2026_02_created_at_idx on event_2026_02 event_part_1
              Index Cond: (created_at = $1)

Subplans Removed: 2 là dấu hiệu runtime pruning đang chạy. Các partition bị loại ở bước khởi tạo không hiện trong EXPLAIN. Có thể tắt toàn bộ pruning bằng enable_partition_pruning (mặc định on), chỉ để chẩn đoán.

Pruning bị phá: bọc partition key trong hàm

Planner chỉ so sánh được khi điều kiện áp trực tiếp lên cột key. Hai query dưới đây đều cho cùng một kết quả logic, nhưng quét cả 3 partition:

WHERE date_trunc('month', created_at) = '2026-02-01'   -- hàm bọc key
WHERE created_at::text >= '2026-02'                    -- cast bọc key

Parallel Append
  ->  Parallel Index Only Scan using event_2026_01_created_at_idx ...
  ->  Parallel Index Only Scan using event_2026_03_created_at_idx ...
  ->  Parallel Index Only Scan using event_2026_02_created_at_idx ...

Viết lại thành khoảng created_at >= '2026-02-01' AND created_at < '2026-03-01'. Đây là bug kinh điển, đặc biệt với timestamptz bị cast ngầm.

Vận hành: những thứ làm partition đau

Phải có partition sẵn cho dữ liệu sắp tới

INSERT ngày không thuộc partition nào thất bại:

ERROR:  no partition of relation "event_part" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2026-04-10).

Hai cách chống: tạo partition trước vài kỳ (cron, hoặc extension pg_partman), và/hoặc có partition DEFAULT làm lưới an toàn. Nhưng DEFAULT có cái giá: mỗi lần ATTACH partition mới, PostgreSQL phải quét DEFAULT để chắc không có dòng nào thuộc partition đó, trong khi giữ ACCESS EXCLUSIVE trên partition DEFAULT (bảng cha chỉ bị SHARE UPDATE EXCLUSIVE). Một CHECK phù hợp trên DEFAULT cho phép bỏ bước quét này.

Attach partition không bị quét lại

Với bảng đã có dữ liệu, thêm CHECK khớp bounds trước khi ATTACH để bỏ bước validate, rồi drop CHECK thừa:

ALTER TABLE event_2026_04 ADD CONSTRAINT p CHECK (created_at >= '2026-04-01' AND created_at < '2026-05-01');
ALTER TABLE event_part ATTACH PARTITION event_2026_04 FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
ALTER TABLE event_2026_04 DROP CONSTRAINT p;

Vòng đời một partition

Đang tải diagram…

Ràng buộc và giới hạn cần biết

Chủ đề	Sự thật
Primary key / unique	Phải chứa mọi cột partition key, và key không được là expression. Không có unique toàn cục theo cột khác.
Foreign key	Bảng khác có thể tham chiếu bảng partition (PG12+), nhưng cột được tham chiếu phải có unique constraint, tức phải chứa partition key.
UPDATE partition key	Dòng bị chuyển sang partition khác (xoá + chèn), tốn hơn update thường.
Số partition	Planner xử lý tốt tới vài nghìn nếu query prune còn ít partition; mỗi session nạp metadata cho mọi partition nó chạm, nên RAM tăng. Đừng mặc định “nhiều hơn là tốt hơn”.
Partition-wise join/aggregate	Có nhưng tắt mặc định (enable_partitionwise_join, enable_partitionwise_aggregate); bật có chủ đích.

Khi nào nên và không nên

Nên partition khi	Không nên khi
Có chính sách retention theo thời gian (log, audit, event)	Bảng nhỏ hơn RAM và chỉ muốn query nhanh: thêm index
Bảng lớn hơn RAM, truy cập tập trung vào partition mới	Query thường không lọc theo key (mọi query quét hết)
Muốn vacuum/reindex từng phần, không động tới cả bảng	Cần unique toàn cục trên cột không phải key
Dữ liệu cũ muốn đẩy sang storage rẻ	Chưa có quy trình tạo/xoá partition tự động

Muốn chuyển bảng lớn đang chạy sang partition: tạo bảng partition mới song song, copy theo lô, rồi đổi tên; hoặc ATTACH bảng cũ như một partition (kèm CHECK) rồi tách dần. Đây là việc nhiều tuần với bảng thật, cần lên kế hoạch riêng.

RANGE, LIST, HASH route dữ liệu thế nào

INSERT vào bảng cha luôn được route tới đúng partition con. LIST theo region (east/west, còn lại vào DEFAULT):

 partition_name | count
----------------+-------
 account_east   |    10
 account_west   |    10
 account_other  |    10

HASH theo tenant_id (4 bucket, chỉ 20 giá trị tenant_id khác nhau nên lệch):

 partition_name | count
----------------+-------
 session_h0     |    30
 session_h1     |    30
 session_h2     |    15
 session_h3     |    25

HASH chỉ prune được với điều kiện equality trên đúng cột hash. Mục đích chính là chia đều tải ghi và dung lượng, không phải tăng tốc đọc theo khoảng.

Ví dụ thực tế

Audit log 18 tháng, 40 triệu dòng mỗi tháng

Bảng audit giữ 18 tháng, RANGE theo tháng. Mỗi đầu tháng cron tạo partition cho 2 tháng tới và DETACH ... CONCURRENTLY rồi DROP partition quá 18 tháng. Việc dọn dữ liệu tốn vài mili-giây thay vì DELETE 40 triệu dòng mỗi tháng (dead tuple, WAL, bloat).

Query “N ngày gần nhất” chỉ chạm 1-2 partition. Tính năng mới cần “lịch sử một user, không giới hạn ngày” sẽ quét mọi partition: đừng bỏ partition, hãy thêm index (user_id, created_at) trên bảng cha (PostgreSQL tạo index tương ứng cho từng partition).

Trade-offs

Ưu điểm

• DETACH/DROP partition xoá dữ liệu cũ tức thì, không dead tuple, không vacuum
• Vacuum, reindex, backup chia nhỏ theo từng partition; dữ liệu lạnh đẩy sang storage rẻ
• Pruning thay các tầng trên của index và cho phép seq scan gọn một partition khi cần quét khoảng lớn

Nhược điểm

• Không nhanh hơn một B-tree index tốt trong đo đạc ở trên
• Query không lọc theo partition key tốn như không partition, cộng Append
• Unique/PK phải chứa partition key; không có unique toàn cục theo cột khác
• Thêm việc vận hành: tạo partition trước, giám sát, quản lý DEFAULT, planning time tăng theo số partition

Checklist production

1. Chọn partition key theo cách retention và query chính lọc dữ liệu, không theo sở thích.
2. Tạo partition trước ít nhất 2 kỳ; cảnh báo khi partition tương lai sắp hết.
3. Quyết định có DEFAULT hay không; nếu có, giám sát số dòng rơi vào đó.
4. Dùng DETACH ... CONCURRENTLY trên hệ thống sống; ATTACH kèm CHECK để tránh quét.
5. Viết điều kiện trực tiếp trên key (khoảng), không bọc hàm hay cast.
6. Kiểm tra EXPLAIN với prepared statement/generic plan: tìm Subplans Removed.
7. Giữ số partition ở mức hợp lý (tuần hoặc tháng, không phải theo giờ nếu không cần).
8. Thiết kế PK có chứa partition key ngay từ đầu.

Sai lầm thường gặp

Sai lầm thường gặp

Partition theo created_at vì tin rằng query sẽ nhanh hơn. Với bảng nhỏ hơn RAM, một index trên created_at cho cùng kết quả (0.75 ms so với 0.77 ms trong đo ở trên). Rồi vài tháng sau chạy query lọc theo user_id và nhận ra nó quét mọi partition. Hãy partition vì retention và vận hành, và kiểm chứng bằng baseline có index.

Tóm tắt

Tóm tắt

• Partitioning không nhanh hơn một B-tree index đúng chỗ; luôn đo thêm baseline “flat + index”
• Lợi ích lớn nhất: retention bằng DETACH/DROP (2.4 ms so với DELETE 59 ms + 72 MB bloat trong demo), vacuum và index chia nhỏ
• Pruning chỉ hoạt động khi WHERE áp trực tiếp lên partition key; hàm hoặc cast bọc key làm quét mọi partition
• Với generic plan, pruning chuyển sang runtime; tìm Subplans Removed trong EXPLAIN
• Cần tạo partition trước, cân nhắc DEFAULT, ATTACH kèm CHECK, DETACH CONCURRENTLY khi chạy production
• PK/unique phải chứa partition key; số partition lớn tăng planning time và RAM

Tự kiểm tra

1. Vì sao query một ngày trên bảng flat có index và trên bảng partition có index đọc cùng 107 buffer?
2. WHERE date_trunc('month', created_at) = '2026-02-01' có prune được không? Viết lại thế nào?
3. Vì sao có partition DEFAULT làm ATTACH chậm hơn?
4. Khi nào DETACH PARTITION thường cần ACCESS EXCLUSIVE và cách nào tránh?

Tham khảo

• PostgreSQL 17 — Table Partitioning
• PostgreSQL 17 — CREATE TABLE
• PostgreSQL 17 — ALTER TABLE