TimescaleDB
PostgreSQL extended for time-series — at PostgreSQL prices.
TimescaleDB is a PostgreSQL extension that turns Postgres into a high-performance time-series database. Same SQL, same drivers, same admin tooling — but with hypertables, columnar compression, continuous aggregates, and retention policies built in.
Deploy with Pier
- 1 Open the Pier dashboard and click Add service.
- 2 Pick TimescaleDB from the template list.
- 3 Choose the version, set a service name, and Pier provisions the container, storage, and ports automatically.
- 4 Attach a domain if you want HTTPS. Traefik auto-provisions the Let's Encrypt certificate.
What is TimescaleDB?
TimescaleDB is a PostgreSQL extension — not a separate database. You install it
into a regular Postgres cluster, run CREATE EXTENSION timescaledb, and your
existing tables can be turned into hypertables with a single function call.
Under the hood, a hypertable looks like a normal table to your application but is silently partitioned by time into “chunks” — typically one chunk per day or week. New writes flow into the latest chunk; old chunks can be compressed, reordered, or dropped automatically.
The result: you keep PostgreSQL’s transactional semantics, joins, foreign keys, indexes, JSONB, full-text search, geospatial (PostGIS), and the entire ecosystem of drivers, ORMs, and tools — while getting time-series performance comparable to purpose-built columnar databases.
How Pier deploys it
Pier uses the official timescale/timescaledb image from Docker Hub. The
default version is latest-pg17 (PostgreSQL 17 with the latest TimescaleDB
build); latest-pg18 is also available in the version selector. The data
volume mounts at /var/lib/postgresql/data — PostgreSQL’s standard PGDATA
location for this image.
When you create the service, Pier generates a strong password, provisions a persistent volume, exposes port 5432 internally on the Pier network, and optionally publishes it via Traefik. Backups, point-in-time restore, password rotation, and connection-string generation work the same as the regular PostgreSQL template — TimescaleDB is wire-compatible with Postgres, so the admin tooling is identical.
When NOT to use TimescaleDB
If your workload is pure OLTP with no time-series component, regular PostgreSQL is simpler and lighter. If you need petabyte-scale analytics over very wide columnar data and don’t need joins or mutability, ClickHouse may be a better fit. If you only need ephemeral metric scraping for a small fleet, vanilla Prometheus without long-term storage is usually enough.
Everywhere else — application metrics, IoT, events, logs, financial data, real-time dashboards — TimescaleDB hits the sweet spot of “boring SQL that scales.”
Key features
Hypertables
Auto-partitioned tables by time. Insert as you would into a regular table; the query planner transparently routes across chunks. Single-digit-millisecond ingest at hundreds of thousands of rows per second on a $5 VPS.
Continuous Aggregates
Incrementally-refreshed materialized views over time-bucketed data. Sub-second queries over years of raw rows without recomputing.
Columnar Compression
90–98% compression on time-series data with no query rewrites. Compressed chunks remain queryable and updateable.
Data Retention Policies
Drop old chunks automatically by age. No DELETE bloat, no manual vacuum tuning, no cron jobs.
100% PostgreSQL
psql, pg_dump, pgBouncer, Prisma, SQLAlchemy, GORM, Hasura, PostGIS — anything that talks PostgreSQL just works. Joins, foreign keys, transactions, ACID — all intact.
Built-in job scheduler
Background jobs for compression, retention, refresh and custom user functions — no external scheduler or cron container.
Use cases
Application metrics & APM
Replace Prometheus + remote-write stacks with a single Postgres-compatible store. Native Grafana TimescaleDB datasource.
IoT & sensor data
Ingest hundreds of thousands of points per second from devices. Query the last hour with millisecond latency, last 10 years from compressed chunks.
Financial tick data
OHLC bars via continuous aggregates, sub-second backtests, tick-level audit trail without giving up SQL joins.
Real-time analytics dashboards
Power BI / Metabase / Grafana dashboards directly off live event data. No ETL, no lambda architectures, no separate OLAP store.
Logs & events
Structured log storage with JSONB columns + time-series semantics. Drop ELK/Loki for low-to-mid log volumes.
Code examples
CREATE TABLE metrics (
time TIMESTAMPTZ NOT NULL,
device TEXT NOT NULL,
value DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time');
CREATE INDEX ON metrics (device, time DESC);CREATE MATERIALIZED VIEW metrics_5m
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', time) AS bucket,
device,
avg(value) AS avg,
max(value) AS max,
min(value) AS min,
count(*) AS n
FROM metrics
GROUP BY bucket, device;
SELECT add_continuous_aggregate_policy('metrics_5m',
start_offset => INTERVAL '7 days',
end_offset => INTERVAL '5 minutes',
schedule_interval => INTERVAL '5 minutes');ALTER TABLE metrics SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');
SELECT add_retention_policy('metrics', INTERVAL '1 year');SELECT time_bucket('1 hour', time) AS hour,
device,
avg(value)
FROM metrics
WHERE time > now() - INTERVAL '24 hours'
GROUP BY hour, device
ORDER BY hour;How it compares
| vs PostgreSQL | TimescaleDB IS PostgreSQL with a time-series extension. Use it whenever your workload includes time-series alongside transactional data — keep one database, keep Postgres ergonomics. |
| vs InfluxDB | Influx uses its own query language (Flux/InfluxQL) and has no joins, foreign keys, or SQL ecosystem. TimescaleDB keeps SQL, joins, transactions, and your existing ORM. |
| vs ClickHouse | ClickHouse wins on raw OLAP throughput at very high cardinality. TimescaleDB wins on row-level consistency, joins, mutability, and Postgres ecosystem fit. |
| vs Prometheus | Prometheus is a scraping monitoring system, not a long-term store. Use TimescaleDB as the durable backend (remote_write) and keep Prometheus for short-term scraping. |
Frequently asked questions
Is TimescaleDB free?
How is it different from plain PostgreSQL with a TIMESTAMPTZ column?
Can I use my existing ORM (Prisma, SQLAlchemy, GORM, ActiveRecord)?
What's the maximum data volume?
How do I migrate from InfluxDB or vanilla PostgreSQL?
Does it support continuous aggregates on hypertables with compression enabled?
What version does Pier deploy?
Related services
Deploy on your VPS
TimescaleDB is a PostgreSQL extension that turns Postgres into a high-performance time-series database. Same SQL, same drivers, same admin tooling — but with hypertables, columnar compression, continuous aggregates, and retention policies built in.
Deploy this service →