Time-Series Data in Postgres: Honest Tradeoffs Before You Reach for TimescaleDB

The pitch for time-series databases is usually "Postgres can't handle time-series at scale." In my experience, plain Postgres handles time-series data significantly better than this framing suggests. The decision to add a specialized extension should come from observed limits, not from preemptive worry about scale you have not yet encountered.

Here is the honest version of what Postgres can do, where it struggles, and when something else is the right call.

What Postgres handles fine

For most production time-series workloads — application metrics, server stats, IoT data at moderate scale — plain Postgres with the right schema and indexes is enough.

CREATE TABLE measurements (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  metric_name TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  value DOUBLE PRECISION NOT NULL,
  tags JSONB DEFAULT '{}'
);

CREATE INDEX measurements_metric_recorded
  ON measurements (metric_name, recorded_at DESC);

-- For time-range queries on large tables
CREATE INDEX measurements_recorded_brin
  ON measurements USING BRIN (recorded_at);

With this shape:

• Time-range queries are fast (BRIN on recorded_at).
• Per-metric queries are fast (composite B-tree).
• Aggregations over time windows are reasonable.
• Storage is dense.

For up to a few hundred million rows of measurements, this works well on a typical RDS instance.

What Postgres struggles with

1. Continuous aggregates that are hot. "Average of every 1-minute bucket over the last 24 hours, refreshed every 30 seconds." Doable with materialized views and a refresh schedule, but the bookkeeping is manual.

2. Retention without active deletion pressure. Hard-deleting old rows is expensive on hot tables. Partitioning helps; without it, retention work itself becomes a load problem.

3. Massive scale. Billions of rows per day with high query volume. Postgres can do it, but the operational care required is significant. At that scale, specialized tools start to look more attractive.

Partitioning is the first lever

For any time-series table that will grow large, declarative partitioning is the right starting point:

CREATE TABLE measurements (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  metric_name TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  value DOUBLE PRECISION NOT NULL
) PARTITION BY RANGE (recorded_at);

-- Monthly partitions
CREATE TABLE measurements_2025_01 PARTITION OF measurements
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE measurements_2025_02 PARTITION OF measurements
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Benefits:

• Retention is fast: drop a partition, instant.
• Range queries are pruned: queries with recorded_at predicates only touch matching partitions.
• Indexes are smaller per partition: fits in cache better.
• Vacuum runs on smaller chunks: less impactful.

The automation: pg_partman extension manages partition creation and retention. Setup is a few lines of config.

When TimescaleDB starts to pay off

TimescaleDB is a Postgres extension that adds time-series-specific features:

• Hypertables: automatic partitioning by time, with much lower overhead than declarative partitioning at the schema level.
• Continuous aggregates: incrementally-maintained materialized views over time buckets.
• Compression: column-store compression for old chunks. 10-100x storage reduction on read-only data.
• Retention policies: built-in.
• Time-bucket functions: convenient grouping by time intervals.

The tradeoff: it is an extension. Some managed Postgres services do not support it; some support it on specific tiers; the OSS version's compression has license restrictions for some use cases.

When TimescaleDB starts to pay off:

• You have meaningful storage costs and compression would help.
• You need real-time continuous aggregates and the materialized-view-with-cron approach is not keeping up.
• You are doing analytics-heavy queries (time-bucket aggregations) regularly.

When TimescaleDB is overkill:

• You are storing modest amounts of time-series data without query patterns that need its specific features.
• Your managed Postgres does not support it (RDS without third-party tools, for instance).
• You can solve the actual problem with partitioning + materialized views.

A practical decision tree

For a new time-series workload:

1. Estimate scale: how many rows per day, retention horizon, query patterns. Most teams overestimate.
2. Start with partitioned plain Postgres unless the estimate is clearly very large.
3. Use BRIN on the time column if the data is naturally chronological.
4. Build a materialized view + refresh schedule for any continuous aggregate need.
5. Monitor table growth, query latency, and disk usage.
6. Consider TimescaleDB if the materialized view refresh becomes a bottleneck or storage costs outweigh the migration effort.

Most workloads stop at step 5.

A real comparison

A team I worked with had ~50M rows/day of metrics data. They started with plain partitioned Postgres on RDS. After a year:

• Total rows: 18B.
• Total disk: 1.2TB.
• Query latency for 1-hour aggregates: 200ms.
• Query latency for 30-day aggregates: 3-5 seconds.
• Operational cost: ~$2k/month for the dedicated read replica.

They evaluated TimescaleDB. The expected wins:

• 5-10x storage reduction on cold partitions (compression).
• 10-100x speedup on continuous-aggregate queries.
• Less operational work managing partitions.

The migration cost: roughly 6 weeks of one engineer.

They did the migration. Six months in, the bill was lower (smaller storage), the queries were faster, and the team spent less time on retention and aggregation. It paid off — but only because they had reached the scale where it mattered.

The team that started at 5M rows/day did not need any of this. The team at 500M rows/day would have needed it from the start.

What I recommend

For any new time-series workload:

• Start partitioned. Do not start unpartitioned.
• Use BRIN on the time column.
• Use composite B-tree (metric, time DESC) for per-metric lookups.
• Schedule retention via DROP PARTITION, not DELETE.
• Use materialized views with cron-refresh for continuous aggregates.
• Re-evaluate when you have observed actual scale, not before.

The last point is the most important. Time-series storage decisions made on imagined scale tend to be wrong. Decisions made on observed scale, with real data on costs and query patterns, are usually right.