PolarDB:Use a columnstore index to accelerate time series data analytics

PostgreSQL 16 (2.0.16.9.8.0 or later) or PostgreSQL 14 (2.0.14.17.35.0 or later)

Two methods are available for these versions:

Add a columnstore index read-only node

The cluster must contain at least one read-only node. Single-node clusters do not support adding a columnstore index read-only node.

Add in the console

1. Log on to the PolarDB console and select the region where your cluster is located. Open the Add/Remove Node wizard in one of the following ways:

   • On the Clusters page, click Add/Remove Node in the Actions column.

   • On the Basic Information page of the target cluster, click Add/Remove Node in the Database Nodes section.

2. Select Add Read-only IMCI Node and click OK.

3. On the cluster upgrade/downgrade page, add the node and complete the payment:

   1. Click Add an IMCI Node and select the node specifications.

   2. Select a switchover time.

   3. (Optional) Review the Product Terms of Service and Service Level Agreement.

   4. Click Buy Now.

   

4. After the payment is complete, return to the cluster details page and wait for the node status to change to Running.

   

Add during purchase

On the PolarDB purchase page, set the IMCI Read-Only Nodes parameter to the number of nodes you need.

PostgreSQL 16 (2.0.16.8.3.0 to 2.0.16.9.8.0) or PostgreSQL 14 (2.0.14.10.20.0 to 2.0.14.17.35.0)

For these versions, the columnstore index is deployed as the polar_csi extension. Create the extension in each database where you want to use the columnstore index.

The polar_csi extension is scoped to the database level. To use the columnstore index in multiple databases within a cluster, create the extension in each database separately.

The database account used to install the extension must be a privileged account.

CREATE EXTENSION polar_csi;

Key functions

Three functions are essential for candlestick queries:

• `time_bucket(bucket_width, ts)` — Groups timestamps by a specified interval (for example, '1 second', '1 minute'). Provided by the Time Series Database.

• `FIRST(price ORDER BY trade_ts)` — Returns the first value of price ordered by trade_ts, which gives the opening price.

• `LAST(price ORDER BY trade_ts)` — Returns the last value of price ordered by trade_ts, which gives the closing price.

For the highest and lowest prices, use the standard SQL aggregate functions MAX(price) and MIN(price).

Prepare the data

1. Create the transaction records table:

   -- Transaction records table
   CREATE TABLE market_trades (
       trade_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- Auto-increment primary key
       trade_ts   TIMESTAMP,                                        -- Transaction timestamp
       market_id  VARCHAR,                                          -- Market ID
       price      DECIMAL,                                          -- Transaction price
       amount     DECIMAL,                                          -- Transaction amount
       insert_ts  TIMESTAMP                                         -- System write timestamp
   );

2. Insert 100 million simulated transaction entries spanning two days. Peak trading hours (08:00–16:00) account for approximately 40 million entries per day; off-peak hours account for approximately 10 million entries per day.

   INSERT INTO market_trades(trade_ts, market_id, price, amount, insert_ts)
   SELECT
       trade_ts,
       market_id,
       price,
       amount,
       trade_ts + (random() * 500)::INT * INTERVAL '1 millisecond' AS insert_ts
   FROM (
       -- ========================
       -- 1. Day 1 peak: 2025-06-01 8:00 - 16:00, 40 million entries
       -- ========================
       SELECT
           '2025-06-01 08:00:00'::TIMESTAMP +
           (random() * 28800)::INT * INTERVAL '1 second' +  -- 28800 seconds = 8 hours
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 40000000)
   
       UNION ALL
   
       -- ========================
       -- 2. Day 1 off-peak: 2025-06-01 16:00 - 2025-06-02 08:00, 10 million entries
       -- ========================
       SELECT
           CASE
               WHEN random() < 0.5 THEN
                   -- 16:00 - 24:00
                   '2025-06-01 16:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
               ELSE
                   -- 00:00 - 08:00 (early morning of day 2)
                   '2025-06-02 00:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
           END +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 10000000)
   
       UNION ALL
   
       -- ========================
       -- 3. Day 2 peak: 2025-06-02 8:00 - 16:00, 40 million entries
       -- ========================
       SELECT
           '2025-06-02 08:00:00'::TIMESTAMP +
           (random() * 28800)::INT * INTERVAL '1 second' +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 40000000)
   
       UNION ALL
   
       -- ========================
       -- 4. Day 2 off-peak: 2025-06-02 16:00 - 2025-06-03 08:00, 10 million entries
       -- ========================
       SELECT
           CASE
               WHEN random() < 0.5 THEN
                   -- 16:00 - 24:00
                   '2025-06-02 16:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
               ELSE
                   -- 00:00 - 08:00 (early morning of day 3)
                   '2025-06-03 00:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
           END +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 10000000)
   ) AS data;

3. Create a columnstore index on the transaction records table:

   CREATE INDEX idx_csi_market_trades ON market_trades USING CSI;

Query examples

All examples use the /*+ SET (polar_csi.enable_query on) */ hint to force the query to use the columnstore index execution plan. In some cases, the optimizer may incorrectly estimate that the row store is more efficient — this hint ensures the columnstore path is used.

-- Second-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 second', trade_ts) AS candle_ts,   -- Data within 1 second
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 second
    MAX(price) AS high,                               -- Highest price within 1 second
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 second
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 second
    SUM(amount) AS vol                                -- Total transaction volume within 1 second
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Minute-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 minute', trade_ts) AS candle_ts,   -- Data within 1 minute
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 minute
    MAX(price) AS high,                               -- Highest price within 1 minute
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 minute
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 minute
    SUM(amount) AS vol                                -- Total transaction volume within 1 minute
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Hour-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 hour', trade_ts) AS candle_ts,     -- Data within 1 hour
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 hour
    MAX(price) AS high,                               -- Highest price within 1 hour
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 hour
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 hour
    SUM(amount) AS vol                                -- Total transaction volume within 1 hour
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Day-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 day', trade_ts) AS candle_ts,      -- Data within 1 day
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 day
    MAX(price) AS high,                               -- Highest price within 1 day
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 day
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 day
    SUM(amount) AS vol                                -- Total transaction volume within 1 day
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;