Re: why there is not VACUUM FULL CONCURRENTLY?

On 2025-01-15 We 11:13 AM, Robert Treat wrote:
> On Mon, Jan 13, 2025 at 8:56 AM Michael Banck <mbanck(at)gmx(dot)net> wrote:
>> Hi,
>> On Sat, Jan 11, 2025 at 09:01:54AM -0500, Andrew Dunstan wrote:
>>> On 2025-01-09 Th 8:35 AM, Alvaro Herrera wrote:
>>>> Maybe we should have a new toplevel command. Some ideas that have been
>>>> thrown around:
>>>>
>>>> - RETABLE (it's like REINDEX, but for tables)
>>>> - ALTER TABLE <tab> SQUEEZE
>>>> - SQUEEZE <table>
>>>> - VACUUM (SQUEEZE)
>>>> - VACUUM (COMPACT)
>>>> - MAINTAIN <tab> COMPACT
>>>> - MAINTAIN <tab> SQUEEZE
>> I don't like any of them a lot :-/
>>
> Agreed, though I do believe there would be a positive gain from
> eliminating the overloaded CLUSTER term.
>
>>> COMPACT tablename ...
>> That sounds like it would compress content rather than just rewrite it
>> normally to get rid of bloat.
>>
>> I think REORG (or REPACK, but that has not history elsewhere) would fit
>> best, we don't need to emulate the myriad of DB2 options...
>>
> I would like REPACK if I didn't believe it would lead to confusion
> with pg_repack (which, afaict, seems to have better performance
> characteristics, so will probably hang around).
>
> Actually, I wonder if we are too focused on the idea this is a
> vaccum/bloat related tool. The original idea behind CLUSTER was not
> related to vacuum or bloat management, but performance. There are
> other reasons to want to rewrite a table as well (think dropped
> columns or new column defaults). Is ALTER TABLE <table> REWRITE an
> option? Current needed options would be for clustering or running
> concurrently, but even without those options sometimes you just want
> to rewrite the table, and this is probably the most straightforward
> than making something up.
>
>

I really don't like any of the ALTER TABLE variants, because that's
about changing the table's definition, and this operation doesn't do
that. I could live with REORG as a top level verb if you don't like COMPACT.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com