Managing indexes - Amazon Quantum Ledger Database (Amazon QLDB)

Managing indexes

This section describes how to create and describe 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.

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.

  • Query performance is improved only when you use an equality predicate; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789).

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

  • Names of indexed fields 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.

  • 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, choose the Tables and indexes tab.

  5. Locate and expand the table name whose index you want to check. The Status column displays the index's current status.

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.

To drop a failed index, contact AWS Support by using the AWS Support Center console.

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 by contacting AWS Support. 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.