IronPort-PHdr: A9a23:/L2cahQ6UBQmpZCBAXYtfPkUrNpsosSZAWYlg6HPa5pwe6iut67vI
 FbYra00ygOSAcODs7kd1bCJ++C4ACpcus3H6CtDOLV3FDY7yuwu1zQ6B8CEDUCpZNXLVAcdW
 OlkahpO0kr/D3JoHt3jbUbZuHy44G1aMBz+MQ1oOra9QdaK3Iy42O+o5pLcfRhDiiajbrNuN
 hW2qhjautULjYd4Jas8xBTErmFUd+hKym9kOFOekwj+68yt+JJv7zlct+gu+sNOX6j2Zbg0Q
 LJdAD87L207+crnuAXMQgeI+nYSSHgdnABSAwjC4hH6X5PxvTX/u+FgxiSUPtD4QK4tVzi64
 KllUBDnhD4ZOjA27W/bksN/gr5VrxKnvBx/3pXbYJqJO/pkf6PSZs0VSGxcVctMTSNBA4e8b
 4wSD+odOOtTsofwq0cSoRa4GAKhAv7kxD1ViX/sxaA00OovHg7I0wI9AdwDrnrUotX3O6gOX
 u6417XIwDbZYv9KxTvx9orFfxY8qv+MR7Jwds/RxFEhGQzfkFWQqJHqNC6Q2OQJsmib7PBvX
 v+ohmE6sQ5xvDmvydo2honKgoIVy1PE+j9izYkvOd24TFR0YcW+HJRMtiCXLo17Sd4tTG90o
 ig10KEGuYKlcygQzpQq3xDSZviGfoWU4x/uVuScLCt7iX9qeLyzmwi+/FSgxOD8VMS63lZHo
 Cpbn9fMuX4A1hje5MiIRPdg40qsxDaC2g7V5+pZIk40jbLWJpEgz7IqiJYesUTOEjXolEnoj
 qKabEcp9+iw5+njbbjqvIKQO5Jqhg3kMKkihtazDOQ3PwUIQmOV4/6z1Kf58k38WLhKi/o2n
 bTHv53CPsQbo7K5AxdS0oY+9xazFzem38ocnXkANF9FdxeHg5HyO1HLPPD4Feqzj0m2nDh32
 f/GOKHhAo/QLnjGirvuYbd851ZdyAo1099f+4pZB74ALf7pREP8tNzVAgUnPwCqwevrEtdw2
 4MGVWKKGKCZMafSsVGS5uIoJumBfJIatyzjK/gk+vHil3w5mVscfamqx5Qac2q3HuhiI0qDe
 nrgmMsOEGYSsgokUOPqkEGCUSJUZ3uqRa084i87CIK4AofZXICinqeM0z2/Hp1IZmBKEFaME
 XDxe4WFQ/cAciWSItVukjAcT7iuV5ch1Q2ytA/907doNOrU9zYEupPjz9d6+ffTlQ0s+jNoF
 ciSyX2NQ3lunmwUXz82wLx/oUtlx1eB0Kh3mfpYGcZO5/NPUwc6K4DTz/ZhC9/oXgLBf8uJS
 Va9T9WpBDE+VNMxw9sUb0lhHNWiiwjP3zC2DL8Ni7yLGJs0/7rZ33jwJ8Zy0XfG1K0nj1knQ
 ctAL2mmibR/9gfNGo7EiEqZl6OweqQd2C7B7nuMzW2LvE1ASg5/Tb3FXWwDZkvRtdn5/kzCT
 6S3CbQjKwdBydSNJbdJatL3l1VGX+zuONDfY2Kqh2ewHgyExquLbIrxZ2Ud0z/dB1YfkwAK+
 XaKLw8+Bj2uo27GFjxhCUrvY1/w8el5sH67VEg0wBqRYkxu1ru1+xoVhfiGRP8J2LILpjshp
 y1yHFmmx93WEcaPpxJ9fKVAZtMw+FZK2XrEuAx+P5ygKrhviEYRfQt5oU3gywl6BIMT2fQt+
 dou0gNoLuq43UlbeimRlcT5PqPXJ3Po8Timb7DWnFfZldeOvKAV56JrhU/kuVSFH1Qj/z1E1
 9hW2jPI6pzUDQZUUpbwUU8f/gR+rqqcaTtrtNCc7mFlLaTh6myK4NkuHuZwkn6d
IronPort-Data: A9a23:+gLsk65p25JMsAVn5LN/9gxRtADGchMFZxGqfqrLsTDasY5as4F+v
 jdLCGiBOaqPMDD0KIt2Pdu18BkOsMKAytE3GQE6rSsxZn8b8sCt6fZ1j6vT04F+CuWZESqLO
 u1HMoGowPgcFyGa/lH3dOG49xGQ7InQLpLkEunIJyttcgFtTSYlmHpLlvUw6mJSqYHR7zil5
 5Wo+6UzBHf/g2QpazhMu/rawP9SlK2aVA0w7wFWic9j4we2e0k9VPo3Oay3Jn3kdYhYdsbSb
 /rD1ryw4lTC9B4rDN6/+p6jGqHdauO60aCm0xK6aoD66vRwjnVaPpUTaJLwXXxqZwChxLid/
 v0W7MDtFl15VkH7sL91vxFwS0mSNEDdkVPNCSDXXce7lyUqf5ZwqhnH4Y5f0YAwo45K7W9yG
 fMwciEOcRaB19uPn5n8ZcRDp5QkJ/P1M9ZK0p1g5Wmx4fcOTpWGWKDW/YYBmjw9gNxUAPOYb
 NhxhThHMEqGOUASfA1NV9RhwY9EhVGmG9FcgF6cu6s7pWvXzQ94+LHxNNPOPNeYLSlQth/B/
 zKXoTykXnn2MvSW9iTd6EKdgNaV3ifrVpJDMJ6bte5l1Qj7Kms7TUd+uUGAifC9lE6zQchSA
 0UQ5Cpoq6939VTtScPyNzWxoFaPrxQRQcZdGKsz9GmlyKvd5UCEAGkaQyRaYdgnqN4eSjkj1
 1vPlNTsbRRqsb2cSHab97SdqTqjNDU9ImoLZCtCRgwAi/H7r5oqilfPR91gHbSuptn0Ai3rh
 SiNtzQxnbgIlMpN0L+0lXjdhDiEtorISgo8oAPaQgqYAhhRONDjPdH1rwKBsrAfce51U2W8g
 ZTNoODGhMhmMH1HvHblrDkldF1x28u4DQ==
IronPort-HdrOrdr: A9a23:Q//CiKwcJ6cAOvFCtyCpKrPwL71zdoMgy1knxilNoNJuEvBw5P
 re/sjzsiWE7gr5OUtQ/uxoV5PsfZqxz+8R3WBVB8bHYOCEggeVxeNZh7cKqgeIc0bDH6xmpM
 RdmsNFZuEYY2IasS+32maFL+o=
Message-ID: <0ac6540e-c9f7-4918-913b-21288f6436cf@atlas.cz>
Date: Mon, 8 Dec 2025 10:13:20 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Adding REPACK [concurrently]
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>
Cc: ah@cybertec.at, jian.universality@gmail.com,
 pgsql-hackers@lists.postgresql.org, mihailnikalayeu@gmail.com, rob@xzilla.net
References: <202512041531.4yz4szwnfsqk@alvherre.pgsql>
Content-Language: cs
From: David Klika <david.klika@atlas.cz>
In-Reply-To: <202512041531.4yz4szwnfsqk@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/0ac6540e-c9f7-4918-913b-21288f6436cf%40atlas.cz>
Precedence: bulk

Hello Alvaro

Thank you for the detailed analysis.

Dne 04.12.2025 v 16:43 Álvaro Herrera napsal(a):
> Hello David,
>
> Thanks for your interest in this.
>
> On 2025-Dec-04, David Klika wrote:
>
>> Let's consider a large table where 80% blocks are fine (filled enough by
>> live tuples). The table could be scanned from the beginning (left side) to
>> identify "not enough filled" blocks and also from the end (right side) to
>> process live tuples by moving them to the blocks identified by the left side
>> scan. The work is over when both scan reaches the same position.
> If you only have a small number of pages that have this problem, then
> you don't actually need to do anything -- the pages will be marked free
> by regular vacuuming, and future inserts or updates can make use of
> those pages.  It's not a problem to have a small number of pages in
> empty state for some time.
>
> So if you're trying to do this, the number of problematic pages must be
> large.

I agree, I had in mind about 20-40% of the table that could have tenths 
of GB.

> Now, the issue with what you propose is that you need to make either the
> old tuples or the new tuples visible to concurrent transactions.  If at
> any point they are both visible, or none of them is visible, then you
> have potentially corrupted the results that would be obtained by a query
> that's scanning the table and halfway through.

When performing a tuple movement from a (right) page to a (left) page, 
both of pages must be hold in shared buffers. I suppose the other 
processes scanning the table also access the table data through the 
shared buffers so the movement could be handled at this level. If the 
tuple movement does not change its xid, it wouldn't even have to be in 
conflict with other transactions that locked/modified the tuple (in 
buffer cache again, just changing the physical location). Looks like 
something dirty...

> The other point is that you need to keep indexes updated.  That is, you
> need to make the indexes point to both the old and new, until you remove
> the old tuples from the table, then remove those index pointers.
> This process bloats the indexes, which is not insignificant, considering
> that the number of tuples to process is large.  If there are several
> indexes, this makes your process take even longer.
>
> You can fix the concurrency problem by holding a lock on the table that
> ensures nobody is reading the table until you've finished.  But we don't
> want to have to hold such a lock for long!  And we already established
> that the number of pages to check is large, which means you're going to
> work for a long time.
> So, I'm not really sure that it's practical to implement what you
> suggest.

I agree. Proposed tuple shuffle might work better compared to the 
current VACUUM FULL (i.e. blocking non-clustered maintenance) but I 
understand that you prefer an universal method of data files maintenance 
(the concurrent variant will be amazing).

Regards David