# Day-to-day operations Everything here assumes the all-in-one Docker stack. The commands translate to the split deployment by running them on the server host. ## Checking it is healthy Beacon has no health endpoint yet. Use these instead: - `docker compose ps` shows every container `Up`. `db` and `redis` have health checks; `app` waits for both before it starts. - `curl -s https:///api/v1/brokers` lists each MQTT broker with its connection status. A broker that never connects is a URL or credentials problem; see [Getting packets in](getting-packets-in.md). - Open `wss:///ws` in a WebSocket client. The first frame is `hello`. - `docker compose logs -f app` shows a `connected` line per broker. Individual packets are not logged at the default `info` level, so a quiet log after that is normal; use the observers endpoint or the web app to see traffic. ## Logs Logs go to stderr and Docker collects them. Docker's default `json-file` driver does not rotate, so on a long-running host set a cap, either in `/etc/docker/daemon.json` for every container or per service in `docker-compose.yml`: ```yaml logging: driver: json-file options: { max-size: "50m", max-file: "5" } ``` Set `log.level` (`debug`, `info`, `warn`, `error`; default `info`) and `log.format` (`text` or `json`) in `config.yaml`, or override them with `LOG_LEVEL` and `LOG_FORMAT`. Invalid values stop startup. Every record has a `component`. Ingest records add the broker name; HTTP completion records add the validated client address, route, status and duration. Query strings and WebSocket hello payloads are never logged. Expected ingest skips and routine WebSocket lifecycle events are at debug level. ```bash docker compose logs -f app # the API and ingest docker compose logs -f caddy # the proxy ``` Caddy also writes a JSON access log to `data/Caddy/logs/access.json`, which the fail2ban rules in [`app_config/fail2ban/`](../app_config/fail2ban/README.md) read. ## Where the data lives Everything persistent is under `data/` next to `docker-compose.yml`: - `data/postgres/` is the database. Deleting it deletes all history. - `data/redis/` is the cache. It can be removed while the stack is down; Beacon rebuilds it. - `data/Caddy/data/` holds TLS certificates. Deleting it means a new certificate request on the next start, which counts against Let's Encrypt rate limits. - `data/app/config.yaml` and `.env` are your configuration. Keep copies of both somewhere else; no backup below includes `.env`. ## Retention Everything below is a duration in `config.yaml`. Shorten them if disk is tight, lengthen them if you want more history. | Setting | Default | What it covers | |---|---|---| | `packets.retention` | `168h` (7 days) | Packets, observations and channel messages | | `analytics.rollup_retention` | `2160h` (90 days) | Hourly stats rollups. Also the longest window the stats endpoints accept | | `telemetry.retention` | `744h` (31 days) | Observer telemetry snapshots | | `routes.retention` | `336h` (14 days) | Known routes, counted from when a route was last seen | | `nodes.delete_after` | `720h` (30 days) | Nodes not heard from | | `observers.delete_after` | off | Observers not heard from. Opt in by setting a duration | Historical stats read the hourly rollups, not raw packets. Each UTC hour is rolled about 95 minutes after it closes, and packet cleanup holds back up to 24 hours of raw rows until their hours are rolled, so raw data lives a little longer than `packets.retention` says. An hour whose raw rows were deleted first is reported as partial, with no values. ## Backing up Two options. **A plain `pg_dump`** from the host, which is enough for most operators: ```bash docker compose exec -T db pg_dump -U beacon -d beacon -Fc > beacon-$(date +%F).dump ``` Keep `data/app/config.yaml` and `.env` with it. The dump holds message data and anything you have decrypted, so treat it as private. **The admin backup endpoint** produces an archive of the database and the saved `config.yaml`, with a manifest and checksums, and a command-line tool that verifies it. It is off by default and needs the admin key. See [Backup and export](backup-export.md). ## Restoring Restore into an empty database, never over live data. With the stack running and the app stopped, drop and recreate the database, load the dump, and only then start the app: ```bash docker compose stop app docker compose exec -T db psql -U beacon -d postgres -c 'DROP DATABASE beacon;' -c 'CREATE DATABASE beacon;' \ && docker compose exec -T db pg_restore -U beacon -d beacon < beacon-YYYY-MM-DD.dump \ && docker compose start app ``` If `pg_restore` reports errors the app is not started; read them before trying again. A 1.x dump cannot be restored into 2.0.0; the server refuses the old schema. See [Upgrading](upgrading.md#upgrading-to-200-from-1x). ## Before deploying a release Migrations run when the server starts, and some need a lock on a busy table. Check two things in `psql` before you pull a new image: ```bash docker compose exec db psql -U beacon -d beacon ``` ```sql -- No transaction older than five minutes. This must return no rows. SELECT pid, now()-xact_start, state, left(query,80) FROM pg_stat_activity WHERE xact_start < now() - interval '5 minutes'; -- No anti-wraparound autovacuum on the tables the release touches. SELECT pid, now()-xact_start, query FROM pg_stat_activity WHERE query LIKE 'autovacuum:%to prevent wraparound%'; ``` A long transaction holds back the locks a migration waits for. An anti-wraparound autovacuum does not give way to a lock request the way an ordinary autovacuum does, so a migration on that table waits until the vacuum ends. Let either finish, or end a stuck client session with `SELECT pg_cancel_backend();`, then deploy. If you run the [daily plan check](#turning-on-the-daily-check), also confirm its report is clean before and after the deploy. Maintainers run the plan checks before every server release; see [Before a release](#before-a-release). ### How startup migrations wait for locks Each migration statement runs with `lock_timeout = 30s`. When a statement times out on a lock, the server logs a `migration lock wait timed out` warning listing the sessions that blocked it (pid, state, `xact_start` and the first 200 characters of the query), waits, and tries the file again: three attempts in all, 10 s after the first and 20 s after the second. A file still blocked after about two minutes stops startup with `migrations failed`, the server exits with status 1, and Docker restarts it (`restart: unless-stopped`), which starts the same check again. A restart loop with these warnings means a session in the blocker list is holding the lock; deal with it and the next start goes through. Retrying is safe. A file without `CONCURRENTLY` runs in one transaction and rolls back whole. An interrupted `CREATE INDEX CONCURRENTLY` leaves an invalid index, which the server drops and rebuilds on the next attempt. An interrupted `DROP INDEX CONCURRENTLY` is finished by the `IF EXISTS` drop on the next attempt. Migration `004_drop_known_routes_iata_last_seen.sql` drops `idx_known_routes_iata_last_seen`, which no query reads and which can reach about 10 GB on a large deployment. If you already dropped it by hand (see below), the migration does nothing. Migrations `011` to `024` (the small-table release) are one statement per file: one small `CREATE INDEX CONCURRENTLY` on `node_iatas`, eleven small index drops on `node_iatas`, `nodes`, `observers` and `channels`, one rename, and `fillfactor = 50` on `observers` and `channels`. Together they take seconds at boot. Each drop needs a brief lock on its table, so run the check above first and don't start an external `pg_dump` or a long transaction against those four tables during the upgrade; a blocked drop stops startup after about two minutes as described below, and the next start resumes where it stopped. Afterwards, compact the two tables once; see [Compacting observers and channels](#after-upgrading-compacting-observers-and-channels). Migrations `025` to `033` (2.1.0) change `packets`: - `025` and `027` add the checks `packets_payload_type_domain` (`payload_type` 0 to 15) and `packets_route_type_domain` (`route_type` 0 to 3) without checking existing rows. Each needs a brief lock on `packets`. - `026` and `028` validate them. Each scans `packets`; writes continue meanwhile. - `029` to `032` build four indexes with `CREATE INDEX CONCURRENTLY`; see [Pre-building the packets indexes](#pre-building-the-packets-indexes). - `033` creates the statistics object `packets_heard_inverted_stat` and runs `ANALYZE packets`. Ingest only stores types decoded from the packet header, which always fit, so only rows changed by hand can fail the checks. Before upgrading, confirm none exist; when none do, this reads the whole table, so run it off-peak: ```sql SELECT EXISTS (SELECT 1 FROM packets WHERE payload_type NOT BETWEEN 0 AND 15 OR route_type NOT BETWEEN 0 AND 3); ``` It must return `f`. Otherwise `026` or `028` fails and stops startup on every attempt; fix or delete those rows first. Migration `034_observations_adverts.sql` builds the partial index `idx_observations_adverts` on `packet_observations` with `CREATE INDEX CONCURRENTLY`; see [Pre-building the observer adverts index](#pre-building-the-observer-adverts-index). The same release starts a background job that fills in missing observation payload types; see [After upgrading: observation payload types](#after-upgrading-observation-payload-types). Migrations `035` to `041` add the channel message read model; see [Read models: channel messages by site](#read-models-channel-messages-by-site). - `035` creates the table `channel_message_iatas`. - `036` to `040` build its five indexes with `CREATE INDEX CONCURRENTLY` on the empty table, which is instant. - `041` builds `idx_channel_messages_channel_id` on `channel_messages` with `CREATE INDEX CONCURRENTLY`. `channel_messages` is small (about 200,000 rows on a large deployment), so it takes well under a second and needs no pre-build. To pre-build `041` anyway, run the pre-deploy check above, then this text exactly, on its own in autocommit mode: ```sql CREATE INDEX CONCURRENTLY idx_channel_messages_channel_id ON channel_messages USING btree (channel_id, id DESC); SELECT indisvalid AND indisready FROM pg_index WHERE indexrelid = 'idx_channel_messages_channel_id'::regclass; ``` The second statement must print `t`. The migration then records the existing valid index instead of building it again. After the deploy, check every new index: ```sql SELECT c.relname, i.indisvalid AND i.indisready FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname IN ('channel_message_iatas_pkey', 'idx_cmi_channel_site', 'idx_cmi_site', 'idx_cmi_channel_site_scope', 'idx_cmi_channel_scope', 'idx_cmi_scope', 'idx_channel_messages_channel_id'); ``` It must return 7 rows, all `t`. Migrations `042` to `045` add the region packet read model; see [Read models: packets by site](#read-models-packets-by-site). - `042` creates the table `packet_sites`. Its foreign key to `packets` takes a brief lock on `packets`, so run the pre-deploy check above first. - `043` to `045` build `idx_packet_sites_site`, `idx_packet_sites_site_type` and `idx_packet_sites_site_scope` with `CREATE INDEX CONCURRENTLY` on the empty table, which takes seconds. No pre-build is needed. After the deploy, check the table's four indexes: ```sql SELECT c.relname, i.indisvalid AND i.indisready AS ok FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE i.indrelid = 'packet_sites'::regclass ORDER BY 1; ``` It must return 4 rows, all `ok = t`. Migrations `047` to `053` finish the cleanup; see [Lookup-table indexes and the route stamp column](#lookup-table-indexes-and-the-route-stamp-column). - `047` to `052` drop six cold indexes on small lookup tables with `DROP INDEX CONCURRENTLY IF EXISTS`: milliseconds each, plus any wait for a lock. - `053` drops the column `known_routes.last_reconfirmed_at` in a transaction. It is a catalog change, not a rewrite (179 ms on a 10.5-million-row copy), but it takes an `ACCESS EXCLUSIVE` lock on `known_routes`, so run the pre-deploy check above, and make sure no `pg_dump` is running: the drop waits for it. While it waits (up to 30 s, three attempts), every other session's reads of `known_routes`, read-only servers' included, queue behind it. The column's space (about 8 bytes a route, some 80 MB on 10.5 million routes) is reclaimed only when the table is next rewritten (`VACUUM FULL` or `pg_repack`); new and updated rows no longer carry it. **It is a one-way door for older images**; see [Upgrading](upgrading.md). ### Pre-building the route hop index Migration `006_known_routes_hop_iata.sql` builds `idx_known_routes_hop_iata`, which `/routes` uses when a request filters by `hopCount`. The server builds it with `CREATE INDEX CONCURRENTLY` during startup and does not serve the API until it finishes. On a large `known_routes` table (tens of millions of rows) that takes 10 to 25 minutes and needs 0.2 to 0.4 GB of disk. To keep that out of the upgrade, build it yourself off-peak before deploying, in `psql` as above: ```sql CREATE INDEX CONCURRENTLY idx_known_routes_hop_iata ON known_routes (hop_count, iata); SELECT indisvalid, indisready FROM pg_index WHERE indexrelid = 'idx_known_routes_hop_iata'::regclass; ``` Run the `CREATE` on its own in autocommit mode, not inside a transaction block. The check must return `t | t`. Anything else means the build was interrupted and left an invalid index; drop it and build again: ```sql DROP INDEX CONCURRENTLY idx_known_routes_hop_iata; CREATE INDEX CONCURRENTLY idx_known_routes_hop_iata ON known_routes (hop_count, iata); ``` When the server starts and finds a valid index with that name, it records the migration as applied without building anything. If you skip this step, the migration still works; startup just takes longer. Run the pre-deploy check above before either. ### Pre-building the packets indexes Migrations `029` to `032` build four indexes on `packets` with `CREATE INDEX CONCURRENTLY` while the API and ingest wait. On a small database that is seconds. On a large one, build them yourself off-peak before deploying, in `psql` as above, each statement on its own in autocommit mode. Use exactly these names and definitions: ```sql CREATE INDEX CONCURRENTLY idx_packets_last_heard_hash ON packets USING btree (last_heard_at DESC, packet_hash DESC); CREATE INDEX CONCURRENTLY idx_packets_type_last_heard ON packets USING btree (payload_type, route_type, last_heard_at DESC, packet_hash DESC); CREATE INDEX CONCURRENTLY idx_packets_scope_last_heard ON packets USING btree (scope_id, last_heard_at DESC, packet_hash DESC) INCLUDE (payload_type, route_type, first_heard_at) WHERE (scope_id IS NOT NULL); CREATE INDEX CONCURRENTLY idx_packets_heard_inverted ON packets USING btree (first_heard_at) INCLUDE (last_heard_at, packet_hash, payload_type, route_type, scope_id) WHERE ((first_heard_at > last_heard_at) IS TRUE); ``` Then check them. Every row must read `t | t`: ```sql SELECT c.relname, i.indisvalid, i.indisready, pg_size_pretty(pg_relation_size(c.oid)) AS size FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname IN ('idx_packets_last_heard_hash', 'idx_packets_type_last_heard', 'idx_packets_scope_last_heard', 'idx_packets_heard_inverted'); ``` An invalid index means the build was interrupted; drop it with `DROP INDEX CONCURRENTLY` and build it again. When the server finds a valid index with one of these names, it records that migration as applied without building anything. It checks only the name, so a different definition under the same name is kept as it is. Pre-build only these indexes. Don't create the two checks or `packets_heard_inverted_stat` yourself: migrations `025`, `027` and `033` fail on an object that already exists, and startup stops. To go back to the previous server image, deploy it. It ignores the new indexes, checks and statistics. Leave them in place: their migrations stay recorded, so a later upgrade would not build them again. ### Pre-building the observer adverts index Migration `034` builds `idx_observations_adverts`, which `/observers/{observerId}/adverts` reads once the [payload-type backfill](#after-upgrading-observation-payload-types) is done. The server builds it at startup while the API and ingest wait. On a large `packet_observations` table (hundreds of millions of rows) that should take a minute or two and about 150 MB of disk, so build it yourself off-peak before deploying. Run the [pre-deploy check](#before-deploying-a-release) first: the long-transaction query must return no rows. Then, in `psql` as above, on its own in autocommit mode, run exactly this text, byte for byte the same as the migration: ```sql CREATE INDEX CONCURRENTLY idx_observations_adverts ON packet_observations (observer_id, id) WHERE payload_type = 4; ``` Then check it: ```sql SELECT indisvalid, indisready, pg_get_indexdef(indexrelid), pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_index WHERE indexrelid = 'idx_observations_adverts'::regclass; ``` It must read `t | t`, with the definition `CREATE INDEX idx_observations_adverts ON public.packet_observations USING btree (observer_id, id) WHERE (payload_type = 4)`. If `indisvalid` is `f`, the build was interrupted; drop the index and build it again: ```sql DROP INDEX CONCURRENTLY idx_observations_adverts; ``` When the server starts and finds a valid index with that name, it records migration `034` as applied without building anything, so startup is instant. If you skip this step, the same build runs at startup instead, and the API and ingest wait for it. Turn on the [daily plan check](#turning-on-the-daily-check) only after migration `034` is recorded. Some of its adverts checks need this index; without it they take 1 to 16 seconds instead of 78 ms. To confirm: ```sql SELECT filename FROM schema_migrations WHERE filename = '034_observations_adverts.sql'; ``` To go back to the previous server image, deploy it. The index stays and nothing reads it: about 150 MB, plus one entry for each advert observation stored. You can leave it. If you drop it (`DROP INDEX CONCURRENTLY idx_observations_adverts;`), also delete its migration record, so a later upgrade builds it again: ```sql DELETE FROM schema_migrations WHERE filename = '034_observations_adverts.sql'; ``` ### After upgrading: route maintenance The release that adds migration 006 changes how known routes are cleaned up. Deploy it off-peak and expect the following. - Route retention runs every `background.reconfirm` (default `1h`). Its first run after the upgrade is a full pass over the grace window (routes under `routes.min_observations` older than `routes.grace`). On a table of about 30 million routes that takes 12 to 14 minutes, in statements of a minute or less. The clock hour of that first run becomes the hour of a weekly full pass; a change to `routes.grace` or `routes.min_observations` also triggers one. Other runs only cover what aged out since the previous run. - Stale neighbor edges are cleared at the end of every retention run. The first run may delete a backlog; later runs take a few seconds. - The route recheck (routes with a hop that has departed or become ambiguous) runs every 5 minutes and works through the table at `routes.reconfirm_rows_per_hour` (default `81000`). A full cycle takes the route count divided by that rate: about 16 days for 32.5 million routes. The first cycle reaches routes older releases never got to, and deletes the ones the same rule already condemned, typically a few percent of the table. About half of those come back the next time they are heard. ### After upgrading: observation payload types Observations stored by servers older than the observation `payload_type` column have no payload type of their own. The release with migration `034` adds a background job, on the `maint` pool, that gives each of them its own packet's payload type. It walks `packet_observations` by id in spans of one million ids, up to ten spans a minute, so at most about 10 million ids a minute; after each span it pauses as [`readmodels.fill`](#pacing-the-backfills) sets, by default as long as the span took, so expect about half that. Each statement writes at most 50,000 rows, and the job saves its position after every span, so a restart resumes where it stopped. When the walk passes the highest id it read at the start of its run, it records `done`. Rows stored later are typed by ingest. How long it takes depends on the id range, not on how many rows need a type: a database whose ids run to about 830 million needed about 80 minutes before the pacing, so expect up to twice that at the default `duty: 0.5`. On a database whose observations are all typed, each statement reads its span and updates nothing. Until a server process reads `done`, `/observers/{observerId}/adverts` answers `503` with `Retry-After: 30` (the statement that served those pages before `done` is gone). The job logs `observation payload type backfill progress: observer advert pages answer 503 until it is done` with `next_id`, `max_id`, `ids_done`, `ids_remaining` and `eta` once a minute while it walks. Once a server process reads `done`, it serves the pages from `idx_observations_adverts` and keeps that until restart. On a database whose observations already all carry a payload type (every deployment that started on a release with the column), the job's first run finds no untyped row and records `done` at once (`observation payload types complete ... probe=true`), so the pages are back within a minute of boot. There is no switch for this gate. Check progress, read-only: ```sql SELECT value, updated_at FROM background_state WHERE key = 'observation_payload_type'; ``` `next_id` climbs about 10 million a minute, and the value ends as `{"next_id": ..., "done": true}`. **Marking it done by hand.** You can skip the walk on a database that has no untyped observations. First check, read-only. This reads all of `packet_observations` once, so run it off-peak: ```sql BEGIN READ ONLY; SET LOCAL statement_timeout = '10min'; SELECT EXISTS (SELECT 1 FROM packet_observations WHERE payload_type IS NULL) AS any_untyped; ROLLBACK; ``` Only if it prints `f`, and after the job's first run has created the key (the progress query returns a row), run: ```sql UPDATE background_state SET value = '{"next_id": 0, "done": true}'::jsonb, updated_at = now() WHERE key = 'observation_payload_type'; ``` It should report `UPDATE 1`. A run that was already in flight can write `"done": false` once more, so run the progress query again two minutes later and repeat the `UPDATE` if needed. Each server process picks up `done` on its next adverts request; no restart is needed. On a deployment that had untyped observations, the backfill corrects two things: the per-observer payload breakdown's unknown (`-1`) bucket shrinks into the real types, and route evidence starts to include those rows. Don't upgrade to a release that drops `idx_observations_observer_id` until the progress query shows `"done": true`. Going back to the previous server image needs nothing here. Older servers ignore the `observation_payload_type` key, and rows the job typed stay typed, which older servers read the same way. ### After upgrading: stats and traces on the custom pool In the release with migration `034`, five statements move from the `api_legacy` pool to `api_custom`, under both `custom_all` and `split`: `GetTopNodes`, `GetStatsTopAdvertisers`, `GetPathStats`, `GetStatsSeries` and `ListTraceTags`. That pool has a 35 s statement timeout, `jit=off` and no parallel workers (see [Database pools and connections](#database-pools-and-connections)). They are also guarded, but a guard's deadline applies only if its family is listed in `guards.enforce`, which is empty by default. For top nodes, top advertisers and paths, requests with at most 32 distinct IATAs and whole-hour bounds use new statements that read the hourly rollups one hour and IATA at a time. Only wider lists, such as every IATA at once, still run the previous statements, now without parallel workers. Measured over all 625 IATAs for 7 days on a copy of a large production database, they stay far inside the timeout: | Statement | With parallel workers | Without | |---|---:|---:| | top nodes | 276 ms | 556 ms | | top advertisers | 1.47 s | 3.63 s | | paths | 1.63 s | 1.58 s | Responses are unchanged. Under `split`, `ListObserverAdverts` and `GetObserverTelemetrySince` run on cached generic plans; see [Configuration](configuration.md#database-pools-and-cost-guards). ### After upgrading: compacting observers and channels The small-table release (migrations `011` to `024`) drops the indexes that made every observer presence update and every channel hearing rewrite index entries, and sets `fillfactor = 50` on `observers` and `channels` so those updates stay on the same page (HOT updates). The fillfactor only applies to pages written from then on. A table that bloated before the upgrade keeps its size until it is rewritten once: on a large deployment `observers` held about 4,000 rows in 52 MB, and about 6 MB after the rewrite. **When.** Once, off-peak, after the release is live and its last migration is recorded. This must return `1`: ```sql SELECT count(*) FROM schema_migrations WHERE filename LIKE '024_%'; ``` Not before: without the fillfactor and the dropped indexes, the tables bloat again. **Is it worth it?** Check the sizes in `psql` as above: ```sql SELECT relname, relpages, pg_size_pretty(pg_relation_size(oid)) AS heap, reltuples::bigint AS rows FROM pg_class WHERE relname IN ('observers', 'channels'); ``` If both are already a few MB, skip it. **Run** as the table owner (the `beacon` user) or a superuser, each statement on its own in autocommit mode, not inside a transaction block: ```sql SET lock_timeout = '3s'; VACUUM FULL observers; VACUUM FULL channels; ``` - Each `VACUUM FULL` rewrites the table and its indexes under an `ACCESS EXCLUSIVE` lock, which blocks reads and writes on that table while it runs. On tables of this size that is a few seconds, so ingest's observer and channel updates and the observer and channel API pages wait that long. - If it reports `canceling statement due to lock timeout`, another session held the table; run the same statement again. - It needs free disk equal to the table's new size plus its indexes, a few MB here. The old files are freed when it finishes. **Verify.** Re-run the size query: `relpages` and the heap size should drop. With fillfactor 50 a table is about twice its packed size, so `observers` ends near 6 MB for 4,000 rows. Then check that updates are now HOT. The counters are cumulative, so take two samples an hour or so apart and compare the change: ```sql SELECT relname, n_tup_upd, n_tup_hot_upd, round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct FROM pg_stat_user_tables WHERE relname IN ('observers', 'channels'); ``` Between the two samples, nearly all new updates (`n_tup_hot_upd` growth over `n_tup_upd` growth) should be HOT; before this release only a few percent were. Sessions holding long snapshots lower it somewhat. **Rollback.** None needed for the rewrite; it changes no data. To go back to the previous server image, just deploy it: it works without the dropped indexes, but type-filtered node pages (`/nodes` with a node type) are slower until the indexes are rebuilt. It ignores the channel id cache and goes back to using a sequence value per channel hearing. If you want the indexes back, rebuild them with `CREATE INDEX CONCURRENTLY` using the definitions in the server's `db/migrations/001_baseline.sql`, for example: ```sql CREATE INDEX CONCURRENTLY idx_nodes_type_last_seen ON nodes USING btree (node_type, last_seen DESC); ``` The baseline's `idx_node_iatas_iata` on `(iata, last_heard DESC)` has been replaced by an index of the same name on `(iata)`; leave it in place. ## Read models: channel messages by site `channel_message_iatas` is a small table with one row for each channel message and each site that heard it. Each row also copies the message's channel and its packet's scope. Ingest keeps it up to date, and a background reconcile repairs it every 5 minutes. On a large deployment it takes about 120-150 MB with its five indexes. While the server process trusts the table, these channel message pages read it: - `/channels/{id}/messages` with `iatas`, with `iatas` and `scope`, with `scope` alone, and with `since` and `iatas`; - `/messages` with `iatas`, and with `scope` alone. Each such page reads about as many index entries as it returns, where the previous statement read every observation of the channel. While the process does not trust the table, those pages answer `503` with `Retry-After: 30`. Two shapes always use the previous all-channel statement, trusted or not, on the two-connection `api_slow` pool: `/messages` with `iatas` and `since`, and `/messages` with `iatas` and `scope`. Pages without `iatas` or `scope` never read the table. Responses are the same as the previous statements' in every case. ### Installing it The release adds migrations `035` to `041`; see [Before deploying a release](#before-deploying-a-release). On the first start the server fills the table in the background: 1. It waits until every transaction that started before boot and holds a transaction id has finished, then runs one full reconcile. 2. It backfills the table oldest message first, in batches of 10,000 message ids (`readmodels.fill.cmi_batch`), pausing after each batch as [`readmodels.fill`](#pacing-the-backfills) sets: at least 0.1 s, and by default as long as the batch took. Once a minute the log shows a `cmi backfill progress` line with `ids_done`, `ids_remaining` and `eta`, then `cmi backfill complete`. On a deployment with about 200,000 messages this takes 3 to 5 minutes, mostly the pauses. 3. It runs a one-time verification: an exact check of two ranges of 2,000 messages, and a page-by-page comparison of the previous and the new statements on the busiest recent sites and scopes. On success it logs `cmi first-time verification passed` and records the time, so later starts skip it. 4. It logs `cmi trusted: region and scope message pages read channel_message_iatas`. Until step 4, the pages above answer `503` with `Retry-After: 30`; the server logs `cmi boot repair starting: region and scope message pages answer 503 until it is trusted` when it begins. If the verification fails, the server logs `cmi verification failed; region and scope message pages answer 503 until a later pass succeeds` and tries again every 5 minutes. See [Pages that wait for a read model](#pages-that-wait-for-a-read-model) for what users see. Check progress, read-only: ```sql SELECT value, updated_at FROM background_state WHERE key = 'cmi'; SELECT pg_size_pretty(pg_total_relation_size('channel_message_iatas')); ``` `complete` turns `true` within minutes, then `first_verified_at` appears. After that, `updated_at` advances every 5 minutes. ### When pages use it Trust is held in memory by each server process; nothing in the database marks the table as exact. A process earns it at every start, once the boot reconcile has run, the backfill is complete and the one-time verification has passed. It drops trust: - after 3 reconciles in a row that each repaired at least one row. It logs `read model untrusted: repair runs fixed rows the writers missed`. Two clean reconciles in a row restore it; - at once when ingest leaves a message without its sites: a failed site insert, a message stored without its sites, or any message insert error. It logs `read model untrusted until the next reconcile` with the reason. Trust returns after the next reconcile that started after the failure, so those pages answer `503` for at most about one interval; - when the stored state is no longer complete and verified, for example after a rebuild. Each restore after a drop logs `read model trusted again: its pages are served from it`. A reconcile that repairs rows logs `cmi reconcile repaired N pair(s): a writer bypassed the read model`. Something stored messages without recording their sites. On a single server running this release that should not happen. Running two server versions that both write to one database at the same time is not supported; if it happens, the pairs the older one leaves out are repaired within one reconcile interval. A [read-only server](configuration.md#read-only-mode) cannot repair anything. Every 5 minutes it checks, read-only, that the writing server's saved state is complete and verified, that the writer saved it within the last 10 minutes (two reconcile intervals, on the database clock), and that the newest 2,000 messages pass the exact check. While all three hold it logs `cmi trusted on this read-only server: region and scope message pages read channel_message_iatas` and serves those pages from the table. Its trust ends on its own 10 minutes after the writer's last save, unless a later check finds a newer one. A writer that is rolled back, stopped or keeps failing stops saving, so on read-only servers those pages answer `503` within 10 minutes, and they log `cmi not trusted on this read-only server: the writer has not saved its state recently`. ### Turning it off Set `readmodels.cmi.enabled: false` in `config.yaml` and restart; see [Configuration](configuration.md#read-models). This turns the pages that read the table off: they answer `503` until the key is `true` again. Every other channel message page is unaffected. The table is still maintained, so turning it back on needs only a restart. ### Metrics and alerts On the [admin listener](#metrics): - `beacon_readmodel_trusted{model="cmi"}` is 1 while this process serves pages from the table. - `beacon_readmodel_repaired_rows_total{model="cmi"}` should stay flat. - `beacon_readmodel_watermark_age_seconds{model="cmi"}` is the time since the last finished reconcile, or on a read-only server since the writer last saved its state. It stays under 600 s. Suggested rules are in [Example alert rules](#example-alert-rules): repaired rows in 3 consecutive 5-minute windows, trusted at 0 for more than 15 minutes (those pages are answering `503`), and a watermark older than 15 minutes. ### Rolling back Deploy the previous image. It ignores `channel_message_iatas`, and the table's rows are deleted with their messages, so it does not grow stale rows. Leave the table and its indexes in place. To roll forward again later, deploy the new image. Before it serves any page from the table, the boot reconcile adds every site the older image stored, starting from the saved watermarks. Those pages answer `503` meanwhile: about a second after a short rollback, a few minutes after days. Nothing to do by hand. Rebuild the table from scratch only if the verification keeps failing: ```sql DELETE FROM background_state WHERE key = 'cmi'; TRUNCATE channel_message_iatas; ``` Then restart the server. It backfills and verifies again as on the first install. A later release will drop `idx_channel_messages_channel` and `idx_channel_messages_sent_brin`. Run this release for at least a week before upgrading to that one. ## Read models: packets by site `packet_sites` holds one row for each packet and each site (IATA) that heard it, at that site's latest hearing. Each row also copies the packet's payload type, route type, scope and first hearing. The observation insert writes the row in the same statement, so a stored observation always has its row. A background reconcile repairs the newest rows every 5 minutes, an hourly audit refills one random older slice, and a front sweep every 30 seconds keeps the newest index entries dense. On a large deployment (about 7.4 million rows) it takes about 1.6 GB: heap 484 MB, primary key 285 MB, `idx_packet_sites_site` 419 MB, `idx_packet_sites_site_type` 353 MB, `idx_packet_sites_site_scope` 53 MB. While the server process trusts the table, `/packets` pages with `iatas`, `iata`, `region` or `regionId` (region pages) read it: - one site: one range of the site's index; - several sites: one range per site; - payload and route types, up to 8 (payload, route) pairs: one exact range per site and pair; - a `since`/`until` window, scopes, or more than 8 type pairs: bounded reads of each site range that continue, within the same request, until the page is full. If 8 reads do not fill the page, the previous release's statement answers that request, on the two-connection `api_slow` pool with a 120 s statement limit (see [Slow region and message statements](#slow-region-and-message-statements)). Unknown site codes are dropped by a cheap check first. Requests with `hashPrefix` never read the table. A region page served from the table now returns `nextPageCursor`; see [Exact page cursors](api-contract.md#exact-page-cursors). While the process does not trust the table, region pages answer `503` with `Retry-After: 30`. Each page is the previous statement's page, except where that statement loses rows: when a site's packets are heard by many observers, it can end a page short, end a walk early (an empty page with `hasMore: false`) or jump `nextCursor` past rows. The new page is full and skips nothing. Packets tied on their site time come out by packet hash, descending. On a production-size copy, the heaviest region pages went from seconds to under a second: all 561 sites for the last hour from 19.5 s to 0.3 s, and the 13 busiest sites for the last 75 minutes at limit 200 from 4.5 s to 31 ms. A filter of rare types on a very sparse site can read a few buffers more than before. An `until` well in the past often runs out of the read budget, so the previous statement answers many of those pages, at the previous cost. ### Ingest cost The site row makes each observation insert about 15-21% slower (mean 104 µs to 119-125 µs) and adds about 37-53% WAL per stored observation (1,425-1,470 B to 2,008-2,188 B), measured over 100,000 inserts on a scratch database. Re-sends of an observation still write nothing. After the deploy, compare `InsertObservation` in `pg_stat_statements` (`mean_exec_time`, and `wal_bytes / calls`) for the hour before and the hour after, and watch `beacon_ingest_queue_depth`. `pg_stat_statements` is cumulative, so take this snapshot an hour before the deploy, at the deploy and an hour after, and divide the differences: ```sql SELECT now(), sum(calls) AS calls, sum(total_exec_time) AS exec_ms, sum(wal_bytes) AS wal_bytes FROM pg_stat_statements WHERE query LIKE '-- name: InsertObservation :one%'; ``` A growing ingest queue, or WAL beyond what you planned for, means rolling back. ### Installing it The release adds migrations `042` to `045`; see [Before deploying a release](#before-deploying-a-release). On the first start the server fills the table in the background: 1. It waits until every transaction that started before boot and holds a transaction id has finished (`packet_sites boot repair waits for transactions from before boot` while it waits), then runs one reconcile from the saved watermark. 2. It backfills the table newest observation first, in slices of 500,000 observation ids (`readmodels.fill.packet_sites_slice`), pausing after each slice as [`readmodels.fill`](#pacing-the-backfills) sets: at least 0.1 s, and by default as long as the slice took, so the fill is busy at most half the time. Once a minute the log shows a `packet_sites backfill progress` line with `ids_done`, `ids_remaining` and `eta`, then `packet_sites backfill complete`. On a large deployment (about 766 million ids, 29.4 million observations) that is about 1,530 slices; expect roughly twice the fill work, about 10 to 40 minutes, longer on a slow disk or a lower `duty`, and a few GB of WAL. 3. It runs a one-time verification. It first runs `VACUUM (ANALYZE) packet_sites` (a few minutes on a large deployment), then an exact check of two slices of 500,000 observation ids, the newest and one random older one, and a page-by-page comparison of region pages of the busiest recent sites with pages read straight from the observations. On success it logs `packet_sites first-time verification passed` and records the time, so later starts skip it. 4. It logs `packet_sites trusted: region packet pages read packet_sites`. Until step 4, region pages answer `503` with `Retry-After: 30`; the server logs `packet_sites boot repair starting: region packet pages answer 503 until it is trusted` when it begins. If the verification fails, the server logs `packet_sites verification failed; region packet pages answer 503 until a later pass succeeds` and tries again every 5 minutes. See [Pages that wait for a read model](#pages-that-wait-for-a-read-model). Check progress, read-only: ```sql SELECT value, updated_at FROM background_state WHERE key = 'packet_sites'; SELECT pg_size_pretty(pg_total_relation_size('packet_sites')); ``` `complete` turns `true`, then `first_verified_at` appears. After that, `updated_at` advances every 5 minutes. ### When pages use it Trust is held in memory by each server process; nothing in the database marks the table as exact. A process earns it at every start, once the boot reconcile has run, the backfill is complete and the one-time verification has passed. It drops trust: - at once when a reconcile or the hourly audit repairs any row: something bypassed the writer. Two clean reconciles in a row restore it. A repair logs `packet_sites reconcile repaired N row(s): a writer bypassed the read model` or `packet_sites audit repaired N row(s) below the watermark`, and the drop logs `read model untrusted: repair runs fixed rows the writers missed`; - at once when an observation insert with its site row fails. Ingest never loses the observation: it logs `observation site row failed; storing the observation without it` and stores it without its row. Trust drops with `read model untrusted until the next reconcile` and the reason, and returns after the next reconcile that started after the failure, so region pages answer `503` for at most about one interval; - after 3 failed reconciles in a row, until one succeeds; - when the saved state is no longer complete and verified, for example after a rebuild. Each restore after a drop logs `read model trusted again: its pages are served from it`. On a single server running this release, repairs should not happen. Running two server versions that both write to one database at the same time is not supported; if it happens, the rows the older one leaves out are repaired within one reconcile interval, and trust returns two clean reconciles later. A [read-only server](configuration.md#read-only-mode) cannot repair anything. Every 5 minutes it checks, read-only, that the writing server's saved state is complete and verified, that the writer saved it within the last 10 minutes (two reconcile intervals), and that the newest 20,000 observations pass the exact check. While all three hold it logs `packet_sites trusted on this read-only server: region packet pages read packet_sites` and serves region pages from the table. Its trust ends on its own 10 minutes after the writer's last save, unless a later check finds a newer one. Otherwise it logs one of: - `packet_sites not trusted on this read-only server: backfill or first verification not done` - `packet_sites not trusted on this read-only server: the writer has not saved its state recently` - `packet_sites not trusted on this read-only server: the newest observations fail the exact check` ### Turning it off Set `readmodels.packet_sites.enabled: false` in `config.yaml` and restart; see [Configuration](configuration.md#read-models). This turns region packet pages off: they answer `503` until the key is `true` again. The table is still maintained and swept, so turning it back on needs only a restart. ### Metrics and alerts On the [admin listener](#metrics): - `beacon_readmodel_trusted{model="packet_sites"}` is 1 while this process serves region pages from the table. - `beacon_readmodel_repaired_rows_total{model="packet_sites"}` should stay flat. - `beacon_readmodel_watermark_age_seconds{model="packet_sites"}` is the time since the last finished reconcile, or on a read-only server since the writer last saved its state. - `beacon_packet_sites_sweep_age_seconds` is the time since the last successful front sweep, and `beacon_packet_sites_sweep_plan_ok` is 1 while the sweep plans as index scans on the three time-ordered indexes. Without that plan the sweep marks no dead entries and the indexes bloat; the server logs `packet_sites sweep plan sets no dead-entry hints; the indexes will bloat`. Both are written only by the server that runs background jobs. - `beacon_packet_sites_front_live{index}` is the entries the last sweep visited, and `beacon_packet_sites_bytes` the table's size with its indexes, hourly. - `beacon_index_bloat_ratio` covers the four `packet_sites` indexes. Their baselines are the sizes of a fresh build, applied once the table holds 100,000 rows, so the ratio reads against a compact index rather than the size the server first saw. Suggested rules are in [Example alert rules](#example-alert-rules): repaired rows in the last 10 minutes, trusted at 0 for more than 15 minutes, a watermark older than 15 minutes, a sweep older than 300 s or a sweep plan at 0, a bloat ratio of the three time-ordered indexes over 2.5 (warn) or 3, and the table over 2.5 GiB (warn; scale it for your deployment). ### Rolling back Deploy the previous image. It ignores `packet_sites`, still has the previous release's indexes, and the table's rows are deleted with their packets, so it does not grow stale rows. Leave the table and its indexes in place. To roll forward again later, deploy the new image. Before it serves any region page from the table, the boot reconcile adds the rows written meanwhile, starting from the saved watermark; region pages answer `503` until then. Nothing to do by hand. Rebuild the table from scratch only if the verification keeps failing: ```sql DELETE FROM background_state WHERE key = 'packet_sites'; TRUNCATE packet_sites; ``` Then restart the server. It backfills and verifies again as on the first install. ## Pages that wait for a read model Three page families are served only from data the server prepares, and answer `503` with `Retry-After: 30` and code `service_unavailable` while it is not ready: | Pages | Ready when | Kill switch | |---|---|---| | `/packets` with `iatas`, `iata`, `region` or `regionId` | this process trusts `packet_sites` | `readmodels.packet_sites.enabled: false` | | `/channels/{id}/messages` and `/messages` with `iatas`, or with `scope` and no `since` (not `/messages` with `iatas` and `since` or `scope`) | this process trusts `channel_message_iatas` | `readmodels.cmi.enabled: false` | | `/observers/{id}/adverts` | the [payload-type backfill](#after-upgrading-observation-payload-types) is done | none | A kill switch set to `false` keeps those pages off (`503`) until it is set back. Every other page is unaffected. Each such `503` is counted in `beacon_api_unavailable_total{reason="not_ready",model}`, `model` being `packet_sites`, `cmi` or `observation_payload_types`. **What users see.** The web app and the mobile app retry a `503` that carries `Retry-After` three times, waiting up to 30 s each, so about 90 s in all, and then show an error. A first fill or a repair that takes longer than that means users must retry by hand (reload the page, or pull to refresh). On the first start of the release that adds a read model, expect those pages to be unavailable for the whole fill: minutes for `channel_message_iatas` on a 200,000-message deployment, roughly 10 to 40 minutes for `packet_sites` on a large one (longer with a lower [`readmodels.fill.duty`](#pacing-the-backfills)), both logged once a minute as `... backfill progress` lines with an `eta`. Plan the upgrade for a quiet hour. **Watching it.** `beacon_readmodel_trusted{model}` is 0 while the pages wait. Alert on it staying 0 for more than 15 minutes, per model (the rules are in [Example alert rules](#example-alert-rules)); it fires during a long first fill as well, which is expected. The adverts gate has no trust gauge; alert on its `503`s instead (`BeaconAdvertsNotReady`, two hours, longer than the backfill takes on a large deployment). ## Pacing the backfills The one-off backfills (`packet_sites`, `channel_message_iatas` and observation payload types) read and write a lot in a short time. Run flat out on a small server they can take the whole disk: on an 8 vCPU, 3.8 GB RAM test server with a rotational disk, the first `packet_sites` fill drove I/O pressure to `full avg10=78%` (`/proc/pressure/io`) and the whole machine stalled twice, sshd and journald included, until it was rebooted. Each fill now paces itself by duty cycle. After each slice or batch it sleeps `max(min_pause, elapsed × (1 − duty) / duty)`, where `elapsed` is how long that slice took, so it is busy at most `duty` of the time. A slow disk makes each slice slower, which stretches the pauses on its own. Pacing changes only when the fills run: they write the same rows, the verification and trust rules are unchanged, and progress is saved after every slice as before. The fill sessions also plan their statements with `plan_cache_mode = force_custom_plan` and, when a read-model table has never been analyzed, run `ANALYZE` on it (at the session start if it already holds rows, otherwise after the first slice). A generic plan built on a new table with no statistics can turn a 14 s slice into one that runs for many minutes. A failed `ANALYZE` is logged (`read-model ANALYZE failed; the fill goes on`) and the fill continues; a table that already has statistics is left to autovacuum. Measured on a laptop Postgres with 3 million synthetic observations (`packet_sites`, slices of 500,000 ids): | `duty` | Fill time | Busy in fill statements | |---|---|---| | `1` | 77 s | 99 % | | `0.5` (default) | 153 s | 50 % | | `0.25` | 316 s | 25 % | Slices of 100,000 ids cost about 7 % more fill work (85 s at `duty: 1`) but cut the longest burst from about 14 s to about 3 s. Settings, under [`readmodels.fill`](configuration.md#backfill-pacing) in `config.yaml`: - `duty` (default `0.5`, at least `0.01`): the share of time a fill may be busy. No single pause is longer than 10 minutes. - `min_pause` (default `100ms`): the shortest pause after a slice or batch. - `packet_sites_slice` (default `500000`) and `cmi_batch` (default `10000`): the slice and batch sizes. A smaller slice gives shorter bursts at a small cost in total work. **On a slow disk** (rotational, network or shared storage), set `duty: 0.25`, and consider `packet_sites_slice: 100000` for shorter bursts. The startup line `config: loaded` shows the values in force. While a fill runs, check `/proc/pressure/io` on the host: `full avg10` should stay well below 50 %. Lower `duty` further if it does not. The reconcile and the hourly audit are not paced: they cover only the observations since the last run, or one slice an hour. ## Slow region and message statements Two kinds of request still read a large part of the observations in one statement: a region `/packets` page whose bounded reads ran out of budget (a sparse filter such as a narrow window or rare payload types over busy sites), and `/messages` with `iatas` and `since` or `scope`. They run on their own pool, `api_slow`: 2 connections by default ([`database.slow_pool_size`](configuration.md#database-pools-and-cost-guards)), `statement_timeout=120s`, no parallel workers, not gated by the cost guards. When every connection is busy a request waits at most 5 s and then answers `503` with `Retry-After: 5`, without touching the other pools; `beacon_api_unavailable_total{reason="slow_pool_busy"}` counts those, and the server logs `api: slow requests refused, every slow connection is busy` at most once a minute. A statement that passes 120 s ends with a `500`; narrowing the request (fewer sites, a wider window) avoids it. Before this release the region statement ran on `api_legacy` without a time limit for a millisecond cursor, and on `api_custom` (35 s, gated by `packets_region`) for an exact page cursor; the all-channel message statement ran on `api_legacy` without a limit. Measured on a copy of a large production database (29.4 million observations, 561 IATAs), with the pool's settings: | Request | Warm | Cold cache | |---|---:|---:| | The busiest region (13 IATAs), three rare payload types | 3.1-3.3 s | 22.9 s | | The 13 busiest IATAs, a one-hour window 60 hours back | 5.5 s | 23.3 s (first run) | | Every IATA (561), a one-minute window | 50 s | 34 s | | Every IATA (561), a one-hour window 120 hours back | 115 s | not measured | Requests naming every IATA are not something the web or app send; the largest region on that database has 22 IATAs. `beacon_db_time_seconds{pool="api_slow"}` and `beacon_pool_acquire_seconds{pool="api_slow"}` show how busy the pool is. ### Reverting to the read-model fallbacks If the read-model pages must be served while a model is not trusted, as in earlier releases, revert the server change that removed the fallbacks rather than rolling back the image. It is a single commit on the server's `dev` branch ("region, scope and advert pages answer 503 until their read model is ready, instead of falling back"); reverting it gives back exactly the previous release's tree: ```bash git revert --no-edit ``` Then build, deploy and restart; no configuration change is needed, because the revert also restores `slow_pool_size`'s default of 8 (an explicit `slow_pool_size` in `config.yaml` still wins). What to expect: - Untrusted region `/packets` pages, channel message pages and observer advert pages are served by the previous statements again, with no `503` from a read model. - All four previous statements (region, per-channel and all-channel messages, adverts) run on `api_slow`: 8 connections, like the old `api_legacy`, ungated, with the 5 s connection wait and a 120 s statement limit (the old pool had no limit). The total connection count grows by 6. - If migration `046` (the drops release) has run, rebuild `idx_observations_observer_id` first, because the restored adverts statement reads it: ```sql CREATE INDEX CONCURRENTLY idx_observations_observer_id ON packet_observations USING btree (observer_id, id); SELECT indisvalid AND indisready FROM pg_index WHERE indexrelid = 'idx_observations_observer_id'::regclass; ``` The second statement must print `t` before you deploy. ## Postgres settings The stacks in this repo run stock `postgres:16-alpine` with no tuning. The settings below suit a busy deployment. Run them as the database superuser (`POSTGRES_USER`, `beacon` in the shipped compose files) in `psql` as above. ### Command line or `ALTER SYSTEM` A setting passed on the Postgres command line (`postgres -c name=value`, in the `command:` of the `db` service) wins over `ALTER SYSTEM`, and `ALTER SYSTEM` cannot change it. Check where each setting comes from first: ```sql SELECT name, setting, source FROM pg_settings WHERE name IN ('jit','wal_compression','max_wal_size','checkpoint_timeout','checkpoint_completion_target','bgwriter_lru_maxpages','bgwriter_lru_multiplier','bgwriter_delay','track_io_timing','session_preload_libraries','log_autovacuum_min_duration','log_lock_waits','log_temp_files','log_min_duration_statement'); ``` For a row whose `source` is `command line`, change the value in the container command instead; it takes effect at the next Postgres restart. The shipped compose files set no command, so there everything comes from `ALTER SYSTEM` or the defaults. ### Settings that apply on reload These take effect without a restart. Two checks first: - `docker compose exec db pg_test_timing` must report a per-loop time under 100 ns. If it is higher, skip `track_io_timing`; timing every read would cost more than it tells you. - `LOAD 'auto_explain';` must succeed in `psql`. If it fails, do not set `session_preload_libraries`: a library that cannot load there breaks every new connection, Beacon's included. Then: ```sql CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- track=top, max=5000 are already the defaults ALTER SYSTEM SET jit = off; ALTER SYSTEM SET max_wal_size = '16GB'; ALTER SYSTEM SET checkpoint_timeout = '30min'; ALTER SYSTEM SET checkpoint_completion_target = 0.9; ALTER SYSTEM SET bgwriter_lru_maxpages = 1000; ALTER SYSTEM SET bgwriter_lru_multiplier = 4; ALTER SYSTEM SET bgwriter_delay = '100ms'; ALTER SYSTEM SET log_autovacuum_min_duration = '60s'; ALTER SYSTEM SET log_lock_waits = on; ALTER SYSTEM SET log_temp_files = '64MB'; ALTER SYSTEM SET log_min_duration_statement = '5s'; ALTER SYSTEM SET wal_compression = 'zstd'; -- only if its source is not 'command line' LOAD 'auto_explain'; -- must succeed first: a bad name breaks every new connection ALTER SYSTEM SET session_preload_libraries = 'auto_explain'; ALTER SYSTEM SET auto_explain.log_min_duration = '2s'; ALTER SYSTEM SET auto_explain.log_format = 'json'; ALTER SYSTEM SET auto_explain.log_analyze = off; ALTER SYSTEM SET track_io_timing = on; -- only after pg_test_timing in the container shows < 100 ns SELECT pg_reload_conf(); DROP INDEX CONCURRENTLY idx_known_routes_iata_last_seen; ``` Notes: - `max_wal_size = '16GB'` lets WAL grow that far between checkpoints. Make sure the volume under `data/postgres/` has the room, or pick a smaller value on a small host. - `pg_stat_statements` only collects once it is in `shared_preload_libraries`, which needs a restart. If it is not there yet, add it at the restart in the next section. Beacon's migration 010 creates the extension itself where it can; see [Statement statistics](#statement-statistics). - `DROP INDEX CONCURRENTLY` cannot run inside a transaction block; run it on its own in autocommit mode, as `psql` does by default. It takes a brief `SHARE UPDATE EXCLUSIVE` lock and waits for older transactions, so run the pre-deploy check above first. It frees about 10 GB on a large deployment. Leave `idx_packets_payload_type` and `idx_packets_route_type` alone; packet queries still use them. Verify: ```sql SELECT name, setting, source, pending_restart FROM pg_settings WHERE name IN ('jit','wal_compression','max_wal_size','checkpoint_timeout','checkpoint_completion_target','bgwriter_lru_maxpages','bgwriter_lru_multiplier','bgwriter_delay','track_io_timing','session_preload_libraries','log_autovacuum_min_duration','log_lock_waits','log_temp_files','log_min_duration_statement'); SELECT to_regclass('idx_known_routes_iata_last_seen'); -- NULL once the index is gone ``` `pending_restart` must be false on every row. Open a new `psql` session and run `SHOW session_preload_libraries;`; it should connect and show `auto_explain`. Statements over 2 s then appear in `docker compose logs db` with their plans as JSON. Rollback: `ALTER SYSTEM RESET ;` for each setting, then `SELECT pg_reload_conf();`. If new connections fail after the `session_preload_libraries` change, reset that one first from a session that is still open. The dropped index had no readers; if you ever want it back, build it with `CREATE INDEX CONCURRENTLY idx_known_routes_iata_last_seen ON known_routes (iata, last_seen DESC);`. ### Settings that need a restart Raise the freeze ages, so autovacuum starts its anti-wraparound passes later and less often: ```sql ALTER SYSTEM SET autovacuum_freeze_max_age = 400000000; ALTER SYSTEM SET vacuum_freeze_table_age = 300000000; ``` `autovacuum_freeze_max_age` only changes at a restart, so restart Postgres off-peak. It takes about 10 seconds; expect a short ingest catch-up after it. At the same restart, apply anything from the reload step whose source is `command line`, for example `wal_compression=zstd`, and `shared_preload_libraries=pg_stat_statements` if that is not loaded yet. Put them in the `db` service's command: ```yaml db: image: postgres:16-alpine command: ["postgres", "-c", "wal_compression=zstd"] ``` If the service already has a `command:`, edit its `-c` values rather than adding a second one. Then `docker compose up -d db` if you changed the command, or `docker compose restart db` if you only ran the `ALTER SYSTEM` lines. Verify: ```sql SHOW autovacuum_freeze_max_age; SHOW wal_compression; SELECT max(age(datfrozenxid)) FROM pg_database; ``` Rollback: `ALTER SYSTEM RESET autovacuum_freeze_max_age; ALTER SYSTEM RESET vacuum_freeze_table_age;`, put the old command back, and restart again. ## Rebuilding the route keyset indexes `/routes` paging and route retention read two indexes on `known_routes`, `idx_known_routes_keyset` and `idx_known_routes_iata_keyset`. Steady deletes and upserts leave them bloated, so rebuild them every 7 to 14 days, and once after upgrading to the release that drops the unused route indexes (`idx_known_routes_last_seen`, `idx_known_routes_hop_count` and `idx_known_routes_reconfirm`). Rebuild one index at a time, off-peak. Each rebuild writes a full new copy before it drops the old one, so check first that the volume under `data/postgres/` has more than twice the index's size free: ```sql SELECT pg_size_pretty(pg_relation_size('idx_known_routes_keyset')), pg_size_pretty(pg_relation_size('idx_known_routes_iata_keyset')); ``` ```bash df -h data/postgres ``` Then run the pre-deploy check above and, in autocommit mode: ```sql REINDEX INDEX CONCURRENTLY idx_known_routes_keyset; ``` When it has finished, check the disk again and run: ```sql REINDEX INDEX CONCURRENTLY idx_known_routes_iata_keyset; ``` Reads and writes carry on during the rebuild. If one is interrupted (cancelled, connection lost, server restarted), it leaves an invalid copy named after the index with a `_ccnew` suffix. The original index is still in place and still used. Find the leftover and drop it before trying again: ```sql SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; DROP INDEX CONCURRENTLY IF EXISTS idx_known_routes_keyset_ccnew; -- or idx_known_routes_iata_keyset_ccnew ``` ## Database pools and connections Beacon opens a separate connection pool per workload, sized by `database.api_plan_layout` (see [Configuration](configuration.md#database-pools-and-cost-guards)). Each pool sets its own session settings and an `application_name`, so `pg_stat_activity` shows which one a connection belongs to: | Pool | `application_name` | Size | Session settings | |---|---|---:|---| | `api_legacy` | `beacon-api_legacy` | 8, `single` only | Server defaults. The rollback layout's only API pool. | | `api_slow` | `beacon-api_slow` | 2 (`slow_pool_size`), not under `single` | Server defaults, with `statement_timeout=120s` and `max_parallel_workers_per_gather=0`. Only the [slow region and message statements](#slow-region-and-message-statements). | | `api_custom` | `beacon-api_custom` | 8 (7 under `split`) | `plan_cache_mode=force_custom_plan`, `jit=off`, `max_parallel_workers_per_gather=0`, `statement_timeout=35s`, `idle_in_transaction_session_timeout=30s` | | `api_cached` | `beacon-api_cached` | 5, `split` only | As `api_custom`, with `plan_cache_mode=force_generic_plan` | | `ingest` | `beacon-ingest` | 6, or one per CPU above 6 | `plan_cache_mode=auto`, `jit=off`, no statement timeout: an ingest write waits out a lock rather than drop a packet | | `maint` | `beacon-maint` | 4 | `plan_cache_mode=force_custom_plan`, `jit=off`, `max_parallel_workers_per_gather=0`. Background jobs, importers, backups and admin queries. | The `single` layout opens only `api_legacy`, `ingest` and `maint`; the other layouts never open `api_legacy`. - **Headroom.** `SHOW max_connections;` must leave room for the total (20 by default on up to 6 CPUs) plus every other client: `psql` sessions, backups, monitoring. If your deploy starts the new server before the old one stops, count both. - **Checking.** Parallel workers carry their leader's `application_name`, so count client backends only: ```sql SELECT application_name, count(*) FROM pg_stat_activity WHERE application_name LIKE 'beacon-%' AND backend_type = 'client backend' GROUP BY 1; ``` No pool should go above its size, `beacon-api_cached` should be absent under `custom_all`, and `beacon-api_legacy` absent except under `single`. - **`pool_max_conns`.** A `pool_max_conns` in `POSTGRES_DSN` is overridden by the pool sizes; the server logs that at startup. - **PgBouncer.** The session settings are sent as startup parameters. Behind PgBouncer, `ignore_startup_parameters` must list `plan_cache_mode`, `jit`, `statement_timeout`, `idle_in_transaction_session_timeout` and `max_parallel_workers_per_gather`, or connections are refused. PgBouncer then drops them, so the pools run on the server defaults; connect Beacon to Postgres directly where you can. - **Background jobs** share the 4 `maint` connections without parallel plans, so a job may take a little longer than before. `beacon_job_duration_seconds` shows it. ### Small hosts Each hourly analytics rollup sets `work_mem` to 256 MB for its own transaction, and Postgres may use that much for every sort or hash step in it. On a host with little memory, watch the `db` container's memory while rollups run (about 95 minutes past each hour). ## Statement statistics `GET /api/v1/admin/db/statements` sums `pg_stat_statements` by statement name (format in the [API contract](api-contract.md#admin)). It needs the extension created and preloaded: - **Created.** Migration `010_pg_stat_statements.sql` creates the extension when the server offers it and the database role may. If not, it logs a notice and is still recorded, so startup never fails over it. The shipped stacks connect as the superuser, so it is created. Otherwise run `CREATE EXTENSION pg_stat_statements;` as a superuser. - **Preloaded.** It only collects once `shared_preload_libraries` includes it, which needs a Postgres restart. Add it to the `db` service's command at the next restart (see [Settings that need a restart](#settings-that-need-a-restart)), keeping any `-c` values already there: ```yaml db: command: ["postgres", "-c", "shared_preload_libraries=pg_stat_statements"] ``` Until both are true the endpoint returns `503`. Check with `SHOW shared_preload_libraries;` and a request to the endpoint with the admin key. Planning time (`plan_ms`) is only recorded while `pg_stat_statements.track_planning` is on. It adds overhead, so turn it on for one day to measure, then off again; both apply on reload: ```sql ALTER SYSTEM SET pg_stat_statements.track_planning = on; SELECT pg_reload_conf(); -- a day later ALTER SYSTEM RESET pg_stat_statements.track_planning; SELECT pg_reload_conf(); ``` API statements are planned on every call, so summed `plan_ms` should stay a small fraction of summed `exec_ms`, under about 7%. ## Metrics With `server.admin_listen` set, the server opens a second listener that serves only `GET /metrics` in Prometheus format. It has no authentication. `/metrics` is not served on the public port. In the Docker stack the listener runs inside the `app` container, so bind it to all of the container's interfaces and publish it on the host's loopback only: ```yaml # config.yaml server: admin_listen: "0.0.0.0:9090" ``` ```yaml # docker-compose.yml, app service ports: - "127.0.0.1:8080:8080" - "127.0.0.1:9090:9090" ``` Never route it through Caddy. Scrape it from the host: ```yaml scrape_configs: - job_name: beacon static_configs: - targets: ["127.0.0.1:9090"] ``` | Metric | Type | Labels | What it is | |---|---|---|---| | `beacon_http_request_seconds` | histogram | `route`, `family` | HTTP request time by route pattern | | `beacon_db_time_seconds` | histogram | `statement`, `pool` | Time per database statement | | `beacon_pool_acquire_seconds` | histogram | `pool` | Wait for a pooled connection | | `beacon_guard_db_seconds` | histogram | `family`, `class` | Database time per guarded request | | `beacon_guard_would_refuse_total` | counter | `family`, `guard` | Requests a guard would refuse if its family enforced | | `beacon_guard_refused_total` | counter | `family`, `guard` | Requests a guard refused | | `beacon_ingest_queue_depth` | gauge | | Messages waiting in the ingest queue | | `beacon_ingest_dropped_total` | counter | | Messages dropped because the ingest queue was full | | `beacon_job_duration_seconds` | histogram | `job` | Background job run time (`cleanup`, `analytics_rollup`, `reconfirm`, `route_retention`, ...) | | `beacon_job_rows_total` | counter | `job` | Rows removed by background jobs, today `reconfirm` and `route_retention` | | `beacon_index_bytes` | gauge | `index` | Size of each index, hourly | | `beacon_index_bloat_ratio` | gauge | `index` | Bytes per row against the last rebuild, hourly. See [Index health](#index-health). | | `beacon_pg_wal_bytes_total` | counter | | WAL written, cluster-wide | | `beacon_pg_checkpoints_total` | counter | `kind` | Checkpoints, `timed` or `requested` | | `beacon_pg_xid_age` | gauge | | Age of the oldest transaction ID in the database | | `beacon_planguard_failures` | gauge | `entry`, `mode` | 1 for each corpus entry and plan mode that failed the last [plan check](#turning-on-the-daily-check) | | `beacon_planguard_last_run_timestamp_seconds` | gauge | | Unix time the last successful plan check finished; 0 until the first one | | `beacon_readmodel_trusted` | gauge | `model` | 1 while this process serves pages from the read model ([`cmi`](#read-models-channel-messages-by-site) or [`packet_sites`](#read-models-packets-by-site)), 0 while those pages answer `503` | | `beacon_readmodel_repaired_rows_total` | counter | `model` | Rows the read-model reconcile repaired; above zero, a writer bypassed the model | | `beacon_readmodel_watermark_age_seconds` | gauge | `model` | Seconds since the last finished reconcile of the read model (on a read-only server, since the writer last saved its state) | | `beacon_packet_sites_sweep_age_seconds` | gauge | | Seconds since the last successful `packet_sites` front sweep; absent before the first | | `beacon_packet_sites_sweep_plan_ok` | gauge | | 1 while the front sweep plans as index scans on its three indexes, 0 otherwise; checked at the first sweep, then hourly | | `beacon_packet_sites_front_live` | gauge | `index` | Index entries written in the last two minutes that the last front sweep visited | | `beacon_packet_sites_bytes` | gauge | | Total size of `packet_sites`, heap and indexes, hourly | | `beacon_db_sequence_used_ratio` | gauge | `sequence`, `cycle` | Share of a sequence's usable values already handed out, 0 to 1, each minute. The usable range ends at the sequence's own limit or its column's type limit (`integer` 2,147,483,647), whichever is lower. `cycle="true"` marks a `CYCLE` sequence, whose ratio is its position, not used-up headroom. See [Sequence headroom](#sequence-headroom). | | `beacon_api_unavailable_total` | counter | `reason`, `model` | `503`s a page answered: `not_ready` while its read model or backfill is not ready, `slow_pool_busy` when `api_slow` had no free connection, `timeout` when its statement ran past its pool's statement limit or an enforced guard's deadline. `model` is `packet_sites`, `cmi` or `observation_payload_types`. | `guard` is `deadline`, `cap`, `admission` or `budget`. The Postgres counters are read once a minute; the index gauges first appear an hour after startup. The two `beacon_planguard_*` gauges exist only with `planguard.daily_check: true`. Go runtime and process metrics are included too. ### Example alert rules ```yaml groups: - name: beacon rules: - alert: BeaconGuardWouldRefuse # any would-refuse delays enforcing that family expr: sum by (family, guard) (increase(beacon_guard_would_refuse_total[1h])) > 0 - alert: BeaconDeadlineHits expr: sum(increase(beacon_guard_refused_total{guard="deadline"}[1h])) > 20 - alert: BeaconListSlow # list-class database time p99 over 1 s expr: histogram_quantile(0.99, sum by (le) (rate(beacon_guard_db_seconds_bucket{class="list"}[15m]))) > 1 for: 15m - alert: BeaconPoolAcquireSlow expr: histogram_quantile(0.99, sum by (le, pool) (rate(beacon_pool_acquire_seconds_bucket[5m]))) > 0.5 for: 10m - alert: BeaconCleanupLong # a cleanup run over 10 min in the last 2 h expr: increase(beacon_job_duration_seconds_count{job="cleanup"}[2h]) - increase(beacon_job_duration_seconds_bucket{job="cleanup",le="600"}[2h]) > 0 - alert: BeaconKeysetBloat expr: beacon_index_bloat_ratio{index=~"idx_known_routes_(iata_)?keyset"} > 4 - alert: BeaconCheckpointsRequested expr: increase(beacon_pg_checkpoints_total{kind="requested"}[1d]) > increase(beacon_pg_checkpoints_total{kind="timed"}[1d]) - alert: BeaconWALAboveBaseline # set the baseline from your own steady state expr: rate(beacon_pg_wal_bytes_total[1h]) > - alert: BeaconXIDAge expr: beacon_pg_xid_age > 300e6 # The two plan check rules only matter with planguard.daily_check: true. - alert: BeaconPlanguardFailures # the last plan check found a regression expr: sum(beacon_planguard_failures) > 0 - alert: BeaconPlanguardStale # no successful plan check for 26 h expr: time() - beacon_planguard_last_run_timestamp_seconds > 26*3600 and on() time() - process_start_time_seconds > 26*3600 # The read-model rules don't apply with readmodels.cmi.enabled: false, where trusted stays 0. - alert: BeaconReadModelRepairing # reconciles repaired rows in 3 consecutive 5-minute windows expr: min_over_time((increase(beacon_readmodel_repaired_rows_total{model="cmi"}[5m]) > bool 0)[15m:5m]) == 1 - alert: BeaconReadModelUntrusted # region and scope message pages answering 503 expr: beacon_readmodel_trusted{model="cmi"} == 0 for: 15m - alert: BeaconReadModelStale # no finished reconcile (or writer save) for 15 min expr: beacon_readmodel_watermark_age_seconds{model="cmi"} > 900 # The packet_sites trust rules don't apply with readmodels.packet_sites.enabled: false. # Install the two sweep rules only for the server that runs background jobs, never for a # read-only server: it never sweeps, so its sweep_plan_ok stays 0 and the plan rule would fire. - alert: BeaconPacketSitesRepairing # a reconcile or audit repaired rows: a writer bypassed the model expr: increase(beacon_readmodel_repaired_rows_total{model="packet_sites"}[10m]) > 0 - alert: BeaconPacketSitesUntrusted # region packet pages answering 503 expr: beacon_readmodel_trusted{model="packet_sites"} == 0 for: 15m - alert: BeaconPacketSitesStale # no finished reconcile (or writer save) for 15 min expr: beacon_readmodel_watermark_age_seconds{model="packet_sites"} > 900 - alert: BeaconPacketSitesSweepStale expr: beacon_packet_sites_sweep_age_seconds > 300 - alert: BeaconPacketSitesSweepPlan # the sweep sets no dead-entry hints; the indexes will bloat expr: beacon_packet_sites_sweep_plan_ok == 0 for: 5m - alert: BeaconPacketSitesBloatWarn expr: beacon_index_bloat_ratio{index=~"idx_packet_sites_site(_type|_scope)?"} > 2.5 - alert: BeaconPacketSitesBloat expr: beacon_index_bloat_ratio{index=~"idx_packet_sites_site(_type|_scope)?"} > 3 - alert: BeaconPacketSitesSize # warn; scale 2.5 GiB for your deployment expr: beacon_packet_sites_bytes > 2.5 * 1024^3 - alert: BeaconAdvertsNotReady # observer advert pages answering 503 for over 2 h expr: sum(increase(beacon_api_unavailable_total{reason="not_ready",model="observation_payload_types"}[15m])) > 0 for: 2h - alert: BeaconSlowPoolBusy # region or message requests refused for want of a slow connection expr: sum(increase(beacon_api_unavailable_total{reason="slow_pool_busy"}[15m])) > 10 # The sequence rules leave out CYCLE sequences, which wrap instead of running out. - alert: BeaconSequenceHalfUsed expr: beacon_db_sequence_used_ratio{cycle="false"} > 0.5 for: 1h labels: {severity: warning} annotations: {summary: "Sequence {{ $labels.sequence }} has used over half of its values"} - alert: BeaconSequenceRunsOutIn90Days expr: predict_linear(beacon_db_sequence_used_ratio{cycle="false"}[7d], 90 * 86400) >= 1 for: 6h labels: {severity: critical} annotations: {summary: "Sequence {{ $labels.sequence }} runs out within 90 days at its current rate"} ``` The two `Untrusted` rules fire while a read model's pages answer `503` for more than 15 minutes, including during a long first fill after an upgrade; see [Pages that wait for a read model](#pages-that-wait-for-a-read-model). Replace `` with your normal WAL rate before loading the rules. The route recheck has no metric yet; at `debug` level it logs a `route reconfirm` line per slice, and `wrapped=true` marks the end of a full cycle. ## Enforcing a guard family Every guard family only observes until you enforce it (see [Configuration](configuration.md#database-pools-and-cost-guards)). Observe for 7 days with metrics on, then enforce a family only when all three hold for it over those 7 days. With `routes` as the example (Prometheus needs at least 7 days of retention): 1. No would-refuse from any guard. Every series must be 0: ```promql sum by (guard) (increase(beacon_guard_would_refuse_total{family="routes"}[7d])) ``` 2. The p99 database time of each class is at most a quarter of its limit: entity 1.25 s, list 2.5 s, aggregation 7.5 s. ```promql histogram_quantile(0.99, sum by (le, class) (rate(beacon_guard_db_seconds_bucket{family="routes"}[7d]))) ``` 3. No request used more than half its limit. Histograms have no exact maximum, so check that no request landed above the bucket at or under half the limit: `le="2.5"` for entity, `le="5"` for list, `le="10"` for aggregation (stricter than 15 s, which has no bucket). Each must be 0: ```promql sum(increase(beacon_guard_db_seconds_count{family="routes",class="list"}[7d])) - sum(increase(beacon_guard_db_seconds_bucket{family="routes",class="list",le="5"}[7d])) ``` Then enforce it at runtime, listing every family that should enforce (the list replaces the current one): ```bash curl -s -X PUT https:///api/v1/admin/config \ -H "Authorization: Bearer $BEACON_API_KEY" -H 'Content-Type: application/json' \ -d '{"guards":{"enforce":["other","routes"]}}' ``` The response's `config.guards.enforce` shows the new list. Add the same list to `guards.enforce` in `config.yaml`, or the next restart goes back to the old one. To stop enforcing, `PUT` the list without that family (`[]` for none). `beacon_guard_refused_total` then counts real refusals. `other` and `routes` are the families to start with. The rest are not ready in this release: `channel_messages` statements are not guarded yet, and the packet and stats families still run statements that later releases make cheaper. Their release notes will say when each is ready, roughly `channel_messages` first, then `packets_global`, then `packets_region` and `packets_window`, then `stats` and `packet_detail`. `packets_window` may stay observed for good. ## Index health Once an hour the server records each index's size (`beacon_index_bytes`) and its bytes per row against a baseline (`beacon_index_bloat_ratio`). The baseline is taken the first time the server sees the index and again whenever it is rebuilt, so the ratio is growth since the last rebuild, not absolute bloat: 1 means as compact as then, 4 means four times the bytes per row. An index that has never been analyzed has no ratio. The four `packet_sites` indexes are never built fresh on a full table, so their first-seen size would make a poor baseline. They start from the bytes per row of a fresh build instead, once the table holds 100,000 rows; below that they have no ratio. A rebuild of one of them re-baselines it from its measured size, as for any index. The first run, and then one run a week, also logs `indexes with no scans since the last statistics reset` with each unused non-unique index and its size. An index that stays on that list for weeks is a candidate to drop; check that no rare job reads it first. When `BeaconKeysetBloat` fires, rebuild that index as in [Rebuilding the route keyset indexes](#rebuilding-the-route-keyset-indexes). The rebuild resets its baseline, and the ratio drops back to about 1 on the next hourly run. ## Lookup-table indexes and the route stamp column Migrations `047` to `052` drop six indexes that no statement's plan reads; every lookup they served goes through a primary key or a table of a few hundred rows: | Index | Table | Why it goes | Rebuild (seconds) | |---|---|---|---| | `idx_channel_iatas_iata` | `channel_iatas` | nothing looks up channel sites by IATA | `CREATE INDEX CONCURRENTLY idx_channel_iatas_iata ON channel_iatas USING btree (iata);` | | `idx_node_neighbors_node` | `node_neighbors` | the primary key `(node_id, neighbor_id, iata)` serves every lookup by node | `CREATE INDEX CONCURRENTLY idx_node_neighbors_node ON node_neighbors USING btree (node_id, iata);` | | `idx_transport_scopes_fingerprint` | `transport_scopes` | scopes are loaded whole | `CREATE INDEX CONCURRENTLY idx_transport_scopes_fingerprint ON transport_scopes USING btree (key_fingerprint);` | | `idx_meshmapper_channel_members_fingerprint` | `meshmapper_channel_members` | channel pages read members by IATA through the primary key | `CREATE INDEX CONCURRENTLY idx_meshmapper_channel_members_fingerprint ON meshmapper_channel_members USING btree (key_fingerprint);` | | `idx_region_iatas_iata` | `region_iatas` | regions are read by id; the IATA lookup reads a tiny table | `CREATE INDEX CONCURRENTLY idx_region_iatas_iata ON region_iatas USING btree (iata);` | | `idx_trace_tags_first_heard` | `trace_tags` | trace pages walk the keyset index | `CREATE INDEX CONCURRENTLY idx_trace_tags_first_heard ON trace_tags USING btree (first_heard_at);` | `idx_analytics_scope_sets_scope` stays: `/scopes/{name}` reads it twice, and without it a rare scope's lookup becomes two full scans of the scope rollup table. No older image needs the six indexes for its plans; rebuilding them after a rollback is optional. Migration `053` drops `known_routes.last_reconfirmed_at`. No statement of this release reads it: route reconfirm stopped stamping it and its index went in earlier releases. The old values are gone for good (no client ever saw them). Every older image needs the column back before it starts, because its route evidence statement lists it and 2.0.7's reconfirm writes it: ```sql ALTER TABLE known_routes ADD COLUMN last_reconfirmed_at timestamp with time zone DEFAULT now() NOT NULL; ``` This is a catalog change, not a rewrite (8 ms on a 10.5-million-row copy), and takes an `ACCESS EXCLUSIVE` lock on `known_routes` for that long. Every route reads the time of the `ALTER`; 2.0.7 then walks routes in arbitrary order on its first reconfirm cycle, which changes nothing it deletes. To roll forward again, the new image's migration `053` is already recorded, so drop the column by hand with `ALTER TABLE known_routes DROP COLUMN last_reconfirmed_at;`, or delete the `053_drop_known_routes_last_reconfirmed_at.sql` row from `schema_migrations` so the migration runs again. ## Sequence headroom `beacon_db_sequence_used_ratio{sequence}` is, for every sequence in Beacon's schema, the share of its usable values already handed out: 0 when unused, 1 when exhausted. The usable range ends at the sequence's own maximum or at its column's type limit, whichever is lower, so an `integer` column on a `bigint` sequence counts against 2,147,483,647. A descending sequence counts from its top. A `CYCLE` sequence wraps to its start instead of running out, so it is reported by position with `cycle="true"`, and the example rules leave it out; Beacon creates none. On a large deployment `channels_id_seq` read about 0.02 and every `bigint` sequence about 0. `channels_id_seq` used to burn about 2,000 values a minute through upsert conflicts (fixed by the channel write-through); the `BeaconSequenceRunsOutIn90Days` rule in [Example alert rules](#example-alert-rules) would have caught that. An `integer` sequence nearing 1 needs its column changed to `bigint`, a table rewrite to plan off-peak. After deploying, check that `channels_id_seq` stays flat over an hour: `delta(beacon_db_sequence_used_ratio{sequence="channels_id_seq"}[1h])` near 0. ## Plan checks (planguard) planguard checks that PostgreSQL still plans each of Beacon's statements the way it was designed. Its corpus, in the server repo under `db/planguard/`, lists every API and job statement with parameters and what its plan must look like: the driving index and its index conditions, the scans it must never use, and buffer, row and time budgets. It runs at three tiers against three databases: | Tier | Database | How it plans | Checks | Who runs it | |---|---|---|---|---| | `fixture` | A synthetic database built through the migrations | Plain `EXPLAIN` | Plan shapes, against the committed `baseline/fixture` | CI, on every server pull request | | `local` | A copy of a large production database, or a restored dump | `EXPLAIN (ANALYZE, BUFFERS)`, read-only | Plan shapes and budgets, against your own `baseline/local` | Maintainers, before a release | | `prod` | A live production database | Plain `EXPLAIN`, read-only, 5 s per plan | Plan shapes only, no baseline | The server's [daily check](#turning-on-the-daily-check), or by hand through psql | Every tier runs each statement inside a `READ ONLY` transaction with a 1 s lock timeout and rolls it back. Only the `local` tier executes statements, and then only reads; writes are only ever planned. Each run gets a verdict: | Verdict | Meaning | Blocks a release | |---|---|---| | `ok` | The plan matches its expectations. | No | | `FALSE_POSITIVE` | A statement that should pass broke a rule: a real regression, or an expectation that no longer holds on this database. | Yes | | `MISSED` | A planted regression (an entry that must fail) passed, so the check itself is not catching what it should. | Yes | | `ERROR` | The statement could not be planned. | Yes | | `DETECTED` | A planted regression failed, as it should. | No | | `RETIRED_INDEX` | The only failures name an index that a pending change drops. Expected until that change ships. | No | | `KNOWN_ISSUE` | A failure the corpus already records as known. | No | | `SKIP` | The run was not made, for example a statement whose feature is not built yet or whose parameters found no value. | No | The command line tool is `cmd/planguard` in the server repo. Run it from a checkout of the commit you are about to release. It exits 0 when no run fails and 1 when one does, and writes its results to `planguard-out/`. ### Fixture tier CI builds the fixture and runs this tier on every pull request. To run it yourself you need an empty database with C collation on PostgreSQL 16 (CI pins 16.15): ```sql CREATE DATABASE planguard TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C' ENCODING 'UTF8'; ``` ```bash go run ./cmd/planguard run --tier fixture --dsn 'postgres://...:5432/planguard' ``` The first run builds the fixture through the migrations, which takes a minute or two; later runs reuse it while its inputs are unchanged. The builder refuses a database that already holds data or is not C-collated. ### Local tier The local tier is the release gate. Run it against a copy of a large production database, or a restored dump, never against the live one: it executes every read statement. The copy needs its own baseline (written to `db/planguard/baseline/local/` in your checkout), so write one on the first run, review it, then check against it: ```bash go run ./cmd/planguard run --tier local --dsn "$COPY_DSN" --baseline write go run ./cmd/planguard run --tier local --dsn "$COPY_DSN" # --baseline check is the default ``` A plan that changes against the baseline fails the check run. When the change is intended, write the baseline again and keep it with the copy. `--only` and `--skip` take a regular expression of entry ids to narrow a run. ### Prod tier through psql The server's daily check runs the prod tier itself. To get the same evidence from a database the server does not check, for example before turning the check on, emit read-only psql scripts and run them yourself. Nothing connects from the tool; you run each script and hand its output back: ```bash go run ./cmd/planguard run --tier prod --emit resolvers > resolvers.sql docker compose exec -T db psql -X -U beacon -d beacon < resolvers.sql > resolvers.out go run ./cmd/planguard run --tier prod --emit plans --params resolvers.out > plans.sql docker compose exec -T db psql -X -U beacon -d beacon < plans.sql > plans.out go run ./cmd/planguard run --tier prod --params resolvers.out --plans plans.out ``` The first script looks up parameter values (20 s statement timeout); the second runs plain `EXPLAIN`s with them. Both set `default_transaction_read_only` and a 1 s lock timeout. Each script carries a random marker, and the last step rejects output that is truncated or does not match the script. ### Before a release Before tagging a server release, maintainers confirm: 1. CI's fixture tier is clean. 2. The local tier, against your copy and its baseline, is clean. 3. On a deployment with the daily check on, `GET /api/v1/admin/db/planguard?fresh=true` returns `"clean": true`, before and after the deploy. `RETIRED_INDEX` runs are expected while an index drop is pending; each names the index that will go. ### Turning on the daily check The daily check is for large deployments only. Its expectations assume production-sized tables, and on a small database the planner rightly picks other plans, which the check reports as failures. Before turning it on, run the prod tier through psql once and look at the result. Then set: ```yaml planguard: daily_check: true ``` and restart the server. It cannot be combined with `server.read_only`. The first check runs about an hour after startup and then once a day; a failed run is retried every hour. Read the last report, or run one now (at most one new run per 5 minutes), with the admin key: ```bash curl -s https:///api/v1/admin/db/planguard \ -H "Authorization: Bearer $BEACON_API_KEY" curl -s 'https:///api/v1/admin/db/planguard?fresh=true' \ -H "Authorization: Bearer $BEACON_API_KEY" ``` `clean` must be true. `failures` lists each failing run with the rules it broke and the indexes it used. The response is described in the [API contract](api-contract.md#admin). With [metrics](#metrics) on, add the two plan check alerts from the [example rules](#example-alert-rules): - `BeaconPlanguardFailures` fires when the last check had a failing run. `beacon_planguard_failures` names the entry and mode. - `BeaconPlanguardStale` fires when no check has succeeded for 26 hours. The last-run gauge is 0 from startup until the first success, so a check that never succeeds fires it too. The `process_start_time_seconds` condition keeps a recent restart from firing it before the first run has had time to happen. To turn the check off, set `daily_check: false` and restart. Nothing is stored in the database. ### Comparing two builds `server.read_only: true` (see [Configuration](configuration.md#read-only-mode)) lets two server builds serve the same database copy side by side, so a change to a query can be checked against real data before it ships. `planguard storediff` then walks the same API pages on both and compares the whole JSON of every page. 1. Build the base and the candidate server binaries. 2. Give each a `config.yaml` with `server.read_only: true` and `ratelimit.enabled: false`, and leave `REDIS_ADDR` unset so neither serves cached responses. Point both `POSTGRES_DSN`s at the same copy, already migrated to the schema both builds expect. Indexes or other objects only the candidate needs can live in a scratch schema on that copy, put first on the candidate's search path with `?search_path=,public` in its DSN. 3. Start them on two ports, for example 8481 and 8482. Each logs a read-only warning at startup and no migration lines. 4. Compare them: ```bash go run ./cmd/planguard storediff --old http://127.0.0.1:8481 --new http://127.0.0.1:8482 \ --cases db/planguard/storediff/smoke.yaml --walk 1,2,3,web --out storediff-out ``` `--walk` lists the page sizes to walk; `web` is each case's web page size. Small page sizes catch pagination that skips or repeats rows. The tool exits non-zero when any page differs, and with `--out` writes `storediff_report.md` there. Never use read-only mode on your live instance: it ingests nothing. ## Rolling back the pool layout If the new pools or plans misbehave, set this and restart the server: ```yaml database: api_plan_layout: single ``` It puts every API statement, the slow ones included, on one pool (`api_legacy`, 8 connections, the server's own settings, no statement limit) and makes every guard family observe, whatever `guards.enforce` says. The alternative is the previous server image. It ignores migration 010; the `pg_stat_statements` extension stays installed and harmless. ## CPU profiling Bounded CPU captures on a running instance are described in [Profiling](profiling.md).