Connect Go to PostgreSQL the Right Way With Pooling

Connecting to PostgreSQL from Go takes three lines. Connecting it well takes a few more decisions: which driver, which pool, how many connections, which timeouts, and what to do when the password is wrong. This article makes those decisions on Go 1.25.6 against a PostgreSQL 18 container, and explains each so you can change them for your own deployment.

Start a database

docker run -d --name pg -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=lab -p 55432:5432 postgres:18-alpine

The container listens on port 55432 on the host, with a database named lab and the superuser postgres. The password is a placeholder for a lab; real credentials come from the environment or a secret store, never from source.

Which driver

For PostgreSQL there are two families. pgx (github.com/jackc/pgx/v5) is the actively maintained driver and toolkit. It speaks the PostgreSQL protocol natively, supports COPY, LISTEN/NOTIFY, batches, rich types and a connection pool, and also offers a database/sql adapter. lib/pq is the older driver, in maintenance mode, usable only through database/sql. New code should use pgx. The rest of this article does.

There are then two ways to use it.

The native pool

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"sync"
	"time"

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

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func main() {
	ctx := context.Background()

	// 1. pgxpool: the native pgx pool.
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		log.Fatal(err)
	}
	cfg.MaxConns = 4
	cfg.MinConns = 1
	cfg.MaxConnLifetime = time.Hour
	cfg.MaxConnIdleTime = 5 * time.Minute
	cfg.ConnConfig.ConnectTimeout = 3 * time.Second
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	if err := pool.Ping(ctx); err != nil {
		log.Fatal(err)
	}
	var ver string
	_ = pool.QueryRow(ctx, `select split_part(version(), ' ', 2)`).Scan(&ver)
	fmt.Printf("pgxpool: connected to PostgreSQL %s\n", ver)

	// Eight concurrent queries share four connections.
	var wg sync.WaitGroup
	for range 8 {
		wg.Add(1)
		go func() {
			defer wg.Done()
			var n int
			_ = pool.QueryRow(ctx, `select 1 from pg_sleep(0.05)`).Scan(&n)
		}()
	}
	wg.Wait()
	st := pool.Stat()
	fmt.Printf("pgxpool stats: max=%d total=%d acquired=%d idle=%d acquiredCount=%d\n",
		st.MaxConns(), st.TotalConns(), st.AcquiredConns(), st.IdleConns(), st.AcquireCount())

	// 2. database/sql with the pgx driver, for code that wants the standard API.
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(4)
	db.SetMaxIdleConns(2)
	db.SetConnMaxLifetime(time.Hour)
	db.SetConnMaxIdleTime(5 * time.Minute)
	var user, dbname string
	if err := db.QueryRowContext(ctx, `select current_user, current_database()`).Scan(&user, &dbname); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("database/sql via pgx: user=%s database=%s open=%d\n", user, dbname, db.Stats().OpenConnections)

	// 3. A wrong password fails at the first use, not at sql.Open.
	bad, _ := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	err = bad.PingContext(ctx)
	fmt.Printf("bad password: %.60s\n", fmt.Sprint(err))
}

pgxpool: connected to PostgreSQL 18.6
pgxpool stats: max=4 total=4 acquired=0 idle=4 acquiredCount=10
database/sql via pgx: user=postgres database=lab open=1
bad password: failed to connect to `user=postgres database=lab`: 127.0.0.1

The program does three things. First it builds a native pool, which is the section to copy.

cfg, err := pgxpool.ParseConfig(dsn)
cfg.MaxConns = 4
cfg.MinConns = 1
cfg.MaxConnLifetime = time.Hour
cfg.MaxConnIdleTime = 5 * time.Minute
cfg.ConnConfig.ConnectTimeout = 3 * time.Second
pool, err := pgxpool.NewWithConfig(ctx, cfg)

ParseConfig reads a URL such as postgres://user:pass@host:5432/db?sslmode=disable, or a keyword string, and also respects the standard PG* environment variables, so containers can configure the connection without code. Then the program overrides the pool settings.

• MaxConns is the ceiling. The default is the larger of 4 and the number of CPUs, which is a poor guess for a service; set it from the database's connection budget. A rule: total connections from all instances of all services must stay under max_connections on the server, minus some headroom.
• MinConns keeps a few connections warm, so a quiet service does not pay a connection setup on the first request after idle time.
• MaxConnLifetime retires connections after a while, so that a failover, a DNS change or a proxy restart eventually takes effect.
• MaxConnIdleTime closes connections idle for too long, which frees server resources after a burst.
• ConnectTimeout bounds each new connection attempt, so a dead host fails in seconds.

Ping forces the first connection and returns an error if the database is unreachable. The program then reads the server version, PostgreSQL 18.6, which is worth logging at start-up to catch a service pointed at the wrong environment.

Reading the pool

Eight goroutines each run a 50 ms query, select 1 from pg_sleep(0.05), on a pool of four. The stats after they finish read max=4 total=4 acquired=0 idle=4 acquiredCount=10. Four connections were open and all four are back in the pool: nothing leaked. acquiredCount=10 counts the ping, the version query and the eight workers. The first four ran at once and the second four waited for a free connection; that wait is the pool doing its job, and its cost is explained in pool settings.

Export pool.Stat() as metrics. AcquireDuration and EmptyAcquireCount tell you how often callers waited for a connection, which is the signal that MaxConns is too low or that something holds connections too long.

The standard API

The second part of the program opens the same database through database/sql:

db, err := sql.Open("pgx", dsn)
db.SetMaxOpenConns(4)
db.SetMaxIdleConns(2)
db.SetConnMaxLifetime(time.Hour)
db.SetConnMaxIdleTime(5 * time.Minute)

The blank import of pgx/v5/stdlib registers the driver under the name pgx. The settings are the database/sql equivalents; sql.DB has no minimum, only a maximum idle count. The program runs select current_user, current_database() and prints user=postgres database=lab open=1: one connection was opened by the first call. Everything about database/sql types, including what each holds, is in the explainer.

Choose one pool per process. If a service needs both styles, say sqlx for most queries and native COPY for bulk loading, share the underlying connections: stdlib.OpenDBFromPool(pool) wraps a pgxpool.Pool as a *sql.DB, and the two views then draw from the same limit.

A wrong password

The last block opens a pool with a bad password and pings it. sql.Open returned without error, because it only validates the DSN's shape. Ping failed with failed to connect to user=postgres database=lab``, followed by the address and the server's message. The distinction matters in tests and in health checks: a service that never pings starts happily and fails on its first request. Ping at start-up, retry for a limited time if the database may still be booting (containers do), then fail loudly.

Error messages include the connection description but pgx masks the password. Even so, do not log the raw DSN; build log fields from the parsed config.

TLS and credentials

The lab uses sslmode=disable because the container has no certificate. In production use sslmode=verify-full with the root certificate for your provider, so the driver verifies the server's identity; require encrypts but does not verify, and is the common source of a false sense of safety. Put the password in an environment variable or a secrets manager. If your provider rotates credentials, set MaxConnLifetime short enough that old connections die before the old password does, and rebuild the pool config when the secret changes.

Boundaries

The settings above suit a service with a handful of instances. Behind a connection pooler such as PgBouncer in transaction mode, session features break: prepared statements across calls, SET, advisory locks and LISTEN. pgx has a mode for this, QueryExecModeSimpleProtocol or QueryExecModeExec, set on the ConnConfig. Serverless platforms that start many short-lived instances need an external pooler, since each instance's pool multiplies against the server's limit. And a pool is no substitute for a query that is too slow; look at timeouts next. The driver is documented at pkg.go.dev/github.com/jackc/pgx/v5, and the server's connection settings in the PostgreSQL documentation.