A duplicate can be an observation

The same record arriving twice is normally filed as a problem to remove on the way in. In a network observatory, the repeated content is redundant but the second observation is not. It says that another relay carried the event, and discarding it deletes part of the measurement.

Deduplication is therefore not a cleanup step. It is a decision about which identity belongs in which table.

The concrete SQL examples below describe BigBrotr’s earlier PostgreSQL implementation. They illustrate the data-modeling decision, not the storage layout of the current Go implementation. The project page describes the current architecture.

When the protocol picks the key for you

BigBrotr collects signed events from independent Nostr relays. The same event can arrive from several relays at different times. Each successful collection supplies the event and an observation about one source.

The identity question here is already settled, and not by me. A Nostr event’s id is the SHA-256 hash of its canonical serialisation: the protocol content-addresses its own records. In the PostgreSQL schema, the events table takes that hash as the primary key directly, as BYTEA, and inserts run ON CONFLICT (id) DO NOTHING. Storing the same event a second time is not an error to handle and not a reconciliation job to schedule later. It is a no-op.

The protocol supplies a stable identity that the database can enforce. Verification still has to establish that the event content matches the supplied identifier before that identifier is trusted for deduplication.

The half that is an actual decision

Collapsing copies into one row without retaining their sources would lose part of the dataset: which relays carried this event. Propagation, redundancy and what a relay served during collection are all questions about those observations.

So the copies are kept, in a second table. event_relay holds (event_id, relay_url, seen_at) with the pair as its composite primary key and the timestamp recorded when the association is inserted, and it is written with the same ON CONFLICT DO NOTHING. One event row, with a separate association for each observed relay.

That conflict rule preserves the first inserted association. It does not replace the timestamp if a later import contains an earlier observation. Preserving the earliest observed time across out-of-order imports requires an explicit update rule in addition to a uniqueness constraint.

The duplicate is not discarded. It is reclassified, from a redundant copy of an event into an observation of a relay. Both tables are hash-partitioned sixteen ways on the event id, so the junction rows for an event land in the same partition as the event itself, and content and tags are LZ4-compressed because the text is the bulk of the archive.

What this cost is the join, and a junction table that is far larger than the table it points at. What it bought is that “how many relays carried this?” and “which relay association was recorded first?” are queries rather than a re-collection.

What to actually ask

Before writing the deduplication step, the useful question is not “how do I detect duplicates” but what does a second copy of this mean?

If it means nothing – the same fact restated by an interchangeable source – put the identity in the primary key and let the database reject it. If it means another observer saw it, the schema needs a second identity for the observation itself.

Getting this wrong is expensive in a specific way. Both mistakes are silent, and both are discovered a year later, when someone asks a question the data can no longer answer.