PolarDB:Use an IMCI on a partitioned table

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

This case creates a multi-level partitioned table, inserts about 320 million rows (~16 GB) of simulated data, and then runs statistical analysis based on partition conditions.

The schema of the test partitioned table is as follows:

• sales: the primary table.

• sales_2023: partitioned by year.

  • sales_2023_a: partitioned by month; months 1 through 6 are defined as partition a.

  • sales_2023_b: partitioned by month; months 7 through 12 are defined as partition b.

• sales_2024: partitioned by year.

  • sales_2024_a: partitioned by month; months 1 through 6 are defined as partition a.

  • sales_2024_b: partitioned by month; months 7 through 12 are defined as partition b.

1. Create a multi-level partitioned table named sales, with the time column sale_date as the partition key. The definition is as follows:

   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. Generate the data and insert it into the partitioned table (about 16 GB).

   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 an IMCI on the table and include the sale_id, product_id, sale_date, and amount columns in the IMCI.

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

Step 3: Run the queries

Run queries by using different execution engines. Three queries (Q1, Q2, and Q3) are generated based on different partition conditions.

• Use the IMCI.

  --- Enable the IMCI and set the query degree of parallelism to 4.
  SET polar_csi.enable_query to on;
  // High kernel version
  SET polar_csi.max_parallel_workers to 4;
  // Low kernel version
  SET polar_csi.exec_parallel 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;

  Note

  The polar_csi.max_parallel_workers parameter was previously named polar_csi.exec_parallel in earlier kernel versions. For kernel versions that do not support polar_csi.max_parallel_workers, use polar_csi.exec_parallel instead.

  • PostgreSQL 14:

    • Use polar_csi.exec_parallel in versions 2.0.14.20.42.0 and earlier.

    • Use polar_csi.max_parallel_workers in versions 2.0.14.20.43.0 and later.

  • PostgreSQL 16:

    • Use polar_csi.exec_parallel in versions 2.0.16.11.15.0 and earlier.

    • Use polar_csi.max_parallel_workers in versions 2.0.16.13.16.0 and later.

• Disable the IMCI and use the row-store engine.

  --- Disable the IMCI, use the row-store engine, and set the query degree of parallelism to 4.
  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;