Skip to content

Slow Queries

  • Everything slow

    Check shared load and waiting work.

  • One search slow

    Inspect that query and its search coverage.

Separate a slow service from one slow kind of search.

You can pick the matching diagnostic checks and collect evidence before changing capacity.

Next: Search coverage

Queries taking longer than expected. First decide whether everything is slow or one kind of search.

Searches are slowShared pressurepermits/pool/heapQuery evidenceslow log · hintsJustified changecoverage · scaleDiagnostic Reportsend to supportall slowone searchjustifiesjustifiesunclear
Searches are slowShared pressurepermits/pool/heapQuery evidenceslow log · hintsJustified changecoverage · scaleDiagnostic Reportsend to supportall slowone searchjustifiesjustifiesunclear
Check shared pressure for widespread slowness and query evidence for a specific slow search; change only what the evidence justifies.

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

Terminal window
set -o pipefail
# Check pressure metrics
curl -fsS -H "Authorization: Bearer <token>" http://localhost:8080/observe/pressure | jq
# Check health status
curl -fsS -H "Authorization: Bearer <token>" http://localhost:8080/observe/doctor | jq '.checks[] | select(.status != "healthy")'
Terminal window
set -o pipefail
curl -fsS -H "Authorization: Bearer <token>" http://localhost:8080/observe/pressure | jq '.metrics[] | select(.name == "query_permit") | .value'
ValueMeaningAction
< 0.8NormalNot the bottleneck
0.8-0.95HighQueries queuing — check database
> 0.95SaturatedFind the slow searches holding permits first; then consider database.channel-pool-size
Terminal window
set -o pipefail
curl -fsS 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
Terminal window
set -o pipefail
curl -fsS -H "Authorization: Bearer <token>" http://localhost:8080/observe/pressure | jq '.metrics[] | select(.name == "jvm_memory") | .value'

If > 0.85: Increase heap size (-Xmx)

Check 1: Slow query analysis (pg_stat_statements)

Section titled “Check 1: Slow query analysis (pg_stat_statements)”

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

Terminal window
set -o pipefail
curl -fsS -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.

Terminal window
set -o pipefail
curl -fsS -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)
ProblemSolution
High permit pressureSpeed up the slow searches; then consider database.channel-pool-size
Pending DB connectionsIncrease database.max-pool-size
Memory pressureIncrease -Xmx heap size
Missing indexesAdd indexes per /observe/hints
Complex filtersAdd type constraint, simplify filter
Large result setsUse pagination (paged results, cursors)

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.

Add to preloadkeep existingRestart PostgreSQLloads collectorEnable extensiononce per databaseQueries tabsnext refreshneedsthenfills
Add to preloadkeep existingRestart PostgreSQLloads collectorEnable extensiononce per databaseQueries tabsnext refreshneedsthenfills
Enable query statistics in order: preload, restart the database, then enable collection.
  1. Preload the library — add pg_stat_statements to PostgreSQL’s shared_preload_libraries, then restart the server; preloading only takes effect at startup.
  2. Enable the 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. IdentityScribe needs no restart or redeploy; its next refresh picks the data up.

Check the current value first:

SHOW shared_preload_libraries;

Edit postgresql.conf and append pg_stat_statements to the comma-separated list. Keep every existing library. If the value is empty, set it to pg_stat_statements; if it already contains auto_explain, the line becomes:

shared_preload_libraries = 'auto_explain,pg_stat_statements'
shared_preload_libraries = ‘auto_explain,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.

Preserve the Postgres service command you already have. Add or update only the shared_preload_libraries flag, keeping every existing library in the comma-separated value:

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

If there is no current preload value, set the flag to pg_stat_statements only. Recreate the container after the command is updated.

ProviderHow to enable
AWS RDSAdd pg_stat_statements to the parameter group’s shared_preload_libraries, reboot the instance
Azure DatabaseServer Parameters → shared_preload_libraries
Google Cloud SQLDatabase Flags → cloudsql.enable_pg_stat_statements
DigitalOceanEnabled by default on managed databases

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

Let IdentityScribe enable the extension (dev / staging)

Section titled “Let IdentityScribe enable the extension (dev / staging)”

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.

Terminal window
set -o pipefail
curl -fsS -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