How to Use TimescaleDB: The Beginner's Guide (First Docker Run, First Hypertable, the Columnstore Rule, and the Continuous-Aggregate Lesson)

CodexFuse Team · 2026-11-03T11:19:13.650Z · 13 min read min read

If you already run PostgreSQL and your data arrives stamped with timestamps, you don't need a second database — you need this TimescaleDB tutorial. This guide shows you how to use TimescaleDB as a complete beginner: install it with Docker, create your first hypertable, turn on columnstore compression, build a continuous aggregate, and connect the whole thing to Grafana. Written as a TimescaleDB for beginners guide, it assumes nothing beyond basic SQL.

What Is TimescaleDB, Exactly?

TimescaleDB is a PostgreSQL extension that turns the PostgreSQL you already know into a database built for time-series data. It was built by Tiger Data — the company formerly known as Timescale, which renamed on June 17, 2025 — and the product name stays TimescaleDB. If your data arrives stamped with a timestamp (metrics, sensor readings, click events, prices), TimescaleDB gives you SQL-native tables that handle millions of rows without turning sluggish, while you keep every tool, driver, and BI connector that speaks PostgreSQL.

Two licenses cover the extension, and both are free to use. The core extension is Apache 2.0; advanced features such as columnstore compression and continuous aggregates sit under the Timescale License (TSL). The company also runs Tiger Cloud, the managed service, which offers a $1,000-credit, 30-day free trial. This guide is about TimescaleDB, the PostgreSQL time-series extension — not the Verilog `timescale compiler directive, GSAP's timeScale() animation method, or d3's scaleTime() chart axis, which share the name by coincidence.

What You Need Before You Start

You need three things: Docker installed and running, about 2 GB of free RAM for a local container, and enough SQL to write a CREATE TABLE and a SELECT. No production infrastructure is needed — the official install script is explicitly labeled "for local development and testing only." If you have never used Docker, read the Docker beginner guide first; you will be back in five minutes. This timescaledb docker walkthrough covers both the official script and a hand-written docker run.

How Do I Install TimescaleDB with Docker?

The fastest supported timescaledb install path is the official quickstart script. Open a terminal and run:

curl -sL https://tsdb.co/start-local | sh

This script downloads TimescaleDB, exposes PostgreSQL on port 6543 (deliberately not 5432, so it won't clash with any local Postgres), auto-tunes settings with timescaledb-tune, and sets up a persistent data volume. Port 6543 is one of the first things beginners trip over: if your psql defaults to 5432, every connection will fail.

Connect with:

psql -h localhost -p 6543 -U postgres

The password is password — fine for a local sandbox, never for anything real. Verify the extension loaded:

SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';

If you prefer to write your own docker run, pull a tagged image — there is no bare latest tag, so use timescale/timescaledb:latest-pg17 (Docker Hub marks it the "latest" equivalent) or a pinned tag such as 2.30.0-pg17. The HA variant is timescale/timescaledb-ha:pg17, but note its PGDATA lives at /home/postgres/pgdata/data, not the usual /var/lib/postgresql/data. Then run:

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg17

Whichever path you chose, run timescaledb-tune on a self-installed instance before first boot. The tool automatically configures your instance from your system's memory and CPU, writes shared_preload_libraries = 'timescaledb' into postgresql.conf, and recommends memory, parallelism, and WAL settings — but its changes take effect on the next restart, not the current session.

What Is a Hypertable, and When Should I Use One?

A hypertable is a normal PostgreSQL table with a time column that TimescaleDB automatically partitions into chunks. A timescaledb hypertable is created with one line, queried exactly like a regular table, and the partitioning is invisible to your SQL.

The decision rule is simple: if your rows arrive stamped with time and you will mostly query them by time range (last hour, last week, per-minute averages), make it a hypertable. Sensor readings, application metrics, logs, stock ticks, and IoT events all qualify. A table of user accounts does not — use a regular table for that. You can mix hypertables and regular tables in the same database and even join them, which is one of the real advantages of the PostgreSQL-extension approach.

How Do I Create My First Hypertable?

The current recommended syntax is a table option on CREATE TABLE:

1. Create the table with the hypertable option:

   CREATE TABLE sensor_data (
     time TIMESTAMPTZ NOT NULL,
     sensor_id INT NOT NULL,
     temperature DOUBLE PRECISION
   ) WITH ( tsdb.hypertable );

2. Confirm it converted:

   SELECT table_name FROM timescaledb_information.hypertables;

3. Insert a test row and query it like any table:

   INSERT INTO sensor_data VALUES (now(), 1, 21.5);
   SELECT time, temperature FROM sensor_data WHERE sensor_id = 1 ORDER BY time DESC LIMIT 5;

The WITH ( tsdb.hypertable ) form is current since version 2.20. Older tutorials use SELECT create_hypertable('sensor_data', 'time'); — that still works, but the new form is what the docs recommend today, and on 2.23+ the partitioning column defaults to the first timestamp-type column. TimescaleDB quietly partitions the data behind the scenes with a default chunk interval of 7 days, so one day of writes lands in the current chunk while old chunks sit still.

To practice at scale, the official quickstart loads about 90 days of 1-second readings across 10 sensors with generate_series — roughly 7,776,001 rows — so you can feel real time-series performance on your laptop instead of ten hand-written rows.

How Does the Automatic Partitioning Work?

Chunks are the whole reason hypertables stay fast. Each chunk is a narrow table holding one interval of time, and queries with a time filter only touch the chunks that overlap that filter — a technique called chunk exclusion. When you ask for "last 24 hours," TimescaleDB doesn't scan 90 days of data; it reads one or two chunks.

The default chunk interval is 7 days. For most sensor and metrics workloads, leave it alone until you understand your write pattern. You can also set it explicitly on creation with tsdb.chunk_interval = '1 day' or tsdb.partition_column = 'recorded_at' when the timestamp column has an unusual name. Chunks also power the two features coming next: compression and retention both operate at chunk granularity, not row granularity.

How Do I Turn On Columnstore Compression?

Time-series data compresses beautifully because consecutive rows look alike. TimescaleDB compression is the current answer to that — implemented as the columnstore: chunks past a certain age are rewritten column by column, which is what makes analytics queries fly and storage costs collapse.

Warning first: most blog tutorials on the internet teach the old names. Since 2.18, "compression" was renamed to "columnstore," and the old functions are deprecated (they still work, but new code should use the new ones). Here is the translation map:

Enable it with a policy so chunks convert automatically when they age:

SELECT add_columnstore_policy('sensor_data', after => INTERVAL '7 days');

Chunks older than 7 days move to the columnstore on their own schedule. Two details beginners miss: first, you can convert a chunk manually with CALL convert_to_columnstore(ch, recompress := true);. Second, for bulk loads, run SET timescaledb.enable_direct_compress_insert = on; before inserting so new rows write straight into the columnstore — otherwise every insert goes through a decompress-first path. In 2.21+ you can also pin the behavior at creation with the tsdb.columnstore table option.

How Do I Build My First Continuous Aggregate?

Continuous aggregates are materialized views that stay fresh automatically — the standard answer to "I need hourly averages over a year, and I need them now." TimescaleDB continuous aggregates are the reason most beginners adopt the extension: without one, your dashboard query scans billions of raw rows every time someone loads the page.

1. Create the aggregate over your hypertable:

   CREATE MATERIALIZED VIEW sensor_hourly
   WITH (timescaledb.continuous) AS
   SELECT time_bucket('1 hour', time) AS bucket,
          sensor_id,
          avg(temperature) AS avg_temp,
          max(temperature) AS max_temp
   FROM sensor_data
   GROUP BY bucket, sensor_id;

2. Schedule its refresh:

   SELECT add_continuous_aggregate_policy('sensor_hourly',
     start_offset => INTERVAL '2 days',
     end_offset => INTERVAL '1 hour',
     schedule_interval => INTERVAL '15 min');

3. Query it like a table:

   SELECT bucket, avg_temp FROM sensor_hourly
   WHERE sensor_id = 1 ORDER BY bucket DESC LIMIT 24;

time_bucket is TimescaleDB's workhorse function for rolling timestamps into fixed intervals. The refresh policy only re-computes recent buckets, so the hourly rollup over a year of data stays cheap. Enable real-time aggregation if you want queries to blend the materialized buckets with the newest un-refreshed rows — the honest default tradeoff is: fresher data costs a little extra query work on the live edge.

How Do I Stop the Database from Growing Forever?

Set a retention policy, and raw chunks past your cutoff are dropped automatically. For a hypertable keeping 30 days of raw readings:

SELECT add_retention_policy('sensor_data', drop_after => INTERVAL '30 days');

Retention works at chunk level, not row level: a whole chunk disappears once every row in it is older than the cutoff. The classic beginner ordering is this: continuous aggregates first, retention second — your 5-minute rollups and hourly rollups survive while the raw second-by-second data expires. One rule: keep retention longer than the continuous aggregate's refresh start_offset, or the aggregate can never re-compute the buckets it needs.

How Do I Connect Grafana to TimescaleDB?

The TimescaleDB Grafana connection needs no separate plugin — use Grafana's built-in PostgreSQL data source, because TimescaleDB speaks the standard PostgreSQL wire protocol. Add a PostgreSQL data source with your host (localhost), port (6543 for the quickstart script, 5432 for a plain Docker run), database, user, and password — then flip on the TimescaleDB toggle in the data source settings.

That toggle matters: with it enabled, Grafana uses TimescaleDB's time_bucket in the $__timeGroup macro and shows TimescaleDB-specific aggregates in the query builder. A typical dashboard panel then looks like:

SELECT $__timeGroup(time, '5m') AS "time",
       avg(temperature) AS "Temperature"
FROM sensor_data
WHERE $__timeFilter(time) AND sensor_id = 1
GROUP BY 1 ORDER BY 1;

For the full walkthrough of panels, variables, and alerts, follow the Grafana beginner guide — it assumes any SQL data source, and TimescaleDB slots in cleanly.

TimescaleDB vs InfluxDB: Which Should I Pick?

TimescaleDB is a PostgreSQL extension; InfluxDB is a purpose-built time-series database with its own data model. They solve the same problem from opposite directions. Our InfluxDB beginner guide covers that side in depth — here is the honest qualitative comparison:

No benchmark numbers here on purpose — published benchmarks always favor whoever ran them. The real deciding question is which model matches your team: if you already live in PostgreSQL, TimescaleDB removes an entire second database from your architecture; if your whole world is metrics pipelines, InfluxDB's purpose-built model may fit better.

Five Beginner Mistakes to Avoid

What Should I Learn Next?

Once the arc above works end to end, the natural next steps are tuning tsdb.chunk_interval to your write volume, exploring the TimescaleDB Toolkit's hyperfunctions for analytics, and wiring a real continuous aggregate into a Grafana dashboard. If you outgrow a laptop container, Tiger Cloud's managed trial is the low-effort way to see how the same hypertables behave on real infrastructure. And if your team is choosing between the two time-series philosophies, the InfluxDB beginner guide gives you the other side of the decision.

Sources

Every fact in this guide was verified against a source fetched this run. The official TimescaleDB quickstart (tigerdata.com, crawled 2026-11-03) documents the install script, port 6543, the tsdb.hypertable syntax, and the 7.7M-row sample load; the timescaledb-tune documentation (tigerdata.com, crawled 2026-11-03) covers auto-configuration; the columnstore documentation confirms the 2.18 renames of add_columnstore_policy, convert_to_columnstore, and the hypercore alter form; Docker Hub (crawled 2026-11-02) documents the latest-pg17 tag and versioned tags such as 2.30.0-pg17 and 2.31.0-pg18; encore.dev (updated ~14 days ago) documents the 2.21 tsdb.columnstore option; aicassindra.com (Oct 2026) documents the 2.20 WITH (tsdb.hypertable) form and named-arg retention policies; amireslampanah.com and baeldung.com carry the continuous-aggregate examples; Grafana Cloud documentation (reviewed Aug 2026) documents the PostgreSQL data source with the TimescaleDB toggle and time_bucket in $__timeGroup; db-engines.com (crawled 2026-11-02) documents the Apache 2.0 + Timescale License split; the AWS Marketplace listing and Tiger Cloud docs (crawled 2026-11-02) document the $1,000, 30-day trial credits; the Tiger Data newsroom (fetched 2026-11-03) documents the June 17, 2025 rename from Timescale; tigerdata.com/compare/influxdb (crawled 2026-11-02) documents InfluxDB v3's SQL-over-DataFusion/Arrow/Parquet and the dropped Flux support.

Frequently Asked Questions

Is TimescaleDB free to use?

Yes. The core extension is Apache 2.0 and advanced features such as columnstore compression and continuous aggregates sit under the Timescale License — both are free to use. The managed Tiger Cloud service is paid, but offers $1,000 in credits for a 30-day trial.

Is TimescaleDB a separate database from PostgreSQL?

No — it is a PostgreSQL extension. You install it into PostgreSQL, your data stays in PostgreSQL, and you query it with standard SQL. That is why every PostgreSQL tool, driver, and BI connector works with it unchanged.

What is the difference between TimescaleDB and InfluxDB?

TimescaleDB extends PostgreSQL with full SQL, ACID transactions, and joins against relational tables. InfluxDB is a purpose-built time-series database with its own data model (measurements, tags, fields) and, in v3, a SQL interface built on DataFusion, Arrow, and Parquet. Pick TimescaleDB if you already live in PostgreSQL; pick InfluxDB for pure monitoring pipelines built around its own model.

Why do old tutorials say create_hypertable()?

That was the classic API for years and it still works. Since version 2.20 the recommended form is CREATE TABLE ... WITH ( tsdb.hypertable ). Old compression function names such as add_compression_policy were likewise renamed to columnstore names in 2.18 — worth knowing when copying code from dated tutorials.

How is TimescaleDB different from Timescale?

Timescale was the company name until June 17, 2025, when it renamed to Tiger Data. TimescaleDB is the product and keeps its name. Don't confuse it with the Verilog `timescale compiler directive or GSAP's timeScale() — same word, unrelated things.