CODEWITHSUNDEEP
postgresql
mitchus
mitchus

Reputation: 4877

Vacuum full faster?

I notice that VACUUM FULL can take a very long time on Postgres.

Is there a way to speed up the process? e.g. by specifying that the DB is in maintenance mode and hence does not need to be available to any other request during the vacuuming?

Upvotes: 2

Views: 6185

Answers (2)

cetver
cetver

Reputation: 11829

This is a very general question, the answer will be the same:

Run VACUUM FULL VERBOSE [table] shows why slow

Most important parameter for vacuum is maintenance_work_mem try to increase it.

List of memory parameters: https://www.postgresql.org/docs/12/runtime-config-resource.html

Vacuum in depth:

Also you can try https://reorg.github.io/pg_repack/ - vacuum without exclusively locks

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246338

You could use pg_prewarm to load the table into RAM before you run VACUUM (FULL) on it.

Also, see that maintenance_work_mem is as big as possible; that will speed up the creation of indexes.

Both these things will help, but there is no magic to make it really fast.

Instead, you could try something like:

BEGIN;
LOCK oldtab IN SHARE MODE;  -- blocks modifications
CREATE TABLE newtab (LIKE oldtab INCLUDING ALL);
INSERT INTO newtab SELECT * FROM oldtab;
/* create indexes and constraints as appropriate */
DROP TABLE oldtab;
ALTER TABLE newtab RENAME TO oldtab;
COMMIT;

That will block SELECT statements only for a short while.

Upvotes: 3

Related Questions