On Thu, Feb 13, 2025 at 1:33 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > Not seeing it: > > https://reorg.github.io/pg_repack/ > > "Details > Full Table Repacks > > [...] > > pg_repack will only hold an ACCESS EXCLUSIVE lock for a short period > during initial setup (steps 1 and 2 above) and during the final > swap-and-drop phase (steps 6 and 7). For the rest of its time, pg_repack > only needs to hold an ACCESS SHARE lock on the original table, meaning > INSERTs, UPDATEs, and DELETEs may proceed as usual." > > During the ACCESS EXCLUSIVE stages you will not have access. The operations under ACCESS EXCLUSIVE are short-lived. In my benchmarks, the "repack_swap" function finishes within the order of magnitude of milliseconds. The operations seem to be catalogue-only. I'd love someone to confirm this though, because I am not a specialist in C and Postgres extensions. Here is the code if you want to have a look: https://github.com/reorg/pg_repack/blob/306b0d4f7f86e807498ac00baec89ecd33411398/lib/repack.c#L843 What I am after is the same, but I seek a deeper understanding of what it does, and why it does it. For example, it swaps relfilenode. Why? > Not only that with pg_repack you are not changing the table definition, > whereas in your case you are introducing a new constraint and associated > index. Correct, but I am not using pg_repack. I have cloned the table using my own tool. I'm citing pg_repack because it does perform a table swap (a bloated table is swapped by a new non-bloated table). Given that I know pg_repack works well in large databases, it has to follow that the approach they have to swapping the tables is robust. > Do you have a dev setup where you can test alternatives with a test > sample of data? Do you mean alternatives to table-cloning-and-swapping? Regards, - Marcelo
