Horton

Re: Vacuum ERRORs out considering freezing dead tuples from before OldestXmin

John Naylor <johncnaylorls@gmail.com>

View thread
From: John Naylor <johncnaylorls@gmail.com>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Pg Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>, Noah Misch <noah@leadboat.com>
Date: 2025-06-17T07:23:42Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Test that vacuum removes tuples older than OldestXmin

  2. Lower minimum maintenance_work_mem to 64kB

  3. Add accidentally omitted test to meson build file

  4. Use DELETE instead of UPDATE to speed up vacuum test

  5. Revert "Test that vacuum removes tuples older than OldestXmin"

  6. Ensure vacuum removes all visibly dead tuples older than OldestXmin

On Mon, Jun 16, 2025 at 9:58 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> Test in attached patch seems to do the job on 32 bit and 64 bit when tested.

Great!

+log_recovery_conflict_waits = true

I don't see this on pg16 -- If this is good to have, maybe worth
calling out in the commit message as a difference?

+# The TIDStore which vacuum uses to store dead items is optimized for its
+# target system. On a 32-bit system, our example requires around 9000 rows to
+# have enough dead items spread out across enough pages to fill the TIDStore
+# and trigger a second round of index vacuuming. We could get away with fewer
+# rows on 64-bit systems, but it doesn't seem worth the special case.

Minor quibble: I wouldn't say it is deliberately optimized (at least
not on local memory) -- it's more of a consequence of pointer-sizes
and the somewhat arbitrary choice to set the slab block sizes to hold
about X number of chunks. For v19, it might be good to hard-code the
block sizes to reduce the possibility of difference and allow a
smaller table.

+my $nrows = 9000;

Running the queries in isolation on an -m32 build shows running 5
index scans, and I found 4000 runs 3 index scans both with and without
asserts. Of course, I'm only using the normal 8kB block sizes. In any
case, 9000 is already a lot less than 200000, so we can go with that
for v17 and v18.

-- 
John Naylor
Amazon Web Services