Dump and insert instead of vacuum

(1) By anonymous on 2022-03-17 10:30:18 [link] [source]

Hello,

I was wondering if the same benefits of vacuuming a database could be achieved by dumping all the data and inserting them into a new database ? Or does inserting lots of new rows might not create an optimal database ?

Asking because I am trying to vacuum a database (about 660GB, WAL enabled) and after more than 60 hours the process is not finished. So I am looking for alternatives to speed up things.

(2) By Adrian Ho (lexfiend) on 2022-03-17 15:46:06 in reply to 1 [link] [source]

Have you tried VACUUM INTO?

(4) By anonymous on 2022-03-19 09:42:19 in reply to 2 [link] [source]

I've just tried your advice and yes it's much faster! Thanks :)

I killed the normal VACUUM, since after 72 hours it was still not finished. Then I tested the VACUUM INTO and it ended in just 23 hours!

(3) By Simon Slavin (slavin) on 2022-03-18 10:52:22 in reply to 1 [source]

The fastest way to reconstruct data is not

CREATE TABLE …;
CREATE INDEX …;
INSERT all your data (by declaring transactions, of course)

it's

CREATE TABLE …;
INSERT all your data (by declaring transactions, of course);
CREATE INDEX …

VACUUM should implement this internally. (I haven't checked the source code.) But if you are experimenting with dumping existing data to a text file, then using that to create a new database, it should help.

Also, you should set journal_mode to OFF while creating the new database, since it's far faster and a partially-constructed database is useless to you.