Adding, removing, and renaming a index

Warning

Supported only for row-oriented tables. Support for column-oriented tables is currently under development.

Alert

The functionality of vector indexes is available in the test mode in main. This functionality will be fully available in version 25.1.

The following features are not supported:

  • Index update: the main table can be modified, but the existing index will not be updated. A new index is to be built to reflect the changes. If necessary, the existing index can be atomically replaced with the newly built one.
  • Building an index for vectors with bit quantization.

These limitations may be removed in future versions.

Adding an index

ADD INDEX: Adds an index with the specified name and type for a given set of columns. The code below adds a global index named title_index for the title column.

ALTER TABLE `series` ADD INDEX `title_index` GLOBAL ON (`title`);

You can specify any secondary index parameters from the CREATE TABLE command.
You can specify any vector index parameters from the CREATE TABLE command.

You can also add a secondary index using the YDB CLI table index command.

Altering an index

Indexes have type-specific parameters that can be tuned. Global indexes, whether synchronous or asynchronous, are implemented as hidden tables, and their automatic partitioning and followers settings can be adjusted just like those of regular tables.

Note

Currently, specifying secondary index partitioning settings during index creation is not supported in either the ALTER TABLE ADD INDEX or the CREATE TABLE INDEX statements.

ALTER TABLE <table_name> ALTER INDEX <index_name> SET <setting_name> <value>;
ALTER TABLE <table_name> ALTER INDEX <index_name> SET (<setting_name_1> = <value_1>, ...);

Note

These settings cannot be reset.

  • <value>: The new value for the setting. Possible values include:
    • ENABLED or DISABLED for the AUTO_PARTITIONING_BY_SIZE and AUTO_PARTITIONING_BY_LOAD settings
    • "PER_AZ:<count>" or "ANY_AZ:<count>" where <count> is the number of replicas for the READ_REPLICAS_SETTINGS
    • An integer of Uint64 type for the other settings

Example

The query in the following example enables automatic partitioning by load for the index named title_index of the table series, sets its minimum partition count to 5, and enables one follower per AZ for every partition:

ALTER TABLE `series` ALTER INDEX `title_index` SET (
    AUTO_PARTITIONING_BY_LOAD = ENABLED,
    AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 5,
    READ_REPLICAS_SETTINGS = "PER_AZ:1"
);

Deleting an index

DROP INDEX: Deletes the index with the specified name. The code below deletes the index named title_index.

ALTER TABLE `series` DROP INDEX `title_index`;

You can also remove a index using the YDB CLI table index command.

Renaming an index

RENAME INDEX: Renames the index with the specified name.

If an index with the new name exists, an error is returned.

Replacement of atomic indexes under load is supported by the command ydb table index rename in the YDB CLI and by YDB SDK ad-hoc methods.

Example of index renaming:

ALTER TABLE `series` RENAME INDEX `title_index` TO `title_index_new`;
Previous
Overview
Next
COLUMN