pgstattuple extension을 사용한 PostgreSQL 성능 향상

이 글은 AWS Database Blog에 게시된 Improve PostgreSQL performance using the pgstattuple extension by Vivek Singh, Kiran Singh, and Sagar Patel 을 한국어 번역 및 편집하였습니다.

기업이 계속해서 방대한 양의 데이터를 생성하고 저장함에 따라 효율적이고 신뢰할 수 있는 데이터베이스 관리 시스템의 필요성이 점점 더 중요해지고 있습니다. 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)인 PostgreSQL은 복잡한 데이터 요구 사항을 처리하기 위한 강력한 솔루션으로 자리 잡았습니다. PostgreSQL의 주요 강점 중 하나는 확장성에 있습니다. 개발자는 확장 및 플러그인의 풍부한 에코시스템을 통해 특정 요구 사항을 충족하도록 데이터베이스의 기능을 개선할 수 있습니다. 이러한 확장은 공간 데이터 지원 및 전체 텍스트 검색 기능부터 고급 데이터 유형 및 성능 최적화 도구에 이르기까지 다양합니다. 이렇듯 PostgreSQL은 광범위한 기능과 성능을 제공하지만 종종 간과되는 확장 중 하나는 pgstattuple입니다. 이는 PostgreSQL 데이터베이스의 내부 작업을 파악하는 데 상당한 가치를 제공할 수 있는 도구입니다.

이 게시물에서는 pgstattuple이 제공하는 통찰력, Amazon Aurora PostgreSQL-Compatible Edition 및 Amazon Relational Database Service (Amazon RDS) for PostgreSQL에서 문제를 진단하는 데 사용하는 방법, pgstattuple의 기능을 활용하는 모범 사례 등을 심층적으로 살펴보겠습니다.

pgstattuple 개요

pgstattuple 확장은 PostgreSQL 테이블 및 인덱스 내의 튜플 수준에서 세부 통계를 쿼리하는 함수 세트를 제공합니다. 이를 통해 PostgreSQL 표준 통계 뷰에서는 제공하지 않는 물리적 스토리지 계층에 대한 가시성이 제공됩니다.

pgstattuple이 제공하는 일부 테이블 및 인덱스 수준 메트릭은 다음과 같습니다.

tuple_count — live tuples의 개수
dead_tuple_count — 아직 정리되지 않은 dead tuples의 수
tuple_len — live tuples의 평균 길이(bytes)
free_space — 사용 가능한 총 여유 공간(bytes)
free_percent — 여유 공간의 백분율입니다. 값이 클수록 더 큰 팽창을 나타냅니다.
dead_tuple_len — dead tuples의 총 길이(bytes)
dead_tuple_percent — dead tuples이 차지하는 공간의 백분율

이러한 메트릭은 단순한 숫자가 아니라 데이터베이스 상태 및 성능 문제에 대한 조기 경보 시스템입니다. 이러한 통계를 모니터링하면 데이터베이스 성능에 조용히 영향을 미칠 수 있는 스토리지 문제를 사전에 식별할 수 있습니다. 과도한 테이블 팽창으로 인해 디스크 공간이 많이 소모되든, 인덱스 단편화로 인해 쿼리 속도가 느려지든, pgstattuple은 심각한 문제가 되기 전에 이러한 문제를 발견할 수 있도록 도와줍니다.

Aurora and Amazon RDS에서 pgstattuple 사용

Aurora와 Amazon RDS 모두 pgstattuple 확장 사용을 지원합니다. 이를 활성화하려면 먼저 CREATE EXTENSION pgstattuple;명령을 사용하여 데이터베이스에 확장을 생성해야 합니다. 활성화한 후에는 pgstattuple(relation)과 같은 함수를 사용하여 페이지 수, live tuple, dead tuple 등을 포함한 테이블에서 사용하는 물리적 스토리지에 대한 세부 정보를 얻을 수 있습니다. pgstattuple_approx(relation) 함수는 이러한 메트릭을 더 빠르게 추정할 수 있습니다. pgstatindex(index)를 사용하여 인덱스 통계를 얻을 수도 있습니다. 이 하위 레벨의 데이터를 분석하면 VACUUM이 필요한 팽창된(Bloated) 테이블을 식별하고, 재 작성하면 이점을 얻을 수 있는 dead tuple 비율이 높은 테이블을 찾고, 데이터베이스의 물리적 스토리지 사용률을 최적화하는 데 도움이 될 수 있습니다.

pgstattuple의 출력은 다음 섹션에서 설명하는 것처럼 모니터링, 유지 관리 및 성능 튜닝에 대한 실행 가능한 통찰력을 제공합니다.

테이블 팽창 감지 및 관리

PostgreSQL 테이블에서 pgstattuple의 가장 유용한 활용 중 하나는 팽창 현상을 식별하고 관리하는 것입니다. UPDATE 및 DELETE 작업을 수행하면 사용하지 않은 공간이 자동으로 회수되지 않고 남게 되면 팽창이 발생합니다. PostgreSQL은 MVCC(다중 버전 동시성 제어) 모델을 통해 데이터 일관성을 유지합니다. 이 모델에서는 기본 데이터의 현재 상태에 관계없이 각 SQL 문에 이전 시간의 데이터 스냅샷이 표시됩니다. 이렇게 하면 동일한 행을 업데이트하는 동시 트랜잭션으로 인해 명령문이 일관되지 않은 데이터를 보는 것을 방지하여 데이터베이스 세션당 트랜잭션을 격리할 수 있습니다. 전통적인 잠금 방법과 달리 MVCC는 잠금 경합을 최소화하여 합리적인 다중 사용자 성능을 제공합니다.

PostgreSQL과 같은 MVCC 시스템에서 행을 삭제해도 데이터 페이지에서 행이 즉시 제거되지 않습니다. 대신 현재 트랜잭션에서는 삭제되거나 만료된 것으로 표시되지만 이전 스냅샷을 보는 트랜잭션에서는 계속 표시되므로 충돌을 방지할 수 있습니다. 트랜잭션이 완료되면 이러한 죽거(dead)나 만료된 튜플은 결국 VACUUM 작업을 통해 정리되고 공간이 다시 확보될 것으로 예상됩니다. PostgreSQL에서 UPDATE 작업은 DELETE와 INSERT를 함께 사용하는 것과 같습니다. 행이 업데이트되면 PostgreSQL은 이전 버전을 만료된 것으로 표시 (예: DELETE) 하지만 이전 트랜잭션 스냅샷에서는 계속 볼 수 있습니다. 그런 다음 업데이트된 값 (예: INSERT)이 포함된 새 버전의 행을 삽입합니다. 시간이 지나면서 만료된 행 버전은 VACUUM 프로세스에서 제거되어 공간을 확보할 때까지 누적됩니다. 이 접근 방식은 PostgreSQL의 MVCC 모델을 가능하게 하며, 업데이트 중에 명시적인 잠금 없이 스냅샷 격리를 제공합니다.

PostgreSQL의 autovacuum은 데드 튜플이 차지하는 스토리지를 회수하고 쿼리 플래너가 사용하는 통계를 업데이트하는 자동화된 유지 관리 프로세스입니다. autovacuum 프로세스는 maximum age(트랜잭션 기준)가 autovacuum_freeze_max_age를 초과하거나 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of tuples 로 계산되는 임계값에 도달할 때 실행됩니다. 이 공식에서 autovacuum_vacuum_threshold 는 정리를 시작하는 데 필요한 업데이트되거나 삭제된 튜플의 최소 수를 나타내며, autovacuum_vacuum_scale_factor는 유지 관리 발생 시기를 결정하기 위해 임계값 계산에 추가되는 테이블 크기 비율입니다. 특정 이유로 autovacuum이 dead tuple을 정리하지 못하는 경우, 급격히 팽창한 테이블을 수동으로 처리해야 할 수도 있습니다.

Dead tuple은 데이터 페이지의 live tuple과 함께 저장됩니다. 예를 들어 autovacuum으로 인해 dead tuple을 정리한 후 페이지의 여유 공간이 늘어날 수도 있습니다. 쿼리 실행 중에 PostgreSQL은 dead tuple로 가득 찬 페이지를 더 많이 스캔하므로 I/O가 증가하고 쿼리가 느려집니다. 급격히 팽창한 테이블들은 데이터베이스 워크로드가 불필요한 읽기 I/O를 소비하게 만들어, 애플리케이션 성능에 영향을 미칩니다. autovacuum이 실패할 경우 팽창한 데이터를 직접 정리해야 할 수 있습니다.

pgstattuple을 사용한 테이블 팽창 분석을 자세히 살펴보기 전에 모든 내용이 제대로 작동할 수 있도록 설정했는지 확인해 보겠습니다. 데이터베이스에 연결하도록 올바르게 구성되어 있고 psql이 설치된 클라이언트 머신과 함께, Amazon RDS 또는 Aurora PostgreSQL 인스턴스에 대한 접근 권한이 필요할 것입니다. PostgreSQL 환경에서 테이블을 생성하고 확장을 설치하는 데 필요한 권한이 있는지 확인하십시오. 이 데모에서는 pgbench_accounts 테이블을 사용하겠습니다. 이 테이블이 아직 없는 경우 pgbench 유틸리티를 사용하여 쉽게 만들 수 있습니다. pgbench -i -s 10 명령어를 실행하여 스케일 팩터 10으로 pgbench 스키마를 초기화하면, pgbench_accounts 테이블과 기타 필요한 테이블들이 생성됩니다. 이를 통해 분석에 사용할 샘플 데이터가 제공됩니다. 또한 데이터베이스 인스턴스에 pgstattuple 확장이 설치되어 있어야 합니다. 아직 설치하지 않았다면 충분한 권한을 가진 사용자로 CREATE EXTENSION pgstattuple; 을 실행하여 설치할 수 있습니다. 이러한 사전 요구 사항을 갖추면 통제된 환경에서 실제 데이터를 사용하여 테이블 팽창 분석을 탐색할 준비가 된 것입니다.

pgstattuple은 포괄적인 테이블 팽창 분석을 제공하지만 리소스를 많이 사용할 수 있습니다.먼저 여기에 설명된 간단한 팽창 추정 쿼리를 사용하는 것이 좋습니다. 더 자세한 분석이 필요한 경우 pgstattuple을 사용하는 방법은 다음과 같습니다. 다음의 예제는 pgstattuple을 사용하여 테이블의 팽창 정보를 분석하는 방법을 보여줍니다.

10,000개의 레코드가 포함된 pgbench_accounts_test 테이블을 생성합니다.

bench=> create table pgbench_accounts_test as (select * from pgbench_accounts limit 10000);
SELECT 10000

이 예제에서 pgstattuple을 쿼리하면 dead tuple의 수가 0으로 반환되고 테이블 크기는 1672kB가 반환됩니다.

SELECT * from pgstattuple('pgbench_accounts_test');
-[ RECORD 1 ]------+--------
table_len		   | 1343488
tuple_count 	   | 10000
tuple_len 		   | 1210000
tuple_percent 	   | 90.06
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space 		   | 18896
free_percent 	   | 1.41

SELECT pg_size_pretty(pg_total_relation_size('public.pgbench_accounts_test'));
pg_size_pretty

1672 kB

pgstattuple의 사용법을 설명하기 위해 autovacuum 기능을 끄고 (프로덕션 환경에서는 권장되지 않음) 2,500개의 레코드를 업데이트합니다.

SHOW autovacuum;
autovacuum
off

UPDATE pgbench_accounts_test SET bid=2 WHERE aid <2501;
UPDATE 2500

이제 이 테이블의 pgstattuple 데이터는 2,500개의 이전 버전 튜플이 dead tuple로 이동되었음을 보여줍니다.

SELECT * from pgstattuple('pgbench_accounts_test');
-[ RECORD 1 ]------+--------
table_len 		   | 1679360
tuple_count 	   | 10000
tuple_len 		   | 1210000
tuple_percent 	   | 72.05
dead_tuple_count   | 2500
dead_tuple_len 	   | 302500
dead_tuple_percent | 18.01
free_space 		   | 23620
free_percent 	   | 1.41
bench=> SELECT pg_size_pretty(pg_total_relation_size('public.pgbench_accounts_test'));
pg_size_pretty

1672 kB
(1 row)

PostgreSQL의 bloat_centech는 전체 크기 대비 테이블 또는 인덱스에서 회수할 수 있는 공간의 비율을 나타냅니다. pgstattuple의 데이터를 사용하여 다음과 같이 계산할 수 있습니다.

bloat_ percentage = (table_len - tuple_len/(table_len) * 100

bloat_percentage 값이 30~ 40% 를 초과하면 주의가 필요한 문제가 있는 팽창을 나타내는 경우가 많습니다. 팽창을 정리하기 위해서는 VACUUM 명령을 사용하십시오.

VACUUM pgbench_accounts_test;

VACUUM 작업 후 pgstattuple 데이터를 살펴보겠습니다.

SELECT * from pgstattuple('pgbench_accounts_test'); 
-[ RECORD 1 ]------+--------
table_len 		   | 1679360
tuple_count 	   | 10000
tuple_len 		   | 1210000
tuple_percent 	   | 72.05
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space 		   | 353220
free_percent 	   | 21.03

VACUUM 작업은 dead_tuple_count를 0으로 재설정합니다. 테이블에 아직 연결되어 있는 사용 가능한 공간은 동일한 테이블에서 삽입 또는 업데이트 작업에 사용할 수 있습니다. 이는 VACUUM 작업 이후에도 table_len이 동일하게 유지 되도록 만듭니다. 팽창이 차지하는 디스크 스토리지를 회수하기 위한 두 가지 옵션이 있습니다.

VACUM FULL — VACUUM FULL은 더 많은 디스크 공간을 회수할 수 있지만 실행 속도는 훨씬 느립니다. 작업 중인 테이블에 대한 ACCESS EXCLUSIVE 잠금이 필요하므로 테이블의 다른 용도와 병행하여 실행할 수 없습니다. VACUUM FULL 작업은 일반적으로 프로덕션 환경에서는 권장되지 않지만, 가동 중지 시간을 계획하고 승인하는 정기 유지 관리 기간에는 허용될 수 있습니다.
pg_repack — pg_repack은 온라인 가용성을 유지하면서 테이블 및 인덱스 팽창을 효율적으로 제거하는 PostgreSQL 확장입니다. CLUSTER 및 VACUUM FULL 명령 과 달리 처리 중 배타적 잠금 시간을 최소화하여 클러스터에 필적하는 성능을 제공합니다. pg_repack을 사용하면 애플리케이션 다운타임을 최소화하면서 온라인 테이블 및 인덱스를 재구성할 수 있지만, 그 한계를 고려하는 것이 중요합니다. 이 확장은 여전히 작업 중에 짧은 배타적 잠금이 필요하며 트랜잭션이 매우 빈번한 테이블에서는 완료하는 데 어려울 수 있어 잠재적으로 데이터베이스 성능에 영향을 미칠 수 있습니다. 전체 재압축이 어려운 사용량이 많은 테이블의 경우 인덱스만 재압축하는 대안을 고려해 보십시오. 모범 사례로는 비프로덕션 환경에서의 철저한 테스트, 트래픽이 적은 기간에 일정 수립, 모니터링 및 롤백 계획 수립 등이 있습니다. 이러한 이점에도 불구하고 사용자는 PostgreSQL 환경에서 pg_repack을 구현할 때 잠재적 위험을 인식하고 그에 따라 계획을 세워야 합니다.

VACUUM FULL 작업은 table_len을 줄입니다.

VACUUM FULL pgbench_accounts_test;
VACUUM
SELECT * from pgstattuple('pgbench_accounts_test');
-[ RECORD 1 ]------+--------
table_len 		   | 1343488
tuple_count 	   | 10000
tuple_len 		   | 1210000
tuple_percent 	   | 90.06
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space 		   | 18896
free_percent 	   | 1.41

VACUUM FULL 작업은 디스크 스토리지로 낭비되는 공간을 회수하고 table_len을 줄입니다. 다음 쿼리는 pgstattuple을 사용하여 데이터베이스에서 가장 큰 테이블 10개에 대한 팽창을 식별합니다.

SELECT relname,
(table_len - tuple_len) AS bloat_size,
ROUND(100 * (table_len - tuple_len)::numeric / NULLIF(table_len::numeric, 0), 2) AS bloat_percentage
FROM (
SELECT relname,
(pgstattuple(oid)).table_len,
(pgstattuple(oid)).tuple_len
FROM pg_class
WHERE relname IN (
SELECT relname
FROM pg_statio_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(relid) DESC,
pg_relation_size(relid) DESC
LIMIT 10
)
) AS subquery
ORDER BY bloat_percentage DESC;
relname | bloat_size | bloat_percentage
-----------------------+-------------+------------------
pgbench_accounts_test  | 469360		 | 27.95
pgbench_tellers 	   | 1307008 	 | 26.64
pgbench_history 	   | 2081296 	 | 25.15
pgbench_accounts 	   | 27945461248 | 18.76
cust_accounts 		   | 267097	     | 9.94
seller_accounts 	   | 2661689	 | 9.91
prod_accounts 		   | 2661689	 | 9.91
prod_teller 		   | 2661689	 | 9.91
pgbench_accounts_1 	   | 6647546624  | 9.90
stage_accounts 		   | 27659129 	 | 9.90
(10 rows)

pgstattuple은 전체 테이블 스캔을 수행하므로 CPU 및 I/O와 같은 인스턴스 리소스를 더 많이 사용할 수 있습니다. 이로 인해 큰 테이블의 경우 pgstattuple 작업이 느려집니다. 대안으로, pgstattuple_approx(relation) 함수를 사용하여 이러한 지표들을 더 빠르게 추정할 수 있습니다. 이는 pgstattuple보다 리소스 사용량이 적지만 매우 큰 테이블이나 사용량이 많은 시스템에서는 여전히 부하가 클 수 있습니다. 사용량이 적은 시간에 실행하거나 가능한 경우 복제본에서 실행하는 것을 고려해 보십시오.

수동 vacuum 자동화

팽창을 정기적으로 모니터링하면 성능이 저하되기 전에 유지 관리 요구 사항을 사전에 식별할 수 있습니다. 또한 팽창 지표는 autovacuum 설정을 미세 조정하여 필요한 경우 공간을 더 적극적으로 정리하는 데도 도움이 됩니다. 용량이 큰 상위 10개 테이블을 식별한 후에는 pg_cron 확장을 사용하여 VACUUM 작업을 자동화할 수 있습니다. pg_cron은 데이터베이스 내에서 확장으로 실행되는 PostgreSQL용 크론 기반 작업 스케줄러입니다. 일반 cron과 동일한 구문을 사용하지만 데이터베이스에서 직접 PostgreSQL 명령을 스케줄링할 수 있습니다. 다음 코드는 pg_cron 함수 cron.schedule을 사용하여 매일 23:00 (GMT) 에 특정 테이블에서 VACUUM을 사용하도록 작업을 설정하는 예제입니다.

SELECT cron.schedule('manual vacuum', '0 23 * * *', 'VACUUM pgbench_accounts_test');
 schedule
----------
1
(1 row)

위 예제를 실행한 후 아래 쿼리는 다음과 같이 cron.job_run_details 테이블의 기록을 확인합니다.

SELECT * FROM cron.job_run_details;
jobid  | runid | job_pid | database | username | command                        | status    | return_message | start_time                    | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
 1     | 1     | 3395    | postgres | adminuser| vacuum pgbench_accounts_test | succeeded | VACUUM         | 2024-05-11 21:10:00.050386+00 | 2024-05-11 21:10:00.072028+00
(1 row)

인덱스 팽창 진단 및 해결

테이블과 마찬가지로 PostgreSQL의 인덱스도 공간을 낭비하고 성능에 영향을 미치는 팽창을 경험할 수 있습니다. pgstattuple의 pgstatindex를 사용하여 인덱스 팽창을 감지할 수 있습니다.

다음 쿼리는 인덱스 식별자, 전체 인덱스 크기(bytes), 평균 리프 밀도를 보여 줍니다.

SELECT i.indexrelid::regclass AS index,
       s.index_size,
       s.avg_leaf_density
FROM pg_index AS i
CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE indrelid = 'myschema.mytable'::regclass;

평균 리프 밀도는 인덱스의 리프 페이지에 있는 유용한 데이터의 백분율입니다. 상당히 팽창된 인덱스는 REINDEX 또는 pg_repack을 사용하여 재구축하여 불필요한 공간을 제거하고 최적의 성능을 복원할 수 있습니다. 사용량이 많고 변동이 심한 인덱스에 팽창이 있는지 주기적으로 확인하는 것이 좋습니다.

index 단편화 평가

pgstattuple의 또 다른 유용한 용도는 인덱스 단편화 문제를 식별하는 것입니다. 단편화는 삭제, 업데이트 및 페이지 분할로 인해 인덱스 페이지가 흩어질 때 발생합니다. 인덱스가 심하게 분할되면 공간을 비효율적으로 차지하는 dead tuple이 더 많아집니다.

leaf_fragment를 사용하여 단편화 정도를 확인할 수 있습니다.

SELECT * FROM pgstatindex('<index_name>');

-[ RECORD 1 ]------+-------
version            | 2
tree_level         | 1
index_size         | 196608
root_block_no      | 3
internal_pages     | 1
leaf_pages         | 22
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 64.48
leaf_fragmentation | 13.64

만약 leaf_fragmentation이 높으면 인덱스가 단편화되었을 가능성이 높으므로 REINDEX를 고려해야 합니다. 재구축을 통해 단편화 및 관련 성능 오버헤드를 제거할 수 있습니다.

pgstattuple 사용 모범 사례

PostgreSQL 모니터링 및 유지 관리에 pgstattuple을 사용할 때는 다음과 같은 모범 사례를 고려하십시오.

PostgreSQL 테이블의 팽창을 추정하려면 PostgreSQL wiki에 언급된 check_postgres 쿼리를 사용하십시오.
pgstattuple 확장을 사용하여 데이터베이스 테이블의 물리적 스토리지를 분석하여, 팽창으로 인해 낭비되는 공간을 포함하여 데이터베이스 내 공간 사용량에 대한 자세한 통계를 제공합니다.
상당히 팽창된 테이블과 인덱스를 재구축하여 불필요한 공간을 회수하십시오.
단편화 문제를 식별하기 위해 높은 dead_tuple_percent를 모니터링하십시오.
워크로드 성능에 중요한 테이블과 인덱스에 유지 관리를 집중하십시오.
간섭을 방지하기위해 사용량이 많은 테이블에서는 pgstattuple을 실행을 피하십시오.
pgstattuple 메트릭을 활용하여 autovacuum 설정을 미세 조정할 수 있습니다.
pgstattuple을 쿼리 분석 및 로그와 결합하여 전체적인 데이터베이스 통찰력을 얻으십시오.

결론

pgstattuple 확장은 PostgreSQL 데이터베이스에서 중요한 진단 메트릭을 찾아내는 강력한 도구 역할을 하며, 팀이 팽창 및 인덱스 단편화와 같이 성능에 영향을 미치는 문제를 식별하고 해결하는 데 도움이 되는 상세한 스토리지 통계를 제공합니다. Aurora 및 RDS PostgreSQL과 원활하게 연동되는 이 확장 프로그램은 스토리지 패턴 및 유지 관리 요구 사항에 대한 필수 가시성을 제공합니다. 효율적이고 고성능의 PostgreSQL 데이터베이스를 유지하기 위해서는 pgstattuple 모범 사례를 따르는 것이 핵심이며, 조직들은 AWS의 다양한 지원 옵션을 통해 데이터베이스 관리를 더욱 강화할 수 있습니다. AWS 엔터프라이즈 지원, 엔터프라이즈 On-Ramp, 비즈니스 지원 고객들은 AWS Countdown Premium 서비스를 활용하여 최적화 가이드를 받을 수 있으며, 이를 통해 팀들은 핵심 비즈니스 목표에 집중하면서도 모범 사례를 자신있게 구현하고 최적의 성능을 유지할 수 있습니다

AWS 기술 블로그