Plain Postgres handles time-series data well into the hundreds of millions of rows, provided the table is designed for it: narrow rows with timestamptz, declarative partitioning by time so old data drops in one statement, a BRIN index on the timestamp for range scans, and rollup tables refreshed on a schedule for the dashboards. The extension that most people reach for automates exactly those four things and adds compression. Reach for it when the data outgrows the manual version, not before.
The search results for this term are dominated by one extension’s README and its hosted product, with a forum thread from someone storing three billion points on a managed Postgres and a tutorial on date_trunc and generate_series in between. That spread is the honest picture: the extension is excellent, the built-in tools go further than the marketing suggests, and the person on the forum is the proof. This post is the built-in path, done properly, with a clear line for where it ends.
Table of contents
- The shape of time-series data
- Table design: narrow rows, right types
- Partition by time
- Indexes: BRIN for time, B-tree for the hot range
- Querying: date_trunc, generate_series and rollups
- When to reach for TimescaleDB
- How this fits the rest of the stack
- FAQ
The shape of time-series data
Time-series data is append-mostly, ordered by time, queried by time range, aggregated into buckets, and eventually deleted in bulk when it ages out. Metrics, sensor readings, events, prices, logs with a numeric payload. Almost nothing about that shape matches the default assumptions of a general-purpose table, which is tuned for rows that get updated in place and looked up by primary key.
Every technique below follows from one of those five properties. Append-mostly means the table only grows. Ordered by time means physical order on disk can match the query order. Range queries mean the index should be small and coarse. Bucketed aggregates mean precomputing them pays. Bulk deletion means partitions, because deleting a hundred million rows with DELETE is a multi-hour lock and a bloated table.
Table design: narrow rows, right types
Start with a table that stores nothing it does not need. A row of a timestamp, a small integer identifying the series, and one or two values is a few dozen bytes; a row that also carries the sensor name, location and unit as text on every reading is ten times that, multiplied by every reading forever. Put the descriptive columns in a sensors table and join when you need them.
CREATE TABLE readings (
ts timestamptz NOT NULL,
sensor_id integer NOT NULL REFERENCES sensors(id),
value real NOT NULL
) PARTITION BY RANGE (ts);Use timestamptz, never timestamp, so the data means the same thing when the server or the client changes time zone. Use real or double precision for measurements and numeric only for money. Skip the surrogate primary key; (sensor_id, ts) is the natural key and a serial column is eight bytes per row of nothing. The Postgres types post covers the width of each option.
Partition by time
Declarative partitioning splits the table into child tables by time range, and the planner reads only the partitions a query’s WHERE ts BETWEEN clause touches. Monthly partitions suit most workloads; daily for very high volume.
CREATE TABLE readings_2026_09 PARTITION OF readings
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE readings_2026_10 PARTITION OF readings
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');Retention becomes a metadata operation. Dropping a partition removes a month of data instantly, with no vacuum debt and no lock on the live table:
DROP TABLE readings_2025_09;The chore is creating partitions ahead of time, because an insert with no matching partition fails. A scheduled job that creates next month’s partition is enough; the pg_partman extension does it for you where it is available. Either way, the cron and at in Linux post is the scheduler half.
Indexes: BRIN for time, B-tree for the hot range
A B-tree on the timestamp of a billion-row table is tens of gigabytes and slows every insert. A BRIN index stores the minimum and maximum timestamp per block range of the table, which for data inserted in time order is a few megabytes and answers range queries almost as well.
CREATE INDEX readings_ts_brin ON readings USING brin (ts);
CREATE INDEX readings_sensor_ts ON readings (sensor_id, ts DESC);The second index is the one that serves latest-value-per-sensor queries, and it is a B-tree because it needs to be precise. Two indexes, both created on the parent so every partition inherits them. If the data arrives out of order, BRIN degrades; CLUSTER the closed partitions or accept the B-tree. The PostgreSQL EXPLAIN post is how to check which index a query actually used.
Querying: date_trunc, generate_series and rollups
The bucketed aggregate is the query time-series data exists for, and Postgres does it with date_trunc:
SELECT date_trunc('hour', ts) AS hour,
sensor_id,
avg(value), min(value), max(value), count(*)
FROM readings
WHERE ts >= now() - interval '7 days'
GROUP BY 1, 2
ORDER BY 1 DESC;Charts want a row for every bucket, including empty ones, which is what generate_series joined to the aggregate provides. And once the same hourly query runs on every dashboard load, precompute it: a readings_hourly table filled by a scheduled INSERT ... SELECT over the last few hours, queried instead of the raw table. That is a continuous aggregate built by hand, and for one or two rollups it is a dozen lines of SQL and a cron entry. The SQL query for the last year of data post covers the range predicates that keep these queries on the index.
When to reach for TimescaleDB
The extension automates partitioning as hypertables, adds time_bucket with gap-filling, maintains continuous aggregates incrementally, compresses closed chunks by ten to twenty times, and runs retention policies on a schedule. Every one of those is something the sections above did by hand, and the hand-built version has a limit.
- Compression. Plain Postgres has none for this shape beyond TOAST. Past a few hundred gigabytes, storage cost is the reason to move.
- Many rollups. Two hand-maintained aggregate tables are fine; ten with different intervals and late-arriving data are a maintenance burden the extension removes.
- Out-of-order ingest at high rates, where BRIN stops helping and the B-tree is too large.
- The team has no one who wants to own the partition-creation job.
The forum thread with three billion rows on plain managed Postgres shows the manual path holds a long way. On a managed database the extension’s availability depends on the provider, so the plain-Postgres design above is the portable one: it runs on any Postgres, including a managed instance on RunxBuild, where a BasicPlus plan at $20 a month (1 vCPU, 2GB) with scheduled backups and a private network handles a small metrics workload and scales to a larger plan when the partitions do. The databases on RunxBuild docs cover the plan ladder; the PostgreSQL performance post covers the settings that matter once the table is large.
How this fits the rest of the stack
Time series on plain Postgres is four decisions: narrow rows with timestamptz, partitions by time so retention is a DROP, a BRIN index for range scans and a B-tree for the latest value, and rollup tables refreshed on a schedule. That design carries hundreds of millions of rows on a modest instance and is portable to any Postgres. The extension starts paying for itself at compression and at many rollups, not at row one. The RunxBuild hosting calculator prices the database plan against the service that writes to it, so the storage line is a number before the first partition fills. Design the table for the query, and let the partitions do the deleting.
Useful related references:
- PostgreSQL Performance: Fix the Queries Before You Touch the Config
- PostgreSQL EXPLAIN: Reading a Query Plan Like Someone Who Has Fixed One
- Postgres Data Types: The Ones That Actually Change Your Schema
- Databases on RunxBuild
FAQ
Is PostgreSQL good for time-series data?
Yes, up to a large scale, if the table is designed for it: narrow rows, partitioning by time, a BRIN index on the timestamp and precomputed rollups for dashboards. Users report billions of rows on plain managed Postgres. Dedicated extensions add compression and automated aggregates, which matter past hundreds of gigabytes or with many rollups.
How many rows can Postgres handle for time series?
Hundreds of millions of rows per table is routine with partitioning, and billions are reported on modest hardware. The practical limits are storage cost, because plain Postgres does not compress this shape, and query time on aggregates that have not been precomputed. Both are addressed by rollup tables before they require an extension.
BRIN or B-tree index for a timestamp column?
BRIN for range queries on data inserted in time order: it is a fraction of the size and nearly as effective. B-tree for precise lookups such as the latest reading per sensor, typically as a composite index on the series id and timestamp. Most time-series tables carry one of each.
How do I delete old time-series data in Postgres?
Partition the table by time and drop the old partitions. A DROP TABLE on a monthly partition removes the data instantly with no vacuum cost, where a DELETE over the same rows would lock, bloat and take hours. Create partitions ahead of time with a scheduled job or the pg_partman extension.
TimescaleDB or plain Postgres?
Start plain if the workload is modest and portability matters: the manual design runs on any Postgres. Move to the extension when storage cost calls for compression, when the number of rollups becomes a maintenance burden, or when ingest is out of order at high rates. The migration is straightforward because the extension builds on the same partitioning ideas.