TimescaleDB — time-series ma'lumotlar

~20 daq · PostgreSQL · Extensions

Server monitoring tizimi qurmoqchisiz — CPU, xotira, tarmoq ma'lumotlari har soniyada yoziladi. Bir oy ichida yuz million qator. Oddiy PostgreSQL da so'rovlar sekinlashadi, disk to'lib boradi. TimescaleDB — vaqt seriyali ma'lumotlar uchun maxsus extension.

TimescaleDB nima?

PostgreSQL extension — vaqt seriyali ma'lumotlar uchun:

Avtomatik partitioning (vaqt bo'yicha)
Siqilgan saqlash (10-20x kam joy)
Vaqt bo'yicha agregatsiya funksiyalari

CREATE EXTENSION IF NOT EXISTS timescaledb;

Hypertable yaratish

Oddiy jadval yaratib, hypertable ga aylantiriladi:

CREATE TABLE metrikalar (
  vaqt    TIMESTAMPTZ NOT NULL,
  server  TEXT,
  cpu     DOUBLE PRECISION,
  xotira  DOUBLE PRECISION
);

-- Hypertable ga aylantirish
SELECT create_hypertable('metrikalar', 'vaqt');

Hypertable — ichida avtomatik partitsiyalanadi (standart: har 7 kun).

Ma'lumot qo'shish

Oddiy INSERT — timescaledb o'zi to'g'ri partitsiyaga yo'naltiradi:

INSERT INTO metrikalar (vaqt, server, cpu, xotira) VALUES
  (NOW(), 'web-01', 45.2, 67.8),
  (NOW(), 'web-02', 12.1, 45.3);

Vaqt bo'yicha agregatsiya

time_bucket — vaqt oralig'i

-- 5 daqiqalik o'rtacha CPU
SELECT
  time_bucket('5 minutes', vaqt) AS daqiqa,
  server,
  avg(cpu)  AS o_rtacha_cpu,
  max(cpu)  AS max_cpu
FROM metrikalar
WHERE vaqt > NOW() - INTERVAL '1 hour'
GROUP BY daqiqa, server
ORDER BY daqiqa DESC;

daqiqa                | server  | o_rtacha_cpu | max_cpu
──────────────────────   ─────────   ─────────────   ────────
2024-01-15 10:05:00   | web-01  | 48.3          | 72.1
2024-01-15 10:00:00   | web-01  | 41.2          | 65.8

Siqish — Compression

Eski ma'lumotlarni siqish:

-- Siqishni yoqish
ALTER TABLE metrikalar SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'server'
);

-- Avtomatik: 30 kundan eski ma'lumotlarni siqish
SELECT add_compression_policy('metrikalar', INTERVAL '30 days');

10-20x kam disk joyi.

Ma'lumotlarni o'chirish — Retention Policy

-- 90 kundan eski ma'lumotlarni avtomatik o'chirish
SELECT add_retention_policy('metrikalar', INTERVAL '90 days');

Continuous Aggregate — real vaqt kesh

Agregatsiya natijalarini saqlash — har so'rovda qayta hisoblash o'rniga:

CREATE MATERIALIZED VIEW soatlik_cpu
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', vaqt) AS soat,
  server,
  avg(cpu)  AS o_rtacha,
  max(cpu)  AS maksimum
FROM metrikalar
GROUP BY soat, server;

-- Avtomatik yangilash
SELECT add_continuous_aggregate_policy('soatlik_cpu',
  start_offset => INTERVAL '3 hours',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

Xulosa

CREATE EXTENSION timescaledb;

-- Yaratish
CREATE TABLE metrikalar (vaqt TIMESTAMPTZ, ...);
SELECT create_hypertable('metrikalar', 'vaqt');

-- Qo'shish — oddiy INSERT
-- O'qish — oddiy SELECT + time_bucket()

-- Optimizatsiya
add_compression_policy('metrikalar', INTERVAL '30 days');
add_retention_policy('metrikalar', INTERVAL '90 days');

-- time_bucket('5 minutes', vaqt) — 5 daqiqalik oraliq

Sensor ma'lumotlari, server monitoring, moliyaviy ma'lumotlar — TimescaleDB uchun ideal.

Keyingi dars

62-dars: Node.js — pg moduli, connection pool, CRUD