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

Hello!

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(at)gmail(dot)com> ezt írta (időpont: 2019. okt. 17., Cs,
17:43):

> On Thu, Oct 17, 2019 at 5:10 PM Durumdara <durumdara(at)gmail(dot)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?
>
> Luca
>