[DB] Vacuum과 Autovacuum이란?

Image: anyrecover.com

Vacuum이란?

Vacuum은 한마디로 휴지통 비우기와 같습니다.

Vacuum은 Non-blocking작업입니다.

Dead tuple → Free space

Autovacuum이란?

Autovacuum = Vacuum + Analyze

Threshold = 전체 데이터 수 * 비율 + 최소문턱값

-- vacuum threshold  
autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threadhold
-- analyze threshold
autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold
-- 전역 설정
alter system set autovacuum_vacuum_scale_factor = 0.1;
-- 확인 쿼리
select * from pg_settings where where name = 'autovacuum_vacuum_scale_factor';
-- 개별설정
alter table {{schema.table_name}} set (autovacuum_vacuum_scale_factor=0.1);
-- 확인쿼리
select relname, reloptions from pg_class where relname={{table_name}};
-- 설정 초기화
alter table {{schema.table_name}} reset (autovacuum_vacuum_scale_factor);

참고문서

--

--

--

Blog https://chrisjune.dev Work for www.29cm.co.kr

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
chrisjune

chrisjune

Blog https://chrisjune.dev Work for www.29cm.co.kr

More from Medium

Oracle 19c “library cache load lock”

Oracle MySQL Heatwave — You need know now!

How To Set Up Replication in MySQL

Class and instance attributes