---
title: "HeliosProxy Connection Pool Modes Tutorial"
---

# HeliosProxy Connection Pool Modes Tutorial

This guide explains the three connection pooling modes available in HeliosProxy and how to choose the right one for your workload.

> **HeliosDB Lite:** use session pooling (the default). Transaction and statement pooling reset each connection with `DISCARD ALL` when it returns to the pool, and `DISCARD ALL` is planned for Lite; until then the reset fails, HeliosProxy closes the backend connection instead of reusing it, and pooling gives no benefit.

## Prerequisites

- HeliosDB cluster running
- `heliosdb-proxy` binary compiled with `--features pool-modes`
- PostgreSQL client (`psql`) for testing

## Quick Start

```bash
# Run the interactive tutorial
./docs/tutorials/pool-modes-tutorial.sh
```

---

## Understanding Pooling Modes

### The Problem

Without connection pooling, each client maintains a dedicated connection to the database. With 1000 clients, you need 1000 backend connections. PostgreSQL connections are expensive:

- ~10MB memory per connection
- Process fork overhead
- Limited by `max_connections`

### The Solution

Connection pooling allows multiple clients to share a smaller pool of backend connections. HeliosProxy offers three modes with different trade-offs:

| Mode | Connection Lifetime | Best For | Limitations |
|------|---------------------|----------|-------------|
| Session | Entire client session | Complex apps | Most connections |
| Transaction | Single transaction | Web apps | No cross-txn state |
| Statement | Single statement | Simple queries | No transactions |

---

## Mode Details

### Session Mode

```toml
[pool_mode]
mode = "session"
```

**Behavior:**
- Client gets a dedicated backend connection on connect
- Connection held until client disconnects
- Full PostgreSQL feature support

**Use Cases:**
- Interactive database sessions
- Applications using prepared statements extensively
- Sessions with temporary tables
- Long-running reporting queries

**Example:**
```sql
-- All these work because same connection is used
SET search_path = 'myschema';
PREPARE my_query AS SELECT * FROM users WHERE id = $1;
CREATE TEMP TABLE results AS SELECT ...;
EXECUTE my_query(1);
```

### Transaction Mode

```toml
[pool_mode]
mode = "transaction"
```

**Behavior:**
- Connection acquired on first query
- Held during active transaction (BEGIN to COMMIT/ROLLBACK)
- Released immediately after transaction ends
- Auto-commit queries get connection for single statement

**Use Cases:**
- Web applications and APIs
- Microservices
- Most OLTP workloads

**Example:**
```sql
-- Connection acquired
BEGIN;
INSERT INTO orders VALUES (...);
UPDATE inventory SET ...;
COMMIT;
-- Connection returned to pool

-- Next query might get different connection
SELECT * FROM orders WHERE id = 123;
-- Connection returned immediately (auto-commit)
```

**Important:** Session state is lost between transactions:
```sql
SET search_path = 'myschema';  -- Uses connection A
SELECT * FROM users;           -- Might use connection B!
```

### Statement Mode

```toml
[pool_mode]
mode = "statement"
```

**Behavior:**
- Connection acquired for each statement
- Released immediately after statement completes
- Maximum connection sharing

**Use Cases:**
- High-throughput read workloads
- Simple single-query operations
- Analytics dashboards

**Limitations:**
- No explicit transactions (BEGIN/COMMIT)
- No prepared statements
- No session variables
- No temporary tables

**Example:**
```sql
-- Each query uses potentially different connection
SELECT * FROM users WHERE id = 1;  -- Connection A
SELECT * FROM orders WHERE user_id = 1;  -- Connection B
```

---

## Configuration Reference

### Full Configuration Example

```toml
[pool_mode]
# Pooling mode: "session", "transaction", or "statement"
mode = "transaction"

# Maximum connections per backend node
max_pool_size = 100

# Minimum idle connections to maintain (warm pool)
min_idle = 10

# Close idle connections after this many seconds
idle_timeout_secs = 600

# Maximum connection lifetime in seconds
max_lifetime_secs = 3600

# Timeout waiting for available connection
acquire_timeout_secs = 5

# SQL to reset connection state when returning to pool
reset_query = "DISCARD ALL"

# Prepared statement handling: "disable", "track", or "named"
prepared_statement_mode = "track"
```

### Configuration Guidelines

| Workload | Mode | max_pool_size | Notes |
|----------|------|---------------|-------|
| Web API | transaction | 20-50 | Per backend node |
| Microservice | transaction | 10-20 | Keep small |
| Analytics | statement | 5-10 | Read-heavy |
| Admin tools | session | 5-10 | Full features |
| Mixed | transaction | 50-100 | Good default |

---

## Monitoring

### Pool Statistics API

```bash
curl http://localhost:9090/pools
```

Response (one entry per backend node):
```json
[
  {
    "node": "3f0c2a8e-5b1d-4c7a-9e2f-1a6b8d4c0e71",
    "active_connections": 12,
    "idle_connections": 38,
    "pending_requests": 0,
    "total_connections_created": 50,
    "total_connections_closed": 0
  }
]
```

Today, `pending_requests` and `total_connections_closed` are not tracked per node and always read `0`, and `total_connections_created` reports the live pool size. If `admin_token` is set, add `-H "Authorization: Bearer $ADMIN_TOKEN"`.

### Key Metrics

| Metric | Description | Alert Threshold |
|--------|-------------|-----------------|
| `active_connections` | Connections in use on the node | > 90% of `max_pool_size` |
| `idle_connections` | Pooled connections ready for reuse | Near 0 under load |

Connection acquire timeouts show up in your application as errors; raise `max_pool_size` or `acquire_timeout_secs` if they persist.

### Expected Lease Duration by Mode

| Mode | Expected Duration | Concern If |
|------|-------------------|------------|
| Session | Minutes to hours | N/A |
| Transaction | 10-100ms | > 1s average |
| Statement | 1-10ms | > 100ms average |

---

## Troubleshooting

### "Connection pool exhausted"

**Symptoms:** Clients timeout waiting for connections

**Causes:**
1. `max_pool_size` too small
2. Long-running transactions holding connections
3. Connection leak (unreleased connections)

**Solutions:**
```toml
# Increase pool size
max_pool_size = 200

# Add timeout for stuck transactions
statement_timeout_secs = 30
```

### "Session state lost between queries"

**Symptoms:** SET commands don't persist, prepared statements fail

**Cause:** Using transaction/statement mode with session-dependent features

**Solutions:**
1. Switch to session mode for affected clients
2. Move state into application layer
3. Use per-query SET: `SET LOCAL search_path = 'schema'`

### "Prepared statement does not exist"

**Symptoms:** EXECUTE fails with "prepared statement X does not exist"

**Cause:** Connection was returned to pool, prepared statement lost

**Solutions:**
```toml
# Track and recreate prepared statements
prepared_statement_mode = "track"

# Or disable server-side prepared statements
prepared_statement_mode = "disable"
```

---

## Best Practices

### 1. Start with Transaction Mode

Transaction mode works for 90% of applications. Only switch if you have specific needs.

### 2. Keep Transactions Short

Long transactions hold connections and reduce sharing efficiency:

```sql
-- Bad: Long transaction
BEGIN;
SELECT * FROM large_table;  -- Takes 30 seconds
COMMIT;

-- Good: Quick transaction
BEGIN;
INSERT INTO results SELECT * FROM large_table;
COMMIT;
-- Process results in application
```

### 3. Use Connection Timeouts

Prevent runaway queries from holding connections:

```toml
[pool_mode]
acquire_timeout_secs = 5
statement_timeout_secs = 30
```

### 4. Monitor Lease Duration

High average lease duration indicates potential issues:
- Long queries that should be optimized
- Missing COMMIT statements
- Inappropriate mode for workload

### 5. Right-size Your Pool

```
max_pool_size = expected_concurrent_transactions * 1.5
```

Too small: Clients wait for connections
Too large: Wasted database resources

---