Use a columnstore index to accelerate time series data analytics

Workflow

1. Data writing: Your business application writes time series data (for example, transaction records) into a PolarDB for PostgreSQL cluster.

2. Columnstore index: Create a columnstore index on the base table. PolarDB for PostgreSQL automatically maintains the columnar data alongside the row store. Compared with row storage, column storage organizes data by column, which delivers a higher compression ratio and reads only the relevant columns during aggregate queries, reducing I/O.

3. Query acceleration: Analytical queries (such as candlestick aggregations) are routed to the columnstore index preferentially, either through the optimizer or through an explicit Hint. The query engine uses columnar storage and parallel processing to scan and aggregate the data, and then returns the result.

Solution advantages

• Simple to use: No business refactoring or complex ETL is required. Create a columnstore index on the base table and analytical queries are transparently accelerated.

• Feature-rich: Natively supports partitioned tables and ships with a rich set of time series analysis functions such as time_bucket, first, and last, simplifying your SQL development.

Step 1: Prepare the environment

1. Verify that your cluster version and configuration meet the following requirements:

   • Cluster versions:

     • PostgreSQL 14 (minor engine version 2.0.14.10.20.0 or later)

     • PostgreSQL 15 (minor engine version 2.0.15.15.7.0 or later)

     • PostgreSQL 16 (minor engine version 2.0.16.8.3.0 or later)

     • PostgreSQL 17 (minor engine version 2.0.17.7.5.0 or later)

     Note

     You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。

   • The wal_level parameter must be set to logical. This setting adds the required information for logical decoding to the write-ahead logging (WAL).

     Note

     You can set the wal_level parameter in the console. Modifying this parameter restarts the cluster. Plan your business operations accordingly and proceed with caution.

   • The source table must have a primary key, and the primary key column must be included when you create the columnstore index. Using a SERIAL or BIGSERIAL data type for the primary key is recommended, as it significantly improves data synchronization efficiency.

   • You can create only one columnstore index for each table.

2. Enable the columnstore index feature.

   The method for enabling IMCI varies depending on the minor engine version of your PolarDB for PostgreSQL cluster:

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

   For PolarDB for PostgreSQL clusters with these versions, two methods are available. The following table outlines the differences.

   Comparison item	[Recommended] Add an IMCI read-only node	Directly use the pre-installed columnstore index extension
   Method	You can add an IMCI read-only node manually in the console.	No action is required. You can use the extension directly.
   Resource allocation	The columnstore engine exclusively uses the node's resources, including all available memory.	The columnstore engine is limited to 25% of the node's memory. The remaining memory is allocated to the row store engine.
   Business impact	Transactional processing (TP) and analytical processing (AP) workloads are isolated on different nodes and do not affect each other.	TP and AP workloads run on the same node and can affect each other.
   Costs	IMCI read-only nodes incur additional charges and are billed at the same rate as regular compute nodes.	No additional cost.

   Add an IMCI read-only node

   There are two ways to add an IMCI read-only node:

   Note

   The cluster must contain at least one read-only node. You cannot add an IMCI read-only node to a single-node cluster.

   Console

   1. Log on to the PolarDB console and select the cluster's region. You can 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 IMCI read-only node and complete the payment.

      1. Click Add a Read-only 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 IMCI read-only node to be added. The node is ready when its status changes to Running.

   During purchase

   On the PolarDB purchase page, in the Nodes section, specify the number of IMCI Read-Only Nodes.

   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 PolarDB for PostgreSQL clusters with these versions, the IMCI feature is provided as the polar_csi extension. To use IMCI, you must first create the extension in the desired database.

   Note

   • The polar_csi extension is scoped at the database level. To use IMCI in multiple databases within a cluster, you must create the polar_csi extension for each database.

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

   There are two ways to install the polar_csi extension:

   Console

   1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where your cluster is located, and then click the cluster ID to open the cluster details page.

   2. In the left-side navigation pane, choose Settings and Management > Extension Management. On the Extension Management tab, select Uninstalled Extensions.

   3. In the upper-right corner of the page, select the target database. In the row for the polar_csi extension, click Install in the Actions column. In the Install Extension dialog box, select the target Database Account and click OK to install the extension in the target database.

   CLI

   Connect to the database cluster and run the following statement in a database where you have sufficient permissions to create the polar_csi extension.

   CREATE EXTENSION polar_csi;

Step 2: Prepare the data

1. This solution uses a transaction records table and simulates generating 100 million transaction records spanning approximately 2 days. Trading hours are 08:00–16:00 each day, producing about 40 million records per day.

   -- 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
   );
   
   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 rows
       -- ========================
       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 rows
       -- ========================
       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 rows
       -- ========================
       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 rows
       -- ========================
       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;

2. Create a columnstore index on the transaction records table.

   CREATE INDEX idx_csi_market_trades ON market_trades USING CSI;

Step 3: Run candlestick aggregation queries

Use case: compute candlesticks over fixed time windows.

Application example: compute per-second highest price, lowest price, opening price, closing price, and total transaction volume.

The following examples compute per-second, per-minute, per-hour, and per-day candlestick data respectively.

-- 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;

SQL notes

• /*+ SET (polar_csi.enable_query on) */: forces the query to use the columnstore index execution plan. In some scenarios, the optimizer may incorrectly estimate that the row store is more efficient. Use this Hint to ensure the query uses the columnstore path.

• time_bucket(bucket_width, ts): a function provided by Time Series Database that groups the timestamp ts by the specified interval bucket_width.