PolarDB:Enable and use a columnstore index

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

CREATE EXTENSION polar_csi;

1. Create a columnstore index for specific columns or all columns

-- Create an index for specific columns
CREATE INDEX [ CONCURRENTLY ] csi_index_name ON table_name USING CSI(column1,column2,...);
-- Create an index for all columns
CREATE INDEX [ CONCURRENTLY ] csi_index_name ON table_name USING CSI;

2. View the index creation progress

Creating a columnstore index for a large table can be time-consuming. You can check the creation progress using the following method. You can also adjust relevant parameters to improve the creation speed.

SELECT * FROM pg_stat_progress_create_index;

3. (Optional) Cancel index creation

If creating an index takes a long time and the process affects your existing services, you can use pg_cancel_backend or pg_terminate_backend to cancel the index creation. These functions require a pid, which you can obtain by following the instructions in the 2. View the index creation progress section.

SELECT pg_cancel_backend(pid);

SELECT pg_terminate_backend(pid);

4. View the index size

Use the pg_relation_size function to view the index size. You can combine it with the pg_size_pretty function to display the size in a more readable format.

SELECT * FROM pg_size_pretty(pg_relation_size('csi_index_name'));

For example, you can view the storage size of the previously created columnstore index.

SELECT * FROM pg_size_pretty(pg_relation_size('idx_csi_sales'));

5. (Optional) Configure real-time data for the columnstore index

PolarDB for PostgreSQL clusters support creating columnstore indexes on historical data and automatically synchronize new data from the row-oriented table to the columnstore index. After you create a columnstore index for a table using the CREATE INDEX statement, new data is written to the columnstore index in real time. No extra operations are required for this process.

To meet the real-time data requirements of certain business scenarios, you can adjust the polar_csi.update_interval and polar_csi.update_batch_count parameters to improve the real-time performance of the columnstore index.

1. Configure parameters

After you create a columnstore index, you can use the following parameters to control whether SQL queries use the columnstore index.

SET polar_csi.enable_query = on;

SET polar_csi.cost_threshold = 0;

2. Configure the scope of the parameters

SET polar_csi.enable_query = ON;

3. Check if a SQL statement uses the columnstore index

You can use tools such as EXPLAIN or EXPLAIN ANALYZE to print the query plan. If the plan contains the CSI Executor keyword, the query is using the columnstore index.

EXPLAIN SELECT COUNT(*) FROM sales;

The following result is returned:

  QUERY PLAN  
--------------
 CSI Executor
(1 row)

4. (Optional) Configure the query consistency level for the columnstore index

The columnstore index in PolarDB for PostgreSQL supports two query consistency levels to meet different business needs.

• Eventual consistency (default): Suitable for queries with high write loads but low real-time data requirements.

• Strong consistency: Returns query results only after the data in the column store is fully consistent with the data in the row store.

The real-time performance of the columnstore index in PolarDB for PostgreSQL can reach the sub-second level. However, when the write load is high, the data latency for the columnstore index increases. The default consistency level is eventual consistency. Therefore, if the write load is high and a SQL query uses the columnstore index, the query might not retrieve the latest data.

For scenarios that require strong consistency between the row store and column store, you can set the polar_csi.forward_replay_wait parameter to strong consistency read (on). When a query is run, the system executes the query only after the data in the columnstore index is consistent with the data in the row store.

Improve index creation speed

The index creation speed is related to several parameters, as shown in the following table:

Improve the real-time performance of the columnstore index

PolarDB added the following parameters in the specified versions. By adjusting these parameters, you can increase the speed of row-to-column transformation and improve the real-time performance of columnstore index data.

• PostgreSQL 14: minor engine version 2.0.14.13.28.0 or later.

• PostgreSQL 16: No related parameters.

Improve query speed

Query performance is mainly related to the following parameters. You can adjust their values to improve query performance: