Working with PostgreSQL

Zerodha · 2021 · 3. Quy mô lớn · Độ khó: Trung bình — bài gốc

Bối cảnh

• Các database của Console (báo cáo, lịch sử giao dịch) tự host trên EC2: hàng trăm tỷ dòng, gần 20 TB, 4 node shard (2021). HA bằng failover replica đơn giản; backup bằng snapshot đĩa AWS.
• Workload đặc biệt: 99% lượt ghi diễn ra ban đêm (quy trình EOD đối soát dữ liệu từ sở giao dịch), hàng chục triệu dòng mới mỗi ngày. Ngoài giờ giao dịch và ngày nghỉ gần như không có user. Cao điểm 40–50.000 query/giây dù đã có lớp cache phía trước.
• Nhiều năm chỉ chạy một cặp master-replica (32 core, 64 GB RAM) chứa hàng tỷ dòng, tới khi dữ liệu tăng vọt; chuyển sang nhiều shard đầu năm 2020.

Thay đổi chính

• Schema: denormalize hầu hết dữ liệu và dùng materialized view để tránh JOIN trên bảng tỷ dòng; bỏ foreign key để bulk import EOD nhanh hơn; bỏ ID tự tăng (tiết kiệm đĩa, insert nhanh hơn).
• Partition theo tháng: thử theo ngày thì insert nhanh (luôn đổ vào partition rỗng) nhưng query theo khoảng ngày chậm hẳn. Theo tháng hợp vì user thường xem báo cáo trong một tháng. Insert ngày đổ vào bảng tạm, phục vụ query ngày hôm sau từ đó, rồi đồng bộ bất đồng bộ vào bảng chính.
• Vacuum: tắt autovacuum vì nó có thể chạy giữa lúc bulk insert và khoá mọi thứ; thay bằng VACUUM ANALYZE theo cron trên partition thay đổi nhiều, như một bước của EOD.
• Tuning: work_mem khoảng 600 MB (cao, vì sort/JOIN lớn; nhân với max_connections có thể làm sập máy nếu đặt sai); shared_buffers tăng dần theo EXPLAIN ANALYZE tới khoảng 1/3 RAM; parallel worker bằng số core để gom dữ liệu nhiều partition.
• Sharding bằng postgres_fdw, theo năm tài chính: loại Citus vì phải thêm cột reference ID vào mọi bảng và viết lại hàng trăm query. Cách làm: snapshot EC2 của primary thành instance mới (kèm failover replica) → nối fdw → xoá partition năm cũ trên primary và thay bằng foreign table trỏ sang shard → xoá partition năm hiện tại trên shard. Xong trong dưới hai giờ, không đổi schema. Mỗi năm làm lại; dữ liệu cũ dời sang máy yếu hơn và rẻ hơn. Nhược: tổng hợp qua mạng chậm hơn, không song song chéo node.
• Postgres làm hot cache (sql-jobber): thay vì Redis (khó lọc/sắp xếp hàng chục loại báo cáo, RAM tăng mãi), mỗi query tới DB lớn ghi kết quả vào một bảng mới trong một Postgres cache. Mọi thao tác lọc/sort sau đó chạy trên bảng này. Hàng đợi job còn giới hạn số query đánh vào DB lớn. Cuối ngày cache có hàng chục triệu bảng; xoá và tạo lại mỗi đêm. Cache DB tắt fsync, WAL, replication; work_mem thấp.

Vì sao (theo Zerodha)

• Một shard duy nhất sẽ dẫn tới: vacuum và import đêm chậm không chịu nổi (đã thấy dấu hiệu); DB quá lớn để hồi phục sau crash/restart; dữ liệu năm cũ ít dùng vẫn chiếm tài nguyên như dữ liệu nóng.
• "97,42%" vấn đề RDBMS là do index và vacuum kém; dữ liệu chưa vượt vài TB thì chưa cần multi-shard. Theo tác giả, Postgres ổn định từ 2013, nâng 9→12 không lỗi, trong khi MySQL từng gây lỗi nhất quán, corruption, rò bộ nhớ (ý kiến của Zerodha).

Bài học cho SWE

• Chia theo trục thời gian khi truy cập tập trung vào dữ liệu gần: dữ liệu cũ sang máy rẻ, gần như không đổi query (Sharding và tách database).
• Lịch ghi theo đợt cho phép tắt cơ chế tự động (autovacuum) và tự lên lịch (Chọn database theo workload).
• Cache kết quả theo từng query thành bảng SQL giữ được khả năng lọc/sort. Xoá cả cache mỗi đêm là cách invalidation đơn giản nhất khi dữ liệu chỉ đổi theo ngày (Cache và invalidation).
• Hàng đợi job phía trước DB lớn vừa là cache vừa là backpressure.

Liên quan

• Zerodha 2021
• Query planner và hiệu năng truy vấn
• Sharding và tách database · Cache và invalidation · Chọn database theo workload