This topic describes the configuration parameters and supported data types for columnstore indexes (polar_csi) in PolarDB for PostgreSQL.
Configuration parameters
| Parameter | Valid values | Default | Description |
|---|---|---|---|
polar_csi.enable_query | on | off | off | Controls whether queries can use columnstore indexes. Set to on to enable columnstore acceleration. |
polar_csi.cost_threshold | 1–1,000,000,000 | 50,000 | Minimum query cost for the columnstore index to be used. Queries with a lower estimated cost skip columnstore acceleration. Use EXPLAIN or EXPLAIN ANALYZE to check query cost. |
polar_csi.exec_parallel | 1–512 | 2 | Degree of parallelism for columnstore queries — the number of CPU cores a single SQL statement can use. Higher values improve performance. Keep this at or below the number of CPU cores on the compute node. |
polar_csi.memory_limit | 1–1,048,576 | 4,096 | Maximum memory the columnstore index can use, in MB. A higher limit speeds up index creation. Keep this below 25% of total cluster memory to avoid out-of-memory (OOM) errors. |
polar_csi.flush_count | 2,048–20,480,000 | 204,800 | Number of rows committed per batch during index creation. Increasing this value improves creation speed but uses more memory. |
polar_csi.update_interval | 0–3,600 | 1 | Interval in seconds for syncing real-time row and column data to the column store. A longer interval batches small transactions together, improving throughput when write volume is high. |
polar_csi.update_batch_count | 1,024–4,294,967,295 | 100,000 | Maximum number of rows processed in a single update transaction. Increasing this threshold improves batch update throughput. |
polar_csi.forward_replay_wait | on | off | off | Query consistency level for columnstore data. off uses eventual consistency — column store data may lag behind the row store. on uses strong consistency — a query waits for column store data to reach the latest version before executing. Enabling this may increase query latency under high write pressure. |
polar_csi.forward_replay_wait_lsn_diff | 0–INT_MAX | 0 | Maximum allowed synchronization latency between the row store and column store, in LSN bytes. If the latency exceeds this threshold, the columnstore engine is no longer used. Takes effect only when polar_csi.forward_replay_wait is enabled. |
polar_csi.sync_addcolumn | on | off | off | Controls whether ADD COLUMN operations are synchronized to the columnstore index. |
Tuning guidance
`polar_csi.cost_threshold`: If this value is too high, queries with high cost cannot use the columnstore index, slowing down those queries. If it is too low, simple queries use the columnstore index unnecessarily, reducing system concurrency. Use EXPLAIN ANALYZE to measure query cost and calibrate this threshold.
`polar_csi.exec_parallel`: The actual benefit depends on your cluster's CPU resources. Set this to a value no higher than the number of CPU cores on the compute node.
`polar_csi.memory_limit`: A larger memory allocation speeds up index creation. Do not exceed 25% of cluster memory, as this risks OOM errors.
`polar_csi.forward_replay_wait` and `polar_csi.forward_replay_wait_lsn_diff`: Use forward_replay_wait = on when query results must reflect the latest committed data. If strong consistency causes excessive wait times, set forward_replay_wait_lsn_diff to a threshold — if the LSN latency between the row store and column store exceeds this value, the columnstore engine is no longer used for that query.
Version support
The following table shows which minor engine versions introduced or changed each parameter.
| Parameter | PostgreSQL version | Minor engine version | Change |
|---|---|---|---|
polar_csi.cost_threshold | PostgreSQL 14 | 2.0.14.13.28.0 and later | Default changed from 1,000 to 50,000 |
polar_csi.cost_threshold | PostgreSQL 16 | 2.0.16.10.12.0 and later | Default changed from 1,000 to 50,000 |
polar_csi.memory_limit | PostgreSQL 14 | 2.0.14.19.40.0 and later | Default changed from 1,024 MB to 4,096 MB |
polar_csi.memory_limit | PostgreSQL 16 | 2.0.16.10.12.0 and later | Default changed from 1,024 MB to 4,096 MB |
polar_csi.update_interval | PostgreSQL 14 | 2.0.14.13.28.0 and later | Parameter added |
polar_csi.update_interval | PostgreSQL 16 | 2.0.16.10.12.0 and later | Parameter added |
polar_csi.update_batch_count | PostgreSQL 14 | 2.0.14.13.28.0 and later | Parameter added |
polar_csi.update_batch_count | PostgreSQL 16 | 2.0.16.10.12.0 and later | Parameter added |
polar_csi.forward_replay_wait_lsn_diff | PostgreSQL 14 | 2.0.14.19.40.0 and later | Parameter added |
polar_csi.forward_replay_wait_lsn_diff | PostgreSQL 16 | 2.0.16.10.12.0 and later | Parameter added |
polar_csi.forward_replay_wait_lsn_diff | PostgreSQL 15, PostgreSQL 17 | Same as the scope of application for the vectorization engine | Parameter added |
polar_csi.sync_addcolumn | PostgreSQL 14 | 2.0.14.19.40.0 and later | Parameter added |
polar_csi.sync_addcolumn | PostgreSQL 16 | 2.0.16.10.12.0 and later | Parameter added |
polar_csi.sync_addcolumn | PostgreSQL 15, PostgreSQL 17 | Same as the scope of application for the vectorization engine | Parameter added |
Data types
The following data types are supported by columnstore indexes.
| Category | Data types |
|---|---|
| Basic types | BOOLEAN, ARRAY, BIT, UUID |
| String and binary types | BPCHAR, CHAR, VARCHAR, TEXT, NAME, BYTEA |
| Date and time types | DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL |
| Numeric types | BIGINT, DECIMAL, DOUBLE PRECISION, FLOAT4, FLOAT8, INTEGER, NUMERIC, REAL, SERIAL, SHORT, SMALLINT |
| Composite types | JSON, JSONB, ENUM, BITSTRING, VARCHAR[], TEXT[] |
BITSTRING,VARCHAR[], andTEXT[]are available in PostgreSQL 14 starting from minor engine version 2.0.14.19.40.0, and in PostgreSQL 16 starting from minor engine version 2.0.16.10.12.0.