Best ways around memory Error on large st_union for overlay processing

--00000000000016acb1064870cd2d
Content-Type: text/plain; charset="UTF-8"

Hi all,

I'm working on combining a bunch of different data layers into an
ultimately flattened set of polygons within one layer, with attributes for
the various input layers as individual columns in the output layer.  I'm
following the general approach described in this blog post -
https://blog.cleverelephant.ca/2019/07/postgis-overlays.html

The datasets I'm dealing with are large and complex, so I'm using
st_dumprings to get the exterior and interior parts of polygons delineated,
and then extracting and unioning all of the exterior rings, when I get a
memory error.  In other words, when I run the following, I get the the
subsequent error

CREATE TABLE boundaries AS
  SELECT ST_Union(ST_ExteriorRing(geom)) AS geom
    FROM circles;

SQL Error [53200]: ERROR: out of memory
  Detail: Failed on request of size 70544.
  Where: parallel worker

I'm trying an approach of splitting up the polygons based on a grid, such
that I can run the above and group by a grid cell ID value... need to try
adjusting that as I just got a memory error there as well, but before I go
further just figured I'd check:
Is there an obvious or more optimal way to do that
st_union(st_exteriorring(geom)) step for large datasets?

I'm running this in AWS Aurora Serverless, with the following specs in case
that helps.
Thanks so much for any advice.

Best regards,
Mike

POSTGIS="3.5.1 0" [EXTENSION] PGSQL="140" GEOS="3.13.0-CAPI-1.19.0"
PROJ="9.5.0 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
USER_WRITABLE_DIRECTORY=/tmp/proj
DATABASE_PATH=/rdsdbbin/aurora-14.17.14.17.4.28991.0/bin/../share/postgresql/proj/proj.db"
GDAL="GDAL 3.9.3, released 2024/10/07" LIBXML="2.12.5" LIBJSON="0.15.99"
LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" (core procs from "3.3.3 0"
need upgrade) TOPOLOGY (topology procs from "3.3.3 0" need upgrade) RASTER
(raster procs from "3.3.3 0" need upgrade)

--00000000000016acb1064870cd2d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi all,</div><div><br></div><div>I&#39;m working on c=
ombining a bunch of different data layers into an ultimately flattened set =
of polygons within one layer, with attributes for the various input layers =
as individual columns in the output layer.=C2=A0 I&#39;m following the gene=
ral approach described in this blog post -=C2=A0<a href=3D"https://blog.cle=
verelephant.ca/2019/07/postgis-overlays.html">https://blog.cleverelephant.c=
a/2019/07/postgis-overlays.html</a></div><div><br></div><div>The datasets I=
&#39;m dealing with are large and complex, so I&#39;m using st_dumprings to=
 get the exterior and interior parts of polygons delineated, and then extra=
cting and unioning all of the exterior rings, when I get a memory error.=C2=
=A0 In other words, when I run the following, I get the the subsequent erro=
r</div><div><div class=3D"gmail-language-sql gmail-highlighter-rouge"><div =
class=3D"gmail-highlight"><pre class=3D"gmail-highlight"><code><span class=
=3D"gmail-k">CREATE</span> <span class=3D"gmail-k">TABLE</span> <span class=
=3D"gmail-n">boundaries</span> <span class=3D"gmail-k">AS</span>
  <span class=3D"gmail-k">SELECT</span> <span class=3D"gmail-n">ST_Union</s=
pan><span class=3D"gmail-p">(</span><span class=3D"gmail-n">ST_ExteriorRing=
</span><span class=3D"gmail-p">(</span><span class=3D"gmail-n">geom</span><=
span class=3D"gmail-p">))</span> <span class=3D"gmail-k">AS</span> <span cl=
ass=3D"gmail-n">geom</span>
    <span class=3D"gmail-k">FROM</span> <span class=3D"gmail-n">circles</sp=
an><span class=3D"gmail-p">;</span>
</code></pre></div></div>SQL Error [53200]: ERROR: out of memory<br>=C2=A0 =
Detail: Failed on request of size 70544.<br>=C2=A0 Where: parallel worker</=
div><div><br></div><div>I&#39;m trying an approach of splitting up the poly=
gons based on a grid, such that I can run the above and group by a grid cel=
l ID value... need to try adjusting that as I just got a memory error there=
 as well, but before I go further just figured I&#39;d check:</div><div>Is =
there an obvious or more optimal way to do that st_union(st_exteriorring(ge=
om)) step for large datasets?=C2=A0</div><div><br></div><div>I&#39;m runnin=
g this in AWS Aurora Serverless, with the following specs in case that help=
s.</div><div>Thanks so much for any advice.=C2=A0=C2=A0</div><div><br></div=
><div>Best regards,</div><div>Mike</div><div><br></div><div>POSTGIS=3D&quot=
;3.5.1 0&quot; [EXTENSION] PGSQL=3D&quot;140&quot; GEOS=3D&quot;3.13.0-CAPI=
-1.19.0&quot; PROJ=3D&quot;9.5.0 NETWORK_ENABLED=3DOFF URL_ENDPOINT=3D<a hr=
ef=3D"https://cdn.proj.org">https://cdn.proj.org</a> USER_WRITABLE_DIRECTOR=
Y=3D/tmp/proj DATABASE_PATH=3D/rdsdbbin/aurora-14.17.14.17.4.28991.0/bin/..=
/share/postgresql/proj/proj.db&quot; GDAL=3D&quot;GDAL 3.9.3, released 2024=
/10/07&quot; LIBXML=3D&quot;2.12.5&quot; LIBJSON=3D&quot;0.15.99&quot; LIBP=
ROTOBUF=3D&quot;1.3.0&quot; WAGYU=3D&quot;0.5.0 (Internal)&quot; (core proc=
s from &quot;3.3.3 0&quot; need upgrade) TOPOLOGY (topology procs from &quo=
t;3.3.3 0&quot; need upgrade) RASTER (raster procs from &quot;3.3.3 0&quot;=
 need upgrade)</div></div>

--00000000000016acb1064870cd2d--