Geospatial Portal

Optimizing PostgreSQL PostGIS Connection Limits for Open-Source Geospatial Portals

A production runbook for sizing, pooling, and isolating PostgreSQL/PostGIS connections so that high-concurrency OGC traffic stays stable under tile storms and bulk feature edits.

This guide sits under the Security Boundary Mapping for OGC Services guide and the broader Core Portal Architecture & Security Boundaries framework, which treats the database connection layer as the innermost trust boundary — the point where an unbounded WFS-T write storm must never be allowed to starve read-only PostGIS tile traffic. Where that overview maps boundaries conceptually, this page is the hands-on implementation: how to put a connection pooler in front of PostGIS, segment pools by OGC service, and prove the change took effect.

Unlike standard OLTP workloads, spatial query execution introduces non-deterministic memory footprints, extended transaction lifecycles, and heavy planner overhead that rapidly exhaust default connection pools. PostgreSQL allocates a dedicated backend process per client, and PostGIS amplifies the cost through functions like ST_Intersects, ST_DWithin, and topology operations that hold row-level locks or maintain intermediate state in work_mem. The objective therefore extends well beyond raising max_connections: it is to architect a deterministic connection lifecycle in front of the database.

Prerequisites

Confirm the following before changing any limits:

  • PostgreSQL 14+ with the PostGIS 3.2+ extension installed and a geometry/geography schema already in use.
  • PgBouncer 1.18+ installed on the same host or a sidecar reachable over the loopback or a private subnet.
  • A dedicated pooler role (for example pgbouncer_auth) with SELECT access to a lookup function for auth_query, so the pooler does not need plaintext credentials for every client role.
  • Shell access with permission to edit /etc/pgbouncer/pgbouncer.ini, /etc/pgbouncer/userlist.txt, and postgresql.conf, plus systemctl reload rights for both services.
  • A baseline of peak concurrency: the maximum simultaneous WMS, WFS, WCS, and WFS-T requests observed at the portal’s stateless middleware tier.
  • Monitoring access to pg_stat_activity, pg_stat_database, and pg_locks (a role with pg_monitor membership is sufficient).

Step-by-step implementation

1. Set a realistic backend ceiling on PostgreSQL

Resist the urge to set a large max_connections. Each backend reserves work_mem per sort or hash node, so a high ceiling multiplied by a generous work_mem invites out-of-memory kills under spatial sorts. Size the ceiling to what the pooler will actually open, plus reserved maintenance slots.

# postgresql.conf — database is the constrained resource, keep the ceiling tight
max_connections = 200
superuser_reserved_connections = 5
work_mem = 32MB                 # per sort/hash node — multiplied by active backends
maintenance_work_mem = 512MB    # VACUUM, CREATE INDEX on geometry columns
shared_buffers = 4GB
log_min_duration_statement = 5000   # log spatial queries slower than 5s

Reload without a restart:

sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo -u postgres psql -c "SHOW max_connections;"

2. Front PostGIS with PgBouncer in transaction mode

Transaction pooling lets many client sessions multiplex over a small set of backends — the only mode that scales OGC concurrency without a backend-per-client explosion. The critical detail for spatial workloads is server_reset_query: it must clear PostGIS session variables, prepared statements, and temporary spatial indexes between client handoffs, or state leaks across requests and corrupts planner estimates.

# /etc/pgbouncer/pgbouncer.ini
[databases]
gisdb = host=127.0.0.1 port=5432 dbname=gisdb

[pgbouncer]
listen_addr = 127.0.0.1
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
server_reset_query = DISCARD ALL      # wipe PostGIS session state on handoff
max_client_conn = 2000                # match peak OGC client concurrency
default_pool_size = 20                 # backends per (db,user) — overridden per pool below
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
query_timeout = 60
server_idle_timeout = 120

3. Segment pools by OGC service type

A single shared pool lets a WFS-T write storm consume every backend and stall WMS tile rendering. Map each OGC service onto its own pool so contention is bounded by service. Declare distinct database aliases that all point at the same PostGIS instance but carry their own sizing.

# /etc/pgbouncer/pgbouncer.ini  — [databases] section
# Read-heavy tile and feature reads: many short transactions
gis_read   = host=127.0.0.1 port=5432 dbname=gisdb pool_size=40 pool_mode=transaction

# Transactional feature updates (WFS-T): fewer, longer-lived writes
gis_write  = host=127.0.0.1 port=5432 dbname=gisdb pool_size=10 pool_mode=transaction

# Topology editing: ST_MakeValid / ST_SnapToGrid can run long — isolate it
gis_topo   = host=127.0.0.1 port=5432 dbname=gisdb pool_size=6  pool_mode=transaction

Then route the portal’s middleware connection strings by service: WMS/WMTS and WFS GetFeature reads point at gis_read, WFS-T transactions at gis_write, and topology editing workflows at gis_topo with a longer per-statement budget. This is the database-tier expression of the segmentation modelled in the parent cluster, and it complements edge-layer isolation covered in Securing MapProxy with Nginx and ModSecurity.

The diagram below shows how OGC workloads are segmented into dedicated transaction-mode pools so a write storm can never starve read-only tile traffic.

OGC Service Pools Isolated in PgBouncer Transaction Mode Four OGC workload sources on the left fan into three dedicated PgBouncer pools. WMS tile reads and WFS feature streams both route to the Read pool (pool_size 40); WFS-T feature edits route to the Write pool (pool_size 10); topology operations such as ST_MakeValid and ST_SnapToGrid route to the Topology pool (pool_size 6, elevated query_timeout). All three pools multiplex onto a single PostgreSQL/PostGIS instance. Because each service has its own bounded pool, a WFS-T write storm cannot consume the backends that read-only tile traffic depends on. WMS tile reads many short transactions WFS feature streams GetFeature reads WFS-T feature edits fewer, longer writes Topology ops ST_MakeValid, ST_SnapToGrid PgBouncer — transaction mode Read pool pool_size = 40 tile + feature reads Write pool pool_size = 10 Topology pool pool_size = 6 elevated query_timeout PostgreSQL PostGIS max_connections 200

4. Cap memory per long-running spatial transaction

Rather than inflating global work_mem, raise memory only inside the transactions that need it. SET LOCAL scopes the change to the current transaction, so a heavy spatial sort gets headroom without every pooled backend reserving it.

BEGIN;
SET LOCAL work_mem = '256MB';                  -- scoped to this transaction only
SET LOCAL statement_timeout = '120s';          -- topology ops get a longer budget
SELECT ST_MakeValid(geom) FROM parcels WHERE invalid_flag;
COMMIT;

5. Reload both services 

sudo systemctl reload pgbouncer
# verify config parsed and pools came up
psql -h 127.0.0.1 -p 6432 -U pgbouncer_auth pgbouncer -c "SHOW DATABASES;"

Verification

Confirm the pooler is interposed and the backend ceiling is respected:

-- Backends in use vs. the hard ceiling — keep 20-30% headroom at peak
SELECT count(*) AS backends,
       current_setting('max_connections')::int AS ceiling,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct
FROM pg_stat_activity;
# PgBouncer's own admin console reports live pool occupancy per service
psql -h 127.0.0.1 -p 6432 -U pgbouncer_auth pgbouncer -c "SHOW POOLS;"

In SHOW POOLS, healthy output shows cl_active tracking client demand while sv_active stays bounded by each pool’s pool_size, and cl_waiting returns to 0 between bursts. A persistently non-zero cl_waiting on gis_read means the read pool is undersized for tile concurrency.

-- No client should accumulate "idle in transaction" sessions holding backends
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'gisdb'
GROUP BY state
ORDER BY 2 DESC;

Troubleshooting matrix

Symptom Likely cause Fix
Degraded tile rendering, intermittent 502/504, but no too many connections error Backends parked in idle in transaction from uncommitted spatial edits Query pg_stat_activity for state = 'idle in transaction'; set idle_in_transaction_session_timeout; fix the client that omits COMMIT
FATAL: too many connections for role pool_size sum across pools exceeds max_connections minus reserved slots Lower per-pool pool_size or raise max_connections modestly, keeping a 20-30% buffer
Tile queries spilling to disk; temp_files/temp_bytes climbing in pg_stat_database Per-session work_mem too low for spatial sorts under pooling Use SET LOCAL work_mem inside heavy transactions instead of inflating the global value
Cross-request results or stale prepared statements after a handoff server_reset_query not clearing PostGIS session state Set server_reset_query = DISCARD ALL in transaction mode
WMS reads stall whenever bulk edits run Reads and writes sharing one pool Split into gis_read / gis_write / gis_topo pools (Step 3)
Queries blocked, pg_locks shows long lock waits Abandoned transaction holding row-level locks Cross-reference pg_locks with pg_stat_activity; terminate the orphaned backend with pg_terminate_backend(pid)

For background on how backend process overhead compounds under concurrent load, see PostgreSQL’s runtime connection configuration reference and the PgBouncer configuration documentation for parameter precedence and transaction-mode caveats.