---
title: "Time-Series Database vs. Relational Database"
description: "Time-series database vs. relational database: a decision rule for when plain Postgres is enough, when it struggles, and when a dedicated TSDB wins."
section: "Time-series basics"
published: 2026-10-21T18:25:15.819Z
updated: 2026-10-21T00:00:00.000Z
---
*Updated at Oct 21, 2026*
> **TimescaleDB is now Tiger Data.**
## Time-series database vs. relational database: the short answer
A time-series database is optimized for time-ordered, append-heavy data such as metrics, sensor readings and events. A relational database is optimized for entities and the relationships between them. For most teams, the practical answer is Postgres extended for time series, which keeps SQL, joins and constraints.
The dividing line is the access pattern, not the data type. Users, orders and devices are state that changes in place. Readings, logs and ticks are events that accumulate and get queried by time window.
A single application usually has both, which is why the choice is rarely "relational or time-series" and more often "how much help does the events table need." For a fuller definition, see [what a time-series database is and when you need one](https://www.tigerdata.com/learn/time-series-database-what-it-is-how-it-works-and-when-you-need-one).
Tiger Data builds [Tiger Cloud](https://www.tigerdata.com/cloud) and the [TimescaleDB](https://www.tigerdata.com/timescaledb) extension, so we have a stake in this answer. The sections below include the cases where plain Postgres, or a dedicated time-series database, is the better choice. The decision rule comes first, then the detail.
## The decision rule: choose X if
### Choose plain Postgres if
Plain Postgres is the right choice when time-series data is a small part of an otherwise transactional application. Ingest is modest, tables stay small enough that routine maintenance is uneventful, retention is short or indefinite, and queries mostly look up recent rows or join to other tables. In that situation, operational simplicity is worth more than storage efficiency, and an extension adds little. This tends to work well until tables and indexes grow large, which the next section covers.
### Choose Postgres with the TimescaleDB extension (Tiger Cloud) if
Postgres with the TimescaleDB extension fits when time-ordered data grows continuously and the team already runs Postgres or wants SQL, joins and constraints. It suits workloads that need retention policies, compression or fast time-window aggregates, and workloads where readings need relational context such as devices, customers and assets in the same query.
Tiger Cloud is the managed way to run it. TimescaleDB also runs self-hosted, or as [TimescaleDB Enterprise](https://www.tigerdata.com/timescaledb-enterprise). The tradeoff is that it is still Postgres, with writes going to a single primary, so extreme write-only workloads can fit a purpose-built engine better.
### Choose a dedicated time-series database if
A dedicated time-series database fits when the workload is mostly write-heavy metrics or telemetry with little relational context. It also fits when the team is comfortable adopting a separate query language and operational surface, or when an existing observability stack already standardizes on one. Strengths tend to include purpose-built ingestion paths, built-in retention and tag-based data models. The costs are a second system to operate, weaker joins to business data in many cases, and in some ecosystems version fragmentation. InfluxDB, for example, has shifted its primary query language across major versions (InfluxQL in 1.x, Flux in 2.x, SQL and InfluxQL in 3), which community reports suggest adds migration and learning-curve cost.
## Signs you've outgrown a plain Postgres table
Each of these is a symptom with an underlying cause. Seeing one or two of them is a reason to evaluate options.
- **Inserts compete with reads.** Sustained high-rate writes slow dashboard queries on the same table, because every insert also updates every index on it.
- **Indexes outgrow memory.** Indexes grow with the whole table. A time index takes new entries at its right edge, but indexes on other columns, such as device_id, take them throughout the index. Once those no longer fit in cache, inserts start reading index pages from disk.
- **Retention runs as a bulk **`**DELETE**`**.** A nightly job that deletes old rows leaves dead tuples behind, so vacuum has more work and the table bloats.
- **Someone maintains time partitions by hand.** A cron job or script creates and drops partitions, and a missed run shows up as failed inserts or a slow, oversized default partition.
- **Time-window aggregates keep slowing down.** Dashboards that average or sum over days or weeks rescan raw rows, so latency tends to rise as history accumulates.
- **Storage cost outpaces the value of old data.** Row storage keeps full detail for data nobody queries at full resolution.
- **Maintenance windows get longer.** `VACUUM`, `REINDEX`, backups and schema changes on the largest table take more time each quarter.
One or two of these is a signal to evaluate options, not an emergency.
## Data model: state vs. events, and why joins matter
Relational tables model entities that are updated in place. Time-series data models immutable, timestamped observations that are mostly inserted and rarely updated. In Postgres, both are ordinary tables:
`CREATE TABLE devices (
device_id BIGINT PRIMARY KEY,
name TEXT NOT NULL,
site TEXT NOT NULL,
customer_id BIGINT NOT NULL
);
CREATE TABLE readings (
time TIMESTAMPTZ NOT NULL,
device_id BIGINT NOT NULL REFERENCES devices (device_id),
value DOUBLE PRECISION
);`
Time-series data almost always needs relational context: which device, which customer, which site. That is the structural advantage of staying relational. Tag-based and schemaless models tend to push that context into denormalized labels, so renaming a site or moving a device to another customer becomes a rewrite of history instead of one `UPDATE`. In Postgres, the same question is a join with a time filter:
`SELECT
d.site,
avg(r.value) AS avg_value
FROM readings r
JOIN devices d USING (device_id)
WHERE r.time > now() - INTERVAL '24 hours'
AND d.customer_id = 42
GROUP BY d.site
ORDER BY d.site;`
For guidance on one wide table versus several, see [time-series data modeling with hypertables](https://www.tigerdata.com/learn/best-practices-time-series-data-modeling-single-or-multiple-partitioned-tables-aka-hypertables).
## Write and query patterns
### Two workload shapes
Time-series workloads are append-heavy, read mostly recent data, run wide time-range aggregations and almost never update rows. OLTP workloads mix reads and writes, rely on point lookups and update rows often.
Plain Postgres handles both shapes, but the time-series shape stresses it in two places. Every insert maintains every index, and that cost grows as indexes grow. Long-range aggregates read large parts of one big table, and an unpartitioned table has no partitions to skip.
### Time-window aggregates in SQL
The query itself barely changes. Here is an hourly average in plain Postgres:
`SELECT
date_trunc('hour', time) AS bucket,
device_id,
avg(value) AS avg_value
FROM readings
WHERE time > now() - INTERVAL '7 days'
GROUP BY bucket, device_id
ORDER BY bucket;`
And the same query using [`time_bucket`](https://www.tigerdata.com/docs/reference/timescaledb/hyperfunctions/time-series-utilities/time_bucket):
`SELECT
time_bucket('1 hour', time) AS bucket,
device_id,
avg(value) AS avg_value
FROM readings
WHERE time > now() - INTERVAL '7 days'
GROUP BY bucket, device_id
ORDER BY bucket;`
The SQL keeps the same shape. The difference is that `date_trunc` truncates to a calendar unit, and PostgreSQL 14 and later adds date_bin for fixed widths such as 15 minutes. `time_bucket` also handles months and time zones, and returns the start of each bucket.
### Continuous aggregates for repeated rollups
Repeated aggregates like this are what [continuous aggregates](https://www.tigerdata.com/docs/learn/continuous-aggregates) are for. A continuous aggregate is a materialized view that refreshes incrementally in the background, so it updates the buckets that received new or changed data instead of rebuilding the whole view. Tiger Docs describe the maintenance burden as much lower than a regular Postgres materialized view for that reason. Continuous aggregates and retention policies need a hypertable. On an empty table, one call converts readings:
`SELECT create_hypertable('readings', by_range('time'));
CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', time) AS bucket,
device_id,
avg(value) AS avg_value
FROM readings
GROUP BY bucket, device_id;
SELECT add_continuous_aggregate_policy('readings_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');`
Two caveats from the docs. Real-time aggregates, which add the newest raw data to the materialized rollup at query time, are off by default in TimescaleDB 2.13 and later, and the `timescaledb.materialized_only` setting controls them. Even with them on, history written into an already-materialized bucket does not appear until the next refresh, so treat the view as eventually consistent for backfills.
### SQL vs. a specialized query language
Using a relational database for time-series data means queries stay in SQL. SQL works with existing BI tools, ORMs and drivers, and engineers already know it.
Specialized languages can be concise for some metrics queries, such as rates and moving windows over tagged series. They tend to cost more once a query needs a join to business data or a second team needs access.
## Compression and partitioning: where plain Postgres stops and extensions start
Two generalizations about relational databases come up often: that they have limited compression and that they only scale vertically. Both are largely true of plain row-store Postgres at default settings. Neither is a fixed property of Postgres with a time-series extension.
### Time partitioning
Declarative partitioning in Postgres works, but someone, or an extension such as `pg_partman`, has to create, attach and drop partitions on a schedule. A [hypertable](https://www.tigerdata.com/docs/learn/hypertables/understand-hypertables) is a Postgres table that partitions itself by time into chunks. Queries that filter on the time column skip chunks outside the range, and writes land in small, recent chunks with small indexes.
The default chunk interval is 7 days for timestamp columns. Tiger Docs suggest sizing it so the indexes of the chunks being written fit within 25% of main memory. See [how to size hypertable chunks](https://www.tigerdata.com/docs/learn/hypertables/sizing-hypertable-chunks).
### Columnar compression
[Hypercore](https://www.tigerdata.com/docs/learn/columnar-storage/understand-hypercore) is the hybrid row and columnar storage engine behind hypertables. Recent data stays in the rowstore for fast inserts. Older chunks convert to the columnstore, a compressed columnar layout that tends to cut storage and speed up analytical scans.
Columnstore chunks still accept inserts, updates and deletes. When you create a hypertable with `CREATE TABLE ... WITH (tsdb.hypertable)`, a columnstore policy is added by default. Tiger Docs cite storage reductions of up to 98%, and the actual ratio depends heavily on the data. For the storage-layout background, see [columnar vs. row-oriented databases](https://www.tigerdata.com/learn/columnar-databases-vs-row-oriented-databases-which-to-choose).
### Retention
Deleting old rows with `DELETE` is slow and leaves bloat that vacuum has to clean. A [retention policy](https://www.tigerdata.com/docs/learn/data-lifecycle/data-retention/about-data-retention) drops whole chunks instead, which removes a file from disk and needs no garbage collection:
`SELECT add_retention_policy('readings', INTERVAL '90 days');`
If you also use continuous aggregates, keep the aggregate refresh window inside the retention window. Tiger Docs warn that when the refresh window covers data a retention policy has removed, those aggregate buckets are removed on refresh.
### Horizontal scale
A hypertable lives on one Postgres primary, which handles all writes. Tiger Cloud scales it by resizing compute, adding read replicas for read-heavy workloads, and tiering older chunks to object storage while keeping them queryable.
It does not spread writes for one hypertable across several writer nodes, and the older multi-node distributed hypertable feature has been sunset. A workload that needs multi-writer scale, or a very high sustained write rate with no relational context, is where a distributed or purpose-built engine can be the better fit.
### Constraints to know
Hypertables carry a few rules that plain tables do not. Unique and primary key indexes must include the time column, and foreign keys from one hypertable to another hypertable are not supported. A hypertable can reference a normal table, as `readings` does above. See the [limitations page](https://www.tigerdata.com/docs/deploy/limitations).
## Plain Postgres vs. Postgres + TimescaleDB vs. a dedicated TSDB
| **Plain Postgres** | **Postgres + TimescaleDB (Tiger Cloud)** | **Purpose-built TSDB (tag-based or NoSQL-style)** |
| --- | --- | --- |
| Ingest pattern | Fine at modest rates; a single growing table and index tends to slow inserts at scale | Time-partitioned chunks keep recent writes in small indexes; writes go to a single primary | Built for high-rate append; ingest paths tuned for it |
| Compression | Row store; limited at defaults | Columnstore for older chunks; ratio depends on data | Typically columnar and compressed by design |
| Query language | SQL | SQL plus time functions such as `time_bucket` | Specialized or SQL-like; varies by product |
| Joins and relational context | Full joins, constraints, foreign keys | Full joins and foreign keys to normal tables; no foreign keys between two hypertables | Often weaker; context tends to live in tags |
| Retention and downsampling | Manual `DELETE` jobs and hand-built rollups | Retention policies drop chunks; continuous aggregates roll up | Retention built in; downsampling built in or via add-ons, depending on the product |
| Operational burden | Lowest if you already run Postgres; rises with table size | Postgres operations plus policies to tune | A second system to run, secure and learn |
| Ecosystem and tooling | Full Postgres ecosystem | Full Postgres ecosystem; some features are Tiger Cloud only | Product-specific clients; often strong in observability stacks |
| Best fit | Modest time-series data inside a transactional app | Growing time-ordered data that needs relational context | Write-heavy metrics with little relational context |
| Main limitation | Maintenance and cost grow with table size | Single-primary writes (scale by resizing, read replicas, tiering); constraint rules; extreme write-only loads may fit elsewhere | Second system; weaker joins to business data; version fragmentation in some products |
The table does not capture team skills, existing infrastructure or cost model. A team with deep Postgres experience and a small ops budget weighs the columns differently from a team that already runs a dedicated metrics stack. Treat it as a fit decision, not a ranking. For named products, see the [best time-series databases compared](https://www.tigerdata.com/learn/the-best-time-series-databases-compared).
## Migration paths
### From a plain Postgres table to a hypertable
Converting an existing table is a data move, not only a metadata change. Tiger Docs say that converting a table that already holds data with `create_hypertable()` can be slow and lock-heavy on very large tables. For those, the docs recommend creating an empty hypertable and loading data in batches. See [creating and configuring hypertables](https://www.tigerdata.com/docs/learn/hypertables/creating-and-configuring-hypertables) for the exact commands.
1. Choose the time column (`TIMESTAMPTZ`, not null) and check constraints: unique and primary keys must include it.
2. Pick a chunk interval based on index growth and memory.
3. Convert in place for small tables, or create an empty hypertable and copy in batches for large ones. Plan a lock window or a cutover.
4. Review indexes after conversion, since the hypertable creates a default index on the time column.
5. Add columnstore and retention policies.
6. Compare query plans before and after, then retire the old table.
### From a standalone time-series database into Postgres
1. Inventory measurements, tags, fields and retention rules.
2. Map tags to columns, or to a metadata table such as `devices` with a foreign key from the readings table.
3. Create hypertables and policies on the target.
4. Dual-write new data to both systems from a known start time.
5. Backfill older data in time-ordered batches, then validate row counts and sample queries.
6. Cut reads over and retire the old system.
Tiger Docs cover the [dual-write and backfill method](https://www.tigerdata.com/docs/migrate/dual-write-and-backfill/dual-write-from-other), and a [self-hosted InfluxDB migration guide](https://www.tigerdata.com/docs/deploy/self-hosted/migration/migrate-influxdb) built around the open source Outflux tool, which supports earlier InfluxDB versions and not version 2 or later.
Three pitfalls come up on both paths. The chunk interval is easy to get wrong in either direction, and it can be changed for new chunks later. Indexes carried over from the old table may not suit the new layout. And queries written in a specialized language have to be rewritten in SQL, which is usually the largest part of the work. For broader context, see [how to choose a database](https://www.tigerdata.com/learn/how-to-choose-a-database-decision-framework-for-modern-applications), and for the managed route, [Tiger Cloud](https://www.tigerdata.com/cloud).
## Closing: a calibrated position
The "just use Postgres" view is right more often than vendors admit. Plain Postgres handles modest event data well, and a separate engine carries a real operating cost. The "Postgres doesn't scale for time series" view is right about plain row-store Postgres once tables, indexes and retention jobs grow. It is less right about Postgres with a time-series extension, which addresses those specific pressures while keeping SQL and joins. A dedicated time-series database still wins for extreme write-only metrics workloads with no relational context, or where an observability stack has already standardized on one.
If your events table is showing one or two of the signs of outgrowing a plain Postgres table, try [Tiger Cloud](https://www.tigerdata.com/cloud) and run your own queries against your own data. The open-source extension is on the [TimescaleDB page](https://www.tigerdata.com/timescaledb).