TimescaleDB

Create a hypertable

You create a regular table and then convert it into a hypertable. A hypertable automatically partitions data into chunks to accelerate your queries.

-- Create timescaledb extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create a regular SQL table
CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

-- Convert the table into a hypertable that is partitioned by time
SELECT create_hypertable('conditions', by_range('time'));

See more:

• About hypertables
• API reference

Enable columnstore

TimescaleDB’s hypercore is a hybrid row-columnar store that boosts analytical query performance on your time-series and event data, while reducing data size by more than 90%. This keeps your analytics operating at lightning speed and ensures low storage costs as you scale. Data is inserted in row format in the rowstore and converted to columnar format in the columnstore based on your configuration.

• Configure the columnstore on a hypertable:

  ALTER TABLE conditions SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'location'
  );

  • Create a policy to automatically convert chunks in row format that are older than seven days to chunks in the columnar format:

  SELECT add_compression_policy('conditions', INTERVAL '7 days');

See more:

• About columnstore
• Enable columnstore manually
• API reference

Insert and query data

Insert and query data in a hypertable via regular SQL commands. For example:

• Insert data into a hypertable named conditions:

  INSERT INTO conditions
    VALUES
      (NOW(), 'office',   70.0, 50.0),
      (NOW(), 'basement', 66.5, 60.0),
      (NOW(), 'garage',   77.0, 65.2);

  • Return the number of entries written to the table conditions in the last 12 hours:

  SELECT
    COUNT(*)
  FROM
    conditions
  WHERE
    time > NOW() - INTERVAL '12 hours';

See more:

• Query data
• Write data

Create time buckets

Time buckets enable you to aggregate data in hypertables by time interval and calculate summary values.

For example, calculate the average daily temperature in a table named conditions. The table has a time and temperature columns:

SELECT
  time_bucket('1 day', time) AS bucket,
  AVG(temperature) AS avg_temp
FROM
  conditions
GROUP BY
  bucket
ORDER BY
  bucket ASC;

See more:

• About time buckets
• API reference
• All TimescaleDB features
• Tutorials

Create continuous aggregates

Continuous aggregates make real-time analytics run faster on very large datasets. They continuously and incrementally refresh a query in the background, so that when you run such query, only the data that has changed needs to be computed, not the entire dataset. This is what makes them different from regular PostgreSQL materialized views, which cannot be incrementally materialized and have to be rebuilt from scratch every time you want to refresh it.

For example, create a continuous aggregate view for daily weather data in two simple steps:

1. Create a materialized view:

   CREATE MATERIALIZED VIEW conditions_summary_daily
   WITH (timescaledb.continuous) AS
   SELECT
     location,
     time_bucket(INTERVAL '1 day', time) AS bucket,
     AVG(temperature),
     MAX(temperature),
     MIN(temperature)
   FROM
     conditions
   GROUP BY
     location,
     bucket;

1. Create a policy to refresh the view every hour:

   SELECT
     add_continuous_aggregate_policy(
       'conditions_summary_daily',
       start_offset => INTERVAL '1 month',
       end_offset => INTERVAL '1 day',
       schedule_interval => INTERVAL '1 hour'
   );

See more:

• About continuous aggregates
• API reference

Documentation associated with Apache licensed “community edition” of TimescaleDB provided for convenience and built from Apache licensed source code available here.