Zero-write upserts in Ecto with IS DISTINCT FROM

An RSS reader polls. That is the whole job: fetch a feed every few minutes, parse it, and write the entries. The uncomfortable truth is that most of those writes are pointless. A feed that publishes twice a day still serves its entire recent history on every poll — the same thirty entries, byte for byte, every four minutes. If your ingestion does a naive upsert, you are rewriting thirty unchanged rows hundreds of times a day, per feed, across thousands of feeds. Every one of those writes bumps updated_at, produces a new row version, and lands in the write-ahead log.

None of it changes any data. It is pure overhead — and worse, it makes "was this entry actually new?" impossible to answer, which matters the moment you want to send a push notification only for genuinely new articles.

SlashFeed's upsert_entry/2 eliminates the no-op writes with one SQL clause, IS DISTINCT FROM. The clause is easy. The interesting part is the edge case it introduces: when Postgres skips the write, Ecto raises an exception, and turning that exception into a clean result is the trick that makes the whole thing usable. This post walks the full pattern, then zooms out to the three-layer deduplication strategy it lives inside.

Why a plain upsert always writes

The standard Ecto upsert looks like this:

%Entry{}
|> Entry.changeset(attrs)
|> Repo.insert(
  on_conflict: {:replace, [:title, :content, :summary, :updated_at]},
  conflict_target: [:feed_id, :guid]
)

Which compiles to roughly:

INSERT INTO entries (...) VALUES (...)
ON CONFLICT (feed_id, guid)
DO UPDATE SET title = EXCLUDED.title, content = EXCLUDED.content, ...

There is no condition on that DO UPDATE. When the row already exists, Postgres runs the SET unconditionally — even if EXCLUDED.title is identical to the row's current title, even if every column is identical. It writes a new row version, fires triggers, advances updated_at, and appends to the WAL. Postgres has no way to know you consider an all-identical update a no-op; you never told it.

For a feed poll re-serving unchanged entries, that is the common case, not the exception. You want the database to write only when something actually differs.

The IS DISTINCT FROM guard

ON CONFLICT ... DO UPDATE accepts a WHERE clause. If the predicate is false, Postgres skips the update for that row entirely — no write, no WAL, no updated_at bump. The predicate you want is "at least one tracked field differs," and the operator for that is IS DISTINCT FROM.

Why not plain !=? Because these columns are nullable, and in SQL NULL != NULL is NULL, not true — so a != comparison silently mishandles every column that is or becomes null. IS DISTINCT FROM is the null-safe version: it treats NULL as an ordinary comparable value, so NULL IS DISTINCT FROM NULL is false and NULL IS DISTINCT FROM 'x' is true, exactly as you'd want. You can compare whole row tuples with it in one expression.

Here is the real on_conflict query from SlashFeed, expressed as an Ecto query so EXCLUDED and the WHERE are both available:

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"),
        word_count: fragment("EXCLUDED.word_count")
      ]
    ],
    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
      )
  )

The WHERE compares the existing row's tuple of seven tracked fields against the incoming (EXCLUDED) tuple. When they match, the whole tuple comparison is false, and Postgres skips the update. Note the asymmetry that is intentional: updated_at, content_hash, and word_count are in the SET list but not in the comparison tuple — they are derived from the tracked fields, so including them in the guard would be redundant, and including updated_at (a fresh timestamp on every attempt) would defeat the whole thing by always making the tuple distinct.

The insert wires the query into on_conflict and requests returning: true so you get the row back on a real write:

%Entry{}
|> Entry.changeset(attrs)
|> Repo.insert(
  on_conflict: conflict_query,
  conflict_target: [:feed_id, :guid],
  returning: true
)

That is the entire optimisation. Identical re-fetches now cost a single index probe and zero writes.

The catch: a skipped write raises StaleEntryError

Here is the part every write-up omits. When the WHERE is false and Postgres skips the update, the statement affects zero rows. With RETURNING there is nothing to return. Ecto inserted a row expecting exactly one row back — new or updated — so when it gets zero, it concludes the operation was lost to a concurrent change and raises Ecto.StaleEntryError.

This is not a bug. It is Ecto correctly reporting "the write I expected did not happen." But it means the happy path — an unchanged entry — throws an exception. If you don't handle it, every no-op re-fetch crashes your ingestion.

The trick is to rescue that specific error and interpret it as what it actually means: the row already exists with identical content. Fetch the existing row and return it with an :unchanged marker:

%Entry{}
|> Entry.changeset(attrs)
|> Repo.insert(on_conflict: conflict_query, conflict_target: [:feed_id, :guid], returning: true)
|> case do
  {:ok, entry} ->
    if existed_before?, do: {:updated, entry}, else: {:new, 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
rescue
  Ecto.StaleEntryError ->
    # IS DISTINCT FROM was false — every tracked field already matches.
    # Postgres skipped the write, RETURNING returned 0 rows, Ecto raised.
    # Fetch and return the unchanged row so callers can suppress side effects.
    {:unchanged, Repo.get_by!(Entry, feed_id: feed_id, guid: guid)}
end

Now the function reports one of four outcomes: {:new, entry}, {:updated, entry}, {:unchanged, entry}, or {:duplicate, existing}. The public upsert_entry/2 collapses the first three into {:ok, entry} for callers that don't care; the richer upsert_entry_with_status/2 keeps them apart for callers that do.

One implementation footnote worth stealing: the rescue clause needs guid and feed_id as plain function parameters. Variables bound inside an Ecto.Query from/2 macro can shadow outer bindings within the same clause and become invisible to rescue, so the actual code extracts the insert into a small private do_upsert_entry(attrs, feed_id, guid, content_hash) whose parameters are guaranteed visible in the rescue block.

Distinguishing new from updated without a timestamp

If a real write happens, how do you know whether it was an insert or an update? The tempting answer — compare inserted_at and updated_at — is wrong. These columns are :utc_datetime (second precision). A content change that arrives in the same second as the original insert leaves both timestamps equal, so you'd misclassify a genuine update as a new insert.

The reliable signal is a cheap existence probe before the upsert, straight against the (feed_id, guid) unique index:

existed_before? =
  is_binary(guid) and
    Repo.exists?(from(e in Entry, where: e.feed_id == ^feed_id and e.guid == ^guid))

If the row existed before the upsert, a successful write was an update; otherwise it was a new insert. This also gracefully handles two edge cases: a nil guid (invalid attrs) short-circuits the probe and falls through to the changeset's validation error, and a concurrent insert of the same entry is still classified correctly — the loser's ON CONFLICT hits identical content, skips the write, raises StaleEntryError, and returns :unchanged, so it never even reaches the existed_before? branch.

Why the four-way status is worth the trouble

The reason to distinguish :new from the rest is not aesthetic — it is the difference between a working notification system and a spam cannon. The Broadway parser that ingests entries fires side effects on exactly one outcome:

case Feeds.upsert_entry_with_status(feed_id, entry_attrs) do
  {:new, entry} ->
    broadcast_new_entry(feed_id, entry)   # PubSub, webhooks, push

  {status, _entry} when status in [:updated, :unchanged, :duplicate] ->
    :ok                                    # already seen — stay silent
end

Before the :new/:unchanged split existed, unchanged re-fetches were indistinguishable from inserts, so every poll of a feed without ETag/Last-Modified support re-broadcast its entire recent history — LiveView badges lit up, webhooks fired, phones buzzed, all for articles the user had already read. The IS DISTINCT FROM guard plus the four-way status is what makes "notify me about new articles" mean new.

The bigger picture: three layers of RSS deduplication

The upsert guard is the innermost of three deduplication layers, each catching a different way the same article can arrive twice.

Layer 1 — GUID identity. Every entry has a guid, and the database enforces unique_index(:entries, [:feed_id, :guid]). This is the primary conflict target: re-fetching an entry with a GUID you've seen routes into the ON CONFLICT path rather than inserting a duplicate. Most well-behaved feeds are fully deduplicated by this layer alone.

Layer 2 — content hash. Some feeds are not well-behaved. A CMS migration, a "republish" button, or a feed that mints a fresh GUID on every render will serve the same article under a new GUID — Layer 1 sees a new GUID and would happily insert a duplicate. So each entry also carries a content_hash: a SHA-256 over the normalised title, content, and URL, backed by a second unique_index(:entries, [:feed_id, :content_hash]).

def compute_content_hash(attrs) when is_map(attrs) do
  normalized = Map.new(attrs, fn {k, v} -> {to_string(k), v} end)

  [normalize_field(normalized["title"]),
   normalize_field(normalized["content"]),
   normalize_url(normalized["url"])]
  |> Enum.join("|")
  |> then(&:crypto.hash(:sha256, &1))
  |> Base.encode16(case: :lower)
end

When an insert with a new GUID collides on the content-hash index instead, the changeset comes back {:error, ...}; the code looks up the offending row and returns {:duplicate, existing} rather than treating it as a failure. Republished articles are caught silently.

Layer 3 — upsert-skip. For entries that do match on GUID, the IS DISTINCT FROM guard from the top of this post decides whether the matching row needs a write at all. Same GUID, same content ⇒ skip. Same GUID, changed content (an edited article) ⇒ update.

Together the three layers map every possible arrival to the right outcome:

Arrival	Caught by	Outcome
Brand-new article	none — inserts	{:new, entry}
Same GUID, edited content	Layer 1 + Layer 3 guard passes	{:updated, entry}
Same GUID, identical content	Layer 3 guard fails (skip)	{:unchanged, entry}
New GUID, identical content (republish)	Layer 2 content-hash index	{:duplicate, existing}
Invalid attrs (no GUID)	changeset validation	{:error, changeset}

Takeaways

• A plain ON CONFLICT DO UPDATE writes unconditionally. For any workload that re-ingests unchanged rows — feed polls, sync jobs, idempotent imports — that is wasted WAL and misleading updated_at values.
• Add a WHERE ... IS DISTINCT FROM to the DO UPDATE. Use IS DISTINCT FROM, not !=, so nullable columns compare correctly. Keep derived fields (updated_at, hashes) out of the comparison tuple.
• Know that a skipped write returns zero rows and Ecto raises Ecto.StaleEntryError. Rescue it and treat it as "unchanged" — that rescue is the load-bearing part of the pattern, not an afterthought.
• Distinguish insert from update with a pre-upsert existence probe against the unique index, not by comparing second-precision timestamps.
• The write guard is one layer of a defense in depth: GUID identity, content hash, and upsert-skip each catch a different duplicate, and reporting the precise outcome is what lets you fire notifications only for genuinely new content.

The StaleEntryError-as-success trick is not in the official Ecto docs, which is a shame, because "skip the write when nothing changed" is exactly what you want for any high-frequency upsert — and once you know the exception is Postgres telling you it did the right thing, the pattern is only a rescue away.