Postgres partitioning theo thời gian: bảng 500 triệu dòng không còn là nỗi sợ

Có một buổi tối mình được page vì bảng events của một service analytics đã chạm 480 triệu dòng, và autovacuum đang chiếm 100% CPU của một replica suốt 6 tiếng không xong. Query nhanh nhất trên bảng — SELECT count(*) WHERE created_at > now() - interval '1 hour' — đã leo lên 4 giây vì planner phải scan quá nhiều page. Data cũ hơn 90 ngày thực tế không ai đọc, nhưng vẫn nằm cùng bảng, vẫn được VACUUM, vẫn ngốn RAM của index.

Đây là kịch bản mà declarative partitioning của Postgres sinh ra để giải quyết. Bài này mình kể lại cách setup, tự động hóa bằng pg_partman, và những bẫy đã dính khi migrate bảng đang chạy.

Partitioning là gì và khi nào cần

Partitioning chia một bảng logic thành nhiều bảng con (partition) theo một tiêu chí — thường là thời gian. Với người viết query, nó vẫn là một bảng: SELECT * FROM events WHERE ... vẫn hoạt động y hệt. Nhưng dưới capo, Postgres chỉ scan các partition thực sự chứa dữ liệu match được WHERE (partition pruning).

Bạn cần cân nhắc partition khi có ít nhất một trong các dấu hiệu:

• Bảng > 100 triệu dòng và đang tăng nhanh
• Autovacuum bắt đầu không kịp — bloat tăng, freeze age tăng dần
• Cần xóa data cũ định kỳ — mà DELETE 50 triệu dòng thì kéo replica lag, sinh WAL khổng lồ, index vẫn còn xác chết
• Query luôn có filter theo một chiều cố định (thường là created_at)

Nếu bảng chỉ vài triệu row và bạn không xóa gì, đừng partition sớm — nó thêm complexity mà không được lợi gì.

Declarative partitioning cơ bản

Postgres 10+ có cú pháp PARTITION BY chuẩn. Đây là ví dụ tối giản cho bảng events chia theo tháng:

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    user_id     uuid NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Tạo partition cụ thể cho từng tháng
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Vài điểm quan trọng lúc setup:

1. Cột partition phải nằm trong primary key. Nếu bạn dùng id làm PK, phải đổi thành PK composite (id, created_at) — Postgres không cho PK không chứa cột partition. Điều này ảnh hưởng đến foreign key và ORM, cần biết trước.

2. Index phải tạo trên bảng cha, không phải partition con. Postgres sẽ tự propagate xuống các partition:

CREATE INDEX idx_events_user_created
    ON events (user_id, created_at DESC);
-- Tự động tạo trên mọi partition đã có VÀ mọi partition mới

3. Đừng quên default partition. Nếu bạn insert một row có created_at không nằm trong range nào, insert sẽ fail. Tạo default để “hứng” lỗi thời gian:

CREATE TABLE events_default PARTITION OF events DEFAULT;

Đây thực chất là canary — nếu default có data, tức là bạn quên tạo partition tương lai.

Query lợi gì

Với EXPLAIN, bạn sẽ thấy partition pruning ngay:

EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2026-08-15' AND created_at < '2026-08-20';

-- Aggregate  (cost=...)
--   ->  Seq Scan on events_2026_08 events
--         Filter: (created_at >= ... AND created_at < ...)

Chỉ events_2026_08 bị scan. Nếu bảng cha có 500M dòng phân bố đều 12 tháng, mỗi partition chỉ ~42M dòng — vừa nhẹ vừa nóng trong cache. Query tương tự không partition sẽ vẫn dùng index nhưng phải walk qua B-tree lớn hơn nhiều.

Cảnh báo pruning bị vô hiệu: nếu bạn filter theo một biểu thức mà planner không chắc thuộc partition nào, pruning tắt và query scan toàn bộ. Ví dụ:

-- KHÔNG prune được (function trên cột)
WHERE date_trunc('day', created_at) = '2026-08-20'

-- Prune được (so sánh trực tiếp)
WHERE created_at >= '2026-08-20' AND created_at < '2026-08-21'

Đây là bẫy phổ biến — dev quen viết WHERE date(created_at) = ..., chuyển sang partitioned table thì query đột nhiên chậm gấp 20 lần. Fix bằng cách viết range explicit.

Drop partition cũ = xóa data tức thì

Đây là lý do lớn nhất mình yêu partitioning. Retention policy 90 ngày với bảng non-partitioned:

DELETE FROM events WHERE created_at < now() - interval '90 days';
-- 50 triệu row, chạy 40 phút, sinh 30GB WAL, autovacuum sau đó chạy 3 tiếng

Với partitioned table:

DROP TABLE events_2026_05;
-- Xong trong ~50ms. Đĩa được giải phóng ngay. Không WAL, không VACUUM.

Sự khác biệt này ở production là ngày và đêm. Job dọn dẹp hàng đêm chạy trong 1 giây thay vì làm nghẽn database 4 tiếng.

Nếu bạn cần archive trước khi drop:

ALTER TABLE events DETACH PARTITION events_2026_05;
-- Bảng detach vẫn nằm đó, không còn thuộc events
-- pg_dump ra S3, hoặc gắn vào bảng archive khác
DROP TABLE events_2026_05;

Tự động hóa bằng pg_partman

Tạo partition thủ công mỗi tháng là kịch bản tốt để quên. Ai cũng sẽ quên. Extension pg_partman sinh ra để tự động:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table  => 'public.events',
    p_control       => 'created_at',
    p_type          => 'range',
    p_interval      => '1 month',
    p_premake       => 4         -- luôn có sẵn 4 partition tương lai
);

-- Retention 90 ngày, tự drop
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false
WHERE parent_table = 'public.events';

Sau đó chạy SELECT partman.run_maintenance() bằng cron (hoặc pg_cron trong DB) mỗi giờ. Nó sẽ:

• Tạo partition tháng sau nếu chưa có
• Drop hoặc detach partition quá 90 ngày

Đây là setup “cắm điện quên đi”. Tuần trước mình mới nhận ra cluster đã tự đẻ và tự xóa partition suốt 3 tháng mà mình chưa can thiệp gì.

Migrate bảng đang chạy — bẫy đáng nhớ

Đây là phần khó. Bảng non-partitioned đang có 300M row, không thể ALTER TABLE ... PARTITION BY trực tiếp — Postgres không hỗ trợ. Cách mình dùng:

1. Tạo bảng mới events_new đã partition
2. Backfill: INSERT INTO events_new SELECT * FROM events — chia batch theo ngày để không kẹt WAL
3. Đồng bộ delta bằng trigger hoặc logical replication cho khoảng thời gian backfill chạy
4. Rename atomic:

BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
COMMIT;

Các bẫy mình từng dính:

• Foreign key trỏ đến events.id: Postgres 12+ cho phép FK trỏ vào partitioned table, nhưng phải đảm bảo cột được reference nằm trong unique key có cột partition. Nếu bạn có FK sẵn — kiểm tra kỹ trước, đôi khi cần bỏ FK.
• ON CONFLICT (id): Trên partitioned table thì ON CONFLICT chỉ hoạt động khi target index bao gồm cột partition, hoặc là index unique trong từng partition (khai báo PARTITION BY + UNIQUE chỉ enforce trong partition).
• Sequence nhảy khoảng: Nếu bạn dùng bigserial và insert từ nhiều partition, thứ tự ID không còn khớp với thứ tự thời gian như trước. Xem xét chuyển sang UUID v7 — hai pattern này bổ trợ cho nhau tốt.
• COPY trực tiếp vào partition con: nhanh hơn COPY vào bảng cha ~20% vì skip tuple routing. Có ích lúc backfill lớn.

Số đo thực tế

Với cùng workload analytics (5000 insert/s, 100 query/s), so sánh trước và sau khi partition theo tháng, retention 90 ngày:

Metric	Non-partitioned	Partitioned (12 tháng)
Kích thước bảng chính	187 GB	62 GB (partition nóng)
Query point-lookup theo user_id + created_at (p99)	42 ms	6 ms
DELETE retention hàng đêm	38 phút, +30GB WAL	DROP: 40 ms, 0 WAL
Autovacuum tổng thời gian/ngày	6.5 giờ	28 phút
Index size của PK	24 GB	8.9 GB (partition nóng)

Điểm đáng nói không phải “nhanh gấp 7 lần” ở p99, mà là autovacuum không còn là nút cổ chai. Trước đó, mọi sự cố hiệu năng đều bắt đầu bằng câu “autovacuum đang chạy trên events”. Sau khi partition, autovacuum trên từng partition nhỏ chạy trong vài phút, không bao giờ block operations khác.

Khi nào KHÔNG partition

Có ba trường hợp mình gặp và quyết định không partition:

• Bảng master data ổn định (< 10M row, không tăng nhanh): partition thêm complexity không có lợi.
• Không có cột phân chia tự nhiên: nếu query phân tán đều mọi trục, không tìm được cột partition sẽ prune hiệu quả, bạn chỉ đang chuyển complexity chứ không giải quyết vấn đề.
• Cross-partition join heavy: partition-wise join của Postgres đã cải thiện nhiều nhưng vẫn có overhead. Nếu 90% query là join giữa các partitioned table với điều kiện phức tạp, đo trước khi làm.

Kết

Postgres partitioning không phải magic, nhưng cho các bảng events/logs/orders lớn dần theo thời gian, đây là công cụ mà mình mặc định reach tới khi thấy dấu hiệu bloat. Setup không quá phức tạp, và pg_partman biến việc bảo trì thành cronjob một dòng.

Nếu bạn đang có một bảng “quá to để VACUUM kịp” — đây là bước tối ưu có tỉ lệ lợi/công cao nhất mình từng làm với Postgres. Đi kèm với UUID v7 làm PK và index đúng theo pattern query, hầu hết vấn đề hiệu năng của bảng lớn sẽ tự biến mất.