GO DB Pool size

Question

consider the following Scenario

DB pool settings are:

func NewDB(Log *zap.SugaredLogger) *DBClient {
    dbname := viper.GetString("DB_NAME")
    user := viper.GetString("DB_USER")
    password := viper.GetString("DB_PASSWORD")
    host := viper.GetString("DB_HOST")
    port := viper.GetInt("DB_PORT")

    dbInfo := fmt.Sprintf("postgres://%v:%v@%v:%v/%v?sslmode=disable",
        user, password, host, port, dbname)
    Log.Infof("Connection String: %v", dbInfo)
    dbConn, err := sql.Open("postgres", dbInfo)
    if err != nil {
        err := fmt.Errorf("db_error: %v", err)
        Log.Error(err)
        log.Fatal(err)
    }

    err = dbConn.Ping()
    if err != nil {
        err := fmt.Errorf("db_error: %v", err)
        Log.Error(err)
        log.Fatal(err)
    }

    dbConn.SetMaxIdleConns(viper.GetInt("DB_MIN_POOL")) // Maximum idle connections
    dbConn.SetMaxOpenConns(viper.GetInt("DB_MAX_POOL")) // Maximum open connections
    dbConn.SetConnMaxLifetime(time.Duration(viper.GetInt("DB_CONN_TIME")) * time.Minute)

    Log.Info("Postgres connected.")
    DB = dbConn

    return &DBClient{Log: Log, DB: dbConn}
}

sql.DBStats{MaxOpenConnections:250, OpenConnections:250, InUse:250, Idle:0, WaitCount:399, WaitDuration:4442935235, MaxIdleClosed:0, MaxIdleTimeClosed:0, MaxLifetimeClosed:69}

MY DB pool size is not coming down.Post this we have increased the size to 2000 and we are restarting our service once in a day.

DB Stat : sql.DBStats{MaxOpenConnections:2000, OpenConnections:154, InUse:151, Idle:3, WaitCount:0, WaitDuration:0, MaxIdleClosed:0, MaxIdleTimeClosed:0, MaxLifetimeClosed:258}

Didn't do much we have increased pool size to 2000 and we are restarting our service once in a day.

How can we solve this problem? I want to bring my pool size down and close all idle connections

Answer 1

1. Why are you keeping idle connections open? SetMaxIdleConns will help you to kill out idle connections. Setting this value to -1 does the required.

If there is no particular reason, you can kill off idle connections.