pgx vs database sql Mode and What You Give Up by Switching

pgx can be used two ways, and the choice is made once and lived with. The native interface is pgx's own API: pgxpool, pgx.Conn, CollectRows, batches. The database/sql adapter, github.com/jackc/pgx/v5/stdlib, registers pgx as a driver so everything built on the standard library works with it: *sql.DB, sqlx, GORM, most migration tools. They share one driver and one connection string, and they behave differently in ways that matter. This article measures both on PostgreSQL 18.6 from Go 1.25.6, shows the types that change shape, and says when each is right.

It belongs to the database cornerstone. The native API is introduced in the pgx tour, the standard one in how database/sql works, and the broader library choice in the library comparison.

Speed

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

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

var typeMap = pgtype.NewMap()

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

type Item struct {
	ID    int64
	Name  string
	Price string
	Tags  []string
}

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func best(n int, fn func()) time.Duration {
	b := time.Duration(1 << 62)
	for i := 0; i < 5; i++ {
		start := time.Now()
		for j := 0; j < n; j++ {
			fn()
		}
		if d := time.Since(start); d < b {
			b = d
		}
	}
	return b / time.Duration(n)
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	std, err := sql.Open("pgx", dsn)
	must(err)
	defer std.Close()
	for _, q := range []string{
		"drop table if exists items666",
		"create table items666 (id bigint primary key, name text not null, price numeric(10,2) not null, tags text[] not null default '{}', meta jsonb)",
		"insert into items666 select i, 'item ' || i, i / 7.0, array['a','b'], jsonb_build_object('n', i) from generate_series(1, 5000) i",
	} {
		_, err := pool.Exec(ctx, q)
		must(err)
	}

	// 1. The same list of 1,000 rows, read both ways.
	listNative := func() {
		rows, _ := pool.Query(ctx, "select id, name, price::text, tags from items666 order by id limit 1000")
		_, err := pgx.CollectRows(rows, pgx.RowToStructByPos[Item])
		must(err)
	}
	listStd := func() {
		rows, err := std.QueryContext(ctx, "select id, name, price::text, tags from items666 order by id limit 1000")
		must(err)
		defer rows.Close()
		var items []Item
		for rows.Next() {
			var it Item
			must(rows.Scan(&it.ID, &it.Name, &it.Price, typeMap.SQLScanner(&it.Tags)))
			items = append(items, it)
		}
		must(rows.Err())
	}
	getNative := func() {
		var name string
		must(pool.QueryRow(ctx, "select name from items666 where id = $1", 2500).Scan(&name))
	}
	getStd := func() {
		var name string
		must(std.QueryRowContext(ctx, "select name from items666 where id = $1", 2500).Scan(&name))
	}
	fmt.Println("1. time per call, best of 5 passes")
	fmt.Printf("   list 1,000 rows:  native %v, database/sql %v\n", best(50, listNative).Round(time.Microsecond), best(50, listStd).Round(time.Microsecond))
	fmt.Printf("   get one by id:    native %v, database/sql %v\n", best(2000, getNative).Round(time.Microsecond), best(2000, getStd).Round(time.Microsecond))

	// 2. Scanning an array through database/sql needs a helper; the native scan does not.
	var tags []string
	err = std.QueryRowContext(ctx, "select tags from items666 where id = 1").Scan(&tags)
	fmt.Println("2. database/sql scanning a text[] into []string:", err)
	err = std.QueryRowContext(ctx, "select tags from items666 where id = 1").Scan(typeMap.SQLScanner(&tags))
	fmt.Println("   with a pgtype.Map scanner:", tags, err)
	err = pool.QueryRow(ctx, "select tags from items666 where id = 1").Scan(&tags)
	fmt.Println("   native:", tags, err)

	// 3. What the types look like on each side.
	var nStd any
	must(std.QueryRowContext(ctx, "select price from items666 where id = 7").Scan(&nStd))
	var nNative pgtype.Numeric
	must(pool.QueryRow(ctx, "select price from items666 where id = 7").Scan(&nNative))
	var tStd any
	must(std.QueryRowContext(ctx, "select now()").Scan(&tStd))
	fmt.Printf("3. numeric through database/sql arrives as %T; natively as %T with Valid=%v\n", nStd, nNative, nNative.Valid)
	fmt.Printf("   timestamptz arrives as %T through database/sql\n", tStd)

	// 4. Features that exist only on the native side, reached from database/sql through the driver connection.
	conn, err := std.Conn(ctx)
	must(err)
	defer conn.Close()
	var copied int64
	must(conn.Raw(func(driverConn any) error {
		pc := driverConn.(*stdlib.Conn).Conn()
		n, err := pc.CopyFrom(ctx, pgx.Identifier{"items666"}, []string{"id", "name", "price"}, pgx.CopyFromRows([][]any{{10001, "copied", "1.00"}, {10002, "copied", "2.00"}}))
		copied = n
		return err
	}))
	fmt.Println("4. CopyFrom reached through Conn.Raw from a *sql.Conn:", copied, "rows")

	// 5. Batches are native only; the standard interface has no equivalent, so a loop pays a round trip each time.
	b := &pgx.Batch{}
	for i := 20000; i < 20500; i++ {
		b.Queue("insert into items666 (id, name, price) values ($1, 'b', 1)", i)
	}
	start := time.Now()
	must(pool.SendBatch(ctx, b).Close())
	batched := time.Since(start)
	start = time.Now()
	for i := 21000; i < 21500; i++ {
		_, err := std.ExecContext(ctx, "insert into items666 (id, name, price) values ($1, 'b', 1)", i)
		must(err)
	}
	fmt.Printf("5. 500 inserts: native batch %v, database/sql loop %v\n", batched.Round(time.Millisecond), time.Since(start).Round(time.Millisecond))

	// 6. Both share one driver, so the same server errors arrive.
	_, err1 := pool.Exec(ctx, "insert into items666 (id, name, price) values (1, 'dup', 1)")
	_, err2 := std.ExecContext(ctx, "insert into items666 (id, name, price) values (1, 'dup', 1)")
	fmt.Printf("6. same SQLSTATE on both sides: %v\n", err1.Error() == err2.Error())
}

1. time per call, best of 5 passes
   list 1,000 rows:  native 750µs, database/sql 1.113ms
   get one by id:    native 191µs, database/sql 186µs
2. database/sql scanning a text[] into []string: sql: Scan error on column index 0, name "tags": unsupported Scan, storing driver.Value type string into type *[]string
   with a pgtype.Map scanner: [a b] <nil>
   native: [a b] <nil>
3. numeric through database/sql arrives as string; natively as pgtype.Numeric with Valid=true
   timestamptz arrives as time.Time through database/sql
4. CopyFrom reached through Conn.Raw from a *sql.Conn: 2 rows
5. 500 inserts: native batch 3ms, database/sql loop 212ms
6. same SQLSTATE on both sides: true

The first block times two operations through both interfaces, taking the best of five passes. A single row lookup took 191µs natively and 186µs through database/sql: the same. The time is the round trip and the server's work, and the interface adds a few microseconds either way. Anyone telling you the adapter makes single queries slower is measuring noise.

Listing 1,000 rows was different: 750µs with CollectRows and 1.113ms through database/sql. The adapter reads each value from pgx, converts it to a driver.Value (a string for most types), and database/sql then converts again into your variable; native scanning decodes the binary value straight into the destination. For small results it does not show. For large ones, thousands of rows or wide rows, it is a 30 to 50 percent difference on the read path, and less garbage to collect on the native side. The cost of the standard interface is conversion, not network.

Types

The second and third blocks show the other cost: the standard interface has fewer types than PostgreSQL, so values come out in a lowest common denominator form.

• Arrays. Scanning a text[] into a []string through database/sql failed: unsupported Scan, storing driver.Value type string into type *[]string. The driver returned the array in PostgreSQL's text form, and database/sql cannot parse it. The fix is a scanner from a pgtype.Map, typeMap.SQLScanner(&tags), which the lab uses to read [a b]. Natively, Scan(&tags) just works.
• numeric. Through the adapter it arrived as a string, to avoid losing precision. Natively it arrives as pgtype.Numeric with a Valid flag, or you can register a decimal type.
• timestamptz arrived as time.Time on both sides.
• json, uuid, ranges, composite types all need pgtype helpers on the standard side and have direct support natively.

Where a library such as GORM or sqlx dictates database/sql, this is the price, and the usual answer is to use the helpers or to scan problem columns as text and convert them.

What exists only natively

The fifth block is the clearest loss. Batches are native only: 500 inserts took 3ms as one SendBatch and 212ms in a database/sql loop, about 70 times as long, because the loop pays a round trip for each statement and the standard interface has no way to pipeline. LISTEN, CopyFrom and CopyTo, per connection configuration, custom type registration and the tracing hooks are native features too.

The adapter does not lock you out of them, which the fourth block shows. sql.Conn.Raw hands you the driver connection, and driverConn.(*stdlib.Conn).Conn() returns the *pgx.Conn underneath. The lab used it to run CopyFrom and loaded two rows. So a codebase on database/sql can still reach for a native feature in the one place that needs it, though it is clumsy to do often, and it must hold a connection for the duration (db.Conn(ctx)).

What stays the same

Server errors are identical on both sides: the sixth block compared a duplicate insert through both and the error text and SQLSTATE matched, because both come from pgconn. Use errors.As with *pgconn.PgError either way. The connection string, TLS settings, target_session_attrs and pool settings in the URL apply to both. Prepared statement caching, cancellation by context and the wire protocol are the same driver. So moving between them is mostly a matter of the API and types, not of behaviour.

Pooling and configuration differences

The two interfaces pool differently, and that is easy to miss when you switch. Native pgxpool has its own settings (MaxConns, MinConns, MaxConnLifetime, health checks, AfterConnect and BeforeAcquire hooks) described in pgxpool. The adapter is a *sql.DB, so it uses database/sql's pool with SetMaxOpenConns, SetMaxIdleConns and SetConnMaxLifetime, covered in connection pool settings, and the driver underneath applies the connection string's own options. The practical differences: the native pool exposes finer statistics (EmptyAcquireCount, acquire durations), the native pool can keep a minimum number of connections warm, and database/sql retries some failed operations on a fresh connection on its own, a behaviour pgxpool leaves to you.

Both cache prepared statements per connection by default, which is why the same SQL text is fast the second time on either side. That cache is also what breaks behind transaction mode poolers, discussed in pgx with PgBouncer, and the fix, a different query execution mode, is a connection setting that applies to both. If you run both interfaces against one server, remember that they are separate pools: add their sizes when you budget connections, or build the *sql.DB from the native pool with stdlib.OpenDBFromPool.

A migration path that costs little

If you are on database/sql and want native features without a rewrite, you do not have to switch everything. Keep the existing code and open a pgxpool.Pool beside it for the paths that need batches, COPY or precise types, such as an import job or a hot read endpoint. Put both behind the interfaces your code already uses, so callers do not know which one serves them. Move a repository at a time, with the contract tests described in moving to sqlc one query at a time, since the same technique applies: a seam, a test that pins behaviour and small steps. If after a few months nothing uses the adapter except libraries that require it, you have reached the state this article recommends, native for your code and the adapter for theirs.

Choosing

Mixing is fine and common, with one care: two pools mean two sets of connections against the same server's max_connections, so size them together. A cleaner way is to build the *sql.DB from the same native pool with stdlib.OpenDBFromPool(pool), so there is one set of connections serving both views.

Two migration notes. Moving from the adapter to native, replace rows.Scan loops with CollectRows, sql.NullString with pointers or pgtype, and sql.ErrNoRows with pgx.ErrNoRows (the adapter maps the native error to sql.ErrNoRows, so code that tests for the standard one keeps working while it migrates). And moving the other way, plan for the type changes in the table above. If you are choosing for a new project and PostgreSQL is the database, native is the better default; the adapter is for compatibility, not for preference. The adapter is documented in the stdlib package reference, and the native API in the pgx reference.