Introduction

Go SQL connection pool is exhausted when max open connections is too low for concurrent queries. This guide provides step-by-step diagnosis and resolution with specific commands and code examples.

Symptoms

Typical symptoms and error messages when this issue occurs:

bash
sql: connection pool exhausted
max open connections reached

Observable indicators: - Application logs show connection or operation failures - Error messages appear in system or application logs - Related dependent services may exhibit cascading failures

Common Causes

  1. 1.The connection pool exhaustion typically occurs when:
  2. 2.Response bodies are not closed after reading
  3. 3.MaxIdleConnsPerHost is set too low
  4. 4.Connection timeout is not configured

Step-by-Step Fix

Step 1: Check Current State

bash
SHOW STATUS LIKE "Threads_connected";

Step 2: Identify Root Cause

bash
SELECT count(*) FROM pg_stat_activity;

Step 3: Apply Primary Fix

go
// Increase pool size in connection string
db, err := sql.Open("postgres",
    "host=localhost port=5432 user=app dbname=db sslmode=disable")
db.SetMaxOpenConns(50)    // Increase from default
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

Apply this configuration and restart the application:

bash
go build && ./application

Step 4: Apply Alternative Fix (If Needed)

go
// Use connection pooling context
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
rows, err := db.QueryContext(ctx, "SELECT * FROM users")

Monitor database connection metrics after changes: SHOW STATUS LIKE "Threads%";

Step 5: Verify the Fix

After applying the fix, verify with:

bash
go test -v ./pkg/database -run TestConnectionPool

Expected output should show successful operation without errors.

Common Pitfalls

  • Forgetting to close response bodies
  • Setting MaxIdleConnsPerHost too low
  • Not handling connection errors gracefully

Best Practices

  • Use context for all database operations
  • Close rows immediately after use
  • Set connection pool limits based on load
  • Go SQL Transaction Deadlock
  • Go SQL Null Value Handling Error
  • Go SQL Connection Refused