Relpages and reltuples under MVCC

Question

If I understand correctly, under MVCC(multi version concurrency control), dead tuples are left in the page, until the Vacuum comes in and mark them "unused", and until "vacuum full" comes in and reorgnize them to defragment the space -- so we use less space for the same data.

I have a table, which in one environment that hasn't done vacuum full has around:

SELECT relpages, reltuples from pg_class where relname='pg_toast_16450';
 relpages  |  reltuples
-----------+--------------
 544447814 | 6.394397e+06

In another environment that has undergone vacuum full has:

SELECT relpages, reltuples from pg_class where relname='pg_toast_16450';
 relpages |  reltuples
----------+--------------
  2476625 | 4.439228e+06

Looks like relpages does lower dramatically, which matches my understanding. However, reltuples does not. (relpages has a 250X change, yet reltuples has just 1.33X) Does that mean reltuples does not include dead tuples? If that's the case, does the query planner utilizing reltuples to design query plan has a way around dead tuples?

It is spelled out here [pg_class](https://www.postgresql.org/docs/current/catalog-pg-class.html). FYI, the space marked as dead tuples by `VACUUM` is recycled. If you are using `VACUUM FULL` that is usually an indication of improper configuration of autovacuum. — Adrian Klaver, Jan 31 '22 at 19:10
Yes, we didn't tune our vacuum before and it hasn't kept up and it crawled to a level where it's too much tuning to make vacuum work, so we decided to `vacuum full` . Definitely need proper alerts to find out and fix earlier — somenickname, Feb 01 '22 at 20:36

score 1 · Accepted Answer · answered Jan 31 '22 at 19:24

reltuples is an estimate for the number of live rows in a table. As the documentation says,

It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.

So the number an always be slightly off if the last such command (perhaps triggered by autovacuum) has run on the table, and VACUUM (FULL) would fix that.

However, there is a second thing to consider, since this is a TOAST table: it may contain some entries that belong to dead rows in the table. The TOAST entries of a dead row need not be dead themselves, but VACUUM (FULL) would not copy them, so the number can shrink additionally. I would suspect that this has happened, because the number is reduced by more than the 10% that I would expect otherwise.

Relpages and reltuples under MVCC

1 Answers1