Aurora DSQL의 비동기 인덱스

CREATE INDEX ASYNC 명령은 지정된 테이블의 하나 이상의 열에 인덱스를 생성합니다. 이 명령은 다른 트랜잭션을 차단하지 않는 비동기 DDL 작업입니다. CREATE INDEX ASYNC를 실행하면 Aurora DSQL이 즉시 job_id를 반환합니다.

sys.jobs 시스템 뷰를 사용하여 이 비동기 작업의 상태를 모니터링할 수 있습니다. 인덱스 생성 작업이 진행 중일 때 다음 절차와 명령을 사용할 수 있습니다.

Aurora DSQL이 비동기 인덱스 작업을 완료하면 시스템 카탈로그를 업데이트하여 인덱스가 활성 상태임을 표시합니다. 현재 다른 트랜잭션이 동일한 네임스페이스의 객체를 참조하는 경우 동시성 오류가 표시될 수 있습니다.

구문

CREATE INDEX ASYNC는 다음 구문을 사용합니다.

CREATE [ UNIQUE ] INDEX ASYNC [ IF NOT EXISTS ] name ON table_name 
     ( { column_name } [ NULLS { FIRST | LAST } ] ) 
     [ INCLUDE ( column_name [, ...] ) ] 
     [ NULLS [ NOT ] DISTINCT ]

파라미터

사용 노트

다음 지침을 참고하세요.

인덱스 생성: 예시

다음 예시에서는 스키마, 테이블, 인덱스를 생성하는 방법을 보여줍니다.

인덱스 생성 상태 쿼리: 예

다음 예시와 같이 sys.jobs 시스템 뷰를 쿼리하여 인덱스의 생성 상태를 확인합니다.

SELECT * FROM sys.jobs

Aurora DSQL은 다음과 유사한 응답을 반환합니다.

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

상태 열은 다음 값 중 하나일 수 있습니다.

카탈로그 테이블 pg_index 및 pg_class를 통해 인덱스의 상태를 쿼리할 수도 있습니다. 특히 indisvalid 및 indisimmediate 속성은 인덱스의 상태를 알려줄 수 있습니다. Aurora DSQL이 인덱스를 생성하는 동안 초기 상태는 INVALID입니다. 인덱스의 indisvalid 플래그는 인덱스가 유효하지 않음을 나타내는 FALSE 또는 f를 반환합니다. 플래그가 TRUE 또는 t를 반환하면 인덱스가 준비된 것입니다.

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)

고유 인덱스 빌드 실패

비동기 고유 인덱스 빌드 작업에서 Found duplicate key while validating index for UCVs라는 세부 정보와 함께 실패 상태가 표시되는 경우 고유성 제약 조건 위반으로 인해 고유 인덱스를 빌드할 수 없음을 나타냅니다.

기본 테이블에서 고유성 위반 감지

다음 SQL 쿼리는 테이블의 지정된 열에서 중복 값을 식별하는 데 도움이 됩니다. 이는 현재 프라이머리 키로 설정되지 않았거나 사용자 테이블의 이메일 주소와 같은 고유한 제약 조건이 없는 열에 고유성을 적용해야 하는 경우에 특히 유용합니다.

아래 예시에서는 샘플 사용자 테이블을 생성하고, 알려진 중복이 포함된 테스트 데이터로 채우고, 감지 쿼리를 실행하는 방법을 보여줍니다.

테이블 스키마 정의

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

중복 이메일 주소 세트가 포함된 샘플 데이터 삽입

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

중복 탐지 쿼리 실행

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

이메일 주소가 중복된 모든 레코드 보기

 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)

인덱스 생성 문을 지금 시도하면 실패합니다.

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)