Quản Lý Hiệu Quả PostgreSQL Partitioned Tables Bằng Ruby

Chúng tôi dùng partitioned tables trong primary PostgreSQL database để efficiently expire old data, vì xóa dữ liệu từ huge table có thể làm database performance suffer. Trước version 10, PostgreSQL không native support partitioned tables, nên dùng pg_partman extension implement partitioning. Nó dùng table inheritance tạo child tables của table to be partitioned và triggers insert data vào child tables thay vì parent. Extension worked well nhưng downside — không option khi dùng Amazon RDS, vì không supported. Giờ PostgreSQL có native partitions, tôi figured now time drop extension để có option dùng RDS.

Partitioning use-case đơn giản: partition tables based on time, tạo new partition mỗi day, week, hoặc month, depend trên rows muốn store across partitions và retention period. Tất cả partitioned tables có column created_at dùng determine partition store each row. Ví dụ table defined như:

create table events (
  project_id integer,
  data jsonb,
  created_at timestamp
) partition by range (created_at);

Muốn weekly partitions, trông như:

create table events_p2019_10_28 partition of events
  for values from ('2019-10-28') to ('2019-11-04');
create table events_p2019_11_04 partition of events
  for values from ('2019-11-04') to ('2019-11-11');

Với time-based partitioning, delete old data = drop partition. Regular maintenance: create new partitions cho date ranges khi approach và delete old partitions chứa data không muốn. Để maintenance easier, tạo pg_partition_manager gem. Naturally, inspired by pg_partman extension served well.

Xem cách dùng gem, given events table và partitioning scheme. Tạo script/rake task:

require "pg_partition_manager"
PgPartitionManager::Time.process([{
  parent_table: "public.events",
  period: "week",
  premake: 1,
  retain: 3
}])

parent_table defined as schema.table_name (public, default schema, often only one Rails developers dùng). period = day, week, month. Chọn tables pre-create (after current period) với premake, tables keep (prior current period) với retain. Gem defaults pre-create 4 tables nếu không specify premake, retain data 7 days, 4 weeks, 6 months nếu không specify retain.

Invoke script/task daily cron job, all set — create/drop tables as needed.

All ActiveRecord queries act như non-partitioned table, nothing change in code. Event.create, Event.where, etc., work như luôn, PostgreSQL put data right partition khi insert. One change notice nếu lots data: include created_at trong queries, PostgreSQL không scan all partitions — chỉ ones cover range trong where clause.

Tóm lại, nếu có nhiều time-based data muốn delete khi expire, dùng PostgreSQL partitioned tables và pg_partition_manager gem để app happy. :)