Subject: Re: Add the ability to limit the amount of memory that can be allocated to backends.

From: [email-masked]
Date: Mon, 22 May 2023 08:42:51 -0400

On Wed, 2023-05-17 at 23:07 -0400, [email-masked] wrote:
> Thanks for the feedback.
> 
> I'm plannning to look at this. 
> 
> Is your benchmark something that I could utilize? I.E. is it a set of
> scripts or a standard test from somewhere that I can duplicate?
> 
> Thanks,
> Reid
> 
Hi Arne,

Followup to the above.

I experimented on my system regarding
"The simple query select * from generate_series(0, 10000000) shows roughly 18.9 % degradation on my test server."

My laptop:
32GB ram
11th Gen Intel(R) Core(TM) i7-11850H 8 cores/16 threads @ 2.50GHz (Max Turbo Frequency. 4.80 GHz ; Cache. 24 MB)
SSD -> Model: KXG60ZNV1T02 NVMe KIOXIA 1024GB (nvme)

I updated to latest master and rebased my patch branches.

I wrote a script to check out, build, install, init, and startup
master, patch 1, patch 1+2, patch 1+2 as master, pg-stats-memory, 
dev-max-memory, and dev-max-memory-unset configured with

../../configure --silent --prefix=/home/rthompso/src/git/postgres/install/${dir} --with-openssl --with-tcl --with-tclconfig=/usr/lib/tcl8.6 --with-perl --with-libxml --with-libxslt --with-python --with-gssapi --with-systemd --with-ldap --enable-nls

where $dir in  master, pg-stats-memory, and dev-max-memory,
dev-max-memory-unset.

The only change made to the default postgresql.conf was to have the
script add to the dev-max-memory instance the line
"max_total_backend_memory = 2048" before startup.
I did find one change in patch 2 that I pushed back into patch 1, this
should only impact the pg-stats-memory instance.

my .psqlrc turns timing on

I created a script where I can pass two instances to be compared.
It invokes
   psql -At -d postgres $connstr -P pager=off -c 'select * from generate_series(0, 10000000)'
100 times on each of the 2 instances and calculates the AVG time and SD
for the 100 runs.  It then uses the AVG from each instance to calculate
the percentage difference.

Depending on the instance, my results differ from master from
negligible to ~5.5%.  Comparing master to itself had up to a ~2%
variation. See below.

------------------------
12 runs comparing dev-max-memory 2048 VS master
Shows ~3% to 5.5% variation

Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1307.14 -> VER dev-max-memory 2048
1240.74 -> VER master
5.21218% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1315.99 -> VER dev-max-memory 2048
1245.64 -> VER master
5.4926% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1317.39 -> VER dev-max-memory 2048
1265.33 -> VER master
4.03141% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1313.52 -> VER dev-max-memory 2048
1256.69 -> VER master
4.42221% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1329.98 -> VER dev-max-memory 2048
1253.75 -> VER master
5.90077% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1314.47 -> VER dev-max-memory 2048
1245.6 -> VER master
5.38032% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1309.7 -> VER dev-max-memory 2048
1258.55 -> VER master
3.98326% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1322.16 -> VER dev-max-memory 2048
1248.94 -> VER master
5.69562% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1320.15 -> VER dev-max-memory 2048
1261.41 -> VER master
4.55074% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1345.22 -> VER dev-max-memory 2048
1280.96 -> VER master
4.8938% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1296.03 -> VER dev-max-memory 2048
1257.06 -> VER master
3.05277% difference
--
Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master
1319.5 -> VER dev-max-memory 2048
1252.34 -> VER master
5.22272% difference

----------------------------
12 showing dev-max-memory-unset VS master
Shows ~2.5% to 5% variation

Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1300.93 -> VER dev-max-memory unset
1235.12 -> VER master
5.18996% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1293.57 -> VER dev-max-memory unset
1263.93 -> VER master
2.31789% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1303.05 -> VER dev-max-memory unset
1258.11 -> VER master
3.50935% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1302.14 -> VER dev-max-memory unset
1256.51 -> VER master
3.56672% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1299.22 -> VER dev-max-memory unset
1282.74 -> VER master
1.27655% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1334.06 -> VER dev-max-memory unset
1263.77 -> VER master
5.41144% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1319.92 -> VER dev-max-memory unset
1262.35 -> VER master
4.45887% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1318.01 -> VER dev-max-memory unset
1257.16 -> VER master
4.7259% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1316.88 -> VER dev-max-memory unset
1257.63 -> VER master
4.60282% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1320.33 -> VER dev-max-memory unset
1282.12 -> VER master
2.93646% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1306.91 -> VER dev-max-memory unset
1246.12 -> VER master
4.76218% difference
--
Calculate average runtime percentage difference between VER dev-max-memory unset and VER master
1320.65 -> VER dev-max-memory unset
1258.78 -> VER master
4.79718% difference
-------------------------------

12 showing pg-stat-activity-only VS master
Shows ~<1% to 2.5% variation

Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1252.65 -> VER pg-stat-activity-backend-memory-allocated
1245.36 -> VER master
0.583665% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1294.75 -> VER pg-stat-activity-backend-memory-allocated
1277.55 -> VER master
1.33732% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1264.11 -> VER pg-stat-activity-backend-memory-allocated
1257.57 -> VER master
0.518702% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1267.44 -> VER pg-stat-activity-backend-memory-allocated
1251.31 -> VER master
1.28079% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1270.05 -> VER pg-stat-activity-backend-memory-allocated
1250.1 -> VER master
1.58324% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1298.92 -> VER pg-stat-activity-backend-memory-allocated
1265.04 -> VER master
2.64279% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1280.99 -> VER pg-stat-activity-backend-memory-allocated
1263.51 -> VER master
1.37394% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1273.23 -> VER pg-stat-activity-backend-memory-allocated
1275.53 -> VER master
-0.18048% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1261.2 -> VER pg-stat-activity-backend-memory-allocated
1263.04 -> VER master
-0.145786% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1289.73 -> VER pg-stat-activity-backend-memory-allocated
1289.02 -> VER master
0.0550654% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1287.57 -> VER pg-stat-activity-backend-memory-allocated
1279.42 -> VER master
0.634985% difference
--
Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master
1272.01 -> VER pg-stat-activity-backend-memory-allocated
1259.22 -> VER master
1.01058% difference
----------------------------------

I also did 12 runs master VS master
Shows, ~1% to 2% variation

Calculate average runtime percentage difference between VER master and VER master
1239.6 -> VER master
1263.73 -> VER master
-1.92783% difference
--
Calculate average runtime percentage difference between VER master and VER master
1253.82 -> VER master
1252.5 -> VER master
0.105334% difference
--
Calculate average runtime percentage difference between VER master and VER master
1256.05 -> VER master
1258.97 -> VER master
-0.232205% difference
--
Calculate average runtime percentage difference between VER master and VER master
1264.8 -> VER master
1248.94 -> VER master
1.26186% difference
--
Calculate average runtime percentage difference between VER master and VER master
1265.08 -> VER master
1275.43 -> VER master
-0.814797% difference
--
Calculate average runtime percentage difference between VER master and VER master
1260.95 -> VER master
1288.81 -> VER master
-2.1853% difference
--
Calculate average runtime percentage difference between VER master and VER master
1260.46 -> VER master
1252.86 -> VER master
0.604778% difference
--
Calculate average runtime percentage difference between VER master and VER master
1253.49 -> VER master
1255.25 -> VER master
-0.140309% difference
--
Calculate average runtime percentage difference between VER master and VER master
1277.5 -> VER master
1267.42 -> VER master
0.792166% difference
--
Calculate average runtime percentage difference between VER master and VER master
1266.2 -> VER master
1283.12 -> VER master
-1.32741% difference
--
Calculate average runtime percentage difference between VER master and VER master
1245.78 -> VER master
1246.78 -> VER master
-0.0802388% difference
--
Calculate average runtime percentage difference between VER master and VER master
1255.15 -> VER master
1276.73 -> VER master
-1.70466% difference

---

From: [email-masked]
Date: Mon, 22 May 2023 11:59:56 -0400

> > scripts or a standard test from somewhere that I can duplicate?

Attach patches updated to master.
Pulled from patch 2 back to patch 1 a change that was also pertinent to patch 1.

From e6f8499e0270f2291494260bc341e8ad1411c2ae Mon Sep 17 00:00:00 2001
From: Reid Thompson <[email-masked]>
Date: Thu, 11 Aug 2022 12:01:25 -0400
Subject: [PATCH 1/2] Add tracking of backend memory allocated

Add tracking of backend memory allocated in total and by allocation
type (aset, dsm, generation, slab) by process.

allocated_bytes tracks the current bytes of memory allocated to the
backend process. aset_allocated_bytes, dsm_allocated_bytes,
generation_allocated_bytes and slab_allocated_bytes track the
allocation by type for the backend process. They are updated for the
process as memory is malloc'd/freed.  Memory allocated to items on
the freelist is included.  Dynamic shared memory allocations are
included only in the value displayed for the backend that created
them, they are not included in the value for backends that are
attached to them to avoid double counting. DSM allocations that are
not destroyed by the creating process prior to it's exit are
considered long lived and are tracked in a global counter
global_dsm_allocated_bytes. We limit the floor of allocation
counters to zero. Created views pg_stat_global_memory_allocation and
pg_stat_memory_allocation for access to these trackers.
---
 doc/src/sgml/monitoring.sgml                | 246 ++++++++++++++++++++
 src/backend/catalog/system_views.sql        |  34 +++
 src/backend/storage/ipc/dsm.c               |  11 +-
 src/backend/storage/ipc/dsm_impl.c          |  78 +++++++
 src/backend/storage/lmgr/proc.c             |   1 +
 src/backend/utils/activity/backend_status.c | 114 +++++++++
 src/backend/utils/adt/pgstatfuncs.c         |  84 +++++++
 src/backend/utils/init/miscinit.c           |   3 +
 src/backend/utils/mmgr/aset.c               |  17 ++
 src/backend/utils/mmgr/generation.c         |  15 ++
 src/backend/utils/mmgr/slab.c               |  22 ++
 src/include/catalog/pg_proc.dat             |  17 ++
 src/include/storage/proc.h                  |   2 +
 src/include/utils/backend_status.h          | 144 +++++++++++-
 src/test/regress/expected/rules.out         |  27 +++
 src/test/regress/expected/stats.out         |  36 +++
 src/test/regress/sql/stats.sql              |  20 ++
 17 files changed, 869 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index df5242fa80..cfc221fb2e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5757,6 +5757,252 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
 
  </sect2>
 
+ <sect2 id="monitoring-pg-stat-memory-allocation-view">
+  <title><structname>pg_stat_memory_allocation</structname></title>
+
+  <indexterm>
+   <primary>pg_stat_memory_allocation</primary>
+  </indexterm>
+
+  <para>
+   The <structname>pg_stat_memory_allocation</structname> view will have one
+   row per server process, showing information related to the current memory
+   allocation of that process in total and by allocator type. Due to the
+   dynamic nature of memory allocations the allocated bytes values may not be
+   exact but should be sufficient for the intended purposes. Dynamic shared
+   memory allocations are included only in the value displayed for the backend
+   that created them, they are not included in the value for backends that are
+   attached to them to avoid double counting.  Use
+   <function>pg_size_pretty</function> described in
+   <xref linkend="functions-admin-dbsize"/> to make these values more easily
+   readable.
+  </para>
+
+  <table id="pg-stat-memory-allocation-view" xreflabel="pg_stat_memory_allocation">
+   <title><structname>pg_stat_memory_allocation</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>datid</structfield> <type>oid</type>
+      </para>
+      <para>
+       OID of the database this backend is connected to
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pid</structfield> <type>integer</type>
+      </para>
+      <para>
+       Process ID of this backend
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>allocated_bytes</structfield> <type>bigint</type>
+      </para>
+     <para>
+      Memory currently allocated to this backend in bytes. This is the balance
+      of bytes allocated and freed by this backend. Dynamic shared memory
+      allocations are included only in the value displayed for the backend that
+      created them, they are not included in the value for backends that are
+      attached to them to avoid double counting.
+     </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>aset_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Memory currently allocated to this backend in bytes via the allocation
+       set allocator.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>dsm_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Memory currently allocated to this backend in bytes via the dynamic
+       shared memory allocator. Upon process exit, dsm allocations that have
+       not been freed are considered long lived and added to
+       <structfield>global_dsm_allocated_bytes</structfield> found in the
+       <link linkend="monitoring-pg-stat-global-memory-allocation-view">
+       <structname>pg_stat_global_memory_allocation</structname></link> view.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>generation_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Memory currently allocated to this backend in bytes via the generation
+       allocator.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>slab_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Memory currently allocated to this backend in bytes via the slab
+       allocator.
+      </para></entry>
+     </row>
+
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect2>
+
+ <sect2 id="monitoring-pg-stat-global-memory-allocation-view">
+  <title><structname>pg_stat_global_memory_allocation</structname></title>
+
+  <indexterm>
+   <primary>pg_stat_global-memory_allocation</primary>
+  </indexterm>
+
+  <para>
+   The <structname>pg_stat_global_memory_allocation</structname> view will
+   have one row showing information related to current shared memory
+   allocations. Due to the dynamic nature of memory allocations the allocated
+   bytes values may not be exact but should be sufficient for the intended
+   purposes. Use <function>pg_size_pretty</function> described in
+   <xref linkend="functions-admin-dbsize"/> to make the byte populated values
+   more easily readable.
+  </para>
+
+  <table id="pg-stat-global-memory-allocation-view" xreflabel="pg_stat_global_memory_allocation">
+   <title><structname>pg_stat_global_memory_allocation</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>datid</structfield> <type>oid</type>
+      </para>
+      <para>
+       OID of the database this backend is connected to
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>shared_memory_size_mb</structfield> <type>integer</type>
+      </para>
+      <para>
+       Reports the size of the main shared memory area, rounded up to the
+       nearest megabyte. See <xref linkend="guc-shared-memory-size"/>.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>shared_memory_size_in_huge_pages</structfield> <type>bigint</type>
+      </para>
+     <para>
+      Reports the number of huge pages that are needed for the main shared
+      memory area based on the specified huge_page_size. If huge pages are not
+      supported, this will be -1. See
+      <xref linkend="guc-shared-memory-size-in-huge-pages"/>.
+     </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>global_dsm_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Long lived dynamically allocated memory currently allocated to the
+       database. Upon process exit, dsm allocations that have not been freed
+       are considered long lived and added to
+       <structfield>global_dsm_allocated_bytes</structfield>.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_aset_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Sum total of <structfield>aset_allocated_bytes</structfield> for all
+       backend processes from
+       <link linkend="monitoring-pg-stat-memory-allocation-view">
+       <structname>pg_stat_memory_allocation</structname></link> view.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_dsm_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Sum total of <structfield>dsm_allocated_bytes</structfield> for all
+       backend processes from
+       <link linkend="monitoring-pg-stat-memory-allocation-view">
+       <structname>pg_stat_memory_allocation</structname></link> view.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_generation_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Sum total of <structfield>generation_allocated_bytes</structfield> for
+       all backend processes from
+       <link linkend="monitoring-pg-stat-memory-allocation-view">
+       <structname>pg_stat_memory_allocation</structname></link> view.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_slab_allocated_bytes</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Sum total of <structfield>slab_allocated_bytes</structfield> for all
+       backend processes from
+       <link linkend="monitoring-pg-stat-memory-allocation-view">
+       <structname>pg_stat_memory_allocation</structname></link> view.
+      </para></entry>
+     </row>
+
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect2>
+
  <sect2 id="monitoring-stats-functions">
   <title>Statistics Functions</title>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c18fea8362..cc8219c665 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1341,3 +1341,37 @@ CREATE VIEW pg_stat_subscription_stats AS
         ss.stats_reset
     FROM pg_subscription as s,
          pg_stat_get_subscription_stats(s.oid) as ss;
+
+CREATE VIEW pg_stat_memory_allocation AS
+    SELECT
+        S.datid AS datid,
+        S.pid,
+        S.allocated_bytes,
+        S.aset_allocated_bytes,
+        S.dsm_allocated_bytes,
+        S.generation_allocated_bytes,
+        S.slab_allocated_bytes
+    FROM pg_stat_get_memory_allocation(NULL) AS S
+        LEFT JOIN pg_database AS D ON (S.datid = D.oid);
+
+CREATE VIEW pg_stat_global_memory_allocation AS
+WITH sums AS (
+    SELECT
+        SUM(aset_allocated_bytes) AS total_aset_allocated_bytes,
+        SUM(dsm_allocated_bytes) AS total_dsm_allocated_bytes,
+        SUM(generation_allocated_bytes) AS total_generation_allocated_bytes,
+        SUM(slab_allocated_bytes) AS total_slab_allocated_bytes
+    FROM
+        pg_stat_memory_allocation
+)
+SELECT
+        S.datid AS datid,
+        current_setting('shared_memory_size', true) as shared_memory_size,
+        (current_setting('shared_memory_size_in_huge_pages', true))::integer as shared_memory_size_in_huge_pages,
+        S.global_dsm_allocated_bytes,
+        sums.total_aset_allocated_bytes,
+        sums.total_dsm_allocated_bytes,
+        sums.total_generation_allocated_bytes,
+        sums.total_slab_allocated_bytes
+    FROM sums, pg_stat_get_global_memory_allocation() AS S
+        LEFT JOIN pg_database AS D ON (S.datid = D.oid);
diff --git a/src/backend/storage/ipc/dsm.c b/src/backend/storage/ipc/dsm.c
index 10b029bb16..64b1fecd1c 100644
--- a/src/backend/storage/ipc/dsm.c
+++ b/src/backend/storage/ipc/dsm.c
@@ -775,6 +775,15 @@ dsm_detach_all(void)
 void
 dsm_detach(dsm_segment *seg)
 {
+	/*
+	 * Retain mapped_size to pass into destroy call in cases where the detach
+	 * is the last reference. mapped_size is zeroed as part of the detach
+	 * process, but is needed later in these cases for dsm_allocated_bytes
+	 * accounting.
+	 */
+	Size		local_seg_mapped_size = seg->mapped_size;
+	Size	   *ptr_local_seg_mapped_size = &local_seg_mapped_size;
+
 	/*
 	 * Invoke registered callbacks.  Just in case one of those callbacks
 	 * throws a further error that brings us back here, pop the callback
@@ -855,7 +864,7 @@ dsm_detach(dsm_segment *seg)
 			 */
 			if (is_main_region_dsm_handle(seg->handle) ||
 				dsm_impl_op(DSM_OP_DESTROY, seg->handle, 0, &seg->impl_private,
-							&seg->mapped_address, &seg->mapped_size, WARNING))
+							&seg->mapped_address, ptr_local_seg_mapped_size, WARNING))
 			{
 				LWLockAcquire(DynamicSharedMemoryControlLock, LW_EXCLUSIVE);
 				if (is_main_region_dsm_handle(seg->handle))
diff --git a/src/backend/storage/ipc/dsm_impl.c b/src/backend/storage/ipc/dsm_impl.c
index 6399fa2ad5..f43bad4439 100644
--- a/src/backend/storage/ipc/dsm_impl.c
+++ b/src/backend/storage/ipc/dsm_impl.c
@@ -66,6 +66,7 @@
 #include "postmaster/postmaster.h"
 #include "storage/dsm_impl.h"
 #include "storage/fd.h"
+#include "utils/backend_status.h"
 #include "utils/guc.h"
 #include "utils/memutils.h"
 
@@ -232,6 +233,14 @@ dsm_impl_posix(dsm_op op, dsm_handle handle, Size request_size,
 							name)));
 			return false;
 		}
+
+		/*
+		 * Detach and destroy pass through here, only decrease the memory
+		 * shown allocated in pg_stat_activity when the creator destroys the
+		 * allocation.
+		 */
+		if (op == DSM_OP_DESTROY)
+			pgstat_report_allocated_bytes_decrease(*mapped_size, PG_ALLOC_DSM);
 		*mapped_address = NULL;
 		*mapped_size = 0;
 		if (op == DSM_OP_DESTROY && shm_unlink(name) != 0)
@@ -332,6 +341,33 @@ dsm_impl_posix(dsm_op op, dsm_handle handle, Size request_size,
 						name)));
 		return false;
 	}
+
+	/*
+	 * Attach and create pass through here, only update backend memory
+	 * allocated in pg_stat_activity for the creator process.
+	 */
+	if (op == DSM_OP_CREATE)
+	{
+		/*
+		 * Posix creation calls dsm_impl_posix_resize implying that resizing
+		 * occurs or may be added in the future. As implemented
+		 * dsm_impl_posix_resize utilizes fallocate or truncate, passing the
+		 * whole new size as input, growing the allocation as needed (only
+		 * truncate supports shrinking). We update by replacing the old
+		 * allocation with the new.
+		 */
+#if defined(HAVE_POSIX_FALLOCATE) && defined(__linux__)
+		/*
+		 * posix_fallocate does not shrink allocations, adjust only on
+		 * allocation increase.
+		 */
+		if (request_size > *mapped_size)
+			pgstat_report_allocated_bytes_increase(request_size - *mapped_size, PG_ALLOC_DSM);
+#else
+		pgstat_report_allocated_bytes_decrease(*mapped_size, PG_ALLOC_DSM);
+		pgstat_report_allocated_bytes_increase(request_size, PG_ALLOC_DSM);
+#endif
+	}
 	*mapped_address = address;
 	*mapped_size = request_size;
 	close(fd);
@@ -538,6 +574,14 @@ dsm_impl_sysv(dsm_op op, dsm_handle handle, Size request_size,
 							name)));
 			return false;
 		}
+
+		/*
+		 * Detach and destroy pass through here, only decrease the memory
+		 * shown allocated in pg_stat_activity when the creator destroys the
+		 * allocation.
+		 */
+		if (op == DSM_OP_DESTROY)
+			pgstat_report_allocated_bytes_decrease(*mapped_size, PG_ALLOC_DSM);
 		*mapped_address = NULL;
 		*mapped_size = 0;
 		if (op == DSM_OP_DESTROY && shmctl(ident, IPC_RMID, NULL) < 0)
@@ -585,6 +629,13 @@ dsm_impl_sysv(dsm_op op, dsm_handle handle, Size request_size,
 						name)));
 		return false;
 	}
+
+	/*
+	 * Attach and create pass through here, only update backend memory
+	 * allocated in pg_stat_activity for the creator process.
+	 */
+	if (op == DSM_OP_CREATE)
+		pgstat_report_allocated_bytes_increase(request_size, PG_ALLOC_DSM);
 	*mapped_address = address;
 	*mapped_size = request_size;
 
@@ -653,6 +704,13 @@ dsm_impl_windows(dsm_op op, dsm_handle handle, Size request_size,
 			return false;
 		}
 
+		/*
+		 * Detach and destroy pass through here, only decrease the memory
+		 * shown allocated in pg_stat_activity when the creator destroys the
+		 * allocation.
+		 */
+		if (op == DSM_OP_DESTROY)
+			pgstat_report_allocated_bytes_decrease(*mapped_size, PG_ALLOC_DSM);
 		*impl_private = NULL;
 		*mapped_address = NULL;
 		*mapped_size = 0;
@@ -769,6 +827,12 @@ dsm_impl_windows(dsm_op op, dsm_handle handle, Size request_size,
 		return false;
 	}
 
+	/*
+	 * Attach and create pass through here, only update backend memory
+	 * allocated in pg_stat_activity for the creator process.
+	 */
+	if (op == DSM_OP_CREATE)
+		pgstat_report_allocated_bytes_increase(info.RegionSize, PG_ALLOC_DSM);
 	*mapped_address = address;
 	*mapped_size = info.RegionSize;
 	*impl_private = hmap;
@@ -813,6 +877,13 @@ dsm_impl_mmap(dsm_op op, dsm_handle handle, Size request_size,
 							name)));
 			return false;
 		}
+
+		/*
+		 * Detach and destroy pass through here, only decrease the memory
+		 * shown allocated in pg_stat_activity when the creator destroys the
+		 * allocation.
+		 */
+		pgstat_report_allocated_bytes_decrease(*mapped_size, PG_ALLOC_DSM);
 		*mapped_address = NULL;
 		*mapped_size = 0;
 		if (op == DSM_OP_DESTROY && unlink(name) != 0)
@@ -934,6 +1005,13 @@ dsm_impl_mmap(dsm_op op, dsm_handle handle, Size request_size,
 						name)));
 		return false;
 	}
+
+	/*
+	 * Attach and create pass through here, only update backend memory
+	 * allocated in pg_stat_activity for the creator process.
+	 */
+	if (op == DSM_OP_CREATE)
+		pgstat_report_allocated_bytes_increase(request_size, PG_ALLOC_DSM);
 	*mapped_address = address;
 	*mapped_size = request_size;
 
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index dac921219f..d798c05180 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -180,6 +180,7 @@ InitProcGlobal(void)
 	ProcGlobal->checkpointerLatch = NULL;
 	pg_atomic_init_u32(&ProcGlobal->procArrayGroupFirst, INVALID_PGPROCNO);
 	pg_atomic_init_u32(&ProcGlobal->clogGroupFirst, INVALID_PGPROCNO);
+	pg_atomic_init_u64(&ProcGlobal->global_dsm_allocation, 0);
 
 	/*
 	 * Create and initialize all the PGPROC structures we'll need.  There are
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 38f91a495b..50b36ba5f7 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -49,6 +49,24 @@ int			pgstat_track_activity_query_size = 1024;
 /* exposed so that backend_progress.c can access it */
 PgBackendStatus *MyBEEntry = NULL;
 
+/*
+ * Memory allocated to this backend prior to pgstats initialization. Migrated to
+ * shared memory on pgstats initialization.
+ */
+uint64		local_my_allocated_bytes = 0;
+uint64	   *my_allocated_bytes = &local_my_allocated_bytes;
+
+/* Memory allocated to this backend by type prior to pgstats initialization.
+ * Migrated to shared memory on pgstats initialization
+ */
+uint64		local_my_aset_allocated_bytes = 0;
+uint64	   *my_aset_allocated_bytes = &local_my_aset_allocated_bytes;
+uint64		local_my_dsm_allocated_bytes = 0;
+uint64	   *my_dsm_allocated_bytes = &local_my_dsm_allocated_bytes;
+uint64		local_my_generation_allocated_bytes = 0;
+uint64	   *my_generation_allocated_bytes = &local_my_generation_allocated_bytes;
+uint64		local_my_slab_allocated_bytes = 0;
+uint64	   *my_slab_allocated_bytes = &local_my_slab_allocated_bytes;
 
 static PgBackendStatus *BackendStatusArray = NULL;
 static char *BackendAppnameBuffer = NULL;
@@ -401,6 +419,32 @@ pgstat_bestart(void)
 	lbeentry.st_progress_command_target = InvalidOid;
 	lbeentry.st_query_id = UINT64CONST(0);
 
+	/* Alter allocation reporting from local storage to shared memory */
+	pgstat_set_allocated_bytes_storage(&MyBEEntry->allocated_bytes,
+									   &MyBEEntry->aset_allocated_bytes,
+									   &MyBEEntry->dsm_allocated_bytes,
+									   &MyBEEntry->generation_allocated_bytes,
+									   &MyBEEntry->slab_allocated_bytes);
+
+	/*
+	 * Populate sum of memory allocated prior to pgstats initialization to
+	 * pgstats and zero the local variable. This is a += assignment because
+	 * InitPostgres allocates memory after pgstat_beinit but prior to
+	 * pgstat_bestart so we have allocations to both local and shared memory
+	 * to combine.
+	 */
+	lbeentry.allocated_bytes += local_my_allocated_bytes;
+	local_my_allocated_bytes = 0;
+	lbeentry.aset_allocated_bytes += local_my_aset_allocated_bytes;
+	local_my_aset_allocated_bytes = 0;
+
+	lbeentry.dsm_allocated_bytes += local_my_dsm_allocated_bytes;
+	local_my_dsm_allocated_bytes = 0;
+	lbeentry.generation_allocated_bytes += local_my_generation_allocated_bytes;
+	local_my_generation_allocated_bytes = 0;
+	lbeentry.slab_allocated_bytes += local_my_slab_allocated_bytes;
+	local_my_slab_allocated_bytes = 0;
+
 	/*
 	 * we don't zero st_progress_param here to save cycles; nobody should
 	 * examine it until st_progress_command has been set to something other
@@ -460,6 +504,9 @@ pgstat_beshutdown_hook(int code, Datum arg)
 {
 	volatile PgBackendStatus *beentry = MyBEEntry;
 
+	/* Stop reporting memory allocation changes to shared memory */
+	pgstat_reset_allocated_bytes_storage();
+
 	/*
 	 * Clear my status entry, following the protocol of bumping st_changecount
 	 * before and after.  We use a volatile pointer here to ensure the
@@ -1195,3 +1242,70 @@ pgstat_clip_activity(const char *raw_activity)
 
 	return activity;
 }
+
+/*
+ * Configure bytes allocated reporting to report allocated bytes to
+ * shared memory.
+ *
+ * Expected to be called during backend startup (in pgstat_bestart), to point
+ * allocated bytes accounting into shared memory.
+ */
+void
+pgstat_set_allocated_bytes_storage(uint64 *allocated_bytes,
+								   uint64 *aset_allocated_bytes,
+								   uint64 *dsm_allocated_bytes,
+								   uint64 *generation_allocated_bytes,
+								   uint64 *slab_allocated_bytes)
+{
+	/* Map allocations to shared memory */
+	my_allocated_bytes = allocated_bytes;
+	*allocated_bytes = local_my_allocated_bytes;
+
+	my_aset_allocated_bytes = aset_allocated_bytes;
+	*aset_allocated_bytes = local_my_aset_allocated_bytes;
+
+	my_dsm_allocated_bytes = dsm_allocated_bytes;
+	*dsm_allocated_bytes = local_my_dsm_allocated_bytes;
+
+	my_generation_allocated_bytes = generation_allocated_bytes;
+	*generation_allocated_bytes = local_my_generation_allocated_bytes;
+
+	my_slab_allocated_bytes = slab_allocated_bytes;
+	*slab_allocated_bytes = local_my_slab_allocated_bytes;
+}
+
+/*
+ * Reset allocated bytes storage location.
+ *
+ * Expected to be called during backend shutdown, before the locations set up
+ * by pgstat_set_allocated_bytes_storage become invalid.
+ */
+void
+pgstat_reset_allocated_bytes_storage(void)
+{
+	if (ProcGlobal)
+	{
+		volatile PROC_HDR *procglobal = ProcGlobal;
+
+		/*
+		 * Add dsm allocations that have not been freed to global dsm
+		 * accounting
+		 */
+		pg_atomic_add_fetch_u64(&procglobal->global_dsm_allocation,
+								*my_dsm_allocated_bytes);
+	}
+
+	/* Reset memory allocation variables */
+	*my_allocated_bytes = local_my_allocated_bytes = 0;
+	*my_aset_allocated_bytes = local_my_aset_allocated_bytes = 0;
+	*my_dsm_allocated_bytes = local_my_dsm_allocated_bytes = 0;
+	*my_generation_allocated_bytes = local_my_generation_allocated_bytes = 0;
+	*my_slab_allocated_bytes = local_my_slab_allocated_bytes = 0;
+
+	/* Point my_{*_}allocated_bytes from shared memory back to local */
+	my_allocated_bytes = &local_my_allocated_bytes;
+	my_aset_allocated_bytes = &local_my_aset_allocated_bytes;
+	my_dsm_allocated_bytes = &local_my_dsm_allocated_bytes;
+	my_generation_allocated_bytes = &local_my_generation_allocated_bytes;
+	my_slab_allocated_bytes = &local_my_slab_allocated_bytes;
+}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 49adc319fc..35f2d2bffe 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2062,3 +2062,87 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 
 	PG_RETURN_BOOL(pgstat_have_entry(kind, dboid, objoid));
 }
+
+/*
+ * Get the memory allocation of PG backends.
+ */
+Datum
+pg_stat_get_memory_allocation(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_MEMORY_ALLOCATION_COLS	7
+	int			num_backends = pgstat_fetch_stat_numbackends();
+	int			curr_backend;
+	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	/* 1-based index */
+	for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+	{
+		/* for each row */
+		Datum		values[PG_STAT_GET_MEMORY_ALLOCATION_COLS] = {0};
+		bool		nulls[PG_STAT_GET_MEMORY_ALLOCATION_COLS] = {0};
+		LocalPgBackendStatus *local_beentry;
+		PgBackendStatus *beentry;
+
+		/* Get the next one in the list */
+		local_beentry = pgstat_fetch_stat_local_beentry(curr_backend);
+		beentry = &local_beentry->backendStatus;
+
+		/* If looking for specific PID, ignore all the others */
+		if (pid != -1 && beentry->st_procpid != pid)
+			continue;
+
+		/* Values available to all callers */
+		if (beentry->st_databaseid != InvalidOid)
+			values[0] = ObjectIdGetDatum(beentry->st_databaseid);
+		else
+			nulls[0] = true;
+
+		values[1] = Int32GetDatum(beentry->st_procpid);
+		values[2] = UInt64GetDatum(beentry->allocated_bytes);
+		values[3] = UInt64GetDatum(beentry->aset_allocated_bytes);
+		values[4] = UInt64GetDatum(beentry->dsm_allocated_bytes);
+		values[5] = UInt64GetDatum(beentry->generation_allocated_bytes);
+		values[6] = UInt64GetDatum(beentry->slab_allocated_bytes);
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+
+		/* If only a single backend was requested, and we found it, break. */
+		if (pid != -1)
+			break;
+	}
+
+	return (Datum) 0;
+}
+
+/*
+ * Get the global memory allocation statistics.
+ */
+Datum
+pg_stat_get_global_memory_allocation(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS	2
+	TupleDesc	tupdesc;
+	Datum		values[PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS] = {0};
+	bool		nulls[PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS] = {0};
+	volatile PROC_HDR *procglobal = ProcGlobal;
+
+	/* Initialise attributes information in the tuple descriptor */
+	tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid",
+					   OIDOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "global_dsm_allocated_bytes",
+					   INT8OID, -1, 0);
+	BlessTupleDesc(tupdesc);
+
+	/* datid */
+	values[0] = ObjectIdGetDatum(MyDatabaseId);
+
+	/* get global_dsm_allocated_bytes */
+	values[1] = Int64GetDatum(pg_atomic_read_u64(&procglobal->global_dsm_allocation));
+
+	/* Returns the record as Datum */
+	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index a604432126..7b8eeb7dbb 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -171,6 +171,9 @@ InitPostmasterChild(void)
 				(errcode_for_socket_access(),
 				 errmsg_internal("could not set postmaster death monitoring pipe to FD_CLOEXEC mode: %m")));
 #endif
+
+	/* Init allocated bytes to avoid double counting parent allocation */
+	pgstat_init_allocated_bytes();
 }
 
 /*
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 0bbbf93672..4146831d75 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -47,6 +47,7 @@
 #include "postgres.h"
 
 #include "port/pg_bitutils.h"
+#include "utils/backend_status.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
 #include "utils/memutils_memorychunk.h"
@@ -513,6 +514,7 @@ AllocSetContextCreateInternal(MemoryContext parent,
 						name);
 
 	((MemoryContext) set)->mem_allocated = firstBlockSize;
+	pgstat_report_allocated_bytes_increase(firstBlockSize, PG_ALLOC_ASET);
 
 	return (MemoryContext) set;
 }
@@ -535,6 +537,7 @@ AllocSetReset(MemoryContext context)
 	AllocSet	set = (AllocSet) context;
 	AllocBlock	block;
 	Size		keepersize PG_USED_FOR_ASSERTS_ONLY;
+	uint64		deallocation = 0;
 
 	Assert(AllocSetIsValid(set));
 
@@ -577,6 +580,7 @@ AllocSetReset(MemoryContext context)
 		{
 			/* Normal case, release the block */
 			context->mem_allocated -= block->endptr - ((char *) block);
+			deallocation += block->endptr - ((char *) block);
 
 #ifdef CLOBBER_FREED_MEMORY
 			wipe_mem(block, block->freeptr - ((char *) block));
@@ -587,6 +591,7 @@ AllocSetReset(MemoryContext context)
 	}
 
 	Assert(context->mem_allocated == keepersize);
+	pgstat_report_allocated_bytes_decrease(deallocation, PG_ALLOC_ASET);
 
 	/* Reset block size allocation sequence, too */
 	set->nextBlockSize = set->initBlockSize;
@@ -605,6 +610,7 @@ AllocSetDelete(MemoryContext context)
 	AllocSet	set = (AllocSet) context;
 	AllocBlock	block = set->blocks;
 	Size		keepersize PG_USED_FOR_ASSERTS_ONLY;
+	uint64		deallocation = 0;
 
 	Assert(AllocSetIsValid(set));
 
@@ -643,11 +649,13 @@ AllocSetDelete(MemoryContext context)
 
 				freelist->first_free = (AllocSetContext *) oldset->header.nextchild;
 				freelist->num_free--;
+				deallocation += oldset->header.mem_allocated;
 
 				/* All that remains is to free the header/initial block */
 				free(oldset);
 			}
 			Assert(freelist->num_free == 0);
+			pgstat_report_allocated_bytes_decrease(deallocation, PG_ALLOC_ASET);
 		}
 
 		/* Now add the just-deleted context to the freelist. */
@@ -664,7 +672,10 @@ AllocSetDelete(MemoryContext context)
 		AllocBlock	next = block->next;
 
 		if (block != set->keeper)
+		{
 			context->mem_allocated -= block->endptr - ((char *) block);
+			deallocation += block->endptr - ((char *) block);
+		}
 
 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, block->freeptr - ((char *) block));
@@ -677,6 +688,7 @@ AllocSetDelete(MemoryContext context)
 	}
 
 	Assert(context->mem_allocated == keepersize);
+	pgstat_report_allocated_bytes_decrease(deallocation + context->mem_allocated, PG_ALLOC_ASET);
 
 	/* Finally, free the context header, including the keeper block */
 	free(set);
@@ -726,6 +738,7 @@ AllocSetAlloc(MemoryContext context, Size size)
 			return NULL;
 
 		context->mem_allocated += blksize;
+		pgstat_report_allocated_bytes_increase(blksize, PG_ALLOC_ASET);
 
 		block->aset = set;
 		block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -939,6 +952,7 @@ AllocSetAlloc(MemoryContext context, Size size)
 			return NULL;
 
 		context->mem_allocated += blksize;
+		pgstat_report_allocated_bytes_increase(blksize, PG_ALLOC_ASET);
 
 		block->aset = set;
 		block->freeptr = ((char *) block) + ALLOC_BLOCKHDRSZ;
@@ -1036,6 +1050,7 @@ AllocSetFree(void *pointer)
 			block->next->prev = block->prev;
 
 		set->header.mem_allocated -= block->endptr - ((char *) block);
+		pgstat_report_allocated_bytes_decrease(block->endptr - ((char *) block), PG_ALLOC_ASET);
 
 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, block->freeptr - ((char *) block));
@@ -1166,7 +1181,9 @@ AllocSetRealloc(void *pointer, Size size)
 
 		/* updated separately, not to underflow when (oldblksize > blksize) */
 		set->header.mem_allocated -= oldblksize;
+		pgstat_report_allocated_bytes_decrease(oldblksize, PG_ALLOC_ASET);
 		set->header.mem_allocated += blksize;
+		pgstat_report_allocated_bytes_increase(blksize, PG_ALLOC_ASET);
 
 		block->freeptr = block->endptr = ((char *) block) + blksize;
 
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 4fb8663cd6..502f877855 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -37,6 +37,7 @@
 
 #include "lib/ilist.h"
 #include "port/pg_bitutils.h"
+#include "utils/backend_status.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
 #include "utils/memutils_memorychunk.h"
@@ -259,6 +260,7 @@ GenerationContextCreate(MemoryContext parent,
 						name);
 
 	((MemoryContext) set)->mem_allocated = firstBlockSize;
+	pgstat_report_allocated_bytes_increase(firstBlockSize, PG_ALLOC_GENERATION);
 
 	return (MemoryContext) set;
 }
@@ -275,6 +277,7 @@ GenerationReset(MemoryContext context)
 {
 	GenerationContext *set = (GenerationContext *) context;
 	dlist_mutable_iter miter;
+	uint64		deallocation = 0;
 
 	Assert(GenerationIsValid(set));
 
@@ -297,9 +300,14 @@ GenerationReset(MemoryContext context)
 		if (block == set->keeper)
 			GenerationBlockMarkEmpty(block);
 		else
+		{
+			deallocation += block->blksize;
 			GenerationBlockFree(set, block);
+		}
 	}
 
+	pgstat_report_allocated_bytes_decrease(deallocation, PG_ALLOC_GENERATION);
+
 	/* set it so new allocations to make use of the keeper block */
 	set->block = set->keeper;
 
@@ -320,6 +328,9 @@ GenerationDelete(MemoryContext context)
 {
 	/* Reset to release all releasable GenerationBlocks */
 	GenerationReset(context);
+
+	pgstat_report_allocated_bytes_decrease(context->mem_allocated, PG_ALLOC_GENERATION);
+
 	/* And free the context header and keeper block */
 	free(context);
 }
@@ -366,6 +377,7 @@ GenerationAlloc(MemoryContext context, Size size)
 			return NULL;
 
 		context->mem_allocated += blksize;
+		pgstat_report_allocated_bytes_increase(blksize, PG_ALLOC_GENERATION);
 
 		/* block with a single (used) chunk */
 		block->context = set;
@@ -469,6 +481,7 @@ GenerationAlloc(MemoryContext context, Size size)
 				return NULL;
 
 			context->mem_allocated += blksize;
+			pgstat_report_allocated_bytes_increase(blksize, PG_ALLOC_GENERATION);
 
 			/* initialize the new block */
 			GenerationBlockInit(set, block, blksize);
@@ -721,6 +734,8 @@ GenerationFree(void *pointer)
 	dlist_delete(&block->node);
 
 	set->header.mem_allocated -= block->blksize;
+	pgstat_report_allocated_bytes_decrease(block->blksize, PG_ALLOC_GENERATION);
+
 	free(block);
 }
 
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index 718dd2ba03..913787dba8 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -69,6 +69,7 @@
 #include "postgres.h"
 
 #include "lib/ilist.h"
+#include "utils/backend_status.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
 #include "utils/memutils_memorychunk.h"
@@ -413,6 +414,13 @@ SlabContextCreate(MemoryContext parent,
 						parent,
 						name);
 
+	/*
+	 * If SlabContextCreate is updated to add context header size to
+	 * context->mem_allocated, then update here and SlabDelete appropriately
+	 */
+	pgstat_report_allocated_bytes_increase(Slab_CONTEXT_HDRSZ(slab->chunksPerBlock),
+										   PG_ALLOC_SLAB);
+
 	return (MemoryContext) slab;
 }
 
@@ -429,6 +437,7 @@ SlabReset(MemoryContext context)
 	SlabContext *slab = (SlabContext *) context;
 	dlist_mutable_iter miter;
 	int			i;
+	uint64		deallocation = 0;
 
 	Assert(SlabIsValid(slab));
 
@@ -449,6 +458,7 @@ SlabReset(MemoryContext context)
 #endif
 		free(block);
 		context->mem_allocated -= slab->blockSize;
+		deallocation += slab->blockSize;
 	}
 
 	/* walk over blocklist and free the blocks */
@@ -465,9 +475,11 @@ SlabReset(MemoryContext context)
 #endif
 			free(block);
 			context->mem_allocated -= slab->blockSize;
+			deallocation += slab->blockSize;
 		}
 	}
 
+	pgstat_report_allocated_bytes_decrease(deallocation, PG_ALLOC_SLAB);
 	slab->curBlocklistIndex = 0;
 
 	Assert(context->mem_allocated == 0);
@@ -482,6 +494,14 @@ SlabDelete(MemoryContext context)
 {
 	/* Reset to release all the SlabBlocks */
 	SlabReset(context);
+
+	/*
+	 * Until context header allocation is included in context->mem_allocated,
+	 * cast to slab and decrement the header allocation
+	 */
+	pgstat_report_allocated_bytes_decrease(Slab_CONTEXT_HDRSZ(((SlabContext *) context)->chunksPerBlock),
+										   PG_ALLOC_SLAB);
+
 	/* And free the context header */
 	free(context);
 }
@@ -546,6 +566,7 @@ SlabAlloc(MemoryContext context, Size size)
 
 			block->slab = slab;
 			context->mem_allocated += slab->blockSize;
+			pgstat_report_allocated_bytes_increase(slab->blockSize, PG_ALLOC_SLAB);
 
 			/* use the first chunk in the new block */
 			chunk = SlabBlockGetChunk(slab, block, 0);
@@ -740,6 +761,7 @@ SlabFree(void *pointer)
 #endif
 			free(block);
 			slab->header.mem_allocated -= slab->blockSize;
+			pgstat_report_allocated_bytes_decrease(slab->blockSize, PG_ALLOC_SLAB);
 		}
 
 		/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..b095740f8a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5408,6 +5408,23 @@
   proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
   provolatile => 's', proparallel => 'r', prorettype => 'int4',
   proargtypes => '', prosrc => 'pg_stat_get_backend_idset' },
+{ oid => '9890',
+  descr => 'statistics: memory allocation information for backends',
+  proname => 'pg_stat_get_memory_allocation', prorows => '100', proisstrict => 'f',
+  proretset => 't', provolatile => 's', proparallel => 'r',
+  prorettype => 'record', proargtypes => 'int4',
+  proallargtypes => '{int4,oid,int4,int8,int8,int8,int8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o}',
+  proargnames => '{pid,datid,pid,allocated_bytes,aset_allocated_bytes,dsm_allocated_bytes,generation_allocated_bytes,slab_allocated_bytes}',
+  prosrc => 'pg_stat_get_memory_allocation' },
+{ oid => '9891',
+  descr => 'statistics: global memory allocation information',
+  proname => 'pg_stat_get_global_memory_allocation', proisstrict => 'f',
+  provolatile => 's', proparallel => 'r', prorettype => 'record',
+  proargtypes => '', proallargtypes => '{oid,int8}',
+  proargmodes => '{o,o}',
+  proargnames => '{datid,global_dsm_allocated_bytes}',
+  prosrc =>'pg_stat_get_global_memory_allocation' },
 { oid => '2022',
   descr => 'statistics: information about currently active backends',
   proname => 'pg_stat_get_activity', prorows => '100', proisstrict => 'f',
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index ef74f32693..e6be67de2a 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -404,6 +404,8 @@ typedef struct PROC_HDR
 	int			spins_per_delay;
 	/* Buffer id of the buffer that Startup process waits for pin on, or -1 */
 	int			startupBufferPinWaitBufId;
+	/* Global dsm allocations */
+	pg_atomic_uint64 global_dsm_allocation;
 } PROC_HDR;
 
 extern PGDLLIMPORT PROC_HDR *ProcGlobal;
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 16500d53b2..9b75fc5223 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -10,6 +10,7 @@
 #ifndef BACKEND_STATUS_H
 #define BACKEND_STATUS_H
 
+#include "common/int.h"
 #include "datatype/timestamp.h"
 #include "libpq/pqcomm.h"
 #include "miscadmin.h"			/* for BackendType */
@@ -32,6 +33,14 @@ typedef enum BackendState
 	STATE_DISABLED
 } BackendState;
 
+/* Enum helper for reporting memory allocator type */
+enum pg_allocator_type
+{
+	PG_ALLOC_ASET = 1,
+	PG_ALLOC_DSM,
+	PG_ALLOC_GENERATION,
+	PG_ALLOC_SLAB
+};
 
 /* ----------
  * Shared-memory data structures
@@ -170,6 +179,15 @@ typedef struct PgBackendStatus
 
 	/* query identifier, optionally computed using post_parse_analyze_hook */
 	uint64		st_query_id;
+
+	/* Current memory allocated to this backend */
+	uint64		allocated_bytes;
+
+	/* Current memory allocated to this backend by type */
+	uint64		aset_allocated_bytes;
+	uint64		dsm_allocated_bytes;
+	uint64		generation_allocated_bytes;
+	uint64		slab_allocated_bytes;
 } PgBackendStatus;

@@ -294,6 +312,11 @@ extern PGDLLIMPORT int pgstat_track_activity_query_size;
  * ----------
  */
 extern PGDLLIMPORT PgBackendStatus *MyBEEntry;
+extern PGDLLIMPORT uint64 *my_allocated_bytes;
+extern PGDLLIMPORT uint64 *my_aset_allocated_bytes;
+extern PGDLLIMPORT uint64 *my_dsm_allocated_bytes;
+extern PGDLLIMPORT uint64 *my_generation_allocated_bytes;
+extern PGDLLIMPORT uint64 *my_slab_allocated_bytes;

 /* ----------
@@ -325,7 +348,12 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
-
+extern void pgstat_set_allocated_bytes_storage(uint64 *allocated_bytes,
+											   uint64 *aset_allocated_bytes,
+											   uint64 *dsm_allocated_bytes,
+											   uint64 *generation_allocated_bytes,
+											   uint64 *slab_allocated_bytes);
+extern void pgstat_reset_allocated_bytes_storage(void);
 
 /* ----------
  * Support functions for the SQL-callable functions to
@@ -337,5 +365,119 @@ extern PgBackendStatus *pgstat_fetch_stat_beentry(BackendId beid);
 extern LocalPgBackendStatus *pgstat_fetch_stat_local_beentry(int beid);
 extern char *pgstat_clip_activity(const char *raw_activity);
 
+/* ----------
+ * pgstat_report_allocated_bytes_decrease() -
+ *  Called to report decrease in memory allocated for this backend.
+ *
+ * my_{*_}allocated_bytes initially points to local memory, making it safe to
+ * call this before pgstats has been initialized.
+ * ----------
+ */
+static inline void
+pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
+									   int pg_allocator_type)
+{
+	uint64		temp;
+
+	/* Avoid allocated_bytes unsigned integer overflow on decrease */
+	if (pg_sub_u64_overflow(*my_allocated_bytes, proc_allocated_bytes, &temp))
+	{
+		/* On overflow, set allocated bytes and allocator type bytes to zero */
+		*my_allocated_bytes = 0;
+		*my_aset_allocated_bytes = 0;
+		*my_dsm_allocated_bytes = 0;
+		*my_generation_allocated_bytes = 0;
+		*my_slab_allocated_bytes = 0;
+	}
+	else
+	{
+		/* decrease allocation */
+		*my_allocated_bytes -= proc_allocated_bytes;
+
+		/* Decrease allocator type allocated bytes. */
+		switch (pg_allocator_type)
+		{
+			case PG_ALLOC_ASET:
+				*my_aset_allocated_bytes -= proc_allocated_bytes;
+				break;
+			case PG_ALLOC_DSM:
+
+				/*
+				 * Some dsm allocations live beyond process exit. These are
+				 * accounted for in a global counter in
+				 * pgstat_reset_allocated_bytes_storage at process exit.
+				 */
+				*my_dsm_allocated_bytes -= proc_allocated_bytes;
+				break;
+			case PG_ALLOC_GENERATION:
+				*my_generation_allocated_bytes -= proc_allocated_bytes;
+				break;
+			case PG_ALLOC_SLAB:
+				*my_slab_allocated_bytes -= proc_allocated_bytes;
+				break;
+		}
+	}
+
+	return;
+}
+
+/* ----------
+ * pgstat_report_allocated_bytes_increase() -
+ *  Called to report increase in memory allocated for this backend.
+ *
+ * my_allocated_bytes initially points to local memory, making it safe to call
+ * this before pgstats has been initialized.
+ * ----------
+ */
+static inline void
+pgstat_report_allocated_bytes_increase(int64 proc_allocated_bytes,
+									   int pg_allocator_type)
+{
+	*my_allocated_bytes += proc_allocated_bytes;
+
+	/* Increase allocator type allocated bytes */
+	switch (pg_allocator_type)
+	{
+		case PG_ALLOC_ASET:
+			*my_aset_allocated_bytes += proc_allocated_bytes;
+			break;
+		case PG_ALLOC_DSM:
+
+			/*
+			 * Some dsm allocations live beyond process exit. These are
+			 * accounted for in a global counter in
+			 * pgstat_reset_allocated_bytes_storage at process exit.
+			 */
+			*my_dsm_allocated_bytes += proc_allocated_bytes;
+			break;
+		case PG_ALLOC_GENERATION:
+			*my_generation_allocated_bytes += proc_allocated_bytes;
+			break;
+		case PG_ALLOC_SLAB:
+			*my_slab_allocated_bytes += proc_allocated_bytes;
+			break;
+	}
+
+	return;
+}
+
+/* ---------
+ * pgstat_init_allocated_bytes() -
+ *
+ * Called to initialize allocated bytes variables after fork and to
+ * avoid double counting allocations.
+ * ---------
+ */
+static inline void
+pgstat_init_allocated_bytes(void)
+{
+	*my_allocated_bytes = 0;
+	*my_aset_allocated_bytes = 0;
+	*my_dsm_allocated_bytes = 0;
+	*my_generation_allocated_bytes = 0;
+	*my_slab_allocated_bytes = 0;
+
+	return;
+}
 
 #endif							/* BACKEND_STATUS_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7fd81e6a7d..ff76aa99a2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1873,6 +1873,24 @@ pg_stat_database_conflicts| SELECT oid AS datid,
     pg_stat_get_db_conflict_startup_deadlock(oid) AS confl_deadlock,
     pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot
    FROM pg_database d;
+pg_stat_global_memory_allocation| WITH sums AS (
+         SELECT sum(pg_stat_memory_allocation.aset_allocated_bytes) AS total_aset_allocated_bytes,
+            sum(pg_stat_memory_allocation.dsm_allocated_bytes) AS total_dsm_allocated_bytes,
+            sum(pg_stat_memory_allocation.generation_allocated_bytes) AS total_generation_allocated_bytes,
+            sum(pg_stat_memory_allocation.slab_allocated_bytes) AS total_slab_allocated_bytes
+           FROM pg_stat_memory_allocation
+        )
+ SELECT s.datid,
+    current_setting('shared_memory_size'::text, true) AS shared_memory_size,
+    (current_setting('shared_memory_size_in_huge_pages'::text, true))::integer AS shared_memory_size_in_huge_pages,
+    s.global_dsm_allocated_bytes,
+    sums.total_aset_allocated_bytes,
+    sums.total_dsm_allocated_bytes,
+    sums.total_generation_allocated_bytes,
+    sums.total_slab_allocated_bytes
+   FROM sums,
+    (pg_stat_get_global_memory_allocation() s(datid, global_dsm_allocated_bytes)
+     LEFT JOIN pg_database d ON ((s.datid = d.oid)));
 pg_stat_gssapi| SELECT pid,
     gss_auth AS gss_authenticated,
     gss_princ AS principal,
@@ -1899,6 +1917,15 @@ pg_stat_io| SELECT backend_type,
     fsync_time,
     stats_reset
    FROM pg_stat_get_io() b(backend_type, object, context, reads, read_time, writes, write_time, writebacks, writeback_time, extends, extend_time, op_bytes, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);
+pg_stat_memory_allocation| SELECT s.datid,
+    s.pid,
+    s.allocated_bytes,
+    s.aset_allocated_bytes,
+    s.dsm_allocated_bytes,
+    s.generation_allocated_bytes,
+    s.slab_allocated_bytes
+   FROM (pg_stat_get_memory_allocation(NULL::integer) s(datid, pid, allocated_bytes, aset_allocated_bytes, dsm_allocated_bytes, generation_allocated_bytes, slab_allocated_bytes)
+     LEFT JOIN pg_database d ON ((s.datid = d.oid)));
 pg_stat_progress_analyze| SELECT s.pid,
     s.datid,
     d.datname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8e63340782..748c337ee7 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1572,4 +1572,40 @@ SELECT COUNT(*) FROM brin_hot_3 WHERE a = 2;
 
 DROP TABLE brin_hot_3;
 SET enable_seqscan = on;
+-- ensure that allocated_bytes exist for backends
+SELECT
+    allocated_bytes > 0 AS result
+FROM
+    pg_stat_activity ps
+    JOIN pg_stat_memory_allocation pa ON (pa.pid = ps.pid)
+WHERE
+    backend_type IN ('checkpointer', 'background writer', 'walwriter', 'autovacuum launcher');
+ result 
+--------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- ensure that pg_stat_global_memory_allocation view exists
+SELECT
+    datid > 0, pg_size_bytes(shared_memory_size) >= 0, shared_memory_size_in_huge_pages >= -1, global_dsm_allocated_bytes >= 0
+FROM
+    pg_stat_global_memory_allocation;
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+-- ensure that pg_stat_memory_allocation view exists
+SELECT
+    pid > 0, allocated_bytes >= 0, aset_allocated_bytes >= 0, dsm_allocated_bytes >= 0, generation_allocated_bytes >= 0, slab_allocated_bytes >= 0
+FROM
+    pg_stat_memory_allocation limit 1;
+ ?column? | ?column? | ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------+----------+----------
+ t        | t        | t        | t        | t        | t
+(1 row)
+
 -- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index fddf5a8277..a01f2545ba 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -805,4 +805,24 @@ DROP TABLE brin_hot_3;
 
 SET enable_seqscan = on;
 
+-- ensure that allocated_bytes exist for backends
+SELECT
+    allocated_bytes > 0 AS result
+FROM
+    pg_stat_activity ps
+    JOIN pg_stat_memory_allocation pa ON (pa.pid = ps.pid)
+WHERE
+    backend_type IN ('checkpointer', 'background writer', 'walwriter', 'autovacuum launcher');
+
+-- ensure that pg_stat_global_memory_allocation view exists
+SELECT
+    datid > 0, pg_size_bytes(shared_memory_size) >= 0, shared_memory_size_in_huge_pages >= -1, global_dsm_allocated_bytes >= 0
+FROM
+    pg_stat_global_memory_allocation;
+
+-- ensure that pg_stat_memory_allocation view exists
+SELECT
+    pid > 0, allocated_bytes >= 0, aset_allocated_bytes >= 0, dsm_allocated_bytes >= 0, generation_allocated_bytes >= 0, slab_allocated_bytes >= 0
+FROM
+    pg_stat_memory_allocation limit 1;
 -- End of Stats Test
-- 
2.25.1

From f5a7a2ccfd975cb29b47af4fff563947085e1748 Mon Sep 17 00:00:00 2001
From: Reid Thompson <[email-masked]>
Date: Sat, 4 Jun 2022 22:23:59 -0400
Subject: [PATCH 2/2] Add the ability to limit the amount of memory that can be
 allocated to backends.

This builds on the work that adds backend memory allocated tracking.

Add GUC variable max_total_backend_memory.

Specifies a limit to the amount of memory (in MB) that may be allocated to
backends in total (i.e. this is not a per user or per backend limit). If unset,
or set to 0 it is disabled. It is intended as a resource to help avoid the OOM
killer on LINUX and manage resources in general. A backend request that would
exhaust max_total_backend_memory memory will be denied with an out of memory
error causing that backend's current query/transaction to fail.  Further
requests will not be allocated until dropping below the limit. Keep this in
mind when setting this value. Due to the dynamic nature of memory allocations,
this limit is not exact. This limit does not affect auxiliary backend
processes. Backend memory allocations are displayed in the
pg_stat_memory_allocation and pg_stat_global_memory_allocation views.
---
 doc/src/sgml/config.sgml                      |  30 ++++
 doc/src/sgml/monitoring.sgml                  |  38 ++++-
 src/backend/catalog/system_views.sql          |   2 +
 src/backend/port/sysv_shmem.c                 |   9 ++
 src/backend/postmaster/postmaster.c           |   5 +
 src/backend/storage/ipc/dsm_impl.c            |  18 +++
 src/backend/storage/lmgr/proc.c               |  45 ++++++
 src/backend/utils/activity/backend_status.c   | 147 ++++++++++++++++++
 src/backend/utils/adt/pgstatfuncs.c           |  16 +-
 src/backend/utils/hash/dynahash.c             |   3 +-
 src/backend/utils/init/miscinit.c             |   8 +
 src/backend/utils/misc/guc_tables.c           |  11 ++
 src/backend/utils/misc/postgresql.conf.sample |   3 +
 src/backend/utils/mmgr/aset.c                 |  33 ++++
 src/backend/utils/mmgr/generation.c           |  16 ++
 src/backend/utils/mmgr/slab.c                 |  15 +-
 src/include/catalog/pg_proc.dat               |   6 +-
 src/include/storage/proc.h                    |   7 +
 src/include/utils/backend_status.h            | 102 +++++++++++-
 src/test/regress/expected/rules.out           |   4 +-
 20 files changed, 498 insertions(+), 20 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5da74b3c40..397661d4b2 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2180,6 +2180,36 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-max-total-backend-memory" xreflabel="max_total_backend_memory">
+      <term><varname>max_total_backend_memory</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>max_total_backend_memory</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies a limit to the amount of memory (MB) that may be allocated to
+        backends in total (i.e. this is not a per user or per backend limit).
+        If unset, or set to 0 it is disabled.  At databse startup
+        max_total_backend_memory is reduced by shared_memory_size_mb
+        (includes shared buffers and other memory required for initialization).
+        Each backend process is intialized with a 1MB local allowance which
+        also reduces total_bkend_mem_bytes_available. Keep this in mind when
+        setting this value. A backend request that would exhaust the limit will
+        be denied with an out of memory error causing that backend's current
+        query/transaction to fail. Further requests will not be allocated until
+        dropping below the limit.  This limit does not affect auxiliary backend
+        processes
+        <xref linkend="glossary-auxiliary-proc"/> or the postmaster process.
+        Backend memory allocations (<varname>allocated_bytes</varname>) are
+        displayed in the
+        <link linkend="monitoring-pg-stat-memory-allocation-view"><structname>pg_stat_memory_allocation</structname></link>
+        view.  Due to the dynamic nature of memory allocations, this limit is
+        not exact.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
 
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index cfc221fb2e..aa53e0be3e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5817,10 +5817,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
       </para>
      <para>
       Memory currently allocated to this backend in bytes. This is the balance
-      of bytes allocated and freed by this backend. Dynamic shared memory
-      allocations are included only in the value displayed for the backend that
-      created them, they are not included in the value for backends that are
-      attached to them to avoid double counting.
+      of bytes allocated and freed by this backend.
      </para></entry>
      </row>
 
@@ -5937,6 +5934,39 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
      </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>max_total_backend_memory_bytes</structfield> <type>bigint</type>
+      </para>
+     <para>
+      Reports the user defined backend maximum allowed shared memory in bytes.
+      0 if disabled or not set. See
+      <xref linkend="guc-max-total-backend-memory"/>.
+     </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_bkend_mem_bytes_available</structfield> <type>bigint</type>
+      </para>
+     <para>
+      Tracks max_total_backend_memory (in bytes) available for allocation. At
+      database startup, total_bkend_mem_bytes_available is reduced by the
+      byte equivalent of shared_memory_size_mb. Each backend process is
+      intialized with a 1MB local allowance which also reduces
+      total_bkend_mem_bytes_available. A process's allocation requests reduce
+      it's local allowance. If a process's allocation request exceeds it's
+      remaining allowance, an attempt is made to refill the local allowance
+      from total_bkend_mem_bytes_available. If the refill request fails, then
+      the requesting process will fail with an out of memory error resulting
+      in the cancellation of that process's active query/transaction.  The
+      default refill allocation quantity is 1MB.  If a request is greater than
+      1MB, an attempt will be made to allocate the full amount. If
+      max_total_backend_memory is disabled, this will be -1.
+      <xref linkend="guc-max-total-backend-memory"/>.
+     </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>global_dsm_allocated_bytes</structfield> <type>bigint</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index cc8219c665..0832027727 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1368,6 +1368,8 @@ SELECT
         S.datid AS datid,
         current_setting('shared_memory_size', true) as shared_memory_size,
         (current_setting('shared_memory_size_in_huge_pages', true))::integer as shared_memory_size_in_huge_pages,
+        pg_size_bytes(current_setting('max_total_backend_memory', true)) as max_total_backend_memory_bytes,
+        S.total_bkend_mem_bytes_available,
         S.global_dsm_allocated_bytes,
         sums.total_aset_allocated_bytes,
         sums.total_dsm_allocated_bytes,
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index eaba244bc9..463bf2e90f 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -34,6 +34,7 @@
 #include "storage/fd.h"
 #include "storage/ipc.h"
 #include "storage/pg_shmem.h"
+#include "utils/backend_status.h"
 #include "utils/guc_hooks.h"
 #include "utils/pidfile.h"
 
@@ -903,6 +904,14 @@ PGSharedMemoryReAttach(void)
 	dsm_set_control_handle(hdr->dsm_control);
 
 	UsedShmemSegAddr = hdr;		/* probably redundant */
+
+	/*
+	 * Init allocated bytes to avoid double counting parent allocation for
+	 * fork/exec processes. Forked processes perform this action in
+	 * InitPostmasterChild. For EXEC_BACKEND processes we have to wait for
+	 * shared memory to be reattached.
+	 */
+	pgstat_init_allocated_bytes();
 }
 
 /*
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 4c49393fc5..06a773c8bb 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -540,6 +540,7 @@ typedef struct
 #endif
 	char		my_exec_path[MAXPGPATH];
 	char		pkglib_path[MAXPGPATH];
+	int			max_total_bkend_mem;
 } BackendParameters;
 
 static void read_backend_variables(char *id, Port *port);
@@ -6122,6 +6123,8 @@ save_backend_variables(BackendParameters *param, Port *port,
 
 	strlcpy(param->pkglib_path, pkglib_path, MAXPGPATH);
 
+	param->max_total_bkend_mem = max_total_bkend_mem;
+
 	return true;
 }
 
@@ -6352,6 +6355,8 @@ restore_backend_variables(BackendParameters *param, Port *port)
 
 	strlcpy(pkglib_path, param->pkglib_path, MAXPGPATH);
 
+	max_total_bkend_mem = param->max_total_bkend_mem;
+
 	/*
 	 * We need to restore fd.c's counts of externally-opened FDs; to avoid
 	 * confusion, be sure to do this after restoring max_safe_fds.  (Note:
diff --git a/src/backend/storage/ipc/dsm_impl.c b/src/backend/storage/ipc/dsm_impl.c
index f43bad4439..41ffe48aa3 100644
--- a/src/backend/storage/ipc/dsm_impl.c
+++ b/src/backend/storage/ipc/dsm_impl.c
@@ -254,6 +254,16 @@ dsm_impl_posix(dsm_op op, dsm_handle handle, Size request_size,
 		return true;
 	}
 
+	/* Do not exceed maximum allowed memory allocation */
+	if (op == DSM_OP_CREATE && exceeds_max_total_bkend_mem(request_size))
+	{
+		ereport(elevel,
+				(errcode_for_dynamic_shared_memory(),
+				 errmsg("out of memory for segment \"%s\" - exceeds max_total_backend_memory: %m",
+						name)));
+		return false;
+	}
+
 	/*
 	 * Create new segment or open an existing one for attach.
 	 *
@@ -523,6 +533,10 @@ dsm_impl_sysv(dsm_op op, dsm_handle handle, Size request_size,
 		int			flags = IPCProtection;
 		size_t		segsize;
 
+		/* Do not exceed maximum allowed memory allocation */
+		if (op == DSM_OP_CREATE && exceeds_max_total_bkend_mem(request_size))
+			return false;
+
 		/*
 		 * Allocate the memory BEFORE acquiring the resource, so that we don't
 		 * leak the resource if memory allocation fails.
@@ -717,6 +731,10 @@ dsm_impl_windows(dsm_op op, dsm_handle handle, Size request_size,
 		return true;
 	}
 
+	/* Do not exceed maximum allowed memory allocation */
+	if (op == DSM_OP_CREATE && exceeds_max_total_bkend_mem(request_size))
+		return false;
+
 	/* Create new segment or open an existing one for attach. */
 	if (op == DSM_OP_CREATE)
 	{
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index d798c05180..8493ca1dbf 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -51,6 +51,7 @@
 #include "storage/procsignal.h"
 #include "storage/spin.h"
 #include "storage/standby.h"
+#include "utils/guc.h"
 #include "utils/timeout.h"
 #include "utils/timestamp.h"
 
@@ -182,6 +183,50 @@ InitProcGlobal(void)
 	pg_atomic_init_u32(&ProcGlobal->clogGroupFirst, INVALID_PGPROCNO);
 	pg_atomic_init_u64(&ProcGlobal->global_dsm_allocation, 0);
 
+	/* Setup backend memory limiting if configured */
+	if (max_total_bkend_mem > 0)
+	{
+		/*
+		 * Convert max_total_bkend_mem to bytes, account for
+		 * shared_memory_size, and initialize total_bkend_mem_bytes.
+		 */
+		int			result = 0;
+
+		/* Get integer value of shared_memory_size */
+		if (parse_int(GetConfigOption("shared_memory_size", true, false), &result, 0, NULL))
+		{
+			/*
+			 * Error on startup if backend memory limit is less than shared
+			 * memory size. Warn on startup if backend memory available is
+			 * less than arbitrarily picked value of 100MB.
+			 */
+
+			if (max_total_bkend_mem - result <= 0)
+			{
+				ereport(ERROR,
+						errmsg("configured max_total_backend_memory %dMB is <= shared_memory_size %dMB",
+							   max_total_bkend_mem, result),
+						errhint("Disable or increase the configuration parameter \"max_total_backend_memory\"."));
+			}
+			else if (max_total_bkend_mem - result <= 100)
+			{
+				ereport(WARNING,
+						errmsg("max_total_backend_memory %dMB - shared_memory_size %dMB is <= 100MB",
+							   max_total_bkend_mem, result),
+						errhint("Consider increasing the configuration parameter \"max_total_backend_memory\"."));
+			}
+
+			/*
+			 * Account for shared memory size and initialize
+			 * total_bkend_mem_bytes.
+			 */
+			pg_atomic_init_u64(&ProcGlobal->total_bkend_mem_bytes,
+							   (uint64) max_total_bkend_mem * 1024 * 1024 - (uint64) result * 1024 * 1024);
+		}
+		else
+			ereport(ERROR, errmsg("max_total_backend_memory initialization is unable to parse shared_memory_size"));
+	}
+
 	/*
 	 * Create and initialize all the PGPROC structures we'll need.  There are
 	 * five separate consumers: (1) normal backends, (2) autovacuum workers
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 50b36ba5f7..cad50b2a06 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -45,6 +45,12 @@
 bool		pgstat_track_activities = false;
 int			pgstat_track_activity_query_size = 1024;
 
+/*
+ * Max backend memory allocation allowed (MB). 0 = disabled.
+ * Centralized bucket ProcGlobal->max_total_bkend_mem is initialized
+ * as a byte representation of this value in InitProcGlobal().
+ */
+int			max_total_bkend_mem = 0;
 
 /* exposed so that backend_progress.c can access it */
 PgBackendStatus *MyBEEntry = NULL;
@@ -68,6 +74,31 @@ uint64	   *my_generation_allocated_bytes = &local_my_generation_allocated_bytes;
 uint64		local_my_slab_allocated_bytes = 0;
 uint64	   *my_slab_allocated_bytes = &local_my_slab_allocated_bytes;
 
+/*
+ * Define initial allocation allowance for a backend.
+ *
+ * NOTE: initial_allocation_allowance && allocation_allowance_refill_qty
+ * may be candidates for future GUC variables. Arbitrary 1MB selected initially.
+ */
+uint64		initial_allocation_allowance = 1024 * 1024;
+uint64		allocation_allowance_refill_qty = 1024 * 1024;
+
+/*
+ * Local counter to manage shared memory allocations. At backend startup, set to
+ * initial_allocation_allowance via pgstat_init_allocated_bytes(). Decrease as
+ * memory is malloc'd. When exhausted, atomically refill if available from
+ * ProcGlobal->max_total_bkend_mem via exceeds_max_total_bkend_mem().
+ */
+uint64		allocation_allowance = 0;
+
+/*
+ * Local counter of free'd shared memory. Return to global
+ * max_total_bkend_mem when return threshold is met. Arbitrary 1MB bytes
+ * selected initially.
+ */
+uint64		allocation_return = 0;
+uint64		allocation_return_threshold = 1024 * 1024;
+
 static PgBackendStatus *BackendStatusArray = NULL;
 static char *BackendAppnameBuffer = NULL;
 static char *BackendClientHostnameBuffer = NULL;
@@ -1272,6 +1303,8 @@ pgstat_set_allocated_bytes_storage(uint64 *allocated_bytes,
 
 	my_slab_allocated_bytes = slab_allocated_bytes;
 	*slab_allocated_bytes = local_my_slab_allocated_bytes;
+
+	return;
 }
 
 /*
@@ -1295,6 +1328,23 @@ pgstat_reset_allocated_bytes_storage(void)
 								*my_dsm_allocated_bytes);
 	}
 
+	/*
+	 * When limiting maximum backend memory, return this backend's memory
+	 * allocations to global.
+	 */
+	if (max_total_bkend_mem)
+	{
+		volatile PROC_HDR *procglobal = ProcGlobal;
+
+		pg_atomic_add_fetch_u64(&procglobal->total_bkend_mem_bytes,
+								*my_allocated_bytes + allocation_allowance +
+								allocation_return);
+
+		/* Reset memory allocation variables */
+		allocation_allowance = 0;
+		allocation_return = 0;
+	}
+
 	/* Reset memory allocation variables */
 	*my_allocated_bytes = local_my_allocated_bytes = 0;
 	*my_aset_allocated_bytes = local_my_aset_allocated_bytes = 0;
@@ -1308,4 +1358,101 @@ pgstat_reset_allocated_bytes_storage(void)
 	my_dsm_allocated_bytes = &local_my_dsm_allocated_bytes;
 	my_generation_allocated_bytes = &local_my_generation_allocated_bytes;
 	my_slab_allocated_bytes = &local_my_slab_allocated_bytes;
+
+	return;
+}
+
+/*
+ * Determine if allocation request will exceed max backend memory allowed.
+ * Do not apply to auxiliary processes.
+ * Refill allocation request bucket when needed/possible.
+ */
+bool
+exceeds_max_total_bkend_mem(uint64 allocation_request)
+{
+	bool		result = false;
+
+	/*
+	 * When limiting maximum backend memory, attempt to refill allocation
+	 * request bucket if needed.
+	 */
+	if (max_total_bkend_mem && allocation_request > allocation_allowance &&
+		ProcGlobal != NULL)
+	{
+		volatile PROC_HDR *procglobal = ProcGlobal;
+		uint64		available_max_total_bkend_mem = 0;
+		bool		sts = false;
+
+		/*
+		 * If allocation request is larger than memory refill quantity then
+		 * attempt to increase allocation allowance with requested amount,
+		 * otherwise fall through. If this refill fails we do not have enough
+		 * memory to meet the request.
+		 */
+		if (allocation_request >= allocation_allowance_refill_qty)
+		{
+			while ((available_max_total_bkend_mem = pg_atomic_read_u64(&procglobal->total_bkend_mem_bytes)) >= allocation_request)
+			{
+				if ((result = pg_atomic_compare_exchange_u64(&procglobal->total_bkend_mem_bytes,
+															 &available_max_total_bkend_mem,
+															 available_max_total_bkend_mem - allocation_request)))
+				{
+					allocation_allowance = allocation_allowance + allocation_request;
+					break;
+				}
+			}
+
+			/*
+			 * Exclude auxiliary and Postmaster processes from the check.
+			 * Return false. While we want to exclude them from the check, we
+			 * do not want to exclude them from the above allocation handling.
+			 */
+			if (MyAuxProcType != NotAnAuxProcess || MyProcPid == PostmasterPid)
+				return false;
+
+			/*
+			 * If the atomic exchange fails (result == false), we do not have
+			 * enough reserve memory to meet the request. Negate result to
+			 * return the proper value.
+			 */
+
+			return !result;
+		}
+
+		/*
+		 * Attempt to increase allocation allowance by memory refill quantity.
+		 * If available memory is/becomes less than memory refill quantity,
+		 * fall through to attempt to allocate remaining available memory.
+		 */
+		while ((available_max_total_bkend_mem = pg_atomic_read_u64(&procglobal->total_bkend_mem_bytes)) >= allocation_allowance_refill_qty)
+		{
+			if ((sts = pg_atomic_compare_exchange_u64(&procglobal->total_bkend_mem_bytes,
+													  &available_max_total_bkend_mem,
+													  available_max_total_bkend_mem - allocation_allowance_refill_qty)))
+			{
+				allocation_allowance = allocation_allowance + allocation_allowance_refill_qty;
+				break;
+			}
+		}
+
+		/* Do not attempt to increase allocation if available memory is below
+		 * allocation_allowance_refill_qty .
+		 */
+
+		/*
+		 * If refill is not successful, we return true, memory limit exceeded
+		 */
+		if (!sts)
+			result = true;
+	}
+
+	/*
+	 * Exclude auxiliary and postmaster processes from the check. Return false.
+	 * While we want to exclude them from the check, we do not want to exclude
+	 * them from the above allocation handling.
+	 */
+	if (MyAuxProcType != NotAnAuxProcess || MyProcPid == PostmasterPid)
+		result = false;
+
+	return result;
 }
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 35f2d2bffe..4bcdfc91bf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2123,7 +2123,7 @@ pg_stat_get_memory_allocation(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_global_memory_allocation(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS	2
+#define PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS	3
 	TupleDesc	tupdesc;
 	Datum		values[PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS] = {0};
 	bool		nulls[PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS] = {0};
@@ -2133,15 +2133,23 @@ pg_stat_get_global_memory_allocation(PG_FUNCTION_ARGS)
 	tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_GLOBAL_MEMORY_ALLOCATION_COLS);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid",
 					   OIDOID, -1, 0);
-	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "global_dsm_allocated_bytes",
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "total_bkend_mem_bytes_available",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "global_dsm_allocated_bytes",
 					   INT8OID, -1, 0);
 	BlessTupleDesc(tupdesc);
 
 	/* datid */
 	values[0] = ObjectIdGetDatum(MyDatabaseId);
 
-	/* get global_dsm_allocated_bytes */
-	values[1] = Int64GetDatum(pg_atomic_read_u64(&procglobal->global_dsm_allocation));
+	/* Get total_bkend_mem_bytes - return -1 if disabled */
+	if (max_total_bkend_mem == 0)
+		values[1] = Int64GetDatum(-1);
+	else
+		values[1] = Int64GetDatum(pg_atomic_read_u64(&procglobal->total_bkend_mem_bytes));
+
+	/* Get global_dsm_allocated_bytes */
+	values[2] = Int64GetDatum(pg_atomic_read_u64(&procglobal->global_dsm_allocation));
 
 	/* Returns the record as Datum */
 	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
diff --git a/src/backend/utils/hash/dynahash.c b/src/backend/utils/hash/dynahash.c
index 012d4a0b1f..cd68e5265a 100644
--- a/src/backend/utils/hash/dynahash.c
+++ b/src/backend/utils/hash/dynahash.c
@@ -104,7 +104,6 @@
 #include "utils/dynahash.h"
 #include "utils/memutils.h"
 
-
 /*
  * Constants
  *
@@ -359,7 +358,6 @@ hash_create(const char *tabname, long nelem, const HASHCTL *info, int flags)
 	Assert(flags & HASH_ELEM);
 	Assert(info->keysize > 0);
 	Assert(info->entrysize >= info->keysize);
-
 	/*
 	 * For shared hash tables, we have a local hash header (HTAB struct) that
 	 * we allocate in TopMemoryContext; all else is in shared memory.
@@ -377,6 +375,7 @@ hash_create(const char *tabname, long nelem, const HASHCTL *info, int flags)
 	}
 	else
 	{
+		/* Set up to allocate the hash header */
 		/* Create the hash table's private memory context */
 		if (flags & HASH_CONTEXT)
 			CurrentDynaHashCxt = info->hcxt;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 7b8eeb7dbb..a7df801f77 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -172,8 +172,16 @@ InitPostmasterChild(void)
 				 errmsg_internal("could not set postmaster death monitoring pipe to FD_CLOEXEC mode: %m")));
 #endif
 
+	/*
+	 * Init pgstat allocated bytes counters here for forked backends.
+	 * Fork/exec backends have not yet reattached to shared memory at this
+	 * point. They will init pgstat allocated bytes counters in
+	 * PGSharedMemoryReAttach.
+	 */
+#ifndef EXEC_BACKEND
 	/* Init allocated bytes to avoid double counting parent allocation */
 	pgstat_init_allocated_bytes();
+#endif
 }
 
 /*
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 68aecad66f..eacd1a6043 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3510,6 +3510,17 @@ struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"max_total_backend_memory", PGC_SU_BACKEND, RESOURCES_MEM,
+			gettext_noop("Restrict total backend memory allocations to this max."),
+			gettext_noop("0 turns this feature off."),
+			GUC_UNIT_MB
+		},
+		&max_total_bkend_mem,
+		0, 0, INT_MAX,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b090ec5245..5466234d64 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -161,6 +161,9 @@
 #vacuum_buffer_usage_limit = 256kB	# size of vacuum and analyze buffer access strategy ring.
 					# 0 to disable vacuum buffer access strategy
 					# range 128kB to 16GB
+#max_total_backend_memory = 0MB		# Restrict total backend memory allocations
+									# to this max (in MB). 0 turns this feature
+									# off.
 
 # - Disk -
 
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 4146831d75..a3891a607b 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -432,6 +432,18 @@ AllocSetContextCreateInternal(MemoryContext parent,
 	else
 		firstBlockSize = Max(firstBlockSize, initBlockSize);
 
+	/* Do not exceed maximum allowed memory allocation */
+	if (exceeds_max_total_bkend_mem(firstBlockSize))
+	{
+		if (TopMemoryContext)
+			MemoryContextStats(TopMemoryContext);
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("out of memory - exceeds max_total_backend_memory"),
+				 errdetail("Failed while creating memory context \"%s\".",
+						   name)));
+	}
+
 	/*
 	 * Allocate the initial block.  Unlike other aset.c blocks, it starts with
 	 * the context header and its block header follows that.
@@ -733,6 +745,11 @@ AllocSetAlloc(MemoryContext context, Size size)
 #endif
 
 		blksize = chunk_size + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
+
+		/* Do not exceed maximum allowed memory allocation */
+		if (exceeds_max_total_bkend_mem(blksize))
+			return NULL;
+
 		block = (AllocBlock) malloc(blksize);
 		if (block == NULL)
 			return NULL;
@@ -933,6 +950,10 @@ AllocSetAlloc(MemoryContext context, Size size)
 		while (blksize < required_size)
 			blksize <<= 1;
 
+		/* Do not exceed maximum allowed memory allocation */
+		if (exceeds_max_total_bkend_mem(blksize))
+			return NULL;
+
 		/* Try to allocate it */
 		block = (AllocBlock) malloc(blksize);
 
@@ -1171,6 +1192,18 @@ AllocSetRealloc(void *pointer, Size size)
 		blksize = chksize + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
 		oldblksize = block->endptr - ((char *) block);
 
+		/*
+		 * Do not exceed maximum allowed memory allocation. NOTE: checking for
+		 * the full size here rather than just the amount of increased
+		 * allocation to prevent a potential underflow of *my_allocation
+		 * allowance in cases where blksize - oldblksize does not trigger a
+		 * refill but blksize is greater than *my_allocation_allowance.
+		 * Underflow would occur with the call below to
+		 * pgstat_report_allocated_bytes_increase()
+		 */
+		if (blksize > oldblksize && exceeds_max_total_bkend_mem(blksize))
+			return NULL;
+
 		block = (AllocBlock) realloc(block, blksize);
 		if (block == NULL)
 		{
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 502f877855..b4c9b40766 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -193,6 +193,16 @@ GenerationContextCreate(MemoryContext parent,
 	else
 		allocSize = Max(allocSize, initBlockSize);
 
+	if (exceeds_max_total_bkend_mem(allocSize))
+	{
+		MemoryContextStats(TopMemoryContext);
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("out of memory - exceeds max_total_backend_memory"),
+				 errdetail("Failed while creating memory context \"%s\".",
+						   name)));
+	}
+
 	/*
 	 * Allocate the initial block.  Unlike other generation.c blocks, it
 	 * starts with the context header and its block header follows that.
@@ -372,6 +382,9 @@ GenerationAlloc(MemoryContext context, Size size)
 	{
 		Size		blksize = required_size + Generation_BLOCKHDRSZ;
 
+		if (exceeds_max_total_bkend_mem(blksize))
+			return NULL;
+
 		block = (GenerationBlock *) malloc(blksize);
 		if (block == NULL)
 			return NULL;
@@ -475,6 +488,9 @@ GenerationAlloc(MemoryContext context, Size size)
 			if (blksize < required_size)
 				blksize = pg_nextpower2_size_t(required_size);
 
+			if (exceeds_max_total_bkend_mem(blksize))
+				return NULL;
+
 			block = (GenerationBlock *) malloc(blksize);
 
 			if (block == NULL)
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index 913787dba8..00a10f3c11 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -356,7 +356,16 @@ SlabContextCreate(MemoryContext parent,
 		elog(ERROR, "block size %zu for slab is too small for %zu-byte chunks",
 			 blockSize, chunkSize);
 
-
+	/* Do not exceed maximum allowed memory allocation */
+	if (exceeds_max_total_bkend_mem(Slab_CONTEXT_HDRSZ(chunksPerBlock)))
+	{
+		MemoryContextStats(TopMemoryContext);
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("out of memory - exceeds max_total_backend_memory"),
+				 errdetail("Failed while creating memory context \"%s\".",
+						   name)));
+	}
 
 	slab = (SlabContext *) malloc(Slab_CONTEXT_HDRSZ(chunksPerBlock));
 	if (slab == NULL)
@@ -559,6 +568,10 @@ SlabAlloc(MemoryContext context, Size size)
 		}
 		else
 		{
+			/* Do not exceed maximum allowed memory allocation */
+			if (exceeds_max_total_bkend_mem(slab->blockSize))
+				return NULL;
+
 			block = (SlabBlock *) malloc(slab->blockSize);
 
 			if (unlikely(block == NULL))
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b095740f8a..4bef1ac428 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5421,9 +5421,9 @@
   descr => 'statistics: global memory allocation information',
   proname => 'pg_stat_get_global_memory_allocation', proisstrict => 'f',
   provolatile => 's', proparallel => 'r', prorettype => 'record',
-  proargtypes => '', proallargtypes => '{oid,int8}',
-  proargmodes => '{o,o}',
-  proargnames => '{datid,global_dsm_allocated_bytes}',
+  proargtypes => '', proallargtypes => '{oid,int8,int8}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{datid,total_bkend_mem_bytes_available,global_dsm_allocated_bytes}',
   prosrc =>'pg_stat_get_global_memory_allocation' },
 { oid => '2022',
   descr => 'statistics: information about currently active backends',
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index e6be67de2a..f62ea132ff 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -406,6 +406,13 @@ typedef struct PROC_HDR
 	int			startupBufferPinWaitBufId;
 	/* Global dsm allocations */
 	pg_atomic_uint64 global_dsm_allocation;
+
+	/*
+	 * Max backend memory allocation tracker. Used/Initialized when
+	 * max_total_bkend_mem > 0 as max_total_bkend_mem (MB) converted to bytes.
+	 * Decreases/increases with free/malloc of backend memory.
+	 */
+	pg_atomic_uint64 total_bkend_mem_bytes;
 } PROC_HDR;
 
 extern PGDLLIMPORT PROC_HDR *ProcGlobal;
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 9b75fc5223..068b96dd09 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -15,6 +15,7 @@
 #include "libpq/pqcomm.h"
 #include "miscadmin.h"			/* for BackendType */
 #include "storage/backendid.h"
+#include "storage/proc.h"
 #include "utils/backend_progress.h"

@@ -305,6 +306,7 @@ typedef struct LocalPgBackendStatus
  */
 extern PGDLLIMPORT bool pgstat_track_activities;
 extern PGDLLIMPORT int pgstat_track_activity_query_size;
+extern PGDLLIMPORT int max_total_bkend_mem;

 /* ----------
@@ -317,6 +319,10 @@ extern PGDLLIMPORT uint64 *my_aset_allocated_bytes;
 extern PGDLLIMPORT uint64 *my_dsm_allocated_bytes;
 extern PGDLLIMPORT uint64 *my_generation_allocated_bytes;
 extern PGDLLIMPORT uint64 *my_slab_allocated_bytes;
+extern PGDLLIMPORT uint64 allocation_allowance;
+extern PGDLLIMPORT uint64 initial_allocation_allowance;
+extern PGDLLIMPORT uint64 allocation_return;
+extern PGDLLIMPORT uint64 allocation_return_threshold;

 /* ----------
@@ -364,6 +370,7 @@ extern int	pgstat_fetch_stat_numbackends(void);
 extern PgBackendStatus *pgstat_fetch_stat_beentry(BackendId beid);
 extern LocalPgBackendStatus *pgstat_fetch_stat_local_beentry(int beid);
 extern char *pgstat_clip_activity(const char *raw_activity);
+extern bool exceeds_max_total_bkend_mem(uint64 allocation_request);
 
 /* ----------
  * pgstat_report_allocated_bytes_decrease() -
@@ -379,7 +386,7 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 {
 	uint64		temp;
 
-	/* Avoid allocated_bytes unsigned integer overflow on decrease */
+	/* Sanity check: my allocated bytes should never drop below zero */
 	if (pg_sub_u64_overflow(*my_allocated_bytes, proc_allocated_bytes, &temp))
 	{
 		/* On overflow, set allocated bytes and allocator type bytes to zero */
@@ -388,13 +395,35 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 		*my_dsm_allocated_bytes = 0;
 		*my_generation_allocated_bytes = 0;
 		*my_slab_allocated_bytes = 0;
+
+		/* Add freed memory to allocation return counter. */
+		allocation_return += proc_allocated_bytes;
+
+		/*
+		 * Return freed memory to the global counter if return threshold is
+		 * met.
+		 */
+		if (max_total_bkend_mem && allocation_return >= allocation_return_threshold)
+		{
+			if (ProcGlobal)
+			{
+				volatile PROC_HDR *procglobal = ProcGlobal;
+
+				/* Add to global tracker */
+				pg_atomic_add_fetch_u64(&procglobal->total_bkend_mem_bytes,
+										allocation_return);
+
+				/* Restart the count */
+				allocation_return = 0;
+			}
+		}
 	}
 	else
 	{
-		/* decrease allocation */
-		*my_allocated_bytes -= proc_allocated_bytes;
+		/* Add freed memory to allocation return counter */
+		allocation_return += proc_allocated_bytes;
 
-		/* Decrease allocator type allocated bytes. */
+		/* Decrease allocator type allocated bytes */
 		switch (pg_allocator_type)
 		{
 			case PG_ALLOC_ASET:
@@ -416,6 +445,30 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 				*my_slab_allocated_bytes -= proc_allocated_bytes;
 				break;
 		}
+
+		/* decrease allocation */
+		*my_allocated_bytes = *my_aset_allocated_bytes +
+			*my_dsm_allocated_bytes + *my_generation_allocated_bytes +
+			*my_slab_allocated_bytes;
+
+		/*
+		 * Return freed memory to the global counter if return threshold is
+		 * met.
+		 */
+		if (max_total_bkend_mem && allocation_return >= allocation_return_threshold)
+		{
+			if (ProcGlobal)
+			{
+				volatile PROC_HDR *procglobal = ProcGlobal;
+
+				/* Add to global tracker */
+				pg_atomic_add_fetch_u64(&procglobal->total_bkend_mem_bytes,
+										allocation_return);
+
+				/* Restart the count */
+				allocation_return = 0;
+			}
+		}
 	}
 
 	return;
@@ -433,7 +486,13 @@ static inline void
 pgstat_report_allocated_bytes_increase(int64 proc_allocated_bytes,
 									   int pg_allocator_type)
 {
-	*my_allocated_bytes += proc_allocated_bytes;
+	uint64		temp;
+
+	/* Sanity check: my allocated bytes should never drop below zero */
+	if (pg_sub_u64_overflow(allocation_allowance, proc_allocated_bytes, &temp))
+		allocation_allowance = 0;
+	else
+		allocation_allowance -= proc_allocated_bytes;
 
 	/* Increase allocator type allocated bytes */
 	switch (pg_allocator_type)
@@ -458,6 +517,9 @@ pgstat_report_allocated_bytes_increase(int64 proc_allocated_bytes,
 			break;
 	}
 
+	*my_allocated_bytes = *my_aset_allocated_bytes + *my_dsm_allocated_bytes +
+		*my_generation_allocated_bytes + *my_slab_allocated_bytes;
+
 	return;
 }
 
@@ -477,6 +539,36 @@ pgstat_init_allocated_bytes(void)
 	*my_generation_allocated_bytes = 0;
 	*my_slab_allocated_bytes = 0;
 
+	/* If we're limiting backend memory */
+	if (max_total_bkend_mem)
+	{
+		volatile PROC_HDR *procglobal = ProcGlobal;
+		uint64		available_max_total_bkend_mem = 0;
+
+		allocation_return = 0;
+		allocation_allowance = 0;
+
+		/* Account for the initial allocation allowance */
+		while ((available_max_total_bkend_mem = pg_atomic_read_u64(&procglobal->total_bkend_mem_bytes)) >= initial_allocation_allowance)
+		{
+			/*
+			 * On success populate allocation_allowance. Failure here will
+			 * result in the backend's first invocation of
+			 * exceeds_max_total_bkend_mem allocating requested, default, or
+			 * available memory or result in an out of memory error.
+			 */
+			if (pg_atomic_compare_exchange_u64(&procglobal->total_bkend_mem_bytes,
+											   &available_max_total_bkend_mem,
+											   available_max_total_bkend_mem -
+											   initial_allocation_allowance))
+			{
+				allocation_allowance = initial_allocation_allowance;
+
+				break;
+			}
+		}
+	}
+
 	return;
 }
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ff76aa99a2..4f96cf4436 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1883,13 +1883,15 @@ pg_stat_global_memory_allocation| WITH sums AS (
  SELECT s.datid,
     current_setting('shared_memory_size'::text, true) AS shared_memory_size,
     (current_setting('shared_memory_size_in_huge_pages'::text, true))::integer AS shared_memory_size_in_huge_pages,
+    pg_size_bytes(current_setting('max_total_backend_memory'::text, true)) AS max_total_backend_memory_bytes,
+    s.total_bkend_mem_bytes_available,
     s.global_dsm_allocated_bytes,
     sums.total_aset_allocated_bytes,
     sums.total_dsm_allocated_bytes,
     sums.total_generation_allocated_bytes,
     sums.total_slab_allocated_bytes
    FROM sums,
-    (pg_stat_get_global_memory_allocation() s(datid, global_dsm_allocated_bytes)
+    (pg_stat_get_global_memory_allocation() s(datid, total_bkend_mem_bytes_available, global_dsm_allocated_bytes)
      LEFT JOIN pg_database d ON ((s.datid = d.oid)));
 pg_stat_gssapi| SELECT pid,
     gss_auth AS gss_authenticated,
-- 
2.25.1

---

From: [email-masked]
Date: Mon, 05 Jun 2023 14:33:28 -0400

More followup to the above.
> SSD -> Model: KXG60ZNV1T02 NVMe KIOXIA 1024GB (nvme)

Hi

Ran through a few more tests on my system varying the
initial_allocation_allowance and allocation_allowance_refill_qty from the
current 1MB to 2, 4, 6, 8, 10 mb.  Also realized that in my last tests/email I
had posted percent difference rather than percent change. Turns out for the
numbers that were being compared they're essentially the same, but I'm
providing both for this set of tests.  Ten runs for each comparison. Compared
dev-max-memory set, dev-max-memory unset, master, and pg-stat-activity-backend-memory-allocated
against master at each allocation value;

Again, the test invokes
   psql -At -d postgres $connstr -P pager=off -c 'select * from generate_series(0, 10000000)'
100 times on each of the 2 instances and calculates the AVG time and SD
for the 100 runs.  It then uses the AVG from each instance to calculate
the percentage difference/change.

These tests contain one code change not yet pushed to pgsql-hackers. In
AllocSetReset() do not enter pgstat_report_allocated_bytes_decrease if no
memory has been freed.

Will format and post some pgbench test result in a separate email.

Percent difference:

───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: difference-dev-max-memory-set VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation        2MB allocation        4MB allocation          6MB allocation        8MB allocation        10MB allocation
   2   │ 4.2263%   difference  3.03961%  difference  0.0585808%  difference  2.92451%  difference  3.34694%  difference  2.67771%  difference
   3   │ 3.55709%  difference  3.92339%  difference  2.29144%    difference  3.2156%   difference  2.06153%  difference  2.86217%  difference
   4   │ 2.04389%  difference  2.91866%  difference  3.73463%    difference  2.86161%  difference  3.60992%  difference  3.07293%  difference
   5   │ 3.1306%   difference  3.64773%  difference  2.38063%    difference  1.84845%  difference  4.87375%  difference  4.16953%  difference
   6   │ 3.12556%  difference  3.34537%  difference  2.99052%    difference  2.60538%  difference  2.14825%  difference  1.95454%  difference
   7   │ 2.20615%  difference  2.12861%  difference  2.85282%    difference  2.43336%  difference  2.31389%  difference  3.21563%  difference
   8   │ 1.9954%   difference  3.61371%  difference  3.35543%    difference  3.49821%  difference  3.41526%  difference  8.25753%  difference
   9   │ 2.46845%  difference  2.57784%  difference  3.13067%    difference  3.67681%  difference  2.89139%  difference  3.6067%   difference
  10   │ 3.60092%  difference  2.16164%  difference  3.9976%     difference  2.6144%   difference  4.27892%  difference  2.68998%  difference
  11   │ 2.55454%  difference  2.39073%  difference  3.09631%    difference  3.24292%  difference  1.9107%   difference  1.76182%  difference
  12   │
  13   │ 28.9089/10            29.74729/10           27.888631/10            28.92125/10           30.85055/10           34.26854/10
  14   │ 2.89089               2.974729              2.7888631               2.892125              3.085055              3.426854
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: difference-dev-max-memory-unset VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation        2MB allocation        4MB allocation          6MB allocation        8MB allocation        10MB allocation
   2   │ 3.96616%  difference  3.05528%  difference  0.563267%   difference  1.12075%  difference  3.52398%  difference  3.25641%   difference
   3   │ 3.11387%  difference  3.12499%  difference  1.1133%     difference  4.86997%  difference  2.11481%  difference  1.11668%   difference
   4   │ 3.14506%  difference  2.06193%  difference  3.36034%    difference  2.80644%  difference  2.37822%  difference  3.07669%   difference
   5   │ 2.81052%  difference  3.18499%  difference  2.70705%    difference  2.27847%  difference  2.78506%  difference  3.02919%   difference
   6   │ 2.9765%   difference  3.44165%  difference  2.62039%    difference  4.61596%  difference  2.27937%  difference  3.89676%   difference
   7   │ 3.201%    difference  1.35838%  difference  2.40578%    difference  3.95695%  difference  2.25983%  difference  4.17585%   difference
   8   │ 5.35191%  difference  3.96434%  difference  4.32891%    difference  3.62715%  difference  2.17503%  difference  0.620856%  difference
   9   │ 3.44241%  difference  2.9754%   difference  3.03765%    difference  1.48104%  difference  1.53958%  difference  3.14598%   difference
  10   │ 10.1155%  difference  4.21062%  difference  1.64416%    difference  1.51458%  difference  2.92131%  difference  2.95603%   difference
  11   │ 3.11011%  difference  4.31318%  difference  2.01991%    difference  4.71192%  difference  2.37039%  difference  4.25241%   difference
  12   │
  13   │ 41.23304/10           31.69076/10           23.800757/10            30.98323/10           24.34758/10           29.526856/10
  14   │ 4.123304              3.169076              2.3800757               3.098323              2.434758              2.9526856
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: difference-master VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation          2MB allocation          4MB allocation           6MB allocation         8MB allocation          10MB allocation
   2   │ 0.0734782%  difference  0.0955457%  difference  0.0521627%   difference  2.32643%   difference  0.286493%   difference  1.26977%    difference
   3   │ 0.547862%   difference  1.19087%    difference  0.276915%    difference  0.334332%  difference  0.260545%   difference  0.108956%   difference
   4   │ 0.0714666%  difference  0.931605%   difference  0.753996%    difference  0.457174%  difference  0.215904%   difference  1.43979%    difference
   5   │ 0.269737%   difference  0.848613%   difference  0.222909%    difference  0.315927%  difference  0.290408%   difference  0.248591%   difference
   6   │ 1.04231%    difference  0.367444%   difference  0.699571%    difference  0.29266%   difference  0.844548%   difference  0.273776%   difference
   7   │ 0.0584984%  difference  0.15094%    difference  0.0721539%   difference  0.594991%  difference  1.80223%    difference  0.500557%   difference
   8   │ 0.355129%   difference  1.19517%    difference  0.201835%    difference  1.2351%    difference  0.266004%   difference  0.80893%    difference
   9   │ 0.0811794%  difference  1.16184%    difference  1.01913%     difference  0.149087%  difference  0.402931%   difference  0.125788%   difference
  10   │ 0.950973%   difference  0.154471%   difference  0.42623%     difference  0.874816%  difference  0.157934%   difference  0.225433%   difference
  11   │ 0.501783%   difference  0.308357%   difference  0.279147%    difference  0.122458%  difference  0.538141%   difference  0.865846%   difference
  12   │
  13   │ 3.952417/10             6.404856/10             4.00405/10               6.702975/10            5.065138/10             5.867437/10
  14   │ 0.3952417               0.6404856               0.400405                 0.6702975              0.5065138               0.5867437
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: difference-pg-stat-activity-backend-memory-allocated VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation          2MB allocation          4MB allocation          6MB allocation         8MB allocation          10MB allocation
   2   │ 2.04788%    difference  0.50705%    difference  0.504772%   difference  0.136316%  difference  0.590087%   difference  1.33931%    difference
   3   │ 1.21173%    difference  0.3309%     difference  0.482685%   difference  1.67956%   difference  0.175478%   difference  0.969286%   difference
   4   │ 0.0680972%  difference  0.295211%   difference  0.867547%   difference  1.12959%   difference  0.193756%   difference  0.714178%   difference
   5   │ 0.91525%    difference  1.42408%    difference  1.49059%    difference  0.641652%  difference  1.34265%    difference  0.378394%   difference
   6   │ 2.46448%    difference  2.67081%    difference  0.63824%    difference  0.650301%  difference  0.481858%   difference  1.65711%    difference
   7   │ 1.31021%    difference  0.0548831%  difference  1.23217%    difference  2.11691%   difference  0.31629%    difference  3.85858%    difference
   8   │ 1.61458%    difference  0.46042%    difference  0.724742%   difference  0.172952%  difference  1.33157%    difference  0.556898%   difference
   9   │ 1.65063%    difference  0.59815%    difference  1.42473%    difference  0.725576%  difference  0.229639%   difference  0.875489%   difference
  10   │ 1.78567%    difference  1.45652%    difference  0.6317%     difference  1.99146%   difference  0.999521%   difference  1.85291%    difference
  11   │ 0.391318%   difference  1.13216%    difference  0.138291%   difference  0.531084%  difference  0.680197%   difference  1.63162%    difference
  12   │
  13   │ 13.459845/10            8.930184/10             8.135467/10             9.775401/10            6.341046/10             13.83377/10
  14   │ 1.3459845               0.8930184               0.8135467               0.9775401              0.6341046               1.3833775
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Percent change:

───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: change-dev-max-memory-set VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation        2MB allocation        4MB allocation          6MB allocation        8MB allocation        10MB allocation
   2   │ 4.13884%  change      2.99411%  change      0.0585636%  change      2.88237%  change      3.29185%  change      2.64233%  change
   3   │ 3.49493%  change      3.84791%  change      2.26549%    change      3.16472%  change      2.0405%   change      2.82179%  change
   4   │ 2.02322%  change      2.87668%  change      3.66617%    change      2.82124%  change      3.54592%  change      3.02643%  change
   5   │ 3.08235%  change      3.5824%   change      2.35263%    change      1.83153%  change      4.75781%  change      4.08438%  change
   6   │ 3.07746%  change      3.29033%  change      2.94646%    change      2.57188%  change      2.12542%  change      1.93562%  change
   7   │ 2.18208%  change      2.10619%  change      2.8127%     change      2.40411%  change      2.28743%  change      3.16474%  change
   8   │ 1.97569%  change      3.54957%  change      3.30007%    change      3.43808%  change      3.35792%  change      7.93011%  change
   9   │ 2.43836%  change      2.54504%  change      3.08242%    change      3.61044%  change      2.85019%  change      3.54281%  change
  10   │ 3.53724%  change      2.13852%  change      3.91926%    change      2.58067%  change      4.18929%  change      2.65428%  change
  11   │ 2.52233%  change      2.36249%  change      3.0491%     change      3.19118%  change      1.89262%  change      1.74644%  change
  12   │
  13   │ 28.4725/10            29.29324/10           27.452864/10            28.49622/10           30.33895/10           33.54893/10
  14   │ 2.84725               2.929324              2.7452864               2.849622              3.033895              3.354893
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: change-dev-max-memory-unset VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation        2MB allocation        4MB allocation          6MB allocation        8MB allocation        10MB allocation
   2   │ 3.88903%  change      3.00931%  change      0.564858%   change      1.11451%  change      3.46296%  change      3.20424%   change
   3   │ 3.06613%  change      3.07691%  change      1.10714%    change      4.75421%  change      2.09268%  change      1.11048%   change
   4   │ 3.09637%  change      2.04089%  change      3.30482%    change      2.7676%   change      2.35028%  change      3.03008%   change
   5   │ 2.77157%  change      3.13506%  change      2.6709%     change      2.2528%   change      2.74681%  change      2.984%     change
   6   │ 2.93285%  change      3.38343%  change      2.5865%     change      4.51183%  change      2.25368%  change      3.82229%   change
   7   │ 3.15057%  change      1.34921%  change      2.37719%    change      3.88018%  change      2.23458%  change      4.09044%   change
   8   │ 5.21243%  change      3.88728%  change      4.23719%    change      3.56254%  change      2.15163%  change      0.62279%   change
   9   │ 3.38416%  change      2.93178%  change      2.99221%    change      1.47015%  change      1.52782%  change      3.09726%   change
  10   │ 10.6543%  change      4.1238%   change      1.63075%    change      1.5032%   change      2.87926%  change      2.91298%   change
  11   │ 3.06248%  change      4.22213%  change      1.99972%    change      4.60347%  change      2.34263%  change      4.16388%   change
  12   │
  13   │ 41.21989/10           31.1598/10            23.471278/10            30.42049/10           24.04233/10           29.03844/10
  14   │ 4.121989              3.11598               2.3471278               3.042049              2.404233              2.903844
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: change-master VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation          2MB allocation          4MB allocation           6MB allocation         8MB allocation          10MB allocation
   2   │ 0.0734512%  change      0.0955%     change      0.0521763%   change      2.35381%   change      0.286904%   change      1.27789%    change
   3   │ 0.549367%   change      1.18382%    change      0.276532%    change      0.333774%  change      0.260206%   change      0.108897%   change
   4   │ 0.0714411%  change      0.927286%   change      0.751164%    change      0.456132%  change      0.216137%   change      1.4295%     change
   5   │ 0.269374%   change      0.845028%   change      0.222661%    change      0.315429%  change      0.29083%    change      0.2489%     change
   6   │ 1.0369%     change      0.368121%   change      0.702026%    change      0.292232%  change      0.840997%   change      0.273402%   change
   7   │ 0.0584813%  change      0.151054%   change      0.07218%     change      0.596766%  change      1.78613%    change      0.499307%   change
   8   │ 0.355761%   change      1.18807%    change      0.201631%    change      1.22752%   change      0.265651%   change      0.805671%   change
   9   │ 0.0812124%  change      1.16863%    change      1.02435%     change      0.149198%  change      0.402121%   change      0.125709%   change
  10   │ 0.955516%   change      0.154351%   change      0.425324%    change      0.871006%  change      0.158059%   change      0.225179%   change
  11   │ 0.500527%   change      0.307882%   change      0.278758%    change      0.122533%  change      0.539593%   change      0.862113%   change
  12   │
  13   │ 3.952031/10             6.389742/10             4.006802/10              6.7184/10              5.046628/10             5.856568/10
  14   │ 0.3952031               0.6389742               0.4006802                0.67184                0.5046628               0.5856568
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ Results: change-pg-stat-activity-backend-memory-allocated VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ 1MB allocation          2MB allocation          4MB allocation          6MB allocation         8MB allocation          10MB allocation
   2   │ 2.02713%    change      0.505768%   change      0.506049%   change      0.136223%  change      0.591833%   change      1.3304%     change
   3   │ 1.20444%    change      0.331448%   change      0.481523%   change      1.66557%   change      0.175325%   change      0.974006%   change
   4   │ 0.068074%   change      0.294776%   change      0.8638%     change      1.12325%   change      0.193568%   change      0.711637%   change
   5   │ 0.91108%    change      1.41401%    change      1.47956%    change      0.6396%    change      1.33369%    change      0.377679%   change
   6   │ 2.43448%    change      2.63562%    change      0.636209%   change      0.648194%  change      0.4807%     change      1.64349%    change
   7   │ 1.30168%    change      0.054868%   change      1.22463%    change      2.09474%   change      0.316791%   change      3.93449%    change
   8   │ 1.60165%    change      0.461483%   change      0.722126%   change      0.173102%  change      1.32277%    change      0.555352%   change
   9   │ 1.63712%    change      0.599944%   change      1.41466%    change      0.722953%  change      0.229375%   change      0.871673%   change
  10   │ 1.76986%    change      1.44599%    change      0.629711%   change      1.97183%   change      0.99455%    change      1.8359%     change
  11   │ 0.392085%   change      1.12579%    change      0.138195%   change      0.532498%  change      0.677892%   change      1.61841%    change
  12   │
  13   │ 13.347599/10            8.869697/10             8.096463/10             9.70796/10             6.316494/10             13.853037/10
  14   │ 1.3347599               0.8869697               0.8096463               0.970796               0.6316494               1.385303
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

---

From: Andrei Lepikhov <[email-masked]>
Date: Fri, 29 Sep 2023 09:52:47 +0700

> Pulled from patch 2 back to patch 1 a change that was also pertinent to patch 1.
+1 to the idea, have doubts on the implementation.

I have a question. I see the feature triggers ERROR on the exceeding of 
the memory limit. The superior PG_CATCH() section will handle the error. 
As I see, many such sections use memory allocations. What if some 
routine, like the CopyErrorData(), exceeds the limit, too? In this case, 
we could repeat the error until the top PG_CATCH(). Is this correct 
behaviour? Maybe to check in the exceeds_max_total_bkend_mem() for 
recursion and allow error handlers to slightly exceed this hard limit?

Also, the patch needs to be rebased.

-- 
regards,
Andrey Lepikhov
Postgres Professional

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 3 Oct 2023 18:33:37 +0700

> recursion and allow error handlers to slightly exceed this hard limit?
By the patch in attachment I try to show which sort of problems I'm 
worrying about. In some PП_CATCH() sections we do CopyErrorData 
(allocate some memory) before aborting the transaction. So, the 
allocation error can move us out of this section before aborting. We 
await for soft ERROR message but will face more hard consequences.

-- 
regards,
Andrey Lepikhov
Postgres Professional
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 33975687b3..3f992b8d92 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -291,10 +291,7 @@ _SPI_commit(bool chain)
 	{
 		ErrorData  *edata;
 
-		/* Save error info in caller's context */
 		MemoryContextSwitchTo(oldcontext);
-		edata = CopyErrorData();
-		FlushErrorState();
 
 		/*
 		 * Abort the failed transaction.  If this fails too, we'll just
@@ -302,6 +299,10 @@ _SPI_commit(bool chain)
 		 */
 		AbortCurrentTransaction();
 
+		/* Save error info in caller's context */
+		edata = CopyErrorData();
+		FlushErrorState();
+
 		/* ... and start a new one */
 		StartTransactionCommand();
 		if (chain)
@@ -383,10 +384,7 @@ _SPI_rollback(bool chain)
 	{
 		ErrorData  *edata;
 
-		/* Save error info in caller's context */
 		MemoryContextSwitchTo(oldcontext);
-		edata = CopyErrorData();
-		FlushErrorState();
 
 		/*
 		 * Try again to abort the failed transaction.  If this fails too,
@@ -395,6 +393,10 @@ _SPI_rollback(bool chain)
 		 */
 		AbortCurrentTransaction();
 
+		/* Save error info in caller's context */
+		edata = CopyErrorData();
+		FlushErrorState();
+
 		/* ... and start a new one */
 		StartTransactionCommand();
 		if (chain)
diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c
index 12edc5772a..f9cf599026 100644
--- a/src/backend/replication/logical/reorderbuffer.c
+++ b/src/backend/replication/logical/reorderbuffer.c
@@ -2565,7 +2565,7 @@ ReorderBufferProcessTXN(ReorderBuffer *rb, ReorderBufferTXN *txn,
 	PG_CATCH();
 	{
 		MemoryContext ecxt = MemoryContextSwitchTo(ccxt);
-		ErrorData  *errdata = CopyErrorData();
+		ErrorData  *errdata;
 
 		/* TODO: Encapsulate cleanup from the PG_TRY and PG_CATCH blocks */
 		if (iterstate)
@@ -2579,6 +2579,8 @@ ReorderBufferProcessTXN(ReorderBuffer *rb, ReorderBufferTXN *txn,
 		 */
 		AbortCurrentTransaction();
 
+		errdata = CopyErrorData();
+
 		/* make sure there's no cache pollution */
 		ReorderBufferExecuteInvalidations(txn->ninvalidations,
 										  txn->invalidations);

---

From: Stephen Frost <[email-masked]>
Date: Wed, 18 Oct 2023 15:00:42 -0400

Greetings,

> By the patch in attachment I try to show which sort of problems I'm worrying
> about. In some PП_CATCH() sections we do CopyErrorData (allocate some
> memory) before aborting the transaction. So, the allocation error can move
> us out of this section before aborting. We await for soft ERROR message but
> will face more hard consequences.

While it's an interesting idea to consider making exceptions to the
limit, and perhaps we'll do that (or have some kind of 'reserve' for
such cases), this isn't really any different than today, is it?  We
might have a malloc() failure in the main path, end up in PG_CATCH() and
then try to do a CopyErrorData() and have another malloc() failure.

If we can rearrange the code to make this less likely to happen, by
doing a bit more work to free() resources used in the main path before
trying to do new allocations, then, sure, let's go ahead and do that,
but that's independent from this effort.

Thanks!

Stephen

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 19 Oct 2023 09:57:08 +0700

> but that's independent from this effort.

I agree that rearranging efforts can be made independently. The code in 
the letter above was shown just as a demo of the case I'm worried about.
IMO, the thing that should be implemented here is a recursion level for 
the memory limit. If processing the error, we fall into recursion with 
this limit - we should ignore it.
I imagine custom extensions that use PG_CATCH() and allocate some data 
there. At least we can raise the level of error to FATAL.

-- 
regards,
Andrey Lepikhov
Postgres Professional

---

From: Stephen Frost <[email-masked]>
Date: Thu, 19 Oct 2023 18:06:10 -0400

Greetings,

* Andrei Lepikhov ([email-masked]) wrote:
> I agree that rearranging efforts can be made independently. The code in the
> letter above was shown just as a demo of the case I'm worried about.
> IMO, the thing that should be implemented here is a recursion level for the
> memory limit. If processing the error, we fall into recursion with this
> limit - we should ignore it.

Ignoring such would defeat much of the point of this effort- which is to
get to a position where we can say with some confidence that we're not
going to go over some limit that the user has set and therefore not
allow ourselves to end up getting OOM killed.  These are all the same
issues that already exist today on systems which don't allow overcommit
too, there isn't anything new here in regards to these risks, so I'm not
really keen to complicate this to deal with issues that are already
there.

Perhaps once we've got the basics in place then we could consider
reserving some space for handling such cases..  but I don't think it'll
actually be very clean and what if we have an allocation that goes
beyond what that reserved space is anyway?  Then we're in the same spot
again where we have the choice of either failing the allocation in a
less elegant way than we might like to handle that error, or risk
getting outright kill'd by the kernel.  Of those choices, sure seems
like failing the allocation is the better way to go.

Thanks,

Stephen

---

From: Andres Freund <[email-masked]>
Date: Thu, 19 Oct 2023 15:22:51 -0700

Hi,

On 2023-10-19 18:06:10 -0400, Stephen Frost wrote:
> allow ourselves to end up getting OOM killed.

I think that is a good medium to long term goal. I do however think that we'd
be better off merging the visibility of memory allocations soon-ish and
implement the limiting later. There's a lot of hairy details to get right for
the latter, and even just having visibility will be a huge improvement.

I think even patch 1 is doing too much at once. I doubt the DSM stuff is
quite right.

I'm unconvinced it's a good idea to split the different types of memory
contexts out. That just exposes too much implementation detail stuff without a
good reason.

I think the overhead even just the tracking implies right now is likely too
high and needs to be optimized. It should be a single math operation, not
tracking things in multiple fields. I don't think pg_sub_u64_overflow() should
be in the path either, that suddenly adds conditional branches.  You really
ought to look at the difference in assembly for the hot functions.

Greetings,

Andres Freund

---

From: Stephen Frost <[email-masked]>
Date: Thu, 19 Oct 2023 18:49:21 -0400

Greetings,

> the latter, and even just having visibility will be a huge improvement.

I agree that having the visibility will be a great improvement and
perhaps could go in separately, but I don't know that I agree that the
limits are going to be that much of an issue.  In any case, there's been
work ongoing on this and that'll be posted soon.  I was just trying to
address the general comment raised in this sub-thread here.

> I think even patch 1 is doing too much at once. I doubt the DSM stuff is

Getting DSM right has certainly been tricky, along with other things,
but we've been working towards, and continue to work towards, getting
everything to line up nicely between memory context allocations of
various types and the amounts which are being seen as malloc'd/free'd.
There's been parts of this also reworked to allow us to see per-backend
reservations as well as total reserved and to get those numbers able to
be matched up inside of a given transaction using the statistics system.

> contexts out. That just exposes too much implementation detail stuff without a

DSM needs to be independent anyway ... as for the others, perhaps we
could combine them, though that's pretty easily done later and for now
it's been useful to see them split out as we've been working on the
patch.

> ought to look at the difference in assembly for the hot functions.

This has been improved in the most recent work and we'll have that
posted soon, probably best to hold off from larger review of this right
now- as mentioned, I was just trying to address the specific question in
this sub-thread since a new patch is coming soon.

Thanks,

Stephen

---

From: Andrei Lepikhov <[email-masked]>
Date: Fri, 20 Oct 2023 09:36:07 +0700

> like failing the allocation is the better way to go.

I've got your point.
The only issue I worry about is the uncertainty and clutter that can be 
created by this feature. In the worst case, when we have a complex error 
stack (including the extension's CATCH sections, exceptions in stored 
procedures, etc.), the backend will throw the memory limit error 
repeatedly. Of course, one failed backend looks better than a 
surprisingly killed postmaster, but the mix of different error reports 
and details looks terrible and challenging to debug in the case of 
trouble. So, may we throw a FATAL error if we reach this limit while 
handling an exception?

-- 
regards,
Andrey Lepikhov
Postgres Professional

---

From: Stephen Frost <[email-masked]>
Date: Fri, 20 Oct 2023 08:39:33 -0400

Greetings,

* Andrei Lepikhov ([email-masked]) wrote:
> procedures, etc.), the backend will throw the memory limit error repeatedly.

I'm not seeing what additional uncertainty or clutter there is- this is,
again, exactly the same as what happens today on a system with
overcommit disabled and I don't feel like we get a lot of complaints
about this today.

> Of course, one failed backend looks better than a surprisingly killed
> postmaster, but the mix of different error reports and details looks
> terrible and challenging to debug in the case of trouble. So, may we throw a
> FATAL error if we reach this limit while handling an exception?

I don't see why we'd do that when we can do better- we just fail
whatever the ongoing query or transaction is and allow further requests
on the same connection.  We already support exactly that and it works
really rather well and I don't see why we'd throw that away because
there's a different way to get an OOM error.

If you want to make the argument that we should throw FATAL on OOM when
handling an exception, that's something you could argue independently of
this effort already today, but I don't think you'll get agreement that
it's an improvement.

Thanks,

Stephen

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 24 Oct 2023 09:39:42 +0700

On 20/10/2023 19:39, Stephen Frost wrote:
Greetings,
> overcommit disabled and I don't feel like we get a lot of complaints

Maybe I missed something or see this feature from an alternate point of 
view (as an extension developer), but overcommit is more useful so far: 
it kills a process.
It means that after restart, the backend/background worker will have an 
initial internal state. With this limit enabled, we need to remember 
that each function call can cause an error, and we have to remember it 
using static PG_CATCH sections where we must rearrange local variables 
to the initial (?) state. So, it complicates development.
Of course, this limit is a good feature, but from my point of view, it 
would be better to kill a memory-consuming backend instead of throwing 
an error. At least for now, we don't have a technique to repeat query 
planning with chances to build a more effective plan.

-- 
regards,
Andrei Lepikhov
Postgres Professional

---

From: Andres Freund <[email-masked]>
Date: Mon, 23 Oct 2023 19:44:35 -0700

Hi,

On 2023-10-24 09:39:42 +0700, Andrei Lepikhov wrote:
> Maybe I missed something or see this feature from an alternate point of view
> (as an extension developer), but overcommit is more useful so far: it kills
> a process.

In case of postgres it doesn't just kill one postgres, it leads to *all*
connections being terminated.

> initial internal state. With this limit enabled, we need to remember that
> each function call can cause an error, and we have to remember it using
> static PG_CATCH sections where we must rearrange local variables to the
> initial (?) state. So, it complicates development.

You need to be aware of errors being thrown regardless this feature, as
out-of-memory errors can be encountered today already. There also are many
other kinds of errors that can be thrown.

Greetings,

Andres Freund

---

From: "Anton A. Melnikov" <[email-masked]>
Date: Tue, 26 Dec 2023 13:49:00 +0300

Hello!

Earlier in this thread, the pgbench results were published, where with a strong memory limit of 100MB
a significant, about 10%, decrease in TPS was observed [1].

Using dedicated server with 12GB RAM and methodology described in [3], i performed five series
of measurements for the patches from the [2].
The series were like this:
1) unpatched 16th version at the REL_16_BETA1 (e0b82fc8e83) as close to [2] in time.
2) patched REL_16_BETA1 at e0b82fc8e83 with undefined max_total_backend_memory GUC (with default value = 0).
3) patched REL_16_BETA1 with max_total_backend_memory = 16GB
4) the same with max_total_backend_memory = 8GB
5) and again with max_total_backend_memory = 200MB

Measurements with max_total_backend_memory = 100MB were not be carried out,
with limit 100MB the server gave an error on startup:
FATAL:  configured max_total_backend_memory 100MB is <= shared_memory_size 143MB
So i used 200MB to retain all other GUCs the same.

Pgbench gave the following results:
1) and 2) almost the same: ~6350 TPS. See orange and green
distributions on the attached graph.png respectively.
3) and 4) identical to each other (~6315 TPS) and a bit slower than 1) and 2) by ~0,6%.
See blue and yellow distributions respectively.
5) is slightly slower (~6285 TPS) than 3) and 4) by another 0,5%. (grey distribution)
The standard error in all series was ~0.2%. There is a raw data in the raw_data.txt.

With the best wishes,

-- 
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

[1] https://www.postgresql.org/message-id/3178e9a1b7acbcf023fafed68ca48d76afc07907.camel%40crunchydata.com
[2] https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
[3] https://www.postgresql.org/message-id/1d3a7d8f-cb7c-4468-a578-d8a1194ea2de%40postgrespro.ru

---

From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 18:28:43 +0100

> of measurements for the patches from the [2].

Can you share some info about the hardware? For example the CPU model,
number of cores, and so on. 12GB RAM is not quite huge, so presumably it
was a small machine.

> 2) patched REL_16_BETA1 at e0b82fc8e83 with undefined
> max_total_backend_memory GUC (with default value = 0).

OK

> So i used 200MB to retain all other GUCs the same.

I'm not very familiar with the patch yet, but this seems a bit strange.
Why should shared_buffers be included this limit?

> The standard error in all series was ~0.2%. There is a raw data in the

I think 6350 is a pretty terrible number, especially for scale 8, which
is maybe 150MB of data. I think that's a pretty clear sign the system
was hitting some other bottleneck, which can easily mask regressions in
the memory allocation code. AFAICS the pgbench runs were regular r/w
benchmarks, so I'd bet it was hitting I/O, and possibly even subject to
some random effects at that level.

I think what would be interesting are runs with

   pgbench -M prepared -S -c $N -j $N

i.e. read-only tests (to not hit I/O), and $N being sufficiently large
to maybe also show some concurrency/locking bottlenecks, etc.

I may do some benchmarks if I happen to find a bit of time, but maybe
you could collect such numbers too?

The other benchmark that might be interesting is more OLAP, with low
concurrency but backends allocating a lot of memory.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 22:52:06 +0100

Hi,

I wanted to take a look at the patch, and I noticed it's broken since
3d51cb5197 renamed a couple pgstat functions in August. I plan to maybe
do some benchmarks etc. preferably on current master, so here's a
version fixing that minor bitrot.

As for the patch, I only skimmed through the thread so far, to get some
idea of what the approach and goals are, etc. I didn't look at the code
yet, so can't comment on that.

However, at pgconf.eu a couple week ago I had quite a few discussions
about such "backend memory limit" could/should work in principle, and
I've been thinking about ways to implement this. So let me share some
thoughts about how this patch aligns with that ...

(FWIW it's not my intent to hijack or derail this patch in any way, but
there's a couple things I think we should do differently.)

I'm 100% on board with having a memory limit "above" work_mem. It's
really annoying that we have no way to restrict the amount of memory a
backend can allocate for complex queries, etc.

But I find it a bit strange that we aim to introduce a "global" memory
limit for all backends combined first. I'm not against having that too,
but it's not the feature I usually wish to have. I need some protection
against runaway backends, that happen to allocate a lot memory.

Similarly, I'd like to be able to have different limits depending on
what the backend does - a backend doing OLAP may naturally need more
memory, while a backend doing OLTP may have a much tighter limit.

But with a single global limit none of this is possible. It may help
reducing the risk of unexpected OOM issues (not 100%, but useful), but
it can't limit the impact to the one backend - if memory starts runnning
out, it will affect all other backends a bit randomly (depending on the
order in which the backends happen to allocate memory). And it does not
consider what workloads the backends execute.

Let me propose a slightly different architecture that I imagined while
thinking about this. It's not radically differrent from what the patch
does, but it focuses on the local accounting first. I believe it's
possible to extend this to enforce the global limit too.

FWIW I haven't tried implementing this - I don't want to "hijack" this
thread and do my own thing. I can take a stab at a PoC if needed.

Firstly, I'm not quite happy with how all the memory contexts have to
do their own version of the accounting and memory checking. I think we
should move that into a new abstraction which I call "memory pool".
It's very close to "memory context" but it only deals with allocating
blocks, not the chunks requested by palloc() etc. So when someone does
palloc(), that may be AllocSetAlloc(). And instead of doing malloc()
that would do MemoryPoolAlloc(blksize), and then that would do all the
accounting and checks, and then do malloc().

This may sound like an unnecessary indirection, but the idea is that a
single memory pool would back many memory contexts (perhaps all for
a given backend). In principle we might even establish separate memory
pools for different parts of the memory context hierarchy, but I'm not
sure we need that.

I can imagine the pool could also cache blocks for cases when we create
and destroy contexts very often, but glibc should already does that for
us, I believe.

For me, the accounting and memory context is the primary goal. I wonder
if we considered this context/pool split while working on the accounting
for hash aggregate, but I think we were too attached to doing all of it
in the memory context hierarchy.

Of course, this memory pool is per backend, and so would be the memory
accounting and limit enforced by it. But I can imagine extending to do
a global limit similar to what the current patch does - using a counter
in shared memory, or something. I haven't reviewed what's the overhead
or how it handles cases when a backend terminates in some unexpected
way. But whatever the current patch does, memory pool could do too.

Secondly, I think there's an annoying issue with the accounting at the
block level - it makes it problematic to use low limit values. We double
the block size, so we may quickly end up with a block size a couple MBs,
which means the accounting granularity gets very coarse.

I think it'd be useful to introduce a "backpressure" between the memory
pool and the memory context, depending on how close we are to the limit.
For example if the limit is 128MB and the backend allocated 16MB so far,
we're pretty far away from the limit. So if the backend requests 8MB
block, that's fine and the memory pool should malloc() that. But if we
already allocated 100MB, maybe we should be careful and not allow 8MB
blocks - the memory pool should be allowed to override this and return
just 1MB block. Sure, this would have to be optional, and not all places
can accept a smaller block than requested (when the chunk would not fit
into the smaller block). It would require a suitable memory pool API
and more work in the memory contexts, but it seems pretty useful.
Certainly not something for v1.

regards

From ad74704ef5a62f9d2b8d98f40d6fbc5b52bc742b Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 17:54:40 +0100
Subject: [PATCH v20231226 1/3] Add tracking of backend memory allocated

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4f8058d8b1b..99f5acf07f4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4563,6 +4563,252 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
  </sect2>

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 11d18ed9dd6..fd7fcaf59e3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1369,3 +1369,37 @@ CREATE VIEW pg_stat_subscription_stats AS
 
 CREATE VIEW pg_wait_events AS
     SELECT * FROM pg_get_wait_events();
+
index 628f3ecd3fb..6725780fc77 100644
@@ -803,6 +803,15 @@ dsm_detach_all(void)
@@ -883,7 +892,7 @@ dsm_detach(dsm_segment *seg)
index 35fa910d6f2..a9e0987747b 100644

 
+		/*
 
+	/*
 
index b6451d9d083..81304a569c0 100644
 
 	/*
index 6e734c6caff..838a7337933 100644
 
+/*

 
@@ -1214,3 +1261,70 @@ pgstat_clip_activity(const char *raw_activity)
 
 	return activity;
 }
+
+/*
index 0cea320c00e..b372ee691ba 100644
@@ -2016,3 +2016,87 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 
index 5c9b6f991e0..2b082c68df6 100644
@@ -170,6 +170,9 @@ InitPostmasterChild(void)
 
 /*
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index c3affaf5a8a..7af0d141da2 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -47,6 +47,7 @@
 #include "postgres.h"
 
@@ -517,6 +518,7 @@ AllocSetContextCreateInternal(MemoryContext parent,
 						name);

@@ -539,6 +541,7 @@ AllocSetReset(MemoryContext context)

@@ -581,6 +584,7 @@ AllocSetReset(MemoryContext context)
 		{
 			/* Normal case, release the block */
 			context->mem_allocated -= block->endptr - ((char *) block);
+			deallocation += block->endptr - ((char *) block);
 
 #ifdef CLOBBER_FREED_MEMORY
 			wipe_mem(block, block->freeptr - ((char *) block));
@@ -591,6 +595,7 @@ AllocSetReset(MemoryContext context)
 	}

@@ -609,6 +614,7 @@ AllocSetDelete(MemoryContext context)

@@ -647,11 +653,13 @@ AllocSetDelete(MemoryContext context)

 
 		/* Now add the just-deleted context to the freelist. */
@@ -668,7 +676,10 @@ AllocSetDelete(MemoryContext context)
 		AllocBlock	next = block->next;
 
 		if (!IsKeeperBlock(set, block))
+		{
 			context->mem_allocated -= block->endptr - ((char *) block);
+			deallocation += block->endptr - ((char *) block);
+		}
 
 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, block->freeptr - ((char *) block));
@@ -681,6 +692,7 @@ AllocSetDelete(MemoryContext context)
 	}

 	/* Finally, free the context header, including the keeper block */
 	free(set);
@@ -730,6 +742,7 @@ AllocSetAlloc(MemoryContext context, Size size)
 			return NULL;

 		block->aset = set;
 		block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -943,6 +956,7 @@ AllocSetAlloc(MemoryContext context, Size size)
 			return NULL;

 		block->aset = set;
 		block->freeptr = ((char *) block) + ALLOC_BLOCKHDRSZ;
@@ -1040,6 +1054,7 @@ AllocSetFree(void *pointer)
 			block->next->prev = block->prev;

 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, block->freeptr - ((char *) block));
@@ -1170,7 +1185,9 @@ AllocSetRealloc(void *pointer, Size size)

 
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 92401ccf738..0ed54571497 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -37,6 +37,7 @@
 
 #include "lib/ilist.h"
@@ -263,6 +264,7 @@ GenerationContextCreate(MemoryContext parent,
 						name);

@@ -279,6 +281,7 @@ GenerationReset(MemoryContext context)

@@ -301,9 +304,14 @@ GenerationReset(MemoryContext context)
 		if (IsKeeperBlock(set, block))
 			GenerationBlockMarkEmpty(block);
 		else
+		{
+			deallocation += block->blksize;
 			GenerationBlockFree(set, block);
+		}
 	}
 
+	pgstat_report_allocated_bytes_decrease(deallocation, PG_ALLOC_GENERATION);
+
 	/* set it so new allocations to make use of the keeper block */
 	set->block = KeeperBlock(set);
 
@@ -324,6 +332,9 @@ GenerationDelete(MemoryContext context)
 {
 	/* Reset to release all releasable GenerationBlocks */
 	GenerationReset(context);
+
+	pgstat_report_allocated_bytes_decrease(context->mem_allocated, PG_ALLOC_GENERATION);
+
 	/* And free the context header and keeper block */
 	free(context);
 }
@@ -370,6 +381,7 @@ GenerationAlloc(MemoryContext context, Size size)
 			return NULL;

 		/* block with a single (used) chunk */
 		block->context = set;
@@ -473,6 +485,7 @@ GenerationAlloc(MemoryContext context, Size size)
 				return NULL;

 			/* initialize the new block */
 			GenerationBlockInit(set, block, blksize);
@@ -725,6 +738,8 @@ GenerationFree(void *pointer)
 	dlist_delete(&block->node);

diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index 40c1d401c4c..c99ff532af2 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -69,6 +69,7 @@
 #include "postgres.h"
 
 #include "lib/ilist.h"
@@ -417,6 +418,13 @@ SlabContextCreate(MemoryContext parent,
 						parent,
 						name);
 
+	/*
 
@@ -433,6 +441,7 @@ SlabReset(MemoryContext context)
 	SlabContext *slab = (SlabContext *) context;
 	dlist_mutable_iter miter;
 	int			i;
+	uint64		deallocation = 0;

@@ -453,6 +462,7 @@ SlabReset(MemoryContext context)
 #endif
 		free(block);
 		context->mem_allocated -= slab->blockSize;
+		deallocation += slab->blockSize;
 	}
 
 	/* walk over blocklist and free the blocks */
@@ -469,9 +479,11 @@ SlabReset(MemoryContext context)
 #endif
 			free(block);
 			context->mem_allocated -= slab->blockSize;
+			deallocation += slab->blockSize;
 		}
 	}

 	Assert(context->mem_allocated == 0);
@@ -486,6 +498,14 @@ SlabDelete(MemoryContext context)
 {
 	/* Reset to release all the SlabBlocks */
 	SlabReset(context);
+
+	/*
+	 * Until context header allocation is included in context->mem_allocated,
+	 * cast to slab and decrement the header allocation
+	 */
+	pgstat_report_allocated_bytes_decrease(Slab_CONTEXT_HDRSZ(((SlabContext *) context)->chunksPerBlock),
+										   PG_ALLOC_SLAB);
+
 	/* And free the context header */
 	free(context);
 }
@@ -550,6 +570,7 @@ SlabAlloc(MemoryContext context, Size size)
 
 			block->slab = slab;
 
@@ -744,6 +765,7 @@ SlabFree(void *pointer)
 
 		/*
index b8b26c263db..fe0549c43d9 100644
@@ -5435,6 +5435,23 @@
index e87fd25d643..26b17d66477 100644
 
index 75fc18c4327..c2c8ba7214d 100644
 
+#include "common/int.h"
 	STATE_DISABLED,
 } BackendState;

 /* ----------

 /* ----------
 
 /* ----------
  * Support functions for the SQL-callable functions to
@@ -338,5 +366,119 @@ extern LocalPgBackendStatus *pgstat_get_local_beentry_by_backend_id(BackendId be
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_index(int idx);
 extern char *pgstat_clip_activity(const char *raw_activity);
 
+/* ----------
 
index 05070393b99..7d412b26801 100644
@@ -1872,6 +1872,24 @@ pg_stat_database_conflicts| SELECT oid AS datid,
@@ -1898,6 +1916,15 @@ pg_stat_io| SELECT backend_type,
index 346e10a3d2b..92326a3e697 100644
@@ -1646,4 +1646,40 @@ SELECT COUNT(*) FROM brin_hot_3 WHERE a = 2;
 
 DROP TABLE brin_hot_3;
 SET enable_seqscan = on;
index e3b4ca96e89..01134187bb8 100644
@@ -849,4 +849,24 @@ DROP TABLE brin_hot_3;
 
 SET enable_seqscan = on;
 
2.41.0

From 468288c2dd154ebcb90cb3a93bee36622169be18 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 18:05:02 +0100
Subject: [PATCH v20231226 2/3] fixup: pgstat_get_local_beentry_by_index

---
 src/backend/utils/adt/pgstatfuncs.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b372ee691ba..9b52cc5091f 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2041,7 +2041,7 @@ pg_stat_get_memory_allocation(PG_FUNCTION_ARGS)
 		PgBackendStatus *beentry;
 
 		/* Get the next one in the list */
-		local_beentry = pgstat_fetch_stat_local_beentry(curr_backend);
+		local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
 		beentry = &local_beentry->backendStatus;
 
 		/* If looking for specific PID, ignore all the others */
-- 
2.41.0

From d45b026dfcaa5d891f83af43645fd92519d9841a Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 17:55:23 +0100
Subject: [PATCH v20231226 3/3] Add the ability to limit the amount of memory
 that can be  allocated to backends.

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index b5624ca8847..6b0e7b753d8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2167,6 +2167,36 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>

index 99f5acf07f4..01e5d5ef85b 100644
@@ -4623,10 +4623,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
@@ -4743,6 +4740,39 @@ description | Waiting for a newly initialized WAL file to reach durable storage
      </para></entry>
      </row>
 
+     <row>
index fd7fcaf59e3..c665985aca8 100644
@@ -1396,6 +1396,8 @@ SELECT
index 2de280ecb6f..af87c1dd3b3 100644
 
@@ -917,6 +918,14 @@ PGSharedMemoryReAttach(void)
 	dsm_set_control_handle(hdr->dsm_control);

 /*
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index b163e89cbb5..a658bc0d131 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -545,6 +545,7 @@ typedef struct
 #endif
 	char		my_exec_path[MAXPGPATH];
 	char		pkglib_path[MAXPGPATH];
+	int			max_total_bkend_mem;
 } BackendParameters;
 
 static void read_backend_variables(char *id, Port **port, BackgroundWorker **worker);
@@ -6130,6 +6131,8 @@ save_backend_variables(BackendParameters *param, Port *port, BackgroundWorker *w

 
@@ -6372,6 +6375,8 @@ restore_backend_variables(BackendParameters *param, Port **port, BackgroundWorke

index a9e0987747b..2c74580cec0 100644

 
index 81304a569c0..51a6c312284 100644

index 838a7337933..a3d610a2461 100644
 
+/*

+/*
@@ -1291,6 +1322,8 @@ pgstat_set_allocated_bytes_storage(uint64 *allocated_bytes,

 /*
@@ -1314,6 +1347,23 @@ pgstat_reset_allocated_bytes_storage(void)
 								*my_dsm_allocated_bytes);
 	}
 
+	/*
@@ -1327,4 +1377,101 @@ pgstat_reset_allocated_bytes_storage(void)
index 9b52cc5091f..09451bc4298 100644
@@ -2077,7 +2077,7 @@ pg_stat_get_memory_allocation(PG_FUNCTION_ARGS)
@@ -2087,15 +2087,23 @@ pg_stat_get_global_memory_allocation(PG_FUNCTION_ARGS)
 
 	/* datid */

index 012d4a0b1fd..cd68e5265af 100644
 
-
 /*
  * Constants
  *
index 2b082c68df6..02a060a3da8 100644
@@ -171,8 +171,16 @@ InitPostmasterChild(void)
 
+	/*
 
 /*
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 9f59440526f..bcad500bde7 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3553,6 +3553,17 @@ struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"max_total_backend_memory", PGC_SU_BACKEND, RESOURCES_MEM,
+			gettext_noop("Restrict total backend memory allocations to this max."),
+			gettext_noop("0 turns this feature off."),
+			GUC_UNIT_MB
+		},
+		&max_total_bkend_mem,
+		0, 0, INT_MAX,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2809c711a1..59cb9886d73 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -160,6 +160,9 @@
 #vacuum_buffer_usage_limit = 256kB	# size of vacuum and analyze buffer access strategy ring;
 					# 0 to disable vacuum buffer access strategy;
 					# range 128kB to 16GB
+#max_total_backend_memory = 0MB		# Restrict total backend memory allocations
+									# to this max (in MB). 0 turns this feature
+									# off.
 
 # - Disk -
 
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 7af0d141da2..656ec384851 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -438,6 +438,18 @@ AllocSetContextCreateInternal(MemoryContext parent,
 	else
 		firstBlockSize = Max(firstBlockSize, initBlockSize);
 
@@ -737,6 +749,11 @@ AllocSetAlloc(MemoryContext context, Size size)
 #endif
 
@@ -937,6 +954,10 @@ AllocSetAlloc(MemoryContext context, Size size)

@@ -1175,6 +1196,18 @@ AllocSetRealloc(void *pointer, Size size)
 		blksize = chksize + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
 		oldblksize = block->endptr - ((char *) block);
 
+		/*
index 0ed54571497..a1667c34371 100644
@@ -200,6 +200,16 @@ GenerationContextCreate(MemoryContext parent,
 
@@ -376,6 +386,9 @@ GenerationAlloc(MemoryContext context, Size size)
 
@@ -479,6 +492,9 @@ GenerationAlloc(MemoryContext context, Size size)

 			if (block == NULL)
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index c99ff532af2..34fe5d713e0 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -360,7 +360,16 @@ SlabContextCreate(MemoryContext parent,
 		elog(ERROR, "block size %zu for slab is too small for %zu-byte chunks",
 			 blockSize, chunkSize);
 
-
 
@@ -563,6 +572,10 @@ SlabAlloc(MemoryContext context, Size size)
 
index fe0549c43d9..19839fc0459 100644
@@ -5448,9 +5448,9 @@
index 26b17d66477..e53ed1cba05 100644
 
index c2c8ba7214d..f07db4e57ff 100644

 /* ----------

 /* ----------
@@ -365,6 +371,7 @@ extern PgBackendStatus *pgstat_get_beentry_by_backend_id(BackendId beid);
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_backend_id(BackendId beid);
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_index(int idx);
 extern char *pgstat_clip_activity(const char *raw_activity);
+extern bool exceeds_max_total_bkend_mem(uint64 allocation_request);
 
 /* ----------
  * pgstat_report_allocated_bytes_decrease() -
@@ -380,7 +387,7 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 {
 	uint64		temp;
 
@@ -389,13 +396,35 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 
@@ -417,6 +446,30 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 
 	return;
@@ -434,7 +487,13 @@ static inline void
 
 	/* Increase allocator type allocated bytes */
 	switch (pg_allocator_type)
@@ -459,6 +518,9 @@ pgstat_report_allocated_bytes_increase(int64 proc_allocated_bytes,
 			break;
 	}

@@ -478,6 +540,36 @@ pgstat_init_allocated_bytes(void)
 	*my_generation_allocated_bytes = 0;
 	*my_slab_allocated_bytes = 0;

index 7d412b26801..cf7a2b52359 100644
@@ -1882,13 +1882,15 @@ pg_stat_global_memory_allocation| WITH sums AS (
2.41.0

---

From: "Anton A. Melnikov" <[email-masked]>
Date: Tue, 23 Jan 2024 14:47:18 +0300

Hi!

Thanks for your interest and reply!

> number of cores, and so on. 12GB RAM is not quite huge, so presumably it

It is HP ProLanit 2x socket server. 2x6 cores Intel(R) Xeon(R) CPU X5675 @ 3.07GHz,
2x12GB RAM, RAID from SSD drives.
Linux 5.10.0-21-amd64 #1 SMP Debian 5.10.162-1 (2023-01-21) x86_64 GNU/Linux

One cpu was disabled and some tweaks was made as Andres advised to avoid
NUMA and other side effects.

Full set of the configuration commands for server was like that:
numactl --cpunodebind=0 --membind=0 --physcpubind=1,3,5,7,9,11 bash
sudo cpupower frequency-set -g performance
sudo cpupower idle-set -D0
echo 3059000 | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_min_freq
(Turbo Boost and hyperthreading was disabled in BIOS.)

> you could collect such numbers too?

Firstly, i repeated the same -c and -j values in read-only mode as you advised.
As one can see in the read-only.png, the absolute TPS value has increased
significantly, by about 13 times.
Patched version with limit 200Mb was slightly slower than with limit 0 by ~2%.
The standard error in all series was ~0.5%.
Since the deviation has increased in comparison with rw test
the difference between unpatched version and patched ones with
limits 0, 8Gb and 16Gb is not sensible.
There is a raw data in the raw_data-read-only.txt.

> some random effects at that level.

To avoid possible I/O bottleneck i followed these steps:
- gave all 24G mem to cpu 0 rather than 12G as in [1];
- created a ramdisk of 12G size;
- disable swap like that:
numactl --cpunodebind=0 --physcpubind=1,3,5,7,9,11 bash
sudo swapoff -a
sudo mkdir /mnt/ramdisk
sudo mount -t tmpfs -o rw,size=12G tmpfs /mnt/ramdisk
  
The inst dir, data dir and log file were all on ramdisk.

Pgbench in rw mode gave the following results:
- the difference between unpatched version and patched ones with
limits 0 and 16Gb almost the same: ~7470+-0.2% TPS.
  (orange, green and blue distributions on the RW-ramdisk.png respectively)
- patched version with limit 8GB is slightly slower than three above;
  (yellow distribution)
- patched version with limit 200MB slower than the first three
   by a measurable value ~0,4% (~7440 TPS);
  (black distribution)
  The standard error in all series was ~0.2%. There is a raw data in the
  raw_data-rw-ramdisk.txt

For the sake of completeness i'm going to repeat read-only measurements
with ramdisk. Аnd perform some tests with increased -c and -j values
as you advised to find the possible point where concurrency/blocking
bottlenecks start to play a role. And do this, of cause, for the last
version of the patch. Thanks for rebased it!

In general, i don't observe any considerable degradation in performance
from this patch of several or even 10%, which were mentioned in [2].

With the best regards,

[1] https://www.postgresql.org/message-id/1d3a7d8f-cb7c-4468-a578-d8a1194ea2de%40postgrespro.ru
[2] https://www.postgresql.org/message-id/3178e9a1b7acbcf023fafed68ca48d76afc07907.camel%40crunchydata.com

---

From: Tomas Vondra <[email-masked]>
Date: Sun, 28 Jan 2024 20:11:56 +0100

Hi,

I took a closer look at this patch over the last couple days, and I did
a bunch of benchmarks to see how expensive the accounting is. The good
news is that I haven't observed any overhead - I did two simple tests,
that I think would be particularly sensitive to this:

1) regular "pgbench -S" with scale 10, so tiny and not hitting I/O

2) "select count(*) from generate_series(1,N)" where N is either 10k or
1M, which should be enough to cause a fair number of allocations

And I tested this on three branches - master (no patches applied),
patched (but limit=0, so just accounting) and patched-limit  (with limit
set to 4.5GB, so high enough not to be hit).

Attached are script and raw results for both benchmarks from two
machines, i5 (small, 4C) and xeon (bigger, 16C/32C), and a PDF showing
the results as candlestick charts (with 1 and 2 sigma intervals). AFAICS
there's no measurable difference between master and patched builds.
Which is good, clearly the local allowance makes the overhead negligible
(at least in these benchmarks).

Now, for the patch itself - I already said some of the stuff about a
month ago [1], but I'll repeat some of it for the sake of completeness
before I get to comments about the code etc.

Firstly, I agree with the goal of having a way to account for memory
used by the backends, and also ability to enforce some sort of limit.
It's difficult to track the memory at the OS level (interpreting RSS
values is not trivial), and work_mem is not sufficient to enforce a
backend-level limit, not even talking about a global limit.

But as I said earlier, it seems quite strange to start by introducing
some sort of global limit, combining memory for all backends. I do
understand that the intent is to have such global limit in order to
prevent issues with the OOM killer and/or not to interfere with other
stuff running on the same machine. And while I'm not saying we should
not have such limit, every time I wished to have a memory limit it was a
backend-level one. Ideally a workmem-like limit that would "adjust" the
work_mem values used by the optimizer (but that's not what this patch
aims to do), or at least a backstop in case something goes wrong (say, a
memory leak, OLTP application issuing complex queries, etc.).

The accounting and infrastructure introduced by the patch seems to be
suitable for both types of limits (global and per-backend), but then it
goes for the global limit first. It may seem simpler to implement, but
in practice there's a bunch of problems mentioned earlier, but ignored.

For example, I really don't believe it's OK to just report the error in
the first backend that happens to hit the limit. Bertrand mentioned that
[2], asking about a situation when a runaway process allocates 99% of
memory. The response to that was

> Initially, we believe that punishing the detector is reasonable if we
> can help administrators avoid the OOM killer/resource starvation.  But
> we can and should expand on this idea.

which I find rather unsatisfactory. Belief is not an argument, and it
relies on the assumption that this helps the administrator to avoid the
OOM killer etc. ISTM it can easily mean the administrator can't even
connect to the database, run a query (to inspect the new system views),
etc. because any of that would hit the memory limit. That seems more
like a built-in DoS facility ...

If this was up to me, I'd probably start with the per-backend limit. But
that's my personal opinion.

Now, some comments about the code etc. Stephen was promising a new patch
version in October [6], but that didn't happen yet, so I looked at the
patches I rebased in December.

0001
----

1) I don't see why we should separate memory by context type - without
knowing which exact backends are using the memory, this seems pretty
irrelevant/useless. Either it's private or shared memory, I don't think
the accounting should break this into more counters. Also, while I'm not
aware of anyone proposing a new memory context type, I doubt we'd want
to add more and more counters if that happens.

suggestion: only two counters, for local and shared memory

If we absolutely want to have per-type counters, we should not mix the
private memory and shared memory ones.

2) I have my doubts about the tracking of shared memory. It seems weird
to count them only into the backend that allocated them, and transfer
them to "global" on process exit. Surely we should know which shared
memory is meant to be long-lived from the start, no?

For example, it's not uncommon that an extension allocates a chunk of
shared memory to store some sort of global state (e.g. BDR/pglogical
does that, I'm sure other extensions do that). But the process that
allocates the shared memory keeps running. AFAICS this would be tracked
as backend DSM memory, which I'm not sure this is what we want ...

And I'm not alone in thinking this should work differently - [3], [4].

3) We limit the floor of allocation counters to zero.

This seems really weird. Why would it be necessary? I mean, how could we
get a negative amount of memory? Seems like it might easily mask issues
with incorrect accounting, or something.

4) pg_stat_memory_allocation

Maybe pg_stat_memory_usage would be a better name ...

5) The comments/docs repeatedly talk about "dynamic nature" and that it
makes the values not exact:

> Due to the dynamic nature of memory allocations the allocated bytes
> values may not be exact but should be sufficient for the intended
> purposes.

I don't understand what "dynamic nature" refers to, and why would it
make the values not exact. Presumably this refers to the "allowance" but
how is that "dynamic nature"?

This definitely needs to explain this better, with some basic estimate
how how accurate the values are expected to be.

6) The SGML docs keep recommending to use pg_size_pretty(). I find that
unnecessary, no other docs reporting values in bytes do that.

7) I see no reason to include shared_memory_size_mb in the view, and
same for shared_memory_size_in_huge_pages. It has little to do with
"allocated" memory, IMO. And a value in "MB" goes directly against the
suggestion to use pg_size_pretty().

suggestion: drop this from the view

8) In a lot of places we do

    context->mem_allocated += blksize;
    pgstat_report_allocated_bytes_increase(blksize, PG_ALLOC_ASET);

Maybe we should integrate the two types of accounting, wrap them into a
single function call, or something? This makes it very simple to forget
updating one of those places. AFAIC the patch tries to minimize the
number of updates of the new shared counters, but with the allowance
that should not be an issue I think.

9) This seems wrong. Why would it be OK to ever overflow? Isn't that a
sign of incorrect accounting?

    /* Avoid allocated_bytes unsigned integer overflow on decrease */
    if (pg_sub_u64_overflow(*my_allocated_bytes, proc_allocated_bytes,
&temp))
    {
        /* On overflow, set allocated bytes and allocator type bytes to
zero */
        *my_allocated_bytes = 0;
        *my_aset_allocated_bytes = 0;
        *my_dsm_allocated_bytes = 0;
        *my_generation_allocated_bytes = 0;
        *my_slab_allocated_bytes = 0;
    }

9) How could any of these values be negative? It's all capped to 0 and
also stored in uint64. Seems pretty useless.

+SELECT
+    datid > 0, pg_size_bytes(shared_memory_size) >= 0,
shared_memory_size_in_huge_pages >= -1, global_dsm_allocated_bytes >= 0
+FROM
+    pg_stat_global_memory_allocation;
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+-- ensure that pg_stat_memory_allocation view exists
+SELECT
+    pid > 0, allocated_bytes >= 0, aset_allocated_bytes >= 0,
dsm_allocated_bytes >= 0, generation_allocated_bytes >= 0,
slab_allocated_bytes >= 0

0003
----

1) The commit message says:

> Further requests will not be allocated until dropping below the limit.
> Keep this in mind when setting this value.

The SGML docs have a similar "keep this in mind" suggestion in relation
to the 1MB local allowance. I find this pretty useless, as it doesn't
really say what to do with the information / what it means. I mean,
should I set the value higher, or what am I supposed to do? This needs
to be understandable for average user reading the SGML docs, who is
unlikely to know the implementation details.

2) > This limit does not affect auxiliary backend processes.

This seems pretty unfortunate, because in the cases where I actually saw
OOM killer to intervene, it was often because of auxiliary processes
allocating a lot of memory (say, autovacuum with maintenance_work_mem
set very high, etc.).

In a way, not excluding these auxiliary processes from the limit seems
to go against the idea of preventing the OOM killer.

3) I don't think the patch ever explains what the "local allowance" is,
how the refill works, why it's done this way, etc. I do think I
understand that now, but I had to go through the thread, That's not
really how it should be. There should be an explanation of how this
works somewhere (comment in mcxt.c? separate README?).

4) > doc/src/sgml/monitoring.sgml

Not sure why this removes the part about DSM being included only in the
backend that created it.

5) > total_bkend_mem_bytes_available

The "bkend" name is strange (to save 2 characters), and the fact how it
combines local and shared memory seems confusing too.

6)
+ /*
+ * Local counter to manage shared memory allocations. At backend
startup, set to
+ * initial_allocation_allowance via pgstat_init_allocated_bytes().
Decrease as
+ * memory is malloc'd. When exhausted, atomically refill if available from
+ * ProcGlobal->max_total_bkend_mem via exceeds_max_total_bkend_mem().
+ */
+uint64		allocation_allowance = 0;

But this allowance is for shared memory too, and shared memory is not
allocated using malloc.

7) There's a lot of new global variables. Maybe it'd be better to group
them into a struct, or something?

8) Why does pgstat_set_allocated_bytes_storage need the new return?

9) Unnecessary changes in src/backend/utils/hash/dynahash.c (whitespace,
new comment that seems not very useful)

10) Shouldn't we have a new malloc wrapper that does the check? That way
we wouldn't need to have the call in every memory context place calling
malloc.

11) I'm not sure about the ereport() calls after hitting the limit.
Adres thinks it might lead to recursion [4], but Stephen [5] seems to
think this does not really make the situation worse. I'm not sure about
that, though - I agree the ENOMEM can already happen, but but maybe
having a limit (which clearly needs to be more stricter than the limit
used by kernel for OOM) would be more likely to hit?

12) In general, I agree with Andres [4] that we'd be better of to focus
on the accounting part, see how it works in practice, and then add some
ability to limit memory after a while.

regards

[1]
https://www.postgresql.org/message-id/4fb99fb7-8a6a-2828-dd77-e2f1d75c7dd0%40enterprisedb.com

[2]
https://www.postgresql.org/message-id/3b9b90c6-f4ae-a7df-6519-847ea9d5fe1e%40amazon.com

[3]
https://www.postgresql.org/message-id/20230110023118.qqbjbtyecofh3uvd%40awork3.anarazel.de

[4]
https://www.postgresql.org/message-id/20230113180411.rdqbrivz5ano2uat%40awork3.anarazel.de

[5]
https://www.postgresql.org/message-id/ZTArWsctGn5fEVPR%40tamriel.snowman.net

[6]
https://www.postgresql.org/message-id/ZTGycSYuFrsixv6q%40tamriel.snowman.net

---

From: Aleksander Alekseev <[email-masked]>
Date: Tue, 12 Mar 2024 16:30:23 +0300

Hi,

> [...]

Just wanted to let you know that v20231226 doesn't apply. The patch
needs love from somebody interested in it.

Best regards,
Aleksander Alekseev (wearing a co-CFM hat)

---

From: "Anton A. Melnikov" <[email-masked]>
Date: Wed, 13 Mar 2024 10:41:45 +0300

> needs love from somebody interested in it.

Thanks for pointing to this!
Here is a version updated for the current master.

With the best regards,

From e31012217fb63f57fe16d7232fec27bb6cd45597 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 17:54:40 +0100
Subject: [PATCH 1/3] Add tracking of backend memory allocated

 src/backend/utils/mmgr/generation.c         |  14 ++
 17 files changed, 868 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 8aca08140e..5f827fe0b0 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4598,6 +4598,252 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
  </sect2>

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 04227a72d1..61ff4a59b6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1375,3 +1375,37 @@ CREATE VIEW pg_stat_subscription_stats AS
 
index c2e33a7e43..b950640643 100644
@@ -802,6 +802,15 @@ dsm_detach_all(void)
@@ -882,7 +891,7 @@ dsm_detach(dsm_segment *seg)
index 8dd669e0ce..af67e55bdf 100644

 
+		/*
 
+	/*
 
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index f3e20038f4..1959b5f4e6 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -180,6 +180,7 @@ InitProcGlobal(void)
 	ProcGlobal->checkpointerLatch = NULL;
 	pg_atomic_init_u32(&ProcGlobal->procArrayGroupFirst, INVALID_PROC_NUMBER);
 	pg_atomic_init_u32(&ProcGlobal->clogGroupFirst, INVALID_PROC_NUMBER);
+	pg_atomic_init_u64(&ProcGlobal->global_dsm_allocation, 0);
 
 	/*
 	 * Create and initialize all the PGPROC structures we'll need.  There are
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 1ccf4c6d83..25b545441b 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -48,6 +48,24 @@ int			pgstat_track_activity_query_size = 1024;
 /* exposed so that backend_progress.c can access it */
 PgBackendStatus *MyBEEntry = NULL;
 
+/*
 
@@ -382,6 +400,32 @@ pgstat_bestart(void)
 
@@ -441,6 +485,9 @@ pgstat_beshutdown_hook(int code, Datum arg)

 	return activity;
 }
+
+/*
index 3876339ee1..fec7ae2d5f 100644
@@ -2032,3 +2032,87 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 
index 537d92c0cf..9b47450813 100644
 
 /*
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index d2dcf526d6..a599a3a6e4 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -47,6 +47,7 @@
 #include "postgres.h"

 

 

 

 

 

 	/* Finally, free the context header, including the keeper block */
 	free(set);
@@ -717,6 +729,7 @@ AllocSetAllocLarge(MemoryContext context, Size size, int flags)
 		return MemoryContextAllocationFailure(context, size, flags);

 	block->aset = set;
 	block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -923,6 +936,7 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
 		return MemoryContextAllocationFailure(context, size, flags);

 	block->aset = set;
 	block->freeptr = ((char *) block) + ALLOC_BLOCKHDRSZ;
@@ -1100,6 +1114,7 @@ AllocSetFree(void *pointer)
 			block->next->prev = block->prev;

 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, block->freeptr - ((char *) block));
@@ -1233,7 +1248,9 @@ AllocSetRealloc(void *pointer, Size size, int flags)

 
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 9124d9b952..d511b67b08 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -37,6 +37,7 @@
 
 #include "lib/ilist.h"
@@ -266,6 +267,7 @@ GenerationContextCreate(MemoryContext parent,
 						name);

@@ -284,6 +286,7 @@ GenerationReset(MemoryContext context)

@@ -306,9 +309,14 @@ GenerationReset(MemoryContext context)

@@ -329,6 +337,9 @@ GenerationDelete(MemoryContext context)
 {
 	/* Reset to release all releasable GenerationBlocks */
 	GenerationReset(context);
+
+	pgstat_report_allocated_bytes_decrease(context->mem_allocated, PG_ALLOC_GENERATION);
+
 	/* And free the context header and keeper block */
 	free(context);
 }
@@ -366,6 +377,7 @@ GenerationAllocLarge(MemoryContext context, Size size, int flags)
 		return MemoryContextAllocationFailure(context, size, flags);

 	/* block with a single (used) chunk */
 	block->context = set;
@@ -488,6 +500,7 @@ GenerationAllocFromNewBlock(MemoryContext context, Size size, int flags,
 		return MemoryContextAllocationFailure(context, size, flags);

 	/* initialize the new block */
 	GenerationBlockInit(set, block, blksize);
@@ -672,6 +685,7 @@ GenerationBlockFree(GenerationContext *set, GenerationBlock *block)
 	dlist_delete(&block->node);
 
 	((MemoryContext) set)->mem_allocated -= block->blksize;
+	pgstat_report_allocated_bytes_decrease(block->blksize, PG_ALLOC_GENERATION);
 
 #ifdef CLOBBER_FREED_MEMORY
 	wipe_mem(block, block->blksize);
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index 516e1c95aa..98a8bbaf91 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -69,6 +69,7 @@
 #include "postgres.h"
 
 #include "lib/ilist.h"
 
+	/*

 

 
@@ -569,6 +589,7 @@ SlabAllocFromNewBlock(MemoryContext context, Size size, int flags)
 
 		block->slab = slab;
 
@@ -797,6 +818,7 @@ SlabFree(void *pointer)
 
 		/*
index 291ed876fc..47903e755e 100644
index 1095aefddf..a350f455cd 100644
@@ -422,6 +422,8 @@ typedef struct PROC_HDR
 
index 7b7f6f59d0..cbf801884d 100644
 
+#include "common/int.h"

 /* ----------

@@ -292,6 +310,11 @@ extern PGDLLIMPORT int pgstat_track_activity_query_size;
  * ----------
  */

 /* ----------
@@ -323,7 +346,12 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 
 /* ----------
  * Support functions for the SQL-callable functions to
@@ -336,5 +364,119 @@ extern LocalPgBackendStatus *pgstat_get_local_beentry_by_proc_number(ProcNumber
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_index(int idx);
 extern char *pgstat_clip_activity(const char *raw_activity);
 
+/* ----------
 
index 0cd2c64fca..2f62f5e4ea 100644
@@ -1877,6 +1877,24 @@ pg_stat_database_conflicts| SELECT oid AS datid,
@@ -1903,6 +1921,15 @@ pg_stat_io| SELECT backend_type,
index 6e08898b18..902d827998 100644
 
 DROP TABLE brin_hot_3;
 SET enable_seqscan = on;
index d8ac0d06f4..40746c9258 100644
 
 SET enable_seqscan = on;
 
2.43.2

From 0934a92f51ccb19266a2faa2f0b9146e3f9a665c Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 18:05:02 +0100
Subject: [PATCH 2/3] fixup: pgstat_get_local_beentry_by_index

---

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index fec7ae2d5f..9c82ec48cb 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2057,7 +2057,7 @@ pg_stat_get_memory_allocation(PG_FUNCTION_ARGS)
 		PgBackendStatus *beentry;

2.43.2

From 3e65eb6c298d3160db9e528ab3c2b857acd57937 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 17:55:23 +0100
Subject: [PATCH 3/3] Add the ability to limit the amount of memory that can be
 allocated to backends.

Specifies a limit to the amount of memory (in MB) that may be allocated to
backends in total (i.e. this is not a per user or per backend limit). If unset,
or set to 0 it is disabled. It is intended as a resource to help avoid the OOM
killer on LINUX and manage resources in general. A backend request that would
exhaust max_total_backend_memory memory will be denied with an out of memory
error causing that backend's current query/transaction to fail.  Further
requests will not be allocated until dropping below the limit. Keep this in
mind when setting this value. Due to the dynamic nature of memory allocations,
this limit is not exact. This limit does not affect auxiliary backend
processes. Backend memory allocations are displayed in the
pg_stat_memory_allocation and pg_stat_global_memory_allocation views.
---
 doc/src/sgml/config.sgml                      |  30 +++
 doc/src/sgml/monitoring.sgml                  |  38 +++-
 src/backend/catalog/system_views.sql          |   2 +
 src/backend/port/sysv_shmem.c                 |   9 +
 src/backend/postmaster/postmaster.c           |   5 +
 src/backend/storage/ipc/dsm_impl.c            |  18 ++
 src/backend/storage/lmgr/proc.c               |  45 +++++
 src/backend/utils/activity/backend_status.c   | 175 ++++++++++++++++++
 src/backend/utils/adt/pgstatfuncs.c           |  16 +-
 src/backend/utils/hash/dynahash.c             |   3 +-
 src/backend/utils/init/miscinit.c             |   8 +
 src/backend/utils/misc/guc_tables.c           |  11 ++
 src/backend/utils/misc/postgresql.conf.sample |   3 +
 src/backend/utils/mmgr/aset.c                 |  33 ++++
 src/backend/utils/mmgr/generation.c           |  16 ++
 src/backend/utils/mmgr/slab.c                 |  15 +-
 src/include/catalog/pg_proc.dat               |   6 +-
 src/include/storage/proc.h                    |   7 +
 src/include/utils/backend_status.h            | 103 ++++++++++-
 src/test/regress/expected/rules.out           |   4 +-
 20 files changed, 526 insertions(+), 21 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 65a6e6c408..4f26284ee1 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2306,6 +2306,36 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>

index 5f827fe0b0..dbe40b8810 100644
@@ -4658,10 +4658,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
@@ -4778,6 +4775,39 @@ description | Waiting for a newly initialized WAL file to reach durable storage
      </para></entry>
      </row>
 
+     <row>
index 61ff4a59b6..c646b28f16 100644
@@ -1402,6 +1402,8 @@ SELECT
index 1a6d8fa0fb..c0788732df 100644
@@ -918,6 +919,14 @@ PGSharedMemoryReAttach(void)

 /*
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 49fc6f979e..abb10ed7c8 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -556,6 +556,7 @@ typedef struct
 #endif
 	char		my_exec_path[MAXPGPATH];
 	char		pkglib_path[MAXPGPATH];
+	int			max_total_bkend_mem;
 } BackendParameters;
 
 static void read_backend_variables(char *id, ClientSocket **client_sock, BackgroundWorker **worker);
@@ -6152,6 +6153,8 @@ save_backend_variables(BackendParameters *param, ClientSocket *client_sock, Back

 
@@ -6391,6 +6394,8 @@ restore_backend_variables(BackendParameters *param, ClientSocket **client_sock,

index af67e55bdf..b6dee75423 100644

 
index 1959b5f4e6..6dbe4373f8 100644
@@ -50,6 +50,7 @@
 
@@ -182,6 +183,50 @@ InitProcGlobal(void)
 	pg_atomic_init_u32(&ProcGlobal->clogGroupFirst, INVALID_PROC_NUMBER);
 	pg_atomic_init_u64(&ProcGlobal->global_dsm_allocation, 0);
 
index 25b545441b..c125dc3abb 100644
@@ -44,6 +44,12 @@
 
+/*
 
@@ -67,6 +73,31 @@ uint64	   *my_generation_allocated_bytes = &local_my_generation_allocated_bytes;
 
+/*

 /*
 
+	/*
@@ -1308,4 +1358,129 @@ pgstat_reset_allocated_bytes_storage(void)
+			switch (MyBackendType)
+				case B_STARTUP:
+				case B_ARCHIVER:
+				case B_BG_WRITER:
+				case B_CHECKPOINTER:
+				case B_WAL_WRITER:
+				case B_WAL_RECEIVER:
+				case B_WAL_SUMMARIZER:
+				default:
+			if (MyProcPid == PostmasterPid)
+	switch (MyBackendType)
+		case B_STARTUP:
+		case B_ARCHIVER:
+		case B_BG_WRITER:
+		case B_CHECKPOINTER:
+		case B_WAL_WRITER:
+		case B_WAL_RECEIVER:
+		case B_WAL_SUMMARIZER:
+		default:
+	if (MyProcPid == PostmasterPid)
index 9c82ec48cb..15d2800d5b 100644
@@ -2093,7 +2093,7 @@ pg_stat_get_memory_allocation(PG_FUNCTION_ARGS)
@@ -2103,15 +2103,23 @@ pg_stat_get_global_memory_allocation(PG_FUNCTION_ARGS)
 
 	/* datid */

index a4152080b5..9450032a89 100644
 
-
 /*
  * Constants
  *
index 9b47450813..d72ec3a0db 100644
 
+	/*
 
 /*
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index d77214795d..56aca7d5bc 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3626,6 +3626,17 @@ struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
index 2244ee52f7..50e2bb2bcf 100644
@@ -169,6 +169,9 @@
 
 # - Disk -
 
index a599a3a6e4..be56585853 100644
 
@@ -724,6 +736,11 @@ AllocSetAllocLarge(MemoryContext context, Size size, int flags)
 #endif
 
 	blksize = chunk_size + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
+
+	/* Do not exceed maximum allowed memory allocation */
+	if (exceeds_max_total_bkend_mem(blksize))
+		return NULL;
+
 	block = (AllocBlock) malloc(blksize);
 	if (block == NULL)
 		return MemoryContextAllocationFailure(context, size, flags);
@@ -917,6 +934,10 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
 	while (blksize < required_size)
 		blksize <<= 1;

@@ -1238,6 +1259,18 @@ AllocSetRealloc(void *pointer, Size size, int flags)
 		blksize = chksize + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
 		oldblksize = block->endptr - ((char *) block);
 
+		/*
index d511b67b08..ea4dc2cb27 100644
@@ -203,6 +203,16 @@ GenerationContextCreate(MemoryContext parent,
 
+	if (exceeds_max_total_bkend_mem(allocSize))
+	{
+		MemoryContextStats(TopMemoryContext);
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("out of memory - exceeds max_total_backend_memory"),
+				 errdetail("Failed while creating memory context \"%s\".",
+						   name)));
+	}
+
 	/*
 	 * Allocate the initial block.  Unlike other generation.c blocks, it
 	 * starts with the context header and its block header follows that.
@@ -372,6 +382,9 @@ GenerationAllocLarge(MemoryContext context, Size size, int flags)
 	required_size = chunk_size + Generation_CHUNKHDRSZ;
 	blksize = required_size + Generation_BLOCKHDRSZ;
 
+	if (exceeds_max_total_bkend_mem(blksize))
+		return NULL;
+
 	block = (GenerationBlock *) malloc(blksize);
 	if (block == NULL)
 		return MemoryContextAllocationFailure(context, size, flags);
@@ -494,6 +507,9 @@ GenerationAllocFromNewBlock(MemoryContext context, Size size, int flags,
 	if (blksize < required_size)
 		blksize = pg_nextpower2_size_t(required_size);

 	if (block == NULL)
index 98a8bbaf91..d0f80f10a2 100644
 
-
 
 	slab = (SlabContext *) malloc(Slab_CONTEXT_HDRSZ(chunksPerBlock));
 	if (slab == NULL)
@@ -582,6 +591,10 @@ SlabAllocFromNewBlock(MemoryContext context, Size size, int flags)
 	}
 	else
 	{
+		/* Do not exceed maximum allowed memory allocation */
+		if (exceeds_max_total_bkend_mem(slab->blockSize))
+			return MemoryContextAllocationFailure(context, size, flags);
+
 		block = (SlabBlock *) malloc(slab->blockSize);
 
index 47903e755e..13f1c15b6d 100644
index a350f455cd..fa7c94c6c9 100644
@@ -424,6 +424,13 @@ typedef struct PROC_HDR
 
index cbf801884d..a833941272 100644
@@ -14,7 +14,7 @@
-#include "storage/procnumber.h"

@@ -303,6 +303,7 @@ typedef struct LocalPgBackendStatus
  */
 extern PGDLLIMPORT bool pgstat_track_activities;
 extern PGDLLIMPORT int pgstat_track_activity_query_size;
+extern PGDLLIMPORT int max_total_bkend_mem;

 /* ----------
@@ -315,6 +316,10 @@ extern PGDLLIMPORT uint64 *my_aset_allocated_bytes;

 /* ----------
@@ -363,6 +368,7 @@ extern PgBackendStatus *pgstat_get_beentry_by_proc_number(ProcNumber procNumber)
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_proc_number(ProcNumber procNumber);
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_index(int idx);
 extern char *pgstat_clip_activity(const char *raw_activity);
+extern bool exceeds_max_total_bkend_mem(uint64 allocation_request);
 
 /* ----------
  * pgstat_report_allocated_bytes_decrease() -
@@ -378,7 +384,7 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 {
 	uint64		temp;
 
@@ -387,13 +393,35 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 
@@ -415,6 +443,30 @@ pgstat_report_allocated_bytes_decrease(int64 proc_allocated_bytes,
 
 	return;
@@ -432,7 +484,13 @@ static inline void
 
 	/* Increase allocator type allocated bytes */
 	switch (pg_allocator_type)
@@ -457,6 +515,9 @@ pgstat_report_allocated_bytes_increase(int64 proc_allocated_bytes,
 			break;
 	}

@@ -476,6 +537,36 @@ pgstat_init_allocated_bytes(void)
 	*my_generation_allocated_bytes = 0;
 	*my_slab_allocated_bytes = 0;

index 2f62f5e4ea..01dee8ddea 100644
@@ -1887,13 +1887,15 @@ pg_stat_global_memory_allocation| WITH sums AS (
2.43.2

---

From: "Anton A. Melnikov" <[email-masked]>
Date: Thu, 14 Mar 2024 23:36:12 +0300

> Here is a version updated for the current master.

During patch updating i mistakenly added double counting of deallocatated blocks.
That's why the tests in the patch tester failed.
Fixed it and squashed fix 0002 with 0001.
Here is fixed version.

With the best wishes!

From 7a0925a38acfb9a945087318a5d91fae4680db0e Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 17:54:40 +0100
Subject: [PATCH 1/2] Add tracking of backend memory allocated

 src/backend/utils/mmgr/generation.c         |  13 ++
 17 files changed, 867 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 8736eac284..41d788be45 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4599,6 +4599,252 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
  </sect2>

 

 
+		/*
 
+	/*

 	/*
 
+/*

 
 
 	return activity;
 }
+
+/*
index 3876339ee1..9c82ec48cb 100644

 /*
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 751cc3408c..cd074add17 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -47,6 +47,7 @@
 #include "postgres.h"
 
 #include "utils/memutils_internal.h"

 

 

 

 

 

 
 	block->aset = set;

 	block->aset = set;

 

diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 5d81af1f94..dbea4f248e 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -37,6 +37,7 @@
 
 #include "lib/ilist.h"
 #include "utils/memutils_internal.h"

 

 

 
 
index 3e15d59683..850760f485 100644
 
 #include "lib/ilist.h"
 #include "utils/memutils_internal.h"
 
+	/*

 

 
 
 		block->slab = slab;

 		/*
index 4af5c2e847..56ab1f9470 100644

+#include "common/int.h"

 /* ----------

 /* ----------
 
 /* ----------
 
+/* ----------

 DROP TABLE brin_hot_3;
 SET enable_seqscan = on;
 
 SET enable_seqscan = on;

From 72656bfa6819990be79bf925e5e888168aeb71a4 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Tue, 26 Dec 2023 17:55:23 +0100
Subject: [PATCH 2/2] Add the ability to limit the amount of memory that can be
 allocated to backends.

 
 
index 41d788be45..3c3689c03b 100644
@@ -4659,10 +4659,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
@@ -4779,6 +4776,39 @@ description | Waiting for a newly initialized WAL file to reach durable storage
      </para></entry>
      </row>
 
+     <row>

 /*

 

 

 

 
+/*

+/*

 /*
 
+	/*
 
 	/* datid */

 
-
 /*
  * Constants
  *
 
+	/*
 
 /*
index 57d9de4dd9..65f0b69319 100644
 
+	{
 
 # - Disk -
 
index cd074add17..f0ee6a9390 100644

 

+		/*
index dbea4f248e..3a6e3abeb8 100644

 
 
 	if (block == NULL)
index 850760f485..36b3f14279 100644
 
-

index 56ab1f9470..79869d1c41 100644

 /* ----------

 /* ----------
 
 /* ----------

 
 	return;

---

From: Alexander Lakhin <[email-masked]>
Date: Fri, 15 Mar 2024 10:00:00 +0300

Hello Anton,

> Fixed it and squashed fix 0002 with 0001.

Please try the following with the patches applied:
echo "shared_buffers = '1MB'
max_total_backend_memory = '10MB'" > /tmp/extra.config

CPPFLAGS="-Og" ./configure --enable-tap-tests --enable-debug --enable-cassert ...
TEMP_CONFIG=/tmp/extra.config make check

It fails for me as follows:
...
# postmaster did not respond within 60 seconds, examine ".../src/test/regress/log/postmaster.log" for the reason
...
src/test/regress/log/postmaster.log contains:
...
TRAP: failed Assert("ret != NULL"), File: "mcxt.c", Line: 1327, PID: 4109270
TRAP: failed Assert("ret != NULL"), File: "mcxt.c", Line: 1327, PID: 4109271
postgres: autovacuum launcher (ExceptionalCondition+0x69)[0x55ce441fcc6e]
postgres: autovacuum launcher (palloc0+0x0)[0x55ce4422eb67]
postgres: logical replication launcher (ExceptionalCondition+0x69)[0x55ce441fcc6e]
postgres: autovacuum launcher (InitDeadLockChecking+0xa6)[0x55ce4408a6f0]
postgres: logical replication launcher (palloc0+0x0)[0x55ce4422eb67]
postgres: logical replication launcher (InitDeadLockChecking+0x45)[0x55ce4408a68f]
postgres: autovacuum launcher (InitProcess+0x600)[0x55ce4409c6f2]
postgres: logical replication launcher (InitProcess+0x600)[0x55ce4409c6f2]
postgres: autovacuum launcher (+0x44b4e2)[0x55ce43ff24e2]
...
grep TRAP src/test/regress/log/postmaster.log | wc -l
445

Best regards,
Alexander

---

From: James Hunter <[email-masked]>
Date: Fri, 27 Dec 2024 11:14:00 -0800

Reviving this thread, because I am thinking about something related --
please ignore the "On Fri, Dec 27, 2024" date, this seems to be an
artifact of me re-sending the message, from the list archive. The
original message was from January 28, 2024.

> memory leak, OLTP application issuing complex queries, etc.).

I think what Tomas suggests is the right strategy. I am thinking of
something like:

1. Say we have a backend_work_mem limit. Then the total amount of
memory available on the entire system, for all queries, as work_mem,
would be backend_work_mem * max_connections.

2. We use this backend_work_mem to "adjust" work_mem values used by
the executor. (I don't care about the optimizer right now -- optimizer
just does its best to predict what will happen at runtime.)

At runtime, every node that uses work_mem currently checks its memory
usage against the session work_mem (and possibly hash_mem_multiplier)
GUC(s). Instead, now, every node would check against its node-specific
"adjusted" work_mem. If it exceeds this limit, it spills, using
existing logic.

In other words -- existing logic spills based on comparison to global
work_mem GUC. Let's make it spill, instead, based on comparison to an
operator-local "PlanState.work_mem" field.

And then let's set that "PlanState.work_mem" field based on a new
"backend_work_mem" GUC. Then no queries will run OOM (at least, not
due to work_mem -- we'll address other memory usages separately), so
they won't need to be canceled. Instead, they'll spill.

This strategy solves the ongoing problem of how to set work_mem, if
some queries have lots of operators and others don't -- now we just
set backend_work_mem, as a limit on the entire query's total work_mem.
And a bit of integration with the optimizer will allow us to
distribute the total backend_work_mem to individual execution nodes,
with the goal of minimizing spilling, without exceeding the
backend_work_mem limit.

Anyway, I revived this thread to see if there's interest in this sort
of strategy --

Thanks,
James

---

From: Tomas Vondra <[email-masked]>
Date: Sat, 28 Dec 2024 02:48:22 +0100

> Anyway, I revived this thread to see if there's interest in this sort

I think there's still interest in having a memory limit of this kind,
but it's not very clear to me what you mean by "adjusted work_mem". Can
you explain how would that actually work in practice?

Whenever I've been thinking about this in the past, it wasn't clear to
me how would we know when to start adjusting work_mem, because we don't
know which nodes will actually use work_mem concurrently.

We certainly don't know that during planning - e.g. because we don't
actually know what nodes will be "above" the operation we're planning.
And AFIAK we don't have any concept which parts of the plan may be
active at the same time ...

Let's say we have limits work_mem=4MB and query_mem=8MB (to keep it
simple). And we have a query plan with 3 separate hash joins, each
needing 4MB. I believe these things can happen:

1) The hash joins are in distant parts of the query plan, and will not
actually run concurrently - we'll run each hashjoin till completion
before starting the next one. So, no problem with query_mem.

2) The hash joins are active at the same time, initialized one by one.
But we don't know what, so we'll init HJ #1, it'll get 4MB of memory.
Then we'll init HJ #2, it'll also get 4MB of memory. And now we want to
init HJ #3, but we've already exhausted query_mem, but the memory is
already used and we can't reclaim it.

How would the work_mem get adjusted in these cases? Ideally, we'd not
adjust work_mem in (1), because it'd force hash joins to spill data to
disk, affecting performance when there's enough memory to just run the
query plan (but maybe that's a reasonable price for the memory limit?).
While in (2) we'd need to start adjusting the memory from the beginning,
but I don't think we know that so early.

Can you explain / walk me through the proposal for these cases?

regards

-- 
Tomas Vondra

---

From: "Anton A. Melnikov" <[email-masked]>
Date: Sat, 28 Dec 2024 15:36:37 +0300

Hi!

On 28.12.2024 04:48, Tomas Vondra wrote:
>> I think what Tomas suggests is the right strategy. 

I'm also interested in this topic. And agreed that it's best to move from the limit
for a separate backend to the global one. In more details let me suggest
the following steps or parts:
1) realize memory limitation for a separate backend independent from the work_mem GUC;
2) add workmem-like limit that would "adjust" the work_mem values used by
the optimize as Thomas suggested;
3) add global limit for all backends.

As for p.1 there is a patch that was originally suggested by my colleague
Maxim Orlov <[email-masked]> and which i modified for the current master.
This patch introduces the only max_backend_memory GUC that specifies
the maximum amount of memory that can be allocated to a backend.
Zero value means no limit.
If the allocated memory size is exceeded, a standard "out of memory" error will be issued.
Also the patch introduces the pg_get_backend_memory_contexts_total_bytes() function,
which allows to know how many bytes have already been allocated
to the process in contexts. And in the build with asserts it adds
the pg_get_backend_memory_allocation_stats() function that allows
to get additional information about memory allocations for debug purposes.

>> with the goal of minimizing spilling, without exceeding the

As for p.2 maybe one can set a maximum number of parallel sort or
hash table operations before writing to disk instead of absolute
value in the work_mem GUC? E.g. introduce а max_query_operations GUC
or a variable in such a way that old work_mem will be equal
to max_backend_memory divided by max_query_operations.

What do you think about such approach?

With the best wishes,

From 8ec3548604bf6a1f5f4c290495843e7080d09660 Mon Sep 17 00:00:00 2001
From: "Anton A. Melnikov" <[email-masked]>
Date: Sat, 28 Dec 2024 12:47:24 +0300
Subject: [PATCH]     Limit backend heap memory allocation

    This per-backend GUC can limit the amount of heap allocated
    memory.  Upon reaching this limit "out of memory" error will
    be triggered.

    GUC is diabled by default.

    Authors: Maxim Orlov <[email-masked]>, Anton A. Melnikov <[email-masked]>
---
 doc/src/sgml/config.sgml                      |  17 +
 src/backend/utils/activity/backend_status.c   | 335 ++++++++++++++++++
 src/backend/utils/misc/guc_tables.c           |  10 +
 src/backend/utils/misc/postgresql.conf.sample |   1 +
 src/backend/utils/mmgr/aset.c                 |  36 +-
 src/backend/utils/mmgr/generation.c           |  18 +-
 src/backend/utils/mmgr/slab.c                 |  17 +-
 src/include/catalog/pg_proc.dat               |  14 +
 src/include/utils/backend_status.h            |  10 +
 src/test/modules/Makefile                     |   4 +
 .../modules/test_backend_memory/.gitignore    |   4 +
 src/test/modules/test_backend_memory/Makefile |  14 +
 src/test/modules/test_backend_memory/README   |   1 +
 .../t/001_max_backend_memory.pl               |  85 +++++
 14 files changed, 540 insertions(+), 26 deletions(-)
 create mode 100644 src/test/modules/test_backend_memory/.gitignore
 create mode 100644 src/test/modules/test_backend_memory/Makefile
 create mode 100644 src/test/modules/test_backend_memory/README
 create mode 100644 src/test/modules/test_backend_memory/t/001_max_backend_memory.pl

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fbdd6ce574..4099d6e1a8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2337,6 +2337,23 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+    <varlistentry id="guc-max-backend-memory" xreflabel="max_backend_memory">
+      <term><varname>max_backend_memory</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>max_backend_memory</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum amount of memory that can be allocated to a backend.
+        Zero means no limit.
+       </para>
+       <para>
+        Default: 0
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
 
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index bf33e33a4e..a41f004cf7 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -12,6 +12,7 @@
 #include "postgres.h"
 
 #include "access/xact.h"
+#include "funcapi.h"
 #include "libpq/libpq-be.h"
 #include "miscadmin.h"
 #include "pg_trace.h"
@@ -20,6 +21,7 @@
 #include "storage/proc.h"		/* for MyProc */
 #include "storage/procarray.h"
 #include "utils/ascii.h"
+#include "utils/builtins.h"
 #include "utils/guc.h"			/* for application_name */
 #include "utils/memutils.h"
 
@@ -73,6 +75,17 @@ static void pgstat_beshutdown_hook(int code, Datum arg);
 static void pgstat_read_current_status(void);
 static void pgstat_setup_backend_status_context(void);
 
+/*
+ * Total memory size allocated by backend.
+ */
+static Size my_allocated_bytes = 0;
+
+/*
+ * GUC variable
+ *
+ * Max backend memory allocation allowed (MB). 0 = disabled
+ */
+int max_backend_memory_size_mb = 0;
 
 /*
  * Report shared-memory space needed by BackendStatusShmemInit.
@@ -1222,3 +1235,325 @@ pgstat_clip_activity(const char *raw_activity)
 
 	return activity;
 }
+
+#ifdef USE_ASSERT_CHECKING
+/*
+ * Single memory allocation.
+ */
+typedef struct
+{
+	void   *addr;
+	Size	size;
+	bool	deleted;
+} HeapBlock;
+
+/*
+ * Dynamic array of all allocated memory.
+ */
+typedef struct
+{
+	HeapBlock	   *blocks;
+	Size			size;
+	Size			capacity;
+	Size			ndeleted;
+} HeapBlockStat;
+
+static HeapBlockStat heapstat =
+{
+	.blocks = NULL,
+	.size = 0,
+	.capacity = 0,
+	.ndeleted = 0
+};
+
+/*
+ * Extend heapstat allocation stat array.
+ */
+static inline void
+HeapBlockStatExtend(void)
+{
+	if (heapstat.capacity == 0)
+	{
+#define INIT_MEMORY_CAPACITY 1024
+		heapstat.capacity = INIT_MEMORY_CAPACITY;
+		heapstat.blocks = (HeapBlock *) malloc(heapstat.capacity *
+											sizeof(heapstat.blocks[0]));
+	}
+	else if (heapstat.capacity == heapstat.size)
+	{
+		heapstat.capacity *= 2;
+		heapstat.blocks = (HeapBlock *) realloc(heapstat.blocks,
+											 heapstat.capacity *
+											 sizeof(heapstat.blocks[0]));
+	}
+}
+
+/*
+ * Compare HeapBlocks. Put not deleted in the beginning of an array.
+ */
+static int
+HeapBlockCmp(const void *p, const void *q)
+{
+	const HeapBlock    *a = (const HeapBlock *) p,
+					   *b = (const HeapBlock *) q;
+	int					arg1 = a->deleted,
+						arg2 = b->deleted;
+
+	if (arg1 < arg2)
+		return -1;
+	if (arg1 > arg2)
+		return 1;
+	return 0;
+}
+
+/*
+ * Put all not deleted items in the beginning.
+ */
+static void
+HeapBlockStatCompactify(void)
+{
+	if (heapstat.ndeleted < heapstat.size / 2)
+		return;
+
+	qsort(heapstat.blocks, heapstat.size, sizeof(heapstat.blocks[0]), HeapBlockCmp);
+	heapstat.size -= heapstat.ndeleted;
+	heapstat.ndeleted = 0;
+}
+
+/*
+ * Push new memory allocation.
+ */
+static void
+HeapBlockStatPush(void *ptr, Size size)
+{
+	Size	i;
+
+	HeapBlockStatExtend();
+	HeapBlockStatCompactify();
+
+	for (i = 0; i < heapstat.size; ++i)
+		Assert(heapstat.blocks[i].deleted || heapstat.blocks[i].addr != ptr);
+
+	Assert(heapstat.ndeleted <= heapstat.size);
+
+	/* Try to find deleted item to reuse it... */
+	for (i = 0; i < heapstat.size; ++i)
+	{
+		if (heapstat.blocks[i].deleted == true)
+		{
+			heapstat.blocks[i].addr = ptr;
+			heapstat.blocks[i].size = size;
+			heapstat.blocks[i].deleted = false;
+			--heapstat.ndeleted;
+			Assert(heapstat.ndeleted <= heapstat.size);
+			return;
+		}
+	}
+
+	/* ... no empty places, append! */
+	heapstat.blocks[i].addr = ptr;
+	heapstat.blocks[i].size = size;
+	heapstat.blocks[i].deleted = false;
+
+	++heapstat.size;
+}
+
+/*
+ * Pop memory allocation.
+ */
+static Size
+HeapBlockStatPop(void *ptr)
+{
+	Size	i;
+
+	for (i = 0; i < heapstat.size; ++i)
+	{
+		if (heapstat.blocks[i].addr == ptr)
+		{
+			Assert(heapstat.blocks[i].deleted == false);
+			heapstat.blocks[i].deleted = true;
+			++heapstat.ndeleted;
+			Assert(heapstat.ndeleted <= heapstat.size);
+			return heapstat.blocks[i].size;
+		}
+	}
+
+	/* should not get here... */
+	Assert(i != heapstat.size);
+	return 0;
+}
+#endif /* USE_ASSERT_CHECKING */
+
+/*
+ * Count backend allocated memory.
+ * If limit is set (max_backend_memory_size_mb) and check=true, then need to
+ * check my_allocated_bytes for going beyond the limit.
+ */
+static bool
+pgstat_alloc(Size size, bool check)
+{
+	/* Exclude auxiliary processes from the check */
+	switch (MyBackendType)
+	{
+		case B_STARTUP:
+		case B_ARCHIVER:
+		case B_BG_WRITER:
+		case B_CHECKPOINTER:
+		case B_WAL_WRITER:
+		case B_WAL_RECEIVER:
+		case B_WAL_SUMMARIZER:
+			return true;
+		default:
+			break;
+	}
+
+	my_allocated_bytes += size;
+
+	if (max_backend_memory_size_mb == 0 || !check)
+		return true;
+
+	/* Check for going beyond the limit */
+#define TO_BYTES(mb)	((Size)(mb) * 1024 * 1024)
+	if (my_allocated_bytes > TO_BYTES(max_backend_memory_size_mb))
+	{
+		my_allocated_bytes -= size;
+		return false;
+	}
+
+	return true;
+}
+
+/*
+ * Count deallocated backend memory.
+ */
+static void
+pgstat_free(Size size)
+{
+	/* Exclude auxiliary processes from the check */
+	switch (MyBackendType)
+	{
+		case B_STARTUP:
+		case B_ARCHIVER:
+		case B_BG_WRITER:
+		case B_CHECKPOINTER:
+		case B_WAL_WRITER:
+		case B_WAL_RECEIVER:
+		case B_WAL_SUMMARIZER:
+			return;
+		default:
+			break;
+	}
+
+	Assert(my_allocated_bytes >= size);
+	my_allocated_bytes -= size;
+}
+
+/*
+ * malloc() with bytes counter
+ */
+void *
+malloc_and_count(Size size)
+{
+	void   *ptr;
+
+	if (!pgstat_alloc(size, true))
+		return NULL;
+
+	ptr = malloc(size);
+	if (ptr == NULL)
+		pgstat_free(size);
+#ifdef USE_ASSERT_CHECKING
+	else
+		HeapBlockStatPush(ptr, size);
+#endif
+
+	return ptr;
+}
+
+/*
+ * realloc() with bytes counter
+ */
+void *
+realloc_and_count(void *ptr, Size new_size, Size old_size)
+{
+	Assert(old_size == HeapBlockStatPop(ptr));
+
+	pgstat_free(old_size);
+
+	if (!pgstat_alloc(new_size, true))
+	{
+		/* New buffer can not be alloced, so need to restore old one */
+		pgstat_alloc(old_size, false);
+#ifdef USE_ASSERT_CHECKING
+		HeapBlockStatPush(ptr, old_size);
+#endif
+		return NULL;
+	}
+
+	ptr = realloc(ptr, new_size);
+	if (ptr == NULL)
+		pgstat_free(new_size);
+#ifdef USE_ASSERT_CHECKING
+	else
+		HeapBlockStatPush(ptr, new_size);
+#endif
+
+	return ptr;
+}
+
+/*
+ * free() with bytes counter
+ */
+void
+free_and_count(void *ptr, Size size)
+{
+#ifdef USE_ASSERT_CHECKING
+	Assert(size == HeapBlockStatPop(ptr));
+#endif
+
+	pgstat_free(size);
+	free(ptr);
+}
+
+/*
+ * pg_get_backend_memory_contexts_total_bytes
+ *		Total amount of bytes in all allocated contexts.
+ */
+Datum
+pg_get_backend_memory_contexts_total_bytes(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(my_allocated_bytes);
+}
+
+/*
+ * pg_get_backend_memory_allocation_stats
+ *		SQL SRF showing backend allocated memory.
+ */
+Datum
+pg_get_backend_memory_allocation_stats(PG_FUNCTION_ARGS)
+{
+#ifdef USE_ASSERT_CHECKING
+#define MEMORY_ALLOCATION_COLS 3
+	ReturnSetInfo  *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	Datum			values[MEMORY_ALLOCATION_COLS];
+	bool			nulls[MEMORY_ALLOCATION_COLS];
+	Size			i;
+	char			text[16];
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	memset(nulls, 0, sizeof(nulls));
+	for (i = 0; i < heapstat.size; ++i)
+	{
+		pg_snprintf(text, sizeof(text), "%p", heapstat.blocks[i].addr);
+		values[0] = CStringGetTextDatum(text);
+		values[1] = Int64GetDatum(heapstat.blocks[i].size);
+		values[2] = BoolGetDatum(heapstat.blocks[i].deleted);
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+	}
+#else
+	elog(ERROR, "this only works for builds with asserts enabled");
+#endif
+
+	return (Datum) 0;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8cf1afbad2..f888d417de 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3732,6 +3732,16 @@ struct config_int ConfigureNamesInt[] =
 		SCRAM_SHA_256_DEFAULT_ITERATIONS, 1, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"max_backend_memory", PGC_SU_BACKEND, RESOURCES_MEM,
+			gettext_noop("Restrict total backend memory allocations to this max (rounded up to the nearest MB)."),
+			NULL,
+			GUC_UNIT_MB
+		},
+		&max_backend_memory_size_mb,
+		0, 0, INT_MAX,
+		NULL, NULL, NULL
+	},
 
 	/* End-of-list marker */
 	{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index a2ac7575ca..1854601ca9 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -171,6 +171,7 @@
 #serializable_buffers = 32		# memory for pg_serial
 #subtransaction_buffers = 0		# memory for pg_subtrans (0 = auto)
 #transaction_buffers = 0		# memory for pg_xact (0 = auto)
+#max_backend_memory = 0			# limit amount of per-backend allocated memory in megabytes
 
 # - Disk -
 
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index dede30dd86..e304da7c3c 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -47,6 +47,7 @@
 #include "postgres.h"
 
 #include "port/pg_bitutils.h"
+#include "utils/backend_status.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
 #include "utils/memutils_internal.h"
@@ -441,7 +442,7 @@ AllocSetContextCreateInternal(MemoryContext parent,
 	 * Allocate the initial block.  Unlike other aset.c blocks, it starts with
 	 * the context header and its block header follows that.
 	 */
-	set = (AllocSet) malloc(firstBlockSize);
+	set = (AllocSet) malloc_and_count(firstBlockSize);
 	if (set == NULL)
 	{
 		if (TopMemoryContext)
@@ -579,13 +580,16 @@ AllocSetReset(MemoryContext context)
 		}
 		else
 		{
+			Size	deallocation_size;
+
 			/* Normal case, release the block */
-			context->mem_allocated -= block->endptr - ((char *) block);
+			deallocation_size = block->endptr - ((char *) block);
+			context->mem_allocated -= deallocation_size;
 
 #ifdef CLOBBER_FREED_MEMORY
 			wipe_mem(block, block->freeptr - ((char *) block));
 #endif
-			free(block);
+			free_and_count(block, deallocation_size);
 		}
 		block = next;
 	}
@@ -649,7 +653,7 @@ AllocSetDelete(MemoryContext context)
 				freelist->num_free--;
 
 				/* All that remains is to free the header/initial block */
-				free(oldset);
+				free_and_count(oldset, oldset->header.mem_allocated);
 			}
 			Assert(freelist->num_free == 0);
 		}
@@ -666,16 +670,20 @@ AllocSetDelete(MemoryContext context)
 	while (block != NULL)
 	{
 		AllocBlock	next = block->next;
+		Size		deallocation_size = 0;
 
 		if (!IsKeeperBlock(set, block))
-			context->mem_allocated -= block->endptr - ((char *) block);
+		{
+			deallocation_size = block->endptr - ((char *) block);
+			context->mem_allocated -= deallocation_size;
+		}

 		if (!IsKeeperBlock(set, block))
-			free(block);
+			free_and_count(block, deallocation_size);
 
 		block = next;
 	}
@@ -683,7 +691,7 @@ AllocSetDelete(MemoryContext context)
 	Assert(context->mem_allocated == keepersize);
 
 	/* Finally, free the context header, including the keeper block */
-	free(set);
+	free_and_count(set, keepersize);
 }
 
 /*
@@ -712,7 +720,7 @@ AllocSetAllocLarge(MemoryContext context, Size size, int flags)
 #endif
 
 	blksize = chunk_size + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
-	block = (AllocBlock) malloc(blksize);
+	block = (AllocBlock) malloc_and_count(blksize);
 	if (block == NULL)
 		return MemoryContextAllocationFailure(context, size, flags);
 
@@ -905,7 +913,7 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
 		blksize <<= 1;
 
 	/* Try to allocate it */
-	block = (AllocBlock) malloc(blksize);
+	block = (AllocBlock) malloc_and_count(blksize);
 
 	/*
 	 * We could be asking for pretty big blocks here, so cope if malloc fails.
@@ -916,7 +924,7 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
 		blksize >>= 1;
 		if (blksize < required_size)
 			break;
-		block = (AllocBlock) malloc(blksize);
+		block = (AllocBlock) malloc_and_count(blksize);
 	}
 
 	if (block == NULL)
@@ -1071,6 +1079,7 @@ AllocSetFree(void *pointer)
 	{
 		/* Release single-chunk block. */
 		AllocBlock	block = ExternalChunkGetBlock(chunk);
+		Size		deallocation_size;
 
 		/*
 		 * Try to verify that we have a sane block pointer: the block header
@@ -1099,12 +1108,13 @@ AllocSetFree(void *pointer)
 		if (block->next)
 			block->next->prev = block->prev;
 
-		set->header.mem_allocated -= block->endptr - ((char *) block);
+		deallocation_size = block->endptr - ((char *) block);
+		set->header.mem_allocated -= deallocation_size;
 
 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, block->freeptr - ((char *) block));
 #endif
-		free(block);
+		free_and_count(block, deallocation_size);
 	}
 	else
 	{
@@ -1223,7 +1233,7 @@ AllocSetRealloc(void *pointer, Size size, int flags)
 		blksize = chksize + ALLOC_BLOCKHDRSZ + ALLOC_CHUNKHDRSZ;
 		oldblksize = block->endptr - ((char *) block);
 
-		block = (AllocBlock) realloc(block, blksize);
+		block = (AllocBlock) realloc_and_count(block, blksize, oldblksize);
 		if (block == NULL)
 		{
 			/* Disallow access to the chunk header. */
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 0238c111d2..c7ec138110 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -37,6 +37,7 @@
 
 #include "lib/ilist.h"
 #include "port/pg_bitutils.h"
+#include "utils/backend_status.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
 #include "utils/memutils_internal.h"
@@ -206,7 +207,7 @@ GenerationContextCreate(MemoryContext parent,
 	 * Allocate the initial block.  Unlike other generation.c blocks, it
 	 * starts with the context header and its block header follows that.
 	 */
-	set = (GenerationContext *) malloc(allocSize);
+	set = (GenerationContext *) malloc_and_count(allocSize);
 	if (set == NULL)
 	{
 		MemoryContextStats(TopMemoryContext);
@@ -330,7 +331,7 @@ GenerationDelete(MemoryContext context)
 	/* Reset to release all releasable GenerationBlocks */
 	GenerationReset(context);
 	/* And free the context header and keeper block */
-	free(context);
+	free_and_count(context, context->mem_allocated + MAXALIGN(sizeof(GenerationContext)));
 }
 
 /*
@@ -361,7 +362,7 @@ GenerationAllocLarge(MemoryContext context, Size size, int flags)
 	required_size = chunk_size + Generation_CHUNKHDRSZ;
 	blksize = required_size + Generation_BLOCKHDRSZ;
 
-	block = (GenerationBlock *) malloc(blksize);
+	block = (GenerationBlock *) malloc_and_count(blksize);
 	if (block == NULL)
 		return MemoryContextAllocationFailure(context, size, flags);
 
@@ -482,7 +483,7 @@ GenerationAllocFromNewBlock(MemoryContext context, Size size, int flags,
 	if (blksize < required_size)
 		blksize = pg_nextpower2_size_t(required_size);
 
-	block = (GenerationBlock *) malloc(blksize);
+	block = (GenerationBlock *) malloc_and_count(blksize);
 
 	if (block == NULL)
 		return MemoryContextAllocationFailure(context, size, flags);
@@ -663,6 +664,9 @@ GenerationBlockFreeBytes(GenerationBlock *block)
 static inline void
 GenerationBlockFree(GenerationContext *set, GenerationBlock *block)
 {
+	/* Have to store the value locally, block will be wiped. */
+	Size	blksize = block->blksize;
+
 	/* Make sure nobody tries to free the keeper block */
 	Assert(!IsKeeperBlock(set, block));
 	/* We shouldn't be freeing the freeblock either */
@@ -671,13 +675,13 @@ GenerationBlockFree(GenerationContext *set, GenerationBlock *block)
 	/* release the block from the list of blocks */
 	dlist_delete(&block->node);
 
-	((MemoryContext) set)->mem_allocated -= block->blksize;
+	((MemoryContext) set)->mem_allocated -= blksize;
 
 #ifdef CLOBBER_FREED_MEMORY
-	wipe_mem(block, block->blksize);
+	wipe_mem(block, blksize);
 #endif
 
-	free(block);
+	free_and_count(block, blksize);
 }
 
 /*
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index 3e15d59683..b8baea6c98 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -69,6 +69,7 @@
 #include "postgres.h"
 
 #include "lib/ilist.h"
@@ -361,7 +362,7 @@ SlabContextCreate(MemoryContext parent,

-	slab = (SlabContext *) malloc(Slab_CONTEXT_HDRSZ(chunksPerBlock));
+	slab = (SlabContext *) malloc_and_count(Slab_CONTEXT_HDRSZ(chunksPerBlock));
 	if (slab == NULL)
 	{
 		MemoryContextStats(TopMemoryContext);
@@ -451,7 +452,7 @@ SlabReset(MemoryContext context)
 #ifdef CLOBBER_FREED_MEMORY
 		wipe_mem(block, slab->blockSize);
 #endif
-		free(block);
+		free_and_count(block, slab->blockSize);
 		context->mem_allocated -= slab->blockSize;
 	}
 
@@ -467,7 +468,7 @@ SlabReset(MemoryContext context)
 #ifdef CLOBBER_FREED_MEMORY
 			wipe_mem(block, slab->blockSize);
 #endif
-			free(block);
+			free_and_count(block, slab->blockSize);
 			context->mem_allocated -= slab->blockSize;
 		}
 	}
@@ -484,10 +485,14 @@ SlabReset(MemoryContext context)
 void
 SlabDelete(MemoryContext context)
 {
+#ifdef MEMORY_CONTEXT_CHECKING
+	SlabContext *slab = (SlabContext *) context;
+#endif
+
 	/* Reset to release all the SlabBlocks */
 	SlabReset(context);
 	/* And free the context header */
-	free(context);
+	free_and_count(context, Slab_CONTEXT_HDRSZ(slab->chunksPerBlock));
 }
 
 /*
@@ -562,7 +567,7 @@ SlabAllocFromNewBlock(MemoryContext context, Size size, int flags)
 	}
 	else
 	{
-		block = (SlabBlock *) malloc(slab->blockSize);
+		block = (SlabBlock *) malloc_and_count(slab->blockSize);
 
 		if (unlikely(block == NULL))
 			return MemoryContextAllocationFailure(context, size, flags);
@@ -795,7 +800,7 @@ SlabFree(void *pointer)
 #ifdef CLOBBER_FREED_MEMORY
 			wipe_mem(block, slab->blockSize);
 #endif
-			free(block);
+			free_and_count(block, slab->blockSize);
 			slab->header.mem_allocated -= slab->blockSize;
 		}
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2dcc2d42da..16dd4a0d97 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8442,6 +8442,20 @@
   proargmodes => '{o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{name, ident, type, level, path, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes}',
   prosrc => 'pg_get_backend_memory_contexts' },
+{ oid => '8085',
+  descr => 'get the allocated memory in all memory contexts of local backend',
+  proname => 'pg_get_backend_memory_contexts_total_bytes', provolatile => 'v',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_get_backend_memory_contexts_total_bytes' },
+{ oid => '8005',
+  descr => 'information about memory allocation of local backend',
+  proname => 'pg_get_backend_memory_allocation_stats', prorows => '1024',
+  proretset => 't', provolatile => 'v', proparallel => 'r',
+  prorettype => 'record', proargtypes => '',
+  proallargtypes => '{text,int8,bool}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{pointer, size, deleted}',
+  prosrc => 'pg_get_backend_memory_allocation_stats' },
 
 # logging memory contexts of the specified backend
 { oid => '4543', descr => 'log memory contexts of the specified backend',
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 4e8b39a66d..26b9338b2f 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -11,6 +11,7 @@
 #define BACKEND_STATUS_H
 
 #include "datatype/timestamp.h"
+#include "fmgr.h"
 #include "libpq/pqcomm.h"
 #include "miscadmin.h"			/* for BackendType */
 #include "storage/procnumber.h"
@@ -285,6 +286,7 @@ typedef struct LocalPgBackendStatus
  */
 extern PGDLLIMPORT bool pgstat_track_activities;
 extern PGDLLIMPORT int pgstat_track_activity_query_size;
+extern PGDLLIMPORT int max_backend_memory_size_mb;

 /* ----------
@@ -337,5 +339,13 @@ extern LocalPgBackendStatus *pgstat_get_local_beentry_by_proc_number(ProcNumber
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_index(int idx);
 extern char *pgstat_clip_activity(const char *raw_activity);
 
+/*
+ * Fuctions to count memory
+ */
+extern void *malloc_and_count(Size size);
+extern void *realloc_and_count(void *ptr, Size new_size, Size old_size);
+extern void free_and_count(void *ptr, Size size);
+extern Datum pg_get_backend_memory_contexts_total_bytes(PG_FUNCTION_ARGS);
+extern Datum pg_get_backend_memory_allocation_stats(PG_FUNCTION_ARGS);
 
 #endif							/* BACKEND_STATUS_H */
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index c0d3cf0e14..f011f29b27 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -54,6 +54,10 @@ else
 ALWAYS_SUBDIRS += ssl_passphrase_callback
 endif
 
+ifneq (test_backend_memory,$(filter test_backend_memory,$(PG_TEST_SKIP)))
+SUBDIRS += test_backend_memory
+endif
+
 # Test runs an LDAP server, so only run if ldap is in PG_TEST_EXTRA
 ifeq ($(with_ldap),yes)
 ifneq (,$(filter ldap,$(PG_TEST_EXTRA)))
diff --git a/src/test/modules/test_backend_memory/.gitignore b/src/test/modules/test_backend_memory/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/src/test/modules/test_backend_memory/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/test_backend_memory/Makefile b/src/test/modules/test_backend_memory/Makefile
new file mode 100644
index 0000000000..c6afaedd0a
--- /dev/null
+++ b/src/test/modules/test_backend_memory/Makefile
@@ -0,0 +1,14 @@
+# src/test/modules/test_backend_memory/Makefile
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_backend_memory
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_backend_memory/README b/src/test/modules/test_backend_memory/README
new file mode 100644
index 0000000000..95ffe2c438
--- /dev/null
+++ b/src/test/modules/test_backend_memory/README
@@ -0,0 +1 @@
+This directory contain tests for max_backend_memory GUC
diff --git a/src/test/modules/test_backend_memory/t/001_max_backend_memory.pl b/src/test/modules/test_backend_memory/t/001_max_backend_memory.pl
new file mode 100644
index 0000000000..c479f6ec4f
--- /dev/null
+++ b/src/test/modules/test_backend_memory/t/001_max_backend_memory.pl
@@ -0,0 +1,85 @@
+# Tests to check backend memory limit (max_backend_memory GUC)
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+# Initialize backend memory limit
+$node->safe_psql('postgres', qq{
+	ALTER SYSTEM SET max_backend_memory = 15;
+	SELECT pg_reload_conf();
+});
+
+# Check backend memory limit after SET.
+my $psql_stdout;
+$psql_stdout = $node->safe_psql('postgres',
+								"SELECT current_setting('max_backend_memory');");
+is($psql_stdout, '15MB', "max_backend_memory is SET correctly");
+
+# Create test table and function.
+$node->safe_psql('postgres', q{
+	CREATE TABLE test(t text);
+	INSERT INTO test VALUES (repeat('1234567890', 400000));
+
+	-- Recursive function that should cause overflow
+	CREATE FUNCTION test_func() RETURNS void LANGUAGE plpgsql AS $$
+	DECLARE
+		bt text;
+	BEGIN
+		SELECT t || 'x' FROM test INTO bt;
+		PERFORM test_func();
+	END;
+$$;
+});
+
+# Call function "test_func" several times for memory leak check.
+my $psql_stdout_first;
+for (my $i = 0; $i < 4; $i++)
+{
+	# Call function and check that it finishes with 'out of memory' error.
+	my ($ret, $stdout, $stderr) = $node->psql('postgres',
+											  "SELECT test_func();");
+	is($ret, 3, 'recursive function call causes overflow');
+	like($stderr, qr/out of memory/, 'expected out of memory error');
+
+	$psql_stdout = $node->safe_psql('postgres',
+									"SELECT pg_get_backend_memory_contexts_total_bytes();");
+	if ($i eq 0)
+	{
+		# Store first value of backend_memory_contexts_total_bytes.
+		$psql_stdout_first = $psql_stdout;
+		is($psql_stdout_first > 0, 1,
+		   "backend has allocated $psql_stdout_first (greater than 0) bytes");
+		next;
+	}
+	# Check other values of backend_memory_contexts_total_bytes.
+	# They should be the same as first value.
+	is($psql_stdout, $psql_stdout_first, "memory does not leak");
+}
+
+# Drop test table and function.
+$node->safe_psql('postgres', q{
+	DROP FUNCTION test_func();
+	DROP TABLE test;
+});
+
+# Deinitialize backend memory limit.
+$node->safe_psql('postgres', q{
+	ALTER SYSTEM RESET max_backend_memory;
+	SELECT pg_reload_conf();
+});
+
+# Check backend memory limit after RESET.
+$psql_stdout = $node->safe_psql('postgres',
+								"SELECT current_setting('max_backend_memory');");
+is($psql_stdout, '0', "max_backend_memory is RESET correctly");
+
+$node->stop;
+
+done_testing();
-- 
2.47.1

---

From: Tomas Vondra <[email-masked]>
Date: Sat, 28 Dec 2024 15:57:44 +0100

On 12/28/24 13:36, Anton A. Melnikov wrote:
> Also the patch introduces the
> pg_get_backend_memory_contexts_total_bytes() function,

Not sure a simple memory limit like in the patch (which just adds memory
accounting + OOM after hitting the limit) can work as anything but a the
last safety measure. It seems to me the limit would have to be set to a
value that's much higher than any backend would realistically need.

The first challenge I envision is that without any feedback (either to
the planner or executor), it may break queries quite easily. It just
takes the planner to add one more sort / hash join / materialize (which
it can do arbitrarily, as it has no concept of the memory limit), and
now the query can't run.

And secondly, there are allocations that we don't restrict by work_mem,
but this memory limit would include them, ofc. The main example that I
can think of is hash join, where we (currently) don't account for the
BufFile arrays, and that can already lead to annoying OOM issues, see
e.g. [1] [2] and [3] (I'm sure there are more threads about the issue).

It's wrong we don't account for the BufFile arrays, so it's not included
in work_mem (or considered is some other way). And maybe we should
finally improve that (not the fault of this patch, ofc). But it's hard,
because as the amount of data grows, we have to add more batches - and
at some point that starts adding more memory than we save. Ultimately,
we end up breaking work_mem one way or the other - either we add more
batches, or allow the hash table to exceed work_mem.

That's a preexisting issue, of course. But wouldn't this simple limit
make the situation worse? The query would likely complete OK (otherwise
we'd get many more reports about OOM), but with the new limit it would
probably fail with OOM. Maybe that's correct, and the response to that
is "Don't set the limit with such queries," although it's hard to say in
advance and it can happen randomly? Not sure.

What bothers me a bit is that users would likely try to reduce work_mem,
but that's the wrong thing to do in this case - it just increases the
number of batches, and thus makes the situation worse.

[1]
https://www.postgresql.org/message-id/20190504003414.bulcbnge3rhwhcsh%40development

[2] https://www.postgresql.org/message-id/20230228190643.1e368315%40karst

[3]
https://www.postgresql.org/message-id/bc138e9f-c89e-9147-5395-61d51a757b3b%40gusw.net

> What do you think about such approach?

It's not clear to me how you want to calculate the number of parallel
operations that might use work_mem. Can you elaborate?

regards

-- 
Tomas Vondra

---

From: Jeremy Schneider <[email-masked]>
Date: Sat, 28 Dec 2024 10:26:45 -0800

On Sat, 28 Dec 2024 15:57:44 +0100
Tomas Vondra <[email-masked]> wrote:
> 
> Not sure a simple memory limit like in the patch (which just adds
> memory accounting + OOM after hitting the limit) can work as anything
> but a the last safety measure. It seems to me the limit would have to
> be set to a value that's much higher than any backend would
> realistically need.
> 
> takes the planner to add one more sort / hash join / materialize
> (which it can do arbitrarily, as it has no concept of the memory
> limit), and now the query can't run.
> 
> And secondly, there are allocations that we don't restrict by
> work_mem, but this memory limit would include them, ofc. The main
> example that I can think of is hash join, where we (currently) don't
> account for the BufFile arrays, and that can already lead to annoying
> OOM issues, see e.g. [1] [2] and [3] (I'm sure there are more threads
> about the issue).

Hi James!

While I don't have a detailed design in mind, I'd like to add a strong
+1 on the general idea that work_mem is hard to effectively use because
queries can vary so widely in how many nodes might need work memory.

I'd almost like to have two limits:

First, a hard per-connection limit which could be set very high - we
can track total memory usage across contexts inside of palloc and pfree
(and maybe this could also be exposed in pg_stat_activity for easy
visibility into a snapshot of memory use across all backends). If
palloc detects that an allocation would take the total over the hard
limit, then you just fail the palloc with an OOM. This protects
postgres from a memory leak in a single backend OOM'ing the whole
system and restarting the whole database; failing a single connection
is better than failing all of them.

Second, a soft per-connection "total_connection_work_mem_target" which
could be set lower. The planner can just look at the total number of
nodes that it expects to allocate work memory, divide the target by
this and then set the work_mem for that query.  There should be a
reasonable floor (minimum) for work_mem - maybe the value of work_mem
itself becomes this and the new target doesn't do anything besides
increasing runtime work_mem.

Maybe even could do a "total_instance_work_mem_target" where it's
divided by the number of average active connections or something.

In practice this target idea doesn't guarantee anything about total work
memory used, but it's the tool I'd most like as an admin. And the
per-connection hard limit is the tool I'd like to have for protecting
myself against memory leaks or individual connections going bonkers and
killing all my connections for an instance restart.

-Jeremy

---

From: Jim Nasby <[email-masked]>
Date: Sun, 29 Dec 2024 01:24:47 -0600

> divided by the number of average active connections or something.

IMHO none of this will be very sane until we actually have cluster-level limits. One sudden burst in active connections and you still OOM the instance. And while we could have such a mechanism do something clever like dynamically lowering every sessions query_mem/work_mem/whatever, ultimately I think it would also need the ability to deny or delay sessions from starting new transactions.

---

From: David Rowley <[email-masked]>
Date: Tue, 31 Dec 2024 12:12:23 +1300

> just does its best to predict what will happen at runtime.)

While I do want to see improvements in this area, I think "don't care
about the optimizer" is going to cause performance issues.  The
problem is that the optimizer takes into account what work_mem is set
to when calculating the costs of work_mem-consuming node types. See
costsize.c for usages of "work_mem". If you go and reduce the amount
of memory a given node can consume after the costs have been applied
then we may end up in a situation where some other plan would have
suited much better.

There's also the problem with what to do when you chop work_mem down
so far that the remaining size is just a pitiful chunk. For now,
work_mem can't go below 64 kilobytes. You might think that's a very
unlikely situation that it'd be chopped down so far, but with
partition-wise join and partition-wise aggregate, we could end up
using a work_mem per partition and if you have thousands of partitions
then you might end up reducing work_mem by quite a large amount.

I think the best solution to this is the memory grant stuff I talked
about in [1]. That does require figuring out which nodes will consume
the work_mem concurrently so that infrastructure you talked about to
do that would be a good step forward towards that, but that's probably
not the most difficult part of that idea.

I definitely encourage work in this area, but I think what you're
proposing might be swapping one problem for another problem.

David

[1] https://www.postgresql.org/message-id/[email-masked]

---

From: James Hunter <[email-masked]>
Date: Mon, 30 Dec 2024 16:51:11 -0800

On Sat, Dec 28, 2024 at 6:57 AM Tomas Vondra <[email-masked]> wrote:
> > ... In more details let me suggest

Yes this seems like a reasonable plan. I would add that (2) might be
good enough to solve (1), without needing to emit an "out of memory"
error.

> last safety measure.

I agree -- since PG already has the ability to spill operators, I
think we can avoid using too much "working memory" without too much
coding effort.

Now, PG uses memory for things other than what's captured in "working
memory," so there is probably still some value in a (1) that kills the
query or backend.

> it can do arbitrarily, as it has no concept of the memory limit), and

Yes -- the planner figures it can allocate another work_mem [*
hash_mem_multiplier] for any new sort / hash join / materialize. It
doesn't know about per-backend memory limits.

That's why (2) is so nice, because it tells the executor (but, to keep
things simple, not the planner) how much working memory it *actually*
has available. And then the executor will (try to) spill rather than
exceed that actual limit.

> but this memory limit would include them, ofc.

I'm neutral on this subject. The example you gave, of BufFile arrays,
should be included in the working-memory calculation, but there will
always be other sources of memory unaccounted for... I'm neutral on
how to deal with these cases...

> probably fail with OOM.

If I understand your point, you're saying that killing a query due to
OOM is OK if the "OOM" is based on the amount of available *global*
memory going to 0, but you're not comfortable doing this when
*per-backend* memory goes to 0.

I think you're right. A per-backend limit is nice because it can be
distributed to individual operators as per-operator working memory,
because operators will just spill. But killing a query based on an
approximation of how much memory we have seems like an overreaction.

Thanks,
James

---

From: James Hunter <[email-masked]>
Date: Mon, 30 Dec 2024 17:03:35 -0800

On Sat, Dec 28, 2024 at 10:26 AM Jeremy Schneider
<[email-masked]> wrote:

Thanks for the feedback, Jeremy!

> queries can vary so widely in how many nodes might need work memory.

That's my main concern with work_mem. Even if I am running a single
query at a time, on a single connection, I still run into the
limitations of the work_mem GUC if I try to run:

(a) a query with a single large Hash Join; vs.
(b) a query with, say, 10 Hash Joins, some of which are small and
others of which are large.

In (a), I want to set work_mem to the total amount of memory available
on the system. In (b), I *don't* want to set it to "(a) / 10", because
I want some Hash Joins to get more memory than others.

It's just really hard to distribute working memory intelligently,
using this GUC!

> First, a hard per-connection limit which could be set very high

> ...  If

Yes, this seems fine to me. Per Tomas's comments in [1] ,the "OOM"
limit would probably have to be global.

> this and then set the work_mem for that query.

I wouldn't even bother to inform the planner of the "actual" working
memory, because that adds complexity to the query compilation process.

Just: planner assumes it will have "work_mem" per node, but records
how much memory it estimates it will use ("nbytes") [2]. The executor
then sets the "actual" work_mem, for each node, based on nbytes and
the global "work_mem" GUC.

(It's hard for the planner to decide how much memory each path / node
should get, while building and costing plans. E.g., is it better to
take X MB of working memory from join 1 and hand it to join 2, which
would cause join 1 to spill Y% more data, but join 2 to spill Z%
less?)

> divided by the number of average active connections or something.

This seems fine to me, but I prefer "backend_work_mem_target", because
I find multiplying easier than dividing. (E.g., if I add 2 more
backends, I would use up to "backend_work_mem_target" * 2 more memory.
Vs., each query now gets "total_instance_work_mem_target / (N+2)"
memory...)

[1] https://www.postgresql.org/message-id/4806d917-c019-49c7-9182-1203129cd295%40vondra.me
[2] https://www.postgresql.org/message-id/CAJVSvF6i_1Em6VPZ9po5wyTubGwifvfNFLrOYrdgT-e1GmR5Fw%40mail.gmail.com

Thanks,
James

---

From: James Hunter <[email-masked]>
Date: Mon, 30 Dec 2024 17:05:47 -0800

On Sat, Dec 28, 2024 at 11:24 PM Jim Nasby <[email-masked]> wrote:
>
> IMHO none of this will be very sane until we actually have cluster-level limits. One sudden burst in active connections and you still OOM the instance.

Fwiw, PG does support "max_connections" GUC, so a backend/connection -
level limit, times "max_connections", yields a cluster-level limit.

Now, which is easier for customers to understand -- that's up for debate!

James

---

From: James Hunter <[email-masked]>
Date: Mon, 30 Dec 2024 17:17:58 -0800

> then we may end up in a situation where some other plan would have

Yes, but this sort of thing is unavoidable in SQL compilers. We can
always generate and cost more paths, but that increases compile time,
which eats away at runtime.

I agree that it's good to mitigate these sorts of cases, but I see
that as a refinement on the original idea.

> then you might end up reducing work_mem by quite a large amount.

I look at this from the opposite direction: if I am doing a
partition-wise join or aggregate, and that join or aggregate requires
more memory than I feel comfortable letting it use, then the current
PG behavior doesn't solve this problem, so much as throw up its hands
and hope for the best. Because if I don't have 64 KB * (# joins) * (#
join partitions), today, then either my query is going to run OOM --
or I am lying, and I really have more memory than I claim. :)

> about in [1].

I am absolutely in favor of memory grants. Long-term, I think memory
grants, like what you described in [1], are absolutely necessary.
However, per-backend limits are:
(a) easier;
(b) a good intermediate step; and
(c) still necessary in a world in which we have memory grants, to
prevent deadlocks.

In [1], you suggested using PG's deadlock detector, but this has the
side effect of killing queries, which I would like to avoid.

Even with memory grants, if you give each backend a minimal amount of
reserved working-memory, you can avoid deadlock, by making the
"offending" spill and run slowly, rather than getting canceled.

> That does require figuring out which nodes will consume

Yeah - for example, specifying work_mem per node, on the PlanState,
gives the executor some options, if it can't checkout as much memory
as it would like.

> [1] https://www.postgresql.org/message-id/[email-masked]

Thanks for the feedback!
James

---

From: Jim Nasby <[email-masked]>
Date: Tue, 31 Dec 2024 14:46:58 -0600

> level limit, times "max_connections", yields a cluster-level limit.

max_connections is useless here, for two reasons:

1. Changing it requires a restart. That’s at *best* a real PITA in production. [1]
2. It still doesn’t solve the actual problem. Unless your workload *and* your data are extremely homogeneous you can’t simply limit the number of connections and call it a day. A slight change in incoming queries, OR in the data that the queries are looking at and you go from running fine to meltdown. You don’t even need a plan flip for this to happen, just the same plan run at the same rate but now accessing more data than before.

Most of what I’ve seen on this thread is discussing ways to *optimize* how much memory the set of running backends can consume. Adjusting how you slice the memory pie across backends, or even within a single backend, is optimization. While that’s a great goal that I do support, it will never fully fix the problem. At some point you need to either throw your hands in the air and start tossing memory errors, because you don’t have control over how much work is being thrown at the engine. The only way that the engine can exert control over that would be to hold new transactions from starting when the system is under duress (ie, workload management). While workload managers can be quite sophisticated (aka, complex), the nice thing about limiting this scope to work_mem, and only as a means to prevent complete overload, is that the problem becomes a lot simpler since you’re only looking at one metric and not trying to support any kind of priority system. The only fanciness I think an MVP would need is a GUC to control how long a transaction can sit waiting before it throws an error. Frankly, that sounds a lot less complex and much easier for DBAs to adjust than trying to teach the planner how to apportion out per-node work_mem limits.

As I said, I’m not opposed to optimizations, I just think they’re very much cart-before-the-horse.

1: While it’d be a lot of work to make max_connections dynamic one thing we could do fairly easily would be to introduce another GUC (max_backends?) that actually controls the total number of allowed backends for everything. The sum of max_backends + autovac workers + background workers + whatever else I’m forgetting would have to be less than that. The idea here is that you’d normally run with max_connections set significantly lower than max_backends. That means that if you need to adjust any of these GUCs (other than max_backends) you don’t need to restart - the new limits would just apply to new connection requests.

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 1 Jan 2025 00:41:05 +0100

> 2. It still doesn’t solve the actual problem. Unless your workload *and* your data are extremely homogeneous you can’t simply limit the number of connections and call it a day. A slight change in i…

I really don't follow your argument ...

Yes, changing max_connections requires a restart - so what? AFAIK the
point James was making is that if you multiply max_connections by the
per-backend limit, you get a cluster-wide limit. And presumably the
per-backend limit would be a GUC not requiring a restart.

Yes, high values of max_connections are problematic. I don't see how a
global limit would fundamentally change that. In fact, it could
introduce yet more weird failures because some unrelated backend did
something weird.

FWIW I'm not opposed to having some global memory limit, but as I
explained earlier, I don't see a way to do that sensibly without having
a per-backend limit first. Because if you have a global limit, a single
backend consuming memory could cause all kinds of weird failures in
random other backends.

> As I said, I’m not opposed to optimizations, I just think they’re very much cart-before-the-horse.

What optimization? I didn't notice anything like that. I don't see how
"adjusting how you slice the memory pie across backends" counts as an
optimization. I mean, that's exactly what a memory limit is meant to do.

Similarly, there was a proposal to do planning with work_mem, and then
go back and adjust the per-node limits to impose a global limit. That
does not seem like an optimization either ... (more an opposite of it).

> 1: While it’d be a lot of work to make max_connections dynamic one thing we could do fairly easily would be to introduce another GUC (max_backends?) that actually controls the total number of allow…

I don't quite understad how max_backends helps with anything except
allowing to change the limit of connections without a restart, or why
would it be needed for introducing a memory limit. To me those seem very
much like two separate features.

regards

-- 
Tomas Vondra

---

From: Jim Nasby <[email-masked]>
Date: Thu, 2 Jan 2025 15:09:04 -0600

> On Dec 31, 2024, at 5:41 PM, Tomas Vondra <[email-masked]> wrote:
> 

That’s basically my argument for having workload management. If a system becomes loaded enough for the global limit to start kicking in it’s likely that query response time is increasing, which means you will soon have more and more active backends trying to run queries. That’s just going to make the situation even worse. You’d either have to start trying to “take memory away” from already running backends or backends that are just starting would have such a low limit as to cause them to spill very quickly, creating further load on the system.

> backend consuming memory could cause all kinds of weird failures in

I agree, but I’m also not sure how much a per-backend limit would actually help on its own, especially in OLTP environments.

> does not seem like an optimization either ... (more an opposite of it).

It’s optimization in that you’re trying to increase how many active backends you can have before getting memory errors. It’s an alternative to throwing more memory at the problem or limiting the rate of incoming workload.

> much like two separate features.

It’s related to this because the number of active backends is directly related to memory consumption. Yet because max_connections requires a restart it’s very hard to actually manage how many active backends you have. Your only option is a single-point connection pool, but that introduces its own problems.

That said, I do think a workload manager would be more effective than trying to limit total connections.

---

From: Tomas Vondra <[email-masked]>
Date: Fri, 3 Jan 2025 04:21:12 +0100

On 1/2/25 22:09, Jim Nasby wrote:
>>>>> IMHO none of this will be very sane until we actually have cluster-
>>>>> level limits. One sudden burst in active connections and you still
>>>>> OOM the instance.
>>>>
>>> 2. It still doesn’t solve the actual problem. Unless your workload
>>> *and* your data are extremely homogeneous you can’t simply limit the
>>> number of connections and call it a day. A slight change in incoming
>>> queries, OR in the data that the queries are looking at and you go
>>> from running fine to meltdown. You don’t even need a plan flip for
>>> this to happen, just the same plan run at the same rate but now
>>> accessing more data than before.
>>>
>>
> That’s basically my argument for having workload management. If a system
> becomes loaded enough for the global limit to start kicking in it’s
> likely that query response time is increasing, which means you will soon
> have more and more active backends trying to run queries. That’s just
> going to make the situation even worse. You’d either have to start
> trying to “take memory away” from already running backends or backends
> that are just starting would have such a low limit as to cause them to
> spill very quickly, creating further load on the system.
> 

I'm not opposed to having a some sort of "workload management" (similar
to what's available in some databases), but my guess is that's (at
least) an order of magnitude more complex than introducing the memory
limit discussed here. I can only guess, because no one really explained
what would it include, how would it be implemented. Which makes it easy
to dream about a solution that would fix the problem ...

What I'm afraid will happen everyone mostly agrees a comprehensive
workload management system would be better than a memory limit (be it
per-backend or a global one). But without a workable proposal how to
implement it no one ends up working on it. And no one gets to work on a
memory limit because the imagined workload management would be better.
So we get nothing ...

FWIW I have a hard time imagining a workload management system without
some sort of a memory limit.

> I agree, but I’m also not sure how much a per-backend limit would
> actually help on its own, especially in OLTP environments.
> 

So what if it doesn't help in every possible case? It'd be valuable even
for OLAP/mixed workloads with sensible max_connection values, because
it'd allow setting the work_mem more aggressively.

>>> Most of what I’ve seen on this thread is discussing ways to
>>> *optimize* how much memory the set of running backends can consume.
>>> Adjusting how you slice the memory pie across backends, or even
>>> within a single backend, is optimization. While that’s a great goal
>>> that I do support, it will never fully fix the problem. At some point
>>> you need to either throw your hands in the air and start tossing
>>> memory errors, because you don’t have control over how much work is
>>> being thrown at the engine. The only way that the engine can exert
>>> control over that would be to hold new transactions from starting
>>> when the system is under duress (ie, workload management). While
>>> workload managers can be quite sophisticated (aka, complex), the nice
>>> thing about limiting this scope to work_mem, and only as a means to
>>> prevent complete overload, is that the problem becomes a lot simpler
>>> since you’re only looking at one metric and not trying to support any
>>> kind of priority system. The only fanciness I think an MVP would need
>>> is a GUC to control how long a transaction can sit waiting before it
>>> throws an error. Frankly, that sounds a lot less complex and much
>>> easier for DBAs to adjust than trying to teach the planner how to
>>> apportion out per-node work_mem limits.
>>>
> It’s optimization in that you’re trying to increase how many active
> backends you can have before getting memory errors. It’s an alternative
> to throwing more memory at the problem or limiting the rate of incoming
> workload.
> 

I don't see it that way. I see it as a "safety measure" for queries that
happen to be more complex than expected, or something. Yes, it may allow
you to use a higher max_connections, or higher work_mem. But it's still
just a safety measure.

>>> 1: While it’d be a lot of work to make max_connections dynamic one
>>> thing we could do fairly easily would be to introduce another GUC
>>> (max_backends?) that actually controls the total number of allowed
>>> backends for everything. The sum of max_backends + autovac workers +
>>> background workers + whatever else I’m forgetting would have to be
>>> less than that. The idea here is that you’d normally run with
>>> max_connections set significantly lower than max_backends. That means
>>> that if you need to adjust any of these GUCs (other than
>>> max_backends) you don’t need to restart - the new limits would just
>>> apply to new connection requests.
>>
> It’s related to this because the number of active backends is directly
> related to memory consumption. Yet because max_connections requires a
> restart it’s very hard to actually manage how many active backends you
> have. Your only option is a single-point connection pool, but that
> introduces its own problems.
> 

I think you need to choose. Either problems with many active backends,
or problems with connection pools ...

> trying to limit total connections.

The "workload management" concept is so abstract I find it very
difficult to discuss without much more detail about how would it
actually work / be implemented.

I believe implementing some rudimentary "global" memory accounting would
not be *that* hard (possibly along the lines of the patches early in
this thread), and adding some sort of dynamic connection limit would not
be much harder I think. But then comes the hard part of actually doing
the "workload management" part, which seems pretty comparable to what a
QoS / scheduler needs to do. With all the weird corner cases.

regards

-- 
Tomas Vondra

---

From: James Hunter <[email-masked]>
Date: Fri, 3 Jan 2025 12:15:07 -0800

I think this discussion is getting away from a manageable scope of work...

> That’s basically my argument for having workload management. If a system becomes loaded enough for the global limit to start kicking in it’s likely that query response time is increasing, which mea…

I think this is backward. There's a fixed number of backends, limited
by "max_connections." A given backend becoming slow to respond doesn't
increase this limit. The load on the system is limited by
"max_connections." This makes the system stable.

If, instead, the system starts cancelling queries, then a naive client
will typically just retry the query. If the reason the query was
cancelled is that the system is overloaded, then the query is likely
to be cancelled again, leading to a retry storm, even though the
number of backends is limited by "max_connections," since a given
backend will be repeatedly cancelled.

Long-term, the solution to, "I don't have enough resources to run my
workload efficiency," is either:

* Buy more resources; or
* Reduce your workload.

We can't help with either of those solutions!

However, the (short-term) problem I'd like to solve is: how do we
expose efficient use of resources, in those cases where we *do* have
enough resources to run a workload efficiently, but the existing
"work_mem" and "hash_mem_multiplier" GUCs are insufficient?

Thanks,
James

---

From: James Hunter <[email-masked]>
Date: Fri, 3 Jan 2025 13:16:45 -0800

> to dream about a solution that would fix the problem ...

I agree -- anyway, I will try to start a new thread sometime next
week, with a more concrete proposal. (I wanted to get general feedback
first, and I got a lot of it -- thanks!)

> memory limit because the imagined workload management would be better.

That seems to be where this thread is heading...

> some sort of a memory limit.

Yes, and more strongly; you can't create a system to manage resources,
unless you first have some way of managing those resources. Today, I
can't say: query X gets 50 MB of RAM, while query Y gets 200 MB of
RAM, even if I wanted to -- at least, not in any useful way that
doesn't involve waiting for the query to exceed its (hidden!) memory
limit, and then killing it.

Before we can discuss how much memory queries X and Y should get, and
whether X can steal memory for Y, etc. -- we need a way to force X and
Y to respect the memory limits we impose! Otherwise, we always end up
back at: I have a secret memory limit for X, and if X exceeds that
memory limit, I'll kill X. I think there's been some rationalization
that "just kill X" is a reasonable response, but I think it really
isn't. Any workload management system whose only available tool is
killing queries is going to be incredibly sensitive / unstable.

It would be better if, when someone -- either workload management, or
a GUC -- decides that query X gets 50 MB of memory, we informed query
X of this limit, and let the query do its best to stay within it. (Yes
it makes sense to have the option to "kill -9", but there's a reason
we have other signals as well...)

The good news is, PostgreSQL operators already try to stay within
work_mem [* hash_mem_multiplier], so the problem of how to get query X
to stay within 50 MB of RAM breaks down into splitting that 50 MB into
per-operator "work_mem" limits, which is (as you point out!) at least
an order of magnitude easier than a general workload management
solution. Once we have per-operator "work_mem" limits, existing
PostgreSQL logic takes care of the rest.

Thanks,
James

---

From: Jim Nasby <[email-masked]>
Date: Mon, 6 Jan 2025 15:07:39 -0600

> QoS / scheduler needs to do. With all the weird corner cases.

I’ve been saying “workload management” for lack of a better term, but my initial suggestion upthread was to simply stop allowing new transactions to start if global work_mem consumption exceeded some threshold. That’s simplistic enough that I wouldn’t really consider it “workload management”. Maybe “deferred execution” would be a better name. The only other thing it’d need is a timeout on how long a new transaction could sit in limbo.

I agree that no matter what everything being proposed would rely on having metrics on actual work_mem consumption. That would definitely be a good feature on its own. I’m thinking adding “work_mem_bytes” and “work_mem_operations” to pg_stat_activity (where “work_mem_operations” would tell you how many different things were using work_mem in the backend.

Incidentally, something related to this that I’ve seen is backend memory consumption slowly growing over time. Unbounded growth of relcache and friends was presumably the biggest contributor. There’s an argument to be made for a view dedicated to tracking per-backend memory stats, with additional info about things contributing to idle memory consumption.

---

From: Tomas Vondra <[email-masked]>
Date: Mon, 6 Jan 2025 22:54:26 +0100

On 1/6/25 22:07, Jim Nasby wrote:
> I’ve been saying “workload management” for lack of a better term, but my
> initial suggestion upthread was to simply stop allowing new transactions
> to start if global work_mem consumption exceeded some threshold. That’s
> simplistic enough that I wouldn’t really consider it “workload
> management”. Maybe “deferred execution” would be a better name. The only
> other thing it’d need is a timeout on how long a new transaction could
> sit in limbo.
> 

How would that not be a huge DoS vector? If a low-priority task can
allocate a chunk of memory, and block/delay execution of everything
else, that seems potentially disastrous.

I believe there is a reason why workload management systems tend to be
fairly complex systems, both to implement and manage, requiring stuff
like resource pools, workload classes, etc.

> I agree that no matter what everything being proposed would rely on
> having metrics on actual work_mem consumption. That would definitely be
> a good feature on its own. I’m thinking adding “work_mem_bytes” and
> “work_mem_operations” to pg_stat_activity (where “work_mem_operations”
> would tell you how many different things were using work_mem in the backend.
> 
> Incidentally, something related to this that I’ve seen is backend memory
> consumption slowly growing over time. Unbounded growth of relcache and
> friends was presumably the biggest contributor. There’s an argument to
> be made for a view dedicated to tracking per-backend memory stats, with
> additional info about things contributing to idle memory consumption.

True, but I don't quite see how would a global memory limit help with
any of that. In fact, relcache seems exactly like the thing to limit at
the backend level.

regards

-- 
Tomas Vondra

---

From: James Hunter <[email-masked]>
Date: Wed, 8 Jan 2025 15:57:39 -0800

> I’ve been saying “workload management” for lack of a better term, but my initial suggestion upthread was to simply stop allowing new transactions to start if global work_mem consumption exceeded so…

Yes, this seems like a good thing to do, but we need to handle
"work_mem" , by itself, first.

The problem is that it's just too easy for a query to blow up work_mem
consumption, almost instantaneously. By the time we notice that we're
low on working memory, pausing new transactions may not be sufficient.
We could already be in the middle of a giant Hash Join, for example,
and the hash table is just going to continue to grow...

Before we can solve the problem you describe, we need to be able to
limit the work_mem consumption by an in-progress query.

James

---

From: Tomas Vondra <[email-masked]>
Date: Sat, 27 Dec 2025 15:52:21 +0100

Hi,

Let me bump this dormant thread after about a year. I don't have any
patch to share or anything like that, but I happened to read two quite
interesting papers relevant to the topic of setting a per-query memory
limit, and distributing the memory between operators in a query plan:

1) Exploiting pipeline interruptions for efficient memory allocation
   Authors: Josep Aguilar-Saborit, Mohammad Jalali, Dave Sharpe, Victor
   Muntés MuleroAuthors Info & Claims
   Published: 2008
   PDF: https://dl.acm.org/doi/epdf/10.1145/1458082.1458169

2) Saving Private Hash Join
   Authors: Laurens Kuiper, Paul Groß, Peter Boncz, Hannes Mühleisen
   Published: 2024-2025
   PDF: https://www.vldb.org/pvldb/vol18/p2748-kuiper.pdf

I read the (2) paper first, expecting to learn some new stuff about hash
joins, but to my surprise it's focusing on how to distribute memory
between multiple hash joins in a single query. The hash joins serve
mostly as an example of an actual/common operator in a query.

The (1) paper establishes the theoretical framework and algorithms, and
(2) presents a more precise/complete model for hash joins.

I think both papers are worth reading, and the framework seems quite
helpful. Even if there some parts may need tweaks, as Postgres does
certain things differently for whatever reason.

I'm not going to explain all the details (the papers are not that long
anyway), but the proposed approach combines a couple basic parts:

1) leverage "pipeline interruption" operations

Some operators materialize the intermediate results. This splits the
plan into parts that don't need memory at the same time. It's enough to
enforce the limit for these parts, not for the whole plan. Which means
the optimization problems are smaller, and the operators can get more
memory than when assuming the whole query runs at the same time.

Of course, the reality is more complicated. Some operators are only
partial pipeline interruptions (e.g. hashjoin breaks for the inner
subplan, not the outer).

And Postgres currently delays the Hash build until the first outer
tuple, unlike DuckDB used in the (2) paper.

2) cost model for memory distribution

The memory is distributed between operators based on a simple cost
model, instead of using a simple scheme where operators get 1/N of
memory. The (2) paper presents a hash join cost model combining
"materialization cost" and "throughput".

But it's a pretty generic approach, and should not be too difficult to
do for other operators. Most operators don't even need to allocate that
much memory, so the cost model can ignore those, I think. Only nodes
that "break the pipeline" would matter.

The important part is that this is a second optimization phase. The
optimizer picks a query plan (assuming some default memory sizes), and
then the cost model determines how to distribute memory within that
particular plan.

The paper does that repeatedly during execution, but I suspect these
adjustments are easier in DuckDB. In Postgres we'd probably do that only
once right after planning? Or maybe not, not sure.

The (1) paper does things a bit differently, so that it works on DB2 (so
less dynamic), but it also focuses on plans with hash joins etc. The
general framework seems to be mostly the same.

Anyway, that's all I have. I don't expect to work on this anytime soon,
but I found those papers interesting.

regards

-- 
Tomas Vondra