pg_repack 사용하는 이유

PostgreSQL의 특이한 MVCC 구현 방식과 vacuum이라는 동작 때문에
제때 vacuum이 동작하지 않으면 Table과 Index의 bloat 현상으로 불필요하게 많은 디스크 공간이 차지 될 수 있고
쿼리가 필요한 block,page 보다 더 많은 양을 읽게 되어 성능이 느려질 수 있습니다

이런 현상을 정리하기 위해 PostgreSQL에는 vacuum full 이라는 명령어가 있지만
이 커맨드는 작업 중 테이블에 Exclusive lock을 잡기 때문에 Production DB에서는 사용하기 어려운 커맨드이고
이런 vacuum full의 한계를 해소하기 위한 tool (extension) 이 pg_repack인데요
이번 글에서는 Aurora-PostgreSQL을 기준으로 pg_repack 에 대해 테스트해본 내용을 공유드리겠습니다.

pg_repack 사용하는 방법

DB에서

작업도중 autovacuum이 돌지 않도록 off 시킴

ALTER TABLE tb_test SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

터미널에서

pg_repack -k -h kimdubi-test-pg -p 5432 -U master -d testdb --table test.tb_test --dry-run  
 
PGOPTIONS="-c max_parallel_maintenance_workers=6 -c idle_in_transaction_session_timeout=12h" \  
pg_repack -k -h kimdubi-test-pg -p 5432 -U master -d testdb --table test.tb_test --wait-timeout=1 --no-kill-backend -e

=> -k, –wait-timeout, –no-kill-backend 옵션 필수, 이유는 아래에서 설명

위 커맨드를 수행하면 내부적으로는 아래와 같이 진행된다

원본 테이블에 대한 변경 사항을 기록하기 위해 로그 테이블을 생성

LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE
 
LOG: (query) CREATE TYPE repack.pk_23990 AS (id bigint)
LOG: (query) CREATE TABLE repack.log_23990 (id bigserial PRIMARY KEY, pk repack.pk_23990, row test.tb_test)

원본 테이블에 INSERT, UPDATE, DELETE 트리거를 추가하고 이를 통해 로그 테이블에 변경사항을 저장 (CDC)

LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON test.tb_test FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_23990(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_23990) END, $2)')
LOG: (query) ALTER TABLE test.tb_test ENABLE ALWAYS TRIGGER repack_trigger
LOG: (query) COMMIT

원본 테이블의 모든 row를 COPY한 신규 테이블을 생성

LOG: (query) LOCK TABLE test.tb_test IN ACCESS SHARE MODE
LOG: (query) CREATE TABLE repack.table_23990 WITH (oids = false) TABLESPACE pg_default AS SELECT id,product_id,seller_id,status,stock_status,sale_start_at,sale_end_at,special_start_at,special_end_at,active_status,active_start_at,active_end_at,created_at,updated_at,created_by,updated_by FROM ONLY test.tb_test WITH NO DATA
LOG: (query) INSERT INTO repack.table_23990 SELECT id,product_id,seller_id,status,stock_status,sale_start_at,sale_end_at,special_start_at,special_end_at,active_status,active_start_at,active_end_at,created_at,updated_at,created_by,updated_by FROM ONLY test.tb_test
LOG: (query) COMMIT

신규 테이블에 인덱스를 생성

LOG: (query) CREATE UNIQUE INDEX index_27767 ON repack.table_23990 USING btree (id)
LOG: (query) CREATE UNIQUE INDEX index_27769 ON repack.table_23990 USING btree (seller_id, product_id)
LOG: (query) CREATE INDEX index_27953 ON repack.table_23990 USING btree (active_status)
LOG: (query) CREATE INDEX index_27955 ON repack.table_23990 USING btree (product_id)
LOG: (query) CREATE INDEX index_27956 ON repack.table_23990 USING btree (status)

로그 테이블에 저장된 모든 변경 사항(CDC) 를 신규 테이블에 COPY

LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_23990 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_23990 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_23990 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_23990 SET (id, product_id, seller_id, status, stock_status, sale_start_at, sale_end_at, special_start_at, special_end_at, active_status, active_start_at, active_end_at, created_at, updated_at, created_by, updated_by) = ($2.id, $2.product_id, $2.seller_id, $2.status, $2.stock_status, $2.sale_start_at, $2.sale_end_at, $2.special_start_at, $2.special_end_at, $2.active_status, $2.active_start_at, $2.active_end_at, $2.created_at, $2.updated_at, $2.created_by, $2.updated_by) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_23990 WHERE id IN (
LOG:    (param:5) = 1000

Table swap

LOG: (query) LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE
LOG: (query) SELECT repack.repack_swap($1)
LOG:    (param:0) = 23990
LOG: (query) COMMIT

원본 테이블 및 로그 테이블 drop

LOG: (query) SELECT repack.repack_drop($1, $2)
LOG:    (param:0) = 23990
LOG:    (param:1) = 4
신규 테이블 analyze
LOG: (query) ANALYZE test.tb_test

주의사항

PostgreSQL 버전별로 호환 가능한 pg_repack 버전이 다름

Aurora PostgreSQL Version pg_repack Version
----------------------------------------------------------------
Aurora PostgreSQL 14 pg_repack 1.4.7
Aurora PostgreSQL 13 pg_repack 1.4.6
Aurora PostgreSQL 12 pg_repack 1.4.5
Aurora PostgreSQL 11 pg_repack 1.4.4
Aurora PostgreSQL 10 pg_repack 1.4.3
 
###  에러 로그  
[ec2-user@testserver ~]$ pg_repack -k -h dbatest-pg.cluster-cvqgfwbdqpys.ap-northeast-2.rds.amazonaws.com -p 5432 -U master -d testdb --table public.contacts --dry-run  

INFO: Dry run enabled, not executing repack
WARNING: permission denied to set role "rds_superuser"
WARNING: permission denied to set role "rds_superuser"
ERROR: pg_repack failed with error: program 'pg_repack 1.4.7' does not match database library 'pg_repack 1.4.6'
 
testdb=> create extension pg_repack version "1.4.7"
testdb-> ;
ERROR: extension "pg_repack" has no installation script nor update path for version "1.4.7"

=> pg_repack을 수행할 터미널 서버의 pg_repack 버전과 대상 RDS pg_repack의 버전이 동일해야함

super user 이슈

[ec2-user@pg_repack-1.4.6]$ pg_repack -h test-repack-instance.xxxxxxx.rds.amazonaws.com -U repackuser -t dashboard testdb
 
ERROR: pg_repack failed with error: You must be a superuser to use pg_repack

=> pg_repack 은 superuser 권한을 요구하는데 RDS에서는 super user권한을 부여할 수 없음. 그래서 pg_repack 수행 시 -k 옵션을 줘서 super user check를 skip 해야함

대상 테이블에는 반드시 PK가 있어야함

[ec2-user@ pg_repack-1.4.6]$ pg_repack -h test-repack-instance.xxxxxxx.rds.amazonaws.com -U repackuser -t dashboard -k testdb
 
WARNING: relation "public.dashboard" must have a primary key or not-null unique keys

metadata lock

### pg_repack
LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE
 
### session 1
testdb=> begin;
BEGIN
testdb=*> select * from test.tb_test limit 1;
 
 
### session 2
select * from test.tb_test limit 10;
Time: 966.620 ms  <=== 단순 limit 10 인데도 쿼리가 오래걸림
 
### session 조회
testdb=> select pid,state,query from pg_stat_activity where usename='master';
  pid  |        state        |                                query
-------+---------------------+----------------------------------------------------------------------
 13213 | active              | LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE
 
testdb=> select pid,state,query from pg_stat_activity where usename='master' and pid = 13213;
  pid  |             state             |                                query
-------+-------------------------------+----------------------------------------------------------------------
 13213 | idle in transaction (aborted) | LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE

=> lock 시도했다가, 실패해서 aborted 반복
=> pg_repack은 repack 수행을 위해 원본 테이블에 ACCESS EXCLUSIVE MODE LOCK을 획득하려함,
이 때 메타데이터 락 연쇄 작용으로 해당 테이블을 사용하려는 모든 트랜잭션이 락 대기하게 될 수 있음
postgresql에서는 lock 획득 시도 후, 실패하면 aborted, 재시도, aborted 과정을 자동으로 반복하므로써 이런 연쇄 대기 장애를 피하지만,
결국 약간의 lock 대기는 하게 되기때문에 서비스 지연이 발생할 수 있음

idle_in_transaction_session_timeout

### 인덱스 생성이 오래걸리는 경우 idel_in_transaction_session_timeout에 걸려 아래처럼 실패할 수 있음
NOTICE: Setting up workers.conns
INFO: repacking table "test.tb_test"
LOG: Initial worker 0 to build index: CREATE INDEX index_28754460 ON repack.table_23990 USING btree (i_id, p) TABLESPACE pg_default
LOG: Initial worker 1 to build index: CREATE UNIQUE INDEX index_30301 ON repack.table_23990 USING btree (id) TABLESPACE pg_default
LOG: Command finished in worker 1: CREATE UNIQUE INDEX index_30301 ON repack.table_23990 USING btree (id) TABLESPACE pg_default
LOG: Command finished in worker 0: CREATE INDEX index_28754460 ON repack.table_23990 USING btree (i_id, p) TABLESPACE pg_default
ERROR: query failed: FATAL:  terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly
DETAIL: query was: SAVEPOINT repack_sp1

 
### 해결 방법
PGOPTIONS="-c max_parallel_maintenance_workers=6 -c idle_in_transaction_session_timeout=12h" \
pg_repack -k -h kimdubi-test-pg -p 5432 -U master -d testdb --table test.tb_test --wait-timeout=1 --no-kill-backend -e

session 정리 이슈

### pg_repack
[root@0d3b57e1fae5 share]# pg_repack -k -h kimdubi-test-pg -p 5432 -U master -d testdb --table test.tb_test
Password:
INFO: repacking table "test.tb_test"
 
 
 
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
.
.
.
.
WARNING: terminating conflicted backends


### 정리된 세션
testdb=*> select * from test.tb_test limit 1;
FATAL: terminating connection due to administrator command
SSL connection has been closed unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.
psql (14.1, server 13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

=> pg_repack 은 –wait-timeout (default 60s) 만큼 기다린 후 락을 점유하고 있는 세션들을 취소하며 pg_repack 수행을 시도하고
그래도 점유에 실패하면 –wait-timeout 만큼 한번 더 기다린 후에, 락을 점유하고 있는 세션들을 kill 해버린다.
이 경우 서비스에 이슈가 발생할 수 있으므로 반드시 –wait-timeout=1 –no-kill-backend 옵션을 부여하여
아래처럼 pg_repack이 락 점유 획득에 실패하면 오래 기다리지않고 다른 세션들도 킬하지 않도록 한다

LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 600
LOG: (query) LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE
DEBUG: query failed: ERROR:  canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE test.tb_test IN ACCESS EXCLUSIVE MODE
LOG: (query) ROLLBACK
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
WARNING: timed out, do not cancel conflicting backends
LOG: (query) ROLLBACK
LOG: (query) RESET statement_timeout
INFO: Skipping repack test.tb_test due to timeout

pg_repack 사용하는 이유#

pg_repack 사용하는 방법#

DB에서#

터미널에서#

주의사항#

pg_repack 사용하는 이유

pg_repack 사용하는 방법

DB에서

터미널에서

주의사항