Using Postgres 11.1, I have been trying to determine/estimate the amount of 'wasted' space in a table that would be recovered by a VACUUM FULL. My plan was to use pg_stat_get_live_tuples (L), pg_stat_get_dead_tuples (D), and pg_total_relation_size (S), then estimate the wasted space as (D / (L+D)) * S.
However, this does not appear to work. While investigating why not, I did the following setup:
CREATE TABLE sam_silly(txt TEXT);
INSERT INTO sam_silly VALUES('one');
INSERT INTO sam_silly VALUES('two');
INSERT INTO sam_silly VALUES('three');
INSERT INTO sam_silly VALUES('four');
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
Then I did an initial count of the space according to these functions. The results were as expected (there are a million i.e. 2^20 rows):
select pg_total_relation_size(c.oid) AS size, pg_stat_get_live_tuples(c.oid) AS live, pg_stat_get_dead_tuples(c.oid) AS dead
FROM pg_class c where relname='sam_silly';
- 47308800, 1048576, 0
I then deleted half the table:
delete from sam_silly where txt like 'one%' or txt like 'three%';
Immediately repeating the query above gave the results I would expect:
- 47308800, 524288, 524288
Half the data is alive, half dead.
Then I ran 'analyze' and this is where things got weird:
- 47316992, 524288, 0
Space went up slightly but now there are no dead rows!
I tried 'vacuum' too:
- 47316992, 524264, 0
Not much change.
Finally, after VACUUM FULL it behaves as I would expect again:
- 23519232, 524264, 0
Now there are no dead tuples but also the space has been recovered.
The real database has autovacuum on, so it will probably vacuum the tables I'm interested in. But it looks like vacuuming causes it to set the number of dead tuples to 0 even though there is still a lot of 'wasted' space.
Is there a way to find out/estimate how much space is 'wasted' in this situation, when pg_stat_get_dead_tuples is returning zero even though it has not reclaimed the space used by the previously-reported dead tuples?
Exact version in case relevant: 'PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit'