Go + PostgreSQL Performance: pgx Driver Optimization

Go and PostgreSQL — A Natural Pairing, Worth Configuring Well

Go's goroutine model is well-suited to PostgreSQL's connection-per-query architecture. Each goroutine can independently acquire a connection, execute a query, and return the connection to the pool — all without blocking other goroutines. The language's built-in concurrency primitives (context.Context, channels, sync package) map directly to the patterns you need for reliable database access: timeouts, cancellation, and safe concurrent use. It is, if I may say so, an elegant match.

But this natural fit deserves better than the default configurations it typically receives. The standard database/sql package uses conservative pool settings. The pgx driver's automatic prepared statement caching creates complications with connection poolers. And Go's ease of spawning goroutines makes it straightforward to exhaust a connection pool that was sized for a different load profile.

This guide covers the full stack of Go PostgreSQL performance decisions: driver choice, connection pool tuning, batch operations, the COPY protocol, prepared statement management, goroutine-safe patterns, type mapping, and code generation with sqlc. Each section provides enough context to act on immediately, with links to dedicated deep-dives for topics that warrant extended treatment.

pgx vs database/sql — Choosing the Right Interface

Go developers accessing PostgreSQL have two primary paths: the standard database/sql interface or the pgx driver's native API. Understanding the trade-off between them is the first decision in any Go PostgreSQL project.

database/sql: The Standard Interface

Go's database/sql package defines a generic database interface. It works with any database that has a driver — PostgreSQL, MySQL, SQLite, and others. Code written against database/sql is portable and works with ORMs like GORM and code generators like sqlc.

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib" // pgx as the database/sql driver
)

db, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/mydb")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

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

The trade-off with database/sql is generality. Because it must work with every database, it uses interface{} (now any) for values and reflection-based scanning. Every row scan involves type assertions and reflection, which adds overhead per row.

pgx Native: PostgreSQL-Specific, Higher Performance

pgx's native API bypasses database/sql entirely. It provides direct access to PostgreSQL-specific features — LISTEN/NOTIFY, COPY, batch operations, custom types — and avoids the reflection overhead of database/sql's scanning:

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

pool, err := pgxpool.New(ctx, "postgres://user:pass@localhost:5432/mydb")
if err != nil {
    log.Fatal(err)
}
defer pool.Close()

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

The API is similar, but the implementation is different. pgx native uses PostgreSQL's binary protocol for type encoding, avoiding the string serialization that database/sql requires. For queries returning many rows with numeric, timestamp, or UUID columns, this produces measurable performance gains.

When to Choose Each

Use pgx native when starting a new project that will always use PostgreSQL, when you need PostgreSQL-specific features (COPY, LISTEN/NOTIFY, custom types), or when performance of row scanning matters (high-throughput data pipelines).

Use database/sql with pgx stdlib when your codebase already uses database/sql extensively, when you need ORM compatibility (GORM, ent), or when you want the option to switch databases in the future (rare in practice).

For a detailed comparison with benchmarks, see pgx Native vs database/sql.

Connection Pool Tuning with pgxpool

pgxpool.Pool is pgx's built-in connection pool. It manages a set of PostgreSQL connections and handles acquisition, release, health checking, and lifetime rotation. Every production Go application using pgx should use pgxpool — raw pgx.Conn connections are best reserved for short-lived scripts and one-off tools.

Configuration Parameters

config, err := pgxpool.ParseConfig("postgres://user:pass@localhost:5432/mydb")
if err != nil {
    log.Fatal(err)
}

// Pool size
config.MaxConns = 20                           // Maximum connections in pool
config.MinConns = 5                            // Pre-warmed connections kept alive

// Connection lifecycle
config.MaxConnLifetime = 1 * time.Hour         // Rotate connections after 1 hour
config.MaxConnIdleTime = 30 * time.Minute      // Close idle connections after 30 minutes
config.HealthCheckPeriod = 1 * time.Minute     // Background liveness checks

// Connection establishment
config.ConnConfig.ConnectTimeout = 5 * time.Second  // Fail fast on unreachable servers

pool, err := pgxpool.NewWithConfig(ctx, config)
if err != nil {
    log.Fatal(err)
}

MaxConns: The Ceiling

MaxConns is the most important pool parameter. Setting it too high wastes PostgreSQL resources and invites lock contention. Setting it too low creates goroutine pile-ups waiting for connections. The widely-cited formula from HikariCP applies equally to pgx:

MaxConns = (CPU cores * 2) + effective_spindle_count

For a PostgreSQL server with 8 CPU cores and SSD storage (spindle count effectively 1): (8 * 2) + 1 = 17. Round to 20 for headroom. This number is per application instance — if you run 4 replicas of your Go service, total PostgreSQL connections from that service are 4 × 20 = 80.

I should note what may seem counterintuitive: a smaller pool often performs better than a larger one. PostgreSQL's per-connection overhead (memory, lock contention, process scheduling) means that 20 connections with efficient query patterns outperform 200 connections competing for the same CPU and I/O.

MinConns: Pre-warming

MinConns keeps a minimum number of idle connections in the pool. This eliminates the latency of establishing a new TCP connection and completing the PostgreSQL authentication handshake on the first few requests after a cold start. For most services, 2–5 is appropriate.

MaxConnLifetime: Rotation

MaxConnLifetime forces connections to close after a set duration, regardless of whether they are idle. This prevents issues with stale server-side state: PostgreSQL server restarts, DNS changes, and memory leaks in PostgreSQL backends. Set this to 30–60 minutes. pgxpool adds jitter (up to 20% of the configured value) to prevent all connections from rotating simultaneously.

Monitoring Pool Statistics

pgxpool exposes pool statistics that you should monitor in production:

stats := pool.Stat()

log.Printf("Pool stats: total=%d, idle=%d, acquired=%d, constructing=%d",
    stats.TotalConns(),
    stats.IdleConns(),
    stats.AcquiredConns(),
    stats.ConstructingConns(),
)

log.Printf("Cumulative: acquireCount=%d, emptyAcquireCount=%d, canceledAcquireCount=%d",
    stats.AcquireCount(),
    stats.EmptyAcquireCount(),
    stats.CanceledAcquireCount(),
)

// Export to Prometheus, StatsD, or your metrics system

The key metric is EmptyAcquireCount — the number of times a goroutine tried to acquire a connection and found the pool empty. A rising EmptyAcquireCount means your pool is undersized for your workload, or — more commonly — your queries are holding connections longer than they should. Either way, it warrants investigation.

For detailed tuning guidance, see pgxpool Tuning for High Concurrency.

Pool Exhaustion and Goroutine Pressure

When all connections are in use, the next goroutine calling pool.Query() blocks until a connection becomes available. Without a timeout, that goroutine waits indefinitely. The remedy is context timeouts:

ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()

rows, err := pool.Query(ctx, "SELECT * FROM users WHERE active = true")
if err != nil {
    // err will be context.DeadlineExceeded if the pool couldn't provide
    // a connection within 5 seconds
    return fmt.Errorf("query failed: %w", err)
}
defer rows.Close()

The other common cause of pool exhaustion — and one I encounter with some regularity — is resource leaks. Every rows object returned by pool.Query() holds a connection until rows.Close() is called:

// BUG: connection leak if the loop breaks or returns early
rows, err := pool.Query(ctx, "SELECT id, name FROM users")
if err != nil {
    return err
}
// Missing: defer rows.Close()

for rows.Next() {
    var id int
    var name string
    if err := rows.Scan(&id, &name); err != nil {
        return err // Connection leaked!
    }
}

Always defer rows.Close() immediately after acquiring rows. The defer executes when the enclosing function returns, regardless of how it returns.

Batch Operations — Reducing Round Trips

Network round trips between your Go application and PostgreSQL add latency that deserves respect. Each query requires at minimum: send query, wait for PostgreSQL to process, receive results. On a network with 1ms latency, 100 individual queries take at least 100ms in round-trip time alone.

pgx's Batch type lets you queue multiple queries and send them all in a single round trip:

batch := &pgx.Batch{}

// Queue multiple queries — they can be different operations
batch.Queue("SELECT name FROM users WHERE id = $1", 1)
batch.Queue("SELECT count(*) FROM orders WHERE user_id = $1", 1)
batch.Queue("UPDATE users SET last_seen = now() WHERE id = $1", 1)

// Send the entire batch in one round trip
br := pool.SendBatch(ctx, batch)
defer br.Close()

// Read results in order
var name string
err := br.QueryRow().Scan(&name)
if err != nil {
    return err
}

var orderCount int
err = br.QueryRow().Scan(&orderCount)
if err != nil {
    return err
}

// Exec for statements without result rows
_, err = br.Exec()
if err != nil {
    return err
}

When to Use Batches

Batches are most valuable when you have multiple independent queries that are logically part of the same operation. Common cases:

• Dashboard loading: Fetch user profile, notification count, and recent activity in one round trip instead of three
• Bulk lookups: Check existence of 50 IDs without 50 individual queries
• Mixed operations: A read followed by a write that can be pipelined

I would recommend keeping batches under 1,000 operations for interactive workloads. For bulk data operations, the COPY protocol (covered in the next section) is better suited to the task.

For goroutine batching patterns, see Goroutine Query Batching.

COPY Protocol — Bulk Loading at Wire Speed

PostgreSQL's COPY protocol is the fastest way to move large amounts of data into a table. It bypasses the normal query parsing and planning overhead entirely, streaming rows directly into the table using a binary or text format.

rows := [][]any{
    {1, "Alice", "alice@example.com", time.Now()},
    {2, "Bob", "bob@example.com", time.Now()},
    {3, "Carol", "carol@example.com", time.Now()},
}

copyCount, err := pool.CopyFrom(
    ctx,
    pgx.Identifier{"users"},                          // Table name
    []string{"id", "name", "email", "created_at"},     // Column names
    pgx.CopyFromRows(rows),                            // Data source
)
if err != nil {
    return fmt.Errorf("copy failed: %w", err)
}
fmt.Printf("Copied %d rows\n", copyCount)

Performance Comparison

For bulk inserts of 10,000+ rows, COPY is typically 10–100x faster than batched INSERT statements. Benchmarks on a typical 8-core PostgreSQL server inserting 100,000 rows:

It is worth mentioning that COPY does not support RETURNING or ON CONFLICT. If you need the inserted IDs back, or you need upsert semantics, batched INSERT statements are the right tool for those situations.

For detailed benchmarks, see pgx Bulk Insert Benchmarks.

Prepared Statements — Explicit vs Implicit

PostgreSQL prepared statements separate query parsing/planning from execution. The first time a prepared statement runs, PostgreSQL parses and plans it. Subsequent executions reuse the plan, saving parsing and (potentially) planning time.

Query Execution Modes

pgx v5 provides four query execution modes, controlled by the DefaultQueryExecMode configuration:

config, _ := pgxpool.ParseConfig(connString)

// Default: cache statement — prepare on first use, cache the prepared statement
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement

// Cache describe — describe the query but use extended protocol without preparing
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeCacheDescribe

// Exec — use the extended protocol with no prepare or describe
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec

// Simple protocol — send query as a single string with parameters interpolated
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

QueryExecModeCacheStatement (default): pgx prepares each unique query on first use and caches the prepared statement name. Optimal for applications that execute the same queries repeatedly with different parameters — which describes most web applications.

QueryExecModeExec: Sends queries through the extended protocol without preparing them. A good middle ground when you want the extended protocol's type safety without the state of prepared statements.

QueryExecModeSimpleProtocol: Sends the query as a plain string with parameters interpolated client-side. No server-side state at all. This is the mode to use with PgBouncer in transaction mode.

The PgBouncer Problem

PgBouncer in transaction mode reassigns connections between clients after each transaction. A statement prepared on connection A does not exist on connection B. The remedy:

config, _ := pgxpool.ParseConfig(connString)
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec
config.ConnConfig.StatementCacheCapacity = 0  // Disable the statement cache entirely

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

For a complete guide to query execution modes, see pgx Query Exec Mode Guide.

Goroutine-Safe Patterns

pgxpool.Pool is safe for concurrent use by multiple goroutines — this is one of its most considerate design decisions. You do not need to add a mutex around pool access.

Collecting Results

pgx v5 provides helper functions that eliminate manual scanning boilerplate:

type User struct {
    ID    int       `db:"id"`
    Name  string    `db:"name"`
    Email string    `db:"email"`
}

// CollectRows + RowToStructByName: scan rows directly into structs
users, err := pgx.CollectRows(
    pool.Query(ctx, "SELECT id, name, email FROM users WHERE active = true"),
    pgx.RowToStructByName[User],
)
if err != nil {
    return nil, err
}
// users is []User, fully scanned and rows are already closed

pgx.CollectRows iterates through all rows, scans each one using the provided function, closes the rows, and returns the collected slice. This is safer than manual iteration because it ensures rows.Close() is always called.

Transactions

Transactions acquire and hold a connection for their duration:

tx, err := pool.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx) // No-op if already committed

_, err = tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
if err != nil {
    return err
}

_, err = tx.Exec(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
if err != nil {
    return err
}

return tx.Commit(ctx)

The defer tx.Rollback(ctx) pattern ensures the transaction is rolled back if the function returns early due to an error. If tx.Commit(ctx) succeeds, the subsequent rollback is a no-op.

Context Discipline

Every pgx database call takes a context.Context as its first parameter. This is not optional decoration — it is the mechanism for timeouts, cancellation, and request scoping. I would encourage you to treat it with the seriousness it deserves.

Request-scoped contexts in HTTP handlers ensure that database queries are cancelled when the client disconnects. Timeout contexts for background jobs prevent runaway queries from holding connections indefinitely. Cancellation for fan-out patterns uses errgroup.WithContext() — when one goroutine returns an error, the derived context is cancelled, aborting in-flight database queries in other goroutines.

Type Mapping and Custom Types

One of pgx native's most worthwhile advantages over database/sql is direct type mapping between Go types and PostgreSQL types. Where database/sql converts everything through strings or []byte, pgx uses PostgreSQL's binary protocol to encode and decode types without intermediate string representation.

JSONB

pgx can marshal Go structs directly to and from PostgreSQL JSONB columns without manual json.Marshal/json.Unmarshal:

type UserPreferences struct {
    Theme    string `json:"theme"`
    Timezone string `json:"timezone"`
}

// Reading JSONB into a struct
var prefs UserPreferences
err := pool.QueryRow(ctx,
    "SELECT preferences FROM users WHERE id = $1", userID,
).Scan(&prefs)

// Writing a struct to a JSONB column
_, err = pool.Exec(ctx,
    "UPDATE users SET preferences = $1 WHERE id = $2",
    prefs, userID,
)

For queries returning thousands of rows with numeric columns, pgx's binary protocol eliminates thousands of strconv calls per query. The savings are quiet but they accumulate.

sqlc and Code Generation

sqlc takes a different approach to Go database access, and one I find rather agreeable: you write SQL queries, and sqlc generates type-safe Go code that calls those queries. It is often described as the "anti-ORM" — instead of hiding SQL behind method chains, it puts SQL at the center and generates the boilerplate around it.

Configuration

version: "2"
sql:
  - schema: "schema.sql"
    queries: "queries.sql"
    engine: "postgresql"
    gen:
      go:
        package: "db"
        out: "internal/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_empty_slices: true

Query Definitions

-- name: GetUser :one
SELECT id, name, email, created_at
FROM users
WHERE id = $1;

-- name: ListActiveUsers :many
SELECT id, name, email
FROM users
WHERE active = true
ORDER BY name;

-- name: CreateUser :one
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING id, name, email, created_at;

-- name: UpdateUserEmail :exec
UPDATE users SET email = $2 WHERE id = $1;

Running sqlc generate produces Go functions with typed parameters and return values:

// Generated by sqlc — you never edit this file
func (q *Queries) GetUser(ctx context.Context, id int32) (User, error) { ... }
func (q *Queries) ListActiveUsers(ctx context.Context) ([]User, error) { ... }
func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) { ... }
func (q *Queries) UpdateUserEmail(ctx context.Context, arg UpdateUserEmailParams) error { ... }

When sqlc Excels

sqlc is strongest when your team has strong SQL skills and prefers writing SQL directly, your queries are complex (CTEs, window functions, lateral joins), you want compile-time guarantees that your queries match your schema, and you want to minimize runtime reflection and overhead.

The choice need not be binary. Some projects use sqlc for performance-critical queries and GORM for everyday CRUD — a sensible division of labor. For GORM-specific optimization, see GORM Preload vs Joins.

The Checklist

Allow me to present a prioritized summary of configuration and patterns: