As a best-selling author, I invite you to explore my books on Amazon. Don't forget to follow me on Medium and show your support. Thank you! Your support means the world!
The moment your application starts talking to a database, performance becomes a conversation about overhead. Every connection handshake, every query parse, every network round-trip adds latency. In high-throughput systems, these milliseconds compound into seconds of user wait time. I've spent years optimizing these interactions, and the patterns I'll share here form the foundation of responsive, scalable data access.
Connection pooling isn't just a technique—it's a necessity. Establishing a new database connection is expensive. There's TCP handshaking, authentication, and session setup. When you're handling hundreds of requests per second, creating a new connection for each operation simply doesn't scale. The overhead quickly becomes the bottleneck.
Here's how I approach connection pooling in Go. The standard library's database/sql package provides a basic pool, but sometimes you need more control. I build wrapper structures that give me visibility and fine-tuning capabilities.
type SmartPool struct {
primary *sql.DB
replicas []*sql.DB
repIndex uint32
healthCh chan *sql.DB
config PoolConfig
}
type PoolConfig struct {
MaxConns int
IdleConns int
ConnMaxLifetime time.Duration
HealthCheckFreq time.Duration
}
func NewSmartPool(primaryDSN string, replicaDSNs []string, config PoolConfig) (*SmartPool, error) {
primary, err := sql.Open("postgres", primaryDSN)
if err != nil {
return nil, err
}
primary.SetMaxOpenConns(config.MaxConns)
primary.SetMaxIdleConns(config.IdleConns)
primary.SetConnMaxLifetime(config.ConnMaxLifetime)
replicas := make([]*sql.DB, len(replicaDSNs))
for i, dsn := range replicaDSNs {
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, err
}
replicas[i] = db
}
pool := &SmartPool{
primary: primary,
replicas: replicas,
healthCh: make(chan *sql.DB, 10),
config: config,
}
go pool.healthChecker()
return pool, nil
}
This pool structure supports read/write splitting. Write operations go to the primary database, while reads can be distributed across replicas. The health checker runs in the background, periodically validating connections and removing unhealthy ones from rotation.
Prepared statements are where many developers leave performance on the table. The first time you prepare a statement, the database parses the SQL, validates it, and creates an execution plan. Reusing prepared statements means skipping all that work.
I implement statement caching with thread safety and cache invalidation. The cache must handle database restarts and schema changes gracefully.
type StatementCache struct {
cache *lru.Cache
db *sql.DB
mutex sync.RWMutex
stats CacheStats
}
type CacheStats struct {
Hits int64
Misses int64
Evicts int64
}
func NewStatementCache(db *sql.DB, size int) *StatementCache {
cache, _ := lru.New(size)
return &StatementCache{
cache: cache,
db: db,
}
}
func (sc *StatementCache) Prepare(ctx context.Context, query string) (*sql.Stmt, error) {
// Check cache first
sc.mutex.RLock()
if stmt, ok := sc.cache.Get(query); ok {
atomic.AddInt64(&sc.stats.Hits, 1)
sc.mutex.RUnlock()
return stmt.(*sql.Stmt), nil
}
sc.mutex.RUnlock()
// Prepare and cache
sc.mutex.Lock()
defer sc.mutex.Unlock()
// Double-check after acquiring write lock
if stmt, ok := sc.cache.Get(query); ok {
return stmt.(*sql.Stmt), nil
}
stmt, err := sc.db.PrepareContext(ctx, query)
if err != nil {
return nil, err
}
sc.cache.Add(query, stmt)
atomic.AddInt64(&sc.stats.Misses, 1)
return stmt, nil
}
func (sc *StatementCache) Invalidate(query string) {
sc.mutex.Lock()
defer sc.mutex.Unlock()
sc.cache.Remove(query)
}
The LRU cache ensures we don't consume unlimited memory with statement storage. When the cache reaches capacity, it automatically evicts the least recently used statements. This works well for most applications where certain queries dominate the workload.
Batch operations transform performance for bulk data handling. Instead of executing 1000 inserts as 1000 separate transactions, we can group them into a single transaction with multiple statements. The reduction in network round-trips is dramatic.
Here's my approach to batch processing with proper error handling and configurable batch sizes:
type BatchProcessor struct {
db *sql.DB
stmtCache *StatementCache
maxBatchSize int
timeout time.Duration
}
func NewBatchProcessor(db *sql.DB, cache *StatementCache, maxBatchSize int) *BatchProcessor {
return &BatchProcessor{
db: db,
stmtCache: cache,
maxBatchSize: maxBatchSize,
timeout: 30 * time.Second,
}
}
func (bp *BatchProcessor) ExecuteBatch(ctx context.Context, queries []BatchQuery) error {
if len(queries) == 0 {
return nil
}
ctx, cancel := context.WithTimeout(ctx, bp.timeout)
defer cancel()
tx, err := bp.db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
for _, query := range queries {
stmt, err := bp.stmtCache.Prepare(ctx, query.SQL)
if err != nil {
return err
}
txStmt := tx.StmtContext(ctx, stmt)
if _, err := txStmt.ExecContext(ctx, query.Params...); err != nil {
return err
}
}
return tx.Commit()
}
func (bp *BatchProcessor) ProcessInBatches(ctx context.Context, queries []BatchQuery) error {
for i := 0; i < len(queries); i += bp.maxBatchSize {
end := i + bp.maxBatchSize
if end > len(queries) {
end = len(queries)
}
batch := queries[i:end]
if err := bp.ExecuteBatch(ctx, batch); err != nil {
return err
}
}
return nil
}
This batch processor handles large datasets by breaking them into manageable chunks. Each batch runs in its own transaction, preventing giant transactions that could lock the database for extended periods.
Connection health checking is crucial for production systems. Databases restart, networks fail, and connections go stale. Without proper health checks, your application might try to use broken connections.
I implement active health checking that runs in the background:
func (sp *SmartPool) healthChecker() {
ticker := time.NewTicker(sp.config.HealthCheckFreq)
defer ticker.Stop()
for range ticker.C {
sp.checkPrimaryHealth()
sp.checkReplicaHealth()
}
}
func (sp *SmartPool) checkPrimaryHealth() {
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := sp.primary.PingContext(ctx); err != nil {
sp.healthCh <- sp.primary
// Logic to mark as unhealthy and potentially reconnect
}
}
The health checker pings each connection periodically. Failed connections get reported through a channel where cleanup and reconnection logic can handle them.
Metrics and monitoring provide the visibility needed to tune these patterns. I instrument everything—connection wait times, statement cache hit rates, batch processing times. These metrics guide capacity planning and performance tuning.
type PoolMetrics struct {
AcquireTime prometheus.Histogram
WaitCount prometheus.Counter
MaxConns prometheus.Gauge
IdleConns prometheus.Gauge
StmtCacheHits prometheus.Counter
StmtCacheMisses prometheus.Counter
}
func (sp *SmartPool) collectMetrics() {
stats := sp.primary.Stats()
metrics.MaxConns.Set(float64(stats.MaxOpenConnections))
metrics.IdleConns.Set(float64(stats.Idle))
// Export custom metrics
metrics.StmtCacheHits.Add(float64(atomic.LoadInt64(&sp.stmtCache.stats.Hits)))
}
These metrics help answer important questions. Is the connection pool sized correctly? Are we preparing statements too frequently? Is batch processing providing the expected benefits?
Error handling deserves special attention. Network timeouts, database restarts, and constraint violations all require different handling strategies. I implement retry logic for transient errors and circuit breakers to prevent cascading failures.
func withRetry(ctx context.Context, maxAttempts int, backoff time.Duration, fn func() error) error {
var lastErr error
for attempt := 1; attempt <= maxAttempts; attempt++ {
if err := fn(); err != nil {
if isTransientError(err) {
lastErr = err
time.Sleep(backoff * time.Duration(attempt))
continue
}
return err
}
return nil
}
return lastErr
}
func isTransientError(err error) bool {
// Check for network timeouts, connection errors, deadlocks
var pqErr *pq.Error
if errors.As(err, &pqErr) {
switch pqErr.Code.Class() {
case "08", "53", "54", "55", "57", "58":
return true
}
}
return false
}
This retry logic handles transient database errors with exponential backoff. It's important to distinguish between transient errors (which should be retried) and permanent errors (which should fail fast).
Connection pool tuning requires understanding your workload. The optimal pool size depends on your database's capacity and your application's concurrency patterns. Too small a pool causes contention; too large a pool overwhelms the database.
I typically start with these guidelines:
- Maximum connections: CPU cores * 2 + number of disks
- Idle connections: maximum connections / 2
- Connection lifetime: 30 minutes to avoid accumulation of stale connections
But these are just starting points. Real tuning requires load testing and monitoring under production-like conditions.
Prepared statement caching needs similar consideration. The cache size should accommodate your most frequently used queries without consuming excessive memory. I monitor cache hit rates and adjust the size accordingly.
Batch size optimization is workload-dependent. Smaller batches reduce transaction duration and locking but increase overhead. Larger batches improve throughput but risk longer locks and transaction timeouts. I test different batch sizes to find the sweet spot for each use case.
Context propagation is essential for graceful shutdown and timeout handling. Every database operation should accept a context that can cancel the operation. This prevents hung queries from accumulating during high load or deployment scenarios.
func (sp *SmartPool) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
conn := sp.GetReadConnection()
stmt, err := sp.stmtCache.Prepare(ctx, query)
if err != nil {
return nil, err
}
return stmt.QueryContext(ctx, args...)
}
The context carries deadlines, cancellation signals, and request-scoped values across API boundaries. This is particularly important in distributed systems where a single request might trigger multiple database operations.
Connection validation before use prevents many common issues. A connection might pass health checks but fail when actually used. I prefer to validate connections with a quick query rather than just a ping.
func (sp *SmartPool) validateConnection(conn *sql.DB) bool {
ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
defer cancel()
var result int
err := conn.QueryRowContext(ctx, "SELECT 1").Scan(&result)
return err == nil && result == 1
}
This validation query ensures the connection can actually execute statements, not just respond to pings.
Load balancing read operations across replicas improves performance and availability. The round-robin approach I showed earlier works well for simple cases. For more sophisticated routing, I sometimes implement weighted load balancing based on replica capacity or geographic proximity.
func (sp *SmartPool) GetReadConnection() *sql.DB {
if len(sp.replicas) == 0 {
return sp.primary
}
index := atomic.AddUint32(&sp.repIndex, 1) % uint32(len(sp.replicas))
return sp.replicas[index]
}
This simple round-robin selection distributes read load evenly across available replicas. For more complex scenarios, I might implement health-aware routing that skips unhealthy replicas.
Transaction management requires careful consideration. The default isolation level might not be appropriate for all use cases. I explicitly set isolation levels when needed:
func (sp *SmartPool) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) {
conn := sp.GetWriteConnection()
return conn.BeginTx(ctx, opts)
}
// Usage with specific isolation level
tx, err := pool.BeginTx(ctx, &sql.TxOptions{
Isolation: sql.LevelReadCommitted,
ReadOnly: false,
})
Explicit isolation level setting prevents surprises and ensures consistent behavior across different database implementations.
Connection pool exhaustion is a common production issue. When all connections are in use, new requests must wait. I implement timeout mechanisms and proper error handling for these scenarios:
func (sp *SmartPool) GetConnectionWithTimeout(ctx context.Context, timeout time.Duration) (*sql.DB, error) {
ctx, cancel := context.WithTimeout(ctx, timeout)
defer cancel()
// Implementation with context support
// ...
}
This timeout prevents indefinite waiting when the pool is saturated. The calling code can then decide whether to retry, fail gracefully, or use alternative data sources.
The combination of these patterns—connection pooling, prepared statement caching, and batch processing—creates a robust foundation for high-performance database access. Each technique addresses a specific source of overhead, and together they can improve throughput by an order of magnitude.
Implementation requires careful testing and monitoring. The optimal configuration depends on your specific workload, database capabilities, and performance requirements. Start with reasonable defaults, then measure and adjust based on real-world usage patterns.
Remember that these patterns work together. Connection pooling makes prepared statement caching more effective. Batch processing benefits from both connection reuse and prepared statements. The whole is greater than the sum of its parts.
The code examples I've provided should serve as a starting point. Adapt them to your specific needs, add appropriate metrics and monitoring, and test thoroughly under load. Database performance is often the difference between an adequate application and an exceptional one.
📘 Checkout my latest ebook for free on my channel!
Be sure to like, share, comment, and subscribe to the channel!
101 Books
101 Books is an AI-driven publishing company co-founded by author Aarav Joshi. By leveraging advanced AI technology, we keep our publishing costs incredibly low—some books are priced as low as $4—making quality knowledge accessible to everyone.
Check out our book Golang Clean Code available on Amazon.
Stay tuned for updates and exciting news. When shopping for books, search for Aarav Joshi to find more of our titles. Use the provided link to enjoy special discounts!
Our Creations
Be sure to check out our creations:
Investor Central | Investor Central Spanish | Investor Central German | Smart Living | Epochs & Echoes | Puzzling Mysteries | Hindutva | Elite Dev | Java Elite Dev | Golang Elite Dev | Python Elite Dev | JS Elite Dev | JS Schools
We are on Medium
Tech Koala Insights | Epochs & Echoes World | Investor Central Medium | Puzzling Mysteries Medium | Science & Epochs Medium | Modern Hindutva
Top comments (0)