Re: Confirming pg_repack being successful

On Thu, May 9, 2024 at 7:14 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> 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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>

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.

https://www.postgresql.org/docs/current/pgstattuple.html

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.

https://github.com/keithf4/pg_bloat_check

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com