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)

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.