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.

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/pool/stats

Response:

{
  "mode": "transaction",
  "total_connections": 50,
  "active_leases": 12,
  "idle_connections": 38,
  "waiting_requests": 0,
  "total_acquires": 15423,
  "total_releases": 15411,
  "avg_lease_duration_ms": 23
}

Key Metrics

Metric	Description	Alert Threshold
active_leases	Connections in use	> 90% of max
waiting_requests	Clients waiting	> 0 sustained
avg_lease_duration_ms	Hold time	Mode dependent

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

---

See Also

• Connection Pool Modes Architecture
• HA Failover with Pooling Tutorial
• Performance Tuning Guide