Hypercore

reference information about the TimescaleDB hybrid row-columnar storage engine

Since 2.18.0

is a hybrid row-columnar storage engine in . It is designed specifically for real-time analytics and powered by time-series data. The advantage of is its ability to seamlessly switch between row-oriented and column-oriented storage, delivering the best of both worlds:

solves the key challenges in real-time analytics:

• High ingest throughput
• Low-latency ingestion
• Fast query performance
• Efficient handling of data updates and late-arriving data
• Streamlined data management

‘s hybrid approach combines the benefits of row-oriented and column-oriented formats:

• Fast ingest with : new data is initially written to the , which is optimized for high-speed inserts and updates. This process ensures that real-time applications easily handle rapid streams of incoming data. Mutability—upserts, updates, and deletes happen seamlessly.

• Efficient analytics with : as the data cools and becomes more suited for analytics, it is automatically converted to the . This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space.

• Faster queries on compressed data in : in the conversion, s are compressed by up to 98%, and organized for efficient, large-scale queries. Combined with chunk skipping, this helps you save on storage costs and keeps your queries operating at lightning speed.

• Fast modification of compressed data in : just use SQL to add or modify data in the . is optimized for superfast INSERT and UPSERT performance.

• Full mutability with transactional semantics: regardless of where data is stored, provides full ACID support. Like in a vanilla database, inserts and updates to the and are always consistent, and available to queries as soon as they are completed.

For an in-depth explanation of how s and work, see the Data model.

Samples

Best practice for using is to:

1. Enable on a

   For efficient queries, remember to segmentby the column you will use most often to filter your data. For example:

   • s:

     Use CREATE TABLE for a

     CREATE TABLE crypto_ticks (
       "time" TIMESTAMPTZ,
       symbol TEXT,
       price DOUBLE PRECISION,
       day_volume NUMERIC
     ) WITH (
       timescaledb.hypertable,
       timescaledb.segmentby='symbol',
       timescaledb.orderby='time DESC'
     );
     

     For v2.23.0 and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column, throws an error and asks for an explicit definition. For earlier versions, set partition_column to a time column.

     If you are self-hosting v2.20.0 to v2.22.1, to convert your data to the after a specific time interval, you have to call add_columnstore_policy after you call CREATE TABLE

     If you are self-hosting v2.19.3 and below, create a relational table, then convert it using create_hypertable. You then enable with a call to ALTER TABLE.

   • s

     1. Use ALTER MATERIALIZED VIEW for a :
        ALTER MATERIALIZED VIEW assets_candlestick_daily set (
           timescaledb.enable_columnstore = true,
           timescaledb.segmentby = 'symbol');
        

     Before you say huh, a is a specialized .

     1. Add a policy to convert s to the at a specific time interval:

        Create a columnstore_policy that automatically converts s in a to the at a specific time interval. For example:

        CALL add_columnstore_policy('assets_candlestick_daily', after => INTERVAL '1d');
        

   is optimized for fast updates on compressed data in the . To modify data in the , use standard SQL.

2. View the policies that you set or the policies that already exist

   SELECT * FROM timescaledb_information.jobs
   WHERE proc_name='policy_compression';
   

   See timescaledb_information.jobs.

You can also convert_to_columnstore and convert_to_rowstore manually for more fine-grained control over your data.

Limitations

s in the have the following limitations:

• ROW LEVEL SECURITY is not supported on s in the .

Available functions

Policies

• add_columnstore_policy(): set a policy to automatically move s in a to the when they reach a given age
• remove_columnstore_policy(): remove a policy from a

Configuration

• ALTER TABLE (hypercore): enable the for a

Manual conversion

• convert_to_columnstore(): manually add a to the
• convert_to_rowstore(): move a from the to the

Statistics and information

• chunk_columnstore_stats(): get statistics about s in the
• hypertable_columnstore_stats(): get statistics related to the

• timescaledb_information.chunk_columnstore_settings: get information about settings on each in the
• timescaledb_information.hypertable_columnstore_settings: get information about settings for all s