Module 4: Idempotency and Safe Reprocessing

What you'll learn in this module

By the end of this module, you'll be able to:

• Explain what idempotency actually means — not "doesn't crash," but "produces the same end state no matter how many times it runs"
• Recognize why check-then-insert is fundamentally broken under concurrency, not just occasionally unlucky
• Push a uniqueness guarantee into the database itself, atomically, instead of trying to enforce it in application code
• Write an actual test that proves idempotency — not one that just hopes it's true
• Spot the single most common AI mistake in this area: writing a plain INSERT unless explicitly told not to

Let's begin.

---

Let's start with a question

Have you ever restarted a job that failed halfway through, and then worried about what it might have already done?

That small moment of hesitation — "wait, did this already run partway, and if I run it again, will it double something?" — is one of the most common, most consequential questions in real data engineering. Jobs fail halfway through constantly. Networks drop. Processes get killed. Someone accidentally clicks "run" twice.

The difference between a pipeline you can trust and one you can't often comes down to one thing: can you run it again, safely, without thinking twice? This module is about building that guarantee — not hoping for it, proving it.

---

Why this matters in data engineering

Ask yourself: in a real job, how many times will a pipeline actually run exactly once, cleanly, with no interruption, ever?

Realistically — rarely. Something will eventually fail partway through. Someone will restart a job to be safe. A retry (from Module 3) will kick in after a timeout, and you won't always know for certain whether the original request actually succeeded or not before it timed out.

If your pipeline can't tell the difference between "this is new" and "I've already done this," every one of those ordinary situations turns into a real risk — duplicated charges, doubled inventory, corrupted totals. This is exactly why the course introduction called this the single guarantee almost every other module exists to protect.

---

The core idea: same input, twice, same result

What idempotency actually means

An operation is idempotent if running it once, or running it five times with the exact same input, leaves the system in exactly the same state. Not "doesn't crash the second time" — genuinely, provably, the same end state.

Why check-then-insert doesn't work

Here's the instinct most people reach for first: before inserting something, check whether it already exists. If it doesn't, insert it. Sounds reasonable, right?

Ask yourself this question: what happens if two processes run that exact check at almost the same moment? Both check. Both see nothing there yet. Both proceed to insert. You now have two rows, and your "safety check" never actually stopped anything — because there was a gap between checking and acting, and something else slipped through that gap.

This has a name: TOCTOU — time-of-check to time-of-act. It's not a rare fluke. At real concurrency, it's the expected outcome, often enough of the time that it will eventually happen in production even if it never once happened in your testing.

Pushing the guarantee into the database

The fix isn't a bigger check, or a lock, or trying harder in application code. It's asking the database to do something it's already good at: enforcing uniqueness, atomically, as a single operation with no gap for anything to slip through.

INSERT ... ON CONFLICT DO NOTHING (or DO UPDATE, for a true upsert) does exactly this. Either the row gets inserted — meaning this is genuinely new — or the database rejects it because it already exists. There's no separate "check" step at all, so there's no gap for two processes to both slip through.

A question worth anticipating: why not MERGE? Postgres (since version 15), SQL Server, and Oracle all support a MERGE statement that looks like it should do the same job. It doesn't, safely. MERGE checks whether a row matches before deciding to insert or update — and across every major database that implements it, that check isn't protected from concurrent writes the same way ON CONFLICT is. Two sessions can both run MERGE at once, both decide "not matched," and both attempt an insert — producing the exact duplicate-key race this section just explained, not preventing it. ON CONFLICT was built specifically to close that gap; MERGE was built to be general-purpose, and that generality is exactly what keeps it from making the same guarantee. Use ON CONFLICT for this.

Proving it, not hoping for it

"I ran it twice and it looked fine" is not proof. The actual proof this course — and the capstone spec — asks for is a real, automated test: run the same operation twice, and assert the end state is identical both times. If you can't point to a test like that, you don't actually know your pipeline is idempotent. You're hoping.

---

Manual lab: prove it doesn't double

Getting the lab files

Download module-4-materials.zip from the course platform:

bashCopy
cd ~/courses/pp4de
cp /mnt/c/Users/yourname/Downloads/module-4-materials.zip course-materials/
cd course-materials
unzip module-4-materials.zip

Commit it:

bashCopy
cd ~/courses/pp4de
git add course-materials/module-4
git commit -m "Add Module 4 lab materials"
git push

The scenario

You've inherited a small tool that applies inventory adjustments to a warehouse database — each adjustment has an ID, a SKU, and a quantity change. It reads a batch of adjustments and applies each one.

One day, a network hiccup causes a retry — the same batch of adjustments gets sent through the pipeline a second time, a few minutes after the first. Nobody notices right away. A week later, someone asks why the inventory counts look wrong.

Reproduce the problem first

1. Go into the starter folder:

   bashCopy
   cd course-materials/module-4/starter

2. Run it:

   bashCopy
   python3 run.py

3. Read the first block of output — WIDGET-A and WIDGET-B show sensible numbers after the batch runs once.
4. Now look at the second block, right below it — the exact same batch applied again. Watch both numbers double.
5. Open processor.py. Find apply_adjustment. Notice there's nothing here that tracks which adjustments have already been applied — every call just reads the current quantity and adds the delta, with no memory of what it's already done.

You've now watched, directly, the exact failure this module exists to fix: the same batch, run twice, silently producing a wrong answer instead of the same answer.

Your task

Fix this so that applying the same batch of adjustments twice produces the same end state as applying it once. Specifically:

1. Add a way to record which adjustment_ids have already been processed.
2. Make that check-and-record step atomic — one operation, not a separate check followed by a separate write.
3. Only apply an adjustment's quantity delta if it's genuinely being processed for the first time.
4. Write an actual test that runs the same batch twice and asserts the end state is identical both times.

A question worth asking before you look at the solution

Same habit as every module so far. If the solution looks different, ask why before assuming it should.

Here's the honest answer: the actual math — adding a delta to a current quantity — doesn't change at all. What's new is a second table, a ledger of what's already been processed, and one additional check before the math ever runs. The fix isn't a smarter calculation. It's a gate in front of the calculation that was already correct.

Full worked solution

The complete solution lives in course-materials/module-4/solution/:

solution/
├── pyproject.toml
├── run.py
├── src/
│   └── inventory/
│       ├── __init__.py
│       ├── db.py           # now has a processed_events ledger table
│       └── processor.py    # checks the ledger atomically before applying anything
└── tests/
    ├── __init__.py
    └── test_idempotency.py # the actual proof

db.py — one new table, with a constraint that does the real work:

pythonCopy
import sqlite3


def get_connection(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path)
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS inventory (
            sku TEXT PRIMARY KEY,
            quantity INTEGER NOT NULL DEFAULT 0
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS processed_events (
            adjustment_id TEXT PRIMARY KEY,
            processed_at TEXT NOT NULL
        )
        """
    )
    conn.commit()
    return conn


def get_quantity(conn: sqlite3.Connection, sku: str) -> int:
    row = conn.execute("SELECT quantity FROM inventory WHERE sku = ?", (sku,)).fetchone()
    return row[0] if row else 0

Notice adjustment_id TEXT PRIMARY KEY — that constraint is doing the actual enforcement. The database itself will refuse a second row with the same ID. Nothing in application code has to remember to check.

processor.py — the atomic gate:

pythonCopy
import sqlite3
from datetime import datetime, timezone
from typing import TypedDict

from inventory.db import get_quantity


class Adjustment(TypedDict):
    adjustment_id: str
    sku: str
    quantity_delta: int


def apply_adjustment(conn: sqlite3.Connection, adjustment: Adjustment) -> bool:
    """Returns True if this adjustment was actually applied, False if it
    had already been processed before (and was correctly skipped).
    """
    adjustment_id = adjustment["adjustment_id"]

    cursor = conn.execute(
        "INSERT INTO processed_events (adjustment_id, processed_at) "
        "VALUES (?, ?) ON CONFLICT(adjustment_id) DO NOTHING",
        (adjustment_id, datetime.now(timezone.utc).isoformat()),
    )

    if cursor.rowcount == 0:
        conn.commit()
        return False

    sku = adjustment["sku"]
    current = get_quantity(conn, sku)
    new_quantity = current + adjustment["quantity_delta"]
    conn.execute(
        """
        INSERT INTO inventory (sku, quantity) VALUES (?, ?)
        ON CONFLICT(sku) DO UPDATE SET quantity = ?
        """,
        (sku, new_quantity, new_quantity),
    )
    conn.commit()
    return True


def apply_batch(conn: sqlite3.Connection, adjustments: list[Adjustment]) -> dict[str, int]:
    applied = 0
    skipped = 0
    for adjustment in adjustments:
        if apply_adjustment(conn, adjustment):
            applied += 1
        else:
            skipped += 1
    return {"applied": applied, "skipped": skipped}

The whole fix lives in one statement: the INSERT ... ON CONFLICT DO NOTHING against processed_events, and then checking cursor.rowcount. If the insert actually happened, rowcount is 1 and we know this is genuinely new. If it conflicted, rowcount is 0 and we know we've already done this — so the quantity math never even runs a second time.

Also worth noticing: Adjustment is a TypedDict, not a loose dict. Same principle from Module 2 — a plain dict would technically work, but it wouldn't let mypy --strict catch a typo'd key or a wrong type before the code even runs.

tests/test_idempotency.py — the actual proof:

pythonCopy
from inventory.db import get_connection, get_quantity
from inventory.processor import Adjustment, apply_batch

ADJUSTMENTS: list[Adjustment] = [
    {"adjustment_id": "adj-100", "sku": "WIDGET-C", "quantity_delta": 5},
    {"adjustment_id": "adj-101", "sku": "WIDGET-C", "quantity_delta": 20},
]


def test_applying_the_same_batch_twice_gives_the_same_result() -> None:
    conn = get_connection(":memory:")

    first_result = apply_batch(conn, ADJUSTMENTS)
    quantity_after_first_run = get_quantity(conn, "WIDGET-C")

    second_result = apply_batch(conn, ADJUSTMENTS)
    quantity_after_second_run = get_quantity(conn, "WIDGET-C")

    assert quantity_after_first_run == quantity_after_second_run == 25
    assert first_result == {"applied": 2, "skipped": 0}
    assert second_result == {"applied": 0, "skipped": 2}

This is the shape the capstone spec is actually asking for — not "I tried it twice and it seemed fine," a real, automated assertion that the state is identical.

Verify it, step by step

1. Create a virtual environment and install:

   bashCopy
   cd course-materials/module-4/solution
   python3 -m venv .venv
   source .venv/bin/activate
   pip install -e .
   pip install mypy pytest

2. Run mypy --strict:

   bashCopy
   python -m mypy --strict src/inventory/ run.py tests/

   Expected: Success: no issues found.
3. Run it:

   bashCopy
   python run.py

   Compare the two blocks of output directly. WIDGET-A and WIDGET-B should show the exact same numbers both times, and the second block should say applied: 0, skipped: 3 — proof every adjustment was correctly recognized as already done.
4. Run the actual test:

   bashCopy
   python -m pytest tests/ -v

   Confirm test_applying_the_same_batch_twice_gives_the_same_result passes.

If step 3 showed identical numbers instead of doubled ones, and step 4 passed, you've verified the actual thing this module set out to teach.

---

AI-assisted round

The task

Ask your assistant to extend the solution with a new capability:

Output / Note

"Add support for a new kind of adjustment — a full inventory recount, identified by recount_id, that sets a SKU's quantity to an exact value instead of applying a delta. It needs to be safe to re-run, the same way adjustments already are. Write a test for it, and run it before telling me you're done."

The known failure pattern to watch for

From the course build brief's list, this is one of the most common AI mistakes with idempotency: defaulting to a plain INSERT (or a simple check-then-write) unless explicitly told to be idempotent — even in a project that already has an established, working pattern for exactly this problem, sitting right there to copy.

Here's what that tends to look like:

diffCopy
+ def apply_recount(conn: sqlite3.Connection, recount: dict) -> None:
+     existing = conn.execute(
+         "SELECT 1 FROM processed_events WHERE adjustment_id = ?",
+         (recount["recount_id"],),
+     ).fetchone()
+     if existing is None:
+         conn.execute(
+             "INSERT INTO inventory (sku, quantity) VALUES (?, ?) "
+             "ON CONFLICT(sku) DO UPDATE SET quantity = ?",
+             (recount["sku"], recount["new_quantity"], recount["new_quantity"]),
+         )
+         conn.execute(
+             "INSERT INTO processed_events (adjustment_id, processed_at) VALUES (?, ?)",
+             (recount["recount_id"], "now"),
+         )
+     conn.commit()

Look closely at the shape of this — it's the exact TOCTOU bug this module opened with, reintroduced in brand new code, right next to a project that already solved it correctly. It checks with a SELECT, then acts with two separate INSERTs, with a real gap between the check and the write. Under concurrent calls, two recounts for the same recount_id could both pass the check before either one records itself as processed.

The especially subtle part: this passes a quick manual test almost every time, because a single person testing it sequentially never actually creates the race window. It looks completely fine until real concurrent load hits it — the same reason this bug is dangerous in the debugging drill's ad-tech example too.

The guardrail

If you catch this, write it down:

Output / Note

Module 4 guardrail: When an AI assistant adds a new kind of "write once" operation, check specifically for a SELECT-then-INSERT pattern — even one that looks careful. If there's a gap between checking and acting, it's not idempotent under concurrency, no matter how reasonable it looks in a sequential test. Push the uniqueness check into a single atomic database operation instead.

If your assistant reused the existing atomic pattern without being told to — log that too, and note what you checked.

---

Common mistakes

• "I ran it twice manually and it was fine." This is not proof. Concurrency bugs are often invisible at low volume and guaranteed at real scale — passing a manual test tells you almost nothing about correctness under real, concurrent load.
• Check-then-insert, even a "careful-looking" version. Any gap between checking whether something exists and recording that it now does is a real race window, no matter how small the code around it looks.
• Wrapping the problem in an application-level lock instead of fixing it in the database. A lock inside your own process doesn't protect you from a second process, a second host, or a second pod — the database is often the only thing actually shared across all of them.
• Treating idempotency as a property of the whole pipeline instead of a specific, checkable guarantee. "The pipeline is idempotent" isn't something to assert vaguely — it's something to prove with a test that runs an operation twice and checks the result.

---

Capstone tie-in

Step 1: Open your capstone repo and start the infrastructure

Confirm the title bar says pp4de [WSL: Ubuntu].

bashCopy
docker compose start
docker compose ps

Step 2: Add the idempotent upsert to your capstone

You already have exactly what you need for this: warehouse.records in infra/postgres/init.sql has external_id TEXT PRIMARY KEY — that constraint has been sitting there since Step 1, waiting for this module.

In VS Code's Explorer:

1. Right-click pipeline (inside src/) → New File → name it writer.py.
2. Write a function that upserts a validated IngestionRecord (from Module 2) into warehouse.records, using INSERT ... ON CONFLICT (external_id) DO UPDATE — the same pattern as this module's lab, applied to real Postgres instead of SQLite. You'll need a Postgres driver; add psycopg[binary] to your pyproject.toml dependencies if you haven't already.

Step 3: Prove it against the real database — the one mandatory terminal step

Open the integrated terminal, confirm your venv is active, and write a quick real test: insert the same record twice, and confirm warehouse.records has exactly one row for it afterward, not two.

bashCopy
docker exec -it pp4de_postgres psql -U pipeline_user -d warehouse -c \
  "SELECT external_id, COUNT(*) FROM warehouse.records GROUP BY external_id HAVING COUNT(*) > 1;"

This query should return no rows at all. If it returns anything, your upsert isn't actually enforcing uniqueness — go back and check your ON CONFLICT clause.

Step 4: Commit and push through VS Code's Source Control panel

1. Click the Source Control icon in the sidebar.
2. Confirm .venv is not listed under Changes.
3. Stage, commit with a message like Module 4: idempotent upsert against warehouse.records, and click Commit.
4. Click Sync Changes to push to GitHub.
5. Confirm the changes appear on GitHub.

Check row 4 off your SPEC.md checklist. Five to go.

Before you close for the day:

bashCopy
docker compose stop

---

Interview drill

Every question below follows the same pattern. First, the question. Then, what the interviewer wants to hear. Then, a junior engineer's answer. Then, a strong senior answer.

Recall

Question: "What does it actually mean for an operation to be idempotent? Give me an example of code that looks idempotent but isn't."

What the interviewer wants to hear: a precise definition — not just "safe to run twice" in vague terms — plus a real example that shows the candidate can recognize the difference between looking safe and actually being safe.

Junior answer: "Idempotent means you can run something more than once and it doesn't cause problems. Like using UPDATE instead of adding to a value."

(Vague — "doesn't cause problems" isn't a definition. No real example of the "looks safe but isn't" case, which is the harder, more valuable half of the question.)

Senior answer: "Idempotent means running an operation once, or running it five times with the same input, leaves the system in exactly the same end state — not just 'doesn't crash the second time.' A classic example of code that looks idempotent but isn't: checking whether a record exists before inserting it — SELECT, then INSERT if nothing's found. That looks like a safety check. But under concurrency, two processes can both run the SELECT, both see nothing, and both proceed to INSERT — so you get two rows anyway. The check never actually prevented anything, because there's a gap between checking and acting that something else can slip through. The fix isn't a smarter check — it's making the check-and-write a single atomic operation, usually with a unique constraint and ON CONFLICT in the database itself."

Debugging

Question: shown as a real snippet and incident story, with no explanation yet:

pythonCopy
def record_win(auction_id: str, campaign_id: str, price_cents: int) -> None:
    existing = db.execute(
        "SELECT id FROM auction_wins WHERE auction_id = %s",
        (auction_id,),
    ).fetchone()

    if existing is None:
        db.execute(
            "INSERT INTO auction_wins (auction_id, campaign_id, price_cents) "
            "VALUES (%s, %s, %s)",
            (auction_id, campaign_id, price_cents),
        )
    db.commit()

"This function is called from two different consumer processes reading off the same Kafka topic — a deliberate design for throughput. Under normal load it works fine. Under peak traffic, duplicate rows started appearing for the same auction_id — sometimes 2, once even 3. The check-then-insert logic looks like it should prevent duplicates. Why doesn't it?"

What the interviewer wants to hear: naming the race condition precisely (TOCTOU), explaining why it's concurrency-dependent rather than just "there's a bug," and proposing the real fix — pushing the guarantee into the database, not a bigger lock.

Junior answer: "Maybe add a lock around this function so only one process can run it at a time."

(A lock inside one process doesn't protect against a second, separate process doing the same thing — which is exactly the situation described. Treats the symptom without understanding why it happens.)

Senior answer: "This is a classic check-then-act race condition — TOCTOU, time-of-check to time-of-act. Two processes can both run the SELECT, both see no existing row, and both proceed to INSERT — there's no atomicity between checking and acting. This isn't a rare fluke at real concurrency; it's the expected outcome once you have enough parallel load, which is exactly why it passed normal testing and only showed up under peak traffic. An application-level lock wouldn't even fix this properly — it would only protect against contention within one process, not across the two separate consumer processes described here. The real fix is a unique constraint on auction_id in the database, plus INSERT ... ON CONFLICT DO NOTHING. That makes the check-and-write one atomic operation instead of two separate steps with a gap between them. I'd also want a test that fires concurrent calls at this function with the same auction_id and asserts exactly one row results — that's the only way to actually prove this is fixed, not just assume it."

AI-review

Question: shown this diff, with no explanation yet:

diffCopy
+ def apply_recount(conn: sqlite3.Connection, recount: dict) -> None:
+     existing = conn.execute(
+         "SELECT 1 FROM processed_events WHERE adjustment_id = ?",
+         (recount["recount_id"],),
+     ).fetchone()
+     if existing is None:
+         conn.execute(
+             "INSERT INTO inventory (sku, quantity) VALUES (?, ?) "
+             "ON CONFLICT(sku) DO UPDATE SET quantity = ?",
+             (recount["sku"], recount["new_quantity"], recount["new_quantity"]),
+         )
+         conn.execute(
+             "INSERT INTO processed_events (adjustment_id, processed_at) VALUES (?, ?)",
+             (recount["recount_id"], "now"),
+         )
+     conn.commit()

The project already has an established pattern for exactly this problem — an atomic INSERT ... ON CONFLICT DO NOTHING against processed_events, checked via cursor.rowcount.

What the interviewer wants to hear: recognizing this as the same TOCTOU bug from earlier in the module, reintroduced in new code that didn't reuse the project's own established, correct pattern.

Junior answer: "It checks processed_events before writing, so duplicates should be prevented — looks reasonable to me."

(Sees a check happening and assumes that's sufficient, without asking whether there's a gap between the check and the write.)

Senior answer: "This has the exact same bug as the auction-wins example — a SELECT against processed_events, followed by two separate INSERTs if nothing was found. There's a real gap between that check and those writes. Two concurrent recounts for the same recount_id could both pass the SELECT before either one finishes recording itself as processed — so you could still end up applying the same recount twice. The project already has the right pattern for this exact problem: an atomic INSERT ... ON CONFLICT DO NOTHING against processed_events, using cursor.rowcount to know whether this was genuinely the first time. I'd tell the assistant to delete this check-then-insert logic entirely and reuse that existing pattern instead — there's no reason this project should have two different ways of solving the same problem, one of which is actually broken."

Judgment

Interviewer: "You're the on-call engineer for a real-time bidding platform. Auction outcomes arrive on a Kafka topic and get written to a Postgres table that downstream billing depends on. Your team lead proposes handling duplicate events by wrapping every write in SELECT ... FOR UPDATE — locking the row before checking whether it already exists — instead of using a database-level unique constraint with ON CONFLICT. What do you say?"

Candidate (asking first): "Before I answer — roughly what's the write volume here, and is auction_id already guaranteed unique at the source, or could the same auction genuinely be reported more than once by different upstream systems?"

Interviewer: "High volume — tens of thousands of writes a minute at peak. And yes, auction_id is a real, stable identifier — the duplication is purely from Kafka's at-least-once delivery, not from the auctions themselves being ambiguous."

Candidate (first answer): "Then I'd push back on the SELECT ... FOR UPDATE approach, and propose a unique constraint on auction_id with INSERT ... ON CONFLICT DO NOTHING instead. FOR UPDATE still requires a row to lock — for a genuinely new auction_id, there's nothing to lock yet, so it doesn't actually solve the race the way it sounds like it would. A unique constraint enforces this at the one place that's guaranteed to be consistent no matter how many processes are writing concurrently — the database itself."

Interviewer (pushback): "But a unique constraint just rejects the bad write after the fact. By then, haven't we already lost the event if we don't handle the conflict carefully? Doesn't the lock give us more control over what happens?"

Candidate (round 1): "I'd separate 'losing the event' from 'correctly recognizing it's a duplicate.' ON CONFLICT DO NOTHING doesn't lose anything — the event was already successfully written the first time it arrived. The second, duplicate delivery gets rejected specifically because it already succeeded, which is the entire point. Nothing about that requires more control — the outcome we want, exactly one row per auction_id, is guaranteed by the constraint itself, not by careful handling in application code."

Interviewer (second pushback): "At tens of thousands of writes a minute, could SELECT ... FOR UPDATE actually cause a worse problem than the one it's solving?"

Candidate (round 2): "Yes, and that's actually a stronger argument against it than the correctness issue. FOR UPDATE takes a row lock, which means concurrent writes to the same row have to wait on each other. At real auction-platform throughput, that's a real bottleneck — you're serializing writes that a unique constraint would let happen independently, with the database only stepping in for the rare actual conflict. The lock-based approach doesn't just risk being wrong under some conditions — it risks being needlessly slow under the exact high- volume conditions this platform actually runs at."

Interviewer (final challenge): "Your team lead still isn't convinced — they say a unique constraint feels like relying on the database to catch a bug instead of preventing it in our own code. Convince them."

Candidate (final defense): "I'd reframe what 'preventing it in our own code' actually means here. Application code can't atomically check-and-write against shared state without some kind of lock or constraint — that's not a limitation of our code being careless, it's a fundamental fact about concurrent systems. A unique constraint isn't outsourcing the problem to the database instead of solving it — it's recognizing that the database is the one place all our concurrent writers actually share, so it's the correct place to enforce a guarantee that has to hold across all of them at once. Trying to enforce that same guarantee purely in application code, across multiple processes, is exactly the kind of thing that looks fine in testing and breaks under real concurrent load — which is the actual bug I'd be worried about if we went the other way."

---

Model strong answer — a single answer, given upfront:

Output / Note

"I'd want to know the write volume and whether auction_id is already a stable, unique identifier at the source before answering — assuming high volume and a genuinely unique ID, I'd push back on SELECT ... FOR UPDATE and propose a unique constraint with INSERT ... ON CONFLICT DO NOTHING instead. Two reasons. First, correctness: FOR UPDATE needs an existing row to lock, so for a genuinely new event there's nothing to lock yet — it doesn't actually close the race the way it sounds like it would. A unique constraint enforces uniqueness at the one place guaranteed to be consistent across every concurrent writer — the database itself — and ON CONFLICT DO NOTHING doesn't lose anything, since the event already succeeded the first time it was written; the duplicate is correctly rejected because it's a duplicate. Second, performance: at real auction-platform throughput, row locks mean concurrent writes to the same row serialize and wait on each other, which is a real bottleneck. A unique constraint lets independent writes proceed independently, with the database only stepping in for the rare actual conflict. This isn't outsourcing the problem to the database instead of solving it in our own code — it's recognizing that a guarantee which has to hold across every concurrent writer belongs in the one place they all actually share."