Use a bulk-write buffer access strategy for TOAST inserts
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254227psql -h localhost -U postgresBuilt from patchset v1 (message #1), October 22, 2026 at 08:23 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254227_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254227_1 && git checkout t254227_1Patchset v1 (message #1) is on t254227_1
Hi hackers,
Bulk insert operations use a BAS_BULKWRITE strategy for the main relation, but
TOAST rows are inserted without it. Large amounts of TOAST data can therefore
evict useful shared buffers. This has already been discussed in [1]/messages/by-id/20220622143841.GS22452@telsasoft.com.
This patch series proposes a new design allowing TOAST inserts to use the bulk
write buffer access strategy. The changes are split into four patches to ease
review.
The first two patches prepare for 0003. 0001 moves allocation and release of the
buffer access strategy out of BulkInsertState, so the same strategy can be passed
to multiple states. 0002 implements the medium term fix suggested by Andres in
22655aa2313 by preventing a BulkInsertState from being reused across relations.
Together, they allow the main and TOAST relations to have separate insertion
states while using one buffer ring.
So, the 4 patches are:
0001:
Buffer access strategies are generally owned by the operation that creates them.
BulkInsertState is an exception: it owns both its BAS_BULKWRITE strategy and
relation local insertion state. Those objects have different useful lifetimes:
one bulk operation may need several relation local states while all of them should
share the same buffer ring.
Pass the strategy to GetBulkInsertState(), leaving its allocation and release
to the caller.
0002:
BulkInsertState contains relation local state, including a target buffer pin
and relation extension information. Reusing it for another relation requires
resetting all that state correctly. Commits b1ecb9b3fcf and 22655aa2313 fixed
bugs caused by failing to do so.
Make GetBulkInsertState() take a relation and ensure that the resulting state
is used only with that relation.
For partitioned COPY, keep one state per leaf relation and share the caller
owned buffer strategy between them. This removes cross relation reuse while
retaining a single buffer ring for the COPY operation.
0003:
Give each BulkInsertState a state for its TOAST relation. Both states use the same
strategy provided by the caller while keeping their relation local insertion state
separate.
0004:
When a rewrite first needs to store TOAST data, create a BAS_BULKWRITE strategy
and a BulkInsertState for the destination relation. The TOAST state then shares
that strategy. Continue using the existing BulkWriteState for the main relation.
Note that the read path is not handled in this patch series, because TOAST data
can be read outside the scan that produced it, so it needs its own strategy and
lifetime. I plan to work on it once the write path (this thread) goes in.
[1]: /messages/by-id/20220622143841.GS22452@telsasoft.com
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
t254227_1v1-0001-Move-buffer-strategy-ownership-out-of-BulkInsertS.patchtext/x-diff; charset=us-asciiDownload+61-13
v1-0002-Bind-BulkInsertState-to-one-relation.patchtext/x-diff; charset=us-asciiDownload+110-27
v1-0003-Use-bulk-write-strategy-for-TOAST-inserts.patchtext/x-diff; charset=us-asciiDownload+296-35
v1-0004-Use-bulk-write-strategy-for-TOAST-during-heap-rew.patchtext/x-diff; charset=us-asciiDownload+69-2
Hi,
Bertrand and I were just chatting. Comments excerpted from that discussion
below:
On 2026-10-16 07:45:26 +0000, Bertrand Drouvot wrote:
Bulk insert operations use a BAS_BULKWRITE strategy for the main relation, but
TOAST rows are inserted without it. Large amounts of TOAST data can therefore
evict useful shared buffers. This has already been discussed in [1].
My comment about this aspect was that I
don't think we should use strategies for toast insertions, as toast accesses
cause random IO, which makes the evictions of the just-toasted data cause much
higher cost than evicting table data itself.
0001:
Buffer access strategies are generally owned by the operation that creates them.
BulkInsertState is an exception: it owns both its BAS_BULKWRITE strategy and
relation local insertion state. Those objects have different useful lifetimes:
one bulk operation may need several relation local states while all of them should
share the same buffer ring.Pass the strategy to GetBulkInsertState(), leaving its allocation and release
to the caller.
Here I think the better direction would be to move the creation of the "bulk
state" into tableam itself, and then make it reused across all DML
operations. For that it needs to be a state that can be extended by different
AMs.
I think we may have had patches floating around for that...
Greetings,
Andres Freund