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. David [1] https://www.postgresql.org/message-id/flat/CAFj8pRDNDOrg90hLMmbo_hiWpgBm%2B73gmWMRUHRkTKwrGnvYJQ%40mail.gmail.com#cc4f8d730d2c5203f53c50260053fec5 [2] https://www.postgresql.org/message-id/flat/CANTTaev-LdgYj4uZoy67catS5SF5u_X-dTHiLH7OKwU6Gv3MFA%40mail.gmail.com
