Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 30, 2025 at 9:07 PM Japin Li <japinli@hotmail.com> wrote:
>
...
> 2.
> +Internal Relation Types:
> +- -1:  TID relation (maps CRID to original TID)
> +- -2:  NULL vector (bit array for NULL values)
> +- -3:  Delete vector (bit array for deleted records)
> +- -5:  TID-CRID mapping table
> +- -9:  Data WOS (buffered row data)
> +- -10: Whiteout WOS (deletion markers)
> +- 0-N: ROS column data relations (one per indexed column)
> +
> +Example:
> +For a VCI index on sales(customer_id, amount, date):
> +
> +Generated relations include:
> +vci_0000001234_00000_d  → Column 0 data (customer_id)
> +vci_0000001234_00001_d  → Column 1 data (amount)
> +vci_0000001234_00002_d  → Column 2 data (date)
> +vci_0000001234_65535_d  → TID relation
> +vci_0000001234_65534_d  → NULL vector
> +vci_0000001234_65533_d  → Delete vector
> +vci_0000001234_65531_m  → TID-CRID mapping
> +vci_0000001234_65527_d  → Data WOS
> +vci_0000001234_65526_d  → Whiteout WOS
>
> The README states that it generates the above relations, but there are
> additional internal relations that are not mentioned.
>
> SELECT relname, relkind FROM pg_class WHERE relname ~ 'vci*' ORDER BY relname;
>         relname         | relkind
> ------------------------+---------
>  vci_0000016578_00000_d | m
>  vci_0000016578_00000_m | m
>  vci_0000016578_00001_d | m
>  vci_0000016578_00001_m | m
>  vci_0000016578_65526_d | m
>  vci_0000016578_65527_d | m
>  vci_0000016578_65530_0 | m
>  vci_0000016578_65530_1 | m
>  vci_0000016578_65531_d | m
>  vci_0000016578_65531_m | m
>  vci_0000016578_65533_d | m
>  vci_0000016578_65533_m | m
>  vci_0000016578_65534_d | m
>  vci_0000016578_65534_m | m
>  vci_0000016578_65535_d | m
>  vci_0000016578_65535_m | m
>
> Based on the above, are the following materialized views unused, or is their
> use just undocumented?
>
>   - vci_0000016578_00000_m
>   - vci_0000016578_00001_m
>   - vci_0000016491_65530_0
>   - vci_0000016578_65530_1
>   - vci_0000016578_65531_d
>   - vci_0000016578_65534_m
>   - vci_0000016578_65535_m
>
> What is the purpose of the '0' and '1' suffixes?
>

Yeah, it was undocumented. I didn't intend for the README to give a
complete list, but in hindsight it may have been clearer if it did.

Those '0' and '1' relations are two more files associated with the
TID-CRID mappings -- those ones are for keeping track of mapping
updates.

+ oid = vci_create_relation(GenRelName(indexRel,
VCI_COLUMN_ID_TID_CRID_UPDATE, '0'), indexRel, indexInfo,
VCI_RELTYPE_TIDCRID);
+ vci_SetMainRelVar(vmr_info, vcimrv_tid_crid_update_oid_0, 0, oid);
+
+ oid = vci_create_relation(GenRelName(indexRel,
VCI_COLUMN_ID_TID_CRID_UPDATE, '1'), indexRel, indexInfo,
VCI_RELTYPE_TIDCRID);
+ vci_SetMainRelVar(vmr_info, vcimrv_tid_crid_update_oid_1, 0, oid);

The README was updated like below; it will be available next time new
patches are posted

------
Internal Relation Types:
- -1:  TID relation (maps CRID to original TID)
- -2:  NULL vector (bit array for NULL values)
- -3:  Delete vector (bit array for deleted records)
- -5:  TID-CRID mappings
- -6:  TID-CRID mappings (update list)
- -9:  Data WOS (buffered row data)
- -10: Whiteout WOS (deletion markers)
- 0-N: ROS column data relations (one per indexed column)

Example:
For a VCI index on sales(customer_id, amount, date):

Generated relations include:
vci_0000012345_00000_d  → Column 0 data (customer_id)
vci_0000012345_00000_m    ... and metadata
vci_0000012345_00001_d  → Column 1 data (amount)
vci_0000012345_00001_m    ... and metadata
vci_0000012345_00002_d  → Column 2 data (date)
vci_0000012345_00002_m    ... and metadata
vci_0000012345_65526_d  → Whiteout WOS
vci_0000012345_65527_d  → Data WOS
vci_0000012345_65531_d  → TID-CRID mappings
vci_0000012345_65531_m    ... and metadata
vci_0000012345_65530_0    ... and update list #0
vci_0000012345_65530_1    ... and update list #1
vci_0000012345_65533_d  → Delete vector
vci_0000012345_65533_m    ... and metadata
vci_0000012345_65534_d  → NULL vector
vci_0000012345_65534_m    ... and metadata
vci_0000012345_65535_d  → TID relation
vci_0000012345_65535_m    ... and metadata
------

======
Kind Regards,
Peter Smith.
Fujitsu Australia