Subject: Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM

From: Jingtang Zhang <[email-masked]>
Date: Tue, 29 Oct 2024 11:18:42 +0800

Hi~

Sorry for multiple comments in separate mail. Just found that the initialization
seems redundant since we have used palloc0?

> +        istate = (HeapInsertState *) palloc0(sizeof(HeapInsertState));
> +        istate->bistate = NULL;
> +        istate->mistate = NULL;

---
Regards, Jingtang

---

From: Bharath Rupireddy <[email-masked]>
Date: Wed, 30 Oct 2024 10:51:23 -0700

Hi,

Thanks for looking into this.

On Mon, Oct 28, 2024 at 8:18 PM Jingtang Zhang <[email-masked]> wrote:
>
> Just found that the initialization

Changed it to palloc() and explicit initializations of the members.
With this, only TupleTableSlot's array in HeapMultiInsertState uses
palloc0(), the rest all use explicit initializations.

> Oh, another comments for v24-0001 patch: we are in heam AM now, should we use
> something like HEAP_INSERT_BAS_BULKWRITE instead of using table AM option,
> just like other heap AM options do?
>
> > +             if ((state->options & TABLE_INSERT_BAS_BULKWRITE) != 0)
> > +                     istate->bistate = GetBulkInsertState();

Defined HEAP_INSERT_BAS_BULKWRITE and used that in heapam.c similar to
INSERT_SKIP_FSM, INSERT_FROZEN, NO_LOGICAL.

> Little question about v24 0002 patch: would it be better to move the
> implementation of TableModifyIsMultiInsertsSupported to somewhere for table AM
> level? Seems it is a common function for future use, not a specific one for
> matview.

It's more tailored for CREATE TABLE AS and CREATE/REFRESH MATERIALIZED
VIEW in the sense that no triggers, foreign table and partitioned
table possible here. INSERT INTO SELECT and Logical Replication Apply
will have a lot more conditions (e.g. RETURNING clause, triggers etc.)
and they will need to be handled differently. So, I left
TableModifyIsMultiInsertsSupported as-is in a common place in
matview.c.

Please find the attached v25 patch set.

-- 
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

---

From: Jingtang Zhang <[email-masked]>
Date: Thu, 31 Oct 2024 12:17:14 +0800

Hi~

I did some performance test these days, and I have some findings.

From the archive months ago, I found there were discussions about which type
of TupleTableSlot to use for buffering tuples. A single column mat view was
used for evaluation. Finally we used virtual one.

However when I test with a 32-columns mat view, I get regression.

Test case:

-- prepare
create table test as 
  select
    i as id0,
    i + 1 as id1,
    i + 2 as id2,
    i + 3 as id3,
    i + 4 as id4,
    i + 5 as id5,
    i + 6 as id6,
    i + 7 as id7,
    i + 8 as id8,
    i + 9 as id9,
    i + 10 as id10,
    i + 11 as id11,
    i + 12 as id12,
    i + 13 as id13,
    i + 14 as id14,
    i + 15 as id15,
    i + 0.01 as f0,
    i + 0.1 as f1,
    i + 0.2 as f2,
    i + 0.3 as f3,
    i + 0.4 as f4,
    i + 0.5 as f5,
    i + 0.6 as f6,
    i + 0.7 as f7,
    i + 0.8 as f8,
    i + 0.9 as f9,
    i + 1.01 as f10,
    i + 1.1 as f11,
    i + 1.2 as f12,
    i + 1.3 as f13,
    i + 1.4 as f14,
    i + 1.5 as f15,
    i + 1.6 as f16
  from generate_series(1,5000000) i;

-- run
create materialized view m1 as select * from test;

HEAD:
Time: 13615.542 ms (00:13.616)
Time: 13545.706 ms (00:13.546)
Time: 13578.475 ms (00:13.578)

Patched
Time: 20112.734 ms (00:20.113)
Time: 19996.957 ms (00:19.997)
Time: 19936.871 ms (00:19.937)

I did a quick perf, the overhead seems to come from virtual tuple materialization.

HEAD:
  12.29%  postgres            [.] pg_checksum_block
   6.33%  postgres            [.] GetPrivateRefCountEntry
   5.40%  postgres            [.] pg_comp_crc32c_sse42
   4.54%  [kernel]            [k] copy_user_enhanced_fast_string
   2.69%  postgres            [.] BufferIsValid
   1.52%  postgres            [.] XLogRecordAssemble

Patched:
  11.75%  postgres            [.] tts_virtual_materialize
   8.87%  postgres            [.] pg_checksum_block
   8.17%  postgres            [.] slot_deform_heap_tuple
   8.09%  postgres            [.] heap_compute_data_size
   6.17%  postgres            [.] fill_val
   3.81%  postgres            [.] heap_fill_tuple
   3.37%  postgres            [.] tts_virtual_copyslot
   2.62%  [kernel]            [k] copy_user_enhanced_fast_string

Not sure if it is a universal situation.

—
Regards, Jingtang

---

From: Daniil Davydov <[email-masked]>
Date: Sun, 9 Mar 2025 18:27:58 +0700

Hi,
A few days ago I came up with an idea to implement multi insert
optimization wherever possible. I prepared a raw patch
and it showed a great performance gain (up to 4 times during INSERT
... INTO ... in the best case).
Then I was very happy to find this thread. You did a great job and I
want to help you to bring the matter to an end.

>    2.62%  [kernel]            [k] copy_user_enhanced_fast_string

I applied v25 patches on the master branch and made some measurements
to find out what is the bottleneck in this case. The 'time' utility
showed that without a patch, this query will run 1.5 times slower. I
also made a few flamegraphs for this test. Most of the time is spent
calling
these two functions : tts_virtual_copyslot and heap_form_tuple.
All tests were run in virtual machine with these CPU characteristics:
Architecture:             x86_64
CPU(s):                   2
  On-line CPU(s) list:    0,1
Virtualization features:
  Virtualization:         AMD-V
  Hypervisor vendor:      KVM
  Virtualization type:    full
Caches (sum of all):
  L1d:                    128 KiB (2 instances)
  L1i:                    128 KiB (2 instances)
  L2:                     1 MiB (2 instances)
  L3:                     32 MiB (2 instances)
NUMA:
  NUMA node(s):           1
  NUMA node0 CPU(s):      0,1

In my implementation, I used Tuplestore functionality to store tuples.
In order to get rid of getting stuck in the above mentioned functions,
I crossed it with the current implementation (v25 patches) and got a
10% increase in performance (for the test above). I also set up v22
patches to
compare performance (with/without tuplestore) for INSERT ... INTO ...
queries (with -j 4 -c 10 parameters for pgbech), and there also was an
increase in TPS (about 3-4%).

I attach a patch that adds Tuplestore to v25. What do you think about this idea?

--
Best regards,
Daniil Davydov
From a59cfcbb05bb07c94a4c0ad6531baa5e531629ae Mon Sep 17 00:00:00 2001
From: Daniil Davidov <[email-masked]>
Date: Sun, 9 Mar 2025 16:37:44 +0700
Subject: [PATCH] Replace holding tuples in virtual slots with tuplestorage

During performance testing, it was found out that in the current
implementation a lot of the program's time is spent calling two functions :
tts_virtual_copyslot and heap_fill_tuple. Calls to these functions are related
to the fact that tuples are stored in virtual_tts, so I propose to replace this
logic with Tuplestore functionality.

Discussion: https://www.postgresql.org/message-id/9F9326B4-8AD9-4858-B1C1-559FC64E6E93%40gmail.com
---
 src/backend/access/heap/heapam.c | 67 +++++++++++++++-----------------
 src/include/access/heapam.h      |  9 ++++-
 2 files changed, 38 insertions(+), 38 deletions(-)

diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index acdce1a4b4..276480213a 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -2665,7 +2665,6 @@ void
 heap_modify_buffer_insert(TableModifyState *state,
 						  TupleTableSlot *slot)
 {
-	TupleTableSlot *dstslot;
 	HeapInsertState *istate;
 	HeapMultiInsertState *mistate;
 	MemoryContext oldcontext;
@@ -2682,8 +2681,10 @@ heap_modify_buffer_insert(TableModifyState *state,
 		mistate =
 			(HeapMultiInsertState *) palloc(sizeof(HeapMultiInsertState));
 		mistate->slots =
-			(TupleTableSlot **) palloc0(sizeof(TupleTableSlot *) * HEAP_MAX_BUFFERED_SLOTS);
-		mistate->cur_slots = 0;
+			(TupleTableSlot **) palloc0(sizeof(void *) * HEAP_MAX_BUFFERED_SLOTS);
+		mistate->tstore = tuplestore_begin_heap(false, false, work_mem);
+		mistate->nused = 0;
+
 		istate->mistate = mistate;
 
 		/*
@@ -2702,36 +2703,11 @@ heap_modify_buffer_insert(TableModifyState *state,
 	istate = (HeapInsertState *) state->data;
 	Assert(istate->mistate != NULL);
 	mistate = istate->mistate;
-	dstslot = mistate->slots[mistate->cur_slots];
-
-	if (dstslot == NULL)
-	{
-		/*
-		 * We use virtual tuple slots buffered slots for leveraging the
-		 * optimization it provides to minimize physical data copying. The
-		 * virtual slot gets materialized when we copy (via below
-		 * ExecCopySlot) the tuples from the source slot which can be of any
-		 * type. This way, it is ensured that the tuple storage doesn't depend
-		 * on external memory, because all the datums that aren't passed by
-		 * value are copied into the slot's memory context.
-		 */
-		dstslot = MakeTupleTableSlot(RelationGetDescr(state->rel),
-									 &TTSOpsVirtual);
-
-		mistate->slots[mistate->cur_slots] = dstslot;
-	}
-
-	Assert(TTS_IS_VIRTUAL(dstslot));
-
-	/*
-	 * Note that the copy clears the previous destination slot contents, so no
-	 * need to explicitly ExecClearTuple() here.
-	 */
-	ExecCopySlot(dstslot, slot);
 
-	mistate->cur_slots++;
+	tuplestore_puttupleslot(mistate->tstore, slot);
+	mistate->nused += 1;
 
-	if (mistate->cur_slots >= HEAP_MAX_BUFFERED_SLOTS)
+	if (mistate->nused >= HEAP_MAX_BUFFERED_SLOTS)
 		heap_modify_buffer_flush(state);
 
 	MemoryContextSwitchTo(oldcontext);
@@ -2746,19 +2722,35 @@ heap_modify_buffer_flush(TableModifyState *state)
 	HeapInsertState *istate;
 	HeapMultiInsertState *mistate;
 	MemoryContext oldcontext;
+	TupleDesc tupdesc;
 
 	/* Quick exit if we haven't inserted anything yet */
 	if (state->data == NULL)
 		return;
 
+	tupdesc = RelationGetDescr(state->rel);
 	istate = (HeapInsertState *) state->data;
 	Assert(istate->mistate != NULL);
 	mistate = istate->mistate;
 
 	/* Quick exit if we have flushed already */
-	if (mistate->cur_slots == 0)
+	if (mistate->nused == 0)
 		return;
 
+	for (int i = 0; i < mistate->nused; i++)
+	{
+		bool ok;
+
+		if (istate->mistate->slots[i] == NULL)
+		{
+			istate->mistate->slots[i] =
+				MakeSingleTupleTableSlot(tupdesc, &TTSOpsMinimalTuple);
+		}
+		ok = tuplestore_gettupleslot(mistate->tstore, true, false,
+									 istate->mistate->slots[i]);
+		Assert(ok);
+	}
+
 	/*
 	 * heap_multi_insert() can leak memory, so switch to short-lived memory
 	 * context before calling it.
@@ -2766,7 +2758,7 @@ heap_modify_buffer_flush(TableModifyState *state)
 	oldcontext = MemoryContextSwitchTo(mistate->mem_ctx);
 	heap_multi_insert(state->rel,
 					  mistate->slots,
-					  mistate->cur_slots,
+					  mistate->nused,
 					  state->cid,
 					  state->options,
 					  istate->bistate);
@@ -2779,14 +2771,15 @@ heap_modify_buffer_flush(TableModifyState *state)
 	 */
 	if (state->buffer_flush_cb != NULL)
 	{
-		for (int i = 0; i < mistate->cur_slots; i++)
+		for (int i = 0; i < mistate->nused; i++)
 		{
 			state->buffer_flush_cb(state->buffer_flush_ctx,
 								   mistate->slots[i]);
 		}
 	}
 
-	mistate->cur_slots = 0;
+	tuplestore_clear(mistate->tstore);
+	mistate->nused = 0;
 }
 
 /*
@@ -2811,11 +2804,13 @@ heap_modify_insert_end(TableModifyState *state)
 
 		heap_modify_buffer_flush(state);
 
-		Assert(mistate->cur_slots == 0);
+		Assert(mistate->nused== 0);
 
 		for (int i = 0; i < HEAP_MAX_BUFFERED_SLOTS && mistate->slots[i] != NULL; i++)
 			ExecDropSingleTupleTableSlot(mistate->slots[i]);
 
+		tuplestore_end(mistate->tstore);
+
 		MemoryContextDelete(mistate->mem_ctx);
 	}
 
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index fdbbf9b8e8..5d8e672059 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -27,8 +27,10 @@
 #include "storage/lockdefs.h"
 #include "storage/read_stream.h"
 #include "storage/shm_toc.h"
+#include "tcop/dest.h"
 #include "utils/relcache.h"
 #include "utils/snapshot.h"
+#include "utils/tuplestore.h"

 /* "options" flag bits for heap_insert */
@@ -285,8 +287,11 @@ typedef struct HeapMultiInsertState
 	/* Array of buffered slots */
 	TupleTableSlot **slots;
 
-	/* Number of buffered slots currently held */
-	int			cur_slots;
+	/* Holds the tuple set */
+	Tuplestorestate *tstore;
+
+	/* Number of buffered tuples currently held */
+	int				nused;
 
 	/* Memory context for dealing with multi inserts */
 	MemoryContext mem_ctx;
-- 
2.43.0

---

From: Daniil Davydov <[email-masked]>
Date: Mon, 17 Mar 2025 11:49:45 +0700

Hi,
Recently I took more careful measurements of the performance. I
compared three branches with each other: HEAD, Patched and Patched
with tuplestore.
Here are the results :

1)
Test case : matview creation test attached in the email from Jingtang Zhang.
10 measurements for each branch.
Result in wall clock execution time :

HEAD
30.532 +- 0.59 seconds elapsed
Patched
20.454 +- 0.114 seconds elapsed
Patched with tuplestore
19.653 +- 0.111 seconds elapsed

2)
-- init.sql
drop table test_insert;
vacuum;
checkpoint;
create table test_insert(i int, f float);

-- iowrite.sql
insert into test_insert select g, (g % 100) / 100.0 from
generate_series(1, 1000000) as g;

Test case :
pgbench -f iowrite.sql -n -j 4 -c 10 -T 40
5 measurements for each branch.
Result in tps :

HEAD
1.025 +- 0.009
Patched
2.923 +- 0.032
Patched with tuplestore
2.987 +- 0.011

P.S.
I cannot find a commitfest entry for this patch. Should we add it there?

--
Best regards,
Daniil Davydov

---

From: Jingtang Zhang <[email-masked]>
Date: Sun, 6 Apr 2025 21:54:44 +0800

Hi, Daniil Davydov.

Thanks for sharing your patch!

It was quite a while since I last looked at the patch. I've tested it again,
and still get regression on patched version where a table has many columns.
And it is totally CPU-bounded on tts_virtual_copyslot.

Unpatched version:
1 col:
Time: 8909.714 ms (00:08.910)
Time: 8803.579 ms (00:08.804)
Time: 8600.415 ms (00:08.600)
32 cols:
Time: 12911.699 ms (00:12.912)
Time: 13543.491 ms (00:13.543)
Time: 13325.368 ms (00:13.325)

Patched version:
1 col:
Time: 3532.841 ms (00:03.533)
Time: 3598.223 ms (00:03.598)
Time: 3515.858 ms (00:03.516)
32 cols:
Time: 35647.724 ms (00:35.648)
Time: 35596.233 ms (00:35.596)
Time: 35669.106 ms (00:35.669)

I've tested your patch with tuplestore and found the regression does not exist
anymore, but I haven't look deep inside it.

Patched version (with tuplestore):
1 col:
Time: 3500.502 ms (00:03.501)
Time: 3486.886 ms (00:03.487)
Time: 3514.233 ms (00:03.514)
32 cols:
Time: 10375.391 ms (00:10.375)
Time: 10248.256 ms (00:10.248)
Time: 10248.289 ms (00:10.248)

It seems to be a good idea if there is no other issue with your patch.

--
Regards,
Jingtang

---

From: Daniil Davydov <[email-masked]>
Date: Mon, 7 Apr 2025 13:26:25 +0700

Hi,

> Time: 35669.106 ms (00:35.669)

Hm, maybe I didn't choose the best way to measure performance. Can you
please share how you do it?

> It seems to be a good idea if there is no other issue with your patch.

As far as I understand, the use of multi inserts for queries like
"INSERT INTO ... SELECT FROM" is not discussed here anymore due to the
fact that in such cases we will have to take into account the volatile
functions and ROW triggers.
I've been thinking about this for a while and made a patch as an
experiment. The principles that the patch works on are listed below.
1)
Since performance decreases for single INSERTs (within a multi inserts
mechanism), I designed this feature as an option for the table. Thus,
if the user knows that he will perform a lot of inserts on the table,
he can specify "WITH (append_optimized=true)".
2)
The availability of volatile functions is monitored during the
construction of a subtree for a ModifyTable node. I'm not that
familiar with the query plan construction mechanism, but it seems to
me that this way we can track any occurrence of volatile functions.
Of course, most volatile functions don't stop us from using multi
inserts, but checking each such function would take a very long time,
so the very fact of having a volatile function is enough for us to
abandon multi-inserts.
3)
Default expressions of the target table are also checked for volatile
functions. The same rules apply to them as in (2). As an exception, I
allowed the use of SERIAL in the column data type, since this is a
fairly common use case.
4)
If the target table contains any ROW triggers, we don't use multi insert.
5)
Patch also contains a regression test. This is a "sandbox" where you
can do some experiments with append-optimized tables.

I hope that patch (targeted on 'master' branch,
2c7bd2ba507e273f2d7fe1b2f6d30775ed4f3c09) will be useful for this
thread.

--
Best regards,
Daniil Davydov
From 224378c11d270aabe28bdd32efacd37ed1984bd1 Mon Sep 17 00:00:00 2001
From: Daniil Davidov <[email-masked]>
Date: Mon, 7 Apr 2025 12:55:50 +0700
Subject: [PATCH v1] Meet append optimized tables

---
 src/backend/access/common/reloptions.c        |  11 +
 src/backend/access/heap/heapam.c              | 205 ++++++++++++++++++
 src/backend/access/heap/heapam_handler.c      |   5 +
 src/backend/access/table/tableamapi.c         |   5 +
 src/backend/commands/explain.c                |   5 +-
 src/backend/executor/execExpr.c               |  17 +-
 src/backend/executor/execProcnode.c           |   9 +
 src/backend/executor/nodeModifyTable.c        | 194 ++++++++++++++++-
 src/backend/optimizer/plan/createplan.c       |   1 +
 src/backend/optimizer/util/clauses.c          |  28 ++-
 src/include/access/heapam.h                   |  41 ++++
 src/include/access/tableam.h                  |  84 +++++++
 src/include/nodes/execnodes.h                 |   6 +
 src/include/nodes/plannodes.h                 |   2 +
 src/include/optimizer/optimizer.h             |   3 +
 src/include/utils/rel.h                       |  10 +
 .../regress/expected/append_optimized.out     | 161 ++++++++++++++
 src/test/regress/parallel_schedule            |   2 +
 src/test/regress/sql/append_optimized.sql     | 105 +++++++++
 19 files changed, 879 insertions(+), 15 deletions(-)
 create mode 100644 src/test/regress/expected/append_optimized.out
 create mode 100644 src/test/regress/sql/append_optimized.sql

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 46c1dce222d..9652cf4179b 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -166,6 +166,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"append_optimized",
+			"Enables using batching for insertion algorithm whenever it possible",
+			RELOPT_KIND_HEAP,
+			AccessExclusiveLock
+		},
+		false
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1905,6 +1914,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_truncate), offsetof(StdRdOptions, vacuum_truncate_set)},
+		{"append_optimized", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, append_optimized)},
 		{"vacuum_max_eager_freeze_failure_rate", RELOPT_TYPE_REAL,
 		offsetof(StdRdOptions, vacuum_max_eager_freeze_failure_rate)}
 	};
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index ed2e3021799..415eef4c35d 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -51,6 +51,7 @@
 #include "utils/datum.h"
 #include "utils/injection_point.h"
 #include "utils/inval.h"
+#include "utils/memutils.h"
 #include "utils/spccache.h"
 #include "utils/syscache.h"
 
@@ -106,6 +107,7 @@ static XLogRecPtr log_heap_new_cid(Relation relation, HeapTuple tup);
 static HeapTuple ExtractReplicaIdentity(Relation relation, HeapTuple tp, bool key_required,
 										bool *copy);
 
+static void heap_modify_insert_end(TableModifyState *state);
 
 /*
  * Each tuple lock mode has a corresponding heavyweight lock, and one or two
@@ -2674,6 +2676,209 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples,
 	pgstat_count_heap_insert(relation, ntuples);
 }
 
+/*
+ * Initialize heap modify state.
+ */
+TableModifyState *
+heap_modify_begin(Relation rel,
+				  CommandId cid,
+				  int options,
+				  TableModifyBufferFlushCb buffer_flush_cb,
+				  void *buffer_flush_ctx)
+{
+	TableModifyState *state;
+	MemoryContext context;
+	MemoryContext oldcontext;
+
+	Assert(RelationIsAppendOptimized(rel));
+	context = AllocSetContextCreate(TopTransactionContext,
+									"heap_modify memory context",
+									ALLOCSET_DEFAULT_SIZES);
+
+	oldcontext = MemoryContextSwitchTo(context);
+	state = palloc(sizeof(TableModifyState));
+	state->rel = rel;
+	state->cid = cid;
+	state->options = options;
+	state->mem_ctx = context;
+	state->buffer_flush_cb = buffer_flush_cb;
+	state->buffer_flush_ctx = buffer_flush_ctx;
+	state->data = NULL;			/* To be set lazily */
+	MemoryContextSwitchTo(oldcontext);
+
+	return state;
+}
+
+/*
+ * Store passed-in tuple into in-memory buffered slots. When full, insert
+ * multiple tuples from the buffers into heap.
+ */
+void
+heap_modify_buffer_insert(TableModifyState *state,
+						  TupleTableSlot *slot)
+{
+	HeapInsertState *istate;
+	HeapMultiInsertState *mistate;
+	MemoryContext oldcontext;
+
+	Assert(RelationIsAppendOptimized(state->rel));
+	oldcontext = MemoryContextSwitchTo(state->mem_ctx);
+
+	/* First time through, initialize heap insert state */
+	if (state->data == NULL)
+	{
+		istate = (HeapInsertState *) palloc(sizeof(HeapInsertState));
+		istate->bistate = NULL;
+		istate->mistate = NULL;
+		state->data = istate;
+		mistate =
+			(HeapMultiInsertState *) palloc(sizeof(HeapMultiInsertState));
+		mistate->slots =
+			(TupleTableSlot **) palloc0(sizeof(void *) * HEAP_MAX_BUFFERED_SLOTS);
+		mistate->tstore = tuplestore_begin_heap(false, false, work_mem);
+		mistate->nused = 0;
+		istate->mistate = mistate;
+
+		/*
+		 * heap_multi_insert() can leak memory. So switch to this memory
+		 * context before every heap_multi_insert() call and reset when
+		 * finished.
+		 */
+		mistate->mem_ctx = AllocSetContextCreate(CurrentMemoryContext,
+												 "heap_multi_insert memory context",
+												 ALLOCSET_DEFAULT_SIZES);
+		istate->bistate = GetBulkInsertState();
+	}
+
+	istate = (HeapInsertState *) state->data;
+	Assert(istate->mistate != NULL);
+	mistate = istate->mistate;
+
+	tuplestore_puttupleslot(mistate->tstore, slot);
+	mistate->nused += 1;
+
+	if (mistate->nused >= HEAP_MAX_BUFFERED_SLOTS)
+		heap_modify_buffer_flush(state);
+
+	MemoryContextSwitchTo(oldcontext);
+}
+
+/*
+ * Insert multiple tuples from in-memory buffered slots into heap.
+ */
+void
+heap_modify_buffer_flush(TableModifyState *state)
+{
+	HeapInsertState *istate;
+	HeapMultiInsertState *mistate;
+	MemoryContext oldcontext;
+	TupleDesc tupdesc;
+
+	Assert(RelationIsAppendOptimized(state->rel));
+
+	/* Quick exit if we haven't inserted anything yet */
+	if (state->data == NULL)
+		return;
+
+	tupdesc = RelationGetDescr(state->rel);
+	istate = (HeapInsertState *) state->data;
+	Assert(istate->mistate != NULL);
+	mistate = istate->mistate;
+
+	/* Quick exit if we have flushed already */
+	if (mistate->nused == 0)
+		return;
+
+	for (int i = 0; i < mistate->nused; i++)
+	{
+		bool ok;
+
+		if (istate->mistate->slots[i] == NULL)
+		{
+			istate->mistate->slots[i] =
+				MakeSingleTupleTableSlot(tupdesc, &TTSOpsMinimalTuple);
+		}
+		ok = tuplestore_gettupleslot(mistate->tstore, true, false,
+									 istate->mistate->slots[i]);
+		Assert(ok);
+	}
+
+	/*
+	 * heap_multi_insert() can leak memory, so switch to short-lived memory
+	 * context before calling it.
+	 */
+	oldcontext = MemoryContextSwitchTo(mistate->mem_ctx);
+	heap_multi_insert(state->rel,
+					  mistate->slots,
+					  mistate->nused,
+					  state->cid,
+					  state->options,
+					  istate->bistate);
+	MemoryContextSwitchTo(oldcontext);
+	MemoryContextReset(mistate->mem_ctx);
+
+	/*
+	 * Invoke caller-supplied buffer flush callback after inserting rows from
+	 * the buffers to heap.
+	 */
+	if (state->buffer_flush_cb != NULL)
+	{
+		for (int i = 0; i < mistate->nused; i++)
+		{
+			state->buffer_flush_cb(state->buffer_flush_ctx,
+								   mistate->slots[i]);
+		}
+	}
+
+	tuplestore_clear(mistate->tstore);
+	mistate->nused = 0;
+}
+
+/*
+ * Heap insert specific function used for performing work at the end like
+ * flushing remaining buffered tuples, cleaning up the insert state and tuple
+ * table slots used for buffered tuples etc.
+ */
+static void
+heap_modify_insert_end(TableModifyState *state)
+{
+	HeapInsertState *istate;
+
+	/* Quick exit if we haven't inserted anything yet */
+	if (state->data == NULL)
+		return;
+
+	istate = (HeapInsertState *) state->data;
+
+	if (istate->mistate != NULL)
+	{
+		HeapMultiInsertState *mistate = istate->mistate;
+
+		heap_modify_buffer_flush(state);
+
+		Assert(mistate->nused == 0);
+
+		for (int i = 0; i < HEAP_MAX_BUFFERED_SLOTS && mistate->slots[i] != NULL; i++)
+			ExecDropSingleTupleTableSlot(mistate->slots[i]);
+
+		tuplestore_end(mistate->tstore);
+		MemoryContextDelete(mistate->mem_ctx);
+	}
+
+	if (istate->bistate != NULL)
+		FreeBulkInsertState(istate->bistate);
+}
+
+/*
+ * Clean heap modify state.
+ */
+void
+heap_modify_end(TableModifyState *state)
+{
+	heap_modify_insert_end(state);
+	MemoryContextDelete(state->mem_ctx);
+}
+
 /*
  *	simple_heap_insert - insert a tuple
  *
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index ac082fefa77..56880165ed0 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2643,6 +2643,11 @@ static const TableAmRoutine heapam_methods = {
 	.tuple_update = heapam_tuple_update,
 	.tuple_lock = heapam_tuple_lock,
 
+	.tuple_modify_begin = heap_modify_begin,
+	.tuple_modify_buffer_insert = heap_modify_buffer_insert,
+	.tuple_modify_buffer_flush = heap_modify_buffer_flush,
+	.tuple_modify_end = heap_modify_end,
+
 	.tuple_fetch_row_version = heapam_fetch_row_version,
 	.tuple_get_latest_tid = heap_get_latest_tid,
 	.tuple_tid_valid = heapam_tuple_tid_valid,
diff --git a/src/backend/access/table/tableamapi.c b/src/backend/access/table/tableamapi.c
index 476663b66aa..ae30c5a21a8 100644
--- a/src/backend/access/table/tableamapi.c
+++ b/src/backend/access/table/tableamapi.c
@@ -94,6 +94,11 @@ GetTableAmRoutine(Oid amhandler)
 	Assert(routine->scan_sample_next_block != NULL);
 	Assert(routine->scan_sample_next_tuple != NULL);
 
+	Assert(routine->tuple_modify_begin != NULL);
+	Assert(routine->tuple_modify_buffer_insert != NULL);
+	Assert(routine->tuple_modify_buffer_flush != NULL);
+	Assert(routine->tuple_modify_end != NULL);
+
 	return routine;
 }
 
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index ef8aa489af8..31ce1fa7acb 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1399,7 +1399,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			switch (((ModifyTable *) plan)->operation)
 			{
 				case CMD_INSERT:
-					pname = operation = "Insert";
+					if (((ModifyTable *) plan)->canUseBatching)
+						pname = operation = "MultiInsert";
+					else
+						pname = operation = "Insert";
 					break;
 				case CMD_UPDATE:
 					pname = operation = "Update";
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..f2d3a236fbc 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -103,7 +103,11 @@ static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
 								 ErrorSaveContext *escontext, bool omit_quotes,
 								 bool exists_coerce,
 								 Datum *resv, bool *resnull);
-
+/*
+ * Every time when we find volatile function during expresstion evaluating, we
+ * must set this flag, so higher level code can process it appropriately.
+ */
+static bool volatile_func_flag = false;
 
 /*
  * ExecInitExpr: prepare an expression tree for execution
@@ -264,6 +268,9 @@ ExecInitQual(List *qual, PlanState *parent)
 	scratch.resvalue = &state->resvalue;
 	scratch.resnull = &state->resnull;
 
+	/* Reset flag indicating the presence of volatile functions in qual */
+	volatile_func_flag = false;
+
 	foreach_ptr(Expr, node, qual)
 	{
 		/* first evaluate expression */
@@ -276,6 +283,10 @@ ExecInitQual(List *qual, PlanState *parent)
 								   state->steps_len - 1);
 	}
 
+	/* Possibly update information about batch-insert-capability */
+	if (parent && !parent->has_volatile)
+		parent->has_volatile = volatile_func_flag;
+
 	/* adjust jump targets */
 	foreach_int(jump, adjust_jumps)
 	{
@@ -1193,6 +1204,10 @@ ExecInitExprRec(Expr *node, ExprState *state,
 			{
 				FuncExpr   *func = (FuncExpr *) node;
 
+				/* Higher level code will handle it */
+				if (func_volatile(func->funcid))
+					volatile_func_flag = true;
+
 				ExecInitFunc(&scratch, node,
 							 func->args, func->funcid, func->inputcollid,
 							 state);
diff --git a/src/backend/executor/execProcnode.c b/src/backend/executor/execProcnode.c
index f5f9cfbeead..2383ef7ea4b 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -416,6 +416,15 @@ ExecInitNode(Plan *node, EState *estate, int eflags)
 		result->instrument = InstrAlloc(1, estate->es_instrument,
 										result->async_capable);
 
+	/* Check whether some nodes below has volatile functions */
+	if ((outerPlanState(result) != NULL &&
+		 outerPlanState(result)->has_volatile) ||
+		(innerPlanState(result) != NULL &&
+		 innerPlanState(result)->has_volatile))
+	{
+		result->has_volatile = true;
+	}
+
 	return result;
 }
 
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 309e27f8b5f..bbaf91bcbac 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -55,6 +55,7 @@
 #include "access/htup_details.h"
 #include "access/tableam.h"
 #include "access/xact.h"
+#include "catalog/pg_proc.h"
 #include "commands/trigger.h"
 #include "executor/execPartition.h"
 #include "executor/executor.h"
@@ -67,6 +68,8 @@
 #include "storage/lmgr.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
+#include "utils/fmgroids.h"
+#include "utils/lsyscache.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
 
@@ -130,6 +133,18 @@ typedef struct UpdateContext
 	LockTupleMode lockmode;
 } UpdateContext;
 
+typedef struct InsertModifyBufferFlushContext
+{
+	ResultRelInfo		*resultRelInfo;
+	EState				*estate;
+	ModifyTableState	*mtstate;
+}		InsertModifyBufferFlushContext;
+
+static InsertModifyBufferFlushContext *insert_modify_buffer_flush_context = NULL;
+static TableModifyState *table_modify_state = NULL;
+
+static void InsertModifyBufferFlushCallback(void *context,
+											TupleTableSlot *slot);
 
 static void ExecBatchInsert(ModifyTableState *mtstate,
 							ResultRelInfo *resultRelInfo,
@@ -174,6 +189,8 @@ static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
 										   ResultRelInfo *resultRelInfo,
 										   bool canSetTag);
 
+static bool ContainVolatileFunctionsChecker(Oid func_id, void *context);
+static bool IsMultiInsertCapable(ModifyTableState *mtstate);
 
 /*
  * Verify that the tuples to be produced by INSERT match the
@@ -806,6 +823,31 @@ ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
 	return ExecProject(newProj);
 }
 
+static void
+InsertModifyBufferFlushCallback(void *context, TupleTableSlot *slot)
+{
+	InsertModifyBufferFlushContext *ctx = (InsertModifyBufferFlushContext *) context;
+	ResultRelInfo *resultRelInfo = ctx->resultRelInfo;
+	EState	   *estate = ctx->estate;
+
+	/* Caller must take care of opening and closing the indices */
+
+	/*
+	 * If there are any indexes, update them for all the inserted tuples, and
+	 * run AFTER ROW INSERT triggers.
+	 */
+	if (resultRelInfo->ri_NumIndices > 0)
+	{
+		List	   *recheckIndexes;
+
+		recheckIndexes =
+			ExecInsertIndexTuples(resultRelInfo,
+								  slot, estate, false,
+								  false, NULL, NIL, false);
+		list_free(recheckIndexes);
+	}
+}
+
 /* ----------------------------------------------------------------
  *		ExecInsert
  *
@@ -1209,17 +1251,22 @@ ExecInsert(ModifyTableContext *context,
 		}
 		else
 		{
-			/* insert the tuple normally */
-			table_tuple_insert(resultRelationDesc, slot,
-							   estate->es_output_cid,
-							   0, NULL);
-
-			/* insert index entries for tuple */
-			if (resultRelInfo->ri_NumIndices > 0)
-				recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
-													   slot, estate, false,
-													   false, NULL, NIL,
-													   false);
+			if (table_modify_state != NULL)
+				table_modify_buffer_insert(table_modify_state, slot);
+			else
+			{
+				/* insert the tuple normally */
+				table_tuple_insert(resultRelationDesc, slot,
+								   estate->es_output_cid,
+								   0, NULL);
+
+				/* insert index entries for tuple */
+				if (resultRelInfo->ri_NumIndices > 0)
+					recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
+														   slot, estate, false,
+														   false, NULL, NIL,
+														   false);
+			}
 		}
 	}
 
@@ -4586,6 +4633,13 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	mtstate->mt_mergeActionLists = mergeActionLists;
 	mtstate->mt_mergeJoinConditions = mergeJoinConditions;
 
+	/*
+	 * Previous ModifyTable node execution (if any) should have released
+	 * these resources.
+	 */
+	Assert(insert_modify_buffer_flush_context == NULL &&
+		   table_modify_state == NULL);
+
 	/*----------
 	 * Resolve the target relation. This is the same as:
 	 *
@@ -4999,6 +5053,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	 *
 	 * We only do this for INSERT, so that for UPDATE/DELETE the batch size
 	 * remains set to 0.
+	 *
+	 * Also determine whether we can use batching for this INSERT command.
 	 */
 	if (operation == CMD_INSERT)
 	{
@@ -5016,6 +5072,27 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 		}
 		else
 			resultRelInfo->ri_BatchSize = 1;
+
+		if (IsMultiInsertCapable(mtstate))
+		{
+			insert_modify_buffer_flush_context =
+				(InsertModifyBufferFlushContext *) palloc0(sizeof(InsertModifyBufferFlushContext));
+			insert_modify_buffer_flush_context->resultRelInfo = resultRelInfo;
+			insert_modify_buffer_flush_context->estate = estate;
+			insert_modify_buffer_flush_context->mtstate = mtstate;
+
+			Assert(estate->es_output_cid != InvalidCommandId);
+
+			table_modify_state =
+				table_modify_begin(resultRelInfo->ri_RelationDesc,
+								   estate->es_output_cid,
+								   0,
+								   InsertModifyBufferFlushCallback,
+								   insert_modify_buffer_flush_context);
+
+		/* For more accurate EXPLAIN output */
+		node->canUseBatching = true;
+		}
 	}
 
 	/*
@@ -5034,6 +5111,90 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	return mtstate;
 }
 
+/*
+ * Returns true if batch insert can be performed in table whithin current query.
+ * We impose the following rules:
+ * 1) Batching is supported only for ordinary tables without ROW triggers
+ * 	  and with append_optimized option set.
+ * 2) Batching is not supported for queries, containing RETURNING clause.
+ * 3) Batching is not supported for queries, containing any volatile
+ *	  functions in plan tree.
+ * 4) Batching is supported only for tables, that hasn't volatile default
+ *	  expressions.
+ */
+static bool
+IsMultiInsertCapable(ModifyTableState *mtstate)
+{
+	ResultRelInfo *relinfo = mtstate->resultRelInfo;
+	TupleDesc tdesc = RelationGetDescr(relinfo->ri_RelationDesc);
+	bool has_row_triggers;
+
+	Assert(mtstate->operation == CMD_INSERT);
+
+	has_row_triggers =
+		(relinfo->ri_TrigDesc != NULL &&
+		(relinfo->ri_TrigDesc->trig_insert_after_row ||
+		 relinfo->ri_TrigDesc->trig_insert_before_row ||
+		 relinfo->ri_TrigDesc->trig_insert_instead_row));
+
+	/* Check (1) - (3) conditions. */
+	if (!RelationIsAppendOptimized(relinfo->ri_RelationDesc) ||
+		relinfo->ri_projectReturning ||
+		has_row_triggers)
+	{
+		return false;
+	}
+
+	/* Check last condition. */
+
+	/*
+	 * By default, this variable is calculated in the end of ExecInitNode
+	 * processing, but we need it now.
+	 */
+	if ((outerPlanState(mtstate) != NULL &&
+		 outerPlanState(mtstate)->has_volatile) ||
+		(innerPlanState(mtstate) != NULL &&
+		 innerPlanState(mtstate)->has_volatile))
+	{
+		mtstate->ps.has_volatile = true;
+		return false;
+	}
+
+	for (AttrNumber i = 0; i < tdesc->natts; i++)
+	{
+		Node *defexpr;
+		if (!TupleDescAttr(tdesc, i)->atthasdef)
+			continue;
+
+		defexpr = TupleDescGetDefault(tdesc, i + 1);
+		if (contain_volatile_functions_extended(defexpr,
+												ContainVolatileFunctionsChecker))
+		{
+			return false;
+		}
+	}
+
+	/* All conditions are met - we can perform batch insert on table. */
+	return true;
+}
+
+/*
+ * Supportive function for IsMultiInsertCapable.
+ *
+ * To decide whether we can use batching, we should iterate across all default
+ * expressions in target table and check if they contain any volatile functions.
+ *
+ * But not all functions are considered dangerous in terms of batching. We can
+ * allow some volatile functions to appear in default expressions. For now, we
+ * only allow to use nextval (in order not to dismiss batching if target table
+ * has SERIAL filed).
+ */
+static bool ContainVolatileFunctionsChecker(Oid func_id, void *context)
+{
+	return (func_volatile(func_id) == PROVOLATILE_VOLATILE &&
+			func_id != F_NEXTVAL);
+}
+
 /* ----------------------------------------------------------------
  *		ExecEndModifyTable
  *
@@ -5047,6 +5208,17 @@ ExecEndModifyTable(ModifyTableState *node)
 {
 	int			i;
 
+	if (table_modify_state != NULL)
+	{
+		Assert(node->operation == CMD_INSERT);
+
+		table_modify_end(table_modify_state);
+		table_modify_state = NULL;
+
+		pfree(insert_modify_buffer_flush_context);
+		insert_modify_buffer_flush_context = NULL;
+	}
+
 	/*
 	 * Allow any FDWs to shut down
 	 */
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a8f22a8c154..7bf13de1e93 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -7133,6 +7133,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
 
 	node->operation = operation;
 	node->canSetTag = canSetTag;
+	node->canUseBatching = false;
 	node->nominalRelation = nominalRelation;
 	node->rootRelation = rootRelation;
 	node->partColsUpdated = partColsUpdated;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 26a3e050086..91ee85e9157 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -157,6 +157,14 @@ static Node *substitute_actual_srf_parameters_mutator(Node *node,
 													  substitute_actual_srf_parameters_context *context);
 static bool pull_paramids_walker(Node *node, Bitmapset **context);
 
+/*
+ * Allow user to supply specific checker for "contain_volatile_functions" call.
+ * In general it is not used, but for example append-optimized tables needs to
+ * ignore some types of volatile functions during default expressions check.
+ */
+
+static bool contain_volatile_functions_checker(Oid func_id, void *context);
+static check_function_callback checker = contain_volatile_functions_checker;
 
 /*****************************************************************************
  *		Aggregate-function clause manipulation
@@ -541,6 +549,23 @@ contain_volatile_functions(Node *clause)
 	return contain_volatile_functions_walker(clause, NULL);
 }
 
+/*
+ * Same as above, but allows to specify user-defined check_function_callback.
+ */
+bool
+contain_volatile_functions_extended(Node *clause,
+									check_function_callback ud_checker)
+{
+	bool res;
+	check_function_callback prev_checker = checker;
+
+	checker = ud_checker;
+	res = contain_volatile_functions_walker(clause, NULL);
+	checker = prev_checker;
+
+	return res;
+}
+
 static bool
 contain_volatile_functions_checker(Oid func_id, void *context)
 {
@@ -553,8 +578,7 @@ contain_volatile_functions_walker(Node *node, void *context)
 	if (node == NULL)
 		return false;
 	/* Check for volatile functions in node itself */
-	if (check_functions_in_node(node, contain_volatile_functions_checker,
-								context))
+	if (check_functions_in_node(node, checker, context))
 		return true;
 
 	if (IsA(node, NextValueExpr))
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index e48fe434cd3..96b9e925e66 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -30,6 +30,7 @@
 #include "storage/shm_toc.h"
 #include "utils/relcache.h"
 #include "utils/snapshot.h"
+#include "utils/tuplestore.h"

 /* "options" flag bits for heap_insert */
@@ -270,6 +271,35 @@ typedef enum
 	PRUNE_VACUUM_CLEANUP,		/* VACUUM 2nd heap pass */
 } PruneReason;
 
+/*
+ * Maximum number of slots that multi-insert buffers can hold.
+ *
+ * Caution: Don't make this too big, as we could end up with this many tuples
+ * stored in multi insert buffer.
+ */
+#define HEAP_MAX_BUFFERED_SLOTS		1000
+
+typedef struct HeapMultiInsertState
+{
+	/* Array of buffered slots */
+	TupleTableSlot **slots;
+
+	/* Holds the tuple set */
+	Tuplestorestate *tstore;
+
+	/* Number of buffered tuples currently held */
+	int				nused;
+
+	/* Memory context for dealing with multi inserts */
+	MemoryContext mem_ctx;
+} HeapMultiInsertState;
+
+typedef struct HeapInsertState
+{
+	struct BulkInsertStateData *bistate;
+	HeapMultiInsertState *mistate;
+} HeapInsertState;
+
 /* ----------------
  *		function prototypes for heap access method
  *
@@ -320,6 +350,17 @@ extern void heap_insert(Relation relation, HeapTuple tup, CommandId cid,
 extern void heap_multi_insert(Relation relation, struct TupleTableSlot **slots,
 							  int ntuples, CommandId cid, int options,
 							  BulkInsertState bistate);
+
+extern TableModifyState *heap_modify_begin(Relation rel,
+										   CommandId cid,
+										   int options,
+										   TableModifyBufferFlushCb buffer_flush_cb,
+										   void *buffer_flush_ctx);
+extern void heap_modify_buffer_insert(TableModifyState *state,
+									  TupleTableSlot *slot);
+extern void heap_modify_buffer_flush(TableModifyState *state);
+extern void heap_modify_end(TableModifyState *state);
+
 extern TM_Result heap_delete(Relation relation, ItemPointer tid,
 							 CommandId cid, Snapshot crosscheck, bool wait,
 							 struct TM_FailureData *tmfd, bool changingPart);
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h
index 8713e12cbfb..3942463b715 100644
--- a/src/include/access/tableam.h
+++ b/src/include/access/tableam.h
@@ -248,12 +248,44 @@ typedef struct TM_IndexDeleteOp
 	TM_IndexStatus *status;
 } TM_IndexDeleteOp;
 
+struct TableModifyState;
+
+/* Callback invoked upon flushing each buffered tuple */
+typedef void (*TableModifyBufferFlushCb) (void *context,
+										  TupleTableSlot *slot);
+
+/* Holds table modify state */
+typedef struct TableModifyState
+{
+	/* These fields are used for inserts for now */
+
+	Relation	rel;			/* Relation to insert to */
+	CommandId	cid;			/* Command ID for insert */
+	int			options;		/* TABLE_INSERT options */
+
+	/* Memory context for dealing with modify state variables */
+	MemoryContext mem_ctx;
+
+	/* Flush callback and its context used for multi inserts */
+	TableModifyBufferFlushCb buffer_flush_cb;
+	void	   *buffer_flush_ctx;
+
+	/* Table AM specific data */
+	void	   *data;
+} TableModifyState;
+
 /* "options" flag bits for table_tuple_insert */
 /* TABLE_INSERT_SKIP_WAL was 0x0001; RelationNeedsWAL() now governs */
 #define TABLE_INSERT_SKIP_FSM		0x0002
 #define TABLE_INSERT_FROZEN			0x0004
 #define TABLE_INSERT_NO_LOGICAL		0x0008
 
+/*
+ * Use BAS_BULKWRITE buffer access strategy. 0x0010 is for
+ * HEAP_INSERT_SPECULATIVE.
+ */
+#define TABLE_INSERT_BAS_BULKWRITE	0x0020
+
 /* flag bits for table_tuple_lock */
 /* Follow tuples whose update is in progress if lock modes don't conflict  */
 #define TUPLE_LOCK_FLAG_LOCK_UPDATE_IN_PROGRESS	(1 << 0)
@@ -571,6 +603,21 @@ typedef struct TableAmRoutine
 	void		(*finish_bulk_insert) (Relation rel, int options);

+	/* ------------------------------------------------------------------------
+	 * Table Modify related functions.
+	 * ------------------------------------------------------------------------
+	 */
+	TableModifyState *(*tuple_modify_begin) (Relation rel,
+											 CommandId cid,
+											 int options,
+											 TableModifyBufferFlushCb buffer_flush_cb,
+											 void *buffer_flush_ctx);
+	void		(*tuple_modify_buffer_insert) (TableModifyState *state,
+											   TupleTableSlot *slot);
+	void		(*tuple_modify_buffer_flush) (TableModifyState *state);
+	void		(*tuple_modify_end) (TableModifyState *state);
+
+
 	/* ------------------------------------------------------------------------
 	 * DDL related functionality.
 	 * ------------------------------------------------------------------------
@@ -1560,6 +1607,43 @@ table_finish_bulk_insert(Relation rel, int options)
 }

+/* ------------------------------------------------------------------------
+ * Table Modify related functions.
+ * ------------------------------------------------------------------------
+ */
+static inline TableModifyState *
+table_modify_begin(Relation rel,
+				   CommandId cid,
+				   int options,
+				   TableModifyBufferFlushCb buffer_flush_cb,
+				   void *buffer_flush_ctx)
+{
+	return rel->rd_tableam->tuple_modify_begin(rel,
+											   cid,
+											   options,
+											   buffer_flush_cb,
+											   buffer_flush_ctx);
+}
+
+static inline void
+table_modify_buffer_insert(TableModifyState *state, TupleTableSlot *slot)
+{
+	state->rel->rd_tableam->tuple_modify_buffer_insert(state, slot);
+}
+
+static inline void
+table_modify_buffer_flush(TableModifyState *state)
+{
+	state->rel->rd_tableam->tuple_modify_buffer_flush(state);
+}
+
+static inline void
+table_modify_end(TableModifyState *state)
+{
+	state->rel->rd_tableam->tuple_modify_end(state);
+}
+
+
 /* ------------------------------------------------------------------------
  * DDL related functionality.
  * ------------------------------------------------------------------------
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 5b6cadb5a6c..cbd798187eb 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1200,6 +1200,12 @@ typedef struct PlanState
 
 	bool		async_capable;	/* true if node is async-capable */
 
+	/*
+	 * Qual of current node or any qual of nodes lower down the plan tree has
+	 * at least one volatile function.
+	 */
+	bool		has_volatile;
+
 	/*
 	 * Scanslot's descriptor if known. This is a bit of a hack, but otherwise
 	 * it's hard for expression compilation to optimize based on the
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..3a38040d991 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -292,6 +292,8 @@ typedef struct ModifyTable
 	CmdType		operation;
 	/* do we set the command tag/es_processed? */
 	bool		canSetTag;
+	/* do we use batching during INSERT? */
+	bool		canUseBatching;
 	/* Parent RT index for use of EXPLAIN */
 	Index		nominalRelation;
 	/* Root RT index, if partitioned/inherited */
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 546828b54bd..9bda34d21bc 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -22,6 +22,7 @@
 #ifndef OPTIMIZER_H
 #define OPTIMIZER_H
 
+#include "nodes/nodeFuncs.h"
 #include "nodes/parsenodes.h"
 
 /*
@@ -142,6 +143,8 @@ extern Expr *canonicalize_qual(Expr *qual, bool is_check);
 extern bool contain_mutable_functions(Node *clause);
 extern bool contain_mutable_functions_after_planning(Expr *expr);
 extern bool contain_volatile_functions(Node *clause);
+extern bool contain_volatile_functions_extended(Node *clause,
+												check_function_callback ud_checker);
 extern bool contain_volatile_functions_after_planning(Expr *expr);
 extern bool contain_volatile_functions_not_nextval(Node *clause);
 
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index b552359915f..e548954d81d 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -348,6 +348,7 @@ typedef struct StdRdOptions
 	StdRdOptIndexCleanup vacuum_index_cleanup;	/* controls index vacuuming */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
 	bool		vacuum_truncate_set;	/* whether vacuum_truncate is set */
+	bool		append_optimized; /* use optimized insertion algorithm */
 
 	/*
 	 * Fraction of pages in a relation that vacuum can eagerly scan and fail
@@ -367,6 +368,15 @@ typedef struct StdRdOptions
 	((relation)->rd_options ? \
 	 ((StdRdOptions *) (relation)->rd_options)->toast_tuple_target : (defaulttarg))
 
+/*
+ * RelationIsAppendOptimized
+ *		Check whether relation can use batching for insertion
+ */
+ #define RelationIsAppendOptimized(relation) \
+	(AssertMacro(RelationIsValid(relation)), \
+	 (relation)->rd_options ? \
+	 ((StdRdOptions *) (relation)->rd_options)->append_optimized : false)
+
 /*
  * RelationGetFillFactor
  *		Returns the relation's fillfactor.  Note multiple eval of argument!
diff --git a/src/test/regress/expected/append_optimized.out b/src/test/regress/expected/append_optimized.out
new file mode 100644
index 00000000000..57b45a20e61
--- /dev/null
+++ b/src/test/regress/expected/append_optimized.out
@@ -0,0 +1,161 @@
+-- Not all INSERT queries are suitable for using batching. All conditions are
+-- listed in nodeModifyTable.c
+-- In this test we want to check whether append_optimized table correcly
+-- determines when to use batching.
+CREATE TABLE optimized_tbl (
+	int_data INT DEFAULT random()
+) WITH (append_optimized=true);
+CREATE TABLE rows_source (int_data INT);
+INSERT INTO rows_source SELECT generate_series(1, 10);
+-- Must not use batching here, because optimized_tbl has volatile function
+-- whithin default expression.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Insert on optimized_tbl  (cost=0.00..35.50 rows=0 width=0)
+   ->  Seq Scan on rows_source  (cost=0.00..35.50 rows=2550 width=4)
+(2 rows)
+
+-- Now default expression not prevent us from using batching.
+ALTER TABLE optimized_tbl ALTER COLUMN int_data SET DEFAULT 0;
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ MultiInsert on optimized_tbl  (cost=0.00..35.50 rows=0 width=0)
+   ->  Seq Scan on rows_source  (cost=0.00..35.50 rows=2550 width=4)
+(2 rows)
+
+-- Must not use batching here, because WHERE clause contains volatile function.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source
+		 WHERE int_data > random();
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Insert on optimized_tbl  (cost=0.00..54.63 rows=0 width=0)
+   ->  Seq Scan on rows_source  (cost=0.00..54.63 rows=850 width=4)
+         Filter: ((int_data)::double precision > random())
+(3 rows)
+
+-- Now WHERE clause not prevent us from using batching.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source
+		 WHERE int_data > 2;
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ MultiInsert on optimized_tbl  (cost=0.00..41.88 rows=0 width=0)
+   ->  Seq Scan on rows_source  (cost=0.00..41.88 rows=850 width=4)
+         Filter: (int_data > 2)
+(3 rows)
+
+-- Create ROW trigger on optimized_tbl.
+CREATE OR REPLACE FUNCTION my_trigger_function()
+RETURNS TRIGGER AS $$
+BEGIN
+    NEW.int_data := NEW.int_data * 10;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER my_row_trigger
+BEFORE INSERT ON optimized_tbl
+FOR EACH ROW
+EXECUTE FUNCTION my_trigger_function();
+-- Must not use batching here, because optimized_tbl has ROW trigger.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Insert on optimized_tbl  (cost=0.00..35.50 rows=0 width=0)
+   ->  Seq Scan on rows_source  (cost=0.00..35.50 rows=2550 width=4)
+(2 rows)
+
+DROP TRIGGER my_row_trigger ON optimized_tbl;
+DROP FUNCTION my_trigger_function();
+-- Must not use batching here, because RETURNING clause is specified.
+EXPLAIN INSERT INTO optimized_tbl VALUES (100) RETURNING int_data;
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Insert on optimized_tbl  (cost=0.00..0.01 rows=1 width=4)
+   ->  Result  (cost=0.00..0.01 rows=1 width=4)
+(2 rows)
+
+-- Now RETURNING not prevent us from using batching.
+EXPLAIN INSERT INTO optimized_tbl VALUES (100);
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ MultiInsert on optimized_tbl  (cost=0.00..0.01 rows=0 width=0)
+   ->  Result  (cost=0.00..0.01 rows=1 width=4)
+(2 rows)
+
+TRUNCATE optimized_tbl;
+CREATE INDEX idx_test_int_data ON optimized_tbl (int_data);
+-- Fill source table with more data, so there will be several buffers flushs
+-- during INSERT opration.
+INSERT INTO rows_source SELECT generate_series(11, 10000);
+-- It is OK to use batching.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
+ MultiInsert on optimized_tbl  (cost=0.00..159.75 rows=0 width=0)
+   ->  Seq Scan on rows_source  (cost=0.00..159.75 rows=11475 width=4)
+(2 rows)
+
+INSERT INTO optimized_tbl
+SELECT int_data FROM rows_source;
+-- Check whether both index and table contains all inserted rows.
+SELECT COUNT(*) FROM optimized_tbl;
+ count 
+-------
+ 10000
+(1 row)
+
+ANALYZE optimized_tbl;
+SELECT c.relname, c.reltuples
+FROM pg_class c
+JOIN pg_index i ON c.oid = i.indexrelid
+WHERE i.indrelid = 'optimized_tbl'::regclass;
+      relname      | reltuples 
+-------------------+-----------
+ idx_test_int_data |     10000
+(1 row)
+
+-- We allow to use SERIAL field in append_optimized table. Check whether such
+-- fields behave correctly.
+CREATE TABLE test_serial(
+	id SERIAL,
+	int_data INT
+) WITH (append_optimized=true);
+CREATE TABLE small_source(int_data INT);
+INSERT INTO small_source SELECT generate_series(1, 10);
+EXPLAIN INSERT INTO test_serial(int_data)
+		SELECT int_data FROM small_source;
+                              QUERY PLAN                              
+----------------------------------------------------------------------
+ MultiInsert on test_serial  (cost=0.00..48.25 rows=0 width=0)
+   ->  Seq Scan on small_source  (cost=0.00..48.25 rows=2550 width=8)
+(2 rows)
+
+INSERT INTO test_serial(int_data)
+SELECT int_data FROM small_source;
+SELECT * FROM test_serial;
+ id | int_data 
+----+----------
+  1 |        1
+  2 |        2
+  3 |        3
+  4 |        4
+  5 |        5
+  6 |        6
+  7 |        7
+  8 |        8
+  9 |        9
+ 10 |       10
+(10 rows)
+
+-- Cleanup
+DROP TABLE optimized_tbl;
+DROP TABLE rows_source;
+DROP TABLE test_serial;
+DROP TABLE small_source;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 0a35f2f8f6a..0cda71a358d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -136,3 +136,5 @@ test: fast_default
 # run tablespace test at the end because it drops the tablespace created during
 # setup that other tests may use.
 test: tablespace
+
+test: append_optimized
diff --git a/src/test/regress/sql/append_optimized.sql b/src/test/regress/sql/append_optimized.sql
new file mode 100644
index 00000000000..ce3ffab2d52
--- /dev/null
+++ b/src/test/regress/sql/append_optimized.sql
@@ -0,0 +1,105 @@
+-- Not all INSERT queries are suitable for using batching. All conditions are
+-- listed in nodeModifyTable.c
+-- In this test we want to check whether append_optimized table correcly
+-- determines when to use batching.
+
+CREATE TABLE optimized_tbl (
+	int_data INT DEFAULT random()
+) WITH (append_optimized=true);
+
+CREATE TABLE rows_source (int_data INT);
+INSERT INTO rows_source SELECT generate_series(1, 10);
+
+-- Must not use batching here, because optimized_tbl has volatile function
+-- whithin default expression.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+
+-- Now default expression not prevent us from using batching.
+ALTER TABLE optimized_tbl ALTER COLUMN int_data SET DEFAULT 0;
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+
+-- Must not use batching here, because WHERE clause contains volatile function.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source
+		 WHERE int_data > random();
+
+-- Now WHERE clause not prevent us from using batching.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source
+		 WHERE int_data > 2;
+
+-- Create ROW trigger on optimized_tbl.
+CREATE OR REPLACE FUNCTION my_trigger_function()
+RETURNS TRIGGER AS $$
+BEGIN
+    NEW.int_data := NEW.int_data * 10;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER my_row_trigger
+BEFORE INSERT ON optimized_tbl
+FOR EACH ROW
+EXECUTE FUNCTION my_trigger_function();
+
+-- Must not use batching here, because optimized_tbl has ROW trigger.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+
+DROP TRIGGER my_row_trigger ON optimized_tbl;
+DROP FUNCTION my_trigger_function();
+
+-- Must not use batching here, because RETURNING clause is specified.
+EXPLAIN INSERT INTO optimized_tbl VALUES (100) RETURNING int_data;
+
+-- Now RETURNING not prevent us from using batching.
+EXPLAIN INSERT INTO optimized_tbl VALUES (100);
+
+TRUNCATE optimized_tbl;
+CREATE INDEX idx_test_int_data ON optimized_tbl (int_data);
+
+-- Fill source table with more data, so there will be several buffers flushs
+-- during INSERT opration.
+INSERT INTO rows_source SELECT generate_series(11, 10000);
+
+-- It is OK to use batching.
+EXPLAIN INSERT INTO optimized_tbl
+		SELECT int_data FROM rows_source;
+
+INSERT INTO optimized_tbl
+SELECT int_data FROM rows_source;
+
+-- Check whether both index and table contains all inserted rows.
+SELECT COUNT(*) FROM optimized_tbl;
+ANALYZE optimized_tbl;
+
+SELECT c.relname, c.reltuples
+FROM pg_class c
+JOIN pg_index i ON c.oid = i.indexrelid
+WHERE i.indrelid = 'optimized_tbl'::regclass;
+
+-- We allow to use SERIAL field in append_optimized table. Check whether such
+-- fields behave correctly.
+CREATE TABLE test_serial(
+	id SERIAL,
+	int_data INT
+) WITH (append_optimized=true);
+
+CREATE TABLE small_source(int_data INT);
+INSERT INTO small_source SELECT generate_series(1, 10);
+
+EXPLAIN INSERT INTO test_serial(int_data)
+		SELECT int_data FROM small_source;
+
+INSERT INTO test_serial(int_data)
+SELECT int_data FROM small_source;
+
+SELECT * FROM test_serial;
+
+-- Cleanup
+DROP TABLE optimized_tbl;
+DROP TABLE rows_source;
+DROP TABLE test_serial;
+DROP TABLE small_source;
-- 
2.43.0

---

From: Haibo Yan <[email-masked]>
Date: Mon, 27 Apr 2026 21:27:16 -0700

> 2c7bd2ba507e273f2d7fe1b2f6d30775ed4f3c09) will be useful for this

Hi all,

I picked this work up again and implemented the full 5-patch series.

The series is structured as follows:

   - 0001 adds the buffered-insert lifecycle API in tableam/heapam and
   provides the heap implementation.
   - 0002 adopts the API for CTAS.
   - 0003 adopts the API for CREATE MATERIALIZED VIEW and REFRESH
   MATERIALIZED VIEW.
   - 0004 adopts the API for COPY FROM.
   - 0005 adopts the API for a restricted first step of INSERT INTO … SELECT
   .

I also reran performance testing locally on my machine:

   - Hardware: MacBook Pro M4, 36GB RAM
   - shared_buffers: 128MB

I compared the unpatched baseline against the current patched series for
CTAS, CMV, RMV, and INSERT INTO ... SELECT.
Table 1 — Median (ms)

Workload

10K Before

10K After

10K Improv

100K Before

100K After

100K Improv

1M Before

1M After

1M Improv

CTAS

1.60

1.17

+26.9%

9.19

5.70

+38.0%

105.61

73.28

+30.6%

CMV

2.11

1.77

+16.1%

10.28

6.20

+39.7%

110.10

79.64

+27.7%

RMV

1.62

1.19

+26.5%

9.91

5.43

+45.2%

106.04

69.57

+34.4%

INSERT … SELECT

1.72

0.89

+48.3%

15.39

7.46

+51.5%

228.24

84.66

+62.9%

Table 2 — Average (ms)

Workload

10K Before

10K After

10K Improv

100K Before

100K After

100K Improv

1M Before

1M After

1M Improv

CTAS

1.65

1.26

+24.1%

9.37

5.82

+37.9%

104.81

74.31

+29.1%

CMV

2.34

1.82

+22.2%

10.32

6.25

+39.4%

110.32

80.50

+27.0%

RMV

1.92

1.21

+36.9%

9.86

5.49

+44.3%

106.79

69.53

+34.9%

INSERT … SELECT

1.69

0.90

+46.8%

15.45

7.39

+52.2%

210.62

85.19

+59.6%

These numbers look ok to me to continue the discussion with the current
design and implementation.

One point worth calling out explicitly: the INSERT INTO ... SELECT support
in patch 5 is intentionally limited, as described above. It is not intended
to claim broad executor coverage yet.

One further improvement still seems possible: making the heap
implementation cache raw HeapTuple bytes directly instead of maintaining
buffered slot arrays. I looked at that direction, but did not include it in
this series because it felt like a larger scope change than what I wanted
for v1.

At this point, I think the current series is in reasonable shape and I’d
really appreciate review on both the API shape and the caller adoptions.

Thanks,
Haibo