Enable and use In-Memory Column Index (IMCI)

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

CREATE EXTENSION polar_csi;

1. Create a columnstore index

-- 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 index progress

Creating a columnstore index on a large table can be time-consuming. You can monitor the progress with the following query. You can also adjust relevant parameters to improve the index 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 services, you can use pg_cancel_backend or pg_terminate_backend to cancel the index creation. Obtain the required pid as described in Step 2. View the index creation progress.

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, view the storage space occupied by the previously created columnstore index.

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

5. (Optional) Real-time data synchronization

PolarDB for PostgreSQL clusters not only support creating columnstore indexes on historical data but also automatically synchronize new data from the row store table to the columnstore index. After you use the CREATE INDEX statement to create a columnstore index for a table, new data is written to the columnstore index in real time, and no extra operations are required.

IMCI uses the logical replication mechanism of PolarDB for PostgreSQL to synchronize data by decoding WAL logs and applying the changes to the columnstore engine. With the default configuration, this mechanism can handle a write throughput of approximately 200,000 QPS (based on the Sysbench oltp_insert standard test).

• Check the current data synchronization latency

  SELECT active_pid, database, slot_name, pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn) AS size FROM pg_replication_slots WHERE slot_name LIKE 'csi_sync_%';

  If the size remains within 50 MB, the columnstore data is typically refreshed within seconds.

• Improve data synchronization efficiency

  To meet the real-time requirements for row-store and column-store data in 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 parameter scope

SET polar_csi.enable_query = ON;

3. Check for index usage

You can use EXPLAIN or EXPLAIN ANALYZE to view the query plan. If the plan includes the CSI Executor keyword, it indicates the query used the columnstore index.

EXPLAIN SELECT COUNT(*) FROM sales;

The following result is returned:

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

4. (Optional) Configure IMCI query consistency

The IMCI feature in PolarDB for PostgreSQL offers two query consistency levels to suit different business needs.

• Eventual consistency (default): Suitable for write-intensive workloads with low real-time data requirements.

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

Data synchronization for IMCI in PolarDB for PostgreSQL typically has a latency on the order of seconds. However, data latency increases under heavy write loads. 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 business scenarios that require strong consistency between row-store and column-store data, you can set the polar_csi.forward_replay_wait parameter to on. This enables strong consistency reads. When a query runs, it waits until the columnstore index data is consistent with the row-store data before execution.

Improve index creation speed

The index creation speed depends on the following parameters:

Improve real-time performance

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

Improve query speed

Query performance primarily depends on the following parameters. You can adjust their values to improve query performance: