POSTGRESQL → DUCKDB · ANALYTICAL BRIDGE

เก็บธุรกรรมให้เป็นระบบ แล้วส่ง snapshot ที่มีขอบเขตไปวิเคราะห์

แล็ปนี้ใช้ runtime เดิมของ Data Systems โดยให้ PostgreSQL เป็นแหล่งข้อมูลระบบงาน ส่วน DuckDB รับ snapshot รายเดือนสำหรับงานวิเคราะห์ และทดลองรับ event เดียวกันเพื่อเปรียบเทียบ query

Checking existing PostgreSQL and DuckDB…

NO NEW ENGINE2 pathsmonthly export · dual write

ทำไมจึงใช้ DUCKDB

ฐานข้อมูลเพื่อการวิเคราะห์อยู่ข้างไฟล์ได้ โดยไม่ต้องเพิ่มบริการใหม่

ทำงานแบบ Embedded

DuckDB ทำงานภายใน process ของ application และเปิดฐานข้อมูลหรือ Parquet ในเครื่องได้โดยตรง

เหมาะกับงานวิเคราะห์แบบคอลัมน์

การประมวลผลแบบ vector และไฟล์แบบ column เหมาะกับ scan, join, window และ aggregation

แบ่งหน้าที่ชัดเจน

PostgreSQL ดูแลความจริงของธุรกรรม ส่วน DuckDB ใช้สำเนาที่กำหนดขอบเขตสำหรับการวิเคราะห์

ANALYTICAL SQL DIRECTLY ON FILES

นำ SQL ไปถามข้อมูลในไฟล์ได้โดยตรง โดยไม่ต้อง import เข้า database server อีกตัวก่อน

DuckDB สามารถ query CSV และ Parquet เสมือนเป็นตาราง โดยไฟล์ยังเป็นแหล่งข้อมูล ส่วน DuckDB ทำหน้าที่ filter, join, group by และ window function

CSV / Parquetไฟล์จาก pipeline

→

DuckDB SQLscan · filter · join · aggregate

→

ผลการวิเคราะห์table · report · Parquet

1 · อ่าน CSV โดยตรง

ไม่ต้อง CREATE TABLE หรือ INSERT ก่อนเริ่มถามข้อมูล

SELECT category, count(*) AS orders, sum(revenue) AS revenue
FROM read_csv_auto('sales.csv')
GROUP BY category;

2 · อ่าน Parquet หลายเดือนพร้อมกัน

รูปแบบชื่อไฟล์กลายเป็น relation เดียว

SELECT date_trunc('month', sale_timestamp) AS month,
       region, sum(revenue) AS revenue
FROM read_parquet('monthly/sales-2024-*.parquet')
GROUP BY month, region
ORDER BY month, region;

3 · Join ข้อมูลระหว่างไฟล์

ไฟล์ไม่จำเป็นต้องกลายเป็นตารางถาวรใน DuckDB ก่อน

SELECT s.category, c.segment, sum(s.revenue) AS revenue
FROM read_parquet('sales/*.parquet') AS s
JOIN read_parquet('customers.parquet') AS c
  ON s.customer_id = c.customer_id
GROUP BY s.category, c.segment;

4 · Window function บนข้อมูลจากไฟล์

ยังใช้ analytical SQL ได้ครบ แม้แหล่งข้อมูลเป็น Parquet

SELECT sale_day, revenue,
       sum(revenue) OVER (
         ORDER BY sale_day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_days
FROM read_parquet('daily-sales.parquet');

CSV

ค่าอยู่ในรูปข้อความ DuckDB ต้องแยก delimiter, record และชนิดข้อมูลก่อนวิเคราะห์

2024-01-01,Central,Electronics,1500.00

Parquet

คอลัมน์ ชนิดข้อมูล compressed page, row group และสถิติอยู่ในไฟล์

sale_day │ region │ category │ revenue

Projection pushdown

SELECT region, sum(revenue)

DuckDB อ่านเฉพาะ region และ revenue แทนการอ่านทุกคอลัมน์ได้

Filter pushdown

WHERE sale_day >= DATE '2024-03-01'

สถิติใน Parquet อาจช่วยให้ DuckDB ข้าม row group ที่ไม่มีข้อมูลเดือนมีนาคม

PostgreSQL vs DuckDB

PostgreSQL โดยทั่วไปนำข้อมูลเข้า table ที่มีการกำกับก่อน query ส่วน DuckDB มัก query Parquet ณ ตำแหน่งไฟล์โดยตรง PostgreSQL ใช้ extension หรือ Foreign Data Wrapper ได้ แต่การวิเคราะห์ไฟล์ตรง ๆ เป็น workflow หลักของ DuckDB

ใช้ไฟล์ที่แล็ปนี้สร้างจริง

หลัง Lab 01 ส่งออกข้อมูลเดือนมกราคม ให้ query Parquet ที่ได้โดยตรงแทนการโหลดเข้าตารางอีกครั้ง

SELECT region, category, count(*) AS orders,
       sum(revenue) AS revenue
FROM read_parquet(
  '/var/lib/data-systems-lab/data/pg-2024-01.parquet'
)
GROUP BY region, category
ORDER BY revenue DESC;

ใจความสำคัญ: DuckDB ไม่จำเป็นต้องเป็นเจ้าของข้อมูลในรูปแบบตารางถาวรก่อน จึงจะใช้ SQL วิเคราะห์ได้

PostgreSQLoperational rows

→

ขอบเขตรายเดือน[start, next month)

→

CSV → Parquetportable snapshot

→

DuckDBanalytical SQL

LAB 01 · MONTHLY EXPORT

ส่งออกข้อมูลทีละเดือน แล้ววิเคราะห์จาก snapshot

กรองด้วยช่วงเดือนแบบครึ่งเปิด: รวมวันเริ่มต้น แต่ไม่รวมวันแรกของเดือนถัดไป
ส่งแถวจาก PostgreSQL ไปยังไฟล์ CSV แบบมีขอบเขต
ให้ DuckDB อ่าน CSV และบันทึก snapshot เป็น Parquet แบบบีบอัด
สรุปผลตามภูมิภาคและหมวดสินค้า

WHERE sale_timestamp >= DATE '2024-01-01'
  AND sale_timestamp < DATE '2024-02-01'

COPY pg_monthly_sales
TO 'pg-2024-01.parquet'
(FORMAT PARQUET);

ขนาดข้อมูลเดือน

ยังไม่ได้ส่งออกข้อมูล

LAB 02 · DUAL WRITE + SAME QUERY

เขียน event เดียวลงสองระบบ แล้วเปรียบเทียบงานที่เทียบเคียงกัน

แล็ปจะเพิ่มรายการขายสังเคราะห์เดียวกันลง PostgreSQL และ DuckDB แล้วรัน aggregation รายเดือนเดียวกันแยกจากกัน ให้เทียบความตรงกันของผลลัพธ์ ต้นทุนการเขียน และเวลา query ไม่ใช่ดูเพียงตัวเลข millisecond ที่น้อยที่สุด

ขอบเขตสำคัญ

การทดลองนี้ไม่ใช่ distributed transaction แบบ atomic ระบบจริงควรใช้ outbox, CDC หรือกลไกกู้คืนเมื่อเขียนสำเร็จเพียงระบบเดียว

SELECT region, category, count(*) AS orders,
       round(sum(revenue), 2) AS revenue
FROM sales
WHERE sale_timestamp >= TIMESTAMP '2024-01-01'
  AND sale_timestamp < TIMESTAMP '2024-02-01'
GROUP BY region, category
ORDER BY revenue DESC;

ยังไม่ได้เปรียบเทียบ

จุดที่ควรสังเกต

ข้อสรุปที่มีประโยชน์คือเรื่องสถาปัตยกรรม ไม่ใช่ผลการแข่งขันความเร็ว

ความสดของข้อมูล

snapshot รายเดือนตั้งใจให้ช้ากว่า PostgreSQL จึงต้องบันทึก cutoff และนโยบายสร้างใหม่

ความสอดคล้อง

dual write มีจุดสำเร็จหรือล้มเหลวสองแห่ง ต้องตรวจจำนวนรายการหรือใช้รูปแบบส่งข้อมูลที่กู้คืนได้

การจับเวลาอย่างเป็นธรรม

เวลาของ DuckDB ในแล็ปนี้รวมการเริ่ม process และ cache กับรูปแบบจัดเก็บก็มีผลต่อทั้งสองระบบ

LAB 03 · FIND THE WINNING WORKLOAD

Columnar จะได้เปรียบ เมื่อ workload ใช้ประโยชน์จากมันได้จริง

การทดลองนี้แยกเวลาประมวลผล query ที่ engine รายงานออกจากเวลาเริ่ม process และทำซ้ำ query ละสามครั้ง สองกรณีแรก scan หนึ่งล้านแถว ส่วนสามกรณี dashboard ใช้ผลสรุปขนาดเล็กใน DuckDB

อย่าซ่อนกรณีที่ไม่ชนะ

จุดได้เปรียบควรมาจากการจัดข้อมูลและการใช้ผลวิเคราะห์ซ้ำอย่างเหมาะสม ไม่ใช่จากคำว่า columnar เพียงอย่างเดียว

1,000,000 rows · 3 repetitions

ยังไม่ได้ทดลอง