1

In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)?

The situation I'm seeing is that vacuum is apparently being run manually, once daily, and the n_dead_tup / n_live_tup data shows that there are a large, and increasing number of dead tuples.

PostgreSQL 9.3 here.

0

Yes, it resets the value. But it is from the stats collector, not vacuum.

postgres=# create table x (a int);
CREATE TABLE
postgres=# insert into x values (1),(2),(3),(4);
INSERT 0 4
postgres=# delete from x where a%2 =0;
DELETE 2

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_all_tables where relname='x';
-[ RECORD 1 ]-------+----------
relid               | 588915846
schemaname          | public
relname             | x
seq_scan            | 1
seq_tup_read        | 4
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 2
n_dead_tup          | 2
n_mod_since_analyze | 6
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0



postgres=# vacuum x;
VACUUM

postgres=# select * from pg_stat_all_tables where relname='x';
-[ RECORD 1 ]-------+------------------------------
relid               | 588915846
schemaname          | public
relname             | x
seq_scan            | 1
seq_tup_read        | 4
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 2
n_dead_tup          | 0
n_mod_since_analyze | 6
last_vacuum         | 2016-01-12 16:49:32.248731+08
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.