Índices assíncronos no Aurora DSQL

O comando CREATE INDEX ASYNC cria um índice em uma ou mais colunas de uma tabela especificada. Esse comando é uma operação de DDL assíncrona que não bloqueia outras transações. Ao executar CREATE INDEX ASYNC, o Aurora DSQL exibe imediatamente um job_id.

Você pode monitorar o status dessa tarefa assíncrona usando a visualização de sistema sys.jobs. Enquanto o trabalho de criação de índice está em andamento, você pode usar estes procedimentos e comandos:

Quando o Aurora DSQL conclui uma tarefa de indexação assíncrona, ele atualiza o catálogo do sistema para mostrar que o índice está ativo. Se outras transações fizerem referência aos objetos no mesmo namespace nesse momento, você poderá ver um erro de simultaneidade.

Para obter a sintaxe com suporte e uma descrição de cada parâmetro, consulte CREATE INDEX.

Observações de uso

Considere as seguintes diretrizes:

Criar um índice: exemplo

O exemplo a seguir demonstra como criar um esquema, uma tabela e, em seguida, um índice.

Consultar o status da criação do índice: exemplo

Consulte a visualização sys.jobs do sistema para verificar o status de criação do índice, conforme mostrado no exemplo a seguir.

SELECT * FROM sys.jobs where job_id = 'wqhu6ewifze5xitg3umt24h5ua';

O Aurora DSQL exibe uma resposta semelhante à seguinte:

           job_id           |  status   | details |  job_type   | class_id | object_id |    object_name    |       start_time       |      update_time
----------------------------+-----------+---------+-------------+----------+-----------+-------------------+------------------------+------------------------
 wqhu6ewifze5xitg3umt24h5ua | completed |         | INDEX_BUILD |     1259 |     26433 | public.nt2_c1_idx | 2025-09-25 22:07:31+00 | 2025-09-25 22:07:46+00

A coluna de status pode ser um dos seguintes valores:

Você também pode consultar o estado do índice por meio das tabelas pg_index e pg_class do catálogo. Os atributos indisvalid e indisimmediate, especificamente, podem indicar em que estado o índice está. Embora o Aurora DSQL crie o índice, o status inicial dele é INVALID. O sinalizador indisvalid do índice exibe FALSE ou f, o que indica que o índice não é válido. Se o sinalizador exibir TRUE ou t, isso significa que o índice está pronto.

SELECT relname AS index_name, indisvalid as is_valid, pg_get_indexdef(indexrelid) AS index_definition
from pg_index, pg_class
WHERE pg_class.oid = indexrelid AND indrelid = 'test.departments'::regclass;

    index_name    | is_valid |                                                 index_definition
------------------+----------+-------------------------------------------------------------------------------------------------------------------
 department_pkey  |     t    | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size)
 test_index1      |     t    | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size)

Falhas na criação de índices únicos

Se sua tarefa assíncrona de criação de índice único mostrar um estado de falha com o detalhe Found duplicate key while validating index for UCVs, isso indica que não foi possível criar um índice único devido a violações à restrição de unicidade.

Detectar violações de unicidade em tabelas primárias

A consulta SQL a seguir ajuda você a identificar valores duplicados em uma coluna especificada da tabela. Isso é particularmente útil quando você precisa impor unicidade em uma coluna que no momento não está definida como chave primária ou não tem uma restrição única, como endereços de e-mail em uma tabela de usuários.

Os exemplos abaixo demonstram como criar uma tabela de usuários de exemplo, preenchê-la com dados de teste contendo duplicatas conhecidas e, em seguida, executar a consulta de detecção.

Definir esquema de tabela

-- Drop the table if it exists
DROP TABLE IF EXISTS users;

-- Create the users table with a simple integer primary key
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);      

Inserir dados de exemplo que incluam conjuntos de endereços de e-mail duplicados

-- Insert sample data with explicit IDs
INSERT INTO users (user_id, email, first_name, last_name) VALUES
    (1, 'john.doe@example.com', 'John', 'Doe'),
    (2, 'jane.smith@example.com', 'Jane', 'Smith'),
    (3, 'john.doe@example.com', 'Johnny', 'Doe'),
    (4, 'alice.wong@example.com', 'Alice', 'Wong'),
    (5, 'bob.jones@example.com', 'Bob', 'Jones'),
    (6, 'alice.wong@example.com', 'Alicia', 'Wong'),
    (7, 'bob.jones@example.com', 'Robert', 'Jones');

Executar consulta de detecção de duplicatas

-- Query to find duplicates
WITH duplicates AS (
    SELECT email, COUNT(*) as duplicate_count
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
SELECT u.*, d.duplicate_count
FROM users u
INNER JOIN duplicates d ON u.email = d.email
ORDER BY u.email, u.user_id;

Visualizar todos os registros com endereços de e-mail duplicados

 user_id |         email          | first_name | last_name |         created_at         | duplicate_count
---------+------------------------+------------+-----------+----------------------------+-----------------
       4 | akua.mansa@example.com | Akua       | Mansa     | 2025-05-21 20:55:53.714432 |               2
       6 | akua.mansa@example.com | Akua       | Mansa     | 2025-05-21 20:55:53.714432 |               2
       1 | john.doe@example.com   | John       | Doe       | 2025-05-21 20:55:53.714432 |               2
       3 | john.doe@example.com   | Johnny     | Doe       | 2025-05-21 20:55:53.714432 |               2
(4 rows)

Se tentássemos a instrução de criação do índice agora, ela falharia:

postgres=> CREATE UNIQUE INDEX ASYNC idx_users_email ON users(email);
      job_id
----------------------------
 ve32upmjz5dgdknpbleeca5tri
(1 row)

postgres=> select * from sys.jobs;
           job_id           |  status   |                       details                       |  job_type   | class_id | object_id |      object_name       |       start_time       |      update_time
----------------------------+-----------+-----------------------------------------------------+-------------+----------+-----------+------------------------+------------------------+------------------------
 qpn6aqlkijgmzilyidcpwrpova | completed |                                                     | DROP        |     1259 |     26384 |                        | 2025-05-20 00:47:10+00 | 2025-05-20 00:47:32+00
 ve32upmjz5dgdknpbleeca5tri | failed    | Found duplicate key while validating index for UCVs | INDEX_BUILD |     1259 |     26396 | public.idx_users_email | 2025-05-20 00:49:49+00 | 2025-05-20 00:49:56+00
(2 rows)