Índices assíncronos no Aurora DSQL - Amazon Aurora DSQL
SintaxeParâmetrosObservações de usoComo criar um índiceConsultar um índiceFalhas na criação de índices únicosViolações de unicidade

Í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.

nota

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));

  2. Insira uma linha de dados na tabela.

    INSERT INTO test.departments VALUES ('Human Resources', 'John Doe', '10')

  3. 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

O Aurora DSQL exibe uma resposta semelhante à seguinte:

           job_id           |   status   | details 
----------------------------+------------+---------
 vs3kcl3rt5ddpk3a6xcq57cmcy | completed  | 
 ihbyw2aoirfnrdfoc4ojnlamoq | processing |

A coluna de status pode ser um dos seguintes valores:

submitted processing failed completed
A tarefa foi enviada, mas o Aurora DSQL ainda não começou a processá-la. O Aurora DSQL está processando a tarefa. 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. Aurora DSQL

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.

  2. Elimine o índice com falha.

  3. 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)