Add measured PostgreSQL/PostGIS tuning profiles and diagnostics

[Medformatik]

Problem

The Postgres/PostGIS service uses mostly upstream defaults despite workloads that include geospatial search, Overture ingestion/conflation, POI operations, job state, and account data. Copying generic tuning values would be unsafe because memory, concurrency, dataset size, and query mix vary by deployment.

Current state

• services/postgis/service.json declares a 2 GiB memory class but no measured PostgreSQL tuning profile.
• No repository configuration was found for shared_buffers, work_mem, planner cost constants, JIT policy, checkpoint/WAL behavior, autovacuum tuning, or pg_stat_statements.
• Overture and data-manager jobs already have capacity/preflight concepts that can provide representative heavy workloads.

Proposed solution

Create reproducible benchmarks and a small set of conservative, memory-budgeted tuning profiles. Add diagnostics that let operators observe slow/regressed queries and validate changes with EXPLAIN (ANALYZE, BUFFERS) rather than accepting folklore defaults.

Acceptance criteria

• A benchmark corpus covers representative search, spatial joins/conflation, ingestion/upsert, cleanup, and normal API queries.
• Baselines capture p50/p95 runtime, buffer hits/reads, temp spill, WAL, CPU, memory, locks, and concurrent behavior.
• pg_stat_statements (or an equally safe mechanism) is available with privacy-aware query normalization and documented reset/retention.
• Small/default/large profiles derive memory settings from explicit container/host budgets and concurrency assumptions.
• Aggregate worst-case memory math covers connections, parallel workers, maintenance, autovacuum, and per-operation memory.
• Planner, JIT, random I/O, parallelism, checkpoint/WAL, connection, and autovacuum changes are individually justified by evidence.
• PostGIS indexes/statistics and analyze/vacuum behavior are included, not only core Postgres knobs.
• Deployment overrides are rendered deterministically and validated against supported PostgreSQL versions.
• Automated regression checks prevent a profile from materially harming the representative read/write mix.
• Each setting has rollback guidance; the default profile remains conservative.
• Admin/monitoring docs explain finding slow queries, collecting plans safely, and avoiding secrets/coordinates in diagnostics.
• Results and hardware/container assumptions are checked into a dated report.

Alternatives considered

Hard-coding values from another project ignores workload and memory differences. Leaving everything at defaults avoids tuning risk but obscures preventable spills, poor plans, and maintenance lag.

Area

PostgreSQL/PostGIS, deployment, observability, performance engineering.