Question 267154 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – n_dead_tup same as it was before VACUUM FULL

Today I noticed that saving operations into one of the table is so slow. I ran query select n_live_tup, n_dead_tup, relname from pg_stat_all_tables and found info that my table has 3 336 as n_live_tup and 46 508 837 as n_dead_tup. I guess it’s because of UPSERT operations almost every minute. I tried to run VACUUM FULL on this table (before i stopped all operations with this table), but n_dead_tup still the same. VACUUM FULL took about 1 minute and was completed succesfully. But why n_dead_tup and also table size still the same as it was?

Tags:

2

Answers


  1. 0

    You can find the possible reasons why VACUUM won’t clean up dead tuples in this article. The same holds for VACUUM (FULL) even though it operates differently.

    1. There is a long-running transaction that either has a transaction ID (it modified something) or operates on the REPEATABLE READ or SERIALIZABLE isolation level. Note that those transactions will keep VACUUM (FULL) from removing dead tuples even if they don’t touch the table at all.

    2. There could be a stale replication slot. Check pg_replication_slots for slots that are not active.

    3. There could be an orphaned prepared transaction; check pg_prepared_xacts.

    4. There could be a standby server with not_standby_feedback = on that is running a very long query or has a very old REPEATABLE READ transaction.

    Login or Signup to reply.
  2. 0

    AFAIK VACUUM FULL doesn’t refresh rows states. Or maybe i wrong, and it’s doesn’t refresh table statistics… Don’t remember. =) Anyway try to make VACUUM instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search