We solved it. The VACUUM full finished fast on pg_largeobject, because we deleted 98% of big largeobject (ours) before. And it worked as zip deletion - it created a new file and copied only living records, which was fast (3 GB vs. 80 GB).
Thanks
dd
Luca Ferrari <fluca1978@gmail.com> ezt írta (időpont: 2019. okt. 17., Cs, 17:43):
On Thu, Oct 17, 2019 at 5:10 PM Durumdara <durumdara@gmail.com> wrote: > Please help me, how the PGSQL full vacuum works internally? (1., 2. case, or something else) >
As far as I know a VACUUM FULL will rewrite the whole table without inserting (and therefore deleting later) not visible tuples. I would be quite surprised of it duplicating the table and removing after the tuples. Surely it is a very invasive command that locks the table and requires I/O.
> How we (and the clients) prepare to this operation? > We must know it to avoid disk out problems, and too much off-time.
The best guess is that you are going to need almost the double of the table size. Since you said that autovacuum is preventing the table to grow, it could mean all the reclaimed space has been consumed by other tuples, so I'm not sure vacuum full can provide you space. Have you looked at pg_stat_user_tables to see the dead tuples fraction?
