Product analytics warehouse built on PostgreSQL advisory locks, a proper star schema, and a built-in experiment framework. Zero dropped events at high concurrency.
-- Worker picks next available job -- without blocking or double-claiming WITH next_job AS ( SELECT id, event_type, payload FROM event_queue WHERE status = 'pending' ORDER BY created_at ASC FOR UPDATE SKIP LOCKED LIMIT 1 ) UPDATE event_queue SET status = 'processing', claimed = NOW() FROM next_job WHERE event_queue.id = next_job.id RETURNING *;
Product analytics at any meaningful scale is a queue problem. Events arrive concurrently from multiple sources. Workers must claim and process them without overlap. If two workers claim the same event, the metric counts are wrong. If a worker crashes mid-claim, the event cannot be lost.
Most systems solve this with application-level locks or a message broker. Spectrum solves it at the database level using PostgreSQL's FOR UPDATE SKIP LOCKED. Each worker atomically claims one row, skipping any row another worker has locked. No external coordinator. No message broker for the hot path.
The downstream requirement was a proper data warehouse structure for analytical queries. Not a log store. A star schema with dimension tables, a central fact table, and the ability to run cohort and experiment queries directly in SQL without ETL preprocessing.
One central fact table surrounded by four dimension tables. Queries against any dimension join directly to FACT_EVENTS. No denormalized lookup columns in the fact row. No pre-built aggregates needed for the dashboard views.
Every event can carry an experiment_id and a variant. The fact table stores both. Aggregations and significance tests run as SQL queries directly over FACT_EVENTS. No separate experiment database.
