2

How to find in PostgreSQL 9.5 what is causing deadlock error/failure when doing full vacuumdb over database with option --jobs to run full vacuum in parallel.

I just get some process numbers and table names... How to prevent this so I could successfuly do full vacuum over database in parallel?

Sasa Bajic
  • 113
  • 1
  • 6

1 Answers1

1

Completing a VACUUM FULL under load is a pretty hard task. The problem is that Postgres is contracting space taken by the table, thus any data manipulation interferes with that.

To achieve a full vacuum you have these options:

  • Lock access to the vacuumed table. Not sure if acquiring some exclusive lock will help, though. You may need to prevent access to the table on application level.
  • Use a create new table - swap (rename tables) - move data - drop original technique. This way you do not contract space under the original table, you free it by simply dropping the table. Of course you are rebuilding all indexes, redirecting FKs, etc.

Another question is: do you need to VACUUM FULL? The only thing it does that VACUUM ANALYZE does not is contracting the table on the file system. If you are not very limited by disk space you do not need doing a full vacuum that much.

Hope that helps.

Boris Schegolev
  • 3,601
  • 5
  • 21
  • 34
  • I thought that VACUUM FULL be good because of performance. Because database is over 350GB in size during after one month of usage, and after VACUUM FULL it is shrinked to 240GB...I think that is a lot of garbage in tables and indexes because of a lot of updates and deletes. And that database will gain in performance because after VACUUM FULL size will be smaller and also indexes will be rebuild. WHat do you think...Should I do because of performance in this case full vacuum? – Sasa Bajic Sep 12 '16 at 16:35
  • 1
    `VACUUM ANALYZE` does everything you need performance-wise. It cleans up the table and indexes (plus updates usage statistics), but leaves the allocated space. You can notice that after the procedure the table does not grow in size for some time because it reuses the previously allocated space. So, if you are not in desperate need of disk space I would advice to only do `VACUUM ANALYZE` in production environment. `VACUUM FULL` does not give you anything more except for some free disk space. – Boris Schegolev Sep 12 '16 at 19:08
  • I find the idea of 'only diskspace' a bit easy, as I notice that at times very significant amounts of diskspace get caught up and freeing this space at the end of a long ETL is well worth while and is not done routinely. However, single threadedly this can take ages, so multithread is a real help. At least in version 11.12 the deadlock with multiple vacuum full jobs persists, also when not under load. Please advise whether this is solved in 12/13/14, so I have one more reason to do an upgrade. – Jan Nov 08 '21 at 13:34