Analysis of Golang SQL Connection Pool Mechanism and Usage

This article analyzes the principle and practical usage of the Golang SQL connection pool (database/sql). It is based on Go 1.16.7 and the source code at https://github.com/golang/go/blob/go1.16.7/src/database/sql/sql.go.

Why a connection pool is needed – Reusing established connections saves the high cost of creating new ones. Unlike PHP PDO’s persistent connections, Go provides a full‑featured pool that can manage connections, ping, and prepared‑statement caching.

Example code demonstrates the performance benefit: two consecutive inserts are executed, the first taking ~493 ms (including connection creation) and the second only ~62 ms because the connection is reused.

func main() {
    //firstly insert
    m := model.EnterGameLog{}
    t1 := time.Now()
    if err := dao.EnterGameLog(context.Background()).Create(&m); err != nil {
        log.Fatalln(err)
    }
    log.Println("first cost", time.Since(t1))

    //secondly insert
    t2 := time.Now()
    if err := dao.EnterGameLog(context.Background()).Create(&m); err != nil {
        log.Fatalln(err)
    }
    log.Println("second cost", time.Since(t2))
    log.Println("done")
}

To pinpoint where the time is spent, the author adds runtime tracing:

import "runtime/trace"

func main() {
    f1, _ := os.Create("trace1.out")
    f2, _ := os.Create("trace2.out")
    defer func() { f1.Close(); f2.Close() }()
    // first insert
    trace.Start(f1)
    t1 := time.Now()
    if err := dao.EnterGameLog(context.Background()).Create(&m); err != nil { log.Fatalln(err) }
    log.Println("first cost", time.Since(t1))
    trace.Stop()

    // second insert
    trace.Start(f2)
    t2 := time.Now()
    if err := dao.EnterGameLog(context.Background()).Create(&m); err != nil { log.Fatalln(err) }
    log.Println("second cost", time.Since(t2))
    trace.Stop()
    log.Println("done")
}

Running go tool trace trace1.out and go tool trace trace2.out shows that the first request spends time on connection creation, ping, and statement preparation, while the second request skips these steps, confirming the advantage of reuse.

Connection state management – The pool tracks three states: idle , inUse , and closed . The state transition diagram (idle → inUse) explains three possible actions when a query needs a connection: use an idle one, wait for one if the max‑open limit is reached, or create a new connection.

Connections are closed in four situations: error ErrBadConn , exceeding maxLifetime or maxIdleTime , exceeding maxIdleCount , or explicit pool shutdown. Sample code snippets illustrate each case.

Pool configuration and visualization – The most important settings are db.SetMaxIdleConns() , db.SetMaxOpenConns() , db.SetConnMaxLifetime() , and db.SetConnMaxIdleTime() . The article discusses heuristics (e.g., max connections ≈ 2 × CPU cores + disk count) and stresses that real‑world tuning must be based on observed pool metrics.

Gorm’s DBStats struct provides runtime statistics such as OpenConnections , InUse , Idle , WaitCount , and various closed‑connection counters. These can be exported via a Prometheus plugin as gauges and counters (e.g., gorm_dbstats_wait_count , gorm_dbstats_max_idle_closed ).

type DBStats struct {
    MaxOpenConnections int
    OpenConnections    int
    InUse              int
    Idle               int
    WaitCount          int64
    WaitDuration       time.Duration
    MaxIdleClosed      int64
    MaxIdleTimeClosed  int64
    MaxLifetimeClosed  int64
}

Monitoring these metrics helps identify bottlenecks: a rising wait_count indicates insufficient MaxOpenConns , while a high max_idle_closed suggests the pool cannot recycle connections, prompting an increase of MaxIdleConns .

Finally, the article provides a stress‑test example that continuously runs 40 concurrent inserts, showing how mis‑configured SetMaxIdleConns() caused many connections to be closed and led to performance degradation in a real project.

In summary, the Golang SQL connection pool is implemented in a single file ( sql.go ) and serves as an excellent case study for understanding connection pooling in other systems. Proper tuning based on observed metrics is essential for optimal performance.