대용량 인덱스를 사용하여 autovacuum 관리
작업 중에 autovacuum은 테이블에서 실행되는 동안 여러 vacuum 단계를
이 프로세스에서는 먼저 전체 인덱스 크기를 확인합니다. 그런 다음, 다음 예제에 나온 것처럼 제거할 수 있는 사용하지 않는 인덱스가 있는지 확인합니다.
테이블 및 해당 인덱스의 크기를 확인하려면
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));pg_size_pretty 6404 MB (1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));pg_size_pretty 11 GB (1 row)
이 예제에서는 인덱스 크기가 테이블보다 큽니다. 이러한 차이로 인해 인덱스가 팽창하거나 사용되지 않아 성능 문제가 발생하여 autovacuum 및 삽입 작업에 영향을 미칠 수 있습니다.
사용하지 않는 인덱스를 확인하려면
pg_stat_user_indexesidx_scan 열에 사용되는 빈도를 확인할 수 있습니다. 다음 예제를 보면 사용하지 않은 인덱스는 idx_scan 값이 0입니다.
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------------+-----------------------+----------+--------------+--------------- 16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0 16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0 16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0 16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0 16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0 16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0 16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0 (7 rows)
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;schemaname | relname | indexrelname | idx_scan ------------+------------------+-----------------------+---------- public | pgbench_accounts | index_f | 6 public | pgbench_accounts | index_b | 3 public | pgbench_accounts | pgbench_accounts_pkey | 0 public | pgbench_accounts | index_d | 0 public | pgbench_accounts | index_e | 0 public | pgbench_accounts | index_c | 0 public | pgbench_accounts | index_a | 0 (7 rows)
참고
이러한 통계는 통계가 재설정된 시점부터 증분됩니다. 사업 분기 말에만 사용되거나 특정 보고서에만 사용되는 인덱스가 있는 경우를 가정해 보겠습니다. 통계가 재설정된 이후로 이 인덱스가 사용되지 않았을 수 있습니다. 자세한 내용은 Statistics Functions
데이터베이스의 통계가 마지막으로 재설정된 시간을 확인하려면 pg_stat_database
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';datname | stats_reset ----------+------------------------------- postgres | 2022-11-17 08:58:11.427224+00 (1 row)
테이블에 최대한 신속하게 Vacuum을 실행하는 방법
RDS for PostgreSQL 12 이상
대형 테이블에 인덱스가 너무 많으면 DB 인스턴스가 트랜잭션 ID 랩어라운드(XID)에 가까워질 수 있는데, 이때 XID 카운터가 0으로 래핑됩니다. 이 옵션을 선택하지 않으면 데이터가 손실될 수 있습니다. 그러나 인덱스를 정리하지 않고도 테이블에 신속하게 vacuum을 실행할 수 있습니다. RDS for PostgreSQL 12에서는 INDEX_CLEANUP
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;INFO: vacuuming "public.pgbench_accounts" INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
autovacuum 세션이 이미 실행 중인 경우, 해당 세션을 종료하여 수동 VACUUM을 시작해야 합니다. 수동 vacuum freeze 수행에 대한 자세한 내용은 수동 vacuum freeze 수행 섹션을 참조하세요.
참고
주기적인 인덱스 정리를 건너뛰면 인덱스 팽창이 발생하여 스캔 성능이 다운그레이드될 수 있습니다. 인덱스는 데드 행을 유지하고 테이블은 데드 라인 포인터를 유지합니다. 따라서 pg_stat_all_tables.n_dead_tup는 인덱스 정리가 포함된 수동 VACUUM 또는 autovacuum이 실행될 때까지 증가합니다. 가장 좋은 방법은 이 프로시저만 사용하여 트랜잭션 ID 랩어라운드를 방지하는 것입니다.
RDS for PostgreSQL 11 이상
그러나 RDS for PostgreSQL 11 이하 버전에서 vacuum을 더 신속하게 완료할 수 있는 유일한 방법은 테이블의 인덱스 수를 줄이는 것입니다. 인덱스를 삭제하면 쿼리 계획에 영향을 미칠 수 있습니다. 사용하지 않는 인덱스를 먼저 삭제한 다음, XID 랩어라운드가 매우 가까워졌을 때 인덱스를 삭제하는 것이 좋습니다. vacuum 프로세스가 완료되면 이러한 인덱스를 다시 생성할 수 있습니다.
