postgres

postgres

Package postgres provides an instrumented PostgreSQL client that implements app.Component, plugging into the app.App lifecycle for managed startup and graceful shutdown.

Quick start

db, err := postgres.NewWithConfig(&postgres.Config{
    DSN:    os.Getenv("DATABASE_URL"),
    Tracer: a.Tracer(),
})
if err != nil {
    log.Fatal(err) // DSN parse errors are caught here, before Start
}

a.Register(db) // Start creates the pool and pings; Shutdown closes it

// After a.Start(ctx):
rows, err := db.DB().QueryContext(ctx, "SELECT id FROM users WHERE active = $1", true)

Two access paths

The client exposes two ways to interact with the database after Start:

DB() — standard *sql.DB

db.DB().QueryContext(ctx, "SELECT id, name FROM projects WHERE active = $1", true)
db.DB().ExecContext(ctx, "UPDATE users SET active = false WHERE id = $1", id)

Compatible with ORMs (sqlx, GORM, Bun), query builders, and migration frameworks (golang-migrate, goose).

Pool() — pgx-native *pgxpool.Pool

// Bulk load via COPY
conn, err := db.Pool().Acquire(ctx)
defer conn.Release()
_, err = conn.Conn().PgConn().CopyFrom(ctx, pgconn.CopyFromReader(r, sql))

// Batch queries — send many, read all results at once
batch := &pgx.Batch{}
batch.Queue("INSERT INTO events (name) VALUES ($1)", "signup")
batch.Queue("INSERT INTO events (name) VALUES ($1)", "purchase")
br := db.Pool().SendBatch(ctx, batch)
defer br.Close()

// LISTEN / NOTIFY for pub-sub patterns
conn, _ := db.Pool().Acquire(ctx)
defer conn.Release()
_, err = conn.Exec(ctx, "LISTEN channel_name")

Both paths share the same connection pool and query tracer — OpenTelemetry spans are recorded regardless of which path is used.

Capability comparison

Use DB() for most CRUD workloads. Use Pool() for bulk inserts, high-throughput pipelines, or event-driven patterns.

Configuration

db, err := postgres.NewWithConfig(&postgres.Config{
    DSN:  "postgres://user:pass@localhost:5432/mydb",
    Name: "primary", // component name in logs (default: "postgres")

    // Connection pool tuning:
    MaxConns:        20,               // max open connections (0 = pgxpool default)
    MinConns:        2,                // min connections to keep warm (0 = none)
    ConnMaxLifetime: 5 * time.Minute,  // recycle connections after this duration
    ConnMaxIdleTime: 60 * time.Second, // close idle connections after this duration

    // Query execution mode (default: pgx.QueryExecModeSimpleProtocol for
    // PgBouncer compatibility). Override only for direct PostgreSQL connections.
    // QueryExecMode: pgx.QueryExecModeCacheStatement,

    Tracer: a.Tracer(), // nil disables query tracing
})

PgBouncer

The client defaults to pgx.QueryExecModeSimpleProtocol, which avoids server-side prepared statements. This is required for PgBouncer in transaction-pooling mode (the standard deployment at GitLab.com) and works correctly with direct PostgreSQL connections too. No additional configuration is needed for PgBouncer compatibility.

Also set ConnMaxLifetime and ConnMaxIdleTime to recycle stale connections:

db, err := postgres.NewWithConfig(&postgres.Config{
    DSN:             "postgres://user:pass@pgbouncer:6432/mydb",
    ConnMaxLifetime: 5 * time.Minute,
    ConnMaxIdleTime: 60 * time.Second,
})

When connecting directly to PostgreSQL without a pooler, you can opt into cached prepared statements for better query performance:

db, err := postgres.NewWithConfig(&postgres.Config{
    DSN:           "postgres://user:pass@localhost:5432/mydb",
    QueryExecMode: pgx.QueryExecModeCacheStatement,
})

Query tracing

When Tracer is set, every query automatically receives an OpenTelemetry span:

• Span name: SQL verb — "db SELECT", "db INSERT", etc. (low cardinality)
• Attributes: db.system = "postgresql", db.statement = <full SQL>, db.name = <database name>
• Errors: recorded on the span automatically

Multiple databases

Give each client a distinct Name so log lines and error messages are unambiguous:

primary, _ := postgres.NewWithConfig(&postgres.Config{Name: "primary", DSN: primaryDSN})
replica, _ := postgres.NewWithConfig(&postgres.Config{Name: "replica", DSN: replicaDSN})

a.Register(primary)
a.Register(replica)

Connection parameters for another client library

postgres.New resolves the PostgreSQL entry of the infrastructure contract and the Kubernetes Secret it names into a DSN: the Secret keys follow the secret_ref format (CNPG, CRUNCHY, ZALANDO or a CUSTOM projection), a value set inline in the contract wins over the Secret, and the contract may declare a connection pooler next to the database. Services that keep their own driver would otherwise have to reimplement these rules. postgres.ConnectionParams applies them and returns the parameters without opening a connection; Params.DSN and Params.DirectDSN render them as postgres:// URLs:

infra, err := infrastructure.DefaultConfig()
if err != nil {
    log.Fatal(err)
}

params, err := postgres.ConnectionParams(ctx, infra)
if err != nil {
    log.Fatal(err)
}

// Application traffic goes through the pooler when the contract declares one;
// migrations and long-running queries connect to the database itself.
pool, err := pgxpool.New(ctx, params.DSN())
if err != nil {
    log.Fatal(err)
}

migrations, err := pgx.Connect(ctx, params.DirectDSN())
if err != nil {
    log.Fatal(err)
}

HasPassword reports whether the contract declares a secret_ref, so Password was read from the Secret; it may be empty. PoolHost and PoolPort equal Host and Port unless the contract declares a pooler, which Pooled reports; the two resolve independently, so a contract that declares only one of them keeps the database's value for the other. Params.URL and Params.DirectURL return the same URLs as *url.URL values for a consumer that edits them first, to keep the credentials out of the DSN or to add query parameters. Password is a secret.Secret and redacts itself when printed.

Testing

Tests that don't need a real database can inspect the configured tracer via QueryTracer() without calling Start:

db, _ := postgres.NewWithConfig(&postgres.Config{
    DSN:    "postgres://localhost/mydb",
    Tracer: tracer,
})
assert.NotNil(t, db.QueryTracer())
assert.Nil(t, db.DB())   // nil before Start
assert.Nil(t, db.Pool()) // nil before Start

For integration tests, use a real PostgreSQL instance and call Start / Shutdown directly.