Re: Lock-free compaction. Why not?

On 7/10/24 11:49, David Rowley wrote:
> On Tue, 9 Jul 2024 at 16:58, Ahmed Yarub Hani Al Nuaimi
> <ahmedyarubhani(at)gmail(dot)com> wrote:
>> The thing is, after reading the code a million times, I still don't understand why lock-free (or minimum locking) is such a big problem! Is it that hard to lazily move tuples from one page to the other after defragmenting it lazily?
>
> I think there are a few things to think about. You may have thought of
> some of these already.
>
> 1. moving rows could cause deadlocking issues. Users might struggle to
> accept that some background process is causing their transaction to
> rollback.
> 2. transaction size: How large to make the batches of tuples to move
> at once? One transaction sounds much more prone to deadlocking.
> 3. xid consumption. Doing lots of small transactions to move tuples
> could consume lots of xids.
> 4. moving tuples around to other pages needs indexes to be updated and
> could cause index bloat.
>
> For #1, maybe there's something that can be done to ensure it's always
> vacuum that's the deadlock victim.
>
> You might be interested in [1]. There's also an older discussion in
> [2] that you might find interesting.
>

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.

Maybe it's a good trade off for some use cases (after all, people do
that using pg_repack/pg_squeeze/... so it clearly has value for them),
but it'd be a bit unfortunate to rediscover those old issues later.

The cluster vacuum was introduced by commit 946cf229e89 in 2010, and
then the "inplace" variant was removed by 0a469c87692 shortly after. I
haven't looked for the threads discussing those changes, but I guess it
should not be hard to find in the archives.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company