3

I have run a full VACUUM to a Postgres table with 9 million rows and I got this back:

INFO:  vacuuming "osm.asp_trs_roads_osm"
INFO:  "asp_trs_roads": found 329992 removable, 9660438 nonremovable row versions in 761738 pages

DETAIL: 0 dead row versions cannot be removed yet. CPU 8.98s/13.06u sec elapsed 549.77 sec. Query returned successfully with no result in 991788 ms.

What does the "329992 removable" mean? And why do I get:

   0 dead row versions cannot be removed yet.

My table is indexed and has primary keys but executing queries on it is very slow.

user1919
  • 3,818
  • 17
  • 62
  • 97

3 Answers3

2

Due to my more than modest knowledge when you change the row (update,delete) a old version is kept if is in use by other transactions (in same physical location), when no more transaction can need the obsolete version of the row - it becomes removable.

sorry - can't find clear description in docs

If I get it right - removable are shown by analyze part of vacuum, and dead rows removed and not is shown by actual vacuum part.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
2

In simple way you can understand that, when you update or delete data on your table, PostgreSQL does not remove it immediately, it just marked it. The Vacuum command is as an garbage collector, it will go through those table and remove it to recover the disk space.

You can read document about Vacuum in Wiki for more details information:

https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT

Mabu Kloesen
  • 1,248
  • 7
  • 8
2

In addition to what others already answered, regarding this output in log:

0 dead row versions cannot be removed yet.

When an update or delete is performed on any row, a new version of that row is created in order to replace previous one once the transaction is committed. Transactions that started before the one performing the update/delete might continue seeing old row version (depending on the transaction isolation level). Therefore vacuum cannot remove those versions until it can be ensured none of them will be visible anymore.

Log reports about those cases: in your example, there are no row versions that couldn't be removed because they might be accessed by other transactions.

See this related question.

alostale
  • 770
  • 1
  • 11
  • 21