Is it possible to never run vacuum full in PostgreSQL

Question

Since a VACUUM FULL locks tables, it is unacceptable for us in our production environment.

Is it possible to only run VACUUM and never VACUUM FULL on a production system?

Does VACUUM make space reusable for new INSERTs?

Answer 1

In order to understand the differences between

• VACUUM
• VACUUM ANALYZE
• VACUUM FULL,

a basic understanding of PostgreSQL's architecture is required.

From several9s¹ we are told that:

• PostgreSQL does not use IN-PLACE update mechanism, so as per the way DELETE and UPDATE command is designed,

  • Whenever DELETE operations are performed, it marks the existing tuple as DEAD instead of physically removing those tuples.
  • Similarly, whenever UPDATE operation is performed, it marks the corresponding existing tuple as DEAD and inserts a new tuple (i.e. UPDATE operation = DELETE + INSERT).

This leads to what is called "bloat" - i.e. the table will become bigger, even if the number of records remains the same because of these dead tuples.

From EnterpriseDB², we have:

• When a vacuum process runs, the space occupied by these dead tuples is marked reusable by other tuples.

This is analgous to deleting a file in the file system - the space is merely marked as reusable and nothing is actually deleted. This doesn't matter - the important thing to note is that the space taken by the row becomes available again to the PostgreSQL server for that table!

The key difference between VACUUM and VACUUM FULL is that with a VACUUM FULL, the freed up space also becomes available to the OS! This might be of use if one wishes to perform a backup and one is running low on disk space. From here:

• Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now).

So, VACUUM FULL is analgous to defragging a disk file (consider a disk file ≡ PostgreSQL table), from Percona³ we have:

VACUUM FULL is the default option available with a PostgreSQL installation that allows us to REBUILD a table. [Emphasis mine]

However, this rebuild does, require an Access Exclusive Lock, which as the manual points out:

• ACCESS EXCLUSIVE

  Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way. [Emphasis mine]

Obviously, and as you point out, a complete rewrite of the table isn't great for production. The manual even says:

• Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

So, to answer your 2 questions:

Q. 1)

does the vacuum make the space reuseable for the new insert?

Yes, it does, but even more importantly, it doesn't take an Access Exclusive lock. From the documentation, we find that:

Autovacuum workers generally don't block other processes.

Autovacuum is a VACUUM without the FULL and is normally configured in the postgresql.conf file.

VACUUM takes a SHARE UPDATE EXCLUSIVE lock.

Again, from the manual:

This mode protects a table against concurrent schema changes and VACUUM runs.

Or as explained by a knowledgeable PostgreSQL contributor here:

Autovacuum does take a lock on the table, but it is a weak lock which does not interfere with normal operations (SELECT, UPDATE, DELETE) but will interfere with things like adding indexes, or truncating the table.

The EnterpriseDB blog referenced above gives "VACUUM and ANALYZE Best Practice Tips" - personally, I would normally advise an ANALYZE after a VACUUM to update the table's statistics for the optimizer! This article can also help you with decisions as to when you might want to run VACUUM.

Q. 2)

The question as asked in the subject.

Is it possible never to run vacuum full in PostgreSQL

Yes, it is possible to never run it, especially if disk space isn't a worry. It may be worthwhile however, as a complete table rewrite (along with indexes) can have a beneficial impact on query performance - records in physical order can lead to fewer pages requiring scans!

Conclusion.

This is a complex issue and a complete answer would be impossibly long, but I would urge you to read this - a bit dated but a good overview - (and also the pages here, here, here (fill factor) & here).

Of course, you don't want VACUUMing interrupting your production systems at busy times. Deciding on the best strategy is the same as virtually everything else in IT - "It depends!".

There are disk space and I/O load parameters to be taken into account and you will have to arrive at a compromise which best suits all of your stakeholders.

^{1) several9s is an excellent site for all things PostgreSQL.
2) EnterpriseDB is also an excellent site and the largest PostgreSQL company with a large number of core team members and contributors.
3) Percona, although previously a MySQL company, now also have a PostgreSQL distribution, and they sponsor the promising pg_stat_monitor extension (see also here). They are highly regarded.}

Answer 2

It may be worthwhile however, as a complete table rewrite (along with indexes) can have a beneficial impact on query performance - records in physical order can lead to fewer pages requiring scans!

If you are interested in the above, you may want to have a look at the CLUSTER command. https://www.postgresql.org/docs/current/sql-cluster.html

Answer 3

For super users, we can't block vacuum full & only vacuum on the table no going to make the space reusable, we have to use vacuum full to physically remove the dead tuple.