# Async Database Connection Pool Optimization Guide

This guide explains how to configure and optimize the asynchronous read-only database connection pool for high-performance scenarios. These optimizations are particularly important for production deployments expecting high traffic volumes.

## Overview

Fides uses [SQLAlchemy 1.4](https://docs.sqlalchemy.org/en/14/) with [asyncpg](https://magicstack.github.io/asyncpg/) for asynchronous database operations. The async read-only connection pool can be pre-warmed and configured to skip expensive rollback operations, resulting in significant performance improvements under load.

### Important: External Connection Pooling Strongly Recommended

⚠️ For production deployments, it is strongly recommended to use an external database connection pooling technology such as:

- [**PgBouncer**](https://www.pgbouncer.org/) - Lightweight connection pooler for PostgreSQL
- [**AWS RDS Proxy**](https://aws.amazon.com/rds/proxy/) - Managed connection pooler for AWS RDS/Aurora
- [**Azure Database for PostgreSQL built-in pooling**](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-pgbouncer)
- [**Google Cloud SQL Auth Proxy**](https://cloud.google.com/sql/docs/postgres/sql-proxy) with connection pooling
- [**Odyssey**](https://github.com/yandex/odyssey) - Advanced multi-threaded PostgreSQL connection pooler

### Why External Pooling?

PostgreSQL uses a **process-per-connection model**, where each connection spawns a separate backend process. This architecture has important implications:

1. **High Memory Overhead**: Each PostgreSQL backend process consumes significant memory (typically 10-20MB base + working memory). With hundreds of connections across multiple Fides workers, this can quickly exhaust database server memory.
2. **Connection Establishment Cost**: Creating new PostgreSQL connections involves process forking, authentication, and initialization.
3. **Scaling Challenges**: Modern cloud deployments often run multiple pods/containers/workers. If each worker maintains a pool of 300 connections, and you have 10 workers, that's 3,000 database connections - far exceeding typical PostgreSQL limits.
4. **Connection Multiplexing**: External poolers maintain a small pool of persistent database connections (e.g., 50-100) and multiplex hundreds or thousands of application connections onto them.

### Architecture with External Pooling

```
┌─────────────────┐
│  Fides Worker 1 │──┐
│  (300 conns)    │  │
└─────────────────┘  │
                     │    ┌──────────────┐
┌─────────────────┐  ├───▶│  PgBouncer   │────────▶│   PostgreSQL   │
│  Fides Worker 2 │──┤    │  (50 conns)  │         │  (50 backends) │
│  (300 conns)    │  │    └──────────────┘         └────────────────┘
└─────────────────┘  │
                     │
┌─────────────────┐  │
│  Fides Worker 3 │──┘
│  (300 conns)    │
└─────────────────┘

Total app connections: 900
Total database backends: 50 (multiplexed)  
```

### Configuration with External Pooling

When using an external connection pooler, you can safely configure larger application-side pools:

```
# Point to PgBouncer instead of directly to PostgreSQL
FIDES__DATABASE__SERVER=pgbouncer.internal.example.com
FIDES__DATABASE__PORT=6432

# Configure larger application pools (safe with external pooling)
FIDES__DATABASE__ASYNC_READONLY_DATABASE_POOL_SIZE=300
FIDES__DATABASE__ASYNC_READONLY_DATABASE_PREWARM=true
FIDES__DATABASE__ASYNC_READONLY_DATABASE_POOL_SKIP_ROLLBACK=true
FIDES__DATABASE__ASYNC_READONLY_DATABASE_AUTOCOMMIT=true
```

### Required Configuration

First, ensure you have a read-only database replica configured:

```
# Read-only database server (required for all settings below)
FIDES__DATABASE__READONLY_SERVER=readonly-db.example.com
FIDES__DATABASE__READONLY_PORT=5432
FIDES__DATABASE__READONLY_USER=fides_readonly
FIDES__DATABASE__READONLY_PASSWORD=your_password
FIDES__DATABASE__READONLY_DB=fides
```

### Recommended Performance Settings

For optimal performance under load, configure these settings together:

```
# Enable connection pool pre-warming (RECOMMENDED)
FIDES__DATABASE__ASYNC_READONLY_DATABASE_PREWARM=true

# Set pool size based on expected peak concurrent requests (start with 300)
FIDES__DATABASE__ASYNC_READONLY_DATABASE_POOL_SIZE=300

# Disable rollback on connection return (RECOMMENDED for read-only)
FIDES__DATABASE__ASYNC_READONLY_DATABASE_POOL_SKIP_ROLLBACK=true

# Enable autocommit for read-only operations (RECOMMENDED)
FIDES__DATABASE__ASYNC_READONLY_DATABASE_AUTOCOMMIT=true

# Allow overflow connections for traffic spikes
FIDES__DATABASE__ASYNC_READONLY_DATABASE_MAX_OVERFLOW=50

# Enable pre-ping to verify connection health
FIDES__DATABASE__ASYNC_READONLY_DATABASE_PRE_PING=true
```

### Example Configurations

#### High-Traffic Production (Recommended)

For applications with 500+ requests per second:

```
# Read-only replica configuration
FIDES__DATABASE__READONLY_SERVER=readonly.db.prod.internal
FIDES__DATABASE__READONLY_PORT=5432
FIDES__DATABASE__READONLY_USER=fides_readonly
FIDES__DATABASE__READONLY_PASSWORD=secure_password

# Optimized pool settings
FIDES__DATABASE__ASYNC_READONLY_DATABASE_PREWARM=true
FIDES__DATABASE__ASYNC_READONLY_DATABASE_POOL_SIZE=300
FIDES__DATABASE__ASYNC_READONLY_DATABASE_MAX_OVERFLOW=50
FIDES__DATABASE__ASYNC_READONLY_DATABASE_POOL_SKIP_ROLLBACK=true
FIDES__DATABASE__ASYNC_READONLY_DATABASE_AUTOCOMMIT=true
FIDES__DATABASE__ASYNC_READONLY_DATABASE_PRE_PING=true
```

### Performance Testing & Tuning

#### Metrics to Monitor

Monitor these metrics to validate your configuration:
1. **Connection Pool Utilization**
2. **Request Latency**
3. **Database Connections**
4. **Database CPU/Memory**

## Best Practices
1. **Always enable skip_rollback for read-only pools**
2. **Start with prewarm disabled**, enable once pool size is properly tuned
3. **Size pool based on actual load testing**, not guesswork
4. **Monitor pool utilization continuously** in production
5. **Test thoroughly**: Validate configuration under realistic load before deploying

These optimizations can reduce latency by 30-50% and significantly improve throughput under load.

### Additional Resources
- [SQLAlchemy Documentation (Version 1.4)](https://docs.sqlalchemy.org/en/14/) - Complete pooling guide.