Investigate Postgres Out of Memory Errors in Production

[rajadain]

On occasion, we will see very high memory usage in RDS in production, which will crash the database and require it to be rebooted (which happens automatically, but likely risks data loss)

Looking at queries executing around then, the highest pressure seems to come from Global RWD delineation. Perhaps the shapes being delineated are too large?

These are SQL snippets from the above recording:

WITH target AS ( SELECT * FROM tdxbasins WHERE ST_Intersects(geom, ST_SetSRID(ST_Point( -80.9637451171875, 42.216313604344776), 4326)) ) SELECT json_build_object( 'type', 'Feature', 'properties', '{}'::json, 'geometry', ST_AsGeoJSON(ST_Union(geom))::json ) FROM tdxbasins WHERE root_id = (SELECT root_id FROM target) AND discover_time >= (SELECT discover

WITH target AS ( SELECT * FROM tdxbasins WHERE ST_Intersects(geom, ST_SetSRID(ST_Point( -73.92193794250488, 42.10395389957932), 4326)) ) SELECT json_build_object( 'type', 'Feature', 'properties', '{}'::json, 'geometry', ST_AsGeoJSON(ST_Union(geom))::json ) FROM tdxbasins WHERE root_id = (SELECT root_id FROM target) AND discover_time >= (SELECT discover

Investigate if these queries cause high RAM usage in Postgres, and devise an appropriate mediation strategy. This could be query optimization, query limitation, or increased resource allocation to RDS.

[aufdenkampe]

@rajadain, this result makes sense for large watersheds, and is almost certainly due to dissolving the basin polygons into a custom watershed boundary.

Fortunately, @ptomasula and I anticipated this. I just created the following issue that describes our solution and its benefits.

• Optimize global watershed delineation for large watersheds #3668