멋지게 놀아라

PostgreSQL에서 reog 대상 테이블 선정 및 반환되는 사이즈 확인

0. 결론

- 정확하게 반환되는 사이즈는 확인 불가 (free 공간 비슷하게 공간 반환)

--> 데이터가 block에 저장 될 때 저장가능 모든 공간을 사용할 수 있는 데이터 사이즈 일 경우 free size가 다 반환되나, 남은 공간이 하나의 Row를 저장할 공간이 되지 않을 경우 , 새로운 block에 저장하므로 free 공간이 생길 수 밖에 없으므로 free 공간이 다 반환된다고 볼 수는 없다

- pgstattuple은 전체 테이블에 대한 full scan을 수행 한다

1. 테스트 데이터 생성

2. 데이터 변경

전체 데이터 중에서 1/100만 변경

update 속도 향상을 위해서 인덱스 생성

3. 데이터 확인

현재 테이블의 데이터 중에서 free 공간이 얼마나 되는지 확인해 보자

테스트 #1, 테스트 #2의 결과가 너무 다르므로 실제 vacuum full을 수행해 본다

4. vacuum full 수행

5. 재 테스트

예상으로는 free size인 10 MB가 줄어서 729 MB가 되기를 기대 하였으나 테이블 사이즈가 732 MB로 약 7 MB정도 줄어 들었다

그래서 다시 auto vacuum이 수행되지 않도록 테이블에 설정 후 다시 테스트 진행

테이블 생성	drop table if exists test_reog ; create table test_reog ( item_code      char(12) ,   order_id       char(12) ,   order_flag     char(1) ,    order_date     timestamp ,   check_flag     char(1) ,   check_date     timestamp ,   delivery_flag  char(1) ,   delivery_date  timestamp ,   delivery_complite_flag char(1) ,   delivery_complite_date timestamp ) ;   alter table test_reog set (autovacuum_enabled = false) ;
데이터 입력	do $insert$ declare   i int; begin   for i in 1..10000000 loop     insert into test_reog values ( concat('item',lpad(i::bpchar,8,'0')) , 'admin' , 'Y' , now() ,  NULL , NULL , NULL , NULL , NULL , NULL ) ;   end loop; end $insert$ ; select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze , pg_size_pretty(pg_relation_size( 'test_reog' )) as table_size from pg_stat_all_tables where relname= 'test_reog' ;  schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size ------------+-----------+------------+-----------+------------+-----------------+--------------+------------  scott      | test_reog |   10000000 |         0 |          0 |                 |              | 730 MB create index test_reog_item_code on test_reog(item_code) ; select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,        pg_size_pretty(pg_table_size('test_reog')) as table_size ,        pg_size_pretty(pg_indexes_size('test_reog')) as idx_size from pg_stat_all_tables where relname= 'test_reog' ;  schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size | idx_size ------------+-----------+------------+-----------+------------+-----------------+--------------+------------+----------  scott      | test_reog |   10000000 |         0 |          0 |                 |              | 730 MB     | 301 MB
데이터 변경	do $update$ declare   i int; begin   for i in 1..10000000 loop     if mod(i,100) = 0 then       update test_reog       set    check_flag = 'Y' , check_date = now()       where  item_code = concat('item',lpad(i::bpchar,8,'0'))::bpchar ;     end if;   end loop; end $update$ ; select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,        pg_size_pretty(pg_table_size('test_reog')) as table_size ,        pg_size_pretty(pg_indexes_size('test_reog')) as idx_size from pg_stat_all_tables where relname= 'test_reog' ;  schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size | idx_size ------------+-----------+------------+-----------+------------+-----------------+--------------+------------+----------  scott      | test_reog |   10000000 |    100000 |     100000 |                 |              | 739 MB     | 301 MB
psstattuple	select pg_size_pretty(table_len) as table_size , pg_size_pretty(tuple_len) as tuple_size , pg_size_pretty(free_space) as free_size , tuple_count , dead_tuple_count , tuple_percent , free_percent from pgstattuple('scott.test_reog');  table_size | tuple_size | free_size | tuple_count | dead_tuple_count | tuple_percent | free_percent ------------+------------+-----------+-------------+------------------+---------------+--------------  739 MB     | 688 MB     | 3002 kB   |    10000000 |           100000 |         93.12 |          0.4 현재는 vacuum이 수행되지 않아서 dead tuple이 공간을 차지 하고 있으므로 free 공간이 3MB임을 알 수 있다
vacuum 후 pgstattuple	vacuum test_reog ; select pg_size_pretty(table_len) as table_size , pg_size_pretty(tuple_len) as tuple_size , pg_size_pretty(free_space) as free_size , tuple_count , dead_tuple_count , tuple_percent , free_percent from pgstattuple('scott.test_reog');  table_size | tuple_size | free_size | tuple_count | dead_tuple_count | tuple_percent | free_percent ------------+------------+-----------+-------------+------------------+---------------+--------------  739 MB     | 688 MB     | 10036 kB  |    10000000 |                0 |         93.12 |         1.33 vacuum을 수행 후 dead tuple이 있던 공간이 사용가능 공간으로 변경 되어서 free 공간이 10MB로 늘어 남
vacuum full	vacuum full test_reog ; select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,        pg_size_pretty(pg_table_size('test_reog')) as table_size ,        pg_size_pretty(pg_indexes_size('test_reog')) as idx_size from pg_stat_all_tables where relname= 'test_reog' ;  schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size | idx_size ------------+-----------+------------+-----------+------------+-----------------+--------------+------------+----------  scott      | test_reog |   10000000 |    100000 |          0 |                 |              | 732 MB     | 301 MB 역시나 10 MB의 공간이 반환 되기를 기대 하였으나, 7 MB의 공간만이 줄어 들었다 select pg_size_pretty(table_len) as table_size , pg_size_pretty(tuple_len) as tuple_size , pg_size_pretty(free_space) as free_size , tuple_count , dead_tuple_count , tuple_percent , free_percent from pgstattuple('scott.test_reog');  table_size | tuple_size | free_size | tuple_count | dead_tuple_count | tuple_percent | free_percent ------------+------------+-----------+-------------+------------------+---------------+--------------  732 MB     | 688 MB     | 2969 kB   |    10000000 |                0 |         94.05 |          0.4 pgstattuple로 다시 정보를 조회해 보면 free 공간이 약 3 MB 존재 한다 테이블 사이즈 739 MB에서 10 MB가 줄어든 729 MB를 기대 하였으나 732 MB의 테이블 사이즈를 가지며, 약 3 MB의 free 공간이 있는 상태이다 그러면 732 MB - 3 MB 하면 729 MB로 예상했던 사이즈로 생각해 볼 수가 있다 ?
3MB ?	vacuum full을 해서 모든 데이터를 다시 insert 했음에도 약 3MB의 free 공간이 존재하는 이유를 찾아보자 select relpages from pg_class where relname = 'test_reog' ;  relpages ----------     93660 전체 블록 개수는 93660 select 2969 *1024 / 93660 ;  ?column? ----------        32 free 공간(2969 kb)을 블로 개수로 나누면 한 블록당 약 32 byte의 여유 공간이 존재 한다는 뜻 블록을 살펴 보자 !!! CREATE EXTENSION pageinspect; 테이블의 블록을 처음부터 확인해 보자 select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',0)) union all select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',1)) union all select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',2)) union all select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',3)) union all select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',93658)) union all select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',93659)) ;  lower | upper | special | pagesize -------+-------+---------+----------    452 |   488 |    8192 |     8192    452 |   488 |    8192 |     8192    452 |   488 |    8192 |     8192    452 |   488 |    8192 |     8192    452 |   488 |    8192 |     8192    332 |  2648 |    8192 |     8192 블록의 lower ~ upper까지의 공간이 free 공간인데, 맨 마지막 블록을 제외하고 488-452 하면 약 36 byte의 free 공간을 가지고 있는 것이 확인된다 전체 free 공간을 더해 보자 do $select$ declare   i int ;   free_sum int := 0 ;   free int := 0 ; begin   for i in 0..93658 loop     select upper - lower     into   free         from page_header(get_raw_page('test_reog', i )) ; free_sum := free_sum + free ; free := 0 ;   end loop;   RAISE NOTICE 'free : % ' , free_sum ; end $select$ ; NOTICE:  free : 3412564 select pg_size_pretty(3412564::numeric) ;  pg_size_pretty ----------------  3333 kB 정확하게 일치 하지 않지만, 비슷한 사이즈의 free 공간이 있음을 확인 할 수 있다 하나의 tuple의 평균 사이즈를 확인해 보면 analyze test_reog ; select 'test_reog' as tablename , sum(avg_width) as avg_row_byte from pg_stats where tablename ='test_reog' ;  tablename | avg_row_byte -----------+--------------  test_reog |           62 하나의 tuple의 평균 사이즈가 62 byte 이므로 하나의 tuple을 저장할 공간으로 62 byte가 되지 않을 경우 다른 블록에 데이터를 insert 하므로 블록 당 free 공간이 존재 함을 확인 할 수 있다 정확하게 free 공간이 일치 하였으면 깔끔했을 것 같으나, 대략적인 원인 정도만 파악을 해서 약간 아쉬움(?)이 남는다

6. Reog 대상 확인 쿼리

아래 쿼리는 테이블 크기가 500MB 이상인 테이블을 대상으로 freespace가 많은 테이블들 순으로 조회 하는 쿼리 입니다