Opportunistically pruning page before update

From: James Coleman <jtc331(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Andres Freund <andres(at)anarazel(dot)de>, Peter Geoghegan <pg(at)bowt(dot)ie>
Subject: Opportunistically pruning page before update
Date: 2023-06-21 12:51:39
Message-ID: CAAaqYe_m2FrwtdSYpgeS-x7oBghsUSs8KUvJdm6S78_u7GJwxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

While at PGCon I was chatting with Andres (and I think Peter G. and a
few others who I can't remember at the moment, apologies) and Andres
noted that while we opportunistically prune a page when inserting a
tuple (before deciding we need a new page) we don't do the same for
updates.

Attached is a patch series to do the following:

0001: Make it possible to call heap_page_prune_opt already holding an
exclusive lock on the buffer.
0002: Opportunistically prune pages on update when the current tuple's
page has no free space. If this frees up enough space, then we
continue to put the new tuple on that page; if not, then we take the
existing code path and get a new page.

One would plausibly expect the following improvements:
- Reduced table bloat
- Increased HOT update rate
- Improved performance on updates

I started to work on benchmarking this, but haven't had time to devote
properly to that, so I'm wondering if there's anyone who might be
interested in collaborating on that part.

Other TODOs:
- Audit other callers of RelationSetTargetBlock() to ensure they don't
hold pointers into the page.

Regards,
James Coleman

Attachment Content-Type Size
v1-0001-Allow-getting-lock-before-calling-heap_page_prune.patch application/octet-stream 3.8 KB
v1-0002-Opportunistically-prune-to-avoid-building-a-new-p.patch application/octet-stream 4.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jelte Fennema 2023-06-21 13:08:19 Re: Support logical replication of DDLs
Previous Message James Coleman 2023-06-21 12:45:19 Re: Use of additional index columns in rows filtering