จัดการ PostgreSQL Partitioned Tables ได้อย่างมีประสิทธิภาพด้วย Ruby

เราใช้ partitioned tables ใน PostgreSQL database หลักเพื่อ expire ข้อมูลเก่าได้อย่างมีประสิทธิภาพ เนื่องจากการลบข้อมูลจำนวนมากจาก huge table ส่งผลให้ database performance ลดลง ก่อน version 10 PostgreSQL ไม่มี native support สำหรับ partitioned tables เราใช้ pg_partman extension implement partitioning มันทำงานโดยใช้ table inheritance ของ PostgreSQL สร้าง child tables ของ table ที่จะ partition และ triggers เพื่อ insert ข้อมูลเข้า child tables แทน parent table Extension นี้ทำงานดีแต่มีข้อเสีย — ไม่ใช่ option ตอนใช้ Amazon RDS เพราะไม่ supported ตอนนี้ PostgreSQL รองรับ native partitions ผมคิดว่าตอนนี้ควร drop extension นั้น เพื่อให้มี option ใช้ RDS

Use-case การ partition ของเราเรียบง่าย: partition tables ตามเวลา สร้าง partition ใหม่ทุก day, week, หรือ month ขึ้นอยู่กับจำนวน rows ที่อยากเก็บ across partitions และระยะเวลาที่อยาก retain data ทุก partitioned tables มี column created_at ใช้決定 partition ไหนเก็บแต่ละ row ตัวอย่าง table อาจ defined แบบนี้:

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

ถ้าอยาก weekly partitions จะเป็นแบบนี้:

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');

กับ time-based partitioning scheme การลบข้อมูลเก่าง่ายแค่ drop partition หนึ่ง Maintenance ปกติที่ต้องทำคือ สร้าง partitions ใหม่สำหรับ date ranges เมื่อเข้าใกล้ และลบ old partitions ที่มีข้อมูลไม่ต้องการแล้ว เพื่อให้ maintenance ง่ายขึ้น ผมสร้าง pg_partition_manager gem โดยธรรมชาติมัน inspired by experience กับ pg_partman extension ที่خدمเราดีมาก

มาดูวิธีใช้ gem นี้ ด้วย events table และ partitioning scheme ข้างต้น คุณจะสร้าง 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 มักเป็น schema เดียวที่ Rails developers ใช้) period เป็น day, week, หรือ month เลือกได้ว่าอยาก pre-create tables กี่ตัวหลัง current period ด้วย premake และอยาก retain tables กี่ตัวก่อน current period ด้วย retain gem default pre-create 4 tables ถ้าไม่ specify premake และ default retain data 7 days, 4 weeks, 6 months ถ้าไม่ specify retain

Invoke script/task นี้ด้วย daily cron job เสร็จ — มันจะสร้างและ drop tables ตามต้องการ

ActiveRecord queries ทั้งหมดทำงานเหมือนกับ non-partitioned table เลย ไม่ต้องเปลี่ยนโค้ดอะไร นั่นคือ Event.create, Event.where ฯลฯ จะทำงานเหมือนเดิม PostgreSQL จะวางข้อมูลใน partition ที่ถูกต้องตอน insert ถ้ามีข้อมูลเยอะ อาจสังเกตว่าเวลา include created_at ใน queries PostgreSQL ไม่ต้อง scan ทุก partitions — แค่ partitions ที่ cover range ใน where clause

สรุป ถ้ามี time-based data เยอะที่อยากลบเมื่อหมดอายุ ใช้ PostgreSQL partitioned tables และ pg_partition_manager gem ทำให้แอปมีความสุข :)