memcs: API for including columns into secondary index

[nshy]

Reviewers

• Team Lead: @sergepetrenko
• CTO: @sergos
• Guest reviewer: @locker

Changelog

2025-01-14

• add API/Introspection section
• add Persistence section
• add details to API/Creation section

2024-11-13

• rename option from include to covers
• mention possible option (will not be supported in the scope of the issue) to return only covered columns in get, select and such

Problem statement

The task is described in https://github.com/tarantool/tarantool-ee/issues/893. Existing scanner API allows to pass index id so this document is merely to come to terms how to specify which columns besides mentioned in the parts include into the index.

Proposed API

Proposed API is:

Creation

index:create_index(name, {parts = {key_column_1, ..., key_column_K}, covers={column_1, ..., column_N}})

covers may reference columns by name or by position as parts. The index key columns and primary index key columns are implicitly covered. The order of columns in the covers is not significant. The default value for option is {}. It is error if same column is specified twice. It is allowed to specify implicitly covered columns explicitly.

As proposed in the issue scan can be done only among covered columns. select and get an such will return all columns. In future we may introduce an option for these operations to return only covered columns, like index:select(key, {column_set = 'covered'}).

Introspection

Covered columns, both explicit and implicit, are visible in index object under covers key. The key is missing for the primary index. Column number in array are 1-based just as in case of describing index parts. The column numbers are sorted.

Example:

tarantool> box.space.test.index.sk
---
- unique: true
  parts:
  - fieldno: 3
    sort_order: asc
    type: string
    exclude_null: false
    is_nullable: false
  covers: [1, 3, 7, 10]
  id: 1
  space_id: 512
  type: TREE
  name: sk
...

Persistence

The covered columns are stored as an array of column numbers in existing opts field of _index space under covers key. Only explicitly covered columns are stored. Column numbers are 0-based just as in case of parts field of the space.

[locker]

This could be useful in Vinyl: #3095

[locker]

It's unclear what get and select should return for a covering index. They can't return all columns. Should they return nulls instead?

[locker]

Then this would be useless in Vinyl. Maybe, add an option for select that would make it return only covered fields? Also, the name cover would probably be better than include because that's what they usually call such indexes in other databases, e.g. in MySQL.

[nshy]

Discussed f2f that currently select and get will return all rows for covering index. In future we may add an option for these operations to return only covered columns. Like index:select(key, {columns_set = 'covered'}).

[locker]

We don't use the term "column" anywhere in the Lua API so it's probably better to call the select option fields. Also, please be more specific about the future API. What values will it take? Can I specify an arbitrary field set?

[sergepetrenko]

I don't like that we cover primary key fields implicitly. In case of memcs multiple secondary indexes will all store a copy of primary key columns then. Given that these are the most "dense" columns, memory impact might be significant.

What do you think about letting the user decide whether the primary key fields are covered or not?

In case they're not, a user will only be able to perfrom select/pairs with {fields = 'covered'}, and trying to use {fields = 'all'} will result in an error.

The user should also include primary key fields if his secondary index is non-unique, otherwise the order for matching secondary keys would be undefined.

[locker]

Sounds interesting, but we need to think about (a) deletion of a tuple from a space and (b) stable iterators in case of a non-unique index.

[locker]

Please describe how the new option will be presented in Lua (via box.space.index) and hot it will be stored in the box.space._index system space.

[sergepetrenko]

We decided not to implement this functionality yet.
Feel free to reopen when any further discussions or redesign are needed.