Managing indexes

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

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.

Important

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.

Note the following constraints when creating indexes:

• An index can only be created on a single top-level field. Composite, nested, unique, and function-based indexes are 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).

  QLDB doesn't 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.

• 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. For more information, see Managing indexes.

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.

Using the console

You can also use the AWS Management 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, choose the table name whose index you want to check.

5. On the table details page, locate the Indexed fields card. The Index status column displays the current status of each index on the table.

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)

Note

The clause WITH (purge = true) is required for all DROP INDEX statements, and true is currently the only supported value.

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

Using the console

You can also use the AWS Management 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, choose the table name whose index you want to drop.

5. On the table details page, locate the Indexed fields card. 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.