Overview
In Tyk deployments using PostgreSQL for analytics and activity log storage, performing "Activity by Key" searches across broad date ranges in the Tyk Dashboard can generate excessive database load, high CPU utilization, and request timeouts.
Symptom & Database Behavior
When an administrator queries API Key activity in the Tyk Dashboard:
- PostgreSQL server CPU utilization spikes to 100%.
- The Dashboard UI displays indefinite loading indicators or returns an HTTP 504 Gateway Timeout.
- PostgreSQL slow query logs reveal long-running queries executing sequential table scans across partitioned analytics tables.
Root Cause
Tyk Pump stores analytics records across date-partitioned PostgreSQL tables. When users search for API key activity using substring matching or without strict time boundaries, queries evaluate leading-wildcard filters (such as LIKE '%key_hash%'). Standard B-Tree database indexes cannot be used for leading-wildcard lookups, forcing the database engine to perform sequential scans across millions of rows across all partition tables.
Resolution Steps
- Constrain Query Time Windows: Always restrict the search date range in the Dashboard UI to narrow intervals (e.g. 1 hour to 24 hours) to allow PostgreSQL to prune unaffected partitions.
Create Trigram Indexes for Substring Searches: For deployments requiring substring key searches, enable the
pg_trgmPostgreSQL extension and add a GIN trigram index on the key hash column:CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX CONCURRENTLY idx_tyk_analytics_key_trgm ON tyk_analytics USING gin (key_hash gin_trgm_ops);- Implement Analytics Retention Policies: Configure automated table rotation and retention purging in Tyk Pump and PostgreSQL to drop historical partitions older than your active auditing window (e.g. 30–90 days).
Applies To
Tyk Dashboard, Tyk Pump, and PostgreSQL analytics storage.
Comments
0 comments
Please sign in to leave a comment.