mirror of
https://github.com/MeshCore-Beacon/beacon-server.git
synced 2026-09-02 00:58:22 +00:00
MrAlders0n
7d759d0a0f
* fix: cascade channel_messages on packet delete * rebuild known_routes keyed on (iata, path_key) * add route retention delete, batch reconfirm, path_key upsert * compute route path_key in store, add DeleteOldRoutes * add routes retention config * wire route retention into cleanup, batch reconfirm * prune routes in reconfirm task, not cleanup * expand routes retention docs in example config
44 lines
2.1 KiB
SQL
44 lines
2.1 KiB
SQL
-- Copyright 2026 Beacon Contributors
|
|
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
|
|
|
-- known_routes grew unbounded (28 GB / 32.8M rows in 24 days on prod) and its
|
|
-- UNIQUE(node_ids, iata) key indexed whole UUID arrays. Rebuild keyed on a
|
|
-- 16-byte md5 of node_ids and add reconfirm bookkeeping. No rows are pruned
|
|
-- here: retention is deployment config (routes.retention/grace), so the first
|
|
-- cleanup tick after startup enforces it; a schema migration must not bake in
|
|
-- one deployment's policy. id stays (the API serializes it) but loses its
|
|
-- index; uniqueness lives on (iata, path_key).
|
|
|
|
CREATE TABLE known_routes_new (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
path_key BYTEA NOT NULL,
|
|
node_ids UUID[] NOT NULL,
|
|
hash_prefix BYTEA[] NOT NULL,
|
|
iata CHAR(3) NOT NULL REFERENCES iata_codes(iata) ON DELETE CASCADE,
|
|
hop_count INT NOT NULL,
|
|
first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
observation_count BIGINT NOT NULL DEFAULT 1,
|
|
last_reconfirmed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (iata, path_key)
|
|
);
|
|
|
|
INSERT INTO known_routes_new
|
|
(id, path_key, node_ids, hash_prefix, iata, hop_count, first_seen, last_seen, observation_count)
|
|
SELECT id,
|
|
decode(md5(array_to_string(node_ids, ',')), 'hex'),
|
|
node_ids, hash_prefix, iata, hop_count, first_seen, last_seen, observation_count
|
|
FROM known_routes;
|
|
|
|
SELECT setval(pg_get_serial_sequence('known_routes_new', 'id'),
|
|
(SELECT COALESCE(MAX(id), 1) FROM known_routes_new));
|
|
|
|
DROP TABLE known_routes;
|
|
ALTER TABLE known_routes_new RENAME TO known_routes;
|
|
ALTER TABLE known_routes RENAME CONSTRAINT known_routes_new_pkey TO known_routes_pkey;
|
|
ALTER TABLE known_routes RENAME CONSTRAINT known_routes_new_iata_fkey TO known_routes_iata_fkey;
|
|
|
|
CREATE INDEX idx_known_routes_hop_count ON known_routes(iata, hop_count);
|
|
CREATE INDEX idx_known_routes_last_seen ON known_routes(last_seen DESC);
|
|
CREATE INDEX idx_known_routes_reconfirm ON known_routes(last_reconfirmed_at);
|