따봉 구걸남's 블로그

이전 글에서 postgresql 의 vacuum 에 대해서 한번 정리 했었다. 

[ PostgreSQL ] 1. vacuum ( vacuum 이 필요한 이유)

vacuum 을 수행하는 명령은 몇가지가 있는데, 각각 기능이 조금 상이하다. 각 명령어마다 어느 작업을 수행하는지 알아보자.

- vacuum 
- vacuum analyze
- autovacuum  
- vacuum full 

VACUUM 작업의 역할

1. dead tuple 정리

VACUUM, VACUUM ANALYZE, VACUUM FULL 모두 dead tuple을 대상으로 공간을 회수한다. 
회수된 공간은 테이블 내에서 재사용 가능해 진다. 

2. 통계정보 수집

VACUUM 만 쓰면 통계정보는 수집하지 않고, VACUUM ANALYZE 를 써야 통계정보까지 새로 수집한다. 
동시에 dead tuple을 정리한다. 
VACUUM FULL만 단독으로 쓰면 통계정보 수집은 하지 않습니다.

3. xid freeze

오래된 tuple의 XID를 frozen 특수 값으로 바꿔서, 트랜잭션 ID wraparound(40억 도달 시 심각한 장애 위험)를 방지
VACUUM, VACUUM ANALYZE, VACUUM FULL 모두 xid freeze 처리를 지원한다.

4. 안쓰는 공간 OS 반환

일반 VACUUM, VACUUM ANALYZE는 dead tuple이 차지하던 공간을 내부적으로만 재사용할 뿐, 실제로 운영체제(OS) 입장에서 파일은 줄지 않는다.
VACUUM FULL은 dead tuple을 삭제한 뒤, 테이블을 새로 복사하여 불필요한 공간을 OS에 반환한다.
느리고 작업 중 락이 걸린다는 단점이 있다.

정리를 하면... 

dead tuple 정리와 xid freeze 는 어느 vacuum 명령을 해도 처리된다.  

OS 로 공간반환이 필요하거나, 통계 수집이 필요할때만 필요한 vacuum 명령어를 사용해주면 된다. 

# autovacuum 과 vacuum analyze 의 차이

autovacuum 은 특정 조건을 만족하면 자동으로 vacuum 작업이 돌게 된다. 
그러나 이름과 다르게, (autovacuum 이면 vacuum 만 자동으로 할거 같지만..)
위의 표에서 보듯이 autovacuum 이 하는 작업은 vacuum analyze 와 동일하다.

 - dead tuple 정리
 - xid freeze 
 - 통계 정보 수집

내부 작동 방식이 차이가 나면서 autovacuum 은 lock 을 적게 사용하여 운영중에도 사용이 가능하다.  

autovacuum 에 대해서는 별도의 포스팅 에서 정리해 보자. 

pg_repack 를 사용하면 테이블 재구축을 할수있어 vacuum full 을 대체할수 있다. 

vacuum full  은 작업시간동안 대상 테이블의 lock 을 잡아 dml 은 물론 조회까지 불가하다.

반면 pg_repack 은 트리거와 로그테이블을 사용하여 테이블 전체를 복사하는데 잠깐 동안만 lock 을 잡게 된다. 

다만 별도의 리눅스 서버에 pg_repack 클라이언트 설치가 필요하고, 이곳에서 명령을 실행해야 한다. 

AWS EC2 서버에 pg_repack 클라이언트를 설치해보자. 

** 참고로 aws free tier 는 스토리지를 20GB 까지 사용할수 있고, 그 이상부터는 유료 과금이 된다. 

postgresql RDS 가 20GB 를 필요로 하니, 추가로 ec2 서버를 생성한다면 유료 과금이 된다.

짧게 테스트용으로만 설치하고 바로 삭제하자. 

테스트환경 구축하기

1. postgresql RDS 설치 

postgresql RDS 설치는 이전에 작성한 글을 참고하자.

[AWS] PostreSQL RDS 생성 하기

2. EC2 설치 (pg_repack client)

ec2 리눅스 서버 설치도 이전에 작성한 글을 참고하자.

아마존 AWS EC2 생성하기 프리티어

설치가 완료되었으면 ec2 -> RDS 로 접속이 가능하도록 방화벽 작업을 해줘야 한다. 

psql 은 설치하지 않아도 pg_repack 사용는 것에는 무방하지만 db접속이 잘 되는지 확인용도로 설치한다. 

# psql (생략가능)

sudo apt update
sudo apt install postgresql-client
sudo apt install libzstd-dev liblz4-dev zlib1g-dev libreadline-dev

psql -h dbid.xxxxxxxxx.ap-northeast-2.rds.amazonaws.com -p 5000 -U pgdba -d postgres

* 나는 기본 포트 대신 5000 을 사용하고 pgdba 라는 유저를 사용한다. 

# pg_repack 설치

설치전에 서버의 pg_repack 버전 확인이 필요하다. 

아직 (db서버에) 설치 전 이라면 아래 명령어로 extension 설치가 필요하다. 

create extension pg_repack;

나는 1.5.0 버전이 설치되어 있는데 client 도 동일하게 버전을 맞춰주자. 

wget https://api.pgxn.org/dist/pg_repack/1.5.0/pg_repack-1.5.0.zip
unzip pg_repack-1.5.0.zip
cd pg_repack-1.5.0

# PostgreSQL 버전에 맞는 컴파일 및 설치
make
sudo make install

* error 가 없이 잘 끝났다면 설치는 완료되었고 경로를 path 에 추가해 준다. 

export PATH=$PATH:/home/ubuntu/pg_repack/bin

이제 pg_repack 을 실행해준다. 

pg_repack -h dbid.xxxxxxxxx .ap-northeast-2.rds.amazonaws.com -p 5000 -U pgdba --table=t1 -k postgres

* RDS 에서 사용하려면 -k 옵션을 붙여줘야 한다. 

만약 pg_repack 의 버전이 맞지 않다면 아래처럼 업그레이드 할수 있는데, postgresql 의 버전이 이를 수용 못하면 

클라이언트를 새로 버전 맞춰서 설치해야 한다. 

ALTER EXTENSION pg_repack UPDATE TO '1.5.2'; 

or 삭제 후 새로 설치 

DROP EXTENSION pg_repack;
CREATE EXTENSION pg_repack VERSION '1.5.2';   -- 클라이언트 버전에 맞추어 변경



# pg_repack client 의 삭제 
sudo apt remove postgresql-repack

rm -rf ./pg_repack

wget https://api.pgxn.org/dist/pg_repack/1.5.0/pg_repack-1.5.0.zip
unzip pg_repack-1.5.0.zip
cd pg_repack-1.5.0

3. pg_repack 작업 스크립트 

동일구조 테이블을 2개 생성하여 각각에 vacuum full 과 pg_repack 을 하여 비교해보자 

-- 1. 동일 구조의 두 테이블 생성
DROP TABLE IF EXISTS t_vacu;
DROP TABLE IF EXISTS t_repk;

CREATE TABLE t_vacu (
    id SERIAL PRIMARY KEY,
    data TEXT
);

CREATE TABLE t_repk (
    id SERIAL PRIMARY KEY,
    data TEXT
);

-- 2. 동일 데이터 대량 삽입 (예: 10만 건)
INSERT INTO t_vacu (data)
SELECT md5(random()::text)
FROM generate_series(1, 100000);

INSERT INTO t_repk (data)
SELECT md5(random()::text)
FROM generate_series(1, 100000);

-- 3. 초기 상태 확인 : 테이블 크기 및 dead tuple 비율 조회
SELECT
    relname,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    n_live_tup,
    n_dead_tup,
    ROUND((n_dead_tup::numeric / (n_live_tup + n_dead_tup)) * 100, 2) AS dead_tuple_pct
FROM pg_stat_user_tables
WHERE relname IN ('t_vacu', 't_repk');

-- 4. 일부 데이터 삭제 (예: 30% 정도)
DELETE FROM t_vacu WHERE id % 10 < 3;
DELETE FROM t_repk WHERE id % 10 < 3;

-- 5. 삭제 후 상태 다시 확인
SELECT
    relname,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    n_live_tup,
    n_dead_tup,
    ROUND((n_dead_tup::numeric / (n_live_tup + n_dead_tup)) * 100, 2) AS dead_tuple_pct
FROM pg_stat_user_tables
WHERE relname IN ('t_vacu', 't_repk');


-- 6. VACUUM FULL,  pg_repack  적용 (t_vacu)
VACUUM FULL t_vacu;

* 아래는 pg_repack 클라이언트에서 실행한다.  
export PATH=$PATH:/home/ubuntu/pg_repack-1.5.0/bin

pg_repack -h dbid.xxxxxxxxx.ap-northeast-2.rds.amazonaws.com -p 5000 -U pgdba --table=t_repk -k postgres
 
-- 7. 적용 후 상태 재확인
SELECT
    relname,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    n_live_tup,
    n_dead_tup,
    ROUND((n_dead_tup::numeric / (n_live_tup + n_dead_tup)) * 100, 2) AS dead_tuple_pct
FROM pg_stat_user_tables
WHERE relname IN ('t_vacu', 't_repk');

Postgresql 에서 많이 사용하는 파라미터와 설명을 정리해 보았다.