리인덱스(Reindex)는 아래와 같은 상황일 때 수행합니다.

• 인덱스가 손상되었을 경우
• 인덱스가 비대해져, 비어있는 공간이 많아졌을 경우
• 인덱스에 대한 스토리지 매개변수를 변경했으며, 변경 사항 적용 여부 확인할 경우
• CONCURRENTLY 옵션으로 인덱스 빌드가 실패해 “INVALID” 상태에서 효율적으로 다시 작성하길 원할 경우

방법

리인덱스 대상 판단 기준

인덱스 손상 여부 확인

1. pg_amcheck라는 바이너리를 통해 데이터베이스 내 손상된 인덱스를 확인합니다.

$ pg_amcheck -d 데이터베이스명 --heapallindexed --rootdescend

• -d 옵션으로, 확인할 데이터베이스를 지정합니다.
• --heapallindexed 옵션으로 모든 힙 튜플이 인덱스의 인덱스 튜플로 존재하는지 확인합니다.
• --rootdescend 옵션으로 각 튜플에 대한 루트 페이지에서 새 검색을 수행하여 리프 수준에서 튜플을 다시 찾습니다. 이때 amcheck의 bt_index_parent_check 함수가 암시적으로 사용되며, 이 함수는 2번에서 다시 언급됩니다. 
   

2. 같이 제공되는 함수를 통해 확인합니다.

다음 두 가지 함수를 통해 확인할 수 있습니다.

• bt_index_check(index regclass, heapallindexed boolean) returns void
• bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean) returns void

두 가지 모두 인덱스 손상을 확인하는 함수이지만, bt_index_parent_check는 bt_index_check의 상위 집합으로 간주할 수 있습니다. 

bt_index_parent_check는 부모/자식 관계에 걸쳐서 이상 여부를 확인합니다.

인덱스의 비어있는 공간 확인

pgstattuple extension에서 제공하는 pgstatindex 함수를 이용해 확인 가능합니다.
pgstattuple extension도 contrib에 포함되어 있어서 create extension을 통해 바로 사용 가능합니다.

다음은 pgstatindex함수에 대한 내용입니다.

pgstatindex(regclass) returns record

pgstatindex가 반환하는 record 값들 중, avg_leaf_density를 이용합니다. avg_leaf_density는 리프 페이지의 평균 밀도로, 인덱스에서 데이터가 차지하는 실제 사용 가능한 공간 비율입니다. 즉, 100에서 이 비율을 뺀 나머지가 인덱스의 비어있는 공간의 비율(팽창 되어있는 비율)이 됩니다.

확인하는 쿼리의 예시는 다음과 같습니다.

tester=# select 100 - (pgstatindex('인덱스명')).avg_leaf_density as index_bloat_ratio;

인덱스에 대한 스토리지 매개변수 변경

인덱스 변경 옵션 중, fillfactor를 수정할 경우에 해당되는 항목입니다.

fillfactor는 페이지를 채우는 비율을 나타내는 옵션으로, 10~100 사이의 백분율로 설정할 수 있습니다(default = 100). INSERT 작업 시 해당 비율까지 테이블 페이지를 채우며, 비워둔 공간은 UPDATE 수행 시 사용됩니다.

fillfactor 수정은 다음과 같이 수행할 수 있고, 수정 이후 반드시 reindex를 수행해야 합니다.

tester=# alter index 인덱스명 set (fillfactor = 80);

참고

• fillfactor는 앞에서 설명된 인덱스의 비어있는 공간의 비율(팽창된 비율)과도 연관이 있습니다.
• avg_leaf_density는 이 fillfactor의 수치를 넘어갈 수 없습니다. 
• 또한, 인덱스가 rebuild를 해야 할 만큼 팽창 되었는지 기준을 따질 때에도 고려해야 합니다.

concurrently 옵션으로 인덱스 rebuild 실패, invalid 상태에서 재작성

concurrently 옵션을 사용해서 인덱스 rebuild를 하는 도중 실패하게 되면, rebuild 과정 중 새로 생성했던 인덱스가 invalid가 될 수 있습니다.

이렇게 rebuild가 필요한 인덱스들이 다수 존재할 수 있는데, 이들의 목록을 보기 위해서는 pg_index 시스템 카탈로그의 indislive, indisvalid를 확인합니다. 

indislive, indisvalid는 각각 boolean 값으로, 정상일 경우 ‘t’, 비정상일 경우 ‘f’를 표기합니다.

tester=# select s.indexrelname, i.indisready, i.indislive, i.indisvalid
from pg_stat_all_indexes s, pg_index i
where i.indisready = 'f' or i.indislive = 'f' or i.indisvalid = 'f';

재현 시나리오

1. 인덱스가 손상된 현상을 재현
2. 해당 인덱스가 손상된 것을 확인
3. 리인덱스를 통한 정상화

인덱스 손상 확인 후 리인덱스 수행

1. 테이블과 인덱스를 생성하고, 강제로 손상 시킵니다.
   먼저 테이블과 인덱스를 생성합니다.

-- 테이블 스페이스를 따로 생성합니다.
-- /var/lib/pgsql/tablespace/test_tbs는 예시를 위한 임의의 경로 입니다.
tester=# create tablespace test_tbs location'/var/lib/pgsql/tablespace/test_tbs';

-- 500만건의 데이터를 랜덤으로 생성합니다. 
tester=# CREATE TABLE concur_test tablespace test_tbs AS 
    SELECT
        gs as idx,
        md5(random()::text) AS random_string
    FROM
        generate_series(1, 5000000) AS gs;

tester=# CREATE INDEX concur_test_idx on concur_test(idx) tablespace test_tbs;

-- 생성한 인덱스의 oid를 확인합니다.
tester=# select oid, relname from pg_class where relname = 'concur_test_idx';

-- 테이블 스페이스 이하에 있는 경로인 PG_15_202209061/16477는 각 환경마다 다르니 알맞은 경로에 맞게 들어갑니다. 
$ cd /var/lib/pgsql/tablespace/test_tbs/PG_15_202209061/16477

$ ls -arlt 38261

-- vi 편집기를 이용해 파일 내용을 조작합니다.
$ vi 38261

2. 인덱스의 손상을 확인합니다.

$ pg_amcheck -d tester --heapallindexed --rootdescend

-- 활용할 수 있는 시스템 카탈로그는 여러가지지만, pg_stat_all_indexes를 이용한 예시입니다.
-- 실제 업무에 적용할 때는 환경에 맞게 새로 작성해서 사용하는 것을 권고합니다.
tester=# CREATE OR REPLACE FUNCTION check_corrupted_indexes()
RETURNS TABLE (
    index_name TEXT,
    table_name TEXT
) AS $$
DECLARE
    index_record RECORD;
BEGIN
    FOR index_record IN
        SELECT indexrelid::regclass AS indexname, relid::regclass::text AS tablename
        FROM pg_stat_all_indexes
    LOOP
        BEGIN
            PERFORM bt_index_parent_check(index_record.indexname);
        EXCEPTION
            WHEN others THEN
                -- 손상된 인덱스를 반환합니다.
                index_name := index_record.indexname::text;
                table_name := index_record.tablename;
                RETURN NEXT;
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

tester=# select * from check_corrupted_indexes();

3. reindex 후 정상화된 것을 확인합니다.

tester=# reindex index concur_test_idx;

tester=# select * from check_corrupted_indexes();

$ pg_amcheck -d tester --heapallindexed --rootdescend

인덱스 비어있는 공간의 증가 확인 후 리인덱스 수행

1. 테이블을 추가로 생성하고, update 전 현황을 확인합니다.

tester=# CREATE TABLE concur_test2 tablespace test_tbs AS 
    SELECT
        gs as idx,
        md5(random()::text) AS random_string
    FROM
        generate_series(1, 5000000) AS gs
;

tester=# create index concur_test2_idx on concur_test2(random_string) tablespace test_tbs;

tester=# select pg_relation_size('concur_test2') as table_size,
pg_relation_size('concur_test2_idx') as index_size,
100 - (pgstatindex('concur_test2_idx')).avg_leaf_density as bloat_ratio;

2. 테이블에 대해 update/delete를 수행합니다.

tester=# update concur_test2 set random_string = 'abcdefghijklmnopqrstuvwxyz1234567890123456789012345678901234567890'
where idx between 1 and 2500000;

tester=# delete from concur_test2 where idx between 1 and 2500000;

3. update 후 analyze로 통계정보를 갱신하고 update/delete 전 쿼리를 통해 인덱스 팽창을 확인합니다.

tester=# analyze concur_test2;
tester=# select pg_relation_size('concur_test2') as table_size,
pg_relation_size('concur_test2_idx') as index_size,
100 - (pgstatindex('concur_test2_idx')).avg_leaf_density as bloat_ratio;

4. reindex 후 정상적으로 조정됨을 확인합니다.

tester=# reindex index concur_test2_idx;

tester=# select pg_relation_size('concur_test2') as table_size,
pg_relation_size('concur_test2_idx') as index_size,
100 - (pgstatindex('concur_test2_idx')).avg_leaf_density as bloat_ratio;

인덱스의 fillfactor 조정 후 리인덱스 수행

tester=# alter index concur_test2_idx set (fillfactor=90);
tester=# reindex index concur_test2_idx;

invalid index 확인 후 리인덱스 수행

tester=# \d concur_test2;
tester=# reindex index concurrently concur_test2_idx;
-- 실행 도중 Ctrl + C 입력하여 중지

참고

ccnew 라는 접미사가 붙은 인덱스는 reindex 과정 중 생성되는 임시 인덱스 입니다.

tester=# select s.indexrelname, i.indisready, i.indislive, i.indisvalid
from pg_stat_all_indexes s, pg_index i
where s.indexrelid = i.indexrelid and
(i.indisready = 'f' or i.indislive = 'f' or i.indisvalid = 'f')
;

참고

공식 홈페이지에 따르면, ccnew 인덱스가 invalid될 경우 이를 삭제하고 다시 reindex를 진행하라고 명시하지만, 본 문서의 경우 invalid 인덱스에 대해서 reindex 후 정상이 되는 과정을 보는 예시이므로 바로 reindex를 수행하고 결과를 확인합니다. 

tester=# reindex index concurrently concur_test2_idx;

참고

reindex 대상 판단 기준 4가지를 한 번에 확인할 수 있는 기능은 postgresql에 없으며, 
이들을 합쳐서 스크립트 또는 쿼리를 작성해서 확인할 수 있습니다.