Slow Queries

Queries taking longer than expected. Start with pressure metrics, then narrow down.

Observe JSON endpoints require an operator session or accepted credential by default; curl examples below use a bearer token.

# Check pressure metrics
curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/pressure | jq

# Check health status
curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/doctor | jq '.checks[] | select(.status != "healthy")'

curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/pressure | jq '.metrics[] | select(.name == "query_permit") | .value'

curl -s http://localhost:8080/metrics \
  | grep -E 'scribe_db_connections_(pending|active)|scribe_db_pool_pressure'

If scribe_db_connections_pending > 0:

• Queries are waiting for database connections
• Increase pool size or reduce query concurrency

curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/pressure | jq '.metrics[] | select(.name == "jvm_memory") | .value'

If > 0.85: Increase heap size (-Xmx)

If pg_stat_statements is enabled, the built-in monitoring shows slow queries:

curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/stats/queries | jq

Example response:

{
  "queries": [
    {
      "queryId": "1234567890",
      "calls": 15420,
      "meanMs": 45.2,
      "totalMs": 697344.0,
      "rows": 30840,
      "queryPreview": "Directory lookup by distinguished name (redacted)"
    }
  ],
  "timestamp": "2026-01-24T12:00:00Z"
}

If you see "error": "query statistics are unavailable", see Enabling query statistics below.

curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/hints | jq '.hints[] | select(.severity == "warning")'

Add the suggested indexes.

When a Query Diagnostic Report is available, open the Corpus context section before changing configuration. It shows how many entry types and attributes were considered, whether the captured statistics are fresh, and whether support has enough context to replay the case at a comparable scale. Missing context does not mean the report is unusable; it tells you which data point was unavailable when the report was captured.

Enable tracing to identify slow stages:

SCRIBE_TRACES_ENABLED=true ./identity-scribe

Check spans for bottlenecks:

• Query.Execute — Database execution time
• Query.Plan — Planning overhead (complex filters)
• Query.Map — Result mapping (too many attributes)

pg_stat_statements records per-query execution statistics. IdentityScribe's Queries tabs (Slow Log, Stats) and the /observe/stats/queries endpoint use it when available.

Enabling it takes two steps, in this order:

1. Preload the library — add pg_stat_statements to PostgreSQL's shared_preload_libraries, then restart the server. Preloading activates the statistics collector and only takes effect at startup.
2. Enable the extension — enable the pg_stat_statements extension once in the IdentityScribe database. In development and staging IdentityScribe can do this for you (see below).

Enabling the extension before the library is preloaded leaves it half-enabled — it exists, but every read fails. Always preload and restart first.

Once both steps are done the Queries tabs populate automatically. Enabling the extension after IdentityScribe is already running needs no restart or redeploy — the next refresh picks it up.

Append to postgresql.conf (keep any value that is already there — don't overwrite it):

shared_preload_libraries = 'pg_stat_statements'

Restart PostgreSQL, then enable the pg_stat_statements extension in the IdentityScribe database — or set database.auto-create-query-stats-extension = true (see below) to let IdentityScribe enable it for you.

On Postgres.app (macOS) the config file is at ~/Library/Application Support/Postgres/var-<version>/postgresql.conf — confirm the exact path with SHOW config_file;, edit it, then restart from the Postgres.app menu.

Add the preload flag to the Postgres service command and recreate the container:

services:
  postgres:
    image: postgres:18-alpine
    command: ["postgres", "-c", "shared_preload_libraries=pg_stat_statements"]

After the reboot, enable the pg_stat_statements extension once against the IdentityScribe database.

For non-production environments, IdentityScribe can enable the extension for you at startup once the library is preloaded:

database.auto-create-query-stats-extension = true

The default is auto — on in development, off everywhere else — so you can opt staging in while production stays untouched. It still requires the library to be preloaded (step 1) and a database role allowed to create extensions; if either is missing, startup logs an actionable note and continues without the feature. Leave it off in production until you have assessed the impact of pg_stat_statements on your workload.

curl -s -H "Authorization: Bearer <token>" http://localhost:8080/observe/stats/queries | jq

A populated queries array confirms it is working. "error": "query statistics are unavailable" means the extension is either not installed, or installed but not preloaded — the Queries tab shows which, with a one-click guide.

If you cannot enable pg_stat_statements, you can still diagnose slow queries using:

• Tracing: Enable SCRIBE_TRACES_ENABLED=true to see per-query execution times
• Hints: Check /observe/hints for missing index recommendations
• Database slow-query logs: Follow your provider's logging guide to capture requests over your chosen duration threshold

• Health and Monitoring — Dashboards and operational workflows
• Signals — Golden signals and error classification
• PromQL Recipes — Alert queries and dashboards
• Logging and Traces — Trace layout and debugging