On Thu, May 9, 2024 at 7:14 PM Wells Oliver <wells.oliver@xxxxxxxxx> wrote:
Is there something definitive one can do to confirm the success of pg_repack? It's my first real usage of it, and I did -E DEBUG, and the final output lines look like so:NOTICE: Waiting for 1 transactions to finish. First PID: 13560
NOTICE: Waiting for 1 transactions to finish. First PID: 13560
DEBUG: ---- swap ----
DEBUG: query failed: ERROR: canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE stats.pitches IN ACCESS EXCLUSIVE MODE
DEBUG: query failed: ERROR: canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE stats.pitches IN ACCESS EXCLUSIVE MODE
DEBUG: query failed: ERROR: canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE stats.pitches IN ACCESS EXCLUSIVE MODE
DEBUG: query failed: ERROR: canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE stats.pitches IN ACCESS EXCLUSIVE MODE
DEBUG: query failed: ERROR: canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE stats.pitches IN ACCESS EXCLUSIVE MODE
DEBUG: query failed: ERROR: canceling statement due to statement timeout
DETAIL: query was: LOCK TABLE stats.pitches IN ACCESS EXCLUSIVE MODE
DEBUG: ---- drop ----
DEBUG: ---- analyze ----
DEBUG: Disconnecting worker 0.
DEBUG: Disconnecting worker 1.
DEBUG: Disconnecting worker 2.
DEBUG: Disconnecting worker 3.
DEBUG: Disconnecting worker 4.
DEBUG: Disconnecting worker 5.
DEBUG: Disconnecting worker 6.
DEBUG: Disconnecting worker 7.I... think it worked, the n_dead_tup number is lower, which is what I was expecting. I just wondered if there might be something definitive here.Thanks.--Wells Oliver
wells.oliver@xxxxxxxxx
Normal vacuums should be taking care of n_dead_tuple. If they're not, pg_repack isn't going to help with that either because it means a transaction is open and holding them. What repack more typically helps with is recovering free, re-usable space that cleaning up the dead tuples leaves behind. To tell if that's been successful a simple comparison of the table size before and after is the easiest thing to check. Or you can use the "pgstattuple" contrib module to see the free space.
Note that you'd have to check each individual index as well as any TOAST tables to get a full picture of the free space cleanup. I wrote a tool that uses pgstattuple but fully scans the given table(s) and gives easier to read output.
