Repository: ethyca/fides
Issue: Nightly build of fides maintaining a large number of open idle connections to hosted database

### Bug Description

Since August 3rd the nightly build (and to a lesser degree the rc build) of fides on our staging environment is keeping a large number of idle database connections open. It's about a 3x increase.

Occasionally this can overwhelm our database instance, making it unavailable for other staging tenants.

See additional context section below for more detail.

Confidential & internal data about the infra that I don't wish to include on a public issue can be viewed in [the associated OPS ticket](https://ethyca.atlassian.net/browse/OPS-407).

#### Steps to Reproduce

1. Deploy the nightly build of fides on a Kubernetes cluster
2. Check the number of idle connections fides keeps open to the hosted postgresql database.

### Expected behavior

The number of connections would be much lower, e.g. 1/3 of observed.

### Screenshots

n/a

### Environment

* Version: Nightly build of main - latest commit d9e3c00d28a43f748b3f9c5a5f1ce2fb08e991d4
* OS: Kubernetes, AWS EKS v1.27.3-eks-a5565ad 
* Python Version: 3.10.12
* Docker Version: containerd://1.6.19

### Additional context


**Query: how many connections are open?**
```
SELECT usename, state, count(*) AS connection_count
FROM pg_stat_activity
WHERE usename = 'fides_nightly'
GROUP BY usename, state
ORDER BY connection_count DESC;
```
```
usename      |state|connection_count|
-------------+-----+----------------+
fides_nightly|idle |              57|
```
**Query: how many connections are active?**
```sql
SELECT usename, state, query, backend_start
FROM pg_stat_activity
WHERE usename = 'fides_nightly' AND state <> 'idle'
ORDER BY backend_start DESC;
```
```
usename|state|query|backend_start|
-------+-----+-----+-------------+
```
**Query: how long have the connections been open?**
```sql
SELECT usename, state, age(now(), backend_start) AS connection_duration
FROM pg_stat_activity
WHERE usename = 'fides_nightly'
ORDER BY backend_start;
```
```
usename      |state|connection_duration|
-------------+-----+-------------------+
fides_nightly|idle |    03:01:21.410322|
fides_nightly|idle |    01:27:42.397356|
fides_nightly|idle |    01:26:45.455808|
fides_nightly|idle |    01:26:15.463816|
fides_nightly|idle |    01:25:45.460022|
fides_nightly|idle |    01:25:15.452283|
fides_nightly|idle |    01:24:45.458899|
fides_nightly|idle |    01:24:15.461488|
fides_nightly|idle |    01:23:45.460777|
fides_nightly|idle |    01:23:15.462621|
fides_nightly|idle |    01:22:52.730462|
fides_nightly|idle |    01:19:15.462048|
fides_nightly|idle |    01:15:56.910751|
fides_nightly|idle |    01:15:26.881626|
fides_nightly|idle |    01:14:56.860062|
fides_nightly|idle |    01:13:56.828506|
fides_nightly|idle |    01:12:56.781522|
fides_nightly|idle |    01:12:26.755376|
fides_nightly|idle |    01:11:56.732336|
fides_nightly|idle |    01:11:26.706441|
fides_nightly|idle |    01:10:56.692042|
fides_nightly|idle |    01:10:26.656191|
fides_nightly|idle |    01:08:56.554017|
fides_nightly|idle |    01:06:27.388653|
fides_nightly|idle |    01:00:45.462934|
fides_nightly|idle |    01:00:15.460313|
fides_nightly|idle |     00:59:45.46394|
fides_nightly|idle |    00:58:51.692505|
fides_nightly|idle |    00:57:56.092717|
fides_nightly|idle |    00:57:26.078899|
fides_nightly|idle |    00:56:56.045336|
fides_nightly|idle |    00:56:26.010928|
fides_nightly|idle |    00:55:55.995537|
fides_nightly|idle |    00:55:25.987037|
fides_nightly|idle |    00:54:55.937741|
fides_nightly|idle |    00:54:25.927437|
fides_nightly|idle |    00:53:25.895177|
fides_nightly|idle |    00:52:55.864517|
fides_nightly|idle |    00:52:25.849862|
fides_nightly|idle |    00:51:05.467596|
fides_nightly|idle |    00:50:35.458049|
fides_nightly|idle |    00:48:10.654575|
fides_nightly|idle |     00:43:55.40883|
fides_nightly|idle |    00:40:10.257298|
fides_nightly|idle |    00:37:50.700027|
fides_nightly|idle |    00:34:05.417863|
fides_nightly|idle |    00:33:05.481834|
fides_nightly|idle |    00:25:54.559949|
fides_nightly|idle |    00:18:04.771576|
fides_nightly|idle |    00:16:35.457527|
fides_nightly|idle |    00:15:35.457228|
fides_nightly|idle |    00:15:05.458018|
fides_nightly|idle |    00:14:35.462944|
fides_nightly|idle |    00:09:35.419186|
```
**Query: where are the connections being made from?**
```sql
SELECT usename, client_addr, count(*) AS connection_count
FROM pg_stat_activity
WHERE usename = 'fides_nightly'
GROUP BY usename, client_addr
ORDER BY connection_count DESC;
```
Note: I've redacted the IP address, but it's a single address.
```
usename      |client_addr|connection_count|
-------------+-----------+----------------+
fides_nightly|x.x.x.x    |              57|
```

**Query: What are non-idle connections being used for?**
```sql
SELECT usename, state, query, backend_start
FROM pg_stat_activity
WHERE usename = 'fides_nightly' AND state <> 'idle'
ORDER BY backend_start DESC;
```
```
usename|state|query|backend_start|
-------+-----+-----+-------------+
```

Changed files:
src/fides/core/utils.py

diff --git a/src/fides/core/utils.py b/src/fides/core/utils.py
index 71fe1321e..39de7736f 100644
--- a/src/fides/core/utils.py
+++ b/src/fides/core/utils.py
@@ -16,6 +16,7 @@ from sqlalchemy.exc import SQLAlchemyError
 
 from fides.common.utils import echo_red
 from fides.connectors.models import ConnectorAuthFailureException
+from functools import partial
 
 logger.bind(name="server_api")
 
@@ -39,12 +40,18 @@ def get_db_engine(connection_string: str) -> Engine:
     # Pymssql doesn't support this arg
     connect_args = {"connect_timeout": 10} if "pymssql" not in connection_string else {}
     try:
-        engine = sqlalchemy.create_engine(connection_string, connect_args=connect_args)
+        engine = sqlalchemy.create_engine(
+            connection_string,
+            connect_args=connect_args,
+            pool_pre_ping=True,
+            max_overflow=0,
+            pool_size=5,
+        )
     except Exception as err:
         raise Exception("Failed to create engine!") from err
 
     try:
-        with engine.begin() as connection:
+        with engine.connect() as connection:
             connection.execute("SELECT 1")
     except Exception as err:
         raise Exception(f"Database connection failed with engine:\\n{engine}!") from err
@@ -53,11 +60,11 @@ def get_db_engine(connection_string: str) -> Engine:
 
 def validate_db_engine(connection_string: str) -> None:
     """
-    Use SQLAlchemy to create a DB engine.
+    Use SQLAlchemy to validate a DB engine.
     """
     try:
-        engine = sqlalchemy.create_engine(connection_string)
-        with engine.begin() as connection:
+        engine = sqlalchemy.create_engine(connection_string, pool_pre_ping=True)
+        with engine.connect() as connection:
             connection.execute("SELECT 1")
     except SQLAlchemyError as error:
         raise ConnectorAuthFailureException(error)