Vertical clustered index

Vertical Clustered Index (columnar storage extension)

Vertical cluster index (VCI) is a columnar store feature implemented by the index access method. VCI is available as an extension feature. This feature provides high performance for OLTP and OLAP processing. For this purpose, VCI has two storage structures: Read Optimized Storage and Write Optimized Storage.

Write Optimized Storage (WOS)

In WOS, the TID of newly updated or deleted data is stored in row format along with xmin and xmax information. Synchronously stores minimal information in WOS when data is updated or deleted in a transaction. Only the writing of these minimum pieces of information occurs, thus reducing performance impact.

Read Optimized Storage (ROS)

ROS stores data for all columns with VCI in column format. The conversion to ROS is done asynchronously with transactions by background workers. ROS data is managed in units called extents. The conversion to ROS via WOS is done in this extent unit.

Update process details

The following describes the flow of processing when data is updated while a VCI index is set.

1. Create index

   When you create a VCI index, the wos and ros data structures are created.

   If data is already inserted into the table, convert it to ROS.

2. Scan Data (SELECT)

   When searching for data in this state, VCI scanning searches in ROS, which is a columnar structure.

3. Insert Data (INSERT)

   When data is inserted, the TID and transaction information of the inserted row are stored in WOS. This operation is synchronous with the transaction.

   Since only minimal data is inserted into WOS and not converted to ROS, OLTP performance impact is minimal.

4. Scan Data (SELECT)

   You must search for all data that occurred in the transaction.

   However, since the conversion from WOS to ROS is asynchronous, data that originated in recent transactions is not in ROS.

   Therefore, it transforms the data that is only in WOS into a temporary structure called local ROS. This allows you to scan all the data.

   Local ROS disappear when the SELECT query is finished. Avoid using VCI scans if the conversion is expensive.

5. Reflect the contents of WOS into ROS

   Background workers reflect the contents of WOS to ROS.

   This is done asynchronously with the transaction, so the performance impact is minimal.

Current Implementation and Future Prospects

Currently, VCI includes a minimal implementation. The following further modifications are required:

1. Remove some hooks added
   1. Implement hooks for index and relation operations
   2. DELETE execution hook (amdelete)

      you want to remove the column from the column every time a VCI indexed table is updated or deleted, not when VACUUM is triggered

   3. Add when aminsert is called

      Changed CREATE INDEX to be called even if an UPDATE occurs on a column not specified in the column field

VCI also plans to implement additional:

1. Implementing VCI parallel scans using the PostgreSQL standard
2. More data types to support
3. Improved time consuming setup when defining indexes on existing data
4. Support for pg_upgrade

These discussions are in the following threads. https://www.postgresql.org/message-id/OS7PR01MB119648CA4E8502FE89056E56EEA7D2%40OS7PR01MB11964.jpnprd01.prod.outlook.com