Quick Start

Create a hypertable

Convert a standard table to a hypertable using create_hypertable:

SELECT create_hypertable(
    'table_name',
    'time_column_name',
    'partitioning_column_name',
    number_partitions,
    'associated_schema_name',
    'associated_table_prefix',
    chunk_time_interval,
    create_default_indexes,
    if_not_exists,
    partitioning_func,
    migrate_data,
    chunk_target_size,
    chunk_sizing_func,
    time_partitioning_func
);

Change the partition interval

To change the chunk time interval after creating a hypertable:

SELECT set_chunk_time_interval(
    'table_name',
    chunk_time_interval,
    'dimension_name'
);

Example: create a hypertable for transaction data

This example creates a hypertable for financial transaction data, partitioned by day.

1. Create a standard table:

   CREATE TABLE transaction_data(
      tm TIMESTAMPTZ NOT NULL,
      id INT NOT NULL,
      price double precision);

2. Set the replication identity:

   ALTER TABLE transaction_data REPLICA IDENTITY DEFAULT;

3. Convert to a hypertable with a 1-day chunk interval:

   If the table already contains data, set migrate_data => true. Migration may take a long time for large tables.

   SELECT create_hypertable('transaction_data', 'tm', chunk_time_interval => INTERVAL '1 day');

4. (Optional) Change the chunk interval to 2 days:

   SELECT set_chunk_time_interval('transaction_data', chunk_time_interval => INTERVAL '2 day');

Choose an aggregation approach

Before creating a continuous aggregate, choose the right aggregation type for your use case:

Use a continuous aggregate when you need fast, recurring queries on large datasets. Enable real-time query on top if you also need the most recent, unaggregated data.

Create a continuous aggregate

A continuous aggregate is declared as a materialized view with timescaledb.continuous:

CREATE MATERIALIZED VIEW transaction_min_cagg
    WITH (timescaledb.continuous) -- Declare as a continuous aggregate
    AS
    SELECT id,
        time_bucket(INTERVAL '1 min', tm) AS bucket, -- Aggregate by 1-minute intervals
        AVG(price),
        MAX(price),
        MIN(price)
    FROM transaction_data
    GROUP BY id, bucket;

Results are stored in time-aligned chunks that match the underlying hypertable's partitioning. Continuous aggregates support incremental updates — only buckets with new or modified data are recomputed.

Enable real-time queries

By default, querying a continuous aggregate returns only completed aggregations. Enable real-time query to include newly ingested data that has not yet been aggregated:

-- Enable real-time query
ALTER MATERIALIZED VIEW transaction_min_cagg SET (timescaledb.materialized_only = false);

-- Disable real-time query
ALTER MATERIALIZED VIEW transaction_min_cagg SET (timescaledb.materialized_only = true);

Create nested aggregates

Build continuous aggregates on top of other continuous aggregates to reduce redundancy and improve performance. For example, create hourly aggregates from per-minute ones:

1. Create a per-minute aggregate:

   CREATE MATERIALIZED VIEW transaction_min_cagg
       WITH (timescaledb.continuous) AS
       SELECT id,
           time_bucket(INTERVAL '1 min', tm) AS bucket,
           AVG(price) AS avg,
           MAX(price) AS max,
           MIN(price) AS min
       FROM transaction_data
       GROUP BY id, bucket;

2. Create an hourly aggregate from the per-minute aggregate:

   CREATE MATERIALIZED VIEW transaction_one_hour_mview
       WITH (timescaledb.continuous) AS
       SELECT id,
           time_bucket(INTERVAL '1 hour', bucket) AS bucket,
           AVG(avg) AS avg,
           MAX(max) AS max,
           MIN(min) AS min
       FROM transaction_min_cagg
       GROUP BY id, time_bucket(INTERVAL '1 hour', bucket);

Refresh a continuous aggregate

refresh_continuous_aggregate(
    cagg     REGCLASS,
    window_start             "any",
    window_end               "any"
);

CALL refresh_continuous_aggregate('transaction_min_cagg', '2024-03-11 00:00:00+08'::timestamptz, NULL);

integer add_continuous_aggregate_policy(
    cagg REGCLASS,
    start_offset "any",
    end_offset "any",
    schedule_interval INTERVAL,
    if_not_exists BOOL = false,
    initial_start TIMESTAMPTZ = NULL,
    timezone TEXT = NULL
);

SELECT add_continuous_aggregate_policy('transaction_min_cagg',
    start_offset => '1 hour', -- Process data from 1 hour ago
    end_offset => INTERVAL '0', -- Process up to current time
    schedule_interval => INTERVAL '1 sec' -- Refresh every second
);