Geospatial Portal

Configuring PostGIS Connection Pooling with PgBouncer

How to place PgBouncer in transaction-pooling mode in front of a PostGIS StatefulSet so hundreds of application connections collapse onto a small, safe set of backend sessions.

This page is an operational companion to Kubernetes StatefulSets for PostGIS Databases and lives inside the larger Infrastructure Orchestration & Configuration Management practice; read the parent guide for how the database itself is provisioned, then use this one to keep GeoServer, GeoNode, and Celery workers from exhausting its connection ceiling. The goal is narrow: a pooler that multiplexes many short-lived client connections onto few long-lived server sessions without breaking spatial workloads.

Prerequisites

Have these in place before deploying the pooler, because each governs whether transaction pooling is safe for your traffic.

  • A running PostGIS StatefulSet (PostgreSQL 16 / PostGIS 3.4) reachable on a headless Service, e.g. postgis-headless:5432.
  • PgBouncer 1.22+ (the edoburu/pgbouncer or bitnami/pgbouncer image, or a sidecar build).
  • A read of the server’s ceiling: SHOW max_connections; — pool sizing is derived from this number, not guessed.
  • Knowledge of which clients use session-level state (SET, LISTEN/NOTIFY, prepared statements, advisory locks) — these are incompatible with transaction pooling and must route around it.
  • kubectl write access to the geoportal namespace and permission to create a Secret, ConfigMap, Deployment, and Service.

Step-by-step implementation

The pooler sits between the application tier and the database. Many client connections terminate at PgBouncer; only a handful of server connections continue to PostGIS, and each is loaned to a client for the duration of a single transaction. The diagram shows that fan-in.

PgBouncer collapses many client connections onto a small server pool Three application sources on the left — GeoServer, GeoNode web workers, and Celery ingestion workers — open a large number of client connections, illustrated as many thin lines. They all terminate at a central PgBouncer node configured for transaction pooling with a default pool size of twenty and a max client connection limit of one thousand. On the right, PgBouncer maintains only a small fixed set of server connections to the PostGIS StatefulSet on port five four three two, well under the server's max_connections of two hundred. A caption notes that each server connection is loaned per transaction and returned to the pool. GeoServer GeoNode web Celery workers many client conns PgBouncer pool_mode = transaction default_pool_size = 20 max_client_conn = 1000 few server conns PostGIS StatefulSet :5432 max_connections = 200 each server connection is loaned per transaction, then returned to the pool

1. Choose transaction pooling deliberately

PgBouncer offers three modes; the choice is a correctness decision, not a tuning knob. Transaction pooling returns the server connection to the pool at every COMMIT, giving the highest multiplexing — ideal for GeoServer and stateless GeoNode requests. It forbids session-scoped state, so any client relying on LISTEN/NOTIFY, session SET, or server-side prepared statements must use a separate session-pooling port or connect directly.

session      → one server conn per client for the whole connection lifetime
transaction  → server conn held only for a transaction (highest reuse, no session state)
statement    → server conn released at statement end (no multi-statement transactions)

Route the bulk of read/write traffic through a transaction-mode database entry and reserve a session-mode entry for the few clients that need it.

2. Write pgbouncer.ini

The core config declares the upstream database, the pool mode, and the ceilings. Point server_reset_query at DISCARD ALL only for session pooling; in transaction mode leave it empty to avoid a wasted round-trip after every transaction.

[databases]
geoportal = host=postgis-headless port=5432 dbname=geoportal

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM pgbouncer.get_auth($1)

pool_mode = transaction
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_client_conn = 1000
max_db_connections = 40
server_reset_query =
server_idle_timeout = 300
ignore_startup_parameters = extra_float_digits

ignore_startup_parameters = extra_float_digits is not optional for PostGIS clients: many drivers (and GeoServer’s JDBC layer) send extra_float_digits at startup, and without this line PgBouncer rejects the connection outright.

3. Provide credentials via userlist or auth_query

For a static roster, hash credentials into userlist.txt. For a database that already manages roles, prefer auth_query, which lets PgBouncer look each user up in a dedicated, tightly-scoped function so you are not maintaining a parallel password file.

-- Run once on the PostGIS server: a SECURITY DEFINER lookup PgBouncer can call
CREATE ROLE pgbouncer LOGIN PASSWORD 'change-me';

CREATE SCHEMA IF NOT EXISTS pgbouncer AUTHORIZATION pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename text)
RETURNS TABLE(usename text, passwd text)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$
  SELECT rolname::text, rolpassword::text
  FROM pg_authid
  WHERE rolname = p_usename AND rolcanlogin;
$$;

REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO pgbouncer;

Keep the static fallback file in a Secret so the pooler can still start if the auth query is briefly unavailable.

"geoportal_app" "SCRAM-SHA-256$4096:...=$...=:...="

4. Deploy PgBouncer in front of the StatefulSet

Run PgBouncer as its own Deployment with two or three replicas plus a Service on 6432. A dedicated Deployment scales and upgrades independently of both the apps and the database; a sidecar is only preferable when a single pod must own its pooler. Mount the ConfigMap and Secret read-only.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
  namespace: geoportal
spec:
  replicas: 2
  selector:
    matchLabels: { app: pgbouncer }
  template:
    metadata:
      labels: { app: pgbouncer }
    spec:
      securityContext:
        runAsNonRoot: true
        runAsUser: 70
      containers:
        - name: pgbouncer
          image: edoburu/pgbouncer:1.22.1
          ports:
            - { containerPort: 6432, name: pgbouncer }
          volumeMounts:
            - { name: config, mountPath: /etc/pgbouncer, readOnly: true }
          readinessProbe:
            tcpSocket: { port: 6432 }
            initialDelaySeconds: 5
            periodSeconds: 10
      volumes:
        - name: config
          projected:
            sources:
              - configMap: { name: pgbouncer-config }
              - secret: { name: pgbouncer-userlist }
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
  namespace: geoportal
spec:
  selector: { app: pgbouncer }
  ports:
    - { port: 6432, targetPort: 6432, name: pgbouncer }

5. Point applications at 6432 and size the pool against the ceiling

Change every application’s connection string to target the pooler Service, not PostGIS directly. The critical arithmetic: total backend connections must stay under the server’s max_connections. With two PgBouncer replicas each holding max_db_connections = 40, the database sees at most 80 sessions — leaving headroom under a max_connections of 200 for replication, admin, and pg_dump.

apiVersion: v1
kind: ConfigMap
metadata:
  name: geoserver-db-env
  namespace: geoportal
data:
  POSTGRES_HOST: "pgbouncer"
  POSTGRES_PORT: "6432"
  POSTGRES_DB: "geoportal"

Size the pool from a formula, not a guess: replicas × max_db_connections + reserved_admin_slots ≤ server max_connections. The reasoning behind that server ceiling — and how to raise it safely for spatial workloads — is worked through in Optimizing PostgreSQL/PostGIS Connection Limits. The StatefulSet those numbers apply to is provisioned in Deploying PostGIS on Kubernetes with Persistent Volumes.

Verification

Confirm clients route through the pooler and that the backend session count stays bounded under load.

# 1. Reach the pooler and open its admin console
psql "host=pgbouncer port=6432 dbname=pgbouncer user=pgbouncer" -c "SHOW POOLS;"
#   database   user           cl_active  sv_active  sv_idle  pool_mode
#   geoportal  geoportal_app  altogether well above sv_active  transaction

# 2. The server never exceeds the pooled ceiling even under many clients
psql "host=pgbouncer port=6432 dbname=geoportal user=geoportal_app" \
  -c "SELECT count(*) FROM pg_stat_activity WHERE datname='geoportal';"
#   count stays <= replicas * max_db_connections (e.g. <= 80)

# 3. Spatial queries still work through the pooler
psql "host=pgbouncer port=6432 dbname=geoportal user=geoportal_app" \
  -c "SELECT ST_AsText(ST_Centroid('LINESTRING(0 0, 2 2)'));"
#   POINT(1 1)

# 4. Client-side pool saturation shows as waiting, not errors
psql "host=pgbouncer port=6432 dbname=pgbouncer user=pgbouncer" -c "SHOW STATS;"

A cl_waiting count that briefly rises and drains under burst traffic is healthy back-pressure; a count in pg_stat_activity that stays under the computed ceiling confirms the pool is doing its job.

Troubleshooting matrix

Symptom Likely cause Fix
Clients fail with unsupported startup parameter: extra_float_digits PgBouncer rejects the driver’s startup param Add ignore_startup_parameters = extra_float_digits (and options if needed)
Prepared statements break with prepared statement "S_1" does not exist Server-side prepared statements under transaction pooling Disable server-side prepared statements in the driver, or route that client to a session-pooling port
pg_stat_activity still hits max_connections max_db_connections × replicas exceeds the server ceiling Recompute the pool budget; lower max_db_connections or default_pool_size
LISTEN/NOTIFY events never arrive Transaction pooling drops the session that registered the listener Use a dedicated session-mode database entry for the notifying client
Auth fails only for some users auth_query function missing rows or pg_authid not readable Grant EXECUTE on the SECURITY DEFINER lookup; verify the role rolcanlogin
Idle app connections pin server sessions pool_mode = session where transaction was intended Set pool_mode = transaction for stateless traffic; leave server_reset_query empty
First query after idle is slow server_idle_timeout closes warm connections aggressively Raise server_idle_timeout and min_pool_size to keep warm sessions ready

For the exact meaning of each pool mode and setting, the official PgBouncer configuration reference is authoritative.

Up one level: Kubernetes StatefulSets for PostGIS Databases.