TimescaleDB Hypertables
Convert a Postgres table into a TimescaleDB hypertable, with automatic time partitioning by chunk interval.
TimescaleDB Hypertables is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is TimescaleDB?
A PostgreSQL extension built for time-series data. Adds automatic time-partitioning, compression, retention, and continuous aggregates. Used standalone or alongside regular Postgres.
Hypertable
A logically single table that's physically split into "chunks" by time:
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE metrics (
ts TIMESTAMPTZ NOT NULL,
device_id BIGINT NOT NULL,
value DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'ts');
-- metrics is now a hypertable, chunks created automatically.Chunk Time Interval
Default chunk = 7 days. Tune for your data velocity (smaller chunks for high-velocity data):
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');
-- One chunk per day, smaller and faster to drop.Inserting Data
Just normal INSERT — TimescaleDB routes rows to the right chunk:
INSERT INTO metrics (ts, device_id, value)
VALUES (NOW(), 1, 42.5);Querying Hypertables
Looks like a normal table. The planner prunes chunks for time-bounded queries:
SELECT * FROM metrics
WHERE ts >= NOW() - INTERVAL '7 days'
ORDER BY ts DESC LIMIT 100;
-- Only the relevant chunks are read.time_bucket: Smarter date_trunc
TimescaleDB's grouping helper:
SELECT time_bucket('1 hour', ts) AS hour,
AVG(value) AS avg_value
FROM metrics
WHERE device_id = 42
AND ts >= NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;Multi-Column Partitioning
For multi-tenant workloads, also partition by device_id:
SELECT create_hypertable('metrics', 'ts', 'device_id', 4);
-- Hash-partitions device_id into 4 space partitions per time chunk.Querying With Both Filters
Best performance when both partition dimensions are filtered:
SELECT * FROM metrics
WHERE device_id = 42
AND ts >= NOW() - INTERVAL '7 days';
-- Prunes by both time and space.Indexes on Hypertables
Indexes propagate to every chunk:
CREATE INDEX metrics_device_idx ON metrics (device_id, ts DESC);
-- Composite index ideal for 'recent data for one device' queries.Updating Hypertables
UPDATEs and DELETEs work but should be rare on time-series. Old data is usually append-only and immutable.
Migrating From Regular Tables
Existing tables can be converted to hypertables:
SELECT create_hypertable('metrics', 'ts', migrate_data => true);Recap
Hypertables = automatic time partitioning for Postgres.
- One logical table, many physical chunks
- create_hypertable + chunk_time_interval
- time_bucket for bucketed aggregations
- Indexes propagate to all chunks
Quick Check
You're ingesting IoT metrics at 100k rows/sec. Why convert the table to a TimescaleDB hypertable?
Frequently asked questions
Is the “TimescaleDB Hypertables” lesson free?
Yes — the full text of “TimescaleDB Hypertables” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “TimescaleDB Hypertables”?
Convert a Postgres table into a TimescaleDB hypertable, with automatic time partitioning by chunk interval. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “TimescaleDB Hypertables” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- TimescaleDB Hypertables
- Continuous Aggregates
- Compression and Retention Policies
- Time-Series Index Choices