Skip to content

vacuuming space

Prayag edited this page Dec 23, 2022 · 1 revision

vacuum data

select trim(pgdb.datname) as Database,
    trim(a.name) as Table,  ((b.mbytes/part.total::decimal)*100)::decimal(5,2) as pct_of_total, b.mbytes, b.unsorted_mbytes
    from stv_tbl_perm a
    join pg_database as pgdb on pgdb.oid = a.db_id
    join (select tbl, sum(decode(unsorted, 1, 1, 0)) as unsorted_mbytes, count(*) as mbytes
    from stv_blocklist group by tbl) b on a.id=b.tbl
    join ( select sum(capacity) as  total
      from stv_partitions where part_begin=0 ) as part on 1=1
    where a.slice=0
    order by 3 desc, db_id, name;
select * from svv_vacuum_progress;
SELECT schema, 
table_id, 
"table", 
diststyle, 
sortkey1, 
sortkey_num, 
unsorted, 
tbl_rows, estimated_visible_rows, stats_off 
FROM svv_table_info;

public | 4333001 | prayag_test_events | KEY(user_key) | date_modified | 3 | 100.00 | 71850 | 71838
SELECT table_id, status, rows, sortedrows, blocks, eventtime
FROM stl_vacuum;



109643 | [VacuumBG] Started Delete Only | 4 | 0 | 9 | 2022-12-09 00:16:23.179951
select *,
datediff(s,txn_start,getdate())/86400||' days '||datediff(s,txn_start,getdate())%86400/3600||' hrs '||datediff(s,txn_start,getdate())%3600/60||' mins '||datediff(s,txn_start,getdate())%60||' secs' duration 
from svv_transactions 
where lockable_object_type='transactionid' and  pid<>pg_backend_pid() 
order by 3;

PG vacuum

SELECT 
t_xmin, 
t_xmax, 
tuple_data_split('visiting_user'::regclass, t_data, t_infomask, t_infomask2, t_bits) 
FROM heap_page_items(get_raw_page('visiting_user', 0));

 t_xmin | t_xmax |                 tuple_data_split                  
--------+--------+---------------------------------------------------
    756 |    757 | {"\\x01000000","\\x073432","\\x3a61041253930200"}
    757 |      0 | {"\\x02000000","\\x073432","\\x1bbd6c1353930200"}
(2 rows)

postgres=# \x
Expanded display is on.
postgres=# select * from pg_class where relname = 'visiting_user';
-[ RECORD 1 ]-------+--------------
oid                 | 16415
relname             | visiting_user
relnamespace        | 2200
reltype             | 16417
reloftype           | 0
relowner            | 10
relam               | 2
relfilenode         | 16469
reltablespace       | 0
relpages            | 0
reltuples           | -1
relallvisible       | 0
reltoastrelid       | 0
relhasindex         | t
relisshared         | f
relpersistence      | p
relkind             | r
relnatts            | 3
relchecks           | 0
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | d
relispartition      | f
relrewrite          | 0
relfrozenxid        | 755
relminmxid          | 1
relacl              | 
reloptions          | 
relpartbound        | 



postgres=# VACUUM visiting_user;
VACUUM

-- after vacuum
postgres=# SELECT                      
t_xmin, 
t_xmax, 
tuple_data_split('visiting_user'::regclass, t_data, t_infomask, t_infomask2, t_bits) 
FROM heap_page_items(get_raw_page('visiting_user', 0));
 t_xmin | t_xmax |                 tuple_data_split                  
--------+--------+---------------------------------------------------
        |        | 
    757 |      0 | {"\\x02000000","\\x073432","\\x1bbd6c1353930200"}
(2 rows)


postgres=# select relpages, 
relpages * 8192 as total_bytes, 
pg_relation_size('visiting_user') as relsize 
FROM pg_class 
WHERE relname = 'visiting_user';
 relpages | total_bytes | relsize 
----------+-------------+---------
        1 |        8192 |    8192
(1 row)
Clone this wiki locally