What you'll learn: By the end of this lesson you will be able to explain what makes time-series data different, describe how a time-partitioned table, the Prometheus local store and InfluxDB 3 each organise it, set a retention policy and a downsampling rule that do not undo each other, define series cardinality and spot a label that will blow it up, and decide which ShopStream data (courier location pings, service metrics) belongs in which store.
A ship's captain writes the position in the logbook every hour. Last week's pages stay in the cabin. Last year's pages are summarised into one line per day and the originals go to the archive. Nobody tears a single entry out of the middle of a page, and nobody asks for "the entry made by whoever sailed on a Tuesday" without giving a date range.
A time-series database is that logbook for machines. Every row is a measurement stamped with a time, new rows arrive in time order, old rows are almost never edited, questions are asked about ranges of time, and the value of old detail fades. The overview of why those properties matter is in storage engines, and the choice among ShopStream's stores is in the Stage 3 data model lesson. This lesson goes into three real systems and the three decisions every design needs: retention, downsampling and cardinality.
Verified against the Prometheus documentation, the TimescaleDB documentation (published by Tiger Data), the InfluxDB 3 Core and InfluxDB v2 documentation and the PostgreSQL 18 manual, all read on 2026-11-08. The SQL on this page was not run here.
The core idea
Treat time as the primary dimension, and let age decide the cost. Partition storage by time so old data leaves in whole pieces. Keep full detail for a short window and keep summaries for a long one. Keep the number of distinct series small enough to track.
Three kinds of example run through the lesson, each labelled:
- Minimal example: one Prometheus metric with two labels and a recording rule.
- Realistic example: ShopStream's courier location pings in a time-partitioned PostgreSQL table, with a retention policy and an hourly rollup.
- Failure example: a retention policy that deletes the raw data a rollup was still refreshing from, and a label that creates a series per order.
What the data looks like
Each system names the same shape differently:
| System | Unit of data | What identifies a stream | Time |
|---|---|---|---|
| Prometheus | A sample: "a float64 or native histogram value" | "Every time series is uniquely identified by its metric name and optional key-value pairs called labels" | A millisecond-precision timestamp |
| InfluxDB 3 Core | A row in a table | Tags: "unique identifying information like sensor_id, building_id, or trace_id"; everything else is a field |
A time column with nanosecond precision; the primary key is the ordered tags plus time |
| TimescaleDB | A row in a hypertable | Whatever columns you choose, as in any PostgreSQL table | A time column that partitions the table |
A series is the stream of values for one identity. In Prometheus, "the change of any label's value, including adding or removing labels, will create a new time series." A minimal example in the documentation's notation:
shopstream_http_requests_total{route="/checkout", status="200"}
shopstream_http_requests_total{route="/checkout", status="500"}That is two series of one metric. Reading a window of one series uses a range selector, the form the query documentation shows with http_requests_total{job="prometheus"}[5m].
Figure 1. ShopStream's two time-series workloads. How to read this: the top path carries service metrics into Prometheus, which holds few series and many samples. The bottom path carries courier pings into a time-partitioned table, which holds many rows per courier and an hourly rollup.
How each system lays data out
TimescaleDB: a PostgreSQL table cut into chunks
The documentation says hypertables "turn ordinary PostgreSQL tables into time-partitioned tables made of chunks", and that adding WITH (timescaledb.hypertable) to a CREATE TABLE creates one. A chunk covers a time span set by the chunk interval, which defaults to 7 days, and the guidance is to "choose a chunk interval so that all active chunks fit in memory." (The documentation shows both a timescaledb. and a tsdb. option prefix on different pages; check the page for your version.) Because it is PostgreSQL, you keep SQL, joins to ordinary tables and the PostgreSQL toolchain.
Prometheus: head block, write-ahead log and two-hour blocks
The storage page describes the layout: ingested samples are grouped into two-hour blocks; the current block lives in memory, protected by a write-ahead log that is replayed on restart; and the initial blocks are "eventually compacted into longer blocks in the background." The page also states a limit: local storage "is not clustered or replicated."
Figure 2. The Prometheus local write path. How to read this: the log exists only so the in-memory block survives a restart. Blocks, once written, are merged and finally dropped by the retention setting.
InfluxDB 3: tables, tags and Parquet
InfluxDB 3 Core organises data as databases that contain tables made of columns; every table has a primary key made of the ordered set of tags and the time, and that key "determines the sort order for all Parquet files related to the table." The schema guidance is to keep a table homogeneous (every row the same tags and fields) and to avoid wide tables.
Retention: age out whole pieces
Retention is the rule that data older than some age is removed. Each system exposes it differently:
| System | Setting | What the page says |
|---|---|---|
| TimescaleDB | add_retention_policy(relation, drop_after => ...) |
"Create a policy to drop chunks older than a given interval"; chunks "fully older than this interval when the policy is run are dropped"; one policy per hypertable |
| Prometheus | --storage.tsdb.retention.time, --storage.tsdb.retention.size |
Time default is 15 days if neither flag is set; the size flag sets the maximum bytes of blocks to keep |
| InfluxDB 3 | A database retention period | "The maximum age of data stored in the database"; past it a point is "not queryable and will be deleted"; "by default, data does not expire" |
Dropping a whole chunk or block, rather than deleting rows, is the point of time partitioning. PostgreSQL's partitioning documentation says dropping an individual partition "is far faster than a bulk operation" and "entirely avoids the VACUUM overhead caused by a bulk DELETE." The TimescaleDB retention pages I read do not state that comparison themselves, so treat it as the PostgreSQL manual's statement about partitions, which hypertable chunks resemble.
Prometheus gives a sizing formula: needed_disk_space = retention_time_seconds * ingested_samples_per_second * bytes_per_sample, with an average of "only 1-2 bytes per sample". An illustrative ShopStream case: 20,000 active series, one sample each every 15 seconds, is about 1,333 samples per second; over the 15-day default (1,296,000 seconds) that is about 1.73 billion samples, so roughly 1.7 to 3.5 GB on disk. The numbers are teaching values, and the formula says nothing about index or memory cost.
Downsampling: keep the shape, lose the detail
Downsampling replaces many fine points with fewer summary points. All three ecosystems describe a way to precompute them:
- TimescaleDB continuous aggregates: "A continuous aggregate stores precomputed rollups so analytical queries avoid rescanning all raw rows." It is a materialized view with
WITH (timescaledb.continuous)and atime_bucketin the query, refreshed by a policy with a start offset, an end offset and a schedule interval. - Prometheus recording rules "precompute frequently needed or computationally expensive expressions" and save them as new series; the page says they are "especially useful for dashboards".
- InfluxDB v2 tasks aggregate data in time windows and write it to a separate bucket. The page notes that for a bucket with a finite retention period you should "schedule tasks to run prior to the end of the retention period" so downsampling finishes before the data is dropped.
A Prometheus rule in the shape of the documentation's example (illustrative expression and names):
groups:
- name: shopstream
rules:
- record: route:shopstream_http_requests_total:sum
expr: sum by (route) (shopstream_http_requests_total)The rollup and the retention rule are two halves of one decision, and they can conflict (see the failure example below).
Cardinality: the cost of distinct series
Cardinality is the number of distinct series. Prometheus's naming guidance is blunt: "every unique combination of key-value label pairs represents a new time series, which can dramatically increase the amount of data stored", so "do not use labels to store dimensions with high cardinality (many different label values)" such as "user IDs, email addresses, or other unbounded sets of values."
This advice is engine-specific, and the documentation says so. The InfluxDB 3 schema guidance states that its storage engine "supports infinite tag value and series cardinality" and that, unlike earlier versions, tag cardinality "doesn't affect the overall performance of your database". The InfluxDB v2 documentation, in contrast, says "good schema design can prevent high series cardinality, resulting in better performing queries." So the rule is not "cardinality is always fatal" but "find out what your engine charges for a distinct series, and keep unbounded identifiers out of Prometheus labels."
and bounded?"} B -->|"yes: route, status class, zone"| C["Use as a label"] B -->|"no: order id, email, session id"| D{"Does the engine charge
per distinct series?"} D -->|"yes, Prometheus"| E["Keep it out of labels:
log it, or store it as a row"] D -->|"no, per its docs, InfluxDB 3"| F["Allowed, but check
query and storage cost"]
Figure 3. A label decision. How to read this: the first question is about the number of values; the second is about the engine's documented behaviour. The Prometheus branch is where most trouble starts.
Realistic example: ShopStream courier pings
This is the realistic example, an educational scenario on ShopStream. The Stage 3 lesson's teaching figures apply: 10,000 couriers online, one ping every 5 seconds, 50 bytes a ping (all illustrative), which is 2,000 writes per second, 100 KB per second and about 8.6 GB per day. Raw pings are kept for 7 days (about 60 GB) and rolled up after that.
A time-partitioned table suits this: the stream is append-only, queries are "this courier, this time window", and it joins to the courier table in the same database. Courier location is not a Prometheus fit: a ping is an event with coordinates, and a courier id as a metric label would add a series per courier, which the cardinality guidance asks you to weigh.
CREATE TABLE courier_pings (
time TIMESTAMPTZ NOT NULL,
courier_id INT NOT NULL,
lat DOUBLE PRECISION,
lon DOUBLE PRECISION,
speed_mps DOUBLE PRECISION
) WITH (timescaledb.hypertable, timescaledb.chunk_interval = '1 day');
CREATE MATERIALIZED VIEW courier_pings_hourly
WITH (timescaledb.continuous) AS
SELECT courier_id,
time_bucket(INTERVAL '1 hour', time) AS bucket,
count(*) AS pings,
avg(speed_mps) AS avg_speed_mps
FROM courier_pings
GROUP BY courier_id, bucket
WITH NO DATA;
SELECT add_continuous_aggregate_policy('courier_pings_hourly',
start_offset => INTERVAL '3 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
SELECT add_retention_policy('courier_pings', drop_after => INTERVAL '7 days');The statements follow the forms on the TimescaleDB pages; they were not run here, so check the options against your version. Why these choices:
- Chunk interval of 1 day. At about 8.6 GB a day (illustrative), the default 7-day chunk would be about 60 GB, which is hard to fit "in memory" on a modest instance. The right value depends on memory; one day is a starting point, not a rule.
- Refresh window of 3 days to 1 hour. It reaches back far enough to catch late pings and stays well inside the 7-day retention.
- Retention on the raw table only. The rollup keeps its own, longer, policy (the function accepts "a hypertable or continuous aggregate").
The live map needs only the latest position per courier. That is a point lookup, served from an overwritten key in the cache layer, not from this table.
Failure examples
The retention policy that deletes the rollup
The TimescaleDB guide on retention with continuous aggregates says you can delete raw data without deleting aggregate data, but "to keep your aggregates while dropping raw data, you must be careful about refreshing your aggregates": the refresh window must not overlap deleted data, because a refresh that sees raw data deleted "also deletes the aggregate data."
A plausible mistake in the design above: to save disk, someone shortens retention to 2 days. The refresh window still reaches back 3 days, so each hourly refresh sees raw data missing in part of its window and the rollup for those hours disappears. What breaks: the hourly history that was meant to be permanent. Detection: the row count of courier_pings_hourly for yesterday drops, or a dashboard shows gaps. Prevention: keep the policies in one reviewed file and add a check that start_offset is smaller than drop_after.
The label that creates a series per order
An engineer adds order_id to a checkout counter to debug one complaint. Each distinct value is a new series. If every order emits the metric once, a day adds up to 500,000 new series per metric (the shared ShopStream figure, illustrative), and the guidance quoted above applies directly. The fix is to remove the label, log the id instead, and rely on the route and status labels for dashboards.
Trade-offs
| Choice | Gain | Cost | Wrong when |
|---|---|---|---|
| Time-partitioned PostgreSQL table | SQL, joins, one toolchain | You size chunks and policies yourself | A pure metrics workload that a monitoring store handles directly |
| Prometheus | Built for metrics, recording rules, simple retention | Local storage is not clustered or replicated; labels must stay bounded | Event data with unbounded identifiers |
| InfluxDB 3 | Documented support for high tag cardinality; tags and fields model | Another system; schema discipline (homogeneous tables) | You already run PostgreSQL and need relational joins |
| Short raw retention plus rollups | Bounded disk cost | Detail is gone after the window | Compliance needs raw data for longer |
| Long raw retention | Full detail | Storage cost grows linearly with the window | Few queries look at old raw data |
with small bounded labels?"} Q1 -->|"yes"| P["Monitoring store, such as Prometheus"] Q1 -->|"no"| Q2{"Need joins with relational data
and SQL?"} Q2 -->|"yes"| T["Time-partitioned table, such as a hypertable"] Q2 -->|"no"| Q3{"Very many distinct series?"} Q3 -->|"yes"| I["Engine documented for high cardinality,
such as InfluxDB 3"] Q3 -->|"no"| T
Figure 4. A first-pass store choice. How to read this: it is a starting point for the conversation, not a verdict; measure on your own data.
In practice: telemetry for the ShopStream courier fleet
This is an educational scenario on ShopStream with illustrative numbers.
This is a realistic scenario, not a case history.
The situation
Courier pings and service metrics share the orders database and the single monitoring server. Backups are growing and a monitoring server restart loses the history because it is not replicated.
Starting architecture
Pings are rows in a plain PostgreSQL table. Metrics go to one Prometheus server with the default retention and no recording rules.
Options considered
| Option | Gain | Cost | Wrong when |
|---|---|---|---|
| A. Keep pings in a plain table, delete old rows nightly | No new component | Bulk deletes and vacuum cost | Ping volume keeps growing |
| B. Hypertable with retention and an hourly rollup | Whole-chunk drops, SQL kept | New extension to operate | The managed database cannot run it |
| C. A separate time-series engine | Purpose-built | New system and new query language | Pings must join to orders often |
The decision
Option B for pings, because joins to couriers and orders matter and volume is moderate. Metrics stay in Prometheus, with recording rules for the dashboards and a rule that labels come from a short reviewed list.
Rollout and verification
Create the hypertable, dual-write for a week, compare ping counts per courier per hour between the old and new tables, then switch readers. Verify the rollup against raw counts for a closed hour, and confirm the oldest chunk is gone after the retention window.
The 2 a.m. failure
The page reads: the rollup for yesterday is empty. Check the policy configuration for a retention change, compare drop_after with the refresh start_offset, and look at job status. Mitigation: restore the retention interval, then re-materialise the missing range from a backup of the raw data if one exists. If it does not, the history is gone, which is why the check belongs in review.
Architect's notes
- Write down retention per tier, in days, next to the cost it saves, and who may change it.
- Decide rollup shape before the raw window ends. A rollup can only contain what you chose to aggregate.
- Review labels like schema. An unbounded label is a schema change with a bill.
- Separate the latest value from the history. "Where is courier 42 now" is a point lookup and "where was courier 42 at 10:00" is a range query.
- Measure restore, not just backup, for time-partitioned stores and for monitoring data.
- Verify the version. Policy names and option prefixes move between releases of TimescaleDB, and InfluxDB 3 differs from v2 in its cardinality behaviour.
Common mistakes
- Unbounded labels (order, session, user, email) in Prometheus.
- Retention shorter than the rollup refresh window.
- Averaging when the aggregate should be a count, a maximum or a percentile; decide what each rollup answers.
- Chunk intervals chosen by default without reading the data rate.
- Treating the monitoring store as durable when its documentation says local storage is not replicated.
- Keeping "everything forever" because deletion feels risky; cost grows with the window.
- Using the time-series store for the latest position of each entity.
Key takeaways
- Time-series data is append-mostly, queried by range, and loses value with age; design around that.
- Partition by time so retention drops whole chunks or blocks instead of deleting rows.
- Retention and downsampling are one decision: the rollup must be complete before the raw data goes, and its refresh window must not overlap deleted data.
- Cardinality is the number of distinct series. Prometheus charges for each one; InfluxDB 3 documents support for very high cardinality. Know your engine.
- A hypertable keeps SQL and joins; Prometheus keeps metrics simple; each has a limit you must name.
- The latest value per entity is a different query from history.
Practice exercises
Exercise 1 (30 min). For 50,000 active series, one sample every 30 seconds and 30 days of retention (illustrative), compute the disk range with the Prometheus formula and 1 to 2 bytes per sample.
Exercise 2 (45 min). List five candidate labels for a delivery-time metric and mark each bounded or unbounded. Rewrite the metric so unbounded identifiers move to logs.
Exercise 3 (1 hour). Choose a chunk interval for 8.6 GB a day of pings on an instance with 16 GB of memory (illustrative) and justify it with the "active chunks fit in memory" rule.
Exercise 4 (1 hour). Design a three-tier retention plan for courier pings (raw, hourly, daily). Give each tier's window and say what each rollup can and cannot answer.
Related reading
- Storage engines: why time-series layouts differ from B-tree layouts.
- ShopStream Stage 3: choosing the right data store: where these two workloads fit.
- Observability and SLOs: metrics in practice.
- SQL vs NoSQL: a decision framework: when a relational table is enough.
- Cassandra and DynamoDB modelling: time-bucketed partitions in key-oriented stores.
Official documentation
- Prometheus documentation: data model, metric and label naming, storage, recording rules.
- TimescaleDB (Tiger Data) documentation: hypertables, add_retention_policy, continuous aggregates, retention with continuous aggregates.
- InfluxDB 3 Core: schema design, databases and retention; InfluxDB v2: downsampling.
- PostgreSQL 18 manual: table partitioning.