Post
A duplicate can be an observation
BigBrotr stores one Nostr event once and records every relay that served it, because repeated content is redundant while repeated observation is the measurement.
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.
When the protocol picks the key for you
BigBrotr archives signed events from thousands of independent Nostr relays. The same event propagates: a note published once is served by every relay that accepted it, so the archive sees it forty times, from forty sources, at forty different moments.
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. So 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.
That is the easy half, and it is easy only because the protocol did the hard part. When the identity of a record is a property of the record rather than something you assign on arrival, deduplication stops being a pipeline stage.
The half that is an actual decision
Collapsing forty copies into one row would have destroyed the dataset, because which relays carried this event is most of what an observatory exists to know. Propagation, redundancy, and how much of the network any single relay can actually see are all questions about the copies.
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 of first observation, and it is written with the same
ON CONFLICT DO NOTHING. One event row; forty junction rows.
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 saw it 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.