Fides Documentation – Ethyca

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 with 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:

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