"Vacuum Analyze" actually performs 2 entirely different tasks.
- Vacuum is used to free up space occupied by dead tuples/rows.
- Analyze is used to analyze the contents of the table, which in turn helps planner to create better query plans.
"Vacuum Analyze" is a manual cleanup operation and it is usually done once a week or month, depending on the frequency of update/deletes performed on the database. This operation can be performed on specific tables or on the whole database. This takes somewhere from 30 mins to even days depending on the size of database and how often do you perform this operation.
When to use VACUUM FULL and ANALYZE:
If your database is taking too much space and there is no space left for your OS to perform any other operation then you need to do a VACUUM FULL, it is also recommend to add the ANALYZE option to it. If you have a high write frequency database, then i would recommend to perform this operation at least once every 3-6 months.
VACUUM(FULL, ANALYZE, VERBOSE);
If you cant lock the whole database and you just need to free up space taken by a table Which does alot of updates/deletes. Then go for VACUUM FULL on specific table
VACUUM FULL VERBOSE your_table_name;
If you are facing a problem, where your queries become slower over time, i.e if you run EXPLAIN on a query and sometime it uses sequential scan and the same query with different parameters uses index scan. Then this means that your table is not completely Analyzed. Analyzing can be performed on the whole database or on specific table. The database or table does not get locked during this operation and your queries will perform better after this operation.
ANALYZE VERBOSE your_table_name
Auto Analyze:
Although you might never need to manually ANALYZE a database, as this is automatically done by the auto analyze deamon which runs in the background and analyzes tables which surpass a certain threshold of updates/deletes which is by default 10% of the table size. But on large tables this threshold is never met and the query becomes slow even on 5% threshold. Therefore ANALYZE should be manually performed along with VACUUM FULL on regular intervals.
Auto Vacuum:
Auto Vacuum is another deamon which runs in the background and Vacuum tables without locking them. Auto Vacuum also runs Auto Analyze with it, therefore auto vacuum will also auto analyze a table. The condition that needs to be met in order for the auto vacuum to perform the operation on a table is by default set at 20% of updates/deletes of the size of the table.
Example:
A table of 40 Million rows, the auto vacuum will run when the table will receive 8 Million updates or deletes. Similarly the table needs to receive 4 Million updates or deletes in order for the auto analyze to start. Mostly tables of such size would become slow before this threshold is received, due to which manually VACUUM FULL ANALYZE is recommend on a regular basis.
Answer from Omer Farooq on Stack OverflowAbout 99% articles i read states that VACUUM FULL is a bad practise and can even results into slower DB.
More recommended way articles states is to VACUUM ANALYZE and REINDEX afterwards.
For sure, let's consider for a while that AUTOVACUUM is not enough for some reason, for example - optimal settings are not tuned up at the moment.
What i found is that during our performance tests on regular basis - VACUUM FULL cause better throughput due to much lower and at the same time with better latency - disk IO.
My theory is that VACUUM FULL cause OS to perform more sequental reads rather than random reads. Also, maybe, it allows Page cache to be more saturated due to lower tables size.
Can someone please point me the right way where and what can i do to investigate the real cause of VACUUM FULL ANALYZE performance gain comparing to VACUUM ANALYZE + REINDEX ?
Thanks in advance !
PG 14 in production.
UPDATE
Found an answer:
https://severalnines.com/blog/tuning-io-operations-postgresql/
section "VACUUM FULL".
The reason is disk io of course, but mainly - such operations like seq scan traverse dead tuples too. Maybe it should check if the tuple is dead or the space was reused for new records, btw, if you read the article - the query time (synthetic, doesn't include indexes) dropped from 1.9s to 1.4s after VACUUM FULL ANALYZE comparing to VACUUM ANALYZE.
sql - How to efficiently vacuum analyze tables in Postgres - Stack Overflow
postgresql - I need to run VACUUM FULL with no available disk space - Database Administrators Stack Exchange
postgresql - VACUUM FREEZE vs. VACUUM FULL - Database Administrators Stack Exchange
sql - PostgreSQL: VACUUM FULL duration estimation - Stack Overflow
"Vacuum Analyze" actually performs 2 entirely different tasks.
- Vacuum is used to free up space occupied by dead tuples/rows.
- Analyze is used to analyze the contents of the table, which in turn helps planner to create better query plans.
"Vacuum Analyze" is a manual cleanup operation and it is usually done once a week or month, depending on the frequency of update/deletes performed on the database. This operation can be performed on specific tables or on the whole database. This takes somewhere from 30 mins to even days depending on the size of database and how often do you perform this operation.
When to use VACUUM FULL and ANALYZE:
If your database is taking too much space and there is no space left for your OS to perform any other operation then you need to do a VACUUM FULL, it is also recommend to add the ANALYZE option to it. If you have a high write frequency database, then i would recommend to perform this operation at least once every 3-6 months.
VACUUM(FULL, ANALYZE, VERBOSE);
If you cant lock the whole database and you just need to free up space taken by a table Which does alot of updates/deletes. Then go for VACUUM FULL on specific table
VACUUM FULL VERBOSE your_table_name;
If you are facing a problem, where your queries become slower over time, i.e if you run EXPLAIN on a query and sometime it uses sequential scan and the same query with different parameters uses index scan. Then this means that your table is not completely Analyzed. Analyzing can be performed on the whole database or on specific table. The database or table does not get locked during this operation and your queries will perform better after this operation.
ANALYZE VERBOSE your_table_name
Auto Analyze:
Although you might never need to manually ANALYZE a database, as this is automatically done by the auto analyze deamon which runs in the background and analyzes tables which surpass a certain threshold of updates/deletes which is by default 10% of the table size. But on large tables this threshold is never met and the query becomes slow even on 5% threshold. Therefore ANALYZE should be manually performed along with VACUUM FULL on regular intervals.
Auto Vacuum:
Auto Vacuum is another deamon which runs in the background and Vacuum tables without locking them. Auto Vacuum also runs Auto Analyze with it, therefore auto vacuum will also auto analyze a table. The condition that needs to be met in order for the auto vacuum to perform the operation on a table is by default set at 20% of updates/deletes of the size of the table.
Example:
A table of 40 Million rows, the auto vacuum will run when the table will receive 8 Million updates or deletes. Similarly the table needs to receive 4 Million updates or deletes in order for the auto analyze to start. Mostly tables of such size would become slow before this threshold is received, due to which manually VACUUM FULL ANALYZE is recommend on a regular basis.
You can run analyze only, no need to run vacuum also. The syntax is:
ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]
In the documentation it states:
ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.
You can find more information here:
http://www.postgresql.org/docs/9.4/static/sql-analyze.html
https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT
Does a vacuum analyze lock tables ?
No, it's the "FULL VACUUM" command that locks tables.
Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.
1 Backup all of the databases on your postgresql server
You will want to backup all of your databases to a partition that has enough space. If you were on Linux, you can use gzip to further compress the backup to save space
su - postgres
pg_dumpall | gzip -9 > /some/partition/all.dbs.out.gz
2 Backup your configuration files
cp /path/to/postgresql/data_directory/*.conf /some/partition/
3 Stop Postgresql
pg_ctl -D /path/to/postgresql/data_directory stop
4 erase the contents of the data directory
rm -Rf /path/to/postgresql/data_directory/*
5 Run initdb to reinitalize your data directory
initdb -D /path/to/postgresql/data_directory
6 Restore configuration files
cp /some/partition/*.conf /path/to/postgresql/data_directory/*.conf
7 Start Postgresql
pg_ctl -D /path/to/postgresql/data_directory start
8 Restore the dump of all the databases you made
gunzip -c /some/partition/all.dbs.out.gz | psql
Method with little to no downtime (minimal user-impact)
NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though.
CAUTION (As noted in the comments by @erny):
Note that high CPU load due to I/O operations may be expected.
You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of exclusive locks. But only on the table you are vacuuming. So all that will happen is that client queries will simply wait for the lock to be acquired if they access the table in question. You don't need to close existing connections.
One thing to be aware of though, is that moving the table and the vacuum full will themselves need to wait for an exclusive lock first!
First, you obviously need some additional storage. As Stéphane mentions in the comments, this needs to be at least twice as big as the table in question as VACUUM FULL does a full copy. If you are lucky and can dynamically add a disk to the machine, do that. In the worst case you can just attach an USB disk (risky and slow though)!
Next, mount the new device and make it available as tablespace:
CREATE TABLESPACE tempspace LOCATION '/path/to/new/folder';
You can list the tablespaces easily using:
\db
Double-check the current tablespace of your table (you need to know where to move it back to):
SELECT tablespace FROM pg_tables WHERE tablename = 'mytable';
If it's NULL, it will be in the default tablespace:
SHOW default_tablespace;
If that is NULL as well, it will likely be pg_default (check the official docs in case it's changed).
Now move the table over:
ALTER TABLE mytable SET TABLESPACE tempspace;
COMMIT; -- if autocommit is off
Vacuum it:
VACUUM FULL mytable;
Move it back:
-- assuming you are using the defaults, the tablespace will be "pg_default".
-- Otherwise use the value from the SELECT we did earlier.
ALTER TABLE mytable SET TABLESPACE pg_default;
COMMIT; -- if autocommit is off
Remove the temporary space:
DROP TABLESPACE tempspace;
Here's a short concise answer.
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 freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear.
For instance, the template0 database is frozen because it never changes (by default you cannot connect to it.)
Every so often the autovacuum daemon will check a database and its tables to see what needs to be vacuumed. If a table is vacuum freeze'd and then never updated, the autovacuum daemon will simply pass it by. Also the "wrap around" protection in postgresql will never kick in on that table either.
tl;dr freezing marks a table as not needing any autovac maintenance. The next update will unfreeze it.
To further explain what Jayadevan wrote.
The way that Postgres works with transactions, and to keep track of visible data is by comparing internal Transaction IDs. However, since those transactions are a 32-bit integer sooner or later they will wrap around, and therefore the new transaction will look like it was made in the past (and thus be visible in a current transaction while it shouldn't), while older transactions will look like they are being done in the future (and since the future doesn't exist yet, that data will not be visible any more).
What Postgres do in order to counter that problem is to assign each row that is old enough to be at risk of suffer from this wraparound a special transaction id that always is older than every transaction. You could see it as if valid transaction ids ranges from 0 to 2147483647, it will set the transaction id for all current rows to -1.
However, since the vacuum is basically to mark empty space for re-use, it only works on data pages that have been changed.
What VACUUM FREEZE do is basically freeze the transaction id for all pages no matter if they have been modified or not, so that all the current rows will be seen as old for all new transactions.
However, as of version 8.2 VACUUM FREEZE have been deprecated and should not be used. Instead there is the parameters vacuum_freeze_table_age and autovacuum_freeze_max_age that specifies how many transactions can occur before a complete scan is being done on the table (effectively do an internal VACUUM FREEZE on the table).