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?
VACUUM make space reusable for new
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
In order to understand the differences between
a basic understanding of PostgreSQL's architecture is required.
From several9s1 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 EnterpriseDB2, 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 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).
VACUUM FULL is analgous to defragging a disk file (consider a disk file ≡ PostgreSQL table), from Percona3 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:
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:
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
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
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!
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 -
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.
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