From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Ahmed Yarub Hani Al Nuaimi <ahmedyarubhani(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Lock-free compaction. Why not? |
Date: | 2024-07-18 18:07:18 |
Message-ID: | CA+TgmoZMOfX5-dTBvPkSX+bOejxYpbnCr5AHo6Tty7km-yd0Pw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Jul 18, 2024 at 7:08 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Wed, 10 Jul 2024 at 22:58, Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> > IIRC long time ago VACUUM FULL actually worked in a similar way, i.e. by
> > moving rows around. I'm not sure if it did the lock-free thing as
> > proposed here (probably not), but I guess at least some of the reasons
> > why it was replaced by CLUSTER would still apply to this new thing.
>
> Yeah, that changed in 9.0. The old version still obtained an AEL on the table.
>
> I think the primary issue with the old way was index bloat wasn't
> fixed. The release notes for 9.0 do claim the CLUSTER method "is
> substantially faster in most cases", however, I imagine there are
> plenty of cases where it wouldn't be. e.g, it's hard to imagine
> rewriting the entire 1TB table and indexes is cheaper than moving 1
> row out of place row.
The other thing I remember besides index bloat is that it was
crushingly slow. My memory is pretty fuzzy after this long, but I feel
like it was on the order of minutes to do VACUUM FULL when you could
have done CLUSTER in seconds -- and then on top of the long wait you
often ended up using more disk space at the end than you had at the
beginning due to the index bloat. I remember being surprised by the
decision to remove it entirely, but it sure was painful to use.
--
Robert Haas
EDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | James Coleman | 2024-07-18 18:10:12 | Seq scan instead of index scan querying single row from primary key on large table |
Previous Message | Tom Lane | 2024-07-18 17:58:00 | Re: make pg_ctl more friendly |