FIMPact Building Footprint SQL Timing Out Ocassionally about hydrovis HOT 2 CLOSED

After a myriad of trial and error tests and fixes, this issue appears to have been primarily related to the ST_AREA function call in the SQL query having some type of inconsistent memory leak problem in PostGIS. At inconsistent times (but especially when running two building footprint queries concurrently, and from lambda and not pgadmin), these queries would result in a rapid expansion of RAM usage, essentially halting the RDS server and locking future queries from running. A reboot and vacuum / analyze seemed to be the only thing that would restore functionality to the database... at least until this happened again (most often when SRF and ANA pipelines would run at the same time).

Removing the ST_AREA() function calls from the query seems to have completely removed this problem, although we left two other experimental solutions in place, as they helped with database performance/headroom across the board: 1)upping the db instance size to m6g.2xlarge and increasing the shared_buffer value in the parameter group from 25% to 75% of available RAM.

from hydrovis.

part of #327

from hydrovis.