Geospatial and Time-Series Data Questions

Direct answer

In PostgreSQL, store readings in an append-only table range-partitioned by time (one partition per day), with a composite B-tree on (sensor_id, ts) for per-sensor range scans and a tiny BRIN index on ts for whole-fleet time ranges. Never UPDATE or DELETE raw rows: retention is DETACH plus DROP of a whole partition. Once a day is closed, compact it into a packed layout (one row per sensor per minute holding an array of values), which removes most of the per-row overhead that makes Postgres expensive for time series. At 10,000 events/s this is a comfortable single-primary design (one Postgres instance takes every write; nothing here needs writes spread across multiple database nodes).

Terms: a B-tree is the default sorted index, one entry per row. A BRIN (block range index) stores only the min and max of a column for each range of table pages (128 pages by default), so it is tiny and works when rows are physically stored in roughly time order, which append-only ingest guarantees. Write amplification is the number of bytes the database writes to disk per byte of data you inserted.

Measured cost per row

I measured the per-row costs on PostgreSQL 16 (default configuration, so full-page writes are on) by loading 1,000,000 readings from 1,000 sensors in arrival order:

SET TIME ZONE 'UTC';
DROP TABLE IF EXISTS r_brin, r_btree, r_packed;
-- 1,000 sensors x 1,000 seconds = 1,000,000 readings, arriving in time order
CREATE TABLE r_brin  (sensor_id int NOT NULL, ts timestamptz NOT NULL, value double precision NOT NULL);
CREATE TABLE r_btree (sensor_id int NOT NULL, ts timestamptz NOT NULL, value double precision NOT NULL);
CREATE INDEX r_brin_ts ON r_brin USING brin (ts);
CREATE INDEX r_btree_sensor_ts ON r_btree (sensor_id, ts);
CREATE INDEX r_btree_ts ON r_btree USING brin (ts);
CHECKPOINT;
SELECT pg_current_wal_lsn() AS l0 \gset
INSERT INTO r_brin SELECT (g % 1000) + 1, timestamptz '2026-01-01' + (g / 1000) * interval '1 second', (g % 97) * 0.5
FROM generate_series(0, 999999) g;
SELECT pg_current_wal_lsn() AS l1 \gset
CHECKPOINT;
SELECT pg_current_wal_lsn() AS l2 \gset
INSERT INTO r_btree SELECT (g % 1000) + 1, timestamptz '2026-01-01' + (g / 1000) * interval '1 second', (g % 97) * 0.5
FROM generate_series(0, 999999) g;
SELECT pg_current_wal_lsn() AS l3 \gset
-- packed layout: one row per sensor per minute
CREATE TABLE r_packed AS
SELECT sensor_id, date_trunc('minute', ts) AS minute, array_agg(value ORDER BY ts) AS vals
FROM r_brin GROUP BY 1, 2;
VACUUM ANALYZE r_brin, r_btree, r_packed;
-- dividing by 1e6 converts the total bytes stored into an average bytes-per-row figure, since exactly 1,000,000 rows were loaded
SELECT round(pg_relation_size('r_brin') / 1e6, 1)                  AS heap_b_per_row,
       round(pg_relation_size('r_btree_sensor_ts') / 1e6, 1)       AS btree_b_per_row,
       pg_relation_size('r_brin_ts')                               AS brin_total_bytes,
       round(pg_total_relation_size('r_packed') / 1e6, 1)          AS packed_b_per_reading,
       round(pg_wal_lsn_diff(:'l1', :'l0') / 1e6, 1)               AS wal_b_per_row_brin_only,
       round(pg_wal_lsn_diff(:'l3', :'l2') / 1e6, 1)               AS wal_b_per_row_with_btree;

Run with psql -f (the \gset lines are psql commands). Output:

 heap_b_per_row | btree_b_per_row | brin_total_bytes | packed_b_per_reading | wal_b_per_row_brin_only | wal_b_per_row_with_btree
----------------+-----------------+------------------+----------------------+-------------------------+--------------------------
           52.2 |            42.1 |            24576 |                  9.5 |                    80.2 |                    166.0

What it shows:

• A reading carries 20 bytes of data (4 + 8 + 8) but costs 52 bytes in the heap: each row has a 23-byte header, a 4-byte line pointer (the slot in the page's internal directory that records where that row's bytes start, one per row on the page) and alignment padding.
• The (sensor_id, ts) B-tree adds 42 bytes per row when built during live inserts: that is more than the roughly 20-odd bytes of raw key data (4-byte sensor_id, 8-byte ts, plus a 6-byte pointer back to the heap row) because the generator cycles through all 1,000 sensors before advancing the timestamp, so each insert lands in a different sensor's part of the tree instead of appending to one page at a time. That spreads writes across many leaf pages at once, so pages split and settle around two-thirds full rather than packing tightly, and each index page also carries its own per-page header overhead. The BRIN index for the whole million rows is 24 KB.
• WAL (write-ahead log, the redo log every change is written to first, which also feeds replicas) is 80 bytes per row without the B-tree and 166 with it: the B-tree roughly doubles write volume.
• Packing 60 readings per row costs 9.5 bytes per reading in total. That saving comes from dropping per-row overhead, not from compression: a 60-value array (480 bytes) is below the roughly 2 KB size at which Postgres's TOAST mechanism (The Oversized-Attribute Storage Technique: Postgres's system for moving large column values out of the main row, and by default compressing them, once they cross that threshold) starts compressing values.

Concrete design for 10,000 events/s

Scale the measurements: 10,000 × 86,400 = 864 million rows/day.

CREATE TABLE readings (
  sensor_id int              NOT NULL,
  ts        timestamptz      NOT NULL,
  value     double precision NOT NULL
) PARTITION BY RANGE (ts);

CREATE TABLE readings_2026_03_01 PARTITION OF readings
  FOR VALUES FROM ('2026-03-01 00:00+00') TO ('2026-03-02 00:00+00');

CREATE INDEX ON readings (sensor_id, ts);      -- created on every partition
CREATE INDEX ON readings USING brin (ts);

CREATE TABLE readings_packed (
  sensor_id int         NOT NULL,
  minute    timestamptz NOT NULL,
  vals      double precision[] NOT NULL,       -- one slot per second
  PRIMARY KEY (sensor_id, minute)
) PARTITION BY RANGE (minute);

Partitioning strategy

• Daily partitions: 864 M rows and about 81 GB of heap plus index each, so the current day's partition and index pages being written stay mostly in memory, and queries for "last hour" prune to one partition.
• Create partitions ahead of time (a scheduled job or the pg_partman extension (a Postgres extension that creates and drops time-based partitions on a schedule, so you are not hand-writing CREATE TABLE ... PARTITION OF for every new day)). A missing partition makes inserts fail; a DEFAULT partition hides that but later blocks creating the real partition if it contains matching rows, so alert on rows landing in it rather than relying on it.

Indexing strategy

• (sensor_id, ts) B-tree answers "sensor 42 between 10:00 and 11:00" with one contiguous index range. Add INCLUDE (value) if you want index-only scans (queries Postgres can answer by reading the index alone, never touching the heap table, because every column the query needs, value included, is already stored in the index), at the cost of a bigger index.
• BRIN on ts answers "all sensors, last 5 minutes" for 24 KB per million rows.
• No other indexes on raw partitions: each one adds its own heap-sized share of WAL.

Write path and write amplification

• Insert with COPY or multi-row INSERT batches of a few thousand rows; one transaction per row wastes commit fsyncs (an fsync is the disk flush Postgres must complete before it can tell the client a transaction is safely committed, so committing once per row forces one disk flush per row instead of one per batch).
• Insert-only means no dead tuples (old row versions that UPDATE or DELETE leave behind because Postgres never overwrites a row in place) and no bloat (heap or index pages left partly empty by those dead tuples until vacuumed); set fillfactor = 100 (the percentage of each page Postgres fills before reserving the rest as free space for future in-place updates; 100 means pack pages fully, which is safe because these rows are never updated) and never update.
• Full-page writes (the first change to each page after a checkpoint logs the whole 8 KB page, not just the changed bytes, so crash recovery can rebuild that page without needing the pre-checkpoint disk image) are a big part of WAL volume. A checkpoint is the periodic point where Postgres flushes every modified page to disk so recovery only has to replay WAL written since then. Raising max_wal_size and checkpoint_timeout reduces how often checkpoints happen and so how many full pages are logged, at the cost of longer crash recovery.
• Drop the B-tree from partitions older than today once they are packed: the packed table's primary key serves per-sensor scans with 60× fewer index entries.

Compression

Plain Postgres does not compress small rows. Options, in the order I would use them: (1) the packed layout above, then ALTER TABLE readings_packed ALTER COLUMN vals SET COMPRESSION lz4 (lz4 is a fast, low-overhead compression algorithm; Postgres's older built-in default, pglz, compresses a bit tighter but noticeably slower) and larger arrays (per sensor per hour: 3,600 values, about 28.8 KB, well over the TOAST threshold) so TOAST actually compresses them; (2) the TimescaleDB extension (a Postgres extension purpose-built for time series that automates partitioning and adds this feature), whose columnstore converts old chunks into compressed column segments, grouped per sensor; (3) export cold partitions to Parquet files in object storage (Parquet stores each column's values contiguously and compressed rather than row by row, which suits cheap archival scans over old data much better than row-oriented storage).

Retention: drop and archive

ALTER TABLE readings DETACH PARTITION readings_2026_03_01 CONCURRENTLY;
-- optional archive first: COPY readings_2026_03_01 TO PROGRAM 'gzip > /archive/readings_2026_03_01.csv.gz' WITH (FORMAT csv);
DROP TABLE readings_2026_03_01;

Dropping a partition is a metadata operation. DELETE FROM readings WHERE ts < ... of 864 M rows would write every deleted row to the WAL, leave dead tuples for VACUUM (Postgres's background cleanup process that reclaims space from dead tuples) to clean up, and bloat the indexes.

Trade-offs and pitfalls

• Time zones: use timestamptz. date_trunc('day', ts) follows the session time zone, so partition bounds and daily rollups must use UTC explicitly.
• Out-of-order data from reconnecting sensors lands in older partitions and weakens BRIN's min/max ranges for those pages; batch late data and keep it rare.
• Anti-wraparound VACUUM: Postgres stamps every row with the ID of the transaction that inserted it, and that ID is a counter that would eventually wrap around and make old rows look like they come from the future; VACUUM prevents that by rewriting old rows as "frozen" (marked permanently visible, so their original transaction ID no longer matters). Insert-only tables produce no dead tuples for ordinary cleanup, but they still accumulate rows that need this freezing pass, so they still get vacuumed (PostgreSQL 13 and later trigger autovacuum on inserts too), and that I/O should be budgeted for.
• When to leave Postgres: at 100,000 events/s the same table is 8.1 TB of heap and index per 10 days on one primary. At that point a TimescaleDB columnstore or a columnar engine is the better tool.

Direct answer

For a time-series logging database, range (time-based) sharding wins for time-range queries and for TTL, because a query for "the last hour" touches only the shards that hold that hour, and expiring old data means dropping whole old shards. Hash sharding wins for write balance, because every write lands on a random shard instead of all writes hitting the newest one. I would not pick either alone at billions of rows: I recommend range partitioning on time at the top level (daily partitions) with hash distribution on a source key (host or service) inside each time range. That keeps TTL as a cheap partition drop and spreads the write hot spot.

Range sharding assigns each shard a contiguous interval of the key, here time (shard 1 holds 1 to 2 September, shard 2 holds 2 to 3 September). Hash sharding computes hash(key) mod N or places the key on a consistent-hash ring, which scatters neighbouring keys across shards. TTL (time-to-live) is the rule that deletes data older than some age. Compaction is the background process that merges small files into large ones and physically removes deleted rows.

Comparison on the four named axes

Why those rebalancing fractions. A consistent-hash ring places every node at a point on a circle (a "ring" of hash values from 0 up to some maximum, wrapping back to 0); each node owns the arc running clockwise from the previous node's point to its own. A key is hashed to a point on the same circle and belongs to the first node reached going clockwise from it. Adding a node adds one more point, which only splits the single arc it lands inside: every other node's arc, and every key in it, is untouched. Once the new node joins, there are N+1 points cutting the ring into N+1 arcs, and by symmetry no arc is special, so each one, including the new node's own arc, has an expected length of 1/(N+1) of the ring. Since only the keys in that one arc change hands, the expected fraction of all keys that move is 1/(N+1): with 4 existing nodes, a 5th takes an expected 1/5 = 20%, and every other node keeps everything it had.

Plain hash(key) mod N has no such locality: moving from mod N to mod (N+1) changes almost every key's target node, not just a fifth of them, because the two functions agree only where k mod N and k mod (N+1) happen to coincide. Running that check for keys 0 to 19 against mod 4 and mod 5 makes it concrete:

keys = range(20)
same = [k for k in keys if k % 4 == k % 5]
print(f"stay on the same node under mod4->mod5: {len(same)} of {len(keys)} ({100*len(same)/len(keys):.0f}%)")

stay on the same node under mod4->mod5: 4 of 20 (20%)

16 of the 20 keys, 80%, land on a different node purely from adding one node, which is what "almost every row moves" means in practice; a real deployment growing from 4 shards to 5 under plain mod N would have to physically relocate about 80% of its rows in one operation.

Worked example

Assume 2 billion log rows/day at roughly 100 bytes/row compressed, 30-day retention, 16 nodes.

• Average write rate: 2,000,000,000 / 86,400 = 23,148 rows/s (peaks are higher).
• Storage: 2e9 × 100 B = 200 GB/day, so 6 TB for 30 days.
• Pure range, one shard per day: the whole 23,148 rows/s average lands on one node while the other 15 idle. That is the classic hot-shard failure.
• Pure hash over 16 nodes: each node takes about 1,447 rows/s, but a "last 15 minutes of errors" query must ask all 16 nodes and each one has to scan all 30 days of its data, since nothing about the layout says where "recent" rows are. The tail latency of the query (its response time is set by the slowest handful of requests, not the average or median) is set by the slowest of those 16 full-table scans. Each day, TTL has to delete 2 billion rows spread across 16 nodes, generating tombstones that compaction must rewrite 200 GB of files to purge.
• Composite (recommended): partition by day; within each day, distribute by hash(host) across 16 nodes. Writes spread (about 1,447 rows/s per node). A query scoped to one host and 15 minutes prunes to that host's slice of one day's partition on effectively one node, the best case. A query that is not host-scoped, such as "last 15 minutes of errors across the fleet", still has to ask all 16 nodes, exactly as under pure hash, since every node holds a slice of every host; what the day partition buys there is not fewer nodes but far less data per node: each node scans only today's partition instead of all 30 days, roughly a 30x cut in rows scanned even though the same 16 nodes are still involved. TTL drops yesterday-minus-30's partition on every node, removing 200 GB with no rewrite. Adding a 17th node only needs to own a share of future partitions; old partitions age out on their own in 30 days, so you often never move historical data at all.

Trade-offs and pitfalls

• Partition granularity. Too fine (hourly × host) creates millions of tiny partitions and files, which hurts query planning (the database has to decide, before a query even runs, which of the many small files could hold matching rows) and compaction. Daily is a good default at this volume; hourly only if a single day's partition is too large to manage.
• Late-arriving rows. Logs arrive minutes or hours late. Range partitioning must accept writes into older partitions, so do not make partitions read-only the instant their day ends, and do not run TTL so aggressively that a late row lands in an already-dropped partition.
• Skewed hash keys. Hashing by host works only if hosts are roughly balanced. One chatty service producing 30% of logs will make its hash bucket hot; add a salt (hash of host plus a small random suffix) for known heavy hitters.
• Queries that are not time-bounded ("all logs ever for request ID X") are bad under both schemes. Serve them with a secondary index or a separate lookup table rather than bending the sharding key.
• When pure hash is right: a key-value workload that fetches single records by ID and has no time-range reads and no bulk TTL. That is not a logging workload.

Direct answer

Store both datasets in one columnar store, both partitioned by day and sorted by (host, time) and distributed across nodes by the same hash of host, so every join is local to a node. Extract error events from raw logs at ingest into a narrow table, keep metrics as a 1-minute rollup that stores sum and count (not an average), and turn the "5 minutes before each error" range join into an equi-join on (host, minute) by expanding each error into its 5 preceding minute keys. That replaces an expensive range join with a cheap hash lookup.

Terms: an equi-join matches rows on equal keys, which databases execute with fast hash tables; a range join matches on inequalities ("between t minus 5 minutes and t"), which most engines execute far less efficiently. Downsampled metrics are rolled up into one row per host per interval. Co-location means both tables' rows for the same host live on the same node, so the join never sends data over the network. A hash join is the algorithm that executes an equi-join fast: build an in-memory hash table from one side keyed by the join column, then scan the other side (the probe side) and look each row up in that table instead of comparing it against every row on the other side. A shard is one slice of a distributed table's rows, held on one node.

Design

flowchart LR
  L[Raw logs] --> X[Ingest parser]
  X --> RL[(raw_logs: day, host, ts)]
  X --> EE[(error_events: host, ts, code)]
  M[CPU scrapes 10s] --> R[1-minute rollup job]
  R --> C[(cpu_1m: host, minute, sum, count)]
  EE --> J[Join on host + minute]
  C --> J
  J --> O[Error with avg CPU of prior 5 min]

Storage layout

• raw_logs: semi-structured logs, partitioned by day, sorted by (host, ts), JSON payload kept as a string or map column. Kept for drill-down, not for the correlation join.
• error_events: host, ts, error_code and a few extracted fields. Populated at ingest (or by a materialized view over raw_logs). It is tiny compared with raw logs.
• cpu_1m: host, minute, cpu_sum, cpu_count (optionally cpu_max). Rolled up from 10-second samples.

Indexing and partitioning

• Both tables partition by day and sort by (host, time). A query for a time window prunes to those days, and within them to the hosts involved.
• Shard both by hash(host) with the same function and shard count, so an error's host and that host's CPU rows are on the same node.
• An inverted or token index on raw log text supports ad-hoc "find errors like X" queries, but the correlation join should not depend on it.

Query-time optimizations

• Minute-key expansion: each error produces 5 keys (the 5 whole minutes before its minute); join on (host, minute).
• Weighted average: compute average CPU as sum of cpu_sum divided by sum of cpu_count, which stays correct when a minute has missing scrapes. An average of per-minute averages would be wrong.
• Filter errors first: apply the time window and error-code filters to error_events before expanding, so the join's probe side is small.
• Report coverage: return how many of the 5 minutes were found, so gaps in metrics are visible instead of silently averaging fewer points.

Worked example (executed on ClickHouse 26.9)

CREATE TABLE cpu_1m (
    host      LowCardinality(String),  -- dictionary-encode: few distinct hosts, cheap to filter on
    minute    DateTime('UTC'),
    cpu_sum   Float64,
    cpu_count UInt32
) ENGINE = MergeTree  -- ClickHouse's core storage engine; almost every real table uses it
PARTITION BY toDate(minute)
ORDER BY (host, minute);

CREATE TABLE error_events (
    host       LowCardinality(String),
    ts         DateTime64(3, 'UTC'),
    error_code LowCardinality(String)
) ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (host, ts);

-- 3 hosts, 60 minutes, 6 samples (10 s scrape) per minute;
-- host h averages (10*h + minute_index) % 100 percent CPU in each minute
INSERT INTO cpu_1m
SELECT concat('web-', toString(h)),
       toDateTime('2026-09-01 12:00:00', 'UTC') + m * 60,
       6 * ((10 * h + m) % 100),
       6
FROM (SELECT number AS h FROM numbers(3)) AS hosts
CROSS JOIN (SELECT number AS m FROM numbers(60)) AS minutes;
-- web-1 lost two scrapes at 12:18 (4 samples, each 99 percent)
ALTER TABLE cpu_1m UPDATE cpu_sum = 4 * 99, cpu_count = 4
WHERE host = 'web-1' AND minute = toDateTime('2026-09-01 12:18:00', 'UTC')
SETTINGS mutations_sync = 1;  -- wait for this demo UPDATE to actually apply before the next statement runs

INSERT INTO error_events VALUES
  ('web-0', '2026-09-01 12:10:42.500', 'HTTP_500'),
  ('web-1', '2026-09-01 12:20:05.000', 'OOM'),
  ('web-2', '2026-09-01 12:03:00.000', 'HTTP_503');

SELECT e.host,
       e.ts,
       e.error_code,
       round(sum(c.cpu_sum) / sum(c.cpu_count), 2) AS avg_cpu_prev_5m,
       count()                                     AS minutes_found
FROM
(
    SELECT host, ts, error_code,
           arrayJoin(arrayMap(k -> toStartOfMinute(ts) - toIntervalMinute(k), range(1, 6))) AS minute
    FROM error_events
) AS e
INNER JOIN cpu_1m AS c ON c.host = e.host AND c.minute = e.minute
GROUP BY e.host, e.ts, e.error_code
ORDER BY e.ts
FORMAT PrettyCompactMonoBlock;

Reading the key line: arrayMap(k -> toStartOfMinute(ts) - toIntervalMinute(k), range(1, 6)) takes the error's timestamp, rounds it down to the start of its minute (toStartOfMinute), then subtracts 1, 2, 3, 4 and 5 minutes from it (range(1, 6) is ClickHouse's half-open range, so k runs 1 through 5); arrayJoin then turns that array of 5 minute values into 5 separate output rows, one per key, which is exactly the "each error produces 5 keys" idea from above. The join then matches each of those 5 rows against cpu_1m on (host, minute), an ordinary hash join: ClickHouse builds a hash table from the smaller side and probes it with the expanded error rows.

Output:

   ┌─host──┬──────────────────────ts─┬─error_code─┬─avg_cpu_prev_5m─┬─minutes_found─┐
1. │ web-2 │ 2026-09-01 12:03:00.000 │ HTTP_503   │              21 │             3 │
2. │ web-0 │ 2026-09-01 12:10:42.500 │ HTTP_500   │               7 │             5 │
3. │ web-1 │ 2026-09-01 12:20:05.000 │ OOM        │           37.07 │             5 │
   └───────┴─────────────────────────┴────────────┴─────────────────┴───────────────┘

Checking it by hand:

• web-0 at 12:10:42 uses minutes 12:05 to 12:09, whose averages are 5, 6, 7, 8 and 9, so 7.
• web-1 at 12:20:05 uses 12:15 to 12:19. Minute 12:18 had 4 samples at 99% while the others had 6 samples each at 25, 26, 27 and 29. Weighted: (150 + 156 + 162 + 396 + 174) / 28 = 1,038 / 28 = 37.07. An average of the five minute-averages would give (25 + 26 + 27 + 99 + 29) / 5 = 41.2, overstating CPU because it gives the 4-sample minute the same weight as full minutes.
• web-2 at 12:03 found only 3 minutes because data starts at 12:00; minutes_found = 3 exposes that rather than hiding it.

Scale arithmetic

For 10,000 hosts: cpu_1m holds 10,000 × 1,440 = 14.4 million rows per day, versus 86.4 million rows per day of raw 10-second samples. One million errors per day expand to 5 million probe keys against a 14.4-million-row table that is co-located and sorted by the same key: a routine hash join, with no network shuffle.

Trade-offs and pitfalls

• Minute alignment is an approximation. "5 minutes before" becomes "the 5 whole minutes before the error's minute", so up to 59 seconds of the most recent CPU is excluded. If that matters, add the partial current minute from raw 10-second data, or use a 10-second rollup for the last minute only.
• Clock skew between the log producer and the metrics scraper can shift events relative to metrics; use a common time source and consider widening the window by a skew budget.
• Late metrics: the rollup must be recomputed for recent minutes when late samples arrive, or the join undercounts.
• When not to co-locate: if metrics and logs live in different systems (a Prometheus-compatible TSDB and a log store), the fallback is a query engine (for example Trino, a distributed SQL engine that can query several storage systems directly without copying their data into one place first) that fetches the error list first, then pulls only the relevant hosts and windows from the TSDB, which works but is slower and harder to keep consistent.

Direct answer

A purpose-built TSDB like Prometheus indexes series: an inverted index maps each label pair (such as service="checkout") to a sorted list of the series that carry it, and within each series the data is already in time order, so a time range is just "which chunks overlap". A columnar store like ClickHouse indexes row ranges: data is sorted by a key you choose, and a sparse primary index keeps one entry per block of 8,192 rows (a "granule"), plus optional data-skipping indexes that store small summaries (min/max, a set of values, or a bloom filter, a compact structure that can say a value is definitely NOT in a block but can only ever say it MIGHT be in one, never guaranteeing a match: it produces false positives, flagging a block that turns out not to contain the value, but never a false negative, so it never skips a block that actually does) per block. For time-range aggregations use time partitioning plus a sort key ending in time; for tag lookups use an inverted index (TSDB) or a sort key starting with the tag (columnar); for high-cardinality filters use a bloom-filter skip index in columnar, and keep unbounded values out of TSDB labels entirely.

TSDB: inverted index over series

In the Prometheus block format, the index file has a symbol table (every distinct label string stored once, referenced elsewhere by a small integer ID instead of repeating the text, which keeps the postings lists below compact), a series section (each series' labels plus references to its compressed chunks with their min and max timestamps) and postings: for each label name and value, a monotonically increasing list of series references (each next ID in the list is larger than the last, which is what lets the merge-intersect below run in a single linear pass instead of needing to re-sort). A query like http_requests_total{service="checkout", code="500"} intersects the postings lists. A tiny model of it:

# A tiny inverted index in the style of a TSDB block: label pair -> sorted series IDs
series = {
    1: {"__name__": "http_requests_total", "service": "checkout", "code": "200"},
    2: {"__name__": "http_requests_total", "service": "checkout", "code": "500"},
    3: {"__name__": "http_requests_total", "service": "search",   "code": "500"},
    4: {"__name__": "http_requests_total", "service": "search",   "code": "200"},
    5: {"__name__": "cpu_seconds_total",   "service": "checkout", "code": "none"},
}
postings = {}
for sid in sorted(series):
    for k, v in series[sid].items():
        postings.setdefault((k, v), []).append(sid)   # stays sorted: IDs added in order

def intersect(a, b):
    """Merge-intersect two sorted lists in O(len(a) + len(b))."""
    i = j = 0; out = []
    while i < len(a) and j < len(b):
        if a[i] == b[j]:
            out.append(a[i]); i += 1; j += 1
        elif a[i] < b[j]:
            i += 1
        else:
            j += 1
    return out

q = [("__name__", "http_requests_total"), ("service", "checkout"), ("code", "500")]
result = postings[q[0]]
print(q[0], result)
for pair in q[1:]:
    print(pair, postings[pair])
    result = intersect(result, postings[pair])
print("matching series:", result, series[result[0]])

Output:

('__name__', 'http_requests_total') [1, 2, 3, 4]
('service', 'checkout') [1, 2, 5]
('code', '500') [2, 3]
matching series: [2] {'__name__': 'http_requests_total', 'service': 'checkout', 'code': '500'}

Because the lists are sorted, the intersection is a linear merge. Time is handled at two levels: each block covers a known time span (2 hours when first written, larger after compaction), and each chunk records its min and max time, so a range query only decompresses overlapping chunks.

InfluxDB 1.x and 2.x use the same idea (the TSI, time series index, mapping tags to series). InfluxDB 3 moved to Parquet files on object storage, which puts it closer to the columnar model below.

Columnar: sparse primary index plus skip indexes

This ClickHouse script loads 10 million rows (1,000 hosts, one row per host per second for about 2.8 hours), sorted by (host, ts), with a bloom-filter skip index on the high-cardinality trace_id. Run with clickhouse local --multiquery < file.sql (tested on 24.10):

CREATE TABLE samples (
    -- LowCardinality(String) dictionary-encodes the column: each distinct string is stored once
    -- and referenced by a small integer, which pays off here because only 1,000 distinct hosts
    -- repeat across 10 million rows.
    host     LowCardinality(String),
    trace_id String,
    ts       DateTime('UTC'),
    value    Float64,
    INDEX trace_bf trace_id TYPE bloom_filter(0.01) GRANULARITY 1
) ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (host, ts);

INSERT INTO samples
SELECT concat('host-', toString(number % 1000)),
       hex(cityHash64(number)),
       toDateTime('2026-03-01 00:00:00', 'UTC') + intDiv(number, 1000),
       number % 97
FROM numbers(10000000);

OPTIMIZE TABLE samples FINAL;

SELECT count() AS parts, sum(rows) AS total_rows, sum(marks) AS marks FROM system.parts WHERE table = 'samples' AND active
FORMAT PrettyCompactMonoBlock;

EXPLAIN indexes = 1
SELECT avg(value) FROM samples
WHERE host = 'host-42' AND ts >= '2026-03-01 01:00:00' AND ts < '2026-03-01 02:00:00';

EXPLAIN indexes = 1
SELECT count() FROM samples WHERE trace_id = hex(cityHash64(1234567));

Output, excerpted (... marks omitted lines; in the omitted Min-Max and Partition sections every query reads 1221/1221 granules):

   ┌─parts─┬─total_rows─┬─marks─┐
1. │     1 │   10000000 │  1222 │
   └───────┴────────────┴───────┘
...
      Parts: 1 | Granules: 1
...
        PrimaryKey
          Keys:
            host
            ts
          Condition: and((ts in (-Inf, 1772330399]), and((ts in [1772326800, +Inf)), (host in [\'host-42\', \'host-42\'])))
          Parts: 1/1
          Granules: 1/1221
          Search Algorithm: binary search
...
         Parts: 1 | Granules: 13
...
           PrimaryKey
             Condition: true
             Parts: 1/1
             Granules: 1221/1221
           Skip
             Name: trace_bf
             Description: bloom_filter GRANULARITY 1
             Parts: 1/1
             Granules: 13/1221

Reading it:

• 10,000,000 / 8,192 = 1,220.7, so 1,221 granules; the index has one mark per granule (a mark is the index's one recorded entry per granule: the row number and file offset where that granule starts, letting ClickHouse jump straight to it instead of scanning from the beginning), plus a final mark, hence 1,222. That is the "sparse" part: 1,222 entries index 10 million rows.
• Query 1 (one host, one hour, 3,600 rows): the primary key narrows to 1 of 1,221 granules by binary search on (host, ts). Partition and min-max pruning did nothing here because all data is on one day; on a table with many days they would discard whole days first.
• Query 2 (one trace ID, a value not in the sort key): the primary index cannot help (Condition: true), but the bloom filter rules out all but 13 of 1,221 granules. One granule really contains the value; the others are expected false positives from the 1% rate the index was built with (1% of 1,221 is about 12).

Which structure for which query

Trade-offs

• Inverted index cost grows with series count. Postings and the in-memory head index are proportional to active series, so a label with millions of values degrades the whole TSDB, not just queries on that label.
• Sparse index cost is nearly fixed, but it only helps filters on the leading sort-key columns. A filter on the third sort column after two high-cardinality ones barely prunes; that is why skip indexes exist, and why they must be measured: a bloom filter on a value present in every granule costs space and saves nothing.
• Choosing the sort key is choosing your fast queries. (host, ts) makes per-host queries cheap and "all hosts, last minute" a scan of every host's range; (ts, host) inverts that. Pick the dominant query, and serve the other with a projection (a second, automatically maintained copy of the table stored with a different sort order, so a query that does not match the primary ORDER BY can still hit a sorted copy instead of scanning) or a rollup.

Direct answer

Run the migration as dual-write first, backfill second, cut over last: start writing new data to both InfluxDB and ClickHouse on day one; backfill the 3 years of history month by month while InfluxDB keeps serving dashboards and alerts; validate ClickHouse against InfluxDB with automated aggregate comparisons; move dashboards and then alerts over one group at a time with side-by-side comparison; keep InfluxDB receiving writes for a few weeks after cutover so rollback is a configuration switch. Downtime for readers is zero because InfluxDB remains authoritative until each consumer is switched.

Terms: dual-writing means the collector sends every new point to both databases. Backfill is loading historical data. Cutover is switching consumers to the new system. Rollback is switching them back. InfluxDB's data model: a measurement is roughly a table name (cpu, http_requests); tags are indexed, string-only key/value labels used to filter and group (host=web-0); fields are the actual values recorded (usage=42.1), not indexed; a retention policy is how long a bucket of data is kept before InfluxDB deletes it; line protocol is InfluxDB's plain-text write format, one line per point (measurement,tag=value field=value timestamp).

Tool names used below: Telegraf is InfluxData's collector agent, the thing that scrapes or receives metrics and writes them out (here, to two destinations for dual-write). Kapacitor is InfluxData's alerting and stream-processing engine. InfluxQL and Flux are InfluxDB's two query languages (InfluxQL is SQL-like; Flux is a newer functional language). Grafana is the dashboard and alerting tool most commonly pointed at both InfluxDB and ClickHouse.

Plan

flowchart LR
  A[0 Inventory + schema] --> B[1 Dual-write new data]
  B --> C[2 Backfill history by month]
  C --> D[3 Validate aggregates]
  D --> E[4 Shadow dashboards + alerts]
  E --> F[5 Cut over by group]
  F --> G[6 Soak, then retire InfluxDB]
  F -.rollback.-> R[Point consumers back to InfluxDB]

Phase 0: inventory and schema design

• List measurements, tags, fields, retention policies, continuous queries (InfluxDB's scheduled downsampling), alerting rules (Kapacitor, Grafana alerts) and every dashboard query (InfluxQL or Flux). Every one of these must be rewritten as SQL, and that rewrite is usually the largest effort, not the data copy.
• Measure series cardinality and points per day per measurement.
• Design the ClickHouse table. One tested option is a narrow table (one row per point per field):

CREATE TABLE metrics (
    measurement LowCardinality(String),
    field       LowCardinality(String),
    series_key  String,
    tags        Map(LowCardinality(String), String),
    ts          DateTime64(9, 'UTC') CODEC(DoubleDelta, ZSTD(1)),
    value       Float64              CODEC(Gorilla, ZSTD(1))
) ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (measurement, field, series_key, ts)
TTL toDateTime(ts) + INTERVAL 5 YEAR DELETE;

series_key is the canonical tag string (for example host=web-0,region=eu), InfluxDB's own notion of a series. DateTime64(9) keeps InfluxDB's nanosecond precision. ReplacingMergeTree collapses rows with an identical sort key during merges, which makes re-running a backfill chunk safe. Monthly partitions follow ClickHouse's guidance to partition no finer than month in most cases. Wide tables (one column per field, per measurement) compress and query better for stable schemas; the narrow table handles changing fields without DDL (data definition language: the CREATE/ALTER TABLE statements that change a table's structure, which a wide table needs every time a new field shows up). I would use wide tables for the handful of hot measurements and the narrow table for the long tail.

Phase 1: dual-write

• Add a second output at the collector (for example a second Telegraf output) so every new point goes to both systems. Record the start time T0.
• Monitor write errors and lag on the ClickHouse side separately; a failure there must never block InfluxDB writes.
• Batch inserts (thousands of rows per insert), since ClickHouse performs poorly with many tiny inserts.

Phase 2: backfill history before T0

• Export month by month. InfluxDB 2.x: influxd inspect export-lp --bucket-id <id> --engine-path <path> --start <RFC3339> --end <RFC3339> --output-path <file> --compress. InfluxDB 1.x provides influx_inspect export for the same job.
• Transform line protocol into rows: split fields into rows (narrow) or columns (wide), build series_key by sorting tag keys, convert timestamps to UTC.
• Load oldest first. Make every chunk idempotent (safe to run more than once with the same result, so a retry after a partial failure never double-counts data): if a month fails validation, drop that month's partition (ALTER TABLE metrics DROP PARTITION 202310) and reload it.
• End the backfill at T0 exactly, so backfill and dual-write neither overlap nor leave a gap. A small overlap is harmless because ReplacingMergeTree deduplicates identical keys at merge time (query with FINAL, a ClickHouse keyword that forces deduplication at query time for rows the background merge has not caught up with yet, or aggregate so pending duplicates are not double counted).

Phase 3: validation

For each measurement, field and hour, compare count, sum, min and max between the two systems, and diff the results. For example, on ClickHouse:

SELECT measurement, toStartOfHour(ts) AS hour, count() AS points, round(sum(value), 3) AS sum_v,
       min(value) AS min_v, max(value) AS max_v
FROM metrics
WHERE ts >= '2023-10-01' AND ts < '2023-11-01'
GROUP BY measurement, hour ORDER BY measurement, hour;

Run the equivalent InfluxQL (SELECT count(v), sum(v), min(v), max(v) ... GROUP BY time(1h)) and flag any hour where the counts differ or sums differ beyond floating-point tolerance. Also spot-check a random sample of raw points per month, and compare the latest hours continuously during dual-write.

Phase 4: side-by-side

• Rebuild each dashboard as a copy pointing at ClickHouse, and view both versions next to each other for a week.
• Run translated alert rules in shadow mode: evaluate them on ClickHouse, log what would fire, but do not page. Compare against the real InfluxDB alerts. Alerts go last, because a missed page is the costliest failure.

Phase 5: cutover, with rollback

• Switch dashboards group by group, then alerts. Each switch is a data-source change in Grafana (or the alert manager), so rollback is the same change reversed.
• Keep dual-write running for 2 to 4 weeks after the last switch, so InfluxDB stays current and rollback loses nothing.

Phase 6: retire

• After the soak period (the diagram's step 6: the 2 to 4 weeks of dual-write from Phase 5, run so a late-discovered problem still has a rollback path with fresh data), stop writes to InfluxDB, keep a final snapshot in object storage for the retention period, then decommission.

Worked sizing

Assume 20,000 series (field-level) at a 60-second interval, 3 years:

• Rows: 20,000 × 1,440 per day × 1,095 days = 31.5 billion rows; one month is 20,000 × 1,440 × 30 = 864 million rows.
• Load time at a sustained 200,000 rows/s: 31.5e9 / 200,000 / 3,600 = 43.8 hours; at 500,000 rows/s, 17.5 hours. The rate is an assumption: measure it on a one-month pilot before promising a timeline, because export and transform often bottleneck before ClickHouse inserts do.
• Since InfluxDB serves all reads during backfill, even a multi-day load has no user impact; throttle it so it does not starve dual-write inserts.

Trade-offs and pitfalls

• Query semantics differ. InfluxQL GROUP BY time() buckets, fill() behaviour and last() semantics must be reproduced in SQL; differences show up as dashboards that look slightly off. The side-by-side phase exists to catch these.
• TTL can silently delete backfilled data. A table TTL shorter than the age of the history (for example 1 year on 3-year-old data) deletes the backfill during merges. Set TTL longer than the oldest data, as above.
• Timestamp precision and time zones: InfluxDB timestamps are nanoseconds since the epoch in UTC; truncating to seconds can create duplicate keys that ReplacingMergeTree then collapses, losing points.
• Type conflicts: InfluxDB allows a field to be an integer in one shard and a float in another; decide the target type up front.
• Continuous queries and retention policies must be recreated as ClickHouse materialized views and TTL rules, or long-range dashboards will query raw data and be slow.