Adding, deleting, and renaming an index

Adding an index

ADD INDEX — adds an index with the specified name and type for the given set of columns in row tables. 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 a set of tables. Synchronous update of such an index requires distributed transactions.
  • LOCAL — a local index within a shard of a columnar or row-based table, does not require distributed transactions during update, but does not provide pruning during search.

• <index_name> — unique index name by which data can be accessed.

• SYNC/ASYNC — indicator of index synchrony.

  • SYNC - synchronous index. Default value.
  • ASYNC - asynchronous index.

• UNIQUE — indicator of a unique secondary index. A unique index must be global synchronous (GLOBAL UNIQUE SYNC) and must not contain the USING <index_type> construct.

• <index_type> — index type, currently supported:

  • secondary — secondary index. Only GLOBAL mode is available for secondary indexes. This is the default index type.
  • vector_kmeans_tree — vector index. Described in detail in Vector index.
  • fulltext_plain — basic fulltext index. Described in detail in Fulltext index.
  • fulltext_relevance — fulltext index with BM25 statistics for relevance scoring. Described in detail in Fulltext index.
  • json — JSON index to speed up JSON_EXISTS and JSON_VALUE predicates on a column of type Json or JsonDocument. Described in more detail in JSON-index.
  • bloom_filter — local Bloom index. Available only for LOCAL. See ALTER TABLE ADD INDEX.
  • bloom_ngram_filter — local N-gram Bloom index. Available only for LOCAL. See ALTER TABLE ADD INDEX.
  • min_max — local min/max index. Available only for LOCAL. See ALTER TABLE ADD INDEX.

• <index_columns> — comma-separated list of column names for the table being created. This list defines the composition and order of columns included in the index key. Must be specified. The index key will include both the columns listed and the columns from the table's primary key.

• <cover_columns> — comma-separated list of column names from the created table that will be saved in the index in addition to index key 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>. Some index parameters cannot be specified during index creation. See Altering an index.

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

Parameters for all index types:

• maximum number of parallel handlers based on partitions involved in index building (an integer between 1 and MaxBuildIndexShardsInFlight from SchemeShardConfig).

  • If the parameter is not specified, the default value 32 or MaxBuildIndexShardsInFlight is currently used, whichever is smaller. MaxBuildIndexShardsInFlight defaults to 1000. In future versions, the default parallelism selection logic may change.
  • You can set a lower limit to reduce the impact of index building on database performance.
  • You can also set a higher limit to speed up index building if you have enough hardware resources.

Parameters specific to vector indexes:

• common parameters for all vector indexes:
  • vector_dimension - embedding vector dimensionality (should be between 1 and 16384)
  • vector_type - vector value type (float, uint8, or int8)
  • 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 (read more about the index type):
  • clusters - number of centroids for k-means algorithm (should be between 2 and 2048)
  • levels - number of levels in the tree (should be between 1 and 16)
  • overlap_clusters - the number of nearest clusters to add each vector to (default 1)
  • adaptive_clusters - for filtered indexes, automatically choose the number of clusters per filtering-column value based on how many vectors that value has (true or false, default false). See Adaptive clusters.
  • the total number of nodes in the tree, calculated as clusters raised to the power of levels, should be no more than 1073741824
  • the product of vector_dimension and clusters should be no more than 4194304

Parameters specific to full-text indexes:

• common parameters for all fulltext indexes:
  • tokenizer - tokenizer type (standard, whitespace, or keyword)
  • use_filter_lowercase - lowercase filter (true or false)
  • use_filter_length - token length filter (true or false); when true, tokens shorter than filter_length_min or longer than filter_length_max are not indexed and are ignored during search
  • filter_length_min - minimum token length (positive integer); only applied when use_filter_length=true
  • filter_length_max - maximum token length (positive integer); only applied when use_filter_length=true
  • use_filter_snowball - Snowball stemmer filter (true or false)
  • language - language for the Snowball stemmer (for example, english, russian)
  • use_filter_ngram - n-gram filter (true or false)
  • use_filter_edge_ngram - edge n-gram filter (true or false)
  • filter_ngram_min_length - minimum n-gram length (positive integer)
  • filter_ngram_max_length - maximum n-gram length (positive integer)

Parameters of local bloom indexes

• bloom_filter
  • false_positive_probability: Target false-positive rate of the filter: the fraction of fragments that are not skipped even though the requested value is not there (range (0, 1)). A lower value reduces extra reads but increases index size.
    • If omitted, the default is 0.0001 for row-oriented tables and 0.1 for column-oriented tables. The stricter default on row-oriented tables matches OLTP point lookups; on column-oriented tables it reflects analytical scans, where a larger trade-off between index size and fragment skipping is acceptable.
  • Indexed columns: YQL types that support equality comparison, except Yson, Json, and JsonDocument (see comparison operators). One column in a column-oriented table; multiple columns in a row-oriented table. On row-oriented tables, the indexed columns must form a left prefix of the primary key.
• bloom_ngram_filter (String and Utf8 columns; column-oriented tables only)
  • ngram_size: N-gram length, an integer from 3 to 8 (default 3).
  • false_positive_probability: Target false-positive rate (range (0, 1); default 0.1).
  • case_sensitive: Whether n-grams respect character case: true or false (default true).

Parameters of the local min_max index

The min_max index has no specific parameters WITH (...).

Limitations

The ADD INDEX operation for creating global secondary (GLOBAL, UNIQUE, etc.) and vector indexes is supported only for row tables. For columnar tables, only local indexes are supported via ADD INDEX: bloom index and min_max index.

Features of local bloom indexes:

• The index is always local (LOCAL); there is no global variant.
• Queries do not use the VIEW <index> syntax (unlike, for example, fulltext indexes).
• The filter is applied on read only to data fragments where an index block was already stored during a write or portion merge, other fragments are not skipped by this index until merge.
• On row-oriented tables, bloom_filter is implemented as a prefix bloom filter: the indexed columns must form a left prefix of the primary key. The filter is built over that key prefix and accelerates point lookups and range scans that constrain the prefix. Multiple bloom indexes over the same prefix length are not allowed.

Features of the local min_max index:

• The index is always local (LOCAL); there is no global variant.
• Queries do not use the VIEW <index> syntax (unlike, for example, full-text indexes).
• The filter is applied during reads only to data fragments for which the minimum and maximum values of the indexed column have already been computed and stored with the table data at write time or merge. For other fragments, no skip by this index is performed.

Examples

Secondary index:

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

Vector index:

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

Full-text index:

ALTER TABLE `series`
  ADD INDEX ft_idx GLOBAL USING fulltext_plain
  ON (title)
  WITH (tokenizer=standard, use_filter_lowercase=true);

JSON index:

ALTER TABLE `series`
  ADD INDEX json_idx GLOBAL USING json
  ON (metadata);

Bloom index:

ALTER TABLE `/Root/Table`
  ADD INDEX idx_bloom LOCAL USING bloom_filter
  ON (resource_id)
  WITH (false_positive_probability = 0.01);

Bloom n-gram index:

ALTER TABLE `/Root/Table`
  ADD INDEX idx_ngram LOCAL USING bloom_ngram_filter
  ON (message)
  WITH (
    ngram_size = 3,
    false_positive_probability = 0.01,
    case_sensitive = true
  );

min_max index:

ALTER TABLE `/Root/Table`
  ADD INDEX idx_created_at LOCAL USING min_max
  ON (created_at);

Changing index parameters

Indexes have type-dependent parameters that you can configure. Global indexes, synchronous or asynchronous, are implemented as hidden tables, and their automatic partitioning and replica parameters can be adjusted in the same way as regular table settings.

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> - name of the table whose index needs to be changed.

• <index_name> - name of the index to change.

• <setting_name> - name of the parameter to change. The set of allowed parameters depends on the index type:

  • for global secondary indexes:

    • 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

  • for local bloom indexes (see Parameters of local bloom indexes):

    • FALSE_POSITIVE_PROBABILITY
    • NGRAM_SIZE and CASE_SENSITIVE (only for bloom_ngram_filter)

  • The min_max index does not support ALTER INDEX.

• <value> - new parameter value. Possible values include:

  • ENABLED or DISABLED for the AUTO_PARTITIONING_BY_SIZE and AUTO_PARTITIONING_BY_LOAD parameters
  • "PER_AZ:<count>" or "ANY_AZ:<count>" where <count> is the number of replicas for READ_REPLICAS_SETTINGS
  • for other parameters — an integer of type Uint64
  • for FALSE_POSITIVE_PROBABILITY — a floating-point number in the range (0, 1); a smaller value usually reduces the number of false positives but increases the index size
  • for NGRAM_SIZE — an integer in the range from 3 to 8 (usually recommended to start with 3)
  • for CASE_SENSITIVE — true or false

Example

The code in the following example enables automatic partitioning by load for the index named title_index in the table series, sets the minimum number of partitions to 5, and starts one replica in each availability zone (AZ) for each 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"
);

For local bloom indexes, you can also change their specific parameters, for example:

ALTER TABLE `/Root/Table` ALTER INDEX idx_ngram SET (
    ngram_size = 4,
    false_positive_probability = 0.005,
    case_sensitive = false
);

Deleting an index

DROP INDEX — deletes the index with the specified name. The code below will delete the index named title_index.

ALTER TABLE `series` DROP INDEX `title_index`;

You can also delete an index using the table index YDB CLI command.

Renaming a secondary index

RENAME INDEX — renames the index with the specified name. If an index with the new name already exists, an error will be returned.

The ability to atomically replace an index under load is supported by the ydb table index rename YDB CLI command and specialized YDB SDK methods.

This applies to global secondary indexes (hidden index table and --replace mode). Local bloom indexes are not applicable to such atomic replacement under load.

Example of renaming an index:

ALTER TABLE `series` RENAME INDEX `title_index` TO `title_index_new`;