From: | Durumdara <durumdara(at)gmail(dot)com> |
---|---|
To: | |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Vacuum very big table - how the full vacuum works in background/internally? |
Date: | 2019-11-06 12:51:49 |
Message-ID: | CAEcMXhnsTxejJii4tS_RqgBxNurz_u3YXuRLn5=Zxw0wOhOMtw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Durumdara | 2019-11-06 13:09:42 | Upgrade PGSQL main version without backup/restore all databases? |
Previous Message | Pavel Stehule | 2019-11-06 06:56:50 | Re: PostgreSQL && data types in ESQL/C |