기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다. # 인덱스 다시 빌드 PostgreSQL [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) 명령은 인덱스 테이블에 저장된 데이터를 사용하고 인덱스의 이전 복사본을 대체하여 인덱스를 다시 빌드합니다. 다음과 같은 시나리오에서는 `REINDEX`를 사용하는 것이 좋습니다. + 인덱스가 손상되어 더 이상 유효한 데이터가 포함되지 않는 경우. 소프트웨어 또는 하드웨어 장애로 인해 이와 같은 경우가 발생할 수 있습니다. + 이전에 인덱스를 사용한 쿼리는 해당 인덱스 사용을 중지하는 경우. + 많은 빈 페이지 또는 거의 빈 페이지로 인덱스가 팽창되는 경우. 팽창률(`bloat_pct`)이 20보다 큰 경우 `REINDEX`를 실행해야 합니다. 다음 쿼리는 `bloat_pct`를 찾는 데 도움이 됩니다. ``` SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size, bs*(relpages-est_pages)::bigint AS extra_size, 100 * (relpages-est_pages)::float / relpages AS extra_pct, fillfactor, CASE WHEN relpages > est_pages_ff THEN bs*(relpages-est_pages_ff) ELSE 0 END AS bloat_size, 100 * (relpages-est_pages_ff)::float / relpages AS bloat_pct, is_na -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO) FROM ( SELECT coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth) ) AS est_pages, coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0 ) AS est_pages_ff, bs, nspname, tblname, idxname, relpages, fillfactor, is_na -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO) FROM ( SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, idxoid, fillfactor, ( index_tuple_hdr_bm + maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign ELSE index_tuple_hdr_bm%maxalign END + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN WHEN nulldatawidth = 0 THEN 0 WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO) FROM ( SELECT n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs, CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS maxalign, /* per page header, fixed size: 20 for 7.X, 24 for others */ 24 AS pagehdr, /* per page btree opaque data */ 16 AS pageopqdata, /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */ CASE WHEN max(coalesce(s.null_frac,0)) = 0 THEN 8 -- IndexTupleData size ELSE 8 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8) END AS index_tuple_hdr_bm, /* data len: we remove null values save space using it fractionnal part from stats */ sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth, max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na FROM ( SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.attpos, ic.indkey, ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor, coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid, CASE WHEN a1.attnum IS NULL THEN ic.idxname ELSE ct.relname END AS attrelname FROM ( SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey, pg_catalog.generate_series(1,indnatts) AS attpos FROM ( SELECT ci.relname AS idxname, ci.reltuples, ci.relpages, i.indrelid AS tbloid, i.indexrelid AS idxoid, coalesce(substring( array_to_string(ci.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor, i.indnatts, pg_catalog.string_to_array(pg_catalog.textin( pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree') AND ci.relpages > 0 ) AS idx_data ) AS ic JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid LEFT JOIN pg_catalog.pg_attribute a1 ON ic.indkey[ic.attpos] <> 0 AND a1.attrelid = ic.tbloid AND a1.attnum = ic.indkey[ic.attpos] LEFT JOIN pg_catalog.pg_attribute a2 ON ic.indkey[ic.attpos] = 0 AND a2.attrelid = ic.idxoid AND a2.attnum = ic.attpos ) i JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname AND s.tablename = i.attrelname AND s.attname = i.attname GROUP BY 1,2,3,4,5,6,7,8,9,10,11 ) AS rows_data_stats ) AS rows_hdr_pdg_stats ) AS relation_stats ORDER BY nspname, tblname, idxname; ``` 완전히 비어 있는 인덱스 페이지는 재사용을 위해 회수됩니다. 그러나 페이지의 인덱스 키가 삭제되었지만 스페이스가 할당된 상태로 남아 있는 경우 주기적으로 재인덱싱하는 것이 좋습니다. 인덱스를 다시 생성하면 쿼리 성능이 향상됩니다. 다음 표에 설명된 대로 세 가지 방법으로 인덱스를 다시 생성할 수 있습니다. | | | | | --- |--- |--- | | **메서드** | **설명** | **제한 사항** | | `CONCURRENTLY` 옵션과 함께 `CREATE INDEX` 및 `DROP INDEX` | 새 인덱스를 빌드하고 이전 인덱스를 제거합니다. 옵티마이저는 이전 인덱스 대신 새로 생성된 인덱스를 사용하여 계획을 생성합니다. 피크가 낮은 기간에 이전 인덱스를 삭제할 수 있습니다. | 인덱스 생성은 모든 수신 변경 사항을 추적해야 하므로 `CONCURRENTLY` 옵션을 사용할 때 더 많은 시간이 걸립니다. 변경 사항이 동결되면 프로세스가 완료로 표시됩니다. | | `CONCURRENTLY` 옵션과 함께 `REINDEX` | 다시 빌드 프로세스 중에 쓰기 작업을 잠급니다. PostgreSQL 버전 12** **이상 버전은 이러한 잠금을 방지하는 `CONCURRENTLY` 옵션을 제공합니다. | `CONCURRENTLY`를 사용하면 인덱스를 다시 빌드하는 데 시간이 더 오래 걸립니다. | | `pg_repack` 확장 | 테이블에서 팽창을 정리하고 인덱스를 다시 빌드합니다. | 이러한 확장은 EC2 인스턴스 또는 데이터베이스에 연결된 로컬 컴퓨터에서 실행해야 합니다. | ## 새 인덱스 생성 `DROP INDEX` 및 `CREATE INDEX` 명령을 함께 사용하면 인덱스가 다시 빌드됩니다. ``` DROP INDEX CREATE INDEX ON TABLE ([,]) ``` 이 접근 방식의 단점은 테이블에서 배타적 잠금이 발생하는 것으로, 이 활동 중 성능에 영향을 미칩니다. `DROP INDEX` 명령으로 테이블에서 읽기 및 쓰기 작업을 모두 차단하는 배타적 잠금을 획득합니다. `CREATE INDEX` 명령은 테이블에서 쓰기 작업을 차단합니다. 읽기 작업은 허용되지만 인덱스 생성 중에는 비용이 많이 듭니다. ## 인덱스 다시 빌드 `REINDEX` 명령은 일관된 데이터베이스 성능을 유지하는 데 도움이 됩니다. 테이블에서 많은 DML 작업을 수행하면 테이블과 인덱스가 모두 팽창합니다. 인덱스는 쿼리 성능을 개선하기 위해 테이블 조회 속도를 높이는 데 사용됩니다. 인덱스 팽창은 조회 및 쿼리 성능에 영향을 미칩니다. 따라서 쿼리 성능의 일관성을 유지하기 위해 DML 작업이 많은 테이블에서 재인덱싱을 수행하는 것이 좋습니다. 이 `REINDEX` 명령은 기본 테이블에서 쓰기 작업을 잠가 인덱스를 처음부터 다시 빌드하지만 테이블에서 읽기 작업을 허용합니다. 그러나 인덱스에서 읽기 작업이 차단됩니다. 해당 인덱스를 사용하는 쿼리는 차단되지만 다른 쿼리는 차단되지 않습니다. PostgreSQL 버전 12에는 인덱스를 처음부터 다시 빌드하지만 테이블 또는 인덱스를 사용하는 쿼리에서 쓰기 또는 읽기 작업을 잠그지 않는 새로운 선택적 파라미터, `CONCURRENTLY`가 도입되었습니다. 그러나 이 옵션을 사용하면 프로세스를 완료하는 데 시간이 더 오래 걸립니다. ## 예시 **인덱스 생성 및 삭제** `CONCURRENTLY` 옵션을 사용하여 새 인덱스를 생성합니다. ``` create index CONCURRENTLY on table(columns) ; ``` `CONCURRENTLY` 옵션을 사용하여 이전 인덱스를 삭제합니다. ``` drop index CONCURRENTLY ; ``` **인덱스 다시 빌드** 단일 인덱스를 다시 빌드하는 방법: ``` reindex index ; ``` 테이블에서 모든 인덱스를 다시 빌드하는 방법: ``` reindex table ; ``` 스키마에서 모든 인덱스를 다시 빌드하는 방법: ``` reindex schema ; ``` **동시에 인덱스 다시 빌드** 단일 인덱스를 다시 빌드하는 방법: ``` reindex index CONCURRENTLY ; ``` 테이블에서 모든 인덱스를 다시 빌드하는 방법: ``` reindex table CONCURRENTLY ; ``` 스키마에서 모든 인덱스를 다시 빌드하는 방법: ``` reindex schema CONCURRENTLY ; ``` **인덱스만 다시 빌드 또는 재배치** 단일 인덱스를 다시 빌드하는 방법: ``` pg_repack -h -d -i -k ``` 모든 인덱스를 다시 빌드하는 방법: ``` pg_repack -h -d -x -t -k ```