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

# 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

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

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

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

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

SET search_path = 'myschema';  -- Uses connection A
SELECT * FROM users;           -- Might use connection B!

Statement Mode

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

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

[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

curl http://localhost:9090/pools

Response (one entry per backend node):

[
  {
    "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:

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

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

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

[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

---