Best ways around memory Error on large st_union for overlay processing

[Mike Treglia]

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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260115/16787626/attachment.htm>