Skip to content

Indexes

Indexes are the query access paths behind the API and KPIs. Because events is partitioned, indexes created on the parent are inherited by every partition (each partition gets its own index instance).

Events Indexes

Index Columns Purpose Access path
ix_events_timestamp timestamp DESC Recent-event queries Partition pruning + order
ix_events_source_sev_ts source, severity, timestamp DESC Filter source + severity Composite fast path
ix_events_severity severity Severity breakdown Analytics
ix_events_priority priority WHERE NOT NULL Priority dashboards Partial index
ix_events_priority_ts priority, timestamp DESC WHERE NOT NULL Priority + time range Partial composite
ix_events_template_id template_id Template analytics FK-ish lookup
ix_events_message_trgm message GIN (pg_trgm) Full-text search (ILIKE %…%) Trigram index
ux_events_id (id, timestamp) PK enforcement Unique
ux_events_dedup_key (dedup_key, timestamp) WHERE NOT NULL Dedup backstop Partial unique

The GIN Trigram Index

Full-text search uses ILIKE '%search%' which a standard B-tree cannot accelerate. Migration 0007 added a GIN index over message using pg_trgm:

CREATE INDEX ix_events_message_trgm ON events USING gin (message gin_trgm_ops);

SELECT * FROM events
WHERE message ILIKE '%connection timeout%'
ORDER BY timestamp DESC
LIMIT 50;

The query planner uses the trigram index to filter candidate rows, dramatically reducing IO on large partitions.

Template & Metadata Indexes

Table Index Purpose
templates occurrence_count DESC Top templates endpoint
template_priority PK template_id L3 priority cache
events (derived) template_id Template → events drilldown
alerts related_source Alert lookup by source
incidents priority, created_at Incident triage lists

Index Tuning Notes

  • work_mem should be raised for large aggregations (KPI queries).
  • GIN indexes are write-slow — acceptable here because writes are batched (executemany, synchronous_commit = off).
  • Migration 0017 added the final round of missing indexes after load-testing the KPI queries.
  • KPI performance indexes (migration 0009) support the real-time dashboard aggregates.

How to Inspect

-- Index usage across partitions
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename LIKE 'events%'
ORDER BY idx_scan DESC
LIMIT 20;