This section describes how to set up VCI.
Setup flow
Edit postgresql.conf to set the required parameters for VCI. After that, start or restart the instance.
The following table lists the parameters that need or are recommended to be configured in advance:
Parameter name | Setting value | Description | Required |
|---|---|---|---|
shared_preload_libraries | Literal 'vci, pg_prewarm' | VCI and shared library to be preloaded at server start. | Y |
session_preload_libraries | Literal 'vci, pg_prewarm' | VCI and shared library to be preloaded at connection start. | Y |
reserve_buffer_ratio | Percentage of shared memory to be used for stable buffer table | Proportion of shared memory to be used for a stable buffer table. | N |
vci.control_max_workers | Number of background workers that manage VCI | Number of background workers that manage VCI. Add this value to max_worker_processes. | N |
vci.max_parallel_degree | Maximum number of background workers used for parallel scan | Maximum number of background workers used for parallel scan. Add this value to max_worker_processes. | N |
Example
shared_preload_libraries = 'vci, pg_prewarm' session_preload_libraries = 'vci, pg_prewarm' reserve_buffer_ratio = 20 vci.control_max_workers = 8 vci.max_parallel_degree = 4 max_worker_processes = 18 # Example: If the initial value was 6, 6 + 8 + 4 = 18
Note
An error occurs if you use VCI to start instances when procfs is not mounted. Ensure that procfs is mounted before starting instances.
See
Refer to "Appendix A Parameters" for information on all parameters for VCI. Refer also to default value for each parameter and details such as specification range in the same chapter. Refer to "Server Configuration" under "Server Administration" in the PostgreSQL documentation for information on shared_preload_libraries, session_preload_libraries, and max_worker_processes.
Execute CREATE EXTENSION to install the VCI and pg_prewarm extensions. Both extensions need to be installed for each database.
Installing VCI
db01=# CREATE EXTENSION vci;
Installing pg_prewarm
db01=# CREATE EXTENSION pg_prewarm;
Note
Only superusers can install VCI extensions.
VCI extensions can only be installed in public schema.
Some operations cannot be performed for VCI extensions. Refer to "11.2.1 Commands that cannot be Used for VCI" for details.
Execute the CREATE INDEX statement with the "USING vci" clause to create a VCI for the desired columns and the "WITH (stable_buffer=true)" clause to enable the stable buffer feature.
To use a separate disk for the VCI, specify the TABLESPACE clause.
db01=# CREATE INDEX idx_vci ON table01 USING vci (col01, col02) WITH (stable_buffer=true);
Note
Some table types cannot be specified on the ON clause of CREATE INDEX. Refer to "11.1.4.1 Relation Types" for details.
Some data types cannot be specified on the column specification of CREATE INDEX. Refer to "11.1.4.2 Data Types" for details.
Some operations cannot be performed for VCI. Refer to "11.2.1 Commands that cannot be Used for VCI" for details.
The same column cannot be specified more than once on the column specification of CREATE INDEX.
VCI cannot be created for table columns that belong to the template database.
CREATE INDEX creates multiple views named vci_10digitRelOid_5digitRelAttr_1charRelType alongside VCI itself. These are called VCI internal relations. Do not update or delete them as they are used for VCI aggregation.
All data for the specified column will be replaced in columnar format when VCI is created, so executing CREATE INDEX on an existing table with data inserted takes more time compared with a general index (B-tree). Jobs can continue while CREATE INDEX is running.
When CREATE INDEX USING VCI is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.
Parallel index build is not supported on VCI indexes.
Execute the SELECT statement to reference the pg_indexes catalog, and confirm that the VCI was created for the target columns.
Example
db01=# SELECT indexdef FROM pg_indexes WHERE indexdef LIKE '%vci%';
indexdef
----------------------------------------------------------
CREATE INDEX idx_vci ON table01 USING vci (col01, col02)
(1 row)