Thread

1. Re: Adding REPACK [concurrently]

   David Klika <david.klika@atlas.cz> — 2025-12-04T15:17:51Z

   Hello
   
   Great to hear about this feature.
   
   You speak about table rewrite (suppose a whole-table rewrite). I would 
   like to share idea of an alternative approach that also takes into 
   account amount of WAL generated during the operation. Applicable to 
   non-clustered case only.
   
   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.
   
   Example:
   
   _ stands for filled enough blocks
   
   D stands for blocks with (many) dead tuples
   
   123456789
   ___DD____
   
   Left scan identifies page #4 and tuples from the right scan (page #9) 
   are moved here. The same with tuples from #8 to #5. Two pages from the 
   data file are trimmed and (only) pages #4 and #5 are written in WAL, 
   others are untouched.
   
   Regards
   David
   
   
   
   
   

2. Re: Adding REPACK [concurrently]

   Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-12-04T15:43:44Z

   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.
   
   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.
   
   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.
   
   -- 
   Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
   
   
   
   

3. Re: Adding REPACK [concurrently]

   Marcos Pegoraro <marcos@f10.com.br> — 2025-12-04T17:47:56Z

   Em qui., 4 de dez. de 2025 às 12:43, Álvaro Herrera <alvherre@alvh.no-ip.org>
   escreveu:
   
   > 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.
   
   
   Not necessarily. I have some tables where I like to use CLUSTER
   every 2 or 3 months, to reorganize the data based on an index
   and consequently load fewer pages with each call. These tables
   don't have more than 2 or 3% of dead records, but they are quite
   disorganized from the point of view of that index, since the
   inserted and updated records don't follow the order I determined.
   
   
   regards
   Marcos
   

4. Re: Adding REPACK [concurrently]

   Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-12-06T10:58:36Z

   Hello,
   
   On 2025-Dec-04, Marcos Pegoraro wrote:
   
   > Em qui., 4 de dez. de 2025 às 12:43, Álvaro Herrera <alvherre@alvh.no-ip.org>
   > escreveu:
   > 
   > > So if you're trying to do this, the number of problematic pages must
   > > be large.
   > 
   > Not necessarily. I have some tables where I like to use CLUSTER every
   > 2 or 3 months, to reorganize the data based on an index and
   > consequently load fewer pages with each call. These tables don't have
   > more than 2 or 3% of dead records, but they are quite disorganized
   > from the point of view of that index, since the inserted and updated
   > records don't follow the order I determined.
   
   I don't understand what does this have to do with what David was
   proposing.  I mean, you're right: if all you want is to CLUSTER, you may
   not have an enormous number of pages to get rid of.  But how can you use
   the technique he proposes to deal with reordering tuples?  If you just
   move the tuples from the end of the table to where some random hole has
   appeared, you've not clustered the table at all.
   
   -- 
   Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
   "People get annoyed when you try to debug them."  (Larry Wall)
   
   
   
   

5. Re: Adding REPACK [concurrently]

   David Klika <david.klika@atlas.cz> — 2025-12-08T09:13:20Z

   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