PolarDB:Use a columnstore index on a partitioned table

Prerequisites

Before you begin, make sure that:

• Your cluster runs one of the supported versions: To check your minor engine version, run SHOW polardb_version; or view the minor engine version in the console. If your version doesn't meet the requirement, upgrade the minor engine version.

  • PostgreSQL 16, minor engine version 2.0.16.8.3.0 or later

  • PostgreSQL 14, minor engine version 2.0.14.10.20.0 or later

• The partitioned table has a primary key, and the primary key column is included when you create the columnstore index.

• The wal_level parameter is set to logical. This adds the information required for logical replication to the write-ahead log (WAL).

  Important

  Set the wal_level parameter in the console. Changing this parameter restarts the cluster—plan your maintenance window accordingly.

Choose an enablement method

The enablement method depends on your minor engine version. Identify your version range before proceeding.

Enable the columnstore index

Follow the instructions for your version and chosen method.

Add an IMCI read-only node (recommended for newer versions)

Note

Your cluster must already have at least one read-only node. You cannot add an IMCI read-only node to a single-node cluster.

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 using one of these paths:

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

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

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

3. On the upgrade/downgrade page, configure and purchase the node:

   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. Return to the cluster details page. The IMCI node is ready when its status changes to Running.

   

Add during purchase:

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

CREATE EXTENSION polar_csi;

Set up a partitioned table and create a columnstore index

This example creates a two-level partitioned table with 320 million rows (~16 GB), partitioned by year and then by month.

Table schema:

1. Create the partitioned table. The partition key is sale_date. The table requires a composite primary key that includes the partition key.

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. Insert ~16 GB of test data.

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. Create a columnstore index on sales. Include the primary key column (sale_id) along with all columns used in analytical queries.

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

Run queries with the columnstore index

The following three queries filter by different sale_date ranges to exercise partition pruning across different partition boundaries.

Using the columnstore index (degree of parallelism = 4):

-- Enable the columnstore index and set the degree of parallelism to 4.
SET polar_csi.enable_query TO on;
SET polar_csi.exec_parallel TO 4;

-- Q1: 2-month range (within a single sub-partition)
EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' AND '2023-3-1' AND amount > 100 GROUP BY sale_date;

-- Q2: 8-month range (spans two sub-partitions)
EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' AND '2023-9-1' AND amount > 100 GROUP BY sale_date;

-- Q3: 14-month range (spans two years)
EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' AND '2024-3-1' AND amount > 100 GROUP BY sale_date;

Using the row-store engine for comparison (degree of parallelism = 4):

-- Disable the columnstore index and use native PostgreSQL parallel execution.
SET polar_csi.enable_query TO off;
SET max_parallel_workers_per_gather TO 4;

-- Q1
EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' AND '2023-3-1' AND amount > 100 GROUP BY sale_date;

-- Q2
EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' AND '2023-9-1' AND amount > 100 GROUP BY sale_date;

-- Q3
EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' AND '2024-3-1' AND amount > 100 GROUP BY sale_date;