pgxpool initialization with max connections and statement timeout

3543 views

                  package store

import (
  "context"
  "time"

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

func OpenPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
  cfg, err := pgxpool.ParseConfig(dsn)
  if err != nil {
    return nil, err
  }
  cfg.MaxConns = 25
  cfg.MaxConnLifetime = 30 * time.Minute
  cfg.MaxConnIdleTime = 5 * time.Minute
  cfg.AfterConnect = func(ctx context.Context, conn *pgxpool.Conn) error {
    _, err := conn.Exec(ctx, "SET statement_timeout = '2s'")
    return err
  }
  return pgxpool.NewWithConfig(ctx, cfg)
}

Postgres stability depends on respecting its limits. I configure pgxpool with explicit MaxConns and MaxConnLifetime so the service doesn't accidentally open too many connections during bursts. I also set a session statement_timeout in AfterConnect, which is a pragmatic safety net when a query goes bad: the database will cancel it instead of letting it run for minutes and pin a connection. This is not a substitute for indexes and query tuning, but it prevents one slow query from turning into a cascading failure. I like returning a small wrapper that includes a Ping method used by readiness checks and graceful shutdown hooks.

Leah Thompson

More from Leah Thompson

pgxpool initialization with max connections and statement timeout

0 Comments

More from Leah Thompson