codeYaan Logo Blog
Home Blog Services Courses
Login
TimescaleDB: Time-Series Data Tutorial
PROGRAMMING LANGUAGES Feb. 2, 2026, 5:30 p.m.

TimescaleDB: Time-Series Data Tutorial

TimescaleDB has become the go‑to extension for PostgreSQL when you need to store, query, and analyze massive streams of time‑series data. It blends the reliability of a relational database with the performance tricks of a purpose‑built time‑series engine, letting you write familiar SQL while handling millions of rows per second. In this tutorial we’ll walk through the core concepts, set up a local instance, and build a few practical examples that you can adapt to IoT sensors, financial tick data, or application metrics.

What Makes TimescaleDB Different?

At its heart TimescaleDB is an open‑source PostgreSQL extension. It doesn’t replace PostgreSQL; it augments it with hypertables, automatic partitioning, and compression. A hypertable looks like a regular table to the user, but under the hood it’s split into many smaller chunks based on time (and optionally another dimension). This chunking enables fast inserts and efficient range queries without the developer having to manage partitions manually.

Beyond partitioning, TimescaleDB offers continuous aggregates (materialized views that stay up‑to‑date), data retention policies, and native support for time‑bucket functions. All of these features are accessed via standard SQL, which means you can leverage existing tools, ORMs, and BI platforms without learning a new query language.

Installing TimescaleDB

The quickest way to get started is with Docker. The official TimescaleDB image bundles PostgreSQL 16 and the extension pre‑installed. If you prefer a native install, Timescale provides apt, yum, and Homebrew packages that integrate with your existing PostgreSQL server.

docker run -d \
  --name timescaledb \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  timescale/timescaledb:latest-pg16

Once the container is running, connect with any PostgreSQL client. For example, using psql:

psql -h localhost -U postgres -W
Password: secret

Enable the extension in the database where you’ll store your time‑series data:

CREATE EXTENSION IF NOT EXISTS timescaledb;

Creating a Hypertable

Imagine you are collecting temperature readings from a fleet of weather stations. Each reading includes a timestamp, station ID, temperature, and humidity. First, define a regular PostgreSQL table that captures this schema.

CREATE TABLE sensor_readings (
    time        TIMESTAMPTZ       NOT NULL,
    station_id  INTEGER          NOT NULL,
    temperature DOUBLE PRECISION NOT NULL,
    humidity    DOUBLE PRECISION NOT NULL
);

Now turn it into a hypertable. The time column is the primary time dimension; you can also specify a second “space” dimension (here station_id) to improve query parallelism.

SELECT create_hypertable(
    'sensor_readings',
    'time',
    'station_id',
    4,               -- number of space partitions (optional)
    chunk_time_interval => INTERVAL '1 day'
);

TimescaleDB will automatically create daily chunks for each station, distributing data across internal partitions while preserving the logical view of a single table.

Inserting Data Efficiently

Bulk inserts are where TimescaleDB shines. You can stream data directly from Python, Go, or any language with a PostgreSQL driver. Below is a Python snippet using psycopg2 that simulates 10,000 sensor readings and inserts them in batches of 1,000 rows.

import random
import datetime
import psycopg2
from psycopg2.extras import execute_values

conn = psycopg2.connect(
    dbname="postgres",
    user="postgres",
    password="secret",
    host="localhost",
    port=5432
)
cur = conn.cursor()

def generate_batch(batch_size=1000):
    now = datetime.datetime.utcnow()
    rows = []
    for _ in range(batch_size):
        ts = now - datetime.timedelta(seconds=random.randint(0, 86400))
        station = random.randint(1, 50)
        temp = round(random.uniform(-10, 35), 2)
        hum = round(random.uniform(20, 90), 2)
        rows.append((ts, station, temp, hum))
    return rows

for _ in range(10):  # 10 batches → 10,000 rows
    batch = generate_batch()
    sql = """
        INSERT INTO sensor_readings (time, station_id, temperature, humidity)
        VALUES %s
    """
    execute_values(cur, sql, batch)
    conn.commit()
    print(f"Inserted batch of {len(batch)} rows")

cur.close()
conn.close()

Notice the use of execute_values which sends the entire batch in a single network round‑trip, dramatically reducing latency compared to row‑by‑row inserts.

Basic Time‑Series Queries

Once data is in place, you can run familiar SQL queries. The time_bucket function is the workhorse for grouping data into regular intervals (e.g., 5‑minute windows).

SELECT
    time_bucket('5 minutes', time) AS bucket,
    AVG(temperature) AS avg_temp,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp
FROM sensor_readings
WHERE station_id = 12
  AND time > now() - INTERVAL '1 day'
GROUP BY bucket
ORDER BY bucket;

This query returns a compact time series of average, minimum, and maximum temperatures for station 12 over the past 24 hours, bucketed into five‑minute slices. Because the data lives in a hypertable, PostgreSQL can prune irrelevant chunks early, making the query fast even on millions of rows.

Continuous Aggregates

For dashboards that refresh every few seconds, recomputing aggregates on the fly can become a bottleneck. TimescaleDB’s continuous aggregates let you materialize the result of a query and keep it up‑to‑date as new rows arrive.

CREATE MATERIALIZED VIEW station_12_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    AVG(temperature) AS avg_temp,
    MAX(humidity) AS max_humidity
FROM sensor_readings
WHERE station_id = 12
GROUP BY hour;

After the view is created, you can query it just like a regular table. TimescaleDB will automatically refresh the view in the background, but you can also trigger a manual refresh for back‑filled data.

REFRESH MATERIALIZED VIEW station_12_hourly;

The materialized view stores only the aggregated rows, dramatically reducing I/O and CPU for downstream analytics.

Data Retention & Compression

Time‑series data grows quickly, and you rarely need raw granularity after a certain age. TimescaleDB provides policies to drop old chunks and compress historical data without losing queryability.

Retention Policy

SELECT add_retention_policy(
    'sensor_readings',
    INTERVAL '90 days'   -- keep only the last 90 days
);

This policy runs automatically and removes chunks older than 90 days. You can also combine it with a DROP policy that runs during off‑peak hours to avoid impacting production workloads.

Compression Policy

ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'station_id'
);

SELECT add_compression_policy(
    'sensor_readings',
    INTERVAL '30 days'   -- compress data older than 30 days
);

Compressed chunks use columnar storage and delta encoding, offering up to 10× space savings while still supporting most SELECT queries (except those that need to update rows).

Pro tip: Schedule the compression policy to run at night and keep the retention policy separate. This way you retain the most recent raw data for debugging, while older data lives in a highly compressed form that still answers analytical queries efficiently.

Real‑World Use Cases

  • IoT Device Monitoring: Thousands of sensors report metrics every second. Hypertables let you ingest this stream with minimal latency, and continuous aggregates power real‑time dashboards showing per‑device health.
  • Financial Tick Data: Stock exchanges generate millions of price updates per day. TimescaleDB’s chunking ensures that queries for recent ticks stay fast, while older data can be compressed for long‑term storage.
  • Application Performance Monitoring (APM): Services emit metrics like CPU usage, request latency, and error rates. By storing them in a hypertable, you can run ad‑hoc queries to pinpoint spikes, and use retention policies to keep only the last 30 days of high‑resolution data.

Advanced Query Patterns

TimescaleDB supports window functions, lag/lead analysis, and even joins across hypertables. Below is an example that calculates the temperature delta between successive readings for each station.

SELECT
    station_id,
    time,
    temperature,
    temperature - LAG(temperature) OVER (
        PARTITION BY station_id
        ORDER BY time
    ) AS temp_change
FROM sensor_readings
WHERE time > now() - INTERVAL '6 hours'
ORDER BY station_id, time;

Because the query only touches the most recent six‑hour chunks, it runs quickly even on a table that holds years of data.

Integrating with Visualization Tools

Most modern BI tools (Grafana, Superset, Metabase) can connect to PostgreSQL directly. Once you point them at your TimescaleDB instance, you can plot the continuous aggregates or raw data without any extra configuration. Grafana, for instance, has a dedicated TimescaleDB data source plugin that automatically exposes time‑bucket functions as query helpers.

When building dashboards, prefer querying the materialized view for high‑level trends and fall back to the raw hypertable for drill‑down analysis. This pattern balances performance and detail.

Best Practices Checklist

  1. Define a clear time column with TIMESTAMPTZ and always store in UTC.
  2. Use create_hypertable with an appropriate chunk_time_interval (daily for high‑frequency data, weekly for slower streams).
  3. Batch inserts using execute_values or COPY for bulk loads.
  4. Leverage time_bucket for regular interval grouping; avoid custom date arithmetic in WHERE clauses.
  5. Set up continuous aggregates for any query that runs frequently on the same time window.
  6. Apply compression after the data has “settled” (e.g., 30 days) to maximize space savings.
  7. Implement retention policies to keep storage costs predictable.
Pro tip: When you anticipate a high write rate, disable autovacuum on the raw hypertable and let TimescaleDB’s background workers handle cleanup. Re‑enable autovacuum on compressed chunks to keep index bloat under control.

Putting It All Together – A Mini Project

Let’s build a tiny “weather station” service that ingests data, stores it in TimescaleDB, and exposes a REST endpoint for the latest hourly aggregates. The project uses FastAPI and asyncpg for async PostgreSQL access.

# app.py
import os
from fastapi import FastAPI, HTTPException
import asyncpg
import datetime

app = FastAPI()
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:secret@localhost:5432/postgres")

@app.on_event("startup")
async def startup():
    app.state.pool = await asyncpg.create_pool(DATABASE_URL)

@app.post("/ingest/")
async def ingest(station_id: int, temperature: float, humidity: float):
    async with app.state.pool.acquire() as conn:
        await conn.execute(
            """
            INSERT INTO sensor_readings (time, station_id, temperature, humidity)
            VALUES ($1, $2, $3, $4)
            """,
            datetime.datetime.utcnow(),
            station_id,
            temperature,
            humidity,
        )
    return {"status": "ok"}

@app.get("/hourly/{station_id}")
async def hourly(station_id: int):
    async with app.state.pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            SELECT hour, avg_temp, max_humidity
            FROM station_12_hourly
            WHERE station_id = $1
            ORDER BY hour DESC
            LIMIT 1
            """,
            station_id,
        )
        if not row:
            raise HTTPException(status_code=404, detail="No data")
        return {"hour": row["hour"], "avg_temp": row["avg_temp"], "max_humidity": row["max_humidity"]}

# Run with: uvicorn app:app --reload

Deploy this service alongside your TimescaleDB container, and you now have a fully functional pipeline: sensors push JSON payloads, the API writes to a hypertable, and a continuous aggregate powers a real‑time dashboard. The code is deliberately concise, but you can extend it with authentication, batch ingestion endpoints, or additional metrics.

Performance Tuning Highlights

  • Chunk Size: Smaller chunks (e.g., 1 hour) improve query pruning for very recent data but increase metadata overhead. Start with 1‑day chunks and adjust after monitoring pg_stat_user_tables.
  • Indexes: A primary key on (time, station_id) is automatically added by create_hypertable. Add secondary indexes (e.g., on temperature) only if you filter on those columns frequently.
  • Parallelism: Enable PostgreSQL’s parallel query execution (max_parallel_workers_per_gather) to let multiple CPU cores scan chunks concurrently.
  • Connection Pooling: Use a pooler like PgBouncer in transaction mode to reduce connection churn, especially for high‑frequency ingestion.

Monitoring TimescaleDB Itself

TimescaleDB exposes several internal functions that help you keep tabs on chunk health, compression status, and background worker activity.

SELECT * FROM timescaledb_information.hypertables;
SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'sensor_readings';
SELECT * FROM timescaledb_information.compression_settings;
SELECT * FROM timescaledb_information.jobs;  -- shows retention/compression jobs

Integrate these queries into your monitoring stack (Prometheus + Grafana) to alert if, for example, compression jobs are lagging or chunk creation is outpacing your storage capacity.

Conclusion

TimescaleDB bridges the gap between traditional relational databases and specialized time‑series engines, giving you the best of both worlds: familiar SQL, robust ACID guarantees, and performance tricks tuned for chronological data. By defining hypertables, leveraging continuous aggregates, and applying retention/compression policies, you can build scalable pipelines that ingest millions of rows per second while keeping queries lightning‑fast. Whether you’re tracking IoT sensors, financial ticks, or application metrics, the patterns covered here provide a solid foundation you can extend to meet any production workload.

Share this article
Back to Blog