Scaling dashboard aggregations over hundreds of millions of events

-- ======================================================================
-- Q: Dashboards with aggregations over hundreds of millions of events. What do
-- you do?
--
-- I separate OLTP from OLAP. Steps in order of cost: (1) proper indexes and a
-- BRIN index on created_at for append-only tables, (2) partitioning by month
-- (fast dropping of old data for retention), (3) precomputed rollups (per
-- hour/day) or materialized views, (4) a read replica for reports, (5) a column
-- store such as ClickHouse or TimescaleDB when the volume requires it.
-- ======================================================================

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id int NOT NULL,
    severity smallint NOT NULL,
    created_at timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, created_at)          -- the partition key inside the PK
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE INDEX ON events USING BRIN (created_at);

-- hourly rollup, refreshed by a scheduled job
INSERT INTO events_hourly (tenant_id, hour, severity, cnt)
SELECT tenant_id, date_trunc('hour', created_at), severity, count(*)
FROM events WHERE created_at >= :from AND created_at < :to
GROUP BY 1, 2, 3
ON CONFLICT (tenant_id, hour, severity) DO UPDATE SET cnt = EXCLUDED.cnt;