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].
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]: https://postgr.es/m/20220622143841.GS22452%40telsasoft.com Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From 2dd2ac793e5267598a7bdc4c9276ac65a7d0e2b3 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 15 Oct 2026 12:17:42 +0000 Subject: [PATCH v1 1/4] Move buffer strategy ownership out of BulkInsertState 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. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- src/backend/access/heap/heapam.c | 9 ++++++--- src/backend/access/heap/heapam_handler.c | 12 +++++++++++- src/backend/commands/copyfrom.c | 23 +++++++++++++++++++++-- src/backend/commands/createas.c | 11 ++++++++++- src/backend/commands/matview.c | 6 +++++- src/backend/commands/tablecmds.c | 6 +++++- src/include/access/heapam.h | 2 +- src/include/access/hio.h | 4 ++-- 8 files changed, 61 insertions(+), 12 deletions(-) 22.1% src/backend/access/heap/ 63.2% src/backend/commands/ 14.5% src/include/access/ diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 6a09a1a1517..d52468c90f9 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1935,14 +1935,18 @@ UpdateXmaxHintBits(HeapTupleHeader tuple, Buffer buffer, TransactionId xid) /* * GetBulkInsertState - prepare status object for a bulk insert + * + * The caller owns strategy and may share it with other bulk insert states. */ BulkInsertState -GetBulkInsertState(void) +GetBulkInsertState(BufferAccessStrategy strategy) { BulkInsertState bistate; + Assert(strategy != NULL); + bistate = (BulkInsertState) palloc_object(BulkInsertStateData); - bistate->strategy = GetAccessStrategy(BAS_BULKWRITE); + bistate->strategy = strategy; bistate->current_buf = InvalidBuffer; bistate->next_free = InvalidBlockNumber; bistate->last_free = InvalidBlockNumber; @@ -1958,7 +1962,6 @@ FreeBulkInsertState(BulkInsertState bistate) { if (bistate->current_buf != InvalidBuffer) ReleaseBuffer(bistate->current_buf); - FreeAccessStrategy(bistate->strategy); pfree(bistate); } diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index 7d2065dec37..e33b9a82c74 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -596,6 +596,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, double *tups_recently_dead) { RewriteState rwstate; + BufferAccessStrategy strategy; BulkInsertState bistate; IndexScanDesc indexScan; TableScanDesc tableScan; @@ -638,9 +639,15 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, /* In concurrent mode, prepare for bulk-insert operation. */ if (concurrent) - bistate = GetBulkInsertState(); + { + strategy = GetAccessStrategy(BAS_BULKWRITE); + bistate = GetBulkInsertState(strategy); + } else + { + strategy = NULL; bistate = NULL; + } /* Set up sorting if wanted */ if (use_sort) @@ -941,7 +948,10 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, if (rwstate) end_heap_rewrite(rwstate); if (bistate) + { FreeBulkInsertState(bistate); + FreeAccessStrategy(strategy); + } /* Clean up */ pfree(values); diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 746b4fdf6c3..8729706bdbe 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -44,6 +44,7 @@ #include "optimizer/optimizer.h" #include "pgstat.h" #include "rewrite/rewriteHandler.h" +#include "storage/bufmgr.h" #include "storage/fd.h" #include "tcop/tcopprot.h" #include "utils/lsyscache.h" @@ -81,6 +82,7 @@ typedef struct CopyMultiInsertBuffer { TupleTableSlot *slots[MAX_BUFFERED_TUPLES]; /* Array to store tuples */ ResultRelInfo *resultRelInfo; /* ResultRelInfo for 'relid' */ + BufferAccessStrategy strategy; /* bulk-write buffer strategy */ BulkInsertState bistate; /* BulkInsertState for this rel if plain * table; NULL if foreign table */ int nused; /* number of 'slots' containing tuples */ @@ -369,7 +371,16 @@ CopyMultiInsertBufferInit(ResultRelInfo *rri) buffer = palloc_object(CopyMultiInsertBuffer); memset(buffer->slots, 0, sizeof(TupleTableSlot *) * MAX_BUFFERED_TUPLES); buffer->resultRelInfo = rri; - buffer->bistate = (rri->ri_FdwRoutine == NULL) ? GetBulkInsertState() : NULL; + if (rri->ri_FdwRoutine == NULL) + { + buffer->strategy = GetAccessStrategy(BAS_BULKWRITE); + buffer->bistate = GetBulkInsertState(buffer->strategy); + } + else + { + buffer->strategy = NULL; + buffer->bistate = NULL; + } buffer->nused = 0; return buffer; @@ -635,9 +646,13 @@ CopyMultiInsertBufferCleanup(CopyMultiInsertInfo *miinfo, { Assert(buffer->bistate != NULL); FreeBulkInsertState(buffer->bistate); + FreeAccessStrategy(buffer->strategy); } else + { Assert(buffer->bistate == NULL); + Assert(buffer->strategy == NULL); + } /* Since we only create slots on demand, just drop the non-null ones. */ for (i = 0; i < MAX_BUFFERED_TUPLES && buffer->slots[i] != NULL; i++) @@ -793,6 +808,7 @@ CopyFrom(CopyFromState cstate) ErrorContextCallback errcallback; CommandId mycid = GetCurrentCommandId(true); uint32 ti_options = 0; /* start with default options for insert */ + BufferAccessStrategy strategy = NULL; BulkInsertState bistate = NULL; CopyInsertMethod insertMethod; CopyMultiInsertInfo multiInsertInfo = {0}; /* pacify compiler */ @@ -1086,7 +1102,8 @@ CopyFrom(CopyFromState cstate) { singleslot = table_slot_create(resultRelInfo->ri_RelationDesc, &estate->es_tupleTable); - bistate = GetBulkInsertState(); + strategy = GetAccessStrategy(BAS_BULKWRITE); + bistate = GetBulkInsertState(strategy); } has_before_insert_row_trig = (resultRelInfo->ri_TrigDesc && @@ -1484,6 +1501,8 @@ CopyFrom(CopyFromState cstate) if (bistate != NULL) FreeBulkInsertState(bistate); + if (strategy != NULL) + FreeAccessStrategy(strategy); MemoryContextSwitchTo(oldcontext); diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 6dbb831ca89..8b336e0a3f8 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -42,6 +42,7 @@ #include "nodes/queryjumble.h" #include "parser/analyze.h" #include "rewrite/rewriteHandler.h" +#include "storage/bufmgr.h" #include "tcop/tcopprot.h" #include "utils/builtins.h" #include "utils/lsyscache.h" @@ -57,6 +58,7 @@ typedef struct ObjectAddress reladdr; /* address of rel, for ExecCreateTableAs */ CommandId output_cid; /* cmin to insert in output tuples */ uint32 ti_options; /* table_tuple_insert performance options */ + BufferAccessStrategy strategy; /* bulk-write buffer strategy */ BulkInsertState bistate; /* bulk insert state */ } DR_intorel; @@ -565,9 +567,15 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) * bulk inserts as there are no tuples to insert. */ if (!into->skipData) - myState->bistate = GetBulkInsertState(); + { + myState->strategy = GetAccessStrategy(BAS_BULKWRITE); + myState->bistate = GetBulkInsertState(myState->strategy); + } else + { + myState->strategy = NULL; myState->bistate = NULL; + } /* * Valid smgr_targblock implies something already wrote to the relation. @@ -619,6 +627,7 @@ intorel_shutdown(DestReceiver *self) if (!into->skipData) { FreeBulkInsertState(myState->bistate); + FreeAccessStrategy(myState->strategy); table_finish_bulk_insert(myState->rel, myState->ti_options); } diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 40748958eaf..d231004bd80 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -33,6 +33,7 @@ #include "miscadmin.h" #include "pgstat.h" #include "rewrite/rewriteHandler.h" +#include "storage/bufmgr.h" #include "storage/lmgr.h" #include "tcop/tcopprot.h" #include "utils/builtins.h" @@ -50,6 +51,7 @@ typedef struct Relation transientrel; /* relation to write to */ CommandId output_cid; /* cmin to insert in output tuples */ uint32 ti_options; /* table_tuple_insert performance options */ + BufferAccessStrategy strategy; /* bulk-write buffer strategy */ BulkInsertState bistate; /* bulk insert state */ } DR_transientrel; @@ -493,7 +495,8 @@ transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) myState->transientrel = transientrel; myState->output_cid = GetCurrentCommandId(true); myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN; - myState->bistate = GetBulkInsertState(); + myState->strategy = GetAccessStrategy(BAS_BULKWRITE); + myState->bistate = GetBulkInsertState(myState->strategy); /* * Valid smgr_targblock implies something already wrote to the relation. @@ -539,6 +542,7 @@ transientrel_shutdown(DestReceiver *self) DR_transientrel *myState = (DR_transientrel *) self; FreeBulkInsertState(myState->bistate); + FreeAccessStrategy(myState->strategy); table_finish_bulk_insert(myState->transientrel, myState->ti_options); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 93ad49e59fd..7f40a6e1299 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6207,6 +6207,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) ListCell *l; EState *estate; CommandId mycid; + BufferAccessStrategy strategy; BulkInsertState bistate; uint32 ti_options; ExprState *partqualstate = NULL; @@ -6235,13 +6236,15 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) if (newrel) { mycid = GetCurrentCommandId(true); - bistate = GetBulkInsertState(); + strategy = GetAccessStrategy(BAS_BULKWRITE); + bistate = GetBulkInsertState(strategy); ti_options = TABLE_INSERT_SKIP_FSM; } else { /* keep compiler quiet about using these uninitialized */ mycid = 0; + strategy = NULL; bistate = NULL; ti_options = 0; } @@ -6620,6 +6623,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) if (newrel) { FreeBulkInsertState(bistate); + FreeAccessStrategy(strategy); table_finish_bulk_insert(newrel, ti_options); diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 9e35961fb9e..1d5cdecc74b 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -371,7 +371,7 @@ extern bool heap_fetch(Relation relation, Snapshot snapshot, extern void heap_get_latest_tid(TableScanDesc sscan, ItemPointer tid); -extern BulkInsertState GetBulkInsertState(void); +extern BulkInsertState GetBulkInsertState(BufferAccessStrategy strategy); extern void FreeBulkInsertState(BulkInsertState); extern void ReleaseBulkInsertStatePin(BulkInsertState bistate); diff --git a/src/include/access/hio.h b/src/include/access/hio.h index 60cfc375fd5..be17351b2d2 100644 --- a/src/include/access/hio.h +++ b/src/include/access/hio.h @@ -21,8 +21,8 @@ /* * state for bulk inserts --- private to heapam.c and hio.c * - * If current_buf isn't InvalidBuffer, then we are holding an extra pin - * on that buffer. + * The caller owns the buffer access strategy. If current_buf isn't + * InvalidBuffer, then we are holding an extra pin on that buffer. * * "typedef struct BulkInsertStateData *BulkInsertState" is in heapam.h */ -- 2.34.1
>From 49a25e34e227d6a184326f12d35faabe76ebfc6f Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 15 Oct 2026 12:18:28 +0000 Subject: [PATCH v1 2/4] Bind BulkInsertState to one relation 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. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- src/backend/access/heap/heapam.c | 33 ++++++---- src/backend/access/heap/heapam_handler.c | 2 +- src/backend/commands/copyfrom.c | 76 ++++++++++++++++++++++-- src/backend/commands/createas.c | 2 +- src/backend/commands/matview.c | 2 +- src/backend/commands/tablecmds.c | 2 +- src/backend/executor/execMain.c | 1 + src/backend/executor/execPartition.c | 1 + src/include/access/heapam.h | 2 +- src/include/access/hio.h | 6 +- src/include/access/tableam.h | 6 +- src/include/nodes/execnodes.h | 2 + src/tools/pgindent/typedefs.list | 1 + 13 files changed, 110 insertions(+), 26 deletions(-) 28.9% src/backend/access/heap/ 46.6% src/backend/commands/ 19.6% src/include/access/ 4.7% src/ diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index d52468c90f9..6e526793c9d 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1934,12 +1934,24 @@ UpdateXmaxHintBits(HeapTupleHeader tuple, Buffer buffer, TransactionId xid) /* - * GetBulkInsertState - prepare status object for a bulk insert + * Verify that bulk insert state is used only with its assigned relation. + */ +static inline void +CheckBulkInsertState(Relation relation, BulkInsertState bistate) +{ + if (unlikely(bistate != NULL && + bistate->relation_id != RelationGetRelid(relation))) + elog(ERROR, "bulk insert state for relation %u used for relation %u", + bistate->relation_id, RelationGetRelid(relation)); +} + +/* + * GetBulkInsertState - prepare bulk insert state for the given relation * - * The caller owns strategy and may share it with other bulk insert states. + * The caller owns strategy and may share it with states for other relations. */ BulkInsertState -GetBulkInsertState(BufferAccessStrategy strategy) +GetBulkInsertState(Relation relation, BufferAccessStrategy strategy) { BulkInsertState bistate; @@ -1947,6 +1959,7 @@ GetBulkInsertState(BufferAccessStrategy strategy) bistate = (BulkInsertState) palloc_object(BulkInsertStateData); bistate->strategy = strategy; + bistate->relation_id = RelationGetRelid(relation); bistate->current_buf = InvalidBuffer; bistate->next_free = InvalidBlockNumber; bistate->last_free = InvalidBlockNumber; @@ -1967,6 +1980,8 @@ FreeBulkInsertState(BulkInsertState bistate) /* * ReleaseBulkInsertStatePin - release a buffer currently held in bistate + * + * Also abandon unused pages from the state's most recent bulk extension. */ void ReleaseBulkInsertStatePin(BulkInsertState bistate) @@ -1975,14 +1990,6 @@ ReleaseBulkInsertStatePin(BulkInsertState bistate) ReleaseBuffer(bistate->current_buf); bistate->current_buf = InvalidBuffer; - /* - * Despite the name, we also reset bulk relation extension state. - * Otherwise we can end up erroring out due to looking for free space in - * ->next_free of one partition, even though ->next_free was set when - * extending another partition. It could obviously also be bad for - * efficiency to look at existing blocks at offsets from another - * partition, even if we don't error out. - */ bistate->next_free = InvalidBlockNumber; bistate->last_free = InvalidBlockNumber; } @@ -2018,6 +2025,8 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid, bool clear_all_visible = false; bool vmbuffer_modified = false; + CheckBulkInsertState(relation, bistate); + /* Cheap, simplistic check that the tuple matches the rel's rowtype. */ Assert(HeapTupleHeaderGetNatts(tup->t_data) <= RelationGetNumberOfAttributes(relation)); @@ -2328,6 +2337,8 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples, int npages = 0; int npages_used = 0; + CheckBulkInsertState(relation, bistate); + /* currently not needed (thus unsupported) for heap_multi_insert() */ Assert(!(options & HEAP_INSERT_NO_LOGICAL)); diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index e33b9a82c74..44e244543ee 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -641,7 +641,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, if (concurrent) { strategy = GetAccessStrategy(BAS_BULKWRITE); - bistate = GetBulkInsertState(strategy); + bistate = GetBulkInsertState(NewHeap, strategy); } else { diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 8729706bdbe..efe334e1523 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -90,6 +90,13 @@ typedef struct CopyMultiInsertBuffer * stream */ } CopyMultiInsertBuffer; +/* Stores single-insert state for one partition. */ +typedef struct CopySingleInsertState +{ + ResultRelInfo *resultRelInfo; + BulkInsertState bistate; +} CopySingleInsertState; + /* * Stores one or many CopyMultiInsertBuffers and details about the size and * number of tuples which are stored in them. This allows multiple buffers to @@ -374,7 +381,7 @@ CopyMultiInsertBufferInit(ResultRelInfo *rri) if (rri->ri_FdwRoutine == NULL) { buffer->strategy = GetAccessStrategy(BAS_BULKWRITE); - buffer->bistate = GetBulkInsertState(buffer->strategy); + buffer->bistate = GetBulkInsertState(rri->ri_RelationDesc, buffer->strategy); } else { @@ -665,6 +672,44 @@ CopyMultiInsertBufferCleanup(CopyMultiInsertInfo *miinfo, pfree(buffer); } +/* + * Create state for single inserts into one partition. + */ +static CopySingleInsertState * +CopySingleInsertStateInit(ResultRelInfo *rri, BufferAccessStrategy strategy) +{ + CopySingleInsertState *state; + + Assert(rri->ri_CopySingleInsertState == NULL); + + state = palloc_object(CopySingleInsertState); + state->resultRelInfo = rri; + state->bistate = GetBulkInsertState(rri->ri_RelationDesc, strategy); + rri->ri_CopySingleInsertState = state; + + return state; +} + +/* + * Release all states used for partitioned single inserts. + */ +static void +CopySingleInsertStatesCleanup(List *states) +{ + ListCell *lc; + + foreach(lc, states) + { + CopySingleInsertState *state = lfirst(lc); + + state->resultRelInfo->ri_CopySingleInsertState = NULL; + FreeBulkInsertState(state->bistate); + pfree(state); + } + + list_free(states); +} + /* * Write out all stored tuples in all buffers out to the tables. * @@ -810,6 +855,7 @@ CopyFrom(CopyFromState cstate) uint32 ti_options = 0; /* start with default options for insert */ BufferAccessStrategy strategy = NULL; BulkInsertState bistate = NULL; + List *singleInsertStates = NIL; CopyInsertMethod insertMethod; CopyMultiInsertInfo multiInsertInfo = {0}; /* pacify compiler */ int64 processed = 0; @@ -1103,7 +1149,8 @@ CopyFrom(CopyFromState cstate) singleslot = table_slot_create(resultRelInfo->ri_RelationDesc, &estate->es_tupleTable); strategy = GetAccessStrategy(BAS_BULKWRITE); - bistate = GetBulkInsertState(strategy); + if (proute == NULL) + bistate = GetBulkInsertState(resultRelInfo->ri_RelationDesc, strategy); } has_before_insert_row_trig = (resultRelInfo->ri_TrigDesc && @@ -1273,8 +1320,25 @@ CopyFrom(CopyFromState cstate) &processed); } - if (bistate != NULL) - ReleaseBulkInsertStatePin(bistate); + if (prevResultRelInfo != NULL && + prevResultRelInfo->ri_CopySingleInsertState != NULL) + ReleaseBulkInsertStatePin(prevResultRelInfo->ri_CopySingleInsertState->bistate); + + if (!leafpart_use_multi_insert && resultRelInfo->ri_FdwRoutine == NULL) + { + CopySingleInsertState *state = resultRelInfo->ri_CopySingleInsertState; + + if (state == NULL) + { + state = CopySingleInsertStateInit(resultRelInfo, strategy); + singleInsertStates = lappend(singleInsertStates, state); + } + + bistate = state->bistate; + } + else + bistate = NULL; + prevResultRelInfo = resultRelInfo; } @@ -1499,7 +1563,9 @@ CopyFrom(CopyFromState cstate) cstate->num_errors)); } - if (bistate != NULL) + if (proute != NULL) + CopySingleInsertStatesCleanup(singleInsertStates); + else if (bistate != NULL) FreeBulkInsertState(bistate); if (strategy != NULL) FreeAccessStrategy(strategy); diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 8b336e0a3f8..daf9c94f543 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -569,7 +569,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) if (!into->skipData) { myState->strategy = GetAccessStrategy(BAS_BULKWRITE); - myState->bistate = GetBulkInsertState(myState->strategy); + myState->bistate = GetBulkInsertState(intoRelationDesc, myState->strategy); } else { diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index d231004bd80..94fcc6b1197 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -496,7 +496,7 @@ transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) myState->output_cid = GetCurrentCommandId(true); myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN; myState->strategy = GetAccessStrategy(BAS_BULKWRITE); - myState->bistate = GetBulkInsertState(myState->strategy); + myState->bistate = GetBulkInsertState(transientrel, myState->strategy); /* * Valid smgr_targblock implies something already wrote to the relation. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 7f40a6e1299..d6fe26acc70 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6237,7 +6237,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) { mycid = GetCurrentCommandId(true); strategy = GetAccessStrategy(BAS_BULKWRITE); - bistate = GetBulkInsertState(strategy); + bistate = GetBulkInsertState(newrel, strategy); ti_options = TABLE_INSERT_SKIP_FSM; } else diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index d7a8f36ca1d..5b2cd3b2576 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1362,6 +1362,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_ChildToRootMap = NULL; resultRelInfo->ri_ChildToRootMapValid = false; resultRelInfo->ri_CopyMultiInsertBuffer = NULL; + resultRelInfo->ri_CopySingleInsertState = NULL; } /* diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c index 4ec69f2bf99..878e7c0a8cd 100644 --- a/src/backend/executor/execPartition.c +++ b/src/backend/executor/execPartition.c @@ -1163,6 +1163,7 @@ ExecInitRoutingInfo(ModifyTableState *mtstate, Assert(partRelInfo->ri_BatchSize >= 1); partRelInfo->ri_CopyMultiInsertBuffer = NULL; + partRelInfo->ri_CopySingleInsertState = NULL; /* * Keep track of it in the PartitionTupleRouting->partitions array. diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 1d5cdecc74b..25522c1ffa9 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -371,7 +371,7 @@ extern bool heap_fetch(Relation relation, Snapshot snapshot, extern void heap_get_latest_tid(TableScanDesc sscan, ItemPointer tid); -extern BulkInsertState GetBulkInsertState(BufferAccessStrategy strategy); +extern BulkInsertState GetBulkInsertState(Relation relation, BufferAccessStrategy strategy); extern void FreeBulkInsertState(BulkInsertState); extern void ReleaseBulkInsertStatePin(BulkInsertState bistate); diff --git a/src/include/access/hio.h b/src/include/access/hio.h index be17351b2d2..5d02d5426e2 100644 --- a/src/include/access/hio.h +++ b/src/include/access/hio.h @@ -19,9 +19,10 @@ #include "utils/relcache.h" /* - * state for bulk inserts --- private to heapam.c and hio.c + * state for bulk inserts into one relation --- private to heapam.c and hio.c * - * The caller owns the buffer access strategy. If current_buf isn't + * The caller owns the buffer access strategy and may share it with states for + * other relations in the same bulk operation. If current_buf isn't * InvalidBuffer, then we are holding an extra pin on that buffer. * * "typedef struct BulkInsertStateData *BulkInsertState" is in heapam.h @@ -29,6 +30,7 @@ typedef struct BulkInsertStateData { BufferAccessStrategy strategy; /* our BULKWRITE strategy object */ + Oid relation_id; /* relation this state belongs to */ Buffer current_buf; /* current insertion target page */ /* diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index ea3f2a6be99..82d5a8717e2 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -1464,9 +1464,9 @@ table_index_delete_tuples(Relation rel, TM_IndexDeleteOp *delstate) * Note that most of these options will be applied when inserting into the * heap's TOAST table, too, if the tuple requires any out-of-line data. * - * The BulkInsertState object (if any; bistate can be NULL for default - * behavior) is also just passed through to RelationGetBufferForTuple. If - * `bistate` is provided, table_finish_bulk_insert() needs to be called. + * A BulkInsertState object (if any; bistate can be NULL for default behavior) + * belongs to one relation and allows the AM to optimize a series of inserts. + * If `bistate` is provided, table_finish_bulk_insert() needs to be called. * * On return the slot's tts_tid and tts_tableOid are updated to reflect the * insertion. But note that any toasting of fields within the slot is NOT diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index e3cea52e7df..761a47d94ec 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -634,6 +634,8 @@ typedef struct ResultRelInfo /* for use by copyfrom.c when performing multi-inserts */ struct CopyMultiInsertBuffer *ri_CopyMultiInsertBuffer; + /* for use by copyfrom.c when performing partitioned single inserts */ + struct CopySingleInsertState *ri_CopySingleInsertState; /* * Used when a leaf partition is involved in a cross-partition update of diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index a7a8dbdd23f..ed0924781d8 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -551,6 +551,7 @@ CopyMethod CopyMultiInsertBuffer CopyMultiInsertInfo CopyOnErrorChoice +CopySingleInsertState CopySeqResult CopySource CopyStmt -- 2.34.1
>From a9b7e4a408448521719fb95f4171bc6a683f088b Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 15 Oct 2026 11:53:29 +0000 Subject: [PATCH v1 3/4] Use bulk-write strategy for TOAST inserts 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. 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. Do not use bulk extension ranges for TOAST relations, as that could reserve too much unused space in small TOAST tables. Add tests for partitioned COPY and COPY's single-insert and multi-insert paths. Reported-by: Justin Pryzby <[email protected]> Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ Discussion: https://postgr.es/m/20220622143841.GS22452%40telsasoft.com --- src/backend/access/common/toast_internals.c | 28 +++++++++-- src/backend/access/heap/heapam.c | 20 +++++--- src/backend/access/heap/heaptoast.c | 10 ++-- src/backend/access/heap/hio.c | 36 ++++++++----- src/backend/access/heap/rewriteheap.c | 2 +- src/backend/access/table/toast_helper.c | 5 +- src/include/access/heaptoast.h | 5 +- src/include/access/hio.h | 6 ++- src/include/access/tableam.h | 4 +- src/include/access/toast_helper.h | 5 +- src/include/access/toast_internals.h | 5 +- src/test/regress/expected/copy.out | 45 +++++++++++++++++ src/test/regress/expected/stats.out | 56 +++++++++++++++++++++ src/test/regress/sql/copy.sql | 53 +++++++++++++++++++ src/test/regress/sql/stats.sql | 50 ++++++++++++++++++ 15 files changed, 296 insertions(+), 34 deletions(-) 7.8% src/backend/access/common/ 22.7% src/backend/access/heap/ 9.3% src/include/access/ 31.0% src/test/regress/expected/ 27.9% src/test/regress/sql/ diff --git a/src/backend/access/common/toast_internals.c b/src/backend/access/common/toast_internals.c index 2bdc0b7d3f2..68508c03715 100644 --- a/src/backend/access/common/toast_internals.c +++ b/src/backend/access/common/toast_internals.c @@ -17,6 +17,7 @@ #include "access/genam.h" #include "access/heapam.h" #include "access/heaptoast.h" +#include "access/hio.h" #include "access/table.h" #include "access/toast_compression.h" #include "access/toast_internals.h" @@ -24,6 +25,7 @@ #include "catalog/catalog.h" #include "miscadmin.h" #include "utils/fmgroids.h" +#include "utils/memutils.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -184,13 +186,15 @@ toast_preserve_valueid(Relation toastrel, Oid toastoid, * value: datum to be pushed to toast storage * oldexternal: if not NULL, toast pointer previously representing the datum * options: options to be passed to heap_insert() for toast rows + * bistate: bulk insert state for the main relation, or NULL * ---------- */ Datum -toast_save_datum(Relation rel, Datum value, - varlena *oldexternal, uint32 options) +toast_save_datum(Relation rel, Datum value, varlena *oldexternal, uint32 options, + BulkInsertState bistate) { Relation toastrel; + BulkInsertState toast_bistate = NULL; Relation *toastidxs; TupleDesc toasttupDesc; CommandId mycid = GetCurrentCommandId(true); @@ -220,6 +224,24 @@ toast_save_datum(Relation rel, Datum value, toastrel = table_open(rel->rd_rel->reltoastrelid, RowExclusiveLock); toasttupDesc = toastrel->rd_att; + if (bistate != NULL) + { + if (bistate->toast_state == NULL) + { + MemoryContext oldcontext; + + /* + * Allocate the TOAST state in the same context as its parent, as + * the current context may be reset before the bulk insert + * finishes. + */ + oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(bistate)); + bistate->toast_state = GetBulkInsertState(toastrel, bistate->strategy); + MemoryContextSwitchTo(oldcontext); + } + toast_bistate = bistate->toast_state; + } + /* Open all the toast indexes and look for the valid one */ validIndex = toast_open_indexes(toastrel, RowExclusiveLock, @@ -354,7 +376,7 @@ toast_save_datum(Relation rel, Datum value, toasttup = heap_form_tuple(toasttupDesc, t_values, t_isnull); - heap_insert(toastrel, toasttup, mycid, options, NULL); + heap_insert(toastrel, toasttup, mycid, options, toast_bistate); /* * Create the index entry. We cheat a little here by not using diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 6e526793c9d..3773e206541 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -58,7 +58,8 @@ static HeapTuple heap_prepare_insert(Relation relation, HeapTuple tup, - TransactionId xid, CommandId cid, uint32 options); + TransactionId xid, CommandId cid, uint32 options, + BulkInsertState bistate); static XLogRecPtr log_heap_update(Relation reln, Buffer oldbuf, Buffer vmbuffer_old, Buffer newbuf, Buffer vmbuffer_new, HeapTuple oldtup, @@ -1960,6 +1961,7 @@ GetBulkInsertState(Relation relation, BufferAccessStrategy strategy) bistate = (BulkInsertState) palloc_object(BulkInsertStateData); bistate->strategy = strategy; bistate->relation_id = RelationGetRelid(relation); + bistate->toast_state = NULL; bistate->current_buf = InvalidBuffer; bistate->next_free = InvalidBlockNumber; bistate->last_free = InvalidBlockNumber; @@ -1973,6 +1975,8 @@ GetBulkInsertState(Relation relation, BufferAccessStrategy strategy) void FreeBulkInsertState(BulkInsertState bistate) { + if (bistate->toast_state != NULL) + FreeBulkInsertState(bistate->toast_state); if (bistate->current_buf != InvalidBuffer) ReleaseBuffer(bistate->current_buf); pfree(bistate); @@ -1986,6 +1990,9 @@ FreeBulkInsertState(BulkInsertState bistate) void ReleaseBulkInsertStatePin(BulkInsertState bistate) { + if (bistate->toast_state != NULL) + ReleaseBulkInsertStatePin(bistate->toast_state); + if (bistate->current_buf != InvalidBuffer) ReleaseBuffer(bistate->current_buf); bistate->current_buf = InvalidBuffer; @@ -2039,7 +2046,7 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid, * Note: below this point, heaptup is the data we actually intend to store * into the relation; tup is the caller's original untoasted data. */ - heaptup = heap_prepare_insert(relation, tup, xid, cid, options); + heaptup = heap_prepare_insert(relation, tup, xid, cid, options, bistate); /* * Find buffer to insert this tuple into. If the page is all visible, @@ -2240,7 +2247,7 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid, */ static HeapTuple heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid, - CommandId cid, uint32 options) + CommandId cid, uint32 options, BulkInsertState bistate) { /* * To allow parallel inserts, we need to ensure that they are safe to be @@ -2276,7 +2283,7 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid, return tup; } else if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD) - return heap_toast_insert_or_update(relation, tup, NULL, options); + return heap_toast_insert_or_update(relation, tup, NULL, options, bistate); else return tup; } @@ -2358,7 +2365,7 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples, slots[i]->tts_tableOid = RelationGetRelid(relation); tuple->t_tableOid = slots[i]->tts_tableOid; heaptuples[i] = heap_prepare_insert(relation, tuple, xid, cid, - options); + options, bistate); } /* @@ -3993,7 +4000,8 @@ l2: * tuples too. We never skip the FSM here. */ heaptup = heap_toast_insert_or_update(relation, newtup, &oldtup, - walLogical ? 0 : HEAP_INSERT_NO_LOGICAL); + walLogical ? 0 : HEAP_INSERT_NO_LOGICAL, + NULL); newtupsize = MAXALIGN(heaptup->t_len); } else diff --git a/src/backend/access/heap/heaptoast.c b/src/backend/access/heap/heaptoast.c index b03d8492959..345afaf622c 100644 --- a/src/backend/access/heap/heaptoast.c +++ b/src/backend/access/heap/heaptoast.c @@ -84,6 +84,7 @@ heap_toast_delete(Relation rel, HeapTuple oldtup, bool is_speculative) * newtup: the candidate new tuple to be inserted * oldtup: the old row version for UPDATE, or NULL for INSERT * options: options to be passed to heap_insert() for toast rows + * bistate: bulk insert state for INSERT, or NULL for UPDATE * Result: * either newtup if no toasting is needed, or a palloc'd modified tuple * that is what should actually get stored @@ -94,7 +95,7 @@ heap_toast_delete(Relation rel, HeapTuple oldtup, bool is_speculative) */ HeapTuple heap_toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, - uint32 options) + uint32 options, BulkInsertState bistate) { HeapTuple result_tuple; TupleDesc tupleDesc; @@ -124,6 +125,7 @@ heap_toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, */ Assert(rel->rd_rel->relkind == RELKIND_RELATION || rel->rd_rel->relkind == RELKIND_MATVIEW); + Assert(bistate == NULL || oldtup == NULL); /* * Get the tuple descriptor and break down the tuple(s) into fields. @@ -214,7 +216,7 @@ heap_toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, */ if (toast_attr[biggest_attno].tai_size > maxDataLen && rel->rd_rel->reltoastrelid != InvalidOid) - toast_tuple_externalize(&ttc, biggest_attno, options); + toast_tuple_externalize(&ttc, biggest_attno, options, bistate); } /* @@ -231,7 +233,7 @@ heap_toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, biggest_attno = toast_tuple_find_biggest_attribute(&ttc, false, false); if (biggest_attno < 0) break; - toast_tuple_externalize(&ttc, biggest_attno, options); + toast_tuple_externalize(&ttc, biggest_attno, options, bistate); } /* @@ -267,7 +269,7 @@ heap_toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, if (biggest_attno < 0) break; - toast_tuple_externalize(&ttc, biggest_attno, options); + toast_tuple_externalize(&ttc, biggest_attno, options, bistate); } /* diff --git a/src/backend/access/heap/hio.c b/src/backend/access/heap/hio.c index fb4ffb97592..4cb7821277e 100644 --- a/src/backend/access/heap/hio.c +++ b/src/backend/access/heap/hio.c @@ -244,15 +244,23 @@ RelationAddBlocks(Relation relation, BulkInsertState bistate, uint32 not_in_fsm_pages; Buffer buffer; Page page; + bool use_extension_state; + + /* + * TOAST writes share the bulk ring and retain a target-page pin, but do + * not reserve private extension ranges that can leave large unused tails + * in small TOAST relations. + */ + use_extension_state = bistate != NULL && relation->rd_rel->relkind != RELKIND_TOASTVALUE; /* * Determine by how many pages to try to extend by. */ - if (bistate == NULL && !use_fsm) + if (!use_extension_state && !use_fsm) { /* - * If we have neither bistate, nor can use the FSM, we can't bulk - * extend - there'd be no way to find the additional pages. + * Without the FSM or relation extension state, there'd be no way to + * find additional pages after extending. */ extend_by_pages = 1; } @@ -295,7 +303,7 @@ RelationAddBlocks(Relation relation, BulkInsertState bistate, * prevent future contention. * --- */ - if (bistate) + if (use_extension_state) extend_by_pages = Max(extend_by_pages, bistate->already_extended_by); /* @@ -308,14 +316,14 @@ RelationAddBlocks(Relation relation, BulkInsertState bistate, /* * How many of the extended pages should be entered into the FSM? * - * If we have a bistate, only enter pages that we don't need ourselves - * into the FSM. Otherwise every other backend will immediately try to - * use the pages this backend needs for itself, causing unnecessary - * contention. If we don't have a bistate, we can't avoid the FSM. + * If we retain private extension state, only enter pages that we don't + * need ourselves into the FSM. Otherwise every other backend will + * immediately try to use the pages this backend needs for itself, causing + * unnecessary contention. * * Never enter the page returned into the FSM, we'll immediately use it. */ - if (num_pages > 1 && bistate == NULL) + if (num_pages > 1 && !use_extension_state) not_in_fsm_pages = 1; else not_in_fsm_pages = num_pages; @@ -404,7 +412,7 @@ RelationAddBlocks(Relation relation, BulkInsertState bistate, FreeSpaceMapVacuumRange(relation, first_fsm_block, last_block + 1); } - if (bistate) + if (use_extension_state) { /* * Remember the additional pages we extended by, so we later can use @@ -421,10 +429,14 @@ RelationAddBlocks(Relation relation, BulkInsertState bistate, bistate->last_free = InvalidBlockNumber; } - /* maintain bistate->current_buf */ + bistate->already_extended_by += extend_by_pages; + } + + /* maintain bistate->current_buf */ + if (bistate) + { IncrBufferRefCount(buffer); bistate->current_buf = buffer; - bistate->already_extended_by += extend_by_pages; } return buffer; diff --git a/src/backend/access/heap/rewriteheap.c b/src/backend/access/heap/rewriteheap.c index 0648e433031..90ddfd11908 100644 --- a/src/backend/access/heap/rewriteheap.c +++ b/src/backend/access/heap/rewriteheap.c @@ -628,7 +628,7 @@ raw_heap_insert(RewriteState state, HeapTuple tup) options |= HEAP_INSERT_NO_LOGICAL; heaptup = heap_toast_insert_or_update(state->rs_new_rel, tup, NULL, - options); + options, NULL); } else heaptup = tup; diff --git a/src/backend/access/table/toast_helper.c b/src/backend/access/table/toast_helper.c index 2613d9dc095..d550599ee13 100644 --- a/src/backend/access/table/toast_helper.c +++ b/src/backend/access/table/toast_helper.c @@ -269,7 +269,8 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute) * Move an attribute to external storage. */ void -toast_tuple_externalize(ToastTupleContext *ttc, int attribute, uint32 options) +toast_tuple_externalize(ToastTupleContext *ttc, int attribute, uint32 options, + struct BulkInsertStateData *bistate) { Datum *value = &ttc->ttc_values[attribute]; Datum old_value = *value; @@ -277,7 +278,7 @@ toast_tuple_externalize(ToastTupleContext *ttc, int attribute, uint32 options) attr->tai_colflags |= TOASTCOL_IGNORE; *value = toast_save_datum(ttc->ttc_rel, old_value, attr->tai_oldexternal, - options); + options, bistate); if ((attr->tai_colflags & TOASTCOL_NEEDS_FREE) != 0) pfree(DatumGetPointer(old_value)); attr->tai_colflags |= TOASTCOL_NEEDS_FREE; diff --git a/src/include/access/heaptoast.h b/src/include/access/heaptoast.h index 4c3fda8a5f9..471883821ac 100644 --- a/src/include/access/heaptoast.h +++ b/src/include/access/heaptoast.h @@ -17,6 +17,8 @@ #include "storage/lockdefs.h" #include "utils/relcache.h" +struct BulkInsertStateData; + /* * Find the maximum size of a tuple if there are to be N tuples per page. */ @@ -109,7 +111,8 @@ * ---------- */ extern HeapTuple heap_toast_insert_or_update(Relation rel, HeapTuple newtup, - HeapTuple oldtup, uint32 options); + HeapTuple oldtup, uint32 options, + struct BulkInsertStateData *bistate); /* ---------- * heap_toast_delete - diff --git a/src/include/access/hio.h b/src/include/access/hio.h index 5d02d5426e2..b93267cace1 100644 --- a/src/include/access/hio.h +++ b/src/include/access/hio.h @@ -19,10 +19,11 @@ #include "utils/relcache.h" /* - * state for bulk inserts into one relation --- private to heapam.c and hio.c + * state for bulk inserts into one relation * * The caller owns the buffer access strategy and may share it with states for - * other relations in the same bulk operation. If current_buf isn't + * related relations in the same bulk operation. The heap AM can create a child + * state for the the relation's TOAST table. If current_buf isn't * InvalidBuffer, then we are holding an extra pin on that buffer. * * "typedef struct BulkInsertStateData *BulkInsertState" is in heapam.h @@ -31,6 +32,7 @@ typedef struct BulkInsertStateData { BufferAccessStrategy strategy; /* our BULKWRITE strategy object */ Oid relation_id; /* relation this state belongs to */ + struct BulkInsertStateData *toast_state; /* state for TOAST relation */ Buffer current_buf; /* current insertion target page */ /* diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index 82d5a8717e2..a927138d492 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -1466,7 +1466,9 @@ table_index_delete_tuples(Relation rel, TM_IndexDeleteOp *delstate) * * A BulkInsertState object (if any; bistate can be NULL for default behavior) * belongs to one relation and allows the AM to optimize a series of inserts. - * If `bistate` is provided, table_finish_bulk_insert() needs to be called. + * The heap AM creates a related state for the TOAST relation that shares the + * same buffer access strategy. If `bistate` is provided, + * table_finish_bulk_insert() needs to be called. * * On return the slot's tts_tid and tts_tableOid are updated to reflect the * insertion. But note that any toasting of fields within the slot is NOT diff --git a/src/include/access/toast_helper.h b/src/include/access/toast_helper.h index 2ec92397f26..57a27755761 100644 --- a/src/include/access/toast_helper.h +++ b/src/include/access/toast_helper.h @@ -16,6 +16,8 @@ #include "utils/rel.h" +struct BulkInsertStateData; + /* * Information about one column of a tuple being toasted. * @@ -107,7 +109,8 @@ extern int toast_tuple_find_biggest_attribute(ToastTupleContext *ttc, bool check_main); extern void toast_tuple_try_compression(ToastTupleContext *ttc, int attribute); extern void toast_tuple_externalize(ToastTupleContext *ttc, int attribute, - uint32 options); + uint32 options, + struct BulkInsertStateData *bistate); extern void toast_tuple_cleanup(ToastTupleContext *ttc); extern void toast_delete_external(Relation rel, const Datum *values, const bool *isnull, diff --git a/src/include/access/toast_internals.h b/src/include/access/toast_internals.h index a31b47f8df5..4ac648642fb 100644 --- a/src/include/access/toast_internals.h +++ b/src/include/access/toast_internals.h @@ -17,12 +17,15 @@ #include "utils/relcache.h" #include "utils/snapshot.h" +struct BulkInsertStateData; + extern Datum toast_compress_datum(Datum value, char cmethod); extern Oid toast_get_valid_index(Oid toastoid, LOCKMODE lock); extern void toast_delete_datum(Relation rel, Datum value, bool is_speculative); extern Datum toast_save_datum(Relation rel, Datum value, - varlena *oldexternal, uint32 options); + varlena *oldexternal, uint32 options, + struct BulkInsertStateData *bistate); extern void toast_valueid_scankey_init(ScanKey entry, Oid toast_typid, Oid8 valueid); diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out index 979bef5f3f2..2de2adc66a0 100644 --- a/src/test/regress/expected/copy.out +++ b/src/test/regress/expected/copy.out @@ -572,6 +572,51 @@ SELECT tableoid::regclass, id % 2 = 0 is_even, count(*) from parted_si GROUP BY (2 rows) DROP TABLE parted_si; +-- Test partition switching with toasted rows in COPY's single-insert path +CREATE TABLE parted_si_toast ( + id int not null, + payload text GENERATED ALWAYS AS (repeat(id::text, 10000)) STORED, + -- Force COPY's single-insert path with a volatile default + rand float8 not null default random() +) +PARTITION BY LIST((id % 2)); +-- Ensure that generated payloads are stored in the TOAST relations +ALTER TABLE parted_si_toast ALTER COLUMN payload SET STORAGE EXTERNAL; +CREATE TABLE parted_si_toast_even PARTITION OF parted_si_toast FOR VALUES IN (0); +CREATE TABLE parted_si_toast_odd PARTITION OF parted_si_toast FOR VALUES IN (1); +COPY parted_si_toast(id) FROM stdin; +SELECT tableoid::regclass, count(*), + bool_and(payload = repeat(id::text, 10000)) AS payload_ok + FROM parted_si_toast GROUP BY 1 ORDER BY 1; + tableoid | count | payload_ok +----------------------+-------+------------ + parted_si_toast_even | 2 | t + parted_si_toast_odd | 2 | t +(2 rows) + +DROP TABLE parted_si_toast; +-- Check that a TOAST bulk insertion state survives a per-tuple context reset +-- between multi-insert flushes. +CREATE TABLE parted_mi_toast ( + id int not null, + payload text GENERATED ALWAYS AS (repeat(id::text, 10000)) STORED +) +PARTITION BY LIST((id % 2)); +ALTER TABLE parted_mi_toast ALTER COLUMN payload SET STORAGE EXTERNAL; +CREATE TABLE parted_mi_toast_even PARTITION OF parted_mi_toast FOR VALUES IN (0); +CREATE TABLE parted_mi_toast_odd PARTITION OF parted_mi_toast FOR VALUES IN (1); +CREATE TRIGGER part_ins_trig + BEFORE INSERT ON parted_mi_toast_odd + FOR EACH ROW EXECUTE FUNCTION part_ins_func(); +COPY parted_mi_toast(id) FROM stdin; +SELECT count(*), bool_and(payload = repeat(id::text, 10000)) AS payload_ok + FROM parted_mi_toast; + count | payload_ok +-------+------------ + 3 | t +(1 row) + +DROP TABLE parted_mi_toast; -- ensure COPY FREEZE errors for foreign tables begin; create foreign data wrapper copytest_wrapper; diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 8b15471248b..1f041617aee 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1900,6 +1900,62 @@ SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_ext t (1 row) +-- Test that TOAST relations use the BAS_BULKWRITE strategy during single and +-- multi insert COPY. +CREATE TABLE test_io_bulkwrite_toast ( + id int, + payload text NOT NULL DEFAULT repeat('x', 10000), + force_single_insert float8 DEFAULT random() +); +ALTER TABLE test_io_bulkwrite_toast ALTER COLUMN payload SET STORAGE EXTERNAL; +-- Make COPY reuse the main relation's first page, so that relation extensions +-- measured below belong to the TOAST relation. +INSERT INTO test_io_bulkwrite_toast (id, payload) VALUES (-1, 'x'); +-- The volatile default makes COPY use the single insert path +SELECT sum(extends) AS io_sum_bulkwrite_toast_single_extends_before + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +COPY test_io_bulkwrite_toast(id) FROM stdin; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT sum(extends) AS io_sum_bulkwrite_toast_single_extends_after + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +SELECT :io_sum_bulkwrite_toast_single_extends_after > + :io_sum_bulkwrite_toast_single_extends_before; + ?column? +---------- + t +(1 row) + +-- Dropping the volatile default makes COPY use the multi insert path +ALTER TABLE test_io_bulkwrite_toast + ALTER COLUMN force_single_insert DROP DEFAULT; +SELECT sum(extends) AS io_sum_bulkwrite_toast_multi_extends_before + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +COPY test_io_bulkwrite_toast(id) FROM stdin; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT sum(extends) AS io_sum_bulkwrite_toast_multi_extends_after + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +SELECT :io_sum_bulkwrite_toast_multi_extends_after > + :io_sum_bulkwrite_toast_multi_extends_before; + ?column? +---------- + t +(1 row) + +DROP TABLE test_io_bulkwrite_toast; -- Test IO stats reset SELECT pg_stat_have_stats('io', 0, 0); pg_stat_have_stats diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql index 6d3923fb3f0..d63ba5f9ced 100644 --- a/src/test/regress/sql/copy.sql +++ b/src/test/regress/sql/copy.sql @@ -524,6 +524,59 @@ SELECT tableoid::regclass, id % 2 = 0 is_even, count(*) from parted_si GROUP BY DROP TABLE parted_si; +-- Test partition switching with toasted rows in COPY's single-insert path +CREATE TABLE parted_si_toast ( + id int not null, + payload text GENERATED ALWAYS AS (repeat(id::text, 10000)) STORED, + -- Force COPY's single-insert path with a volatile default + rand float8 not null default random() +) +PARTITION BY LIST((id % 2)); + +-- Ensure that generated payloads are stored in the TOAST relations +ALTER TABLE parted_si_toast ALTER COLUMN payload SET STORAGE EXTERNAL; +CREATE TABLE parted_si_toast_even PARTITION OF parted_si_toast FOR VALUES IN (0); +CREATE TABLE parted_si_toast_odd PARTITION OF parted_si_toast FOR VALUES IN (1); + +COPY parted_si_toast(id) FROM stdin; +0 +1 +2 +3 +\. + +SELECT tableoid::regclass, count(*), + bool_and(payload = repeat(id::text, 10000)) AS payload_ok + FROM parted_si_toast GROUP BY 1 ORDER BY 1; + +DROP TABLE parted_si_toast; + +-- Check that a TOAST bulk insertion state survives a per-tuple context reset +-- between multi-insert flushes. +CREATE TABLE parted_mi_toast ( + id int not null, + payload text GENERATED ALWAYS AS (repeat(id::text, 10000)) STORED +) +PARTITION BY LIST((id % 2)); + +ALTER TABLE parted_mi_toast ALTER COLUMN payload SET STORAGE EXTERNAL; +CREATE TABLE parted_mi_toast_even PARTITION OF parted_mi_toast FOR VALUES IN (0); +CREATE TABLE parted_mi_toast_odd PARTITION OF parted_mi_toast FOR VALUES IN (1); +CREATE TRIGGER part_ins_trig + BEFORE INSERT ON parted_mi_toast_odd + FOR EACH ROW EXECUTE FUNCTION part_ins_func(); + +COPY parted_mi_toast(id) FROM stdin; +0 +1 +2 +\. + +SELECT count(*), bool_and(payload = repeat(id::text, 10000)) AS payload_ok + FROM parted_mi_toast; + +DROP TABLE parted_mi_toast; + -- ensure COPY FREEZE errors for foreign tables begin; create foreign data wrapper copytest_wrapper; diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 674637e172b..7c11c07b4ab 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -902,6 +902,56 @@ SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_after FROM pg_stat_io WHERE context = 'bulkwrite' \gset SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_extends_before; +-- Test that TOAST relations use the BAS_BULKWRITE strategy during single and +-- multi insert COPY. +CREATE TABLE test_io_bulkwrite_toast ( + id int, + payload text NOT NULL DEFAULT repeat('x', 10000), + force_single_insert float8 DEFAULT random() +); +ALTER TABLE test_io_bulkwrite_toast ALTER COLUMN payload SET STORAGE EXTERNAL; + +-- Make COPY reuse the main relation's first page, so that relation extensions +-- measured below belong to the TOAST relation. +INSERT INTO test_io_bulkwrite_toast (id, payload) VALUES (-1, 'x'); + +-- The volatile default makes COPY use the single insert path +SELECT sum(extends) AS io_sum_bulkwrite_toast_single_extends_before + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +COPY test_io_bulkwrite_toast(id) FROM stdin; +0 +1 +2 +3 +\. +SELECT pg_stat_force_next_flush(); +SELECT sum(extends) AS io_sum_bulkwrite_toast_single_extends_after + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +SELECT :io_sum_bulkwrite_toast_single_extends_after > + :io_sum_bulkwrite_toast_single_extends_before; + +-- Dropping the volatile default makes COPY use the multi insert path +ALTER TABLE test_io_bulkwrite_toast + ALTER COLUMN force_single_insert DROP DEFAULT; +SELECT sum(extends) AS io_sum_bulkwrite_toast_multi_extends_before + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +COPY test_io_bulkwrite_toast(id) FROM stdin; +4 +5 +6 +7 +\. +SELECT pg_stat_force_next_flush(); +SELECT sum(extends) AS io_sum_bulkwrite_toast_multi_extends_after + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +SELECT :io_sum_bulkwrite_toast_multi_extends_after > + :io_sum_bulkwrite_toast_multi_extends_before; +DROP TABLE test_io_bulkwrite_toast; + -- Test IO stats reset SELECT pg_stat_have_stats('io', 0, 0); SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset -- 2.34.1
>From 368688a89a80e34d340758a8854a4b4e1d16818d Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 15 Oct 2026 11:54:57 +0000 Subject: [PATCH v1 4/4] Use bulk-write strategy for TOAST during heap rewrites Heap rewrites use a separate insertion path, so their TOAST writes do not benefit from preceding commit XXXX. 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. Add a VACUUM FULL test that checks TOAST extensions are counted in the bulkwrite I/O context. Reported-by: Justin Pryzby <[email protected]> Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ Discussion: https://postgr.es/m/20220622143841.GS22452%40telsasoft.com --- src/backend/access/heap/rewriteheap.c | 17 +++++++++++++- src/test/regress/expected/stats.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/stats.sql | 20 ++++++++++++++++ 3 files changed, 69 insertions(+), 1 deletion(-) 24.5% src/backend/access/heap/ 41.0% src/test/regress/expected/ 34.4% src/test/regress/sql/ diff --git a/src/backend/access/heap/rewriteheap.c b/src/backend/access/heap/rewriteheap.c index 90ddfd11908..438dedc6c51 100644 --- a/src/backend/access/heap/rewriteheap.c +++ b/src/backend/access/heap/rewriteheap.c @@ -133,6 +133,8 @@ typedef struct RewriteStateData Relation rs_old_rel; /* source heap */ Relation rs_new_rel; /* destination heap */ BulkWriteState *rs_bulkstate; /* writer for the destination */ + BufferAccessStrategy rs_toast_strategy; /* ring for TOAST writes */ + BulkInsertState rs_toast_bistate; /* parent state for TOAST writes */ BulkWriteBuffer rs_buffer; /* page currently being built */ BlockNumber rs_blockno; /* block where page will go */ bool rs_logical_rewrite; /* do we need to do logical rewriting */ @@ -320,6 +322,11 @@ end_heap_rewrite(RewriteState state) } smgr_bulk_finish(state->rs_bulkstate); + if (state->rs_toast_bistate != NULL) + { + FreeBulkInsertState(state->rs_toast_bistate); + FreeAccessStrategy(state->rs_toast_strategy); + } logical_end_heap_rewrite(state); @@ -627,8 +634,16 @@ raw_heap_insert(RewriteState state, HeapTuple tup) */ options |= HEAP_INSERT_NO_LOGICAL; + if (state->rs_toast_bistate == NULL) + { + state->rs_toast_strategy = GetAccessStrategy(BAS_BULKWRITE); + state->rs_toast_bistate = GetBulkInsertState(state->rs_new_rel, + state->rs_toast_strategy); + } + heaptup = heap_toast_insert_or_update(state->rs_new_rel, tup, NULL, - options, NULL); + options, + state->rs_toast_bistate); } else heaptup = tup; diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 1f041617aee..62308fe6c2b 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1955,6 +1955,39 @@ SELECT :io_sum_bulkwrite_toast_multi_extends_after > t (1 row) +-- Check that a TOAST bulk insertion state survives per-tuple context resets +-- during a table rewrite. +ALTER TABLE test_io_bulkwrite_toast + ADD COLUMN rewrite_id int GENERATED ALWAYS AS (id) STORED; +SELECT count(*), bool_and(rewrite_id = id) AS rewrite_ok + FROM test_io_bulkwrite_toast; + count | rewrite_ok +-------+------------ + 9 | t +(1 row) + +-- Test that TOAST writes performed during a heap rewrite use the +-- BAS_BULKWRITE strategy. +SELECT sum(extends) AS io_sum_rewrite_toast_extends_before + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +VACUUM FULL test_io_bulkwrite_toast; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT sum(extends) AS io_sum_rewrite_toast_extends_after + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +SELECT :io_sum_rewrite_toast_extends_after > + :io_sum_rewrite_toast_extends_before; + ?column? +---------- + t +(1 row) + DROP TABLE test_io_bulkwrite_toast; -- Test IO stats reset SELECT pg_stat_have_stats('io', 0, 0); diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 7c11c07b4ab..41702fc76c3 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -950,6 +950,26 @@ SELECT sum(extends) AS io_sum_bulkwrite_toast_multi_extends_after WHERE context = 'bulkwrite' \gset SELECT :io_sum_bulkwrite_toast_multi_extends_after > :io_sum_bulkwrite_toast_multi_extends_before; + +-- Check that a TOAST bulk insertion state survives per-tuple context resets +-- during a table rewrite. +ALTER TABLE test_io_bulkwrite_toast + ADD COLUMN rewrite_id int GENERATED ALWAYS AS (id) STORED; +SELECT count(*), bool_and(rewrite_id = id) AS rewrite_ok + FROM test_io_bulkwrite_toast; + +-- Test that TOAST writes performed during a heap rewrite use the +-- BAS_BULKWRITE strategy. +SELECT sum(extends) AS io_sum_rewrite_toast_extends_before + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +VACUUM FULL test_io_bulkwrite_toast; +SELECT pg_stat_force_next_flush(); +SELECT sum(extends) AS io_sum_rewrite_toast_extends_after + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'bulkwrite' \gset +SELECT :io_sum_rewrite_toast_extends_after > + :io_sum_rewrite_toast_extends_before; DROP TABLE test_io_bulkwrite_toast; -- Test IO stats reset -- 2.34.1
