Database Pooling

This document describes the database connection pool architecture, its limits, and production scaling strategies including PgBouncer configuration.

Table of Contents

1. Overview
2. Current Architecture
3. Connection Pool Fundamentals
4. Prisma Connection Pool
5. Connection Pool Sizing
6. PgBouncer Setup
7. Production Configuration
8. Monitoring and Troubleshooting

Overview

The Poker Engine uses Prisma ORM with PostgreSQL for all persistent data storage. Database connections are managed through Prisma’s built-in connection pool, which the app sizes from environment variables.

Key points:

• Default pool size: 50 connections when NODE_ENV=production, 10 otherwise
• Default pool timeout: 10 seconds
• PostgreSQL default max connections: 100. The production compose file doesn’t change this

Current Architecture

Prisma Client Setup

The database client is a singleton configured in src/config/database.ts. Before creating PrismaClient, it appends the pool settings to the connection string:

export const databasePoolConfig = {
  poolSize: env.DATABASE_POOL_SIZE ?? (env.NODE_ENV === 'production' ? 50 : 10),
  poolTimeout: env.DATABASE_POOL_TIMEOUT ?? 10,
};

Connection String

Database connection is configured via the DATABASE_URL environment variable. In the production compose file, it’s built from POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB:

DATABASE_URL="postgresql://user:password@localhost:5432/poker_engine?schema=public"

Connection Pool Fundamentals

Why Connection Pooling?

Database connections are expensive resources:

• TCP connection setup: ~1-5ms
• PostgreSQL authentication: ~1-10ms
• Connection memory overhead: ~10MB per connection on PostgreSQL

Connection pooling maintains a set of open connections that can be reused, avoiding the overhead of creating new connections for each query.

Connection Lifecycle

┌─────────────┐         ┌─────────────┐         ┌─────────────┐
│  Application│         │ Connection  │         │  PostgreSQL │
│   Request   │────────>│    Pool     │────────>│   Database  │
│             │         │             │         │             │
│  - Query    │         │ - Idle Conn │         │ - Execute   │
│  - Execute  │<────────│ - Active    │<────────│ - Return    │
│  - Return   │         │ - Waiting   │         │             │
└─────────────┘         └─────────────┘         └─────────────┘

Pool States

State	Description
Idle	Connection available for use
Active	Connection executing a query
Waiting	Request waiting for available connection

Prisma Connection Pool

Default Pool Size

Prisma’s own default of num_cpus * 2 + 1 doesn’t apply, because the app always sets connection_limit.

Configuration

Set the pool size with DATABASE_POOL_SIZE and the wait time with DATABASE_POOL_TIMEOUT:

DATABASE_POOL_SIZE=50
DATABASE_POOL_TIMEOUT=10

You can also set connection_limit in DATABASE_URL. If it’s present, the app uses the URL as-is and ignores both variables, so set pool_timeout in the URL too:

DATABASE_URL="postgresql://user:pass@localhost:5432/poker?connection_limit=50&pool_timeout=10"

Additional Pool Parameters

DATABASE_URL="postgresql://user:pass@host:5432/db?\
connection_limit=50&\
pool_timeout=10&\
connect_timeout=30"

Parameter	Default	Description
connection_limit	Set by the app, see above	Maximum connections in pool
pool_timeout	Set by the app, see above	Time to wait for available connection
connect_timeout	30 seconds	Time to establish new connection

Connection Pool Sizing

Sizing Formula

The optimal pool size depends on several factors:

Recommended pool size =
  min(
    PostgreSQL max_connections - reserved,
    expected_concurrent_queries * 1.5
  )

Sizing by Deployment

Deployment	Recommended Pool Size	PostgreSQL max_connections
Development	5-10	Default (100)
Small Production (<1K users)	20-30	Default (100)
Medium Production (<10K users)	40-60	150-200
Large Production	50-100	200+ (or PgBouncer)

These are starting points. The app runs as a single instance, so the pool size is also the app’s total connection count, plus any other tools connected to the database.

Calculating Concurrent Query Needs

Estimate based on your workload:

Concurrent queries ≈
  (Peak requests/second) × (Average query duration in seconds)

Example:
  500 req/s × 0.02s average = 10 concurrent queries

With safety margin (3x):
  10 × 3 = 30 connections recommended

Memory Impact

PostgreSQL memory per connection:

Total connection memory = max_connections × (work_mem + maintenance_work_mem + shared_buffers_per_conn)

Approximate per connection: 5-15 MB

Example:
  50 connections × 10 MB = 500 MB reserved for connections

Common Pool Exhaustion Symptoms

Symptom	Likely Cause	Solution
Timed out fetching a connection from the pool	Pool too small	Increase DATABASE_POOL_SIZE
Slow query response times	Pool contention	Increase pool or optimize queries
PostgreSQL too many connections	Exceeded max_connections	Add PgBouncer, increase PostgreSQL limit

PgBouncer Setup

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It’s optional: use it when other clients share the database or you need to cap server connections.

Why PgBouncer?

• Connection multiplexing: Many app connections share fewer DB connections
• Connection reuse: Reduces connection setup overhead
• Overload protection: Prevents PostgreSQL from being overwhelmed
• Zero-downtime deploys: Connections survive application restarts

Architecture

┌─────────────┐
│     App     │
│ (50 conns)  │
└──────┬──────┘
       │
┌──────▼──────┐
│  PgBouncer  │
│ (100 conns) │
└──────┬──────┘
       │
┌──────▼──────┐
│ PostgreSQL  │
│(100 max_conn)│
└─────────────┘

Installation

Ubuntu/Debian:

sudo apt-get install pgbouncer

macOS:

brew install pgbouncer

Docker:

docker run -d \
  --name pgbouncer \
  -e DATABASE_URL="postgres://user:pass@postgres:5432/poker" \
  -e POOL_MODE="transaction" \
  -e MAX_CLIENT_CONN="500" \
  -e DEFAULT_POOL_SIZE="50" \
  -p 6432:6432 \
  edoburu/pgbouncer

Configuration (pgbouncer.ini)

[databases]
; Database connection configuration
poker = host=localhost port=5432 dbname=poker_engine

[pgbouncer]
; Listen settings
listen_addr = 0.0.0.0
listen_port = 6432

; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pool mode: "session", "transaction", or "statement"
; Use "transaction" for most web applications
pool_mode = transaction

; Pool size settings
default_pool_size = 50      ; connections per database
min_pool_size = 10          ; minimum connections to keep
reserve_pool_size = 5       ; extra connections for burst
reserve_pool_timeout = 3    ; seconds before using reserve

; Connection limits
max_client_conn = 500       ; max connections from applications
max_db_connections = 100    ; max connections to PostgreSQL

; Timeouts
server_idle_timeout = 60    ; close idle server connections
client_idle_timeout = 0     ; don't close idle client connections
server_connect_timeout = 15

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Statistics
stats_period = 60

Pool Modes Explained

Mode	Best For	Description
session	Apps using prepared statements	Connection locked until client disconnects
transaction	Most web apps (recommended)	Connection returned after each transaction
statement	Simple queries only	Connection returned after each statement

For Prisma: Transaction mode requires pgbouncer=true in the connection string (see below).

Caveats for this app:

• Run migrations against PostgreSQL directly. prisma migrate deploy uses DATABASE_URL, and the schema has no separate directUrl
• The first-install bootstrap takes a session-level pg_advisory_lock. Complete the first startup with a direct connection, because transaction mode doesn’t keep a client on one server connection

User Authentication (userlist.txt)

"postgres" "md5password_hash_here"
"app_user" "md5password_hash_here"

Generate MD5 hash:

echo -n "passwordusername" | md5sum
# Prepend 'md5' to the hash

Connecting Through PgBouncer

Update DATABASE_URL to point to PgBouncer:

# Before (direct connection)
DATABASE_URL="postgresql://user:pass@localhost:5432/poker_engine"

# After (through PgBouncer)
DATABASE_URL="postgresql://user:pass@localhost:6432/poker"

Important: When using PgBouncer with Prisma, add pgbouncer=true:

DATABASE_URL="postgresql://user:pass@localhost:6432/poker?pgbouncer=true"

The app still appends connection_limit and pool_timeout from DATABASE_POOL_SIZE and DATABASE_POOL_TIMEOUT. Don’t reduce the pool to connection_limit=1: the app is one long-running process, so that would run all its queries one at a time.

Production Configuration

Environment Variables

The Poker Engine provides environment variables for pool configuration in src/config/database.ts:

Variable	Default (Dev)	Default (Prod)	Description
DATABASE_URL	Required	Required	PostgreSQL connection string
DATABASE_POOL_SIZE	10	50	Number of connections in pool (positive integer)
DATABASE_POOL_TIMEOUT	10	10	Seconds to wait for available connection (positive integer)

How it works:

• If DATABASE_URL already contains connection_limit, that value takes precedence
• Otherwise, DATABASE_POOL_SIZE and DATABASE_POOL_TIMEOUT are appended automatically
• The pool configuration is logged on startup in development mode

Recommended Production Setup

# =============================================================================
# DATABASE (Production)
# =============================================================================

# Option 1: Use environment variables (recommended for simplicity)
DATABASE_URL="postgresql://app_user:password@db.internal:5432/poker"
DATABASE_POOL_SIZE=50
DATABASE_POOL_TIMEOUT=15

# Option 2: Through PgBouncer
DATABASE_URL="postgresql://app_user:password@pgbouncer.internal:6432/poker?pgbouncer=true"
DATABASE_POOL_SIZE=50

# Option 3: Direct connection with explicit URL parameters
# DATABASE_URL="postgresql://app_user:password@db.internal:5432/poker?connection_limit=50&pool_timeout=30"

PostgreSQL Configuration (postgresql.conf)

# Connection limits
max_connections = 200           # or higher with PgBouncer

# Memory (adjust based on available RAM)
shared_buffers = 1GB            # 25% of RAM
effective_cache_size = 3GB      # 75% of RAM
work_mem = 16MB                 # per-operation memory
maintenance_work_mem = 256MB

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 64MB

# Query planning
random_page_cost = 1.1          # for SSD storage
effective_io_concurrency = 200  # for SSD storage

Environment-Specific Pool Sizes

Environment	Pool Configuration	Reason
Development	DATABASE_POOL_SIZE=5	Minimal resource usage
Staging	DATABASE_POOL_SIZE=20	Mirror production proportionally
Production	DATABASE_POOL_SIZE=50	Balance load and resources

Multiple Application Servers

Run one application instance. Live table state is held in the memory of the app process, so the engine doesn’t support multiple app servers for gameplay.

Monitoring and Troubleshooting

Key Metrics to Monitor

Metric	Source	Alert Threshold
Active connections	pg_stat_activity	>80% of max
Waiting queries	pg_stat_activity	Any sustained waiting
Configured pool size	/metrics/prometheus (when METRICS_SCRAPE_TOKEN is set)	n/a
Pool utilization	PgBouncer stats	>90%
Connection errors	Application logs	Any errors

PostgreSQL Connection Monitoring

-- Current connection count by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name;

-- Long-running queries (potential connection hogs)
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '30 seconds'
  AND state != 'idle';

-- Kill a specific connection if needed
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = <pid>;

PgBouncer Monitoring

Connect to PgBouncer admin console:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Useful commands:

-- Pool statistics
SHOW POOLS;

-- Client connections
SHOW CLIENTS;

-- Server connections
SHOW SERVERS;

-- Connection stats
SHOW STATS;

-- Configuration
SHOW CONFIG;

Prisma Query Logging

In development, the app logs queries by default. Set QUERY_MONITOR=true to detect N+1 queries instead, with QUERY_MONITOR_THRESHOLD and QUERY_MONITOR_VERBOSE to tune it. In production, the app logs Prisma errors only.

Common Issues and Solutions

Issue: P1000: Authentication failed

Error: P1000: Authentication failed against database server

Solutions:

1. Verify DATABASE_URL credentials
2. Check pg_hba.conf allows connections from your IP
3. Ensure user has access to the database

Issue: P1001: Can't reach database server

Error: P1001: Can't reach database server at `hostname:port`

Solutions:

1. Verify PostgreSQL is running: pg_isready -h localhost
2. Check firewall rules
3. Verify connection string host/port

Issue: P1010: User was denied access

Error: P1010: User 'username' was denied access on the database

Solutions:

1. Grant permissions: GRANT ALL ON DATABASE poker TO username;
2. Check pg_hba.conf authentication method

Issue: Timed out fetching a connection from the pool

Error: Timed out fetching a connection from the connection pool

Solutions:

1. Increase DATABASE_POOL_SIZE, or connection_limit if it’s in DATABASE_URL
2. Add PgBouncer for better connection management
3. Check for long-running queries holding connections
4. Increase DATABASE_POOL_TIMEOUT if connections are temporarily exhausted

Issue: too many connections for role

FATAL: too many connections for role "username"

Solutions:

1. Raise the role’s limit with ALTER ROLE username CONNECTION LIMIT n;
2. Increase max_connections in postgresql.conf
3. Use PgBouncer for connection pooling

Summary

Scale	Configuration	Pool Size	Notes
Development	Direct Prisma	10	Default settings
Small prod	Direct Prisma	20-50	Default is 50; adjust DATABASE_POOL_SIZE
Medium prod	Direct Prisma	40-60	Raise PostgreSQL max_connections if needed
Large prod	PgBouncer	50-100	Add PgBouncer between app and DB

Quick Start Checklist:

1. [ ] Set an appropriate DATABASE_POOL_SIZE
2. [ ] Install and configure PgBouncer if you need it
3. [ ] Set pgbouncer=true when using PgBouncer
4. [ ] Monitor connection metrics in production
5. [ ] Configure PostgreSQL max_connections appropriately
6. [ ] Set up alerting for connection exhaustion