checkId: check.postgres.pool plugin: stellaops.doctor.postgres severity: warn tags: [database, postgres, pool, connections]

PostgreSQL Connection Pool

This Doctor check measures how close PostgreSQL is to running out of connections — one of the most common causes of platform-wide outages in Stella Ops. When the pool is exhausted, every service that needs the database starts timing out at once, so catching rising pressure early prevents a cascading failure. It is aimed at operators triaging latency, timeouts, or suspected connection leaks.

What It Checks

Connects to PostgreSQL and queries pg_stat_activity and pg_settings to evaluate connection pool health:

Evidence collected: ActiveConnections, IdleConnections, MaxConnections, UsageRatio, ConfiguredMaxPoolSize, ConfiguredMinPoolSize, WaitingConnections.

The check requires ConnectionStrings:StellaOps or Database:ConnectionString to be configured.

The SQL query executed:

SELECT
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') as active,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') as idle,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_conn,
  (SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Client') as waiting

Why It Matters

Waiting connections are the early-warning signal: they mean requests are already queuing for database access, which translates directly into increased latency for end users. A high usage ratio is the next step toward full exhaustion, at which point services begin failing rather than merely slowing down. Connection leaks, if not caught early, march the pool steadily toward that cliff even under normal load.

Common Causes

How to Fix

Docker Compose

# Check active database connections
docker compose -f docker-compose.stella-ops.yml exec postgres \
  psql -U stellaops -d stellaops_platform -c \
  "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

# Terminate idle connections
docker compose -f docker-compose.stella-ops.yml exec postgres \
  psql -U stellaops -d stellaops_platform -c \
  "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < now() - interval '10 minutes';"

To raise the PostgreSQL connection ceiling, add server flags in docker-compose.stella-ops.yml:

services:
  postgres:
    command: >
      postgres
      -c max_connections=200
      -c shared_buffers=256MB

Increase Npgsql pool size via connection string:

services:
  platform:
    environment:
      ConnectionStrings__StellaOps: "Host=postgres;Database=stellaops_platform;Username=stellaops;Password=stellaops;Maximum Pool Size=50;Minimum Pool Size=5"

Bare Metal / systemd

# Check connection statistics
psql -U stellaops -d stellaops_platform -c \
  "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

# Check for long-running queries
psql -U stellaops -d stellaops_platform -c \
  "SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC LIMIT 10;"

# Increase max connections
sudo -u postgres psql -c "ALTER SYSTEM SET max_connections = 200;"
sudo systemctl restart postgresql

Kubernetes / Helm

# Check connection pool from inside a pod
kubectl exec -it <postgres-pod> -- psql -U stellaops -d stellaops_platform -c \
  "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

# Terminate idle connections
kubectl exec -it <postgres-pod> -- psql -U stellaops -d stellaops_platform -c \
  "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < now() - interval '10 minutes';"

Set in Helm values.yaml:

postgresql:
  maxConnections: 200
  sharedBuffers: 256MB

platform:
  database:
    connectionString: "Host=postgres;Database=stellaops_platform;Username=stellaops;Password=stellaops;Maximum Pool Size=50;Minimum Pool Size=5"

Verification

stella doctor run --check check.postgres.pool