Adding, removing, and renaming a index

Adding an index

ADD INDEX — adds an index with the specified name and type for a given set of columns. Grammar:

ALTER TABLE `<table_name>`
  ADD INDEX `<index_name>`
    [GLOBAL|LOCAL]
    [UNIQUE]
    [SYNC|ASYNC]
    [USING <index_type>]
    ON ( <index_columns> )
    [COVER ( <cover_columns> )]
    [WITH ( <parameter_name> = <parameter_value>[, ...])]
  [,   ...]

• GLOBAL/LOCAL — global or local index; depending on the index type (<index_type>), only one of them may be available:

  • GLOBAL — an index implemented as a separate table or set of tables. Synchronous updates to such an index require distributed transactions.
  • LOCAL — a local index within a shard of a row-oriented or column-oriented table. Does not require distributed transactions for updates, but does not provide pruning during search.

• <index_name> — unique index name that will be used to access data.

• SYNC/ASYNC — synchronous or asynchronous writes to the index, synchronous by default.

• <index_type> - index type, currently supported:

  • secondary — secondary index. Only GLOBAL is available. This is the default value.
  • vector_kmeans_tree — vector index. Described in detail in Vector index.

• UNIQUE — creates an index with uniqueness guarantee for inserted values.

• <index_columns> — comma-separated list of column names from the created table that can be used for index searches. Must be specified.

• <cover_columns> — comma-separated list of column names from the created table that will be saved in the index in addition to search columns, providing the ability to get additional data without accessing the table. Empty by default.

• <parameter_name> and <parameter_value> — index parameters specific to a particular <index_type>.

Parameters specific to vector indexes:

• common parameters for all vector indexes:
  • vector_dimension - embedding vector dimensionality (16384 or less)
  • vector_type - vector value type (float, uint8, int8, or bit)
  • distance - distance function (cosine, manhattan, or euclidean), mutually exclusive with similarity
    • similarity - similarity function (inner_product or cosine), mutually exclusive with distance
• specific parameters for vector_kmeans_tree (see Vector Index Type `vector_kmeans_tree` {#kmeans-tree-type}):
  • clusters - number of centroids for k-means algorithm (values greater than 1000 may degrade performance)
  • levels - number of levels in the tree

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

Examples

A regular secondary index:

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

A vector index:

ALTER TABLE `series`
  INDEX emb_cosine_idx GLOBAL SYNC USING vector_kmeans_tree
  ON (embedding) COVER (title)
  WITH (
    distance="cosine",
    vector_type="float",
    vector_dimension=512,
    clusters=128,
    levels=2
  );

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.

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>, ...);

• <table_name>: The name of the table whose index is to be modified.

• <index_name>: The name of the index to be modified.

• <setting_name>: The name of the setting to be modified, which should be one of the following:

  • AUTO_PARTITIONING_BY_SIZE
  • AUTO_PARTITIONING_BY_LOAD
  • AUTO_PARTITIONING_PARTITION_SIZE_MB
  • AUTO_PARTITIONING_MIN_PARTITIONS_COUNT
  • AUTO_PARTITIONING_MAX_PARTITIONS_COUNT
  • READ_REPLICAS_SETTINGS

• <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`;