Database Pooling with Pgcat

Pgcat is a PostgreSQL pooler with sharding, load balancing, and failover support. It provides server-side connection pooling, allowing multiple Permify replicas to share a centralized connection pool, reducing the total number of connections to your PostgreSQL database.For more information, see the pgcat repository.

Use pgcat for server-side pooling when running multiple Permify replicas or when your Postgres connection budget is tight.

When to use pgcat vs direct Postgres

pgcat (recommended): Centralized pooling/multiplexing across all Permify pods. Easier scaling, fewer server connections.
Direct Postgres: Use a small warm client pool in Permify to avoid cold dials during bursts.

Installation

Docker (quick start)

Create a pgcat.toml (see “Reference pgcat configuration” below).
Run:

docker run -d   --name pgcat   -p 6432:6432   -v $(pwd)/pgcat.toml:/etc/pgcat/pgcat.toml   postgresml/pgcat:latest

Kubernetes (standalone service)

Create a ConfigMap from your pgcat.toml:

kubectl create configmap pgcat-config --from-file=pgcat.toml

Deploy pgcat:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgcat
spec:
  replicas: 2
  selector:
    matchLabels:
      app: pgcat
  template:
    metadata:
      labels:
        app: pgcat
    spec:
      containers:
      - name: pgcat
        image: postgresml/pgcat:latest
        ports:
        - containerPort: 6432
        volumeMounts:
        - name: cfg
          mountPath: /etc/pgcat
      volumes:
      - name: cfg
        configMap:
          name: pgcat-config
---
apiVersion: v1
kind: Service
metadata:
  name: pgcat
spec:
  selector:
    app: pgcat
  ports:
  - port: 6432
    targetPort: 6432

Update network policies/security groups so Permify pods can reach the pgcat Service.

Kubernetes (sidecar pattern)

Run pgcat as a sidecar next to Permify in the same Pod for very small deployments. In most cases a shared pgcat Service is simpler to operate and scale.

Reference pgcat configuration (session mode)

File: pgcat.toml

[general]
host = "0.0.0.0"
port = 6432
enable_prometheus_exporter = false
prometheus_exporter_port = 9930
connect_timeout = 5000                       # ms
healthcheck_timeout = 1000                   # ms
healthcheck_delay = 30000                    # ms
shutdown_timeout = 60000                     # ms
ban_time = 60                                # s
log_client_connections = false
log_client_disconnections = false
admin_username = "postgres"
admin_password = "ADMIN_PASSWORD"
worker_threads = 4

[pools.postgres]
pool_mode = "session"
default_role = "any"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true
sharding_function = "pg_bigint_hash"

[pools.postgres.users.0]
username = "postgres"
password = "DB_USER_PASSWORD"
pool_size = 20                               # max server connections for this user
statement_timeout = 0

[pools.postgres.shards.0]
servers = [
  [ "PRIMARY_HOST", 5432, "primary" ],
  [ "REPLICA_HOST", 5432, "replica" ]
]
database = "permify"

Notes:

Keep Permify max_connection_idle_time lower than pgcat’s idle/server timeouts so the client drops first.

Permify → Pgcat configuration

Keep the client pool small; pgcat owns the warm pool.

Query parameters for session mode:

plan_cache_mode=force_custom_plan: Forces PostgreSQL to create custom plans for each execution instead of reusing generic plans. Prevents plan cache conflicts when pgcat reuses backend connections across different client sessions. See PostgreSQL documentation.
default_query_exec_mode=cache_describe: Configures pgx to cache only the Describe phase without creating server-side named prepared statements. Avoids prepared statement conflicts when pgcat reuses backend connections, while still optimizing the Describe round-trip.

database:
  engine: postgres
  writer:
    uri: postgresql://postgres:DB_USER_PASSWORD@pgcat:6432/permify?plan_cache_mode=force_custom_plan&default_query_exec_mode=cache_describe
  reader:
    uri: postgresql://postgres:DB_USER_PASSWORD@pgcat:6432/permify?plan_cache_mode=force_custom_plan&default_query_exec_mode=cache_describe

  # pgxpool sizing
  max_connections: 1
  min_connections: 0

  # Lifecycle / health
  max_connection_lifetime: 3600s
  max_connection_idle_time: 240s       # keep < pgcat’s idle/server timeout
  max_connection_lifetime_jitter: 600s
  health_check_period: 30s
  connect_timeout: 10s

Caution (session mode):

Non-zero min_connections across many pods can pin backend sessions and exhaust server connection budget.

Direct Postgres (no proxy)

Use a small warm client pool to reduce cold connects.

database:
  engine: postgres
  uri: postgresql://postgres:DB_USER_PASSWORD@postgres:5432/permify?sslmode=require

  max_connections: 15-25        # tune to workload and DB limits
  min_connections: 1-3          # small warm pool
  max_connection_lifetime: 30m
  max_connection_idle_time: 5-10m
  max_connection_lifetime_jitter: 5-10m
  health_check_period: 30-60s
  connect_timeout: 10s

Sizing rules of thumb

With pgcat
- min_connections: 0
- max_connections: 1 per pod (raise to 2–5 only if you observe client-side waits)
- Ensure pool_size x shards/users fits Postgres max_connections with headroom
Direct Postgres
- min_connections: 1–3, max_connections sized to real concurrency/CPU
- Keep idle/lifetime settings reasonable to avoid thundering-herd reconnects

Monitoring

Track both sides and correlate spikes:

pgcat: pool utilization, server connection counts, read/write routing, query latency
Permify: pool (in-use/total), “connect” spans, error rates

Debug patterns

“Connect” spikes + rising pgcat server connections → proxy opening backends (cold path)
“Connect” spikes + Idle=0 in client pool → client pool exhaustion (no warm conns)

Troubleshooting

Connect spikes with pgcat

Keep min_connections: 0
Ensure max_connection_idle_time < pgcat idle/server timeout
In session mode with many pods, check pool_size and server connection headroom

Churn without pgcat

Set min_connections: 1–3

Backward compatibility

max_open_connections → deprecated, use max_connections instead (still works)
max_idle_connections → deprecated, use min_connections instead (still works, maps to min_connections if not set)

Introduction

Getting Started

Modeling Guides

Setting Up

Operations

Integrations

Use Cases

Migration

When to use pgcat vs direct Postgres

Installation

Docker (quick start)

Kubernetes (standalone service)

Kubernetes (sidecar pattern)

Reference pgcat configuration (session mode)

Permify → Pgcat configuration

Direct Postgres (no proxy)

Sizing rules of thumb

Monitoring

Troubleshooting

Backward compatibility

​When to use pgcat vs direct Postgres

​Installation

​Docker (quick start)

​Kubernetes (standalone service)

​Kubernetes (sidecar pattern)

​Reference pgcat configuration (session mode)

​Permify → Pgcat configuration

​Direct Postgres (no proxy)

​Sizing rules of thumb

​Monitoring

​Troubleshooting

​Backward compatibility

When to use pgcat vs direct Postgres

Installation

Docker (quick start)

Kubernetes (standalone service)

Kubernetes (sidecar pattern)

Reference pgcat configuration (session mode)

Permify → Pgcat configuration

Direct Postgres (no proxy)

Sizing rules of thumb

Monitoring

Troubleshooting

Backward compatibility