테이블 및 인덱스 팽창 진단

PostgreSQL 다중 버전 동시성 제어(MVCC)를 사용하여 데이터 무결성을 유지할 수 있습니다. PostgreSQL MVCC는 트랜잭션이 커밋되거나 롤백될 때까지 업데이트 또는 삭제된 행(튜플이라고도 함)의 내부 복사본을 저장하는 방식으로 작동합니다. 이 저장된 내부 복사본은 사용자에게 표시되지 않습니다. 그러나 VACUUM 또는 AUTOVACUUM 유틸리티를 사용해 이러한 보이지 않는 복사본을 주기적으로 정리하지 않으면 테이블 팽창이 발생할 수 있습니다. 테이블 팽창을 확인하지 않으면 스토리지 비용이 증가하고 처리 속도가 저하될 수 있습니다.

대부분의 경우 Aurora의 VACUUUM 또는 AUTOVACUUM에 대한 기본 설정만으로도 원치 않는 테이블 팽창을 충분히 처리할 수 있습니다. 하지만 애플리케이션에서 다음과 같은 상황이 발생할 경우에는 팽창 여부를 확인하는 것이 좋습니다.

시작하려면 죽은 튜플이 얼마나 많은 공간을 사용하고 있는지, 그리고 테이블 및 인덱스 팽창을 정리하면 어느 정도의 공간을 복구할 수 있는지에 대한 가장 정확한 정보를 수집하세요. 이를 수행하려면 pgstattuple 확장을 사용하여 Aurora 클러스터에서 통계를 수집합니다. 자세한 내용은 pgstattuple을 참조하세요. pgstattuple 확장을 사용할 수 있는 권한은 pg_stat_scan_tables 역할 및 데이터베이스 수퍼유저로 제한됩니다.

Aurora에서 pgstattuple 확장을 생성하려면 클라이언트 세션을 클러스터(예: psql 또는 pgAdmin)에 연결하고 다음 명령을 사용하세요.

CREATE EXTENSION pgstattuple;

프로파일링하려는 각 데이터베이스에서 확장을 생성합니다. 확장을 생성한 후 명령줄 인터페이스(CLI)를 사용하여 회수할 수 있는 사용 불가 공간이 얼마인지 측정하세요. 통계를 수집하기 전에 AUTOVACUUM을 0으로 설정하여 클러스터 파라미터 그룹을 수정합니다. 0으로 설정하면 Aurora가 애플리케이션에서 남긴 죽은 튜플을 자동으로 정리할 수 없으므로, 결과의 정확도에 영향을 미칠 수 있습니다. 다음 명령을 입력하여 간단한 테이블을 생성합니다.

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

다음 예에서는 DB 클러스터에 대해 AUTOVACUUM이 켜진 상태에서 쿼리를 실행합니다. dead_tuple_count는 0이며, 이는 AUTOVACUUM이 사용되지 않는 데이터나 튜플을 PostgreSQL 데이터베이스에서 삭제했음을 나타냅니다.

pgstattuple을 사용하여 테이블에 대한 정보를 수집하려면 쿼리에 테이블 이름 또는 객체 식별자(OID)를 지정합니다.

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

다음 쿼리에서는 AUTOVACUUM을 끄고 테이블에서 25,000개의 행을 삭제하는 명령을 입력합니다. 그 결과, dead_tuple_count가 25000으로 증가합니다.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

죽은 튜플을 회수하려면 VACUUM 프로세스를 시작합니다.

애플리케이션 중단 없이 팽창 관찰

Aurora 클러스터의 설정은 대부분의 워크로드에 대한 모범 사례를 제공하도록 최적화되어 있습니다. 하지만 사용자의 애플리케이션 및 사용 패턴에 더 잘 맞도록 클러스터를 최적화할 수도 있습니다. 이 경우 사용량이 많은 애플리케이션을 중단하지 않고 pgstattuple 확장을 사용하면 됩니다. 이렇게 하려면 다음 단계를 수행합니다.

애플리케이션을 실행하고 결과를 확인한 후, 복원된 사본에서 pg_repack 또는 VACUUM FULL을 사용하여 차이점을 비교해 봅니다. dead_tuple_count, dead_tuple_len 또는 dead_tuple_percent가 크게 감소한 경우 프로덕션 클러스터의 정리 일정을 조정하여 팽창을 최소화하세요.

임시 테이블의 팽창 방지

애플리케이션에서 임시 테이블을 생성할 경우, 이러한 임시 테이블이 더 이상 필요하지 않을 때는 애플리케이션에서 해당 임시 테이블을 제거해야 합니다. 자동 정리 프로세스에서는 임시 테이블을 찾지 않습니다. 임시 테이블을 확인하지 않은 상태로 두면 데이터베이스 팽창이 빠르게 발생할 수 있습니다. 또한 팽창 현상은 시스템 테이블로 확장될 수 있습니다. 시스템 테이블은 pg_attribute 및 pg_depend와 같은 PostgreSQL 객체 및 속성을 추적하는 내부 테이블입니다.

임시 테이블이 더 이상 필요하지 않은 경우 TRUNCATE 문을 사용하여 테이블을 비우고 공간을 확보할 수 있습니다. 그런 다음, pg_attribute 및 pg_depend 테이블을 수동으로 정리합니다. 이러한 테이블을 정리하면 임시 테이블을 생성하고 잘라내거나 삭제해도 계속해서 튜플이 추가되거나 시스템 팽창이 발생하지 않습니다.

콘텐츠가 커밋될 때 새 행을 삭제하는 다음 구문을 포함하면 임시 테이블을 생성하는 동안 이러한 문제를 방지할 수 있습니다.

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

이 ON COMMIT DELETE ROWS 절은 트랜잭션이 커밋될 때 임시 테이블을 잘라냅니다.

인덱스의 팽창 방지

테이블에서 인덱싱된 필드를 변경하면 인덱스 업데이트로 인해 해당 인덱스에 하나 이상의 죽은 튜플이 생성됩니다. 기본적으로 autovacuum 프로세스는 인덱스의 팽창을 정리하지만, 이러한 정리에는 상당히 많은 시간과 리소스가 사용됩니다. 테이블을 생성할 때 인덱스 정리 기본 설정을 지정하려면 vacuum_index_cleanup 절을 포함하세요. 기본적으로 테이블 생성 시 해당 절은 AUTO로 설정됩니다. 즉, 테이블을 정리할 때 서버가 인덱스 정리가 필요한지 여부를 결정합니다. 이 절을 ON으로 설정하면 특정 테이블에 대한 인덱스 정리를 켤 수 있고, OFF로 설정하면 해당 테이블에 대한 인덱스 정리를 끌 수 있습니다. 인덱스 정리를 끄면 시간이 절약될 수 있지만 인덱스가 팽창할 수 있다는 점을 유의하세요.

명령줄에서 테이블에 VACUUM을 실행할 때 인덱스 정리를 수동으로 제어할 수 있습니다. 테이블을 정리하고 인덱스에서 죽은 튜플을 제거하려면 값이 ON이고 테이블 이름이 있는 INDEX_CLEANUP 절을 포함하세요.

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

인덱스를 지우지 않고 테이블을 정리하려면 값을 OFF로 지정합니다.

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM