Ory Kratos Identity Retrieval: Admin API Pagination vs. External Indexing

Answering the Core Question

When does the Admin API become inefficient?

• Per‑page > 1,000 – the database must materialise a large result set in memory, increasing latency and memory pressure on Kratos.
• Page number > 100 – offset‑based pagination forces the DB to scan all preceding rows, which grows linearly with depth.
• Identity count > 500,000 – even modest pagination can hit the 90th‑percentile latency of 200–300 ms on typical hardware; beyond this, deep traversal is usually slower than a targeted query on an external index.

Which trait‑filter patterns are unperformant?

• Range or bounding queries on dates, numbers, or enum values (e.g., created_at > '2024-01-01').
• Multi‑field AND/OR combinations that require cross‑trait joins (e.g., email LIKE '%@example.com' AND age > 30).
• Partial or fuzzy matches (LIKE, ILIKE, regex) and full‑text search across trait values.
• Sorting on non‑indexed traits, which forces in‑memory ordering after the initial scan.

In these scenarios, the Admin API falls back to in‑memory filtering, causing high CPU usage and memory spikes in Kratos and its underlying DB.

Practical Steps for Your Environment

1. Measure current load: Run GET /identities?page=1&per_page=1000 and log response time and CPU/memory usage on Kratos and the DB. Repeat with page=100, 500, 1,000 to observe linear growth.
2. Check DB indexes: Verify that identity.id and identity.schema_url are indexed. Traits stored in identity.traits are usually JSONB; ensure any frequently queried keys have GIN or BTREE indexes.
3. For complex filters, create a lightweight sync job: Use Kratos’ event stream or webhook to push identity changes to a separate PostgreSQL or ElasticSearch instance. Keep the sync eventual – a few seconds of lag is acceptable for admin dashboards.
4. Implement the external query: On the sync store, expose a REST endpoint that accepts bounding parameters (date ranges, numeric thresholds, multi‑field AND/OR) and leverages native indexes or full‑text search.
5. Monitor performance: Continuously compare latency of paginated Admin API calls vs. the external endpoint. If the external endpoint consistently returns under 100 ms for deep queries, it is the preferred path.

Missing Diagnostic Detail

To fine‑tune the thresholds for your specific deployment, could you share the current identity count and the database backend (PostgreSQL, MySQL, SQLite, etc.)? Knowing these values will help adjust the per_page and page limits for optimal performance.