Vacuum very big table - how the full vacuum works in background/internally?

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Vacuum very big table - how the full vacuum works in background/internally?
Date: 2019-10-17 15:09:51
Message-ID: CAEcMXhkZSKQ5NYv4-ON4e6YJXsAZeVfxvKyrxNPP3Ejo2QbuMg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

We stored some binaries in largeobjects.
Because of that the table size now 80 GB.
We deleted 80% of records (lo_unlink), and autovacuum reclaimed space for
new elements. So the table don't grow anymore, but we have to get more
space in this server.

We can delete 99% of these records, but for really reclaim free space in
HDD we need to run full vacuum.

For this operation we need to know how the PGSQL vacuum works in the
background.

Some of admins said to us that:
a.) It copies the table fully (minium 66 GB space needed).
b.) Then it deletes the unneeded data.
In this case we need extra empty space in a temporary period, and more time
(the copy of 66 GB could be slow in SSD too).

The DBISAM/ElevateDB, ZIP file deletion, VirtualBOX VDI Compact works as:
a.) It locks the original file/table.
b.) Copy remaining elements to new (first empty) file.
c.) Then it removes old file, and use new.
In this case we need only very limited empty space (3-4 GB), and the
operation is much faster (because of less HDD operation).

Please help me, how the PGSQL full vacuum works internally? (1., 2. case,
or something else)

How we (and the clients) prepare to this operation?
We must know it to avoid disk out problems, and too much off-time.

Thank you for your help!

Best regards
dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin Pryzby 2019-10-17 15:30:06 Re: v12 and pg_restore -f-
Previous Message Adrian Klaver 2019-10-17 14:41:01 Re: Changing PK on replicated database