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)