Slow Queries

Use this guide when queries take longer than expected.

# Check pressure metrics
curl -s http://localhost:8080/observe/pressure | jq

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

curl -s http://localhost:8080/observe/pressure | jq '.query_permit'

curl -s http://localhost:8080/metrics | grep hikari.*pending

If hikaricp_connections_pending > 0:

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

curl -s http://localhost:8080/observe/pressure | jq '.jvm_memory'

If > 0.85: Increase heap size (-Xmx)

If you have pg_stat_statements enabled in PostgreSQL, the built-in monitoring shows slow queries:

curl -s http://localhost:8080/observe/stats/queries | jq

Example response:

{
  "queries": [
    {
      "queryId": "1234567890",
      "calls": 15420,
      "meanMs": 45.2,
      "totalMs": 697344.0,
      "rows": 30840,
      "queryPreview": "SELECT * FROM entries WHERE dn = $1..."
    }
  ],
  "timestamp": "2026-01-24T12:00:00Z"
}

If you see "error": "pg_stat_statements extension not available", see Enabling pg_stat_statements below.

curl -s http://localhost:8080/observe/hints | jq '.hints[] | select(.severity == "warning")'

Add suggested indexes to improve performance.

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)

The pg_stat_statements extension provides detailed query execution statistics. IdentityScribe’s monitoring UI and /observe/stats/queries endpoint use this extension when available.

Add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Then restart PostgreSQL and create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

After enabling, verify the extension is active:

curl -s http://localhost:8080/observe/stats/queries | jq

If successful, you’ll see query statistics. If you see "error": "pg_stat_statements extension not available", the extension is not installed or not loaded.

If you install pg_stat_statements after the initial IdentityScribe deployment, the v_slow_queries database view may need to be refreshed. The view is created during migration and checks for the extension at that time.

To refresh the view, either:

1. Re-run the migration by restarting IdentityScribe (Flyway will detect and apply pending migrations)
2. Manually recreate the view by executing the view DDL from the migration file

If the endpoint returns an empty array despite having slow queries, this view refresh is likely needed.

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
• PostgreSQL logs: Set log_min_duration_statement = 100 to log queries over 100ms

• Monitoring — Operational workflows
• Observability — Metrics reference