Day 093 — pgx & Postgres Connection Pooling

Month 4 · Week 2 · ⬅ Day 092 · Day 094 ➡ · Journal index

🎯 Learning Objective

Use pgx/v5 as a native Postgres driver, run queries through a pgxpool.Pool, and understand what a connection pool buys you and how to size it.

📚 Topics

• pgx's two modes: native interface vs database/sql compatibility
• pgxpool.New / pgxpool.ParseConfig; Acquire/Release vs direct pool methods
• DSN / connection-string anatomy; pool sizing knobs

📖 Reading / Sources

• pgx v5 — pkg.go.dev
• pgxpool — pkg.go.dev
• pgx GitHub — "Choosing Between the pgx and database/sql Interfaces"
• Postgres — Connection strings

📝 Notes

• pgx is a pure-Go Postgres driver and toolkit. It offers two ways in: the native pgx interface (pgxpool.Pool, richer types, COPY, LISTEN/NOTIFY, batching) and a database/sql shim via github.com/jackc/pgx/v5/stdlib. Use native for new code; use the shim when a library demands *sql.DB → [[sql-drivers]].
• A connection pool amortises the expensive TCP + TLS + auth handshake to Postgres. Without it you'd pay milliseconds per query just to connect. The pool keeps N live connections and lends them out → [[connection-pooling]]. I modelled this stdlib-only in examples/month-04/pool.
• pgxpool.New(ctx, connString) returns a *pgxpool.Pool that is safe for concurrent use by many goroutines. Like *sql.DB, create it once and share it.
• Two usage styles: call pool.Query/QueryRow/Exec directly (the pool auto-acquires and auto-releases a connection per call — the common case), or conn := pool.Acquire(ctx) + defer conn.Release() when you need several statements pinned to the same physical connection (e.g. a session-local setting or a transaction).
• Sizing: pool_max_conns in the connection string, or cfg.MaxConns after ParseConfig. A pool larger than Postgres's max_connections (default 100, shared across all clients) is a footgun; size for max_connections / replicas and consider a server-side pooler (PgBouncer) at scale.
• DSN anatomy: postgres://user:pass@host:5432/dbname?sslmode=require&pool_max_conns=10. Key/value form (host=... user=... sslmode=...) is equivalent. sslmode matters in prod (require/verify-full).
• pgx scans into Go types natively (no sql.NullString dance for many types). v5 adds helpers: pgx.CollectRows(rows, pgx.RowToStructByName[T]) maps result rows to a struct by column name.
• Always pass ctx first; a cancelled context aborts the query and returns the connection to the pool → [[context-propagation]].

💻 Code Examples

pgx is third-party, so this is a snippet. The pooling mechanics (bounded buffered channel, Acquire(ctx) respecting cancellation, defer Release) are runnable here: examples/month-04/pool · Run: go run ./examples/month-04/pool.

import (
    "context"
    "errors"

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

func newPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    cfg, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, err
    }
    cfg.MaxConns = 10 // cap pool size; keep below Postgres max_connections
    pool, err := pgxpool.NewWithConfig(ctx, cfg)
    if err != nil {
        return nil, err
    }
    if err := pool.Ping(ctx); err != nil { // force a real connection now
        pool.Close()
        return nil, err
    }
    return pool, nil
}

type User struct {
    ID    int
    Email string
}

func userByID(ctx context.Context, pool *pgxpool.Pool, id int) (User, error) {
    rows, err := pool.Query(ctx, `SELECT id, email FROM users WHERE id = $1`, id)
    if err != nil {
        return User{}, err
    }
    // CollectExactlyOneRow returns pgx.ErrNoRows when nothing matched.
    u, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByName[User])
    if errors.Is(err, pgx.ErrNoRows) {
        return User{}, errors.New("user not found")
    }
    return u, err
}

🏋️ Exercises / Practice

Exercise	Status	Link
Bounded connection pool with Acquire(ctx) / Release (concept)	✅	examples/month-04/pool
Repository over a pool-backed store	✅	exercises/month-04/week-2/inmemrepo

🐛 Mistakes Made

• Set MaxConns higher than Postgres's max_connections across several app replicas → "too many clients already". Sized down and learned the pool is per-process.
• Forgot defer conn.Release() after pool.Acquire and slowly starved the pool. The direct pool.Query style avoids this when you don't need a pinned connection.

❓ Open Questions

• When is PgBouncer (transaction-pooling mode) worth it, and which pgx features break under it (prepared statements / session state)?

🧠 Active Recall (answer without looking)

1. Q: Why prefer a pool over opening a fresh connection per query?

A

Each new connection pays a TCP + TLS + auth handshake (milliseconds). A pool keeps connections warm and lends them out, so you pay that cost once at startup, not per query.

1. Q: When do you need pool.Acquire instead of calling pool.Query directly?

A

When several statements must run on the *same physical connection* — a transaction, or a session-local setting (`SET ...`). Direct pool methods may pick a different connection each call.

🪶 Feynman Reflection

pgx talks to Postgres directly and fast; pgxpool is a coat-check for connections. You hand the pool a context and a query, it grabs a free connection, runs it, and reclaims it. Size the pool so all your app processes together don't out-number what the database will accept.

🕳️ Knowledge Gaps

• pgx's prepared-statement cache and how it interacts with connection poolers.

✅ Summary

I can build a pgxpool.Pool, choose between direct pool calls and Acquire/Release, read a Postgres DSN, and size a pool sensibly against max_connections.

⏭️ Next Steps / Prep for Tomorrow

• Day 094: sqlc — generating type-safe Go from plain SQL.

---

Time spent	Difficulty	Confidence
90 min	🟦🟦⬜⬜⬜	🟦🟦🟦⬜⬜

Suggested commit: docs(journal): pgx and pgxpool connection pooling (day 093)