CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/golang-github-com-jackc-pgx-v5

pgx is a pure Go driver and toolkit for PostgreSQL providing a native high-performance interface with PostgreSQL-specific features plus a database/sql compatibility adapter.

—

Pending
Overview
Eval results
Files

connection-pool.mddocs/

Connection Pool (pgxpool)

pgxpool is a concurrency-safe connection pool. It provides an interface nearly identical to pgx.Conn but safe for use from multiple goroutines simultaneously. This is the recommended approach for production applications.

Import

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

When to Use

Use pgxpool.Pool for:

  • Web servers and APIs
  • Long-running services
  • Any application with concurrent database access
  • Applications that need automatic connection health checks

Creating a Pool

func New(ctx context.Context, connString string) (*Pool, error)
func NewWithConfig(ctx context.Context, config *Config) (*Pool, error)
func ParseConfig(connString string) (*Config, error)

Simple Creation

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}
defer pool.Close()

New returns immediately without waiting for connections. Acquire a connection immediately to verify connectivity:

pool, err := pgxpool.New(ctx, connString)
if err != nil {
    return err
}
defer pool.Close()

// Verify connectivity
if err := pool.Ping(ctx); err != nil {
    return err
}

With Custom Config

config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}

config.MaxConns = 20
config.MinConns = 5
config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
    // Set up connection (e.g. prepare statements, register types)
    t, err := conn.LoadType(ctx, "my_enum")
    if err != nil {
        return err
    }
    conn.TypeMap().RegisterType(t)
    return nil
}

pool, err := pgxpool.NewWithConfig(ctx, config)

Pool Config

type Config struct {
    ConnConfig *pgx.ConnConfig

    // Hooks
    BeforeConnect func(context.Context, *pgx.ConnConfig) error
    AfterConnect  func(context.Context, *pgx.Conn) error
    BeforeAcquire func(context.Context, *pgx.Conn) bool // deprecated: use PrepareConn
    PrepareConn   func(context.Context, *pgx.Conn) (bool, error)
    AfterRelease  func(*pgx.Conn) bool
    BeforeClose   func(*pgx.Conn)
    ShouldPing    func(context.Context, ShouldPingParams) bool

    // Pool size
    MaxConns  int32         // default: max(4, runtime.NumCPU())
    MinConns  int32         // default: 0
    MinIdleConns int32      // default: 0

    // Lifetimes
    MaxConnLifetime       time.Duration // default: 1 hour
    MaxConnLifetimeJitter time.Duration // default: 0
    MaxConnIdleTime       time.Duration // default: 30 minutes
    PingTimeout           time.Duration // default: no timeout
    HealthCheckPeriod     time.Duration // default: 1 minute
}

func (c *Config) Copy() *Config
func (c *Config) ConnString() string

PrepareConn Behavior

The PrepareConn hook is called before a connection is returned from the pool. Return values:

  • (true, nil): connection is valid, proceed
  • (true, err): return connection to pool, fail query with err
  • (false, err): destroy connection, fail query with err
  • (false, nil): destroy connection, retry with a new connection

Connection String Pool Parameters

You can configure pool settings in the connection string:

postgres://user:pass@host/db?pool_max_conns=10&pool_min_conns=2&pool_max_conn_lifetime=1h30m&pool_max_conn_idle_time=30m&pool_health_check_period=1m&pool_max_conn_lifetime_jitter=5m

ShouldPingParams

type ShouldPingParams struct {
    Conn         *pgx.Conn
    IdleDuration time.Duration
}

Default ShouldPing behavior: ping connections idle for ≥ 1 second.

Pool Methods

Query Methods

Direct query methods (acquire and release automatically):

func (p *Pool) Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
func (p *Pool) Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
func (p *Pool) QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
func (p *Pool) CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
func (p *Pool) SendBatch(ctx context.Context, b *pgx.Batch) pgx.BatchResults

Transaction Methods

func (p *Pool) Begin(ctx context.Context) (pgx.Tx, error)
func (p *Pool) BeginTx(ctx context.Context, txOptions pgx.TxOptions) (pgx.Tx, error)

Connection Management

func (p *Pool) Acquire(ctx context.Context) (*Conn, error)
func (p *Pool) AcquireAllIdle(ctx context.Context) []*Conn
func (p *Pool) AcquireFunc(ctx context.Context, f func(*Conn) error) error
func (p *Pool) Close()
func (p *Pool) Config() *Config
func (p *Pool) Ping(ctx context.Context) error
func (p *Pool) Reset()
func (p *Pool) Stat() *Stat

Acquired Conn

type Conn struct { /* unexported */ }

func (c *Conn) Release()
func (c *Conn) Conn() *pgx.Conn
func (c *Conn) Hijack() *pgx.Conn
func (c *Conn) Begin(ctx context.Context) (pgx.Tx, error)
func (c *Conn) BeginTx(ctx context.Context, txOptions pgx.TxOptions) (pgx.Tx, error)
func (c *Conn) Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
func (c *Conn) Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
func (c *Conn) QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
func (c *Conn) CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
func (c *Conn) SendBatch(ctx context.Context, b *pgx.Batch) pgx.BatchResults
func (c *Conn) Ping(ctx context.Context) error
  • Release() returns the connection to the pool. Must be called exactly once. Safe to call multiple times (subsequent calls are ignored).
  • Hijack() takes ownership; caller is responsible for closing. Panics if called on a released/hijacked connection.

Pool Transaction (Tx)

type Tx struct { /* unexported */ }

func (tx *Tx) Begin(ctx context.Context) (pgx.Tx, error)
func (tx *Tx) Commit(ctx context.Context) error
func (tx *Tx) Rollback(ctx context.Context) error
func (tx *Tx) Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
func (tx *Tx) Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
func (tx *Tx) QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
func (tx *Tx) CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
func (tx *Tx) SendBatch(ctx context.Context, b *pgx.Batch) pgx.BatchResults
func (tx *Tx) LargeObjects() pgx.LargeObjects
func (tx *Tx) Prepare(ctx context.Context, name, sql string) (*pgconn.StatementDescription, error)
func (tx *Tx) Conn() *pgx.Conn

Commit or Rollback return the associated connection to the pool.

Pool Statistics

type Stat struct { /* unexported */ }

func (s *Stat) TotalConns() int32
func (s *Stat) AcquiredConns() int32
func (s *Stat) IdleConns() int32
func (s *Stat) ConstructingConns() int32
func (s *Stat) MaxConns() int32
func (s *Stat) AcquireCount() int64
func (s *Stat) AcquireDuration() time.Duration
func (s *Stat) CanceledAcquireCount() int64
func (s *Stat) EmptyAcquireCount() int64
func (s *Stat) EmptyAcquireWaitTime() time.Duration
func (s *Stat) NewConnsCount() int64
func (s *Stat) MaxLifetimeDestroyCount() int64
func (s *Stat) MaxIdleDestroyCount() int64

Formula: TotalConns = ConstructingConns + AcquiredConns + IdleConns

Tracer Interfaces

type AcquireTracer interface {
    TraceAcquireStart(ctx context.Context, pool *Pool, data TraceAcquireStartData) context.Context
    TraceAcquireEnd(ctx context.Context, pool *Pool, data TraceAcquireEndData)
}

type ReleaseTracer interface {
    TraceRelease(pool *Pool, data TraceReleaseData)
}

type TraceAcquireStartData struct{}

type TraceAcquireEndData struct {
    Conn *pgx.Conn
    Err  error
}

type TraceReleaseData struct {
    Conn *pgx.Conn
}

Implement these on the connection's tracer (set via Config.ConnConfig.Tracer) to trace pool acquire/release events. The tracelog.TraceLog and multitracer.Tracer both implement these.

Usage Examples

Basic Direct Query

var name string
err := pool.QueryRow(ctx, "SELECT name FROM users WHERE id = $1", 42).Scan(&name)

Query Multiple Rows

rows, err := pool.Query(ctx, "SELECT id, name FROM users WHERE active = $1", true)
if err != nil {
    return err
}
defer rows.Close()

type User struct {
    ID   int64
    Name string
}

users, err := pgx.CollectRows(rows, pgx.RowToStructByPos[User])

Acquire and Release

conn, err := pool.Acquire(ctx)
if err != nil {
    return err
}
defer conn.Release()

rows, err := conn.Query(ctx, "SELECT * FROM users")
// ... process rows

AcquireFunc (Auto-Release)

err := pool.AcquireFunc(ctx, func(conn *pgxpool.Conn) error {
    _, err := conn.Exec(ctx, "UPDATE users SET last_seen = now() WHERE id = $1", id)
    return err
})

Transaction

tx, err := pool.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx)

_, err = tx.Exec(ctx, "INSERT INTO orders(user_id, amount) VALUES ($1, $2)", userID, amount)
if err != nil {
    return err
}

return tx.Commit(ctx)

Or with BeginFunc:

err := pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
    _, err := tx.Exec(ctx, "INSERT INTO orders(user_id, amount) VALUES ($1, $2)", userID, amount)
    return err
})

Reset Pool

pool.Reset() // closes all connections; pool remains open for new connections

Use after server restart or failover.

Monitor Pool Health

stats := pool.Stat()
fmt.Printf("Total conns: %d, Acquired: %d, Idle: %d\n",
    stats.TotalConns(), stats.AcquiredConns(), stats.IdleConns())

Best Practices

  1. Create once, use everywhere: Create a single pool at application startup and reuse it.
  2. Set appropriate pool size: Default is max(4, NumCPU). Increase for high-concurrency workloads.
  3. Use AfterConnect for setup: Register custom types, prepare statements, or set session variables.
  4. Monitor pool statistics: Use pool.Stat() to track pool health and tune configuration.
  5. Set MinConns for latency: Pre-establish connections to avoid startup latency on first request.
  6. Use health checks: Keep HealthCheckPeriod at default (1 minute) for automatic bad connection removal.
  7. Don't acquire unnecessarily: Use direct query methods (pool.Query, etc.) when possible; they acquire/release automatically.

Install with Tessl CLI

npx tessl i tessl/golang-github-com-jackc-pgx-v5

docs

batch.md

common-patterns.md

connection-pool.md

copy.md

database-sql.md

direct-connection.md

index.md

querying.md

testing.md

tracing.md

transactions.md

tile.json