- Book: Database Playbook: Choosing the Right Store for Every System You Build
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
A team I talked to last quarter was stuck. Their compliance review had flagged TimescaleDB's licensing, their managed Postgres provider didn't ship the extension, and the consensus answer on every blog post was "use TimescaleDB for time-series." So they started building on native partitioning expecting a disaster. They didn't get one.
What they got was a system within 15-30% of a TimescaleDB hypertable on four of five workloads. The fifth one fell off a cliff. That gap is the whole story, and almost nobody benchmarks it honestly.
The honest comparison nobody runs
Most TimescaleDB-vs-Postgres benchmarks are unfair in two directions at once. The pro-Timescale ones compare hypertables against a single un-partitioned bigint table. That's not a real production setup, that's a strawman. The skeptic posts run native partitioning without BRIN indexes, without pg_partman-style automation, and without realistic data distribution.
Both are useless if you're the engineer who has to pick.
A fair comparison needs four things native partitioning rarely gets in benchmarks: declarative range partitioning on the time column, BRIN indexes on time, B-tree on series id, and automated partition creation. That's the configuration TimescaleDB compares against once you actually tune it. Anything less and you're benchmarking laziness, not Postgres.
The dataset and the setup
Numbers in this post come from a synthetic workload that mirrors a fleet-monitoring use case: 1 billion rows, 500 distinct series (think 500 servers each emitting metrics), 2 years of history at roughly 30-second intervals. Postgres 18 with TimescaleDB 2.x compiled against it. Same machine, same disk, same shared_buffers, same work_mem. Numbers below are medians from 20 runs after warmup, not first-run cold-cache.
The schema you'll see both versions of below:
CREATE TABLE metrics (
ts timestamptz NOT NULL,
series_id int NOT NULL,
value double precision NOT NULL,
tags jsonb
);
That's it. No premature optimization, no exotic types. The question is what you do next.
Native partitioning setup
Range partition on ts, weekly intervals, BRIN on time, B-tree on series:
CREATE TABLE metrics (
ts timestamptz NOT NULL,
series_id int NOT NULL,
value double precision NOT NULL,
tags jsonb
) PARTITION BY RANGE (ts);
-- one partition per ISO week. 104 partitions for 2 years
CREATE TABLE metrics_2024w01
PARTITION OF metrics
FOR VALUES FROM ('2024-01-01') TO ('2024-01-08');
-- ... 103 more, automated by pg_partman or a cron job
-- BRIN on time. Mandatory. Don't skip this.
CREATE INDEX ON metrics USING brin (ts)
WITH (pages_per_range = 32);
-- B-tree on series for point lookups
CREATE INDEX ON metrics (series_id, ts DESC);
Two things matter here and people get them wrong.
First, the partition size. Weekly partitions on 1B rows give you about 10M rows per partition, which is the sweet spot for Postgres's query planner. Daily partitions sound smaller and faster, but the planner overhead from 730 partitions starts eating wins. Monthly partitions are too coarse for hot-cold tiering. Weekly is the right answer for almost everyone.
Second, BRIN. A B-tree on the time column for 1B rows is in the tens of gigabytes and doesn't actually help much because the data is already ordered by insertion time. BRIN is the right tool. It stores min/max per block range, so a range scan over a week of data hits the relevant blocks directly. Without BRIN, native partitioning is genuinely slow and you'll think Timescale is magic. It isn't. You just forgot the index.
Automation lives in pg_partman or a pg_cron job:
SELECT partman.create_parent(
p_parent_table => 'public.metrics',
p_control => 'ts',
p_type => 'range',
p_interval => '1 week',
p_premake => 4 -- keep 4 weeks of future partitions ready
);
TimescaleDB hypertable setup
CREATE TABLE metrics (
ts timestamptz NOT NULL,
series_id int NOT NULL,
value double precision NOT NULL,
tags jsonb
);
SELECT create_hypertable(
'metrics', 'ts',
chunk_time_interval => INTERVAL '1 week'
);
CREATE INDEX ON metrics (series_id, ts DESC);
Three lines. That's the whole pitch. Chunks are weekly automatically, the planner knows about them, compression and continuous aggregates ride on top.
Now the five query shapes.
Query 1: Point lookup, single series, narrow time
The most common shape in dashboards. "Show me this one metric for the last hour."
SELECT ts, value
FROM metrics
WHERE series_id = 142
AND ts >= now() - INTERVAL '1 hour'
ORDER BY ts;
| Setup | p50 latency |
|---|---|
| Native + BRIN + B-tree | 4.1 ms |
| TimescaleDB hypertable | 3.6 ms |
Difference: ~14%. Both are fine. The B-tree on (series_id, ts DESC) is doing the work in both setups. Timescale's planner shaves a tiny bit off by pruning chunks without consulting the planner's full partition-elimination machinery, but you'd never feel it.
Query 2: Range scan, single series, wide time
"Show me this metric for the last 30 days for a chart."
SELECT ts, value
FROM metrics
WHERE series_id = 142
AND ts >= now() - INTERVAL '30 days'
ORDER BY ts;
| Setup | p50 latency |
|---|---|
| Native + BRIN + B-tree | 89 ms |
| TimescaleDB hypertable | 71 ms |
Difference: ~25%. Native is touching 4-5 partitions, the B-tree gives it series scoping, and the planner walks them in order. Timescale's chunk exclusion is slightly tighter and its internal scan logic is built for this shape, but the gap is small enough that almost no API user would notice.
Query 3: Aggregation per bucket, single series
"Five-minute averages for the last 7 days, for one server."
SELECT
time_bucket('5 minutes', ts) AS bucket,
avg(value)
FROM metrics
WHERE series_id = 142
AND ts >= now() - INTERVAL '7 days'
GROUP BY bucket
ORDER BY bucket;
For native partitioning you replace time_bucket() with date_bin(), which has been in core Postgres since 14:
SELECT
date_bin('5 minutes', ts, TIMESTAMPTZ '2024-01-01') AS bucket,
avg(value)
FROM metrics
WHERE series_id = 142
AND ts >= now() - INTERVAL '7 days'
GROUP BY bucket
ORDER BY bucket;
| Setup | p50 latency |
|---|---|
Native + date_bin
|
142 ms |
TimescaleDB + time_bucket
|
118 ms |
Difference: ~20%. Both are scanning the same ~2M rows, both are bucket-aggregating in memory. Timescale's win here is the chunk-aware aggregate execution path and slightly better parallel scan tuning. Not magic. Not 10x. Twenty percent.
Query 4: Top-N per series
"For each of these 50 servers, give me the latest 100 samples."
SELECT series_id, ts, value
FROM metrics
WHERE series_id = ANY (ARRAY[1,2,3, /* ...47 more */ 50])
AND ts >= now() - INTERVAL '1 day'
ORDER BY series_id, ts DESC;
| Setup | p50 latency |
|---|---|
| Native + B-tree | 23 ms |
| TimescaleDB hypertable | 19 ms |
Difference: ~17%. The B-tree on (series_id, ts DESC) carries both setups. Timescale gets a small win from its skip-scan optimization for series lookups, but the index is doing the heavy lifting in both cases.
So far native partitioning is holding within 15-30%. Then query 5 happens.
Query 5: The cliff
"Daily averages for the last 90 days, all 500 series, for a dashboard that refreshes every minute."
SELECT
date_bin('1 day', ts, TIMESTAMPTZ '2024-01-01') AS day,
series_id,
avg(value)
FROM metrics
WHERE ts >= now() - INTERVAL '90 days'
GROUP BY day, series_id
ORDER BY day, series_id;
This is 90 days × 500 series × ~2880 samples per day per series. About 130 million rows aggregated per query.
| Setup | p50 latency |
|---|---|
| Native partitioning | 14.2 s |
| TimescaleDB raw scan | 11.8 s |
| TimescaleDB continuous aggregate | 38 ms |
Read that again. Thirty-eight milliseconds. That's not a 15-30% win. That's 370x.
A continuous aggregate is a materialized view that Timescale maintains incrementally as new data arrives. You pre-compute the daily averages once, append to the rollup on every insert, and the dashboard query reads ~45,000 rows instead of 130 million.
CREATE MATERIALIZED VIEW metrics_daily
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 day', ts) AS day,
series_id,
avg(value) AS avg_value,
max(value) AS max_value,
min(value) AS min_value,
count(*) AS sample_count
FROM metrics
GROUP BY day, series_id;
SELECT add_continuous_aggregate_policy(
'metrics_daily',
start_offset => INTERVAL '7 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour'
);
Native partitioning has materialized views, sure, but they need full refresh. No incremental rollup. You can build incremental rollups by hand with triggers or a scheduled job that processes the last hour's worth of data, but you're now maintaining a small distributed system inside Postgres and the bugs are yours. Trigger-based rollups also serialize write contention on the rollup table.
This is where TimescaleDB earns its place.
The row count where the trade flips
The pattern from the benchmark:
- Under 100M rows: Native partitioning is fine. You don't even need BRIN at this size. Use Timescale if you already have it, but don't switch databases for it.
- 100M to 500M rows: Native + BRIN holds within 15-30%. Pick on operational fit, not performance.
- 500M to 1B rows: Native still holds for queries 1-4. Query 5 (the dashboard rollup pattern) starts hurting badly. Either accept the latency, build manual rollups, or move to Timescale.
- Beyond 1B rows: Continuous aggregates are doing a job you'd otherwise build yourself. Native partitioning still works for raw queries, but the time you save on operations gets eaten by the time you spend maintaining rollup pipelines.
The threshold isn't strictly about row count. It's about whether your read pattern is "individual queries" or "dashboards that aggregate everything every minute." A 100M-row table with dashboard queries every 30 seconds will hit the continuous-aggregate wall sooner than a 1B-row table that's mostly accessed for one-series drilldowns.
The licensing footnote (read this before you commit)
TimescaleDB is dual-licensed and the difference matters.
Apache 2.0 (the free, permissive part):
- Hypertables
- Chunk-based partitioning
- Continuous aggregates
-
time_bucket()and the basic time functions - Most retention and compression policies
Timescale License (TSL, source-available but not OSI-approved):
- Columnar compression beyond the basic level
- Some advanced continuous aggregate features (real-time aggregation, hierarchical)
- Tiered storage to object stores
- Some user-defined actions
For most teams using TimescaleDB for the time-series basics (hypertables and continuous aggregates), you're on Apache 2.0 and there's nothing to worry about. The TSL features matter if you're storing tens of TB and need columnar compression, or if you're building a SaaS that re-exports the database.
The other licensing question is your managed provider. AWS RDS doesn't ship TimescaleDB. Cloud SQL on GCP doesn't ship it. Azure Database for PostgreSQL doesn't ship it. If you're locked into one of those, your options are: move to Timescale's own cloud, run self-managed Postgres on EC2/Compute Engine, or use Aiven/Crunchy/another provider that does support the extension. That operational reality kills the discussion for some teams before performance ever enters the picture.
If you're on RDS and can't move, native partitioning is your answer. Stop reading benchmarks and start tuning BRIN.
The gotcha nobody warns you about
date_bin() and time_bucket() look interchangeable. They aren't.
time_bucket() lets you specify a custom origin in a single call and handles month/year intervals (time_bucket('1 month', ts)). date_bin() requires an explicit origin and rejects month/year intervals because months have variable length.
This means if you're prototyping on Timescale and want to migrate to native partitioning later, your aggregation queries will break for any bucket size of 1 month or larger. You'll need to switch to date_trunc('month', ts) for those, which doesn't support arbitrary intervals. The migration path looks clean on paper. In practice you're rewriting every dashboard query if you used non-day buckets.
Pick your bucket function with the migration story in mind, not just the prettier syntax.
A decision tree
Here's the call, made out loud.
Do you need continuous aggregates that update incrementally on a dataset above 500M rows?
If yes → TimescaleDB, no debate. Building this yourself is a project, not a query.
If no → keep reading.
Are you on AWS RDS, Cloud SQL, or Azure Postgres and can't move?
If yes → native partitioning + BRIN + pg_partman. You don't have a choice and it's actually fine.
If no → keep reading.
Is your dataset below 500M rows and likely to stay there for 18+ months?
If yes → native partitioning. Fewer moving parts, no licensing review, no extension version to chase across upgrades.
If no → are your queries dominated by full-fleet dashboards or by single-series drilldowns?
- Dashboards → TimescaleDB. Continuous aggregates are the reason.
- Drilldowns → either works. Pick on operational fit.
Do you already run TimescaleDB somewhere else in your stack?
If yes → use it. Operational consistency beats a 20% query win that's hidden inside a microservice nobody profiles.
The honest takeaway: native Postgres partitioning is more capable than the prevailing "use TimescaleDB" wisdom suggests, and the gap on point/range/aggregate queries is small enough that operational concerns win the tiebreaker most of the time. Continuous aggregates are the one place where the extension does something native Postgres genuinely can't match without you building it by hand, and once your dashboards rely on them, you're stuck with the extension whether you wanted to be or not.
Pick the row count where you'll re-evaluate. Write it down. Then stop arguing about it until you hit that number.
What's the row count where you flipped (or would flip)? Drop your dataset size, query pattern, and whether you stayed on native or switched in the comments. Curious whether the 500M threshold matches what other people see in production.
If this was useful
This post is one tiny slice of how time-series workloads fit into the bigger picture of database selection. The Database Playbook: Choosing the Right Store for Every System You Build walks through how to pick the right store for each part of your system, including the chapter on time-series, observability, and analytical workloads where the TimescaleDB-vs-native call lives next to questions about ClickHouse, DuckDB, and Parquet on S3. If you liked the framing of "decide once, write down the threshold, re-evaluate later," that's most of the book.
Top comments (0)