Data model

The fact table, the dimensions around it, and why every repeated string is an integer.

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

A narrow fact table with dimensions beside it, hourly rollups that outlive the detail, and the offline enrichment datasets. The shape is dictated by scale.

In plain terms

One big table, one row per DNS lookup. Beside it, small lookup tables for the things that repeat: device names, domain names, upstream resolvers.

The big table stores a number pointing at those instead of the text. Writing 14 three million times instead of “Kitchen iPad” three million times is most of the difference between a database that fits comfortably on a Raspberry Pi and one that does not.

The fact table#

queries
  id            integer primary key
  hash          text     unique       -- (time, client, domain, qtype) digest
  ts            integer               -- ms since epoch
  device_id     → devices
  domain_id     → domains
  qtype         text                  -- A, AAAA, HTTPS, PTR, …
  reason        text                  -- the resolver's verdict, verbatim
  blocked       boolean               -- denormalised from reason
  status        text                  -- NOERROR, NXDOMAIN, SERVFAIL
  elapsed_ms    real
  cached        boolean
  upstream_id   → upstreams
  rule_list_id  integer               -- the resolver's own list id, sentinel-valued
  rule_text     text
  answer_ips    text                  -- JSON array
  dest_ip       text                  -- first resolved address
  dest_cc       text
  dest_asn      integer
  dest_lat      real
  dest_lon      real
  dest_city     text

The schema is the same on both resolvers. That is the point of the provider seam: reason holds whatever verdict string came back, normalised to a common vocabulary, and nothing downstream branches on which resolver produced it.

Why every repeated string is an integer#

At ~36k queries a day the table crosses three million rows inside a 90-day window, on an SD card. Domain, client, upstream and AS name are all high-cardinality-but-repeating, so each is a dimension row and the fact table carries an integer.

The indexes, and the one that was wrong#

Index	Serves
(ts)	Range scans and retention sweeps
(device_id, ts)	“Everything this device did”
(domain_id, ts)	“Everywhere this name was asked for”
(dest_cc, ts)	The destinations mapThe destinations map. Present but unused on Pi-hole, where the column is always NULL
(reason, ts) / (blocked, ts)	Every aggregate that splits on the verdict
(ts, domain_id) / (ts, device_id)	The distinct counts, as index-only scans

Index on (ts) only

SEARCH queries USING INDEX idx_queries_ts

~80,000 random row lookups to read one column

Index on (ts, domain_id)

SEARCH queries USING COVERING INDEX idx_queries_ts_domain

index-only scan · the table is never opened

The same count, over the same range. The plan on top walked the range correctly and then fetched one small column from each matching row, one random read at a time; the plan below answered from the index without touching the table at all. Both report as using an index, which is why the first one survived as long as it did.

Dimensions#

Table	Keyed by	Notes
devices	address (IP or ClientID)	name, agh_name, mac, vendor, hostname, first/last seen
domains	name	registrable, tracker/company/category, first/last seen
companies / trackers / tracker_categories	companiesdb ids	Loaded from the dataset, not derived
tracker_domains	domain	Searchable by any suffix, not only observed names
upstreams	address	So the same resolver string is stored once
filter_lists	AGH's own filter_list_id	Joins directly to rule_list_id, no translation table

Bookkeeping#

ingest_state is a key/value table rather than columns, so a new cursor never costs a migration: the query-log watermark, the backfill cursor, rollup progress and the last retention sweep all live in it. dataset_versions records which enrichment version is loaded so a 820k-row reload does not happen on every boot.

Reading it yourself#

It is a plain SQLite file. Nothing stops you asking your own questions of it:

sudo docker exec -it elele-dns sqlite3 /data/queries.db

-- the ten loudest names in the last day
SELECT d.name, COUNT(*) n
FROM queries q JOIN domains d ON d.id = q.domain_id
WHERE q.ts > (strftime('%s','now') - 86400) * 1000
GROUP BY d.name ORDER BY n DESC LIMIT 10;