31 lines
1.2 KiB
MySQL
31 lines
1.2 KiB
MySQL
|
-- 010_churn_metrics.sql
|
||
|
|
-- Fast-path BGP churn metrics, written by the obmp-churn-monitor service.
|
||
|
|
--
|
||
|
|
-- obmp-churn-monitor reads openbmp.parsed.unicast_prefix from Kafka with its
|
||
|
|
-- own consumer group and only COUNTS announcements/withdrawals per
|
||
|
|
-- (router, peer) -- no relational RIB maintenance. Because counting is far
|
||
|
|
-- cheaper than psql-app's per-route upserts, it stays real-time even when the
|
||
|
|
-- main ingestion pipeline lags minutes behind under a churn storm. This is the
|
||
|
|
-- decoupled "visibility path": it does not speed up the bulk DB write, it
|
||
|
|
-- guarantees churn visibility survives a storm the bulk pipeline cannot.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS churn_metrics (
|
||
|
|
ts timestamptz NOT NULL DEFAULT now(),
|
||
|
|
router_ip inet,
|
||
|
|
peer_ip inet,
|
||
|
|
peer_asn bigint,
|
||
|
|
adds integer,
|
||
|
|
dels integer
|
||
|
|
);
|
||
|
|
|
||
|
|
SELECT create_hypertable('churn_metrics', 'ts', if_not_exists => TRUE);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_churn_ts ON churn_metrics (ts DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_churn_router_ts ON churn_metrics (router_ip, ts DESC);
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
PERFORM add_retention_policy('churn_metrics', INTERVAL '7 days');
|
||
|
|
EXCEPTION WHEN OTHERS THEN
|
||
|
|
RAISE NOTICE 'churn_metrics retention policy not added: %', SQLERRM;
|
||
|
|
END $$;
|