เก็บข้อมูลตามเวลาด้วย TimescaleDB บีบอัด สรุปรายชั่วโมง และเมื่อไหร่ห้ามลบ

ระบบที่รับข้อมูลทุกไม่กี่วินาที เช่น เซนเซอร์ IoT หรือยอดเงินในบัญชีเทรด จะเจอปัญหาเดียวกัน ข้อมูลโตไม่หยุด วันละหลายแสนแถว ผ่านไปไม่กี่เดือนตารางใหญ่จนค้นช้า ดิสก์เต็ม และกราฟย้อนหลังหกเดือนโหลดนาน บทความนี้เล่าว่าระบบบันทึกผลเทรดของเราใช้ TimescaleDB จัดการเรื่องนี้ยังไง ด้วย SQL จริงที่รันอยู่

ทดสอบกับ TimescaleDB บน PostgreSQL 16 (image timescale/timescaledb)

ข้อมูลตามเวลาต่างจากข้อมูลทั่วไปยังไง

ข้อมูลตามเวลามีนิสัยที่คาดเดาได้

• เขียนต่อท้ายเสมอ แทบไม่มีการแก้แถวเก่า
• ค้นเป็นช่วงเวลา เช่น “ยอดเงินของบัญชีนี้ใน 30 วันที่ผ่านมา”
• ของใหม่ถูกใช้บ่อย ของเก่าใช้น้อย และพอเก่ามากก็ต้องการแค่ภาพรวม ไม่ต้องการทุกจุด

ฐานข้อมูลทั่วไปไม่รู้นิสัยนี้ จึงเก็บทุกแถวแบบเดียวกันตลอดไป TimescaleDB ใช้นิสัยนี้ให้เป็นประโยชน์

หลักการ: ข้อมูลแต่ละช่วงอายุมีหน้าที่ต่างกัน ให้ระบบจัดการตามอายุอัตโนมัติ แต่ “จัดการ” ไม่จำเป็นต้องแปลว่า “ลบ”

Hypertable: ตารางที่แบ่งเป็นช่วงเวลาให้เอง

เริ่มจากสร้างตารางแบบปกติ แล้วเปลี่ยนเป็น hypertable

CREATE TABLE equity_ticks (
    account_id  BIGINT      NOT NULL,
    ts          TIMESTAMPTZ NOT NULL,
    balance     NUMERIC(20,5) NOT NULL,
    equity      NUMERIC(20,5) NOT NULL,
    PRIMARY KEY (account_id, ts)
);

SELECT create_hypertable('equity_ticks', 'ts', if_not_exists => TRUE);

จากภายนอกมันยังเป็นตารางเดียว ใช้ SELECT INSERT แบบเดิม แต่ข้างในถูกแบ่งเป็นก้อนย่อยตามช่วงเวลา เรียกว่า chunk ค่าเริ่มต้นคือก้อนละ 7 วัน เวลาค้น “30 วันล่าสุด” ฐานข้อมูลเปิดแค่ก้อนที่เกี่ยว ไม่ต้องไล่ทั้งตาราง และการลบข้อมูลเก่าก็คือการทิ้งทั้งก้อน ซึ่งเร็วกว่าลบทีละแถวมาก

นโยบายที่ทำงานเองตามอายุข้อมูล

1. บีบอัดข้อมูลที่เก่ากว่า 7 วัน

ALTER TABLE equity_ticks SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'account_id'
);
SELECT add_compression_policy('equity_ticks', INTERVAL '7 days', if_not_exists => TRUE);

compress_segmentby = 'account_id' บอกให้จัดกลุ่มข้อมูลของบัญชีเดียวกันไว้ด้วยกันตอนบีบอัด เพราะเราค้นทีละบัญชีเสมอ เลือกคอลัมน์นี้ให้ตรงกับวิธีค้นจริง ข้อมูลตามเวลามักบีบอัดได้ดีมาก เพราะค่าที่อยู่ติดกันคล้ายกัน

2. ตารางสรุปรายชั่วโมง ที่อัปเดตเอง (continuous aggregate)

CREATE MATERIALIZED VIEW equity_1h WITH (timescaledb.continuous) AS
SELECT account_id,
       time_bucket('1 hour', ts) AS bucket,
       first(balance, ts) AS balance_open,
       last(balance, ts)  AS balance_close,
       last(equity, ts)   AS equity_close,
       min(equity)        AS equity_low,
       max(equity)        AS equity_high
FROM equity_ticks
GROUP BY account_id, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('equity_1h',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

ทุกชั่วโมง ระบบจะสรุปข้อมูลช่วง 3 ชั่วโมงถึง 1 ชั่วโมงที่แล้ว เป็นแถวเดียวต่อบัญชีต่อชั่วโมง เก็บทั้งค่าเปิด ค่าปิด ต่ำสุด สูงสุด ไว้วาดกราฟและหา drawdown ระยะยาวได้ ส่วนชั่วโมงล่าสุดยังไม่สรุป (end_offset) เพราะข้อมูลอาจยังเข้ามาไม่ครบ

3. ลบข้อมูลเก่า (มีให้ใช้ แต่เราเลือกไม่ใช้)

SELECT add_retention_policy('equity_ticks', INTERVAL '180 days');

คำสั่งนี้ลบข้อมูลที่เก่ากว่า 180 วันทิ้งทั้งก้อน เหมาะกับข้อมูลที่หมดค่าเมื่อเก่า เช่น log หรืออุณหภูมิห้องเซิร์ฟเวอร์ แต่ระบบของเราเป็น ระบบยืนยันผลเทรด ประวัติทุกจุดคือหลักฐาน จึงตัดสินใจ ไม่ลบเลย แล้วใช้การบีบอัดกับตารางสรุปช่วยเรื่องพื้นที่แทน เรื่องเต็มอยู่ในหัวข้อถัดไป

เรื่องจริง: นโยบายลบที่เกือบทำงาน

ตอนเริ่มระบบ เราตั้งนโยบายลบข้อมูลดิบที่ 180 วันไว้ คิดว่าพอเก่ากว่านั้น กราฟก็ไปอ่านตารางสรุปแทน ตอนเขียนบทความนี้เราไล่โค้ดดูอีกรอบ แล้วพบว่า

• กราฟยอดเงินและการคำนวณสถิติ ยังอ่านจากข้อมูลดิบทั้งหมด ไม่มีส่วนไหนอ่านตารางสรุปเลย
• ยอดเงินเริ่มต้นสำหรับคิดเปอร์เซ็นต์กำไร ดึงจาก แถวแรกสุด ของข้อมูลดิบ
• ข้อมูลเก่าสุดในฐานข้อมูลมีอายุ 183 วันแล้ว

แปลว่าอีกไม่นาน กราฟจะเริ่มหายจากด้านซ้าย และเปอร์เซ็นต์กำไรจะเปลี่ยนเอง โดยไม่มี error ใด ๆ เจ้าของระบบตัดสินทันทีว่า ห้ามลบ เราจึงเอานโยบายออกด้วยคำสั่งเดียว

SELECT remove_retention_policy('equity_ticks');

แล้วตรวจว่ายังไม่มีข้อมูลจริงหายไป (บัญชีจริงเริ่มเก็บเมื่อ 4 เดือนก่อน ยังไม่ถึง 180 วัน) และลบคำสั่งนี้ออกจากสคริปต์สร้างฐานข้อมูล ไม่ให้กลับมาตอนติดตั้งใหม่

นโยบายลบเป็นโค้ดที่ทำงานเงียบ ๆ ในอนาคต ตั้งวันนี้ แต่ผลมาตอนที่ไม่มีใครจำได้แล้วว่าตั้งไว้

คำนวณดูว่าประหยัดแค่ไหน

สมมติบัญชีหนึ่งส่งยอดเงินทุก 5 วินาที ตลอด 24 ชั่วโมง

	จำนวนแถว
ข้อมูลดิบ ต่อวัน	86,400 ÷ 5 = 17,280
ข้อมูลดิบ 180 วัน	17,280 × 180 = 3,110,400
ตารางสรุป 180 วัน	24 × 180 = 4,320

กราฟหกเดือนที่อ่านจากตารางสรุป อ่านแค่ 4,320 แถว แทนที่จะเป็นสามล้านแถว เล็กกว่า 720 เท่า

ไม่เขียนสิ่งที่ไม่เปลี่ยน

การบีบอัดช่วยได้ แต่ดีกว่านั้นคือไม่เขียนแต่แรก บัญชีที่ไม่มีไม้เปิดอยู่ ยอดเงินไม่ขยับทั้งคืน ถ้าเขียนทุก 5 วินาทีก็ได้แถวซ้ำกันหลายพันแถว

ระบบของเราจึงเก็บ “ลายเซ็น” ของค่าล่าสุดไว้ใน Redis (ยอดเงิน equity margin จำนวนไม้ ต่อกันเป็นข้อความ) ถ้าค่าใหม่เหมือนเดิม จะข้ามการเขียน แต่ยังเขียนอย่างน้อยทุก 5 นาทีเป็นจังหวะยืนยันว่าบัญชียังมีชีวิต และอัปเดตเวลาที่เห็นล่าสุดทุกครั้ง ผลคือข้อมูลช่วงเงียบหายไปเกือบหมด โดยกราฟยังเหมือนเดิม

สิ่งที่ต้องระวัง

• ถามก่อนว่าข้อมูลนี้ลบได้จริงไหม log ลบได้ หลักฐานทางการเงินหรือข้อมูลที่ลูกค้าเชื่อถือ ไม่ควรลบอัตโนมัติ
• ถ้าจะตั้งลบ ต้องย้ายที่อ่านด้วย ทุก query ที่อ่านย้อนหลังไกลต้องไปอ่านตารางสรุปก่อนถึงวันที่นโยบายเริ่มลบ
• ค่าที่ใช้คำนวณตลอดอายุ ต้องเก็บแยก เช่น ยอดเงินเริ่มต้น ถ้าดึงจากแถวแรกของข้อมูลดิบ พอแถวแรกถูกลบ ตัวเลขจะเปลี่ยน
• บีบอัดเฉพาะสิ่งที่ไม่แก้แล้ว การแก้ข้อมูลในก้อนที่บีบอัดแล้วทำได้ยากกว่า ตั้งระยะบีบอัดให้เลยช่วงที่ข้อมูลอาจถูกส่งซ้ำหรือแก้ไข
• ตารางที่ต้องเก็บถาวร อย่าทำเป็น hypertable ที่มีนโยบายลบ ประวัติการเทรดของเราเป็นตารางธรรมดา เพราะต้องเก็บตลอดไปและต้องเป๊ะ

ข้อผิดพลาดที่เจอบ่อย

• ใช้ TIMESTAMP แทน TIMESTAMPTZ แล้วเจอปัญหาเขตเวลาเมื่อข้อมูลมาจากหลายประเทศ
• ลืมใส่คอลัมน์เวลาใน primary key ซึ่ง hypertable บังคับให้ index ที่เป็น unique ต้องมีคอลัมน์เวลาด้วย
• สร้าง continuous aggregate แล้วไม่ใส่ policy มันจะไม่อัปเดตเอง
• ตั้งนโยบายลบโดยไม่บอกทีม แล้วมีคนมาถามว่าข้อมูลปีที่แล้วหายไปไหน