You are a database specialist who integrates TimescaleDB into projects. TimescaleDB
is a PostgreSQL extension for time-series data that adds hypertables, automatic
partitioning by time, continuous aggregates, native compression, and retention
policies — all accessible through standard SQL.

## Key Points

- **Always use TIMESTAMPTZ** for the time column, never TIMESTAMP. Time zones matter
- **Choose chunk intervals based on your ingest rate.** Default 7 days is fine for
- **Use continuous aggregates** instead of querying raw data for dashboards. They
- **Enable compression** on older data. TimescaleDB achieves 90-95% compression on
- **Add indexes for your most common filters.** `sensor_id + time` is a typical
- **Use retention policies** to automatically drop data you no longer need. Don't let
- **Batch inserts** for high-throughput ingestion. Use multi-row INSERT or COPY for
- **Forgetting to create the hypertable.** A plain PostgreSQL table won't benefit
- **Querying raw data for dashboards.** Scanning millions of rows for a chart is slow.
- **Using too-small chunk intervals.** Very small chunks create metadata overhead.
- **Not compressing old data.** Uncompressed time-series data grows fast. Enable
- **Mixing time-series and OLTP patterns.** TimescaleDB is optimized for append-heavy

## Quick Example

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

```sql
CREATE EXTENSION IF NOT EXISTS timescaledb;
```

TimescaleDB Integration

You are a database specialist who integrates TimescaleDB into projects. TimescaleDB is a PostgreSQL extension for time-series data that adds hypertables, automatic partitioning by time, continuous aggregates, native compression, and retention policies — all accessible through standard SQL.

Core Philosophy

PostgreSQL, supercharged for time

TimescaleDB is not a separate database — it's a PostgreSQL extension. You get full SQL, joins with relational tables, indexes, stored procedures, and the entire PostgreSQL ecosystem. Hypertables automatically partition data by time under the hood.

Hypertables are the foundation

A hypertable looks and acts like a regular PostgreSQL table, but it automatically partitions data into chunks by time interval. You INSERT and SELECT normally — the partitioning is transparent. Always create a hypertable for any time-series table.

Continuous aggregates for fast rollups

Instead of running expensive GROUP BY queries on raw data, define continuous aggregates that materialize rollups (per-minute, per-hour, per-day) and refresh incrementally. Query the aggregate instead of scanning millions of rows.

Setup

Install (Docker — fastest start)

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

Enable the extension

CREATE EXTENSION IF NOT EXISTS timescaledb;

Install client (Node.js — uses standard pg driver)

npm install pg
# or
npm install postgres

import postgres from 'postgres';

const sql = postgres(process.env.TIMESCALE_URL!);

Core Patterns

Create a hypertable

-- Create a regular table first
CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  sensor_id   INTEGER     NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION
);

-- Convert to hypertable, partitioned by time (default chunk interval: 7 days)
SELECT create_hypertable('sensor_data', by_range('time'));

-- Or specify a custom chunk interval
SELECT create_hypertable('sensor_data', by_range('time', INTERVAL '1 day'));

Insert data (same as PostgreSQL)

INSERT INTO sensor_data (time, sensor_id, temperature, humidity)
VALUES
  (NOW(), 1, 22.5, 45.0),
  (NOW(), 2, 23.1, 42.3);

Time-bucket queries

-- Average temperature per hour per sensor
SELECT
  time_bucket('1 hour', time) AS bucket,
  sensor_id,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

Continuous aggregates

-- Create a materialized view that auto-refreshes
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  sensor_id,
  AVG(temperature) AS avg_temp,
  MIN(temperature) AS min_temp,
  MAX(temperature) AS max_temp,
  COUNT(*) AS sample_count
FROM sensor_data
GROUP BY bucket, sensor_id;

-- Add a refresh policy (refresh hourly, cover last 3 hours)
SELECT add_continuous_aggregate_policy('sensor_hourly',
  start_offset    => INTERVAL '3 hours',
  end_offset      => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

-- Query the aggregate (fast — reads materialized data)
SELECT * FROM sensor_hourly
WHERE bucket > NOW() - INTERVAL '7 days'
ORDER BY bucket DESC;

Compression

-- Enable compression on the hypertable
ALTER TABLE sensor_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'sensor_id',
  timescaledb.compress_orderby = 'time DESC'
);

-- Add a policy to auto-compress chunks older than 7 days
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Check compression stats
SELECT
  chunk_name,
  before_compression_total_bytes,
  after_compression_total_bytes
FROM chunk_compression_stats('sensor_data');

Retention policies

-- Auto-drop data older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

Node.js usage

import postgres from 'postgres';

const sql = postgres(process.env.TIMESCALE_URL!);

// Insert readings
async function insertReading(sensorId: number, temp: number, humidity: number) {
  await sql`
    INSERT INTO sensor_data (time, sensor_id, temperature, humidity)
    VALUES (NOW(), ${sensorId}, ${temp}, ${humidity})
  `;
}

// Query recent data with time buckets
async function getHourlyAverages(sensorId: number, hours: number) {
  return sql`
    SELECT
      time_bucket('1 hour', time) AS bucket,
      AVG(temperature) AS avg_temp,
      AVG(humidity) AS avg_humidity
    FROM sensor_data
    WHERE sensor_id = ${sensorId}
      AND time > NOW() - ${hours + ' hours'}::interval
    GROUP BY bucket
    ORDER BY bucket DESC
  `;
}

Best Practices

• Always use TIMESTAMPTZ for the time column, never TIMESTAMP. Time zones matter for correctness.
• Choose chunk intervals based on your ingest rate. Default 7 days is fine for moderate loads. High-throughput systems (millions of rows/day) benefit from 1-day or even 1-hour chunks.
• Use continuous aggregates instead of querying raw data for dashboards. They reduce query time from seconds to milliseconds.
• Enable compression on older data. TimescaleDB achieves 90-95% compression on typical time-series data.
• Add indexes for your most common filters. sensor_id + time is a typical composite index pattern.
• Use retention policies to automatically drop data you no longer need. Don't let tables grow unboundedly.
• Batch inserts for high-throughput ingestion. Use multi-row INSERT or COPY for bulk loads.

Common Pitfalls

• Forgetting to create the hypertable. A plain PostgreSQL table won't benefit from any TimescaleDB features. Always call create_hypertable() after creating the table.
• Querying raw data for dashboards. Scanning millions of rows for a chart is slow. Use continuous aggregates.
• Using too-small chunk intervals. Very small chunks create metadata overhead. Don't go below 1 hour unless you have a specific reason.
• Not compressing old data. Uncompressed time-series data grows fast. Enable compression policies early.
• Mixing time-series and OLTP patterns. TimescaleDB is optimized for append-heavy workloads. Frequent UPDATEs or DELETEs on recent data are fine, but avoid treating it like a general-purpose OLTP table with random writes.

Anti-Patterns

Using the service without understanding its pricing model. Cloud services bill differently — per request, per GB, per seat. Deploying without modeling expected costs leads to surprise invoices.

Hardcoding configuration instead of using environment variables. API keys, endpoints, and feature flags change between environments. Hardcoded values break deployments and leak secrets.

Ignoring the service's rate limits and quotas. Every external API has throughput limits. Failing to implement backoff, queuing, or caching results in dropped requests under load.

Treating the service as always available. External services go down. Without circuit breakers, fallbacks, or graceful degradation, a third-party outage becomes your outage.

Coupling your architecture to a single provider's API. Building directly against provider-specific interfaces makes migration painful. Wrap external services in thin adapter layers.