Ingest

How a circular buffer is turned into ninety days of history without gaps or duplicates.

internalsHow it is built. Never needed to run it.page 13 of 24

AdGuard Home returns its log newest-first and offers no “everything after time T” query. Both directions of travel are therefore the same backwards walk; they differ only in where they stop.

Pi-hole could serve a forward window directly, and is walked backwards anyway. Both directions of travel are the same backwards walk; they differ only in where they stop — and the reason for giving up the forward window is below.

In plain terms

Your resolver’s log is a whiteboard that gets wiped from the bottom once it fills up. This copies it into a notebook before that happens.

The awkward part is that you can only read the whiteboard from the newest line upwards. So both jobs — “catch up on what happened since I last looked” and “grab the old stuff before it is wiped” — are the same motion, reading upwards. They just stop in different places: the first stops when it reaches something it already has, the second keeps going until the whiteboard runs out.

Two walks, one mechanism#

	Starts at	Stops at	Bounded by
Catch-up	Now	The watermark: the newest row already stored	Traffic since the last poll, so usually one page
Backfill	The oldest row stored	The end of the resolver's buffer	A fixed number of pages per pass

the resolver’s log

catch-up ↑

14:05:05already stored
14:04:28already stored
14:03:51already stored
14:03:14already stored
14:02:37already stored
14:02:00already stored
14:01:23not yet seen← watermark
14:00:46not yet seen
14:00:09not yet seen
13:59:32not yet seen
13:58:55not yet seen
13:58:18not yet seen
13:57:41not yet seen
13:57:04not yet seen

Newest at the top, because that is the only way the log can be read. Catch-up stops the moment it recognises a row it already has; backfill carries on past the store's oldest row until the log itself runs out. The overlap between them is deliberate — deduplication absorbs it, and the alternative is a gap.

Catch-up runs every tick. Backfill runs alongside it until it completes once, and is deliberately capped per pass so a 3,000-entry buffer never starves the live poll.

The cursor#

FTL also exposes from and until, so catch-up could be a forward window. It is not, deliberately: expressing both walks as one backwards traversal for both resolvers means one set of watermark and cursor bookkeeping rather than two, and the awkward direction is the one that has to work anyway.

Deduplication#

Reading the same page twice must not count a query twice, and because both walks overlap by design, it happens constantly. AdGuard Home assigns no query id at all, so there is nothing to key on even if we wanted to.Pi-hole’s row id looks like it would do, but it is local to the instance that issued it — rebuild FTL’s database and the same query comes back with a different number. So identity for is derived the same way for both. The digest covers (time, client, domain, qtype), the tuple that distinguishes two genuine queries, and is stored UNIQUE on the fact table.

hash = sha256(rawTime ⧺ client ⧺ domain ⧺ qtype)[0:16]
INSERT OR IGNORE INTO queries (hash, ...) VALUES (...)

That makes deduplication a property of the schema rather than of the poller’s timing. Overlapping polls, a restart mid-batch, or backfill re-reading a page catch-up already wrote are all no-ops. Sixteen hex characters is a birthday probability under 1 in 1000 across a 90-day window, and saves roughly 35MB of index against a full digest.

Dimensions#

Every repeated string is an integer foreign key: domains, devices, upstreams, companies. At ~36k queries a day the fact table crosses three million rows inside the retention window, and storing gateway.icloud.com three thousand times is the difference between a 300MB store and a gigabyte one.

Resolution is cached in memory per process, because a poll of fifty rows touches maybe eight distinct domains and the same handful recur for hours. On a miss it is a single statement:

INSERT INTO domains (name, ...) VALUES (?, ...)
ON CONFLICT (name) DO UPDATE SET last_seen = excluded.last_seen
RETURNING id

DO UPDATE rather than DO NOTHING because SQLite’s RETURNING yields no row for an ignored conflict; the update is a no-op touch whose only job is to make the id come back on both paths, in one statement and one lock.

What is stored per query#

Group	Columns
Identity	hash, ts, device_id, domain_id, qtype
Verdict	reason, blocked, service_name, rule_list_id, rule_text
Answer	status, answer_ips, dest_ip, dest_cc, dest_asn, dest_lat, dest_lon, dest_city
Performance	elapsed_ms, cached, upstream_id, dnssec, proto

blocked is denormalised from reason because every aggregate in the product splits on it, and dest_ip is denormalised out of answer_ips because the map needs it indexed. See Data model.

Failure behaviour#

The resolver is unreachable. Backoff grows to a minute and stays there. This is the common case: it restarts, or the Pi reboots, and hammering it every five seconds through an outage helps nobody.
A malformed entry. Skipped and counted, never fatal. One bad row must not stop a page of good ones.
A crash mid-batch. Nothing to reconcile. The watermark only advances after a successful write, and the unique hash makes the replay idempotent.