Geospatial Portal

Sizing PostGIS for Concurrent WFS Queries

This guide derives a defensible concurrency ceiling for a spatial database serving feature queries, and sets memory, pooling and application limits from it. It belongs to Autoscaling & Capacity Planning for Geospatial Workloads, within the Infrastructure Orchestration & Configuration Management framework.

Prerequisites

  • A PostGIS instance with representative data volumes — sizing against an empty database tells you nothing.
  • A set of real feature queries taken from access logs, covering the range from cheap to expensive.
  • A connection pooler in front of the database, per configuring PostGIS connection pooling with PgBouncer.
  • Permission to run a load test against a staging copy at production scale.

Feature queries are memory-shaped, not CPU-shaped

A WFS query’s cost profile differs from an ordinary web query in one way that dominates sizing: the working memory it needs is a property of the query plan, allocated per sort or hash node, and a spatial query has many such nodes. A query with a spatial join, an ordering, and a deduplication can hold several allocations simultaneously — and the setting that governs them is per-node, not per-connection.

Working memory is allocated per plan node Three query shapes with the number of concurrent memory allocations each produces, and the effect on worst-case total memory. QUERY SHAPE ALLOCATIONS WORST CASE PER QUERY bounded bbox, no sort an index scan and a limit 1 one work_mem spatial join plus order the common analyst query 3 to 4 several work_mem, concurrently join, order, distinct, aggregate a reporting extract 6 or more and parallel workers multiply it again Sizing as work_mem times max_connections understates the worst case by whatever the plan-node count happens to be.

Step-by-step implementation

1. Measure the real cost of your own queries 

-- Take the queries from the access log, not from imagination, and measure
-- both time and the memory the plan actually used.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT p.id, p.geom
FROM parcels p
JOIN wards w ON ST_Intersects(p.geom, w.geom)
WHERE w.code = 'E05009345'
ORDER BY p.updated_at DESC
LIMIT 1000;
--   read: Execution Time, and every "Sort Method" / "Memory Usage" line
-- The distribution matters more than any single query. Pull the real spread.
SELECT round(mean_exec_time::numeric, 1) AS mean_ms,
       round((mean_exec_time * calls)::numeric / 1000, 1) AS total_s,
       calls,
       left(query, 80) AS query
FROM pg_stat_statements
WHERE query ILIKE '%ST_Intersects%' OR query ILIKE '%geom%'
ORDER BY total_s DESC
LIMIT 15;

2. Derive the concurrency ceiling from measurement 

# Three inputs, one ceiling.
CORES=16                 # usable cores on the database host
MEAN_MS=180              # measured mean feature-query time
TARGET_P95_MS=1500       # the latency the portal promises

# Active backends beyond the core count add queueing, not throughput.
# Start at the core count and validate with a load test rather than assuming.
echo "start ceiling: $CORES active backends"

# Then size max_connections to leave room for maintenance and monitoring,
# and let the pooler queue everything above the ceiling.
echo "max_connections: $(( CORES * 2 + 20 ))"

3. Set memory from the worst plan, not the average 

# postgresql.conf — the numbers below are illustrative; derive yours.
shared_buffers = 8GB               # roughly a quarter of RAM for a dedicated host
effective_cache_size = 24GB        # planner hint: what the OS is likely caching

# work_mem is PER PLAN NODE. Size it so that (nodes x work_mem x active queries)
# fits comfortably, and raise it per session for the rare heavy query instead.
work_mem = 32MB
maintenance_work_mem = 1GB

# Parallelism multiplies work_mem again; bound it deliberately for a mixed workload.
max_parallel_workers_per_gather = 2
max_parallel_workers = 8

# Bound a runaway feature query rather than discovering it in a latency graph.
statement_timeout = 120s
idle_in_transaction_session_timeout = 60s

4. Raise the limit only where it is needed 

-- A reporting role that genuinely needs more memory gets it, without every
-- tile query being sized for the worst case.
ALTER ROLE reporting SET work_mem = '256MB';
ALTER ROLE reporting SET statement_timeout = '600s';

-- And the routine path stays tight.
ALTER ROLE geonode_app SET work_mem = '32MB';

5. Validate the ceiling with a load test

Finding the plateau, and staying on it A throughput curve rising to a plateau and falling, with a latency curve rising throughout, and the recommended ceiling marked on the plateau. active backends → the ceiling throughput falls latency (dashed) keeps rising idle capacity rate

Re-measure when anything underneath changes

A ceiling derived from measurement is valid for the system it was measured on, and several ordinary changes invalidate it quietly.

Four changes that require re-measuring the ceiling Version upgrade, data growth, cartography change and storage change, each with why the previous measurement no longer holds. major version upgrade planner behaviour changes, so plans and costs change a large data load index depth and cache hit rates move together a cartography change the renderer issues different queries entirely different storage random-read latency dominates index-heavy spatial work

Keep the load test as a versioned artefact next to the configuration it produced, so re-measuring is running a script rather than reconstructing an experiment. The half-hour it takes after a version upgrade is repaid the first time it shows that a plan regression has halved the ceiling — which is a fixable problem when it is found deliberately and an unexplained slowdown when it is not.

Verification 

# 1. Active backends stay at or below the ceiling under load
watch -n2 'psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE state = '\''active'\'';"'
#   expect: hovering at the ceiling, not far above it

# 2. Waiting happens at the pooler, in milliseconds, not at the database
psql -h pgbouncer -p 6432 -U pgbouncer -c "SHOW POOLS;" | awk 'NR<5 {print $1,$6,$7,$8}'
#   expect: cl_waiting small, and maxwait in single-digit milliseconds

# 3. No query is spilling to disk when it should not be
psql -Atc "SELECT sum(temp_bytes) FROM pg_stat_database WHERE datname='geoportal';"
#   compare before and after a load run; a large jump means work_mem is too small

# 4. p95 for the representative query set meets the promise
k6 run --vus 40 --duration 5m wfs-queries.js | grep 'http_req_duration'
#   expect: p95 inside the stated target

# 5. A runaway query is bounded rather than fatal
psql -c "SET statement_timeout = '2s'; SELECT count(*) FROM parcels p JOIN parcels q ON ST_Intersects(p.geom, q.geom);"
#   expect: canceling statement due to statement timeout

Troubleshooting matrix

Symptom Likely cause Fix
Queries slow down as concurrency rises, with CPU not saturated Lock and buffer contention beyond the plateau Lower the ceiling; let the pooler queue the excess
The database is killed by the out-of-memory killer work_mem multiplied by plan nodes and parallel workers Lower work_mem; raise it per role for the queries that need it
Temporary file usage spikes during reports work_mem too small for the heavy queries only Set a higher value on the reporting role, not globally
Pool waits are long even below the ceiling Pool size smaller than the ceiling, or one sub-pool exhausted Align pool sizes with the ceiling; check per-pool waits
One slow query blocks many others No statement timeout, holding a connection indefinitely Set statement_timeout per role from the slowest legitimate query
Adding CPU did not help The workload is IO-bound on random reads, not CPU-bound Measure the plan; consider storage with better random-read latency
Latency is fine in staging and poor in production Staging data volume is smaller, so plans differ Size against production-scale data; compare plans, not just timings

FAQ

Is the core count really the right starting ceiling?

It is a starting point that is usually close for CPU-bound spatial work, and it must be validated by measurement. Workloads dominated by random IO plateau lower; workloads dominated by cached reads plateau slightly higher. The important part is that the ceiling comes from a curve you measured rather than from a memory calculation.

Should read replicas be used to raise the ceiling?

For read-only feature queries, yes, and it moves the problem rather than removing it: each replica has its own ceiling and its own pool, and something must decide which queries may tolerate replication lag. It is worth doing when catalogue and feature reads dominate, and it does nothing for a write-heavy editing portal.

How does this interact with the renderer fleet’s autoscaling?

Directly: the fleet’s maximum replica count should be derived from this ceiling. A renderer fleet permitted to grow beyond what the pool can serve converts a database limit into queueing at every renderer, which looks like a renderer problem and is not.

What about max_connections — why not set it high and rely on the pooler?

Because the pooler only helps if the database’s own limit is below the point where contention dominates. A high max_connections allows the pooler to be misconfigured into admitting more work than the database serves well, and removes the backstop. Keep it modest and let queueing happen where it is cheap.

Do parallel query workers help feature queries?

For large aggregate scans, yes; for the typical bounded, indexed feature query, they add coordination overhead and consume additional working memory. Bound max_parallel_workers_per_gather for a mixed workload and let the heavy reporting role raise it if measurement justifies it.

Should the ceiling differ between working hours and overnight?

It can, and it usually should not be automated. Overnight the mix shifts towards bulk work — ingestion, seeding, reporting — which tolerates queueing and benefits from more memory per query. A scheduled change of role-level settings, rather than of the ceiling itself, gives the heavy jobs what they need without loosening the limits that protect interactive readers during the day.

How does this number relate to what the pooler advertises?

The pooler’s maximum should sit at or just below this ceiling, and the sum of its sub-pools must not exceed it. A pooler configured to admit more than the database serves well converts a queue that costs milliseconds into contention that costs every query — which is precisely the outcome the pooler was introduced to prevent.

Up one level: Autoscaling & Capacity Planning for Geospatial Workloads.