[Postgresql] 파티션 테이블에 무중단으로 LIKE 전용 인덱스 생성하기

 

 

배경 — 왜 이 방법이 필요한가?

*운영 중인 대용량 테이블에 인덱스를 추가하면, 테이블 전체에 ACCESS SHARE LOCK이 걸려 쓰기가 차단됩니다. 

PostgreSQL은 CONCURRENTLY 옵션으로 이를 우회할 수 있지만, 파티션된 부모 테이블(partitioned table)에는 CONCURRENTLY를 직접 쓸 수 없습니다.

CREATE INDEX CONCURRENTLY ON your_parent_table ...
 → 에러 발생!
파티션된 테이블은 CONCURRENTLY를 지원하지 않습니다. (PostgreSQL 공식 제약)

 

해결책은 자식 파티션 각각에 CONCURRENTLY로 인덱스를 만든 뒤, 
부모에 껍데기(invalid) 인덱스를 만들고, 마지막으로 연결하는 것입니다.

참고로 아래와 같은 이유로 이번 인덱스 생성은 psql에서 해야합니다

Step 1 — 자식 파티션에 인덱스 생성

• 각 자식 파티션에 인덱스 생성
• 현재 월: CONCURRENTLY / 과거 월: 일반 CREATE INDEX

*pg_inherits 시스템 카탈로그를 조회해 부모 테이블의 모든 자식 파티션 목록을 동적으로 가져옵니다. 현재 월 파티션은 CONCURRENTLY로, 나머지는 일반 방식으로 인덱스를 생성하는 SQL을 동적으로 구성합니다.

-- pg_inherits로 자식 파티션 목록을 조회하고,
-- 현재 월이면 CONCURRENTLY, 그 외엔 일반 CREATE INDEX 구문을 동적으로 생성합니다.

psql -U postgres -d your_database -t -c "
SELECT format(
  'CREATE INDEX %s IF NOT EXISTS %I ON %I.%I (column1, column2);',

  CASE
    WHEN c.relname LIKE '%' || to_char(CURRENT_DATE, 'YYYY_MM') || '%'
      OR c.relname LIKE '%' || to_char(CURRENT_DATE, 'YYYYMM') || '%'
    THEN 'CONCURRENTLY'   -- 현재 월: 무중단 인덱스 생성
    ELSE ''               -- 과거 월: 빠른 일반 인덱스 생성
  END,

  'idx_' || c.relname || '_suffix',  -- 자식 인덱스 이름
  n.nspname,                        -- 스키마 이름 (자동 감지)
  c.relname                         -- 자식 파티션 테이블 이름 (자동 감지)
)
FROM pg_inherits i
JOIN pg_class     c ON i.inhrelid      = c.oid
JOIN pg_namespace n ON c.relnamespace  = n.oid
WHERE i.inhparent = 'your_parent_table'::regclass
" | psql -U postgres -d your_database

 

 

LIKE 전용 Operator class를 사용하지 않으려면 해당 ops 대신 일반 인덱스를 생성하면 된다. 대신 LIKE를 사용하고 부분 매칭이 들어가야한다면 위 연산자 클래스를 사용하는 것이 non-locale하며 바이트 단위로 비교하기 때문에 더 효율적이라고 한다.

https://dba.stackexchange.com/questions/53811/why-would-you-index-text-pattern-ops-on-a-text-column

 

Step 2 — 부모 테이블에 인덱스 생성 (ON ONLY)

• 부모 테이블에만 "껍데기" 인덱스 생성
• ON ONLY — 자식에는 전파되지 않음, 즉시 invalid 상태

*ON ONLY 키워드는 인덱스를 부모 테이블에만 생성하고 자식 파티션에 자동 전파하지 않습니다. 이 인덱스는 처음에 invalid(무효) 상태로 생성됩니다. Step 3에서 자식 인덱스를 연결하면 비로소 valid(유효) 상태가 됩니다.

psql -U postgres -d your_database -c "
CREATE INDEX IF NOT EXISTS idx_your_parent_table_suffix
  ON ONLY your_parent_table (column1, column2);
  --  ↑ ON ONLY: 자식 파티션에 전파하지 않음
  --  이 시점에서 인덱스는 'invalid' 상태
"

 

Step 3 — 자식 인덱스를 부모에 연결

• ATTACH PARTITION으로 인덱스 연결
• 모든 자식 인덱스가 연결되면 부모 인덱스가 valid 상태로 전환

*ALTER INDEX ... ATTACH PARTITION은 이미 생성된 자식 인덱스를 부모 인덱스에 연결합니다. 이 명령은 테이블 스캔 없이 메타데이터만 변경하므로 매우 빠르게 완료됩니다. 모든 자식 파티션의 인덱스가 연결되면 부모 인덱스가 자동으로 valid 상태로 전환됩니다.

psql -U postgres -d your_database -t -c "
SELECT format(
  'ALTER INDEX idx_your_parent_table_suffix ATTACH PARTITION %I;',
  'idx_' || c.relname || '_suffix'
)
FROM pg_inherits i
JOIN pg_class     c ON i.inhrelid      = c.oid
JOIN pg_namespace n ON c.relnamespace  = n.oid
WHERE i.inhparent = 'your_parent_table'::regclass
" | psql -U postgres -d your_database

 

인덱스 상태 확인

-- 인덱스 생성 후 valid 상태인지 확인
SELECT
    indexrelid::regclass  AS index_name,
    indisvalid            AS is_valid,
    indisready            AS is_ready
FROM  pg_index
WHERE indexrelid::regclass::text
        LIKE '%your_parent_table%';

-- is_valid = true  → 정상 사용 가능
-- is_valid = false → 아직 invalid (연결 미완료)

 

인덱스 삭제 (Cascade로 한번에)

*파티션 테이블의 부모 인덱스를 삭제하면 연결된 모든 자식 파티션의 인덱스도 함께 삭제됩니다. 개별 자식 인덱스를 따로 삭제할 필요가 없습니다.

-- 부모 인덱스 삭제 → 모든 자식 인덱스도 CASCADE 삭제됨
psql -U postgres -d your_database -c "
DROP INDEX IF EXISTS idx_your_parent_table_suffix;
"