Dữ liệu chuỗi thời gian

Metric của server, dữ liệu cảm biến, log sự kiện, lịch sử giá: mỗi dòng là một giá trị đo tại một thời điểm. Loại dữ liệu này có cách dùng rất khác bảng nghiệp vụ, nên thiết kế cũng khác. Trang này giải thích cách chọn khoá, partition theo thời gian để xoá dữ liệu cũ, bảng tổng hợp sẵn, và khi nào nên chuyển sang database chuyên dụng.

Đặc điểm

	Bảng nghiệp vụ (đơn hàng, user)	Chuỗi thời gian
Ghi	Thêm, sửa, xoá từng dòng	Gần như chỉ thêm, theo thứ tự thời gian, liên tục
Đọc	Theo id, theo người dùng	Theo khoảng thời gian, thường là gần đây nhất; gom nhóm (trung bình, max theo phút, giờ)
Dữ liệu cũ	Vẫn quan trọng	Ít giá trị dần, dữ liệu chi tiết chỉ cần giữ vài ngày tới vài tuần
Xoá	Từng dòng, ít	Cả một khoảng thời gian, định kỳ

Ba đặc điểm cuối quyết định thiết kế: index theo thời gian, xoá theo khối thời gian, và giữ dữ liệu theo nhiều tầng.

Khoá chính và index

Query hay gặp nhất là "giá trị của thiết bị X trong khoảng thời gian T", nên khoá chính tự nhiên là (device_id, ts). Khoá này vừa chống trùng một mẫu đo, vừa cho query theo một thiết bị đọc liền một đoạn index.

Trên InnoDB, khoá chính quyết định thứ tự lưu dữ liệu (Clustered index). Với khoá (device_id, ts), dữ liệu của cùng một thiết bị nằm cạnh nhau, nên query theo thiết bị rất nhanh. Nhưng mỗi lần ghi lại chèn vào giữa cây, vì mẫu đo mới của các thiết bị khác nhau rải khắp cây. Nếu query chủ yếu là "mọi thiết bị trong 5 phút gần nhất" thì khoá bắt đầu bằng ts hợp hơn, và mỗi lần ghi chỉ chèn vào cuối cây. Chọn theo query chạy nhiều nhất.

BRIN: index gần như không tốn chỗ

Dữ liệu chuỗi thời gian được ghi theo thứ tự thời gian, nên dòng có ts gần nhau nằm ở các page gần nhau trên đĩa. PostgreSQL có loại index BRIN (Block Range Index) tận dụng đúng điều này. BRIN không lưu từng dòng như B-tree, mà với mỗi nhóm page liền nhau, nó chỉ lưu giá trị nhỏ nhất và lớn nhất của cột. Khi query một khoảng thời gian, PostgreSQL bỏ qua mọi nhóm page có khoảng giá trị nằm ngoài, rồi chỉ đọc vài nhóm còn lại.

Cái lợi là index cực nhỏ. Trên 5 triệu dòng, B-tree theo ts tốn 112 MB, còn BRIN chỉ 25 KB, mà query một giờ dữ liệu vẫn chỉ hơn 1 ms. Cái giá là BRIN chỉ có ích khi thứ tự vật lý khớp với thứ tự của cột. Dữ liệu ghi lộn xộn (nhận muộn, nạp lại từ nơi khác) làm mỗi nhóm page chứa đủ mọi khoảng thời gian, và BRIN gần như vô dụng.

Partition theo thời gian

Xoá dữ liệu cũ là việc định kỳ của mọi bảng chuỗi thời gian. Xoá bằng DELETE … WHERE ts < ? thì database phải xoá từng dòng: ghi undo log, binlog hay WAL cho từng dòng, giữ lock, và với PostgreSQL còn phải chờ VACUUM dọn chỗ. Bảng chia partition theo ngày hoặc tháng thì xoá một khoảng thời gian chỉ là bỏ cả partition (ALTER TABLE … DROP PARTITION trên MySQL, DROP TABLE partition trên PostgreSQL). Thao tác này chỉ xoá file, nên nhanh và gần như không sinh log. Tài liệu PostgreSQL cũng nói bỏ partition nhanh hơn nhiều so với DELETE hàng loạt và tránh hẳn chi phí VACUUM.

-- MySQL: partition theo ngày
CREATE TABLE metrics (
  device_id INT NOT NULL,
  ts        DATETIME NOT NULL,
  value     DOUBLE NOT NULL,
  PRIMARY KEY (device_id, ts)          -- khoá chính phải chứa cột partition
) PARTITION BY RANGE (TO_DAYS(ts)) (
  PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-01-02')),
  PARTITION p20250102 VALUES LESS THAN (TO_DAYS('2025-01-03'))
);

-- PostgreSQL: partition theo ngày
CREATE TABLE metrics (
  device_id INT NOT NULL, ts TIMESTAMP NOT NULL, value DOUBLE PRECISION NOT NULL,
  PRIMARY KEY (device_id, ts)
) PARTITION BY RANGE (ts);
CREATE TABLE metrics_20250101 PARTITION OF metrics FOR VALUES FROM ('2025-01-01') TO ('2025-01-02');

Trên lab, xoá một ngày (200.000 dòng) bằng DELETE mất gần một giây và sinh 3,6 MB binlog trên MySQL, còn DROP PARTITION mất vài chục ms và chỉ sinh vài trăm byte binlog.

Partition còn có lợi thứ hai là partition pruning: query có điều kiện trên cột partition chỉ đọc những partition liên quan. EXPLAIN cho thấy query một ngày chỉ chạm đúng một partition.

Cần một job chạy định kỳ: tạo trước partition cho những ngày sắp tới, và bỏ partition cũ quá thời hạn giữ. Quên tạo partition mới thì lệnh ghi vào ngày chưa có partition sẽ lỗi trên MySQL. Trên PostgreSQL, có thể tạo thêm một partition DEFAULT để hứng những dòng này. Các giới hạn của partition (khoá chính và UNIQUE phải chứa cột partition, query không có cột partition phải quét mọi partition) ở MySQL chương 10.

Bảng tổng hợp và giữ dữ liệu theo tầng

Dashboard hiếm khi cần từng mẫu đo của một năm trước. Nó cần trung bình, max theo giờ hay theo ngày. Vì vậy cách làm phổ biến là giữ dữ liệu theo tầng:

• Dữ liệu thô giữ ngắn, ví dụ 7 ngày, để xem chi tiết khi điều tra sự cố.
• Bảng tổng hợp theo phút giữ vài tháng.
• Bảng tổng hợp theo giờ hoặc ngày giữ nhiều năm.

Bảng tổng hợp (rollup) được một job cập nhật định kỳ bằng upsert, để chạy lại cùng một khoảng thời gian thì ghi đè đúng giá trị chứ không sinh dòng trùng:

INSERT INTO metrics_1h (device_id, hour, n, total, max_value)
SELECT device_id, DATE_FORMAT(ts, '%Y-%m-%d %H:00:00'), COUNT(*), SUM(value), MAX(value)
FROM metrics
WHERE ts >= ? AND ts < ?                -- khoảng vừa đóng, ví dụ giờ vừa qua
GROUP BY 1, 2
ON DUPLICATE KEY UPDATE n = VALUES(n), total = VALUES(total), max_value = VALUES(max_value);

Lưu tổng và số mẫu chứ không lưu trung bình, vì trung bình của các trung bình theo giờ không bằng trung bình theo ngày khi số mẫu mỗi giờ khác nhau. Có tổng và số mẫu thì gộp tiếp lên theo ngày vẫn tính đúng. Trên lab, rollup theo giờ gom 1,8 triệu dòng thô thành 43.200 dòng, và query trung bình theo ngày cho mọi thiết bị nhanh hơn khoảng 28 lần.

Dữ liệu đến muộn (thiết bị mất mạng rồi gửi bù) làm số liệu của một giờ đã tổng hợp bị sai. Cách thường dùng là cho job tổng hợp lại vài khoảng gần nhất mỗi lần chạy, nhờ upsert nên chạy lại không hại gì.

Ước lượng dung lượng khi giữ theo tầng so với giữ dữ liệu thô:

1.000 series (thiết bị × chỉ số)

Mỗi series một mẫu mỗi 10 giây

Giữ dữ liệu thô 7 ngày

Giữ bảng theo phút 90 ngày

Giữ bảng theo giờ 2 năm

8.640.000dòng thô mỗi ngày (100 dòng/giây)

17,9 GBtổng khi giữ theo tầng

486 GBnếu giữ dữ liệu thô suốt 2 năm

Tầng	Số dòng	Dung lượng
Thô, giữ 7 ngày	60.480.000	4,7 GB
Theo phút, giữ 90 ngày	129.600.000	11,7 GB
Theo giờ, giữ 2 năm	17.520.000	1,6 GB
So sánh: giữ thô suốt 2 năm	6.307.200.000	486 GB

Thang log; khoảng 77 byte mỗi dòng thô và 90 byte mỗi dòng tổng hợp, chưa nén.

Khi nào chuyển sang database chuyên dụng

MySQL hay PostgreSQL với partition và rollup đủ cho nhiều hệ thống, nhất là khi dữ liệu chuỗi thời gian cần JOIN với dữ liệu nghiệp vụ. Nên cân nhắc database chuyên dụng khi lượng ghi lên tới hàng trăm nghìn mẫu mỗi giây, khi cần nén mạnh để giữ dữ liệu lâu, hoặc khi việc tự viết job partition và rollup đã thành gánh nặng:

Hệ thống	Là gì	Hợp khi
TimescaleDB	Extension của PostgreSQL. Hypertable tự chia dữ liệu theo thời gian thành các chunk	Muốn giữ SQL và hệ sinh thái PostgreSQL, cần JOIN với bảng nghiệp vụ
ClickHouse	Database lưu theo cột, cho phép đặt TTL để tự xoá dữ liệu cũ	Phân tích trên hàng tỷ dòng, log và sự kiện (Cấu trúc lưu trữ)
InfluxDB	Database chuyên cho chuỗi thời gian	Metric và dữ liệu cảm biến với mô hình measurement, tag, field
Prometheus	Hệ thống giám sát kéo metric định kỳ, có kho chuỗi thời gian cục bộ	Metric vận hành. Kho cục bộ chỉ trên một node, mặc định giữ 15 ngày; muốn giữ lâu thì gửi sang kho khác bằng remote write

Câu trả lời trong phỏng vấn thường là: bắt đầu bằng database đang có, với khoá đúng, partition theo thời gian và rollup. Chuyển sang hệ chuyên dụng khi đo được là cần, và nói rõ cái giá là thêm một hệ thống để vận hành và phải đồng bộ dữ liệu sang đó.

Ghi nhớ

1. Chuỗi thời gian gần như chỉ thêm, đọc theo khoảng gần đây, và dữ liệu cũ mất giá trị dần. Thiết kế theo đúng ba điều đó.
2. Khoá chính thường là (thiết bị, thời gian). Trên PostgreSQL, BRIN cho index theo thời gian gần như không tốn chỗ khi dữ liệu được ghi theo thứ tự.
3. Partition theo thời gian để xoá dữ liệu cũ bằng cách bỏ cả partition thay vì DELETE, và để query chỉ đọc partition liên quan. Cần job tạo partition mới và bỏ partition cũ.
4. Giữ dữ liệu theo tầng: thô ngắn hạn, tổng hợp dài hạn. Bảng tổng hợp lưu tổng và số mẫu, cập nhật bằng upsert để chạy lại được.

Nguồn tham khảo

Đã đối chiếu (đọc nguồn ngày 2026-10-23):

Nguồn	Dùng cho phần
PostgreSQL 17 docs, BRIN Indexes	BRIN dành cho cột có tương quan tự nhiên với vị trí vật lý; lưu tóm tắt (min, max) cho mỗi nhóm page liền nhau
PostgreSQL 17 docs, Table Partitioning	Bỏ partition nhanh hơn nhiều so với DELETE hàng loạt và tránh chi phí VACUUM; khoá chính, UNIQUE phải chứa cột partition
MySQL 8.0 Reference Manual, Partitioning Keys, Primary Keys, and Unique Keys, Management of RANGE and LIST Partitions	Mọi khoá UNIQUE phải chứa mọi cột trong biểu thức partition; DROP PARTITION xoá cả dữ liệu trong partition
TimescaleDB docs, Hypertables	Hypertable tự chia dữ liệu theo thời gian thành chunk
Prometheus docs, Storage	Kho cục bộ không cluster, không replicate, giới hạn ở một node; tích hợp remote storage; mặc định giữ 15 ngày
ClickHouse docs, MergeTree	TTL để xoá dòng hết hạn; mỗi cột một file ở định dạng Wide

Chưa đối chiếu lần này: nén và continuous aggregate của TimescaleDB, mô hình measurement/tag/field của InfluxDB, partition DEFAULT của PostgreSQL (từ kiến thức chung), lỗi khi ghi vào khoảng chưa có partition trên MySQL (từ kiến thức chung).

Số đo chi tiết

Đo bằng script lab/time-series/ ngày 2026-10-23 trên MacBook M2 (MySQL 8.0, PostgreSQL 17 trong Docker), hai lượt. Bảng metrics 10 ngày, mỗi ngày 200.000 dòng (200 thiết bị, mỗi thiết bị một mẫu mỗi 86 giây), khoá chính (device_id, ts).

Partition theo ngày: xoá một ngày và query một ngày

	MySQL 8.0	PostgreSQL 17
EXPLAIN query một ngày	chỉ partition p8	chỉ partition metrics_p8
AVG một ngày, bảng có partition	32–38 ms	13–14 ms
AVG một ngày, bảng không partition	246–288 ms (khoá bắt đầu bằng device_id nên quét cả bảng)	27–28 ms (quét song song)
DELETE một ngày (200.000 dòng)	799–884 ms, 3,6 MB binlog	122–123 ms, 21,7–21,8 MB WAL, chưa tính VACUUM
Bỏ một partition	30–48 ms, 0,2 KB binlog	3–4 ms, 34–42 KB WAL

BRIN so với B-tree (PostgreSQL, 5 triệu dòng, bảng 261 MB)

	Query một giờ (3.600 dòng)	Kích thước index
Không index (tắt quét song song)	126–133 ms
B-tree trên ts	0,6 ms	112 MB
BRIN trên ts	1,3–1,4 ms	25 KB
BRIN, dữ liệu ghi xáo trộn thứ tự	179–305 ms	25 KB

BRIN đọc thừa khoảng 16.500 dòng (Rows Removed by Index Recheck) ngoài 3.600 dòng cần, vì nó chỉ biết nhóm page nào có thể chứa khoảng thời gian, không biết từng dòng.

Bảng tổng hợp theo giờ (MySQL)

Rollup theo giờ của 1.800.000 dòng thô (9 ngày, 200 thiết bị) ra 43.200 dòng, ít hơn 42 lần, tạo trong khoảng 5 giây. Query trung bình và max theo ngày cho mọi thiết bị: 401 ms từ dữ liệu thô, 14 ms từ rollup. Chạy lại job cho ngày cuối không sinh dòng trùng.

Code đã dùng để chạy thử

Chạy bằng npm run lab -- time-series sau npm run lab:up, khoảng 1 phút. Xem Lab.

lab/time-series/partition.mjs: DELETE so với bỏ partition, partition pruning

// Dữ liệu chuỗi thời gian: xoá dữ liệu cũ bằng DELETE so với bỏ cả partition, và partition pruning.
// Bảng metrics 10 ngày, mỗi ngày 200.000 dòng (200 thiết bị × 1.000 mẫu), partition theo ngày, trên MySQL 8.0 và PostgreSQL 17.
import mysql from 'mysql2/promise'
import pg from 'pg'
import { MYSQL, PG } from '../common/config.mjs'
import { ensureDb } from '../common/run-sql.mjs'

const DAYS = 10, PER_DAY = 200_000
const ms = (v) => (v >= 100 ? Math.round(v).toLocaleString('vi-VN') : v.toFixed(1).replace('.', ',')) + ' ms'
const time = async (fn) => { const t0 = performance.now(); const r = await fn(); return [performance.now() - t0, r] }
const day = (d) => `2025-01-${String(d + 1).padStart(2, '0')}`

ensureDb('mysql', 'ts')
ensureDb('pg', 'ts')

// ---------- MySQL ----------
const my = await mysql.createConnection({ ...MYSQL, database: 'ts', multipleStatements: true })
const binlogPos = async () => { const [[s]] = await my.query('SHOW MASTER STATUS'); return [s.File, Number(s.Position)] }
const parts = Array.from({ length: DAYS + 1 }, (_, d) => `PARTITION p${d} VALUES LESS THAN (TO_DAYS('${day(d)}') + 1)`).join(',\n  ')
for (const t of ['metrics', 'metrics_flat']) {
  await my.query(`DROP TABLE IF EXISTS ${t}`)
  await my.query(`CREATE TABLE ${t} (device_id INT NOT NULL, ts DATETIME NOT NULL, value DOUBLE NOT NULL,
    PRIMARY KEY (device_id, ts))` + (t === 'metrics' ? ` PARTITION BY RANGE (TO_DAYS(ts)) (\n  ${parts})` : ''))
}
await my.query('SET SESSION cte_max_recursion_depth = 1000000')
for (let d = 0; d < DAYS; d++) {
  for (const t of ['metrics', 'metrics_flat']) {
    await my.query(`INSERT INTO ${t}
      WITH RECURSIVE s(i) AS (SELECT 0 UNION ALL SELECT i + 1 FROM s WHERE i < ${PER_DAY - 1})
      SELECT i % 200, '${day(d)}' + INTERVAL (i DIV 200) * 86 SECOND, RAND() * 100 FROM s`)
  }
}
const [, plan] = await time(() => my.query(`EXPLAIN SELECT AVG(value) FROM metrics WHERE ts >= '${day(8)}' AND ts < '${day(9)}'`))
console.log(`\n=== MySQL: ${(DAYS * PER_DAY).toLocaleString('vi-VN')} dòng, partition theo ngày ===`)
console.log(`EXPLAIN query một ngày: partitions = ${plan[0][0].partitions}`)
const [tq] = await time(() => my.query(`SELECT AVG(value) FROM metrics WHERE ts >= '${day(8)}' AND ts < '${day(9)}'`))
const [tf] = await time(() => my.query(`SELECT AVG(value) FROM metrics_flat WHERE ts >= '${day(8)}' AND ts < '${day(9)}'`))
console.log(`AVG một ngày: bảng có partition ${ms(tq)}, bảng không partition ${ms(tf)} (khoá chính bắt đầu bằng device_id nên phải quét cả bảng)`)
let [f0, p0] = await binlogPos()
const [tDel, [del]] = await time(() => my.query(`DELETE FROM metrics_flat WHERE ts < '${day(1)}'`))
let [f1, p1] = await binlogPos()
console.log(`DELETE một ngày (${del.affectedRows.toLocaleString('vi-VN')} dòng): ${ms(tDel)}, binlog thêm ${f0 === f1 ? ((p1 - p0) / 1e6).toFixed(1).replace('.', ',') + ' MB' : 'hơn một file'}`)
;[f0, p0] = await binlogPos()
const [tDrop] = await time(() => my.query('ALTER TABLE metrics DROP PARTITION p0'))
;[f1, p1] = await binlogPos()
console.log(`DROP PARTITION một ngày: ${ms(tDrop)}, binlog thêm ${((p1 - p0) / 1000).toFixed(1).replace('.', ',')} KB`)
await my.end()

// ---------- PostgreSQL ----------
const p = new pg.Client({ ...PG, database: 'ts' })
await p.connect()
const walBytes = async () => BigInt((await p.query('SELECT pg_current_wal_lsn() - \'0/0\'::pg_lsn AS b')).rows[0].b)
await p.query('DROP TABLE IF EXISTS metrics, metrics_flat CASCADE')
await p.query('CREATE TABLE metrics (device_id INT NOT NULL, ts TIMESTAMP NOT NULL, value DOUBLE PRECISION NOT NULL, PRIMARY KEY (device_id, ts)) PARTITION BY RANGE (ts)')
for (let d = 0; d < DAYS; d++) await p.query(`CREATE TABLE metrics_p${d} PARTITION OF metrics FOR VALUES FROM ('${day(d)}') TO ('${day(d)}'::date + 1)`)
await p.query('CREATE TABLE metrics_flat (device_id INT NOT NULL, ts TIMESTAMP NOT NULL, value DOUBLE PRECISION NOT NULL, PRIMARY KEY (device_id, ts))')
for (const t of ['metrics', 'metrics_flat']) {
  // Giống phía MySQL: mỗi ngày 200.000 dòng, 200 thiết bị, mỗi thiết bị một mẫu mỗi 86 giây
  await p.query(`INSERT INTO ${t} SELECT i % 200, TIMESTAMP '${day(0)}' + (i / ${PER_DAY}) * INTERVAL '1 day' + ((i % ${PER_DAY}) / 200) * INTERVAL '86 second', random() * 100
    FROM generate_series(0, ${DAYS * PER_DAY - 1}) AS i`)
}
await p.query('VACUUM ANALYZE metrics')
await p.query('VACUUM ANALYZE metrics_flat')
const { rows: pl } = await p.query(`EXPLAIN SELECT AVG(value) FROM metrics WHERE ts >= '${day(8)}' AND ts < '${day(9)}'`)
console.log(`\n=== PostgreSQL: ${(DAYS * PER_DAY).toLocaleString('vi-VN')} dòng, partition theo ngày ===`)
console.log('EXPLAIN query một ngày chỉ quét:', pl.map((r) => r['QUERY PLAN']).filter((l) => /Scan on/.test(l)).map((l) => l.match(/on (\w+)/)[1]).join(', '))
const [pq] = await time(() => p.query(`SELECT AVG(value) FROM metrics WHERE ts >= '${day(8)}' AND ts < '${day(9)}'`))
const [pf] = await time(() => p.query(`SELECT AVG(value) FROM metrics_flat WHERE ts >= '${day(8)}' AND ts < '${day(9)}'`))
console.log(`AVG một ngày: bảng có partition ${ms(pq)}, bảng không partition ${ms(pf)}`)
let w0 = await walBytes()
const [pDel, del2] = await time(() => p.query(`DELETE FROM metrics_flat WHERE ts < '${day(1)}'`))
let w1 = await walBytes()
console.log(`DELETE một ngày (${del2.rowCount.toLocaleString('vi-VN')} dòng): ${ms(pDel)}, WAL thêm ${(Number(w1 - w0) / 1e6).toFixed(1).replace('.', ',')} MB, còn phải chờ VACUUM để lấy lại chỗ`)
w0 = await walBytes()
const [pDrop] = await time(() => p.query('DROP TABLE metrics_p0'))
w1 = await walBytes()
console.log(`DROP TABLE một partition: ${ms(pDrop)}, WAL thêm ${(Number(w1 - w0) / 1000).toFixed(1).replace('.', ',')} KB`)
await p.end()

lab/time-series/brin-rollup.mjs: BRIN và bảng tổng hợp theo giờ

// Hai kỹ thuật cho chuỗi thời gian:
//   1. PostgreSQL: index BRIN so với B-tree trên cột thời gian, khi dữ liệu được ghi theo thứ tự thời gian (5 triệu dòng).
//   2. Bảng tổng hợp theo giờ (rollup) cập nhật bằng upsert, so với tính trên dữ liệu thô (MySQL, bảng metrics_flat của partition.mjs).
import mysql from 'mysql2/promise'
import pg from 'pg'
import { MYSQL, PG } from '../common/config.mjs'
import { ensureDb } from '../common/run-sql.mjs'

const ms = (v) => (v >= 100 ? Math.round(v).toLocaleString('vi-VN') : v.toFixed(1).replace('.', ',')) + ' ms'
const mb = (b) => (Number(b) / 1e6).toFixed(Number(b) < 1e6 ? 3 : 0).replace('.', ',') + ' MB'
async function best(fn, n = 5) { let m = Infinity; for (let i = 0; i < n; i++) { const t0 = performance.now(); await fn(); m = Math.min(m, performance.now() - t0) } return m }

ensureDb('pg', 'ts')
ensureDb('mysql', 'ts')

// ---------- 1. BRIN ----------
const p = new pg.Client({ ...PG, database: 'ts' })
await p.connect()
await p.query('DROP TABLE IF EXISTS readings')
await p.query('CREATE TABLE readings (ts TIMESTAMP NOT NULL, device_id INT NOT NULL, value DOUBLE PRECISION NOT NULL)')
await p.query(`INSERT INTO readings SELECT TIMESTAMP '2025-01-01' + i * INTERVAL '1 second', i % 500, random() * 100 FROM generate_series(0, 4999999) AS i`)
await p.query('VACUUM ANALYZE readings')
const range = `ts >= TIMESTAMP '2025-02-01' AND ts < TIMESTAMP '2025-02-01 01:00'` // một giờ = 3.600 dòng
const q = `SELECT COUNT(*), AVG(value) FROM readings WHERE ${range}`
const size = async (i) => (await p.query(`SELECT pg_relation_size('${i}') AS b`)).rows[0].b
const { rows: [{ b: heap }] } = await p.query(`SELECT pg_relation_size('readings') AS b`)
console.log(`\n=== PostgreSQL: 5.000.000 dòng ghi theo thứ tự thời gian, bảng ${mb(heap)} ===`)
await p.query('SET max_parallel_workers_per_gather = 0')
const tSeq = await best(() => p.query(q))
await p.query('CREATE INDEX readings_btree ON readings (ts)')
const tBtree = await best(() => p.query(q))
const sBtree = await size('readings_btree')
await p.query('DROP INDEX readings_btree')
await p.query('CREATE INDEX readings_brin ON readings USING brin (ts)')
await p.query('ANALYZE readings')
const tBrin = await best(() => p.query(q))
const sBrin = await size('readings_brin')
const { rows: plan } = await p.query(`EXPLAIN (ANALYZE, BUFFERS) ${q}`)
const heapLine = plan.map((r) => r['QUERY PLAN']).find((l) => /Heap Blocks|Rows Removed by Index Recheck/.test(l))
console.log(`Query một giờ (3.600 dòng): không index ${ms(tSeq)}, B-tree ${ms(tBtree)}, BRIN ${ms(tBrin)}`)
console.log(`Kích thước index: B-tree ${mb(sBtree)}, BRIN ${mb(sBrin)}`)
console.log(`BRIN trong EXPLAIN ANALYZE: ${heapLine?.trim()}`)
// Nếu dữ liệu không theo thứ tự thời gian, BRIN gần như vô dụng: xáo trộn rồi đo lại.
await p.query('CREATE TABLE readings_shuffled AS SELECT * FROM readings ORDER BY random()')
await p.query('DROP TABLE readings')
await p.query('ALTER TABLE readings_shuffled RENAME TO readings')
await p.query('CREATE INDEX readings_brin ON readings USING brin (ts)')
await p.query('VACUUM ANALYZE readings')
const tBrinShuf = await best(() => p.query(q))
console.log(`Cùng dữ liệu nhưng thứ tự ghi bị xáo trộn: BRIN ${ms(tBrinShuf)}`)
await p.query('DROP TABLE readings')
await p.end()

// ---------- 2. Rollup theo giờ ----------
const my = await mysql.createConnection({ ...MYSQL, database: 'ts' })
await my.query('DROP TABLE IF EXISTS metrics_1h')
await my.query(`CREATE TABLE metrics_1h (device_id INT NOT NULL, hour DATETIME NOT NULL,
  n INT NOT NULL, total DOUBLE NOT NULL, max_value DOUBLE NOT NULL, PRIMARY KEY (device_id, hour))`)
// Job chạy định kỳ: tổng hợp một khoảng thời gian, chạy lại cùng khoảng thì upsert ghi đè đúng giá trị.
// Lưu tổng và số mẫu (không lưu trung bình), để gộp tiếp lên theo ngày vẫn tính đúng trung bình.
const rollup = (from, to) => my.query(`INSERT INTO metrics_1h (device_id, hour, n, total, max_value)
  SELECT device_id, DATE_FORMAT(ts, '%Y-%m-%d %H:00:00'), COUNT(*), SUM(value), MAX(value)
  FROM metrics_flat WHERE ts >= ? AND ts < ? GROUP BY 1, 2
  ON DUPLICATE KEY UPDATE n = VALUES(n), total = VALUES(total), max_value = VALUES(max_value)`, [from, to])
const t0 = performance.now()
await rollup('2025-01-02', '2025-01-11')
const tRoll = performance.now() - t0
await rollup('2025-01-10', '2025-01-11') // chạy lại ngày cuối: không sinh dòng trùng
const [[{ c }]] = await my.query('SELECT COUNT(*) AS c FROM metrics_1h')
const [[{ raw }]] = await my.query('SELECT COUNT(*) AS raw FROM metrics_flat')
const rawQ = `SELECT device_id, DATE(ts) AS d, AVG(value), MAX(value) FROM metrics_flat GROUP BY device_id, d`
const rolQ = `SELECT device_id, DATE(hour) AS d, SUM(total) / SUM(n), MAX(max_value) FROM metrics_1h GROUP BY device_id, d`
const tRaw = await best(() => my.query(rawQ))
const tRol = await best(() => my.query(rolQ))
console.log(`\n=== MySQL: rollup theo giờ của ${raw.toLocaleString('vi-VN')} dòng thô (9 ngày, 200 thiết bị) ===`)
console.log(`Tạo rollup: ${c.toLocaleString('vi-VN')} dòng trong ${ms(tRoll)}, ít hơn ${Math.round(raw / c)} lần`)
console.log(`Trung bình và max theo ngày cho mọi thiết bị: từ dữ liệu thô ${ms(tRaw)}, từ rollup ${ms(tRol)}`)
await my.end()