How to use pooling correctly?

[micahshell]

I'm new to Go and want to write web apps using psql as my db, so I'm going to have to use connection pooling. Below is a simplified version of what I'm doing, my issue is that I don't think it's using more than one connection. I'm testing it using the ab command, from a separate machine.

Sending 100 reqs with 20 concurrent connections*
ab -c 20 -n 100 http://192.168.1.166:3000/data | grep "per second"

Here's the code:
Sorry it's a bit much, the login info for psql is just for testing...

package main

import (
	"context"
	"fmt"
	"log"
	"net/http"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

func Config() *pgxpool.Config {
	const defaultMaxConns = 10
	const defaultMinConns = 0
	const defaultMaxConnLifetime = time.Hour * 1
	const defaultMaxConnIdleTime = time.Minute * 30
	const defaultHealthCheckPeriod = time.Minute
	const defaultConnectTimeout = time.Second * 5

	const DB_URL = "postgres://ps-user:password@localhost:5432/testing"

	dbConfig, err := pgxpool.ParseConfig(DB_URL)
	if err != nil {
		log.Fatal("Failed to create a config, error", err)
	}

	dbConfig.MaxConns = defaultMaxConns
	dbConfig.MinConns = defaultMinConns
	dbConfig.MaxConnLifetime = defaultMaxConnLifetime
	dbConfig.MaxConnIdleTime = defaultMaxConnIdleTime
	dbConfig.HealthCheckPeriod = defaultHealthCheckPeriod
	dbConfig.ConnConfig.ConnectTimeout = defaultConnectTimeout

	dbConfig.BeforeAcquire = func(ctx context.Context, c *pgx.Conn) bool {
		log.Println("Before acquiring the connection pool to the database!!")
		return true
	}

	dbConfig.AfterRelease = func(c *pgx.Conn) bool {
		log.Println("After releasing the connection pool to the database!!")
		return true
	}

	dbConfig.BeforeClose = func(c *pgx.Conn) {
		log.Println("Closed the connection pool to the database!!")
	}

	return dbConfig
}

func main() {
	http.HandleFunc("/data", handler)

	if err := http.ListenAndServe(":3000", nil); err != nil {
		fmt.Println(err)
	}
}

func handler(w http.ResponseWriter, r *http.Request) {
	connPool, err := pgxpool.NewWithConfig(context.Background(), Config())
	if err != nil {
		log.Fatal("Error while creating connection to the database!", err)
	}
	defer connPool.Close()

	connection, err := connPool.Acquire(context.Background())
	if err != nil {
		log.Fatal("Error whole acquiring connection from the database pool!", err)
	}

	defer connection.Release()

	err = connection.Ping(context.Background())
	if err != nil {
		log.Fatal("Could not ping the db!", err)
	}

	fmt.Println("Connected to the db!")

	if err := InsertData(connection, "John Doe"); err != nil {
		fmt.Print(err)
	}

	stats := connPool.Stat()
	fmt.Println("Connections:", stats.TotalConns())
	fmt.Println("Accuired Connections:", stats.AcquiredConns())
	fmt.Println("Accuired Count:", stats.AcquireCount())
	fmt.Println("Accuired Duration:", stats.AcquireDuration())
	fmt.Println("Accuired Constructing:", stats.ConstructingConns())
	fmt.Println("Idle Connections:", stats.IdleConns())
}

func CreateTableQuery(p *pgxpool.Conn) error {
	_, err := p.Exec(context.Background(), "CREATE TABLE IF NOT EXISTS customers(id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL);")
	if err != nil {
		return err
	}
	return nil
}

func InsertData(p *pgxpool.Conn, name string) error {
	query := `INSERT INTO customers(name) VALUES ($1)`
	_, err := p.Exec(context.Background(), query, name)

	if err != nil {
		return err
	}

	return nil
}

The output of the stats looks like this for each request. Am I doing something wrong or is this expected?

Thanks for your time.

2024/04/15 21:11:27 After releasing the connection pool to the database!!
2024/04/15 21:11:27 Closed the connection pool to the database!!
Connections: 1
Accuired Connections: 1
Accuired Count: 1
Accuired Duration: 21.697917ms
Accuired Constructing: 0
Idle Connections: 0
2024/04/15 21:11:27 After releasing the connection pool to the database!!
2024/04/15 21:11:27 Closed the connection pool to the database!!

[jackc]

You should create a pool to be used by your entire program. You are creating a new pool for each request.

[micahshell]

Dang I missed that looking at your chat example , thanks! Basically have a global variable of a type *pgxpool.Pool?

[jackc]

That is the simplest, correct way.

But in my production projects, I usually find some way to pass the pool to the handler (via context, struct field that the handler is defined on, or handler defined as closure that captures pool, etc.). Among other things, this makes testing easier.

[micahshell]

I'll give that a try, I did get it to work properly with a global variable, one question is where to create the connection pool? Before I was doing it with each request which was the culprit, right now I'm creating it in my main func like this:

func main() {
	pool, err := storage.CreatePool()
	if err != nil {
		log.Fatal("Can't access the db", err)
	}
	defer pool.Close()
	app := echo.New()

	// Setting middleware configs
	config.SetMiddleware(app)

	// Setting routes
	routes.InitHomeRoutes(app)
	routes.InitUserRoutes(app)

	app.Start(":3000")
}

storage.CreatePool() creates the with: pool, err = pgxpool.NewWithConfig(context.Background(), Config())

My routes are using handlers where I use functions that first connect like: connection, err := pool.Acquire(context.Background()), then defer connection.Release(), then perform the SQL commands with the connection var.

This works as intended and I can see when I print out the stats, but I feel like this could be done cleaner...

Thanks for your help.

[jackc]

Creating the pool in main is reasonable in a small project. Eventually, you might factor that out to a function to make testing easier.

Also, I usually give my handlers a reference to the pool via closure or struct or something like that rather that use a global at all. It makes testing much cleaner. In particular, it makes running tests in parallel much simpler.

As far as using pool.Acquire, unless you need the queries to run on the same connection for some reason you can call the query methods directly on the pool.

[Sadotib]

Creating the pool in main is reasonable in a small project. Eventually, you might factor that out to a function to make testing easier.

Also, I usually give my handlers a reference to the pool via closure or struct or something like that rather that use a global at all. It makes testing much cleaner. In particular, it makes running tests in parallel much simpler.

As far as using pool.Acquire, unless you need the queries to run on the same connection for some reason you can call the query methods directly on the pool.

is it a good idea to create the pool in init() during the initial build of the service?

[jackc]

is it a good idea to create the pool in init() during the initial build of the service?

I typically prefer not to use init() for initial setup. I find it easier to read configuration and handle errors outside of init().