# PgBouncer Connection Pooler

PgBouncer is a lightweight connection pooler for PostgreSQL used by the Yemaya
platform to efficiently manage database connections.

## Overview

PgBouncer sits between the application and PostgreSQL, providing:

- **Connection pooling**: Reuses database connections across multiple clients
- **Connection limiting**: Controls the number of connections to PostgreSQL
- **Transaction pooling**: Shares connections at transaction boundaries for
  maximum efficiency
- **Reduced overhead**: PostgreSQL forks a process for each connection;
  PgBouncer reduces this

## Configuration

### Pool Mode: Transaction

Yemaya uses **transaction pooling mode** which:

- Releases connections back to the pool at transaction commit/rollback
- Allows many clients to share few server connections
- Requires session-independent queries (no session-level features)

### Connection Limits

```ini
default_pool_size = 20       # Server connections per user/database pair
min_pool_size = 5            # Minimum server connections maintained
reserve_pool_size = 5        # Extra connections for burst traffic
max_client_conn = 200        # Maximum client connections
```

### Timeouts

```ini
server_idle_timeout = 600    # Close idle server connections after 10min
client_idle_timeout = 600    # Close idle client connections after 10min
server_connect_timeout = 15  # Timeout for connecting to PostgreSQL
query_timeout = 120          # Query execution timeout
```

## Environment Variables

| Variable                   | Description            | Default    |
| -------------------------- | ---------------------- | ---------- |
| `POSTGRES_HOST`            | PostgreSQL host        | `postgres` |
| `POSTGRES_PORT`            | PostgreSQL port        | `5432`     |
| `POSTGRES_DB`              | Database name          | `yemaya`   |
| `POSTGRES_USER`            | Database user          | `yemaya`   |
| `POSTGRES_PASSWORD`        | Database password      | -          |
| `PGBOUNCER_ADMIN_PASSWORD` | Admin console password | -          |
| `PGBOUNCER_STATS_PASSWORD` | Stats user password    | -          |

## Usage

### Development (Docker Compose)

```bash
# Start all services including PgBouncer
docker compose up -d

# Connect through PgBouncer (port 6432)
psql "postgresql://yemaya:yemaya_dev_password@localhost:6432/yemaya"

# Direct connection bypassing PgBouncer (port 5432)
psql "postgresql://yemaya:yemaya_dev_password@localhost:5432/yemaya"
```

### Application Configuration

Set environment variables for your application:

```env
# Use PgBouncer for regular connections (recommended)
DATABASE_URL="postgresql://yemaya:password@pgbouncer:6432/yemaya?pgbouncer=true"

# Direct connection for migrations only
DATABASE_URL_DIRECT="postgresql://yemaya:password@postgres:5432/yemaya"
```

### Prisma Configuration

The `@yemaya/database` package automatically detects PgBouncer and configures
Prisma appropriately:

```typescript
import {
  createDatabaseClient,
  createDirectDatabaseClient,
} from '@yemaya/database';

// Regular client (uses PgBouncer)
const db = createDatabaseClient();

// Direct client for migrations (bypasses PgBouncer)
const directDb = createDirectDatabaseClient();
```

## Monitoring

### PgBouncer Admin Console

Connect to the admin console:

```bash
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer
```

Useful commands:

```sql
SHOW STATS;     -- Query statistics
SHOW POOLS;     -- Pool status
SHOW CLIENTS;   -- Connected clients
SHOW SERVERS;   -- Backend connections
SHOW CONFIG;    -- Current configuration
```

### Programmatic Monitoring

Use the `PgBouncerAdmin` class from `@yemaya/database`:

```typescript
import { PgBouncerAdmin } from '@yemaya/database';

const admin = new PgBouncerAdmin({
  host: 'localhost',
  port: 6432,
  user: 'pgbouncer_admin',
  password: 'admin_password',
});

// Get pool statistics
const pools = await admin.getPoolStats();

// Get summary metrics
const summary = await admin.getSummary();

// Health check
const health = await admin.checkHealth();
```

### Health Checks

Integrate with the `@yemaya/health` package:

```typescript
import {
  HealthChecker,
  createPgBouncerCheck,
  createPgBouncerDetailedCheck,
} from '@yemaya/health';
import { PgBouncerAdmin } from '@yemaya/database';

const checker = new HealthChecker({ service: 'api', version: '1.0.0' });
const pgbouncerAdmin = new PgBouncerAdmin();

// Basic health check
checker.register(createPgBouncerCheck(pgbouncerAdmin));

// Detailed health check with metrics
checker.register(
  createPgBouncerDetailedCheck(pgbouncerAdmin, {
    maxWaitingThreshold: 5,
    maxWaitingCritical: 20,
    serverUtilizationThreshold: 80,
  })
);
```

## Troubleshooting

### High Waiting Clients

If you see many waiting clients:

1. Increase `default_pool_size`
2. Check for long-running queries/transactions
3. Verify application is closing connections properly

### Connection Refused

Check:

1. PgBouncer is running: `docker compose ps`
2. PostgreSQL is healthy: `docker compose logs postgres`
3. Authentication in `userlist.txt`

### Slow Queries

If queries are slow through PgBouncer:

1. Check pool saturation in `SHOW POOLS`
2. Verify no connection leaks
3. Consider increasing pool size

### Prisma Migration Errors

Always use direct connection for migrations:

```bash
DATABASE_URL=$DATABASE_URL_DIRECT npx prisma migrate deploy
```

## Security

- PgBouncer uses MD5 authentication by default
- Admin access is restricted to specific users
- Consider using TLS in production
- Never expose PgBouncer port publicly without authentication

## Files

- `pgbouncer.ini` - Main configuration file
- `userlist.txt` - User authentication
- `docker-entrypoint.sh` - Container startup script
- `Dockerfile` - Container image definition
