vacuum_check_logs.sql

--install dblink and connect to database postgres
drop extension dblink;
create extension dblink;
SELECT dblink_connect('myconn', 'dbname=postgres');
drop table vestat;

SET vacuum_cost_delay = 2;

--create test table vestat and generate for it datas
CREATE TABLE vestat(id int, ival int) WITH (autovacuum_enabled = off);
create index vestat_idx on vestat(id);
--run vacuum table
vacuum verbose vestat;
SELECT pg_sleep(3);

INSERT INTO vestat(id) SELECT ival FROM generate_series(1,1000000) As ival;

--delete small part of it and check the number of released blocks in the "tail" of the relationship
delete from vestat where id>900000;

--run vacuum table
vacuum verbose vestat;
SELECT pg_sleep(3);

--run transaction repeatable_read on the test table. update values in table and check vacuum
--block trunsaction prevents vacuum clear tuples and pages
select dblink_exec('myconn','begin transaction isolation level repeatable read');
select * from dblink('myconn','select sum(ival) from vestat') as db1(a integer);
UPDATE vestat SET ival = id + 2;
vacuum verbose vestat;
SELECT pg_sleep(3);
CHECKPOINT;

--cancel blocking the transit and run vacuum again.
select dblink_exec('myconn','commit');
vacuum verbose vestat;
SELECT pg_sleep(3);
CHECKPOINT;

--delete all tuples. vacuum marks add blocks as frozen and all-visible
--also we can see changes in n_dead_tup_vacuumed as count tuples as removed
delete from vestat;
vacuum verbose vestat;
SELECT pg_sleep(3);
CHECKPOINT;

--and again insert tuples and update it
--run vacuum and check result. we can see statistic on blk_write_time
--we can see changes in everywhere except blks_removed
INSERT INTO vestat(id) SELECT ival FROM generate_series(1,1000000) As ival;
UPDATE vestat SET ival = id + 2;
vacuum verbose vestat;
SELECT pg_sleep(3);
CHECKPOINT;