# Í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: **`sys.wait_for_job(job_id)'your_index_creation_job_id'`** Bloqueia a sessão atual até que o trabalho especificado seja concluído ou falhe. Retorna um booliano indicando êxito ou falha. **`DROP INDEX`** Cancela uma tarefa de criação de índice em andamento. Quando a criação assíncrona do índice é concluída, o Aurora DSQL atualiza o catálogo do sistema para marcar o índice como ativo. Observe que as transações simultâneas que acessam objetos no mesmo namespace durante essa atualização podem encontrar erros de simultaneidade. 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. ## Sintaxe `CREATE INDEX ASYNC` usa a sintaxe a seguir. ``` CREATE [ UNIQUE ] INDEX ASYNC [ IF NOT EXISTS ] name ON table_name ( { column_name } [ NULLS { FIRST | LAST } ] ) [ INCLUDE ( column_name [, ...] ) ] [ NULLS [ NOT ] DISTINCT ] ``` ## Parâmetros **`UNIQUE`** Instrui o Aurora DSQL a verificar se há valores duplicados na tabela ao criar o índice e sempre que você adicionar dados. Se você especificar esse parâmetro, as operações de inserção e atualização que resultariam em entradas duplicadas gerarão um erro. **`IF NOT EXISTS`** Instrui o Aurora DSQL a não lançar uma exceção se já houver um índice com o mesmo nome. Nessa situação, o Aurora DSQL não cria o índice. Observe que o índice que você está tentando criar pode ter uma estrutura muito diferente da estrutura do índice existente. Se você especificar esse parâmetro, o nome do índice será obrigatório. **`name`** O nome do índice. Não é possível incluir o nome do esquema nesse parâmetro. O Aurora DSQL cria o índice no mesmo esquema da tabela principal. O nome do índice deve ser diferente do nome de qualquer outro objeto, como tabela ou índice, no esquema. Se você não especificar um nome, o Aurora DSQL gerará um nome automaticamente com base no nome da tabela principal e da coluna indexada. Por exemplo, se você executar `CREATE INDEX ASYNC on table1 (col1, col2)`, o Aurora DSQL atribuirá automaticamente o nome `table1_col1_col2_idx` ao índice. **`NULLS FIRST | LAST`** A ordem de classificação das colunas nulas e não nulas. `FIRST` indica que o Aurora DSQL deve classificar colunas nulas antes de colunas não nulas. `LAST` indica que o Aurora DSQL deve classificar colunas nulas após colunas não nulas. **`INCLUDE`** Uma lista de colunas a serem incluídas no índice como colunas não chave. Não é possível usar uma coluna não chave em uma qualificação de pesquisa de verificação de índice. O Aurora DSQL ignora a coluna em termos de exclusividade para um índice. **`NULLS DISTINCT | NULLS NOT DISTINCT`** Especifica se o Aurora DSQL deve considerar valores nulos como distintos em um índice exclusivo. O padrão é `DISTINCT`, o que significa que um índice exclusivo pode conter vários valores nulos em uma coluna. `NOT DISTINCT` indica que um índice não pode conter vários valores nulos em uma coluna. ## Observações de uso Considere as seguintes diretrizes: + O comando `CREATE INDEX ASYNC` não introduz bloqueios. Isso também não afeta a tabela base que o Aurora DSQL usa para criar o índice. + Durante as operações de migração do esquema, o procedimento `sys.wait_for_job(job_id)'your_index_creation_job_id'` é útil. Ele garante que as operações subsequentes de DDL e DML tenham como alvo o índice recém-criado. + Sempre que o Aurora DSQL executa uma nova tarefa assíncrona, ele verifica a visualização `sys.jobs` e exclui tarefas com status `completed` ou `failed` por mais de 30 minutos. Portanto, `sys.jobs` mostra principalmente as tarefas em andamento e não contém informações sobre tarefas antigas. + Se o Aurora DSQL não conseguir criar um índice assíncrono, o índice permanecerá `INVALID`. Para índices exclusivos, as operações de DML estão sujeitas a restrições de exclusividade até que você elimine o índice. Recomendamos que você elimine os índices inválidos e os recrie. ## Criar um índice: exemplo O exemplo a seguir demonstra como criar um esquema, uma tabela e, em seguida, um índice. 1. Crie uma tabela chamada `test.departments`. ``` CREATE SCHEMA test; CREATE TABLE test.departments (name varchar(255) primary key NOT null, manager varchar(255), size varchar(4)); ``` 1. Insira uma linha de dados na tabela. ``` INSERT INTO test.departments VALUES ('Human Resources', 'John Doe', '10') ``` 1. Crie um índice assíncrono. ``` CREATE INDEX ASYNC test_index on test.departments(name, manager, size); ``` O comando `CREATE INDEX` exibe um ID de trabalho, conforme mostrado abaixo. ``` job_id -------------------------- jh2gbtx4mzhgfkbimtgwn5j45y ``` O `job_id` indica que o Aurora DSQL enviou um novo trabalho para criar o índice. Você pode usar o procedimento `sys.wait_for_job(job_id)'your_index_creation_job_id'` para bloquear outros trabalhos na sessão até que o trabalho seja concluído ou atinja o tempo limite. ## 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: | Status | Descrição | | --- | --- | | submitted | A tarefa foi enviada, mas o Aurora DSQL ainda não começou a processá-la. | | processing | O Aurora DSQL está processando a tarefa. | | failed | A tarefa falhou. Consulte os detalhes da coluna para ter mais informações. Se o Aurora DSQL falhar ao criar o índice, ele não removerá automaticamente a definição do índice. Você deve remover o índice manualmente com o comando DROP INDEX. | | completed | O Aurora DSQL concluiu a tarefa com êxito. | 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. **Como resolver falhas na criação de índices únicos** 1. Remova todas as linhas da tabela primária que tenham entradas duplicadas para as chaves especificadas em seu índice secundário único. 1. Elimine o índice com falha. 1. Emita um novo comando create index. ## 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) ```