Go and PostgreSQL: Advanced pgx, pgxpool, and Transaction Techniques

Introduction: Why pgx Instead of database/sql

Go's standard database/sql interface is designed to be universal, but that very universality becomes a bottleneck when working seriously with PostgreSQL. The pgx library by jackc provides direct access to the PostgreSQL protocol, bypassing the database/sql abstraction layer, which offers several fundamental advantages.

Native support for PostgreSQL types: JSONB, arrays, UUID, pgtype.Numeric — without unnecessary conversions.
Support for batch queries via SendBatch, which drastically reduces the number of round-trips.
Built-in connection pool pgxpool with fine-grained health-check and timeout configuration.
Support for the COPY protocol for bulk data insertion.
Direct use of prepared statements at the protocol level.

For Go developers building high-load services on PostgreSQL, migrating from database/sql + lib/pq to pgx v5 is one of the most cost-effective technical decisions of 2024–2026. Installation:

go get github.com/jackc/pgx/v5\ngo get github.com/jackc/pgx/v5/pgxpool

Configuring pgxpool: Pool Size, Timeouts, and Health Checks

The connection pool is the central element of high-performance PostgreSQL usage in Go. pgxpool manages the lifecycle of connections, their reuse, and health verification.

package main\n\nimport (\n    "context"\n    "fmt"\n    "log"\n    "time"\n\n    "github.com/jackc/pgx/v5/pgxpool"\n)\n\nfunc NewPool(dsn string) (*pgxpool.Pool, error) {\n    config, err := pgxpool.ParseConfig(dsn)\n    if err != nil {\n        return nil, fmt.Errorf("parse config: %w", err)\n    }\n\n    // Maximum number of connections in the pool\n    config.MaxConns = 30\n    // Minimum number of idle connections\n    config.MinConns = 5\n    // Maximum connection lifetime\n    config.MaxConnLifetime = 1 * time.Hour\n    // Maximum connection idle time\n    config.MaxConnIdleTime = 30 * time.Minute\n    // Health check period\n    config.HealthCheckPeriod = 1 * time.Minute\n    // Timeout for establishing a connection\n    config.ConnConfig.ConnectTimeout = 5 * time.Second\n\n    // Health check: execute SELECT 1 after acquiring a connection\n    config.BeforeAcquire = func(ctx context.Context, conn *pgxpool.Conn) bool {\n        return conn.Ping(ctx) == nil\n    }\n\n    // Hook after releasing a connection\n    config.AfterRelease = func(conn *pgx.Conn) bool {\n        // Reset prepared statements if the connection was in an error state\n        return conn.IsClosed() == false\n    }\n\n    pool, err := pgxpool.NewWithConfig(context.Background(), config)\n    if err != nil {\n        return nil, fmt.Errorf("create pool: %w", err)\n    }\n\n    return pool, nil\n}

A few important rules when configuring pgxpool:

MaxConns should not exceed PostgreSQL's max_connections minus connections reserved for replication and administrative tasks. A practical rule: MaxConns = (number of CPU cores * 2) + number of disks.
MinConns keeps "warm" connections available and avoids latency spikes during traffic bursts.
MaxConnLifetime prevents the accumulation of long-lived connections that may hold resources on the PostgreSQL side.
Don't overuse BeforeAcquire with Ping — it adds latency to every connection acquisition. Enable it only when dealing with stuck connections.

Working with Transactions: Explicit Transactions, Savepoints, and Error Handling

Transactions in PostgreSQL via pgx require explicit management. A common mistake is ignoring rollback on panic or error.

func TransferFunds(ctx context.Context, pool *pgxpool.Pool, fromID, toID int64, amount float64) error {\n    tx, err := pool.Begin(ctx)\n    if err != nil {\n        return fmt.Errorf("begin tx: %w", err)\n    }\n    // Guarantee rollback on function exit with error\n    defer func() {\n        if err != nil {\n            _ = tx.Rollback(ctx)\n        }\n    }()\n\n    // Debit\n    _, err = tx.Exec(ctx,\n        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",\n        amount, fromID,\n    )\n    if err != nil {\n        return fmt.Errorf("debit: %w", err)\n    }\n\n    // Savepoint before credit\n    _, err = tx.Exec(ctx, "SAVEPOINT before_credit")\n    if err != nil {\n        return fmt.Errorf("savepoint: %w", err)\n    }\n\n    // Credit\n    _, err = tx.Exec(ctx,\n        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",\n        amount, toID,\n    )\n    if err != nil {\n        // Roll back to savepoint, not to the beginning of the transaction\n        if rbErr := tx.Exec(ctx, "ROLLBACK TO SAVEPOINT before_credit"); rbErr != nil {\n            return fmt.Errorf("rollback to savepoint: %w", rbErr)\n        }\n        return fmt.Errorf("credit: %w", err)\n    }\n\n    // Log the operation within the same transaction\n    _, err = tx.Exec(ctx,\n        "INSERT INTO audit_log (from_id, to_id, amount, created_at) VALUES ($1, $2, $3, NOW())",\n        fromID, toID, amount,\n    )\n    if err != nil {\n        return fmt.Errorf("audit log: %w", err)\n    }\n\n    return tx.Commit(ctx)\n}

For isolation levels, use pool.BeginTx with an explicit level specified:

tx, err := pool.BeginTx(ctx, pgx.TxOptions{\n    IsoLevel:   pgx.Serializable,\n    AccessMode: pgx.ReadWrite,\n})

The "function with transaction" pattern is a convenient wrapper for reuse:

func WithTx(ctx context.Context, pool *pgxpool.Pool, fn func(pgx.Tx) error) error {\n    tx, err := pool.Begin(ctx)\n    if err != nil {\n        return err\n    }\n    defer func() {\n        if p := recover(); p != nil {\n            _ = tx.Rollback(ctx)\n            panic(p)\n        } else if err != nil {\n            _ = tx.Rollback(ctx)\n        } else {\n            err = tx.Commit(ctx)\n        }\n    }()\n    err = fn(tx)\n    return err\n}

Prepared Statements and Their Impact on Performance

pgx automatically caches prepared statements in extended query protocol mode. On the first execution, the query is parsed and planned on the PostgreSQL side; subsequent calls use the cached plan.

// Explicit statement preparation\nfunc PrepareStatements(ctx context.Context, conn *pgx.Conn) error {\n    _, err := conn.Prepare(ctx, "get_user_by_email",\n        "SELECT id, name, email, created_at FROM users WHERE email = $1 AND deleted_at IS NULL",\n    )\n    if err != nil {\n        return fmt.Errorf("prepare get_user_by_email: %w", err)\n    }\n    return nil\n}\n\n// Using a prepared statement\nfunc GetUserByEmail(ctx context.Context, conn *pgx.Conn, email string) (*User, error) {\n    var u User\n    err := conn.QueryRow(ctx, "get_user_by_email", email).Scan(\n        &u.ID, &u.Name, &u.Email, &u.CreatedAt,\n    )\n    if err != nil {\n        return nil, err\n    }\n    return &u, nil\n}

An important nuance with pgxpool: prepared statements are bound to a specific connection. When using a pool, it is recommended to use automatic caching via QueryExecModeSimpleProtocol or configure StatementCacheCapacity in the connection config:

config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement\nconfig.ConnConfig.StatementCacheCapacity = 512

Working with PostgreSQL Custom Types: JSONB, Arrays, UUID

One of pgx's greatest strengths is native support for PostgreSQL types without unnecessary serialization overhead.

JSONB

import "github.com/jackc/pgx/v5/pgtype"\n\ntype UserSettings struct {\n    Theme    string `json:"theme"`\n    Language string `json:"language"`\n    Notifications bool `json:"notifications"`\n}\n\nfunc SaveUserSettings(ctx context.Context, pool *pgxpool.Pool, userID int64, settings UserSettings) error {\n    data, err := json.Marshal(settings)\n    if err != nil {\n        return err\n    }\n    _, err = pool.Exec(ctx,\n        "UPDATE users SET settings = $1 WHERE id = $2",\n        data, userID,\n    )\n    return err\n}\n\nfunc GetUserSettings(ctx context.Context, pool *pgxpool.Pool, userID int64) (*UserSettings, error) {\n    var raw []byte\n    err := pool.QueryRow(ctx,\n        "SELECT settings FROM users WHERE id = $1",\n        userID,\n    ).Scan(&raw)\n    if err != nil {\n        return nil, err\n    }\n    var s UserSettings\n    if err := json.Unmarshal(raw, &s); err != nil {\n        return nil, err\n    }\n    return &s, nil\n}

PostgreSQL Arrays

// Inserting an array of tags\nfunc AddTags(ctx context.Context, pool *pgxpool.Pool, articleID int64, tags []string) error {\n    _, err := pool.Exec(ctx,\n        "UPDATE articles SET tags = $1::text[] WHERE id = $2",\n        tags, articleID,\n    )\n    return err\n}\n\n// Reading an array\nfunc GetTags(ctx context.Context, pool *pgxpool.Pool, articleID int64) ([]string, error) {\n    var tags []string\n    err := pool.QueryRow(ctx,\n        "SELECT tags FROM articles WHERE id = $1",\n        articleID,\n    ).Scan(&tags)\n    return tags, err\n}

UUID

import "github.com/google/uuid"\n\nfunc CreateOrder(ctx context.Context, pool *pgxpool.Pool, userID int64) (uuid.UUID, error) {\n    id := uuid.New()\n    _, err := pool.Exec(ctx,\n        "INSERT INTO orders (id, user_id, status, created_at) VALUES ($1, $2, 'pending', NOW())",\n        id, userID,\n    )\n    if err != nil {\n        return uuid.Nil, err\n    }\n    return id, nil\n}

Batch Queries with pgx: Reducing Round-Trips

Batching is one of pgx's most powerful tools for high-load scenarios. Instead of N separate queries, you send a single batch and read N results in a single round-trip.

func GetMultipleUsers(ctx context.Context, pool *pgxpool.Pool, ids []int64) ([]*User, error) {\n    conn, err := pool.Acquire(ctx)\n    if err != nil {\n        return nil, err\n    }\n    defer conn.Release()\n\n    batch := &pgx.Batch{}\n    for _, id := range ids {\n        batch.Queue("SELECT id, name, email FROM users WHERE id = $1", id)\n    }\n\n    results := conn.SendBatch(ctx, batch)\n    defer results.Close()\n\n    var users []*User\n    for range ids {\n        var u User\n        err := results.QueryRow().Scan(&u.ID, &u.Name, &u.Email)\n        if err != nil {\n            return nil, fmt.Errorf("scan user: %w", err)\n        }\n        users = append(users, &u)\n    }\n\n    return users, nil\n}\n\n// Batch insert\nfunc BulkInsertEvents(ctx context.Context, pool *pgxpool.Pool, events []Event) error {\n    conn, err := pool.Acquire(ctx)\n    if err != nil {\n        return err\n    }\n    defer conn.Release()\n\n    batch := &pgx.Batch{}\n    for _, e := range events {\n        batch.Queue(\n            "INSERT INTO events (user_id, type, payload, created_at) VALUES ($1, $2, $3, $4)",\n            e.UserID, e.Type, e.Payload, e.CreatedAt,\n        )\n    }\n\n    br := conn.SendBatch(ctx, batch)\n    defer br.Close()\n\n    for i := range events {\n        if _, err := br.Exec(); err != nil {\n            return fmt.Errorf("insert event %d: %w", i, err)\n        }\n    }\n    return nil\n}

For bulk insertion of hundreds of thousands of rows, use the COPY protocol — it is even faster than batch queries:

func BulkCopyUsers(ctx context.Context, pool *pgxpool.Pool, users []User) error {\n    conn, err := pool.Acquire(ctx)\n    if err != nil {\n        return err\n    }\n    defer conn.Release()\n\n    rows := make([][]interface{}, len(users))\n    for i, u := range users {\n        rows[i] = []interface{}{u.Name, u.Email, u.CreatedAt}\n    }\n\n    _, err = conn.Conn().CopyFrom(\n        ctx,\n        pgx.Identifier{"users"},\n        []string{"name", "email", "created_at"},\n        pgx.CopyFromRows(rows),\n    )\n    return err\n}

Concurrent Updates: Optimistic Locking and SELECT FOR UPDATE

In highly concurrent systems, properly handling parallel updates is critical. pgx provides convenient tooling for both approaches.

SELECT FOR UPDATE (Pessimistic Locking)

func ReserveProduct(ctx context.Context, pool *pgxpool.Pool, productID int64, quantity int) error {\n    return WithTx(ctx, pool, func(tx pgx.Tx) error {\n        var stock int\n        err := tx.QueryRow(ctx,\n            "SELECT stock FROM products WHERE id = $1 FOR UPDATE",\n            productID,\n        ).Scan(&stock)\n        if err != nil {\n            return fmt.Errorf("lock product: %w", err)\n        }\n\n        if stock < quantity {\n            return fmt.Errorf("insufficient stock: have %d, want %d", stock, quantity)\n        }\n\n        _, err = tx.Exec(ctx,\n            "UPDATE products SET stock = stock - $1 WHERE id = $2",\n            quantity, productID,\n        )\n        return err\n    })\n}

Optimistic Locking via version/updated_at

type Product struct {\n    ID      int64\n    Name    string\n    Price   float64\n    Version int // Version counter\n}\n\nfunc UpdateProductOptimistic(ctx context.Context, pool *pgxpool.Pool, p Product) error {\n    result, err := pool.Exec(ctx,\n        `UPDATE products \n         SET name = $1, price = $2, version = version + 1 \n         WHERE id = $3 AND version = $4`,\n        p.Name, p.Price, p.ID, p.Version,\n    )\n    if err != nil {\n        return fmt.Errorf("update: %w", err)\n    }\n\n    if result.RowsAffected() == 0 {\n        return fmt.Errorf("optimistic lock conflict: product %d was modified concurrently", p.ID)\n    }\n    return nil\n}\n\n// Retry wrapper for optimistic locking\nfunc WithOptimisticRetry(maxAttempts int, fn func() error) error {\n    for i := 0; i < maxAttempts; i++ {\n        err := fn()\n        if err == nil {\n            return nil\n        }\n        // Retry only on version conflict\n        if strings.Contains(err.Error(), "optimistic lock conflict") {\n            time.Sleep(time.Duration(i*10) * time.Millisecond)\n            continue\n        }\n        return err\n    }\n    return fmt.Errorf("exceeded max retry attempts (%d)", maxAttempts)\n}

Connection Pool Monitoring and Bottleneck Diagnostics

pgxpool exposes a Stat() method for retrieving the current pool state. Integrate it with Prometheus or any other monitoring system.

import (\n    "github.com/prometheus/client_golang/prometheus"\n    "github.com/prometheus/client_golang/prometheus/promauto"\n)\n\nvar (\n    poolAcquiredConns = promauto.NewGauge(prometheus.GaugeOpts{\n        Name: "pgxpool_acquired_connections",\n        Help: "Number of currently acquired connections",\n    })\n    poolIdleConns = promauto.NewGauge(prometheus.GaugeOpts{\n        Name: "pgxpool_idle_connections",\n        Help: "Number of idle connections in the pool",\n    })\n    poolTotalConns = promauto.NewGauge(prometheus.GaugeOpts{\n        Name: "pgxpool_total_connections",\n        Help: "Total number of connections in the pool",\n    })\n    poolWaitCount = promauto.NewCounter(prometheus.CounterOpts{\n        Name: "pgxpool_wait_total",\n        Help: "Total number of times waited for a connection",\n    })\n)\n\nfunc MonitorPool(pool *pgxpool.Pool, interval time.Duration) {\n    ticker := time.NewTicker(interval)\n    defer ticker.Stop()\n    for range ticker.C {\n        stat := pool.Stat()\n        poolAcquiredConns.Set(float64(stat.AcquiredConns()))\n        poolIdleConns.Set(float64(stat.IdleConns()))\n        poolTotalConns.Set(float64(stat.TotalConns()))\n        poolWaitCount.Add(float64(stat.EmptyAcquireCount()))\n    }\n}

Key metrics for analyzing PostgreSQL performance from a Go application:

AcquiredConns / MaxConns — if close to 100%, increase the pool size or optimize your queries.
EmptyAcquireCount — the number of times a goroutine waited for a free connection. A rising value signals an undersized pool.
MaxConnLifetimeDestroyCount — frequent connection recreation due to lifetime expiry may indicate a MaxConnLifetime that is too short.

For diagnosing slow queries, use pg_stat_statements on the PostgreSQL side and slow query logging on the Go side:

// Middleware for logging slow queries\ntype LoggingQuerier struct {\n    pool      *pgxpool.Pool\n    threshold time.Duration\n    logger    *slog.Logger\n}\n\nfunc (lq *LoggingQuerier) QueryRow(ctx context.Context, sql string, args ...any) pgx.Row {\n    start := time.Now()\n    row := lq.pool.QueryRow(ctx, sql, args...)\n    elapsed := time.Since(start)\n    if elapsed > lq.threshold {\n        lq.logger.WarnContext(ctx, "slow query",\n            "sql", sql,\n            "duration_ms", elapsed.Milliseconds(),\n        )\n    }\n    return row\n}

Docker Integration for Local Development

For reproducible local development with PostgreSQL, use Docker Compose. Below is a minimal configuration with performance tuning settings.

version: '3.8'\nservices:\n  postgres:\n    image: postgres:16-alpine\n    environment:\n      POSTGRES_DB: myapp\n      POSTGRES_USER: myapp\n      POSTGRES_PASSWORD: secret\n    ports:\n      - "5432:5432"\n    volumes:\n      - postgres_data:/var/lib/postgresql/data\n      - ./init.sql:/docker-entrypoint-initdb.d/init.sql\n    command: >\n      postgres\n        -c max_connections=200\n        -c shared_buffers=256MB\n        -c effective_cache_size=768MB\n        -c work_mem=4MB\n        -c log_min_duration_statement=100\n        -c log_statement=all\n    healthcheck:\n      test: ["CMD-SHELL", "pg_isready -U myapp"]\n      interval: 5s\n      timeout: 5s\n      retries: 5\n\nvolumes:\n  postgres_data:

In your Go code, use environment variables for the DSN so the configuration works both locally and in production:

func main() {\n    dsn := os.Getenv("DATABASE_URL")\n    if dsn == "" {\n        dsn = "postgres://myapp:secret@localhost:5432/myapp?sslmode=disable"\n    }\n\n    pool, err := NewPool(dsn)\n    if err != nil {\n        log.Fatalf("failed to create pool: %v", err)\n    }\n    defer pool.Close()\n\n    // Verify the connection at startup\n    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)\n    defer cancel()\n\n    if err := pool.Ping(ctx); err != nil {\n        log.Fatalf("failed to ping database: %v", err)\n    }\n    log.Println("Connected to PostgreSQL")\n}

Conclusion and Best Practices

Working with PostgreSQL from Go via pgx is not just a matter of choosing a driver — it is a complete ecosystem of tools for building reliable, high-performance services. Let's summarize the key practices:

Always use pgxpool in production code. Direct connections via pgx.Connect are only appropriate for administrative tasks and tests.
Configure MaxConns deliberately: base it on the capacity of your PostgreSQL server, not arbitrary numbers.
Always defer rollback: the defer tx.Rollback() pattern is safe — it is a no-op after a successful Commit.
Use batch queries for operations that can be grouped together. The savings on round-trips are especially noticeable under high network latency.
Native types over strings: UUID, JSONB, arrays — use them directly; don't convert to string unless necessary.
Monitor the pool: pgxpool metrics should be part of your service's observability from day one.
COPY protocol for bulk inserts: when you need to insert thousands of rows, COPY is 5–10× faster than batch INSERT.
Savepoints for partial rollbacks in complex transactions — this is a standard PostgreSQL feature, don't overlook it.
Log slow queries: configure log_min_duration_statement in PostgreSQL and add middleware in Go for correlation.
Docker for local development with a healthcheck ensures the application starts only after the database is ready.

pgx is actively evolving: v5 brought an improved API for type handling, more flexible query execution control, and better performance. Keep an eye on releases and update your dependencies — it's a direct investment in the performance of your Go service on PostgreSQL.

Go and PostgreSQL: Advanced Techniques with pgx, Connection Pooling, and Transactions