RSS is more chaotic than it looks. The spec defines a <guid> element to uniquely identify each entry, and most well-behaved publishers set a stable GUID — typically the article's canonical URL — and never change it. A simple INSERT ... ON CONFLICT (feed_id, guid) DO NOTHING handles that case perfectly.
But feeds do not always behave.
Running SlashFeed against a few hundred feeds in production exposed every deduplication edge case I hadn't thought to plan for. Some publishers run WordPress with a plugin that regenerates GUIDs when a post is edited. Some CMS platforms assign a brand-new GUID on every publish, so a one-word typo fix produces a second copy of the same article. Feedburner once had an incident where it re-issued the full archive of a large tech blog with fresh GUIDs, flooding every subscriber's reader with hundreds of "new" articles overnight. Cross-posting networks syndicate the same content to multiple feeds with no attempt at GUID coordination. And that's before you meet feeds where the <guid> element is simply absent and the parser has to synthesize one from a hash of the URL.
A naive insert-or-ignore on GUID alone creates duplicates in every one of those scenarios. SlashFeed uses three layers to prevent them. The third one took me the longest to get right.
Layer 1: GUID uniqueness
The first layer is exactly what you'd expect. Every entry gets a unique_index on (feed_id, guid) in the migration, and the Ecto changeset validates it:
@required_fields ~w(guid content_hash feed_id)a
def changeset(entry, attrs) do
entry
|> cast(attrs, @required_fields ++ @optional_fields)
|> validate_required(@required_fields)
|> unique_constraint([:feed_id, :guid])
|> unique_constraint([:feed_id, :content_hash])
|> foreign_key_constraint(:feed_id)
end
The uniqueness scope is (feed_id, guid) rather than just guid because GUIDs are not globally unique across feeds. Many publishers use incrementing integers — "entry-1", "post-42" — or relative paths as their GUIDs. Scoping to the feed makes the constraint correct.
What makes this layer more useful than a plain DO NOTHING is that the upsert merges edited content back onto the existing row:
conflict_query =
from(e in Entry,
update: [
set: [
title: fragment("EXCLUDED.title"),
content: fragment("EXCLUDED.content"),
summary: fragment("EXCLUDED.summary"),
author: fragment("EXCLUDED.author"),
url: fragment("EXCLUDED.url"),
content_hash: fragment("EXCLUDED.content_hash"),
updated_at: fragment("EXCLUDED.updated_at"),
enclosure_url: fragment("EXCLUDED.enclosure_url"),
enclosure_type: fragment("EXCLUDED.enclosure_type")
]
],
where: ...
)
Repo.insert(changeset,
on_conflict: conflict_query,
conflict_target: [:feed_id, :guid],
returning: true
)
Same GUID, updated content: the existing row is updated in place. The article surfaces as changed rather than duplicated. I'll come back to the where: clause in a moment — it's the heart of the third layer.
Layer 2: Content hashing
GUID uniqueness handles the normal case but not the pathological one: what if the GUID changes but the content is identical?
The solution is a content hash computed before the insert:
@doc """
Computes a SHA-256 content hash from the normalised title, content, and URL.
Used for duplicate detection when GUIDs differ but content is identical.
"""
@spec compute_content_hash(map()) :: String.t()
def compute_content_hash(attrs) when is_map(attrs) do
normalized = Map.new(attrs, fn {k, v} -> {to_string(k), v} end)
title = normalized["title"]
content = normalized["content"]
url = normalized["url"]
[normalize_field(title), normalize_field(content), normalize_url(url)]
|> Enum.join("|")
|> then(&:crypto.hash(:sha256, &1))
|> Base.encode16(case: :lower)
end
defp normalize_field(nil), do: ""
defp normalize_field(value) when is_binary(value), do: String.trim(value)
defp normalize_url(nil), do: ""
defp normalize_url(value) when is_binary(value) do
value |> String.trim() |> String.downcase()
end
The normalisation choices are deliberate:
- Title and content are trimmed but not downcased. A capitalisation change in a headline is a genuine content change — a publisher fixing a proper noun should produce a different hash. Trimming whitespace handles the CMS-noise case where trailing newlines get added or stripped inconsistently.
- The URL is downcased. HTTP URLs are case-insensitive on the scheme and host, and many CMS exports produce inconsistent casing on the path. Downcasing the whole URL avoids spurious hash mismatches from that noise.
- The three fields are joined with a pipe character. A naive concatenation of title + content would be ambiguous: the title of one article might be a prefix of another's full body text. The pipe separator makes the preimage unambiguous.
This hash gets a second unique_index alongside the GUID one:
create unique_index(:entries, [:feed_id, :guid])
create unique_index(:entries, [:feed_id, :content_hash])
create index(:entries, [:published_at])
When the insert succeeds, we have a new entry. When it hits the GUID conflict, the ON CONFLICT DO UPDATE merges the new content. When it hits the content_hash conflict — different GUID, same article — we detect that separately and return a distinct value to the caller:
|> case do
{:ok, entry} ->
{:ok, entry}
{:error, changeset} ->
# content_hash collision: different GUID, identical content
case Repo.get_by(Entry, feed_id: feed_id, content_hash: content_hash) do
%Entry{} = existing -> {:duplicate, existing}
nil -> {:error, changeset}
end
end
{:duplicate, existing} instead of {:error, changeset} gives callers something useful to pattern-match on. In the Broadway parsing pipeline it is silently discarded. A future reconciliation pass could use the returned row to merge read state across the two GUIDs. The distinction is cheap to make at upsert time and expensive to reconstruct later from logs.
Layer 3: No-op re-fetch with IS DISTINCT FROM
The third layer is not about deduplication in the traditional sense. It is about not doing work when nothing has changed.
The feed scheduler runs every five minutes. Most of the time, a feed's content hasn't changed since the last fetch. The GUID-based ON CONFLICT DO UPDATE would still fire an UPDATE on every existing entry because the conflict clause runs unconditionally. That means WAL writes, index maintenance, autovacuum pressure, and updated_at timestamps getting bumped even when the content is byte-for-byte identical. For a feed with 200 entries fetched every hour that's 2,400 pointless writes a day per feed.
PostgreSQL's IS DISTINCT FROM operator solves this. Unlike !=, it handles NULL values correctly: NULL IS DISTINCT FROM NULL is false, NULL IS DISTINCT FROM 'x' is true. A WHERE clause on the ON CONFLICT update fires the write only when at least one tracked field has actually changed:
conflict_query =
from(e in Entry,
update: [
set: [
title: fragment("EXCLUDED.title"),
content: fragment("EXCLUDED.content"),
...
]
],
where:
fragment(
"(?, ?, ?, ?, ?, ?, ?) IS DISTINCT FROM (EXCLUDED.title, EXCLUDED.content, EXCLUDED.summary, EXCLUDED.author, EXCLUDED.url, EXCLUDED.enclosure_url, EXCLUDED.enclosure_type)",
e.title,
e.content,
e.summary,
e.author,
e.url,
e.enclosure_url,
e.enclosure_type
)
)
When every tracked field already matches the incoming row, the WHERE predicate is false. PostgreSQL skips the write entirely. No WAL write, no index update, no updated_at drift. But there is a catch: because the UPDATE is skipped, the RETURNING clause returns zero rows. Ecto's Repo.insert/2 with returning: true then raises Ecto.StaleEntryError — it expected a row back and got nothing.
The Ecto.StaleEntryError rescue
StaleEntryError is normally a concurrency bug: you loaded a record, someone deleted it, and your update landed on nothing. In this case it is intentional and benign — PostgreSQL chose not to write anything because the data was already current. The fix is to rescue the exception and return the existing row:
rescue
Ecto.StaleEntryError ->
# IS DISTINCT FROM WHERE was false — every tracked field already matches.
# PostgreSQL skipped the write; RETURNING returned 0 rows; Ecto raises StaleEntryError.
{:ok, Repo.get_by!(Entry, feed_id: feed_id, guid: guid)}
end
The function returns {:ok, entry} whether the row was freshly written or already current. Callers get a consistent interface either way. The only downstream concern is the PubSub broadcaster — it should not emit a :new_entry event for an unchanged article — and that is handled by the {:duplicate, _} path described above.
Why there are two functions
The actual implementation splits the upsert into a public upsert_entry/2 and a private do_upsert_entry/4. The reason is a variable-shadowing quirk in Ecto's from/2 macro:
def upsert_entry(feed_id, entry_attrs) do
normalized = Map.new(entry_attrs, fn {k, v} -> {to_string(k), v} end)
content_hash = Entry.compute_content_hash(normalized)
guid = normalized["guid"]
attrs =
normalized
|> Map.put("feed_id", feed_id)
|> Map.put("content_hash", content_hash)
do_upsert_entry(attrs, feed_id, guid, content_hash)
end
defp do_upsert_entry(attrs, feed_id, guid, content_hash) do
conflict_query = from(e in Entry, ...)
%Entry{}
|> Entry.changeset(attrs)
|> Repo.insert(on_conflict: conflict_query, ...)
|> case do
...
end
rescue
Ecto.StaleEntryError ->
{:ok, Repo.get_by!(Entry, feed_id: feed_id, guid: guid)}
end
Variables bound inside from/2 macro expansions can shadow outer bindings in the same function clause, making them inaccessible in that clause's rescue block. Pushing the query and rescue into a private function gives the rescue block a clean, unambiguous reference to guid as a plain function parameter — no macro magic in scope.
The {:duplicate, existing} vs {:error, changeset} distinction
One detail worth spelling out explicitly: the content-hash collision and a genuine insert failure look identical at the Postgres level — both surface as a changeset error from Repo.insert/2. The difference is which unique constraint fired.
When the changeset error arrives, we look at the underlying violation. If a row exists with that (feed_id, content_hash) pair, it is definitively a duplicate. If no such row exists, the error is a genuine problem — a malformed changeset, a race condition on another constraint — and we propagate {:error, changeset} unchanged.
{:error, changeset} ->
case Repo.get_by(Entry, feed_id: feed_id, content_hash: content_hash) do
%Entry{} = existing -> {:duplicate, existing}
nil -> {:error, changeset}
end
The extra lookup on the duplicate path adds one SELECT per genuine duplicate, which is rare in practice. The payoff is that callers never need to inspect a changeset to decide whether to log or ignore — the return tag makes the semantics explicit.
What this prevents in practice
Three layers, three failure modes:
-
Normal edited content (same GUID, changed body) — the GUID upsert picks it up. The row is updated,
updated_atis bumped, the entry surfaces as changed. One write, correct behaviour. -
Republished content (new GUID, same article) — the content hash catches it. The caller gets
{:duplicate, existing}instead of a second row for the same article. The reader count stays clean. -
Unchanged fetch (same GUID, same content) —
IS DISTINCT FROMeliminates the write. PostgreSQL skips the update, Ecto raisesStaleEntryError, the rescue returns the existing row. Zero WAL pressure, zeroupdated_atdrift, zero spurious PubSub events.
Here is how it looks from the Broadway pipeline that processes each parsed feed:
defp upsert_and_broadcast(feed_id, entry_attrs) do
case Feeds.upsert_entry(feed_id, entry_attrs) do
{:ok, entry} ->
broadcast_new_entry(feed_id, entry)
maybe_enqueue_scrape(feed_id, entry)
{:duplicate, _existing} ->
:ok
{:error, reason} ->
Logger.warning("Failed to upsert entry for feed #{feed_id}: #{inspect(reason)}")
end
end
Three return values, three clean pattern-match arms. New entry: broadcast and optionally schedule a content scrape. Seen-before content under a different GUID: silently drop. Actual error: log it and move on.
The real-world case that made layer 2 worth the implementation effort: a large tech blog I subscribe to pushed a full site migration that regenerated every article's GUID while keeping the content identical. Without content hashing, every subscriber would have seen 600 "new" articles overnight. With it, the parser marked every one of them {:duplicate, _} and the inbox stayed clean. Layer 3 paid off the next morning, when the scheduler picked those same 600 entries back up on the next fetch cycle and discarded all 600 writes without touching the database.