Managing indexes - Amazon Quantum Ledger Database (Amazon QLDB)

Managing indexes

This section describes how to create, describe, and drop indexes in Amazon QLDB. The quota for the number of indexes per table that you can create is defined in Quotas and limits in Amazon QLDB.

Creating indexes

As also described in Creating tables and indexes, you can use the CREATE INDEX statement to create an index on a table for a specified top-level field, as follows. The table name and the indexed field name are both case sensitive.

CREATE INDEX ON VehicleRegistration (VIN)
CREATE INDEX ON VehicleRegistration (LicensePlateNumber)

Each index that you create on a table has a system-assigned unique ID. To find this index ID, see the following section Describing indexes.

Indexes are optional in QLDB, but we strongly recommend using them to improve query performance for seek operations. Note the following constraints when creating indexes:

  • Indexes can only be created on a single top-level field. Composite, nested, unique, and function-based indexes are currently not supported.

  • You can create an index on any Ion data types, including list and struct. However, you can only do the indexed lookup by equality of the whole Ion value regardless of the Ion type. For example, when using a list type as an index, you can't do an indexed lookup by one item inside the list.

  • Query performance is improved only when you use an equality predicate; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789). Be sure to enclose the list of values in parentheses when you use the IN operator. Without the parentheses, the statement invokes a table scan rather than an indexed lookup.

    QLDB doesn't currently honor inequalities in query predicates. As a result, range filtered scans are not implemented.

  • Names of indexed fields are case sensitive and can have a maximum of 128 characters.

Warning

QLDB requires an index to efficiently look up a document. Without an index, QLDB needs to do a full table scan when reading documents. This can cause performance problems on large tables, including concurrency conflicts and transaction timeouts.

To avoid table scans, you must run statements with a WHERE predicate clause using an equality operator (= or IN) on an indexed field or a document ID. For more information, see Optimizing query performance in the Working with data and history topic.

Describing indexes

Index creation in QLDB is asynchronous. The amount of time it takes to finish building an index on a non-empty table varies depending on the table size. To check the status of an index build, you can query the system catalog table information_schema.user_tables.

For example, the following statement queries the system catalog for all indexes on the VehicleRegistration table.

SELECT VALUE indexes FROM information_schema.user_tables info, info.indexes indexes WHERE info.name = 'VehicleRegistration'
{
    indexId: "Djg2nt0yIs2GY0T29Kud1z",
    expr: "[VIN]",
    status: "ONLINE"
},
{
    indexId: "4tPW3fUhaVhDinRgKRLhGU",
    expr: "[LicensePlateNumber]",
    status: "FAILED",
    message: "aws.ledger.errors.InvalidEntityError: Document contains multiple values for indexed field: LicensePlateNumber"
}

Index fields

  • indexId – The unique ID of the index.

  • expr – The indexed document path. This field is a string in the form: [fieldName].

  • status – The index's current status. The status of an index can be one of the following values:

    • BUILDING – Is actively building the index for the table.

    • FINALIZING – Has finished building the index and is starting to activate it for use.

    • ONLINE – Is active and ready to use in queries. QLDB doesn't use the index in queries until the status is online.

    • FAILED – Is unable to build the index due to an unrecoverable error. Indexes in this state still count against your quota of indexes per table. For more information, see Common errors.

    • DELETING – Is actively deleting the index after a user dropped it.

  • message – The error message that describes the reason that the index has a FAILED status. This field is only included for failed indexes.

You can also use the QLDB console to check an index's status.

To check the status of an index (console)

  1. Sign in to the AWS Management Console, and open the Amazon QLDB console at https://console.aws.amazon.com/qldb.

  2. In the navigation pane, choose Ledgers.

  3. In the list of Ledgers, choose the ledger name whose indexes you want to manage.

  4. On the ledger details page, under the Tables tab, locate and expand the table name whose index you want to check.

  5. The Status column displays the index's current status.

Dropping indexes

Use the DROP INDEX statement to drop an index. When you drop an index, it's permanently deleted from the table.

First, find the index ID from information_schema.user_tables. For example, the following query returns the indexId of the indexed LicensePlateNumber field on the VehicleRegistration table.

SELECT indexes.indexId FROM information_schema.user_tables info, info.indexes indexes WHERE info.name = 'VehicleRegistration' and indexes.expr = '[LicensePlateNumber]'

Then, use this ID to drop the index. The following is an example that drops index ID 4tPW3fUhaVhDinRgKRLhGU. The index ID is a unique identifier that should be enclosed in double quotation marks.

DROP INDEX "4tPW3fUhaVhDinRgKRLhGU" ON VehicleRegistration WITH (purge = true)
Important

The keyword purge is case sensitive and must be all lowercase.

You can also use the QLDB console to drop an index.

To drop an index (console)

  1. Sign in to the AWS Management Console, and open the Amazon QLDB console at https://console.aws.amazon.com/qldb.

  2. In the navigation pane, choose Ledgers.

  3. In the list of Ledgers, choose the ledger name whose indexes you want to manage.

  4. On the ledger details page, under the Tables tab, locate and expand the table name whose index you want to drop.

  5. Select the index that you want to drop, and then choose Drop index.

Common errors

This section describes common errors that you might encounter when creating indexes, and suggests possible solutions.

Note

Indexes that have a status of FAILED still count against your quota of indexes per table. A failed index also prevents you from modifying or deleting any documents that caused the index creation to fail on the table.

You must explicitly drop the index to remove it from the quota.

Document contains multiple values for indexed field: fieldName.

QLDB is unable to build an index for the specified field name because the table contains a document with multiple values for the same field (that is, duplicate field names).

You must first drop the failed index. Then, make sure that all of the documents in the table have only one value for each field name before retrying the index creation. You can also create an index for another field that has no duplicates.

QLDB also returns this error if you try to insert a document that contains multiple values for a field that is already indexed on the table.

Exceeded indexes limit: Table tableName already has n indexes, and cannot create more.

QLDB enforces a limit of five indexes per table, including failed indexes. You must drop an existing index before creating a new one.

No defined index with identifier: indexId.

You tried to drop an index that doesn't exist for the specified table and index ID combination. To learn how to check existing indexes, see Describing indexes.