Connection Pooling

Connection Pooling

Optimize database connections for performance and scalability.

Overview

Why Connection Pooling?

Database connections are expensive resources:

Problem	Impact
Connection overhead	~100-300ms per new connection
Memory usage	~10MB per PostgreSQL connection
Connection limits	PostgreSQL default: 100 connections

Connection pooling solves these by maintaining reusable connections.

Pooling Architecture

Application-Level Pooling

Mantis components include built-in connection pooling.

Mandible Pool Configuration

Mandible is the only component with a database pool. The [database] fields are url, max_connections, min_idle, and connection_timeout_secs:

mandible.toml

[database]
url = "postgres://mantis:password@localhost:5432/mantis"
max_connections = 20         # Maximum pool size
min_idle = 5                 # Minimum idle connections
connection_timeout_secs = 30 # Connection acquisition timeout

Environment Variables

# Mandible (the connection URL; layered overrides use the MANDIBLE__DATABASE__ prefix)
DATABASE_URL=postgres://mantis:password@localhost:5432/mantis
MANDIBLE__DATABASE__MAX_CONNECTIONS=20
MANDIBLE__DATABASE__MIN_IDLE=5
MANDIBLE__DATABASE__CONNECTION_TIMEOUT_SECS=30

Sizing Guidelines

Pool Size Calculation

Formula:

pool_size = (core_count * 2) + spinning_disk_count

For SSD storage:

pool_size = core_count * 2

Example for 4-core server with SSD:

pool_size = 4 * 2 = 8

Recommended Settings

Deployment	Mandible pool
Development	5
Small (< 100 targets)	10
Medium (< 1000 targets)	20
Large (1000+ targets)	50

(Only Mandible connects to PostgreSQL, so it is the only pool to size.)

PostgreSQL max_connections

Ensure PostgreSQL can handle every Mandible instance’s pool:

max_connections >= (mandible_pool * mandible_instances) + overhead

Example:
max_connections >= (20 * 2) + 20 = 60

# postgresql.conf
max_connections = 200

PgBouncer Setup

For large deployments, use PgBouncer as an external connection pooler.

Installation

# Ubuntu/Debian
sudo apt install pgbouncer

# RHEL/Rocky/Alma
sudo dnf install pgbouncer

Configuration

/etc/pgbouncer/pgbouncer.ini

[databases]
mantis = host=localhost port=5432 dbname=mantis

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool settings
pool_mode = session              # what Mandible's LISTEN needs; see Pool Modes
default_pool_size = 50           # Connections per database/user
max_client_conn = 1000           # Max client connections
min_pool_size = 10               # Minimum pool size
reserve_pool_size = 5            # Emergency connections

# Connection limits
max_db_connections = 100         # Max connections to database

# Timeouts
server_connect_timeout = 15
server_idle_timeout = 600
server_lifetime = 3600
client_idle_timeout = 0          # 0 = disabled

# Logging
log_connections = 1
log_disconnections = 1
stats_period = 60

User Authentication

Create userlist:

# Generate password hash
psql -U postgres -c "SELECT concat('\"mantis\" \"', passwd, '\"') FROM pg_shadow WHERE usename='mantis'"

# Add to userlist.txt
echo '"mantis" "SCRAM-SHA-256$..."' >> /etc/pgbouncer/userlist.txt

Pool Modes

Mode	Description	Use Case
session	Connection per client session	Legacy apps
transaction	Connection per transaction	Most applications
statement	Connection per statement	Simple queries

Caution

Mandible needs session mode on the connection its DATABASE_URL names. It holds LISTEN registrations there for entity-change and notification events, and a transaction-mode pooler hands the server connection to another client between transactions: the notifications stop arriving, with no error. Either run the pool in session mode, or point Mandible at PostgreSQL directly and pool only other clients.

Start PgBouncer

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Connect Through PgBouncer

Update application configuration:

mandible.toml

[database]
url = "postgres://mantis:password@localhost:6432/mantis"
max_connections = 50  # Can be higher with PgBouncer

Monitoring Connections

Application Pool Metrics

The /api/v1/health endpoint performs a database connectivity check and returns {"status":"ok"} (200) when healthy or {"status":"unavailable"} (503) when the database is unreachable. It does not expose pool internals. For a pure process-liveness check that never gates on external dependencies, use /api/v1/health/live instead (always 200 while the process is running). Monitor pool saturation from the PostgreSQL side:

-- Active vs idle connections for the Mandible DB user
SELECT state, count(*) FROM pg_stat_activity
WHERE usename = 'mantis' GROUP BY state;

PostgreSQL Connections

-- Current connections
SELECT count(*), state
FROM pg_stat_activity
WHERE datname = 'mantis'
GROUP BY state;

-- Connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
WHERE datname = 'mantis'
GROUP BY application_name;

-- Long-running connections
SELECT pid, usename, application_name,
       now() - backend_start as connection_age,
       state, query
FROM pg_stat_activity
WHERE datname = 'mantis'
ORDER BY backend_start;

PgBouncer Statistics

# Connect to PgBouncer admin console
psql -p 6432 -U pgbouncer pgbouncer

# Show pools
SHOW POOLS;

# Show clients
SHOW CLIENTS;

# Show servers (actual PostgreSQL connections)
SHOW SERVERS;

# Show statistics
SHOW STATS;

Troubleshooting

Connection Exhaustion

Symptoms:

• “too many connections” errors
• Slow response times
• Connection timeouts

Diagnosis:

SELECT count(*) FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE state = 'idle' ORDER BY backend_start;

Solutions:

1. Increase max_connections in PostgreSQL
2. Reduce pool sizes
3. Use PgBouncer
4. Identify connection leaks

Connection Leaks

Symptoms:

• Pool exhaustion over time
• Idle connections accumulating

Diagnosis:

-- Find idle connections
SELECT pid, usename, application_name,
       now() - state_change as idle_time
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_time DESC;

Solutions:

1. Lower min_idle so fewer idle connections are held open, or use PgBouncer’s server_idle_timeout to recycle idle server connections
2. Check application code for unclosed connections
3. Enable connection lifecycle logging

Slow Connection Acquisition

Symptoms:

• High latency on first requests
• Timeout during connection

Diagnosis:

# Test connection time
time psql -h localhost -U mantis -c "SELECT 1"

Solutions:

1. Increase min_idle to pre-warm the pool
2. Check network latency to database
3. Reduce SSL negotiation overhead (connection reuse)

PgBouncer Issues

Client authentication failed:

# Check userlist.txt format
cat /etc/pgbouncer/userlist.txt

# Verify auth type matches PostgreSQL
grep auth_type /etc/pgbouncer/pgbouncer.ini

No free connections:

# Check pool status
psql -p 6432 pgbouncer -c "SHOW POOLS"

# Increase pool size or max_db_connections

Best Practices

1. Size Pools Appropriately

• Don’t over-provision (wastes memory)
• Don’t under-provision (causes contention)
• Monitor and adjust based on metrics

2. Use Connection Timeouts

The [database] section accepts only url, max_connections, min_idle, and connection_timeout_secs. Set the acquisition timeout here; for idle-recycling and max-lifetime behavior use an external pooler (PgBouncer).

[database]
connection_timeout_secs = 30  # Connection acquisition timeout

3. Enable Health Checks

Built-in health checks verify database connectivity:

curl https://localhost:3000/api/v1/health/ready

4. Monitor Pool Utilization

Mantis does not currently export pool-utilization gauges to Prometheus. Monitor pool saturation from the PostgreSQL side using pg_stat_activity:

-- Count active connections for the Mantis DB user
SELECT state, count(*) FROM pg_stat_activity
WHERE usename = 'mantis' GROUP BY state;

Alert when the active connection count approaches the configured max_connections value in your PostgreSQL instance.

5. Use Session Mode for Mandible

Transaction mode is the more efficient one, but Mandible’s LISTEN registrations do not survive it (see Pool Modes):

pool_mode = session

Next Steps

• Maintenance - Database maintenance
• PostgreSQL Setup - PostgreSQL configuration
• Monitoring - Metrics and alerts