Objective: This is a 4 part series of blog. With the following objectives...
Objective: Test the below...
1) what vacuum does to a table
2) what analyze does to a table
3) what autovacuum TX wrap does to a table
4) how to control autovacuum TX wrap routine
1) what vacuum does to a table
2) what analyze does to a table
3) what autovacuum TX wrap does to a table
4) how to control autovacuum TX wrap routine
In the first part we will test the vacuum operation on a table.
PostgreSQL version: 15
OS: Centos 7.9
In this blog we are going to test vacuum operation and see the stats changes on the table
Target DB: pgtst_db
Target Schema: pgtst_schema
Target table:
create table pgtst_vacuum
(
id int PRIMARY KEY
,name text
);
Description of the ddl as seen in psql:
pgtst_db=# \d pgtst_vacuum
Table "pgtst_schema.pgtst_vacuum"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"pgtst_vacuum_pkey" PRIMARY KEY, btree (id)
pgtst_db=#
DDL of the table secured through pg_dump:
pg_dump pgtst_db -t pgtst_schema.pgtst_vacuum --schema-only
Key output:
--
-- Name: pgtst_vacuum; Type: TABLE; Schema: pgtst_schema; Owner: postgres
--
CREATE TABLE pgtst_schema.pgtst_vacuum (
id integer NOT NULL,
name text
);
ALTER TABLE pgtst_schema.pgtst_vacuum OWNER TO postgres;
--
-- Name: pgtst_vacuum pgtst_vacuum_pkey; Type: CONSTRAINT; Schema: pgtst_schema; Owner: postgres
--
ALTER TABLE ONLY pgtst_schema.pgtst_vacuum
ADD CONSTRAINT pgtst_vacuum_pkey PRIMARY KEY (id);
Let us collect few stats on the table: -- call it as data collection rotuine
pg_class
pg_stat_user_Tables
The table & toast - pg_class info:
pgtst_db=# select * from pg_class where relname='pgtst_vacuum' and relkind='r';
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 0
reltuples | -1
relallvisible | 0
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 771
relminmxid | 1
relacl |
reloptions |
relpartbound |
pgtst_db=# select * from pg_class where oid=32814;
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | -1
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 771
relminmxid | 1
relacl |
reloptions |
relpartbound |
pg_stat_user_Tables info for pgtst_vacuum & its toast table:
pgtst_db=# select * from pg_stat_user_tables where relname='pgtst_vacuum';
-[ RECORD 1 ]-------+-------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
pgtst_db=# select * from pg_stat_user_tables where relid=32814;
(0 rows)
An additional piece of info:
postgres=# select * from pg_database where datname='pgtst_db';
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716 <<<<<<<<<<< the min of the relfrozenxid among the tables stored in that db
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Now let us load the base data:
INSERT INTO pgtst_schema.pgtst_vacuum
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (1,10) s(i);
Load completed:
INSERT 0 10
pgtst_db=#
Data collection rotuine:
psql -d pgtst_db -f query_table_data.sql -x -a -q >[output file per step]
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 0
reltuples | -1 <<< still didnt find # of records loaded
relallvisible | 0
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 771 <<< still hasnt increased!!!
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814;
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | -1
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 771
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10 <<< it shows the change
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10
n_dead_tup | 0
n_mod_since_analyze | 10 <<< it shows the change
n_ins_since_vacuum | 10 <<< it shows the change
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db';
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716 <<< no changes here
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Let us check what vacuum does to a table:
vacuum verbose pgtst_vacuum;
Output:
pgtst_db=# vacuum verbose pgtst_vacuum;
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 0 removed, 10 remain, 0 are dead but not yet removable
removable cutoff: 773, which was 0 XIDs old when operation ended
new relfrozenxid: 772, which is 1 XIDs ahead of previous value <<<< notice here the relfrozenxid is incremented
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 42.517 MB/s, avg write rate: 63.776 MB/s
buffer usage: 5 hits, 4 misses, 6 dirtied
WAL usage: 2 records, 2 full page images, 13881 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 773, which was 0 XIDs old when operation ended
new relfrozenxid: 773, which is 2 XIDs ahead of previous value
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 48.225 MB/s, avg write rate: 0.000 MB/s
buffer usage: 19 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pgtst_db=#
Data collection routine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 1 <<< now the number of pages has changed
reltuples | 10 <<< now we can see the record count in the table
relallvisible | 1 <<< now we can see this visibility page map has changed
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772 <<< relation frozen txid has moved forward
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814;
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0 <<< Guess the toast table is now initialized, we dont have any row overflow yet.
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 773 <<< the relfrozenxid has moved +1 compared to table, probably the toast table has a change after table dml,so its TX id is advanced
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10
n_dead_tup | 0
n_mod_since_analyze | 10
n_ins_since_vacuum | 0 <<< the # of inserts post vacuum has changed. Note here vacuum is intrested in only inserts since last such vacuum operation
last_vacuum | 2023-01-24 02:46:38.555339-05 <<< last vacuum date
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 1 <<< obvious vacuum count increase
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no change in any status yet
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Now let us try add some more rows to the table.
pgtst_db=# INSERT INTO pgtst_schema.pgtst_vacuum
SELECT i, lpad('TST',mod(1,100),'CHK')
FROM generate_series (11,100) s(i);
INSERT 0 90
pgtst_db=#
Data collect routine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 1
reltuples | 100 <<< row count has increased, this is a difference in behaviour after first vacuum. Initial load didnt show any change for this field, but after vacuum the inserts are visible here.
relallvisible | 0 <<< has become 0 from 1(vacuum), since last insert
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814; <<< no change
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 773
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 101 <<< 101 inserts!!!!! really?? I inserted only 100 records, there was though a failed attempt due to primary key violation, postgresql is tracking it!!!
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 100 <<< live tuples were only 100, which is accepted
n_dead_tup | 1 <<< dead tuples or record is 1, this is the failed one insert, I guess postgresql tried inserting the record, failed and let the row stay there, may be a seperate blog is needed for this behaviour
n_mod_since_analyze | 0 <<< is 0, since analyze already ran automatically
n_ins_since_vacuum | 91 <<< 91 again!!! inplace of 90. Fine but learnt why so
last_vacuum | 2023-01-24 02:46:38.555339-05
last_autovacuum |
last_analyze |
last_autoanalyze | 2023-01-24 03:30:34.63487-05 <<< the reason why the # of records in the pg_class increased automatically.
vacuum_count | 1
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1 <<< acceptable change
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no change yet
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Let us do a vacuum now...
pgtst_db=# vacuum verbose pgtst_vacuum;
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 1
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 1 removed, 100 remain, 0 are dead but not yet removable
removable cutoff: 776, which was 0 XIDs old when operation ended <<< notice only this message this time, we dont have info on relfrozenxid
index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed
index "pgtst_vacuum_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 47.492 MB/s
buffer usage: 23 hits, 0 misses, 6 dirtied
WAL usage: 4 records, 3 full page images, 17674 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 776, which was 0 XIDs old when operation ended
new relfrozenxid: 776, which is 3 XIDs ahead of previous value
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 4 hits, 0 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pgtst_db=#
Data collect rotuine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 1
reltuples | 100
relallvisible | 1 <<< only change since our last data collection. As expected the pages are marked as visible
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772 <<< hasnt changed, surprisingly. We had a TX
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814;
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 776 <<< this one changed though
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 101
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 100
n_dead_tup | 0 <<< dead records are gone now
n_mod_since_analyze | 0
n_ins_since_vacuum | 0 <<< changes are nullified post vacuum
last_vacuum | 2023-01-24 03:42:10.203435-05 <<< OK
last_autovacuum |
last_analyze |
last_autoanalyze | 2023-01-24 03:30:34.63487-05
vacuum_count | 2 <<< OK too!
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db';
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Until now we have only 1 page. Let us load this table with more data
pgtst_db=# INSERT INTO pgtst_schema.pgtst_vacuum
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (101,10000) s(i);
INSERT 0 9900
pgtst_db=#
Data collect routine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 1
reltuples | 100 <<< now again no record count change, why did no autoanalyze run?
relallvisible | 1
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814; <<< No change here
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 776
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10001 <<< so the 9900 rows were tracked here.
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000
n_dead_tup | 0
n_mod_since_analyze | 9900 <<< agreed change, no analyze you see
n_ins_since_vacuum | 9900 <<< agreed change
last_vacuum | 2023-01-24 03:42:10.203435-05
last_autovacuum |
last_analyze |
last_autoanalyze | 2023-01-24 03:30:34.63487-05
vacuum_count | 2
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no change yet!!
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Let us vacuum here...
pgtst_db=# vacuum verbose pgtst_vacuum;
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 0
pages: 0 removed, 105 remain, 1 scanned (0.95% of total) <<< observe here out of 105 pages, we scanned only 1 page. I think this is explained here in DISABLE_PAGE_SKIPPING in postgresql manual
tuples: 0 removed, 10000 remain, 0 are dead but not yet removable
removable cutoff: 778, which was 0 XIDs old when operation ended <<< relfrozenxid isnt updated yet!!!!
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 16 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 778, which was 0 XIDs old when operation ended
new relfrozenxid: 778, which is 2 XIDs ahead of previous value <<< toast table has relfrozenxid changes, but this is still empty
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 69.754 MB/s
buffer usage: 4 hits, 0 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 5625 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
Data collect routine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 105 <<< Ok, the page count as expected is updated
reltuples | 10000 <<< Ok, the record count as expected is updated
relallvisible | 105 <<< Ok, the visibility map is equal to # of pages.
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814;
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 778 <<< no wonder, this keeps changing as usual
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10001
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000
n_dead_tup | 0
n_mod_since_analyze | 0 <<< we ran just vacuum why is this # reset?
n_ins_since_vacuum | 0 <<< alright
last_vacuum | 2023-01-24 03:57:16.226664-05 <<< yes thats us
last_autovacuum | 2023-01-24 03:52:35.088234-05 <<< hmm bad, the auto vacuum kicked off on the table!!!!
last_analyze |
last_autoanalyze | 2023-01-24 03:52:35.184237-05 <<< the autovacuum had auto analyze too!!!
vacuum_count | 3 <<< alright 3 manual vacuums
autovacuum_count | 1 <<< 1 auto vacuum
analyze_count | 0 <<< no changes here, since no manual analyze is ever ran
autoanalyze_count | 2 <<< OK, all are auto.
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no updates here yet
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
We will load some more data, this time we will disable autovacuum.
=============================
Autovacuum disable routine:
postgres=# select name,setting from pg_Settings where name='autovacuum';
name | setting
------------+---------
autovacuum | on
(1 row)
postgres=#
alter system set autovacuum='off';
postgres=# alter system set autovacuum='off';
ALTER SYSTEM
postgres=# select name,setting from pg_Settings where name='autovacuum';
name | setting
------------+---------
autovacuum | on
(1 row)
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# select name,setting from pg_Settings where name='autovacuum';
name | setting
------------+---------
autovacuum | off
(1 row)
=============================
Let us try loading some more data:
pgtst_db=# INSERT INTO pgtst_schema.pgtst_vacuum
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (10001,20000) s(i);
INSERT 0 10000
pgtst_db=#
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r'; <<< no changes
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 105
reltuples | 10000 <<< now again no record count change, why did no autoanalyze run?
relallvisible | 105
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814; <<< no changes
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 778
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 20001 <<< has increased, OK as expected
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 20000 <<< has increased, OK as expected
n_dead_tup | 0
n_mod_since_analyze | 10000 <<< has increased, OK as expected
n_ins_since_vacuum | 10000 <<< has increased, OK as expected
last_vacuum | 2023-01-24 03:57:16.226664-05
last_autovacuum | 2023-01-24 03:52:35.088234-05
last_analyze |
last_autoanalyze | 2023-01-24 03:52:35.184237-05
vacuum_count | 3
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no changes yet
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Vacuum the table:
pgtst_db=# vacuum verbose pgtst_vacuum;
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 0
pages: 0 removed, 211 remain, 129 scanned (61.14% of total) <<< now we have scanned much larger # of pages
tuples: 0 removed, 20045 remain, 0 are dead but not yet removable <<< why 20045?
removable cutoff: 779, which was 0 XIDs old when operation ended <<< no updates yet to relfrozenxid
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 251.522 MB/s
buffer usage: 265 hits, 0 misses, 115 dirtied
WAL usage: 111 records, 2 full page images, 20312 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 779, which was 0 XIDs old when operation ended
new relfrozenxid: 779, which is 1 XIDs ahead of previous value <<< As usual the relfrozenxid in toast table increased, but is aligned with removable cutoff
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 4 hits, 0 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pgtst_db=#
Vacuum result:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 211 <<< # of pages increased
reltuples | 20045 <<< # of records increased
relallvisible | 211 <<< # of pages visible increased, again equal to the page count
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772 <<< no changes to this yet
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814;
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 779 <<< this changed
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 20001
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 20045 <<< how is it possible, when only 20000 records were inserted!!!!
n_dead_tup | 0
n_mod_since_analyze | 10000
n_ins_since_vacuum | 0 <<< vacuum we performed reset this value
last_vacuum | 2023-01-24 05:52:59.461504-05 <<< OK
last_autovacuum | 2023-01-24 03:52:35.088234-05
last_analyze |
last_autoanalyze | 2023-01-24 03:52:35.184237-05
vacuum_count | 4 <<< Ok
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no changes here yet
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
>>> resuming on 25Jan2023 - there is no change in the metric, pulled a new one and compared against previous day one.All match.
=============================
Let us try loading deleting some data:
delete pgtst_vacuum where id between 10001 and 20000;
pgtst_db=# delete from pgtst_vacuum where id between 10001 and 20000;
DELETE 10000
pgtst_db=#
Data Collection Routine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r'; <<< No Changes here
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 211
reltuples | 20045
relallvisible | 211
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814; <<< No Changes here
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 779
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 3 <<< increased because of the count I took from the table
seq_tup_read | 20000 <<< increased because of the count I took from the table
idx_scan | 3 <<< increased in order to perform the delete
idx_tup_fetch | 10000 <<< increased in order to perform the delete
n_tup_ins | 20001
n_tup_upd | 0
n_tup_del | 10000 <<< increased after the delete
n_tup_hot_upd | 0
n_live_tup | 10045 <<< decreased in order to perform the delete
n_dead_tup | 10000 <<< increased after the delete
n_mod_since_analyze | 20000 <<< increased after the delete
n_ins_since_vacuum | 0
last_vacuum | 2023-01-24 05:52:59.461504-05
last_autovacuum | 2023-01-24 03:52:35.088234-05
last_analyze |
last_autoanalyze | 2023-01-24 03:52:35.184237-05
vacuum_count | 4
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< No change
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Vacuum the table:
pgtst_db=# vacuum verbose pgtst_vacuum;
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: table "pgtst_vacuum": truncated 211 to 105 pages <<< table downsized
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 1
pages: 106 removed, 105 remain, 129 scanned (61.14% of total) <<< table downsized
tuples: 10000 removed, 10025 remain, 0 are dead but not yet removable <<< table downsized
removable cutoff: 780, which was 1 XIDs old when operation ended <<< No relfrozenxid, but this cutoff figure tells us that there was a transaction
index scan needed: 110 pages from table (52.13% of total) had 10000 dead item identifiers removed <<< index scan details
index "pgtst_vacuum_pkey": pages: 57 in total, 26 newly deleted, 26 currently deleted, 0 reusable <<< index management
avg read rate: 17.831 MB/s, avg write rate: 86.185 MB/s
buffer usage: 698 hits, 30 misses, 145 dirtied <<< buffer cleanup
WAL usage: 415 records, 141 full page images, 344499 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 781, which was 0 XIDs old when operation ended <<< toast table shows the TXID
new relfrozenxid: 781, which is 2 XIDs ahead of previous value <<< toast table shows the TXID
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 4.180 MB/s, avg write rate: 0.000 MB/s
buffer usage: 19 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pgtst_db=#
Data Collection Routine:
\pset pager off
\pset title 'pg_class output of pgtst_vacuum'
select * from pg_class where relname='pgtst_vacuum' and relkind='r';
pg_class output of pgtst_vacuum
-[ RECORD 1 ]-------+-------------
oid | 32811
relname | pgtst_vacuum
relnamespace | 16389
reltype | 32813
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32811
reltablespace | 0
relpages | 105 <<< OK, expected change
reltuples | 10025 <<< OK, expected change
relallvisible | 105 <<< OK, expected change
reltoastrelid | 32814
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 772
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_class output of toast table of pgtst_vacuum'
select * from pg_class where oid=32814;
pg_class output of toast table of pgtst_vacuum
-[ RECORD 1 ]-------+---------------
oid | 32814
relname | pg_toast_32811
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 32814
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 781 <<< OK, expected change
relminmxid | 1
relacl |
reloptions |
relpartbound |
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 3
seq_tup_read | 20000
idx_scan | 3
idx_tup_fetch | 10000
n_tup_ins | 20001
n_tup_upd | 0
n_tup_del | 10000
n_tup_hot_upd | 0
n_live_tup | 10025 <<< OK 25 reduced than expected. 20045 became 10025.
n_dead_tup | 0 <<< Dead tuples were cleaned up.
n_mod_since_analyze | 20000
n_ins_since_vacuum | 0
last_vacuum | 2023-01-25 02:27:18.031977-05 <<< last vacuum date
last_autovacuum | 2023-01-24 03:52:35.088234-05
last_analyze |
last_autoanalyze | 2023-01-24 03:52:35.184237-05
vacuum_count | 5 <<< vacuum count increased by 1 as expected
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2
\pset title 'pg_stat_user_tables of toast table of pgtst_vacuum'
select * from pg_stat_user_tables where relid=32814;
(0 rows)
\pset title 'pg_database of pgtst_db'
select * from pg_database where datname='pgtst_db'; <<< no changes
pg_database of pgtst_db
-[ RECORD 1 ]--+------------
oid | 16388
datname | pgtst_db
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 716
datminmxid | 1
dattablespace | 1663
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
daticulocale |
datcollversion | 2.17
datacl |
Observation Summary:
1. It takes care of updating the pg_class table or view with relpages (# of blocks), reltupes (# of rows), relallvisible (# of block visible or visibility map).
2. It takes care of updating the pg_stat_user_tables table or view with n_live_tup, n_dead_tup & resets the n_ins_since_vacuum. It just updates the other fields like vacuum_count & last_vacuum dates
Questions I have:
1) Why failed inserts were tracked by postgresql. I guess it inserted the record then the constriant check triggered per row!!!
2) Visibility map (relallvisible) and # of pages (relpages) - under which circumstance they will show different values??
3) The # of live_tuples is much higher than the actual# of records
\pset title 'pg_stat_user_tables of pgtst_vacuum'
select * from pg_stat_user_tables where relname='pgtst_vacuum';
pg_stat_user_tables of pgtst_vacuum
-[ RECORD 1 ]-------+------------------------------
relid | 32811
schemaname | pgtst_schema
relname | pgtst_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 20001
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 20045 <<<< 20045!!!
n_dead_tup | 0
n_mod_since_analyze | 10000
n_ins_since_vacuum | 0
last_vacuum | 2023-01-24 05:52:59.461504-05
last_autovacuum | 2023-01-24 03:52:35.088234-05
last_analyze |
last_autoanalyze | 2023-01-24 03:52:35.184237-05
vacuum_count | 4
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2
pgtst_db=# \d pgtst_vacuum
Table "pgtst_schema.pgtst_vacuum"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"pgtst_vacuum_pkey" PRIMARY KEY, btree (id)
pgtst_db=# select count(1) from pgtst_vacuum;
-[ RECORD 1 ]
count | 20000 <<<<<<<<< 20000
pgtst_db=#
No comments:
Post a Comment