Time-Series Databases — InfluxDB & TimescaleDB

Time-series database — সেন্সর, IoT, metrics: কোটি-কোটি timestamped point-এর জন্য optimized engine

Read: ~40 min Intermediate 10 practice problems Live SQLite analog

1. The Day Your Postgres Table Hit a Billion Rows

Imagine a Bangladesh garment factory in Gazipur with 5,000 IoT sensors — temperature, humidity, machine vibration, electricity draw — sampling once a second. That is 432 million rows per day, every day, forever. Your dashboard wants the average temperature per hour for the last 30 days. A naive Postgres table with a B-tree on timestamp would still work — until your SELECT needs to scan tens of billions of rows and your write throughput melts.

A time-series database (TSDB) is purpose-built for exactly this: append-mostly writes, chronological order, automatic partitioning by time, columnar compression, fast time-range queries, and built-in downsampling and retention.

Time-series data কী? প্রতিটি row-এর সাথে একটি timestamp যুক্ত — যেমন প্রতি সেকেন্ডে একটি sensor-এর reading, প্রতি মিনিটে server-এর CPU usage, প্রতি tick-এ একটি stock-এর দাম। এই data প্রায়ই append-only — পুরোনো row সাধারণত update হয় না, শুধু নতুন row যুক্ত হয়। সাধারণ Postgres এই pattern handle করতে পারে, কিন্তু TSDB এর জন্য বিশেষভাবে optimize করা।

In this module: what makes time-series special; InfluxDB (line protocol, tags vs fields, Flux, retention policies); TimescaleDB (Postgres extension, hypertables, continuous aggregates, compression, drop_chunks); real use cases — IoT, server metrics with Prometheus, financial ticks; comparing a TSDB against plain Postgres + B-tree; downsampling strategies; and a runnable SQLite analog so you can feel the patterns yourself.

2. What Makes Time-Series Workloads Special

PropertyWhat it meansWhy a TSDB exploits it
Append-onlyNew points arrive at the latest time; old points are rarely updated.Indexes never need rebalancing for inserts; storage is a giant log.
Chronological orderData arrives roughly in time order.Disk writes become sequential; SSDs love this.
Range queries dominate"Show me the last 1 hour / 24 hours / 30 days."Partition by time — most queries hit one or two partitions.
RetentionOld data is dropped wholesale ("keep 90 days then delete").Drop entire time partitions instantly; no row-by-row delete.
DownsamplingOld data is rolled up to coarser granularity (1s → 1m → 1h → 1d).Pre-compute and store these rollups; queries become tiny.
Compression friendlyValues often change slowly; timestamps are sequential.Delta-of-delta encoding (Gorilla) gives 10–30× compression.
Time-series data-র ছয়টি বৈশিষ্ট্য — append-only, chronological, range query, retention, downsampling, compressible। এই ছয়টি বৈশিষ্ট্যকে কাজে লাগিয়েই TSDB সাধারণ relational DB-এর চেয়ে অনেক দ্রুত হয়। উদাহরণ: "৯০ দিনের পুরোনো data মুছে দাও" — Postgres-এ DELETE দিলে long-running, vacuum-এ পরে cleanup করতে হয়; TSDB-তে পুরো partition একবারে drop হয়, মিলিসেকেন্ডে।

3. InfluxDB — Line Protocol, Tags, Fields, Flux

InfluxDB is a purpose-built TSDB. Data is written using a compact text format called the line protocol:

metrics.txt
# Line protocol format:
#   measurement,tag_set field_set timestamp
sensor,line=4,floor=2,factory=gazipur temperature=29.4,humidity=68.1,vibration=0.07 1715337600000000000
sensor,line=4,floor=2,factory=gazipur temperature=29.6,humidity=68.0,vibration=0.08 1715337601000000000
sensor,line=5,floor=2,factory=gazipur temperature=31.2,humidity=72.4,vibration=0.11 1715337601000000000

Three pieces matter:

  • Measurement — like a SQL table name (sensor).
  • Tags — indexed metadata used for filtering and grouping (line, floor, factory). Always strings. Cheap to filter on.
  • Fields — the actual measured values (temperature=29.4, humidity=68.1). Not indexed. Queryable, but expensive to filter on.
Tag বনাম Field — এটিই InfluxDB-এর সবচেয়ে গুরুত্বপূর্ণ design decision। Tag index করা থাকে — তাই WHERE line='4' দ্রুত হয়। কিন্তু Tag-এর cardinality বেশি হলে (যেমন প্রতিটি user-এর id কে tag বানানো — কোটি-কোটি unique value) memory blow up করে। Field index করা হয় না — তাই value-ভিত্তিক filter ধীর। সাধারণ নিয়ম: যেটি দিয়ে আপনি GROUP BY/WHERE করবেন সেটি tag, যে value পরিমাপ করছেন সেটি field।

Querying with Flux

InfluxDB 2.x introduced Flux, a functional data-flow language. Data flows through a pipeline of operations connected with the pipe-forward operator |>:

avg_temperature.flux
// Average temperature per hour for line 4, last 24 hours
from(bucket: "factory")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "sensor" and
                       r.line == "4" and
                       r._field == "temperature")
  |> aggregateWindow(every: 1h, fn: mean)
  |> yield(name: "hourly_avg_temp")

Older InfluxDB 1.x uses InfluxQL, a SQL-like dialect — easier to read, less expressive than Flux:

avg_temperature.influxql
SELECT MEAN("temperature") FROM "sensor"
WHERE  "line" = '4'
  AND  time >= now() - 24h
GROUP BY time(1h);

Retention policies

A retention policy tells InfluxDB how long to keep raw data — for example, "keep 1-second granularity for 7 days, 1-minute for 30 days, 1-hour for 1 year, 1-day forever". Behind the scenes the database drops old shards on a schedule.

4. TimescaleDB — Postgres With Time-Series Superpowers

TimescaleDB takes a different path: it is a Postgres extension. You install it with CREATE EXTENSION timescaledb; and from then on all your favourite Postgres tools keep working — psql, ORMs, JOINs, foreign keys, constraints. The killer feature it adds is the hypertable.

মূল কথা: TimescaleDB একটি Postgres extension — মানে আপনি যেই Postgres database ব্যবহার করছেন সেটিতেই এটি যোগ হয়। SQL একই, JOIN আছে, transaction আছে, foreign key আছে। শুধু time-series table-গুলোকে hypertable বানিয়ে রাখলে Postgres সেগুলোকে সময় অনুযায়ী auto-partition করে chunk-এ রাখে — একটি query চালালে শুধু দরকারি chunk-গুলোই scan হয়।

Hypertable — auto-partitioning by time

From your point of view, a hypertable looks and queries like a single table. Internally Timescale splits it into many chunks (default: one chunk per 7 days), each a real Postgres table. Inserts route to the correct chunk; SELECT with a time predicate prunes irrelevant chunks entirely — no index scan needed.

hypertable.sql
-- Step 1: ordinary Postgres table
CREATE TABLE sensor_reading (
    time        TIMESTAMPTZ      NOT NULL,
    factory     TEXT             NOT NULL,
    line        INTEGER          NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    vibration   DOUBLE PRECISION
);

-- Step 2: turn it into a hypertable, chunked weekly
SELECT create_hypertable('sensor_reading', 'time',
                          chunk_time_interval => INTERVAL '7 days');

-- Step 3: a normal index on the tag columns we filter by
CREATE INDEX ON sensor_reading (factory, line, time DESC);

Continuous aggregates — auto-refreshing materialized rollups

A continuous aggregate is a materialized view that keeps itself up to date incrementally — when new rows arrive in the hypertable, only the affected time buckets are recomputed. This is how you turn billions of raw rows into tiny pre-rolled summaries that dashboards can hit in milliseconds.

continuous_agg.sql
-- Hourly average per factory + line
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', time) AS bucket,
    factory, line,
    avg(temperature) AS avg_temp,
    max(temperature) AS max_temp,
    avg(humidity)    AS avg_humidity,
    count(*)              AS samples
FROM sensor_reading
GROUP BY bucket, factory, line;

-- Refresh policy — keep the rollup at most 5 minutes stale
SELECT add_continuous_aggregate_policy('sensor_hourly',
        start_offset => INTERVAL '2 days',
        end_offset   => INTERVAL '5 minutes',
        schedule_interval => INTERVAL '5 minutes');

Retention & compression

drop_chunks deletes chunks older than a cutoff — instantaneous, since each chunk is its own table. Compression turns older chunks into a columnar, dictionary-encoded format that is typically 10–20× smaller than the row-store version.

retention_compression.sql
-- Drop raw data older than 90 days
SELECT add_retention_policy('sensor_reading',
                            INTERVAL '90 days');

-- Compress chunks older than 7 days
ALTER TABLE sensor_reading SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'factory, line',
    timescaledb.compress_orderby   = 'time DESC'
);
SELECT add_compression_policy('sensor_reading',
                              INTERVAL '7 days');

5. Real-World Use Cases

IoT — Bangladesh garment factories
Floor-by-floor temperature/humidity monitoring; anomaly alerts when machine vibration exceeds threshold; energy-usage analytics.
Server metrics — Prometheus pairing
Prometheus scrapes metrics, optionally remote-writes to TimescaleDB for long-term storage with SQL queryability.
Financial ticks
Stock/FX/crypto price ticks at sub-second resolution; OHLC candle aggregation, real-time analytics.
Vehicle telematics
GPS, fuel, speed and engine telemetry from fleets — Pathao, Uber-style ops dashboards.
Smart-grid metering
Per-household electricity readings every 15 minutes; utility billing and demand-forecasting.
Healthcare telemetry
ICU patient vitals, wearable health-band streams.
একটি বাস্তব দৃশ্য: Gazipur-এ একটি garment factory-তে ৫,০০০ sensor প্রতি সেকেন্ডে data পাঠাচ্ছে। প্রতি ৭ দিন পরপর Timescale একটি নতুন chunk বানাচ্ছে। Floor manager-এর dashboard প্রতি ১৫ সেকেন্ডে "গত ১ ঘণ্টার gড় তাপমাত্রা প্রতি floor-এ" দেখাচ্ছে — এটি raw sensor_reading থেকে নয়, continuous aggregate sensor_hourly থেকে আসছে। তাই dashboard load মিলিসেকেন্ডে। ৯০ দিনের পুরোনো data drop_chunks দিয়ে আপনাআপনি মুছে যাচ্ছে।

6. TSDB vs Plain Postgres + B-tree

Could you not just CREATE INDEX ON sensor_reading (time) on a vanilla Postgres table? Yes — and for tens of millions of rows it works fine. Trouble starts beyond that.

✅ TSDB / Hypertable

  • Time-based partitioning — query prunes whole chunks, not just rows.
  • Inserts always hit the latest chunk — almost zero index churn.
  • Drop old data instantly via drop_chunks.
  • Native compression (10-20× on cold chunks).
  • Continuous aggregates pre-compute rollups.

⚠️ Plain Postgres + B-tree

  • Single giant table — index gets huge, vacuum gets painful.
  • Insert path competes with index B-tree balancing.
  • DELETE + VACUUM for retention is slow and bloats indexes.
  • No native rollup — you write the cron yourself.
  • No columnar compression for cold data.
Practical rule Below ~50 million rows, plain Postgres + a good index is fine. Beyond that, especially if writes are sustained and queries are time-bounded, a TSDB is the right tool. TimescaleDB is the easiest jump from Postgres because the SQL stays the same.

7. Downsampling Strategies — and a Runnable SQLite Analog

Downsampling = keep raw data only for a short window, then progressively aggregate into coarser granularities. A typical pipeline:

  • Raw 1-second points — keep for 7 days.
  • 1-minute averages — keep for 30 days.
  • 1-hour averages — keep for 1 year.
  • 1-day averages — keep forever.
Downsampling-এর মূল বুদ্ধি: পুরোনো second-level data কেউ আর দেখে না; যা দেখে সেটি "গত ১ বছরে প্রতিদিনের gড়"। তাই raw-এর বদলে rollup রাখুন। স্টোরেজ ১০০×, query ১০০× দ্রুত। TimescaleDB-তে এটি continuous aggregate দিয়ে; InfluxDB-তে retention policy + downsampling task দিয়ে।

Below is a runnable SQLite analog — a regular table with a time index, mimicking how a hypertable feels. We insert a tiny stream of factory sensor readings, then write a downsampling query that produces hourly averages. SQLite has no time_bucket, so we use strftime.

downsample_hourly.sql
-- Hourly downsampling — equivalent to TimescaleDB time_bucket('1 hour', ts)
SELECT
    strftime('%Y-%m-%d %H:00:00', ts) AS bucket,
    factory, line,
    COUNT(*)                          AS samples,
    ROUND(AVG(temperature), 2)         AS avg_temp,
    ROUND(MAX(temperature), 2)         AS max_temp,
    ROUND(AVG(humidity), 2)            AS avg_humidity
FROM     sensor_reading
GROUP BY bucket, factory, line
ORDER BY factory, line, bucket;

Run it. The output is the hourly rollup — one row per (hour, factory, line) — exactly what a TimescaleDB continuous aggregate would store. The only differences in real life: Timescale recomputes this incrementally as new data arrives, and stores it in a materialized view that dashboards query directly.

Time-bounded latest-value query — the bread and butter of dashboards

latest_per_line.sql
-- The latest reading per (factory, line)
SELECT r.factory, r.line, r.ts, r.temperature, r.humidity
FROM   sensor_reading r
JOIN (
    SELECT factory, line, MAX(ts) AS latest
    FROM   sensor_reading
    GROUP BY factory, line
) latest_per_line
  ON r.factory = latest_per_line.factory
 AND r.line    = latest_per_line.line
 AND r.ts      = latest_per_line.latest
ORDER BY r.factory, r.line;

In TimescaleDB you would write the same with last(temperature, time) — a built-in aggregate just for "give me the most recent value per group". Tiny syntax sugar, but it makes time-series queries read like prose.

8. Glossary (শব্দকোষ)

TermMeaningবাংলায়
Time-seriesA sequence of timestamped data points, usually append-only.timestamp-যুক্ত data point-এর ধারাবাহিক সিরিজ।
HypertableTimescaleDB abstraction — one logical table, internally partitioned into time-based chunks.logically এক table, internally সময়-ভিত্তিক chunk-এ ভাগ করা।
ChunkAn individual time-range partition inside a hypertable.hypertable-এর ভেতরের একটি time-range partition।
Continuous aggregateAuto-refreshing materialized view of a hypertable rollup.স্বয়ংক্রিয়ভাবে refresh হওয়া rollup materialized view।
Tag (InfluxDB)Indexed metadata column. String-only. Cheap to filter.indexed metadata column — filter ও grouping-এর জন্য।
Field (InfluxDB)Measured value. Not indexed. Numeric or string.পরিমাপ করা মান — index করা থাকে না।
Line protocolInfluxDB's compact write format: measurement,tags fields ts.InfluxDB-এর সংক্ষিপ্ত data ingest format।
FluxInfluxDB 2.x functional pipeline query language.InfluxDB 2.x-এর functional pipeline query ভাষা।
DownsamplingAggregating high-frequency data into coarser intervals over time.উচ্চ-ঘনত্বের data-কে সময়ের সাথে ক্রমশ সংক্ষিপ্ত interval-এ rollup করা।
drop_chunksTimescaleDB function that drops chunks older than a cutoff — instant retention.কোনো cutoff-এর পুরোনো chunk-গুলো instant drop করার TimescaleDB function।

9. Practice Problems

Where shown, the SQL is runnable in the in-page SQLite engine. Try yourself, then check.

যেগুলোতে runnable SQL দেওয়া আছে — সরাসরি browser-এ run করুন। অন্যগুলোতে concept ব্যাখ্যা করুন।
  1. In one sentence, what is the defining feature of a time-series workload?
    এক বাক্যে — time-series workload-এর সংজ্ঞায়ক বৈশিষ্ট্য কী?
    ✨ Show Answer (উত্তর দেখুন)

    Answer: A high rate of append-only writes of timestamped points, queried mostly by time range with aggregations like averages, max, percentiles per time bucket.

    Append-only ভাবে timestamped point-এর বিপুল ingest, এবং সাধারণত time range-ভিত্তিক aggregation query।

  2. In InfluxDB, should the column region (values: "dhaka", "ctg", "syl") be a tag or a field? Why?
    region কি tag হবে নাকি field?
    ✨ Show Answer

    Answer: A tag — it has low cardinality, it is an attribute used for filtering and grouping (WHERE region='dhaka', GROUP BY region), and tags are indexed in InfluxDB. Fields are for the measured numeric values.

    Tag — কারণ এটি filtering/grouping-এর জন্য ব্যবহার হবে এবং cardinality কম। Tag InfluxDB-তে index করা থাকে।

  3. Using the sensor_reading table from §7, write a query for the hourly average temperature for line 4 only.
    শুধু line 4-এর জন্য hourly average temperature বের করুন।
    ✨ Show Answer
    ans3.sql
    SELECT   strftime('%Y-%m-%d %H:00:00', ts) AS hour,
         ROUND(AVG(temperature), 2) AS avg_temp,
         COUNT(*) AS samples
FROM     sensor_reading
WHERE    line = 4
GROUP BY hour
ORDER BY hour;
  4. Why is dropping 90 days of old data with drop_chunks faster than DELETE WHERE ts < now() - INTERVAL '90 days'?
    drop_chunks কেন সাধারণ DELETE-এর চেয়ে দ্রুত?
    ✨ Show Answer

    Answer: drop_chunks drops whole partitions (each is a real Postgres table) — basically a DROP TABLE per chunk, which is metadata-only and instant. DELETE rewrites the table row-by-row, generates WAL, leaves bloat behind, and triggers expensive vacuum + index rebuilds.

    drop_chunks পুরো partition (একটি Postgres table) drop করে — metadata-only operation, instant। DELETE প্রতিটি row touch করে, WAL লেখে, vacuum-এর কাজ বাড়ায়।

  5. Write a query that returns the latest reading per (factory, line) — using only standard SQL.
    প্রতিটি (factory, line)-এর সর্বশেষ reading বের করুন।
    ✨ Show Answer
    ans5.sql
    SELECT r.*
FROM   sensor_reading r
JOIN (
  SELECT factory, line, MAX(ts) AS latest
  FROM   sensor_reading
  GROUP BY factory, line
) lp ON r.factory = lp.factory
   AND r.line    = lp.line
   AND r.ts      = lp.latest;
  6. In TimescaleDB, what does a continuous aggregate save you compared to a plain materialized view?
    Continuous aggregate plain materialized view-এর তুলনায় কী সুবিধা দেয়?
    ✨ Show Answer

    Answer: A regular materialized view recomputes from scratch on every REFRESH. A continuous aggregate is incremental — when new rows arrive, only the affected time buckets are recomputed. This makes it cheap to keep nearly-real-time rollups over hypertables that grow forever.

    Plain materialized view প্রতিবার পুরো recompute করে। Continuous aggregate incremental — নতুন data এলে শুধু affected bucket recompute হয়।

  7. Why is "tag with high cardinality" considered an anti-pattern in InfluxDB?
    InfluxDB-তে high-cardinality tag কেন anti-pattern?
    ✨ Show Answer

    Answer: Tags are indexed and the index is loaded into memory. If you make user_id a tag in a system with millions of users, the in-memory index explodes — known as the "high-cardinality" or "series cardinality" problem — slowing writes and crashing the server. Use fields (or a separate dimension table in TimescaleDB) for high-cardinality identifiers.

    Tag in-memory index-এ থাকে। লাখ-কোটি unique value-যুক্ত কলামকে tag বানালে memory blow up করে। এমন কলামকে field হিসেবে রাখুন।

  8. Sketch a 4-tier downsampling retention plan for IoT sensors at 1 Hz.
    1 Hz IoT sensor data-র জন্য একটি 4-tier downsampling retention plan লিখুন।
    ✨ Show Answer

    Answer:

    • Tier 1 — raw 1-second points: keep 7 days.
    • Tier 2 — 1-minute averages (continuous aggregate): keep 30 days.
    • Tier 3 — 1-hour averages: keep 1 year.
    • Tier 4 — 1-day averages: keep forever.

    Tier 1 raw 1s — 7 দিন; Tier 2 1m gড় — 30 দিন; Tier 3 1h gড় — 1 বছর; Tier 4 1d gড় — চিরকাল।

  9. Name the typical role each of these plays in production: Prometheus, TimescaleDB, Grafana.
    Production-এ Prometheus, TimescaleDB ও Grafana-এর সাধারণ ভূমিকা কী?
    ✨ Show Answer

    Answer: Prometheus scrapes server/app metrics on a short-term local store; TimescaleDB acts as long-term storage (Prometheus remote-writes to it) with full SQL access; Grafana is the dashboard layer, querying both Prometheus and TimescaleDB to render time-series charts and alerts.

    Prometheus = short-term metric scraping; TimescaleDB = long-term SQL-queryable storage (remote_write); Grafana = dashboard ও alerting UI।

  10. When would you NOT pick a time-series database for timestamped data?
    Timestamped data হলেও কখন TSDB ব্যবহার করবেন না?
    ✨ Show Answer

    Answer: When your write rate is modest (a few thousand rows per day), data fits comfortably in a regular Postgres table, queries are not predominantly time-range aggregations, and you frequently update or delete individual past rows. The operational complexity of a TSDB is not justified at small scale; a vanilla Postgres index on timestamp handles it.

    যখন write rate কম, data ছোট, query অধিকাংশ time-range aggregation নয় এবং পুরোনো row নিয়মিত update/delete হয় — তখন সাধারণ Postgres-ই যথেষ্ট।

Summary — Module 49

Time-series workloads are append-mostly, chronological, range-queried, retention-bounded, and very compressible. InfluxDB attacks the problem with a custom engine: line protocol for ingest, tags vs fields, Flux/InfluxQL for queries, and retention policies for retention. TimescaleDB attacks it as a Postgres extension: hypertables auto-partition by time into chunks, continuous aggregates incrementally maintain rollups, drop_chunks handles instant retention, and native compression squeezes cold chunks 10–20×. Use TSDBs for IoT sensors, server metrics, telemetry and ticks. For modest scale, plain Postgres + a B-tree on timestamp is still a fine tool.

TSDB-এর মূল কৌশল — সময় অনুযায়ী auto-partition, পুরোনো chunk drop, columnar compression, continuous aggregate দিয়ে rollup। InfluxDB একটি স্বতন্ত্র engine; TimescaleDB Postgres-এর extension — একই SQL, একই tooling, কিন্তু hypertable + chunk + continuous aggregate। IoT, metrics, finance — যেখানেই "প্রতি সেকেন্ডে কোটি data point" আসে, সেখানেই TSDB-এর আসল মূল্য।

Next Module → Wrapping up Phase 8 — choosing the right engine for the job.

Prev: Elasticsearch Next: Module 50