When the universal translator gets in the way
You are building a service that stores complex data. Your schema uses PostgreSQL arrays for tags, JSONB for flexible payloads, and range types for scheduling. You reach for the standard database/sql package because it is the Go default. You quickly discover that the standard library treats every database like a flat spreadsheet. It strips away dialect-specific features to maintain compatibility across MySQL, SQLite, and Postgres. You end up writing custom marshaling code, wrestling with connection leaks, and losing performance on every query.
pgx drops the universal translator. It is a PostgreSQL-specific driver that speaks the database's native protocol directly. Instead of translating everything into generic SQL types, it maps PostgreSQL features straight to Go types. Arrays become slices. JSONB becomes []byte or structs. Ranges become native range types. You get built-in connection pooling, prepared statement caching, and row-level streaming without fighting the abstraction layer.
Think of database/sql like a universal travel adapter. It works everywhere, but you lose access to local features like grounded outlets or fast charging. pgx is plugging directly into the wall. You get the full voltage and all the local features, but you are tied to that specific outlet.
How the driver talks to the database
PostgreSQL communicates over a binary wire protocol. Every query travels as a series of framed messages. The client sends a startup packet, authentication data, and query commands. The server responds with parameter descriptions, row descriptions, and data rows. The standard library driver intercepts these messages, strips the type metadata, and converts everything into generic []byte or string values. You lose the type information before it reaches your code.
pgx reads the wire protocol directly. It keeps the type descriptors intact. When the database says a column is JSONB, the driver knows it is JSONB. When it says a column is INT4[], the driver knows it is an array of 32-bit integers. This direct mapping removes the reflection overhead and custom marshaling that plagues generic drivers. You get faster execution, lower memory allocation, and type safety that the compiler can verify.
Connection pooling happens at the driver level. The pool maintains a minimum and maximum number of open TCP connections. When a query finishes, the connection goes back into the ready queue. If the pool is exhausted, new queries block until a connection is released. This prevents the classic too many connections crash that happens when every request opens a fresh TCP socket.
Pool management is automatic. Trust the pool size defaults or tune them based on your database's max_connections limit.
The minimal setup
Here is the simplest way to connect, query, and scan a row. The pool handles connection reuse automatically.
package main
import (
"context"
"fmt"
"log"
"os"
"github.com/jackc/pgx/v5/pgxpool"
)
type User struct {
ID int
Name string
}
func main() {
// Read connection string from environment or fall back to local default
connStr := os.Getenv("DATABASE_URL")
if connStr == "" {
connStr = "postgres://user:password@localhost:5432/mydb?sslmode=disable"
}
// Create a pool that manages a set of persistent connections
pool, err := pgxpool.New(context.Background(), connStr)
if err != nil {
log.Fatalf("failed to initialize pool: %v", err)
}
defer pool.Close() // Returns all connections to the OS when main exits
// Execute a query and scan the result directly into a struct field
var u User
err = pool.QueryRow(context.Background(), "SELECT id, name FROM users WHERE id = $1", 1).Scan(&u.ID, &u.Name)
if err != nil {
log.Fatalf("query failed: %v", err)
}
fmt.Printf("Found user: %d - %s\n", u.ID, u.Name)
}
The pool initializes a set of connections to the database. When you call QueryRow, it borrows a connection from the pool, sends the query, and returns the connection automatically when the row is scanned. You never manage individual connections manually. The context.Background() call provides a cancellation signal. If the query hangs or the parent request times out, the context tells the driver to abort the network call and release the connection back to the pool.
Walking through the execution path
When your program starts, pgxpool.New dials the PostgreSQL server. It completes the SSL handshake, authenticates with the credentials in the connection string, and opens a baseline number of TCP sockets. These sockets sit idle in the pool, ready for work.
When QueryRow runs, the pool hands out one socket. The driver formats the SQL query into a binary Parse message, sends the parameters in a Bind message, and executes with an Execute message. The database processes the query and streams back a RowDescription followed by DataRow messages. pgx reads the row description to determine the column types. It then reads the data row and writes the bytes directly into the memory addresses you passed to Scan.
If you query multiple rows, you call Query instead of QueryRow. This returns a pgx.Rows iterator. You loop with rows.Next(), call rows.Scan() for each iteration, and check rows.Err() after the loop finishes. The iterator holds the connection for the entire duration of the loop. When you call rows.Close(), the connection returns to the pool. If you forget to close it, the connection stays checked out until the garbage collector runs, which can starve your pool under load.
Context is plumbing. Run it through every long-lived call site.
Handling complex types and transactions
Production code rarely runs single queries. You usually need atomic operations, context propagation, and proper error handling. Here is how a transaction looks with native pgx types and context cancellation.
func updateUserBalance(ctx context.Context, pool *pgxpool.Pool, userID int, amount float64) error {
// Start a transaction tied to the incoming context
tx, err := pool.BeginTx(ctx, pgx.TxOptions{})
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
// Rollback automatically if the function returns early with an error
defer tx.Rollback(ctx)
// Update the balance and check how many rows were affected
result, err := tx.Exec(ctx, "UPDATE accounts SET balance = balance + $1 WHERE user_id = $2", amount, userID)
if err != nil {
return fmt.Errorf("update balance: %w", err)
}
// Verify the update actually touched a row
rowsAffected := result.RowsAffected()
if rowsAffected == 0 {
return fmt.Errorf("account not found")
}
// Commit the transaction. If this fails, the deferred rollback runs but is harmless.
if err := tx.Commit(ctx); err != nil {
return fmt.Errorf("commit tx: %w", err)
}
return nil
}
The transaction borrows a single connection from the pool for its entire lifetime. All queries inside the transaction run on that same connection, which is required for ACID guarantees. The defer tx.Rollback(ctx) is a safety net. If Commit succeeds, the rollback becomes a no-op. If any step fails, the deferred call cleans up the database state. Context propagation ensures that if the HTTP request times out, the database operation cancels immediately instead of hanging until the TCP timeout expires.
Complex types work the same way. If you query a JSONB column, you can scan it into a []byte, a string, or a custom struct. If you want automatic struct mapping, you scan into []byte and pass it to json.Unmarshal. For arrays, you scan directly into a Go slice. The driver handles the boundary conversion. You do not need to write reflection-heavy marshaling code.
Don't fight the type system. Match the scan target to the column type or write a decoder.
Common pitfalls and compiler signals
The driver is strict about types and contexts. The compiler and runtime will catch mistakes early, but the error messages can feel verbose if you are used to dynamic languages.
If you pass a nil context or forget to pass one entirely, the compiler rejects the program with cannot use nil as context.Context in argument. Every query method requires a context as the first parameter. This is a hard convention in Go. The context controls timeouts, cancellation, and request-scoped values. Functions that accept a context should respect it and propagate it down the call stack. The receiver name for methods is usually one or two letters matching the type, but query methods follow the standard ctx, query, args... pattern.
Type mismatches during scanning trigger runtime errors. If you try to scan a PostgreSQL TEXT column into a Go int, the driver returns scan error: cannot convert text to int. The driver does not guess. You must match the Go type to the database type, or implement a custom decoder.
Forgetting to close a pgx.Rows iterator leaks a connection. The compiler cannot catch this, but the pool will eventually exhaust its connections and block new queries. Always defer rows.Close() immediately after calling Query().
Error wrapping with %w is standard practice. The if err != nil { return fmt.Errorf("...: %w", err) } pattern is verbose by design. The community accepts the boilerplate because it makes the failure path explicit and preserves the original error chain for debugging. Public names start with a capital letter. Private start lowercase. No keywords like public or private. This naming rule applies to your custom types and helper functions as well.
Goroutines are cheap. Database connections are not.
When to reach for pgx
Database drivers are a trade-off between portability and performance. Pick the right tool based on your project's constraints.
Use pgx native API when you need PostgreSQL-specific features like JSONB, arrays, full-text search, or row-level streaming. Use pgxpool when you are building a production service that requires automatic connection reuse, prepared statement caching, and predictable latency. Use the database/sql compatibility layer when you are migrating an existing codebase and cannot rewrite all query logic immediately. Use a single pgx connection instead of a pool only for administrative scripts or one-off migrations where connection overhead is irrelevant. Use standard library database/sql with a generic driver when your application must switch databases at runtime without recompilation.
Trust the pool. Measure your latency. Tune the max size.