On 7/10/24 11:49, David Rowley wrote: > On Tue, 9 Jul 2024 at 16:58, Ahmed Yarub Hani Al Nuaimi > <ahmedyarubh...@gmail.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
