GTM Galaxy

Article

The Race to Upsert: Solving Lead Deduplication Under Webhook Bursts

Claudine Baumbach / / Pacific

The standard RevOps "find-or-create" pattern is a race condition masquerading as logic.

Imagine a typical GTM event burst: A lead submits a demo request. Simultaneously, your product sends a 'Sign Up' event via Segment, and an enrichment tool like Clearbit fires a firmographic webhook. In a perfect execution, these arrive sequentially. In the real world, they often hit your ingestion edge within the same 50-millisecond window.

If your automation relies on a simple "check if email exists, then write" step, you have a duplicate problem. This isn't a failure of your database speed; it's a fundamental constraint of transaction isolation. To fix it, you have to move deduplication out of the application code and into the infrastructure layer.

The "Read-Then-Write" Fallacy

Most GTM systems—whether custom workers or low-code platforms—operate on a naive logic gate. They query the database for a record, and if the result is null, they issue an insert.

Under the hood, PostgreSQL defaults to the READ COMMITTED isolation level. In this mode, a transaction only sees data that was committed before it began. If Transaction A (the form submission) and Transaction B (the product sign-up) start at the same millisecond, Transaction B cannot see the record Transaction A is currently writing. Both receive a "Not Found" response. Both proceed to insert.

By the time the database realizes there is a conflict, you either have two identical records or a 500 error that breaks your pipeline. Application-level checks cannot bridge this gap because the "truth" of the database is in flux during those critical milliseconds.

Strategy 1: PostgreSQL Atomic Upserts

The most elegant solution within a relational database is the INSERT ... ON CONFLICT statement, commonly known as an upsert. Instead of asking the database if a record exists, you attempt to create it and provide a deterministic backup plan for conflicts.

INSERT INTO leads (email, first_name, last_name, last_seen)
VALUES ('jane@example.com', 'Jane', 'Doe', NOW())
ON CONFLICT (email) 
DO UPDATE SET 
    last_seen = EXCLUDED.last_seen,
    first_name = COALESCE(leads.first_name, EXCLUDED.first_name);

When this command runs, PostgreSQL uses its internal locking mechanism on the unique index (the email column). If two concurrent requests hit the same index, the database engine forces them to queue. The first transaction acquires a lock and performs the insert. The second transaction is blocked until the first commits. Once unblocked, it detects the conflict and automatically pivots to the UPDATE path.

In our load tests simulating 50 concurrent requests for a single lead, this pattern reduced the duplicate rate to zero. The cost is a minor increase in latency for the "losing" transaction, which must wait for the lock to release. However, this wait time is measured in single-digit milliseconds—significantly faster than any application-level retry logic.

Strategy 2: Distributed Locking with Redis

SQL upserts are excellent when your staging database is the source of truth. But RevOps engineering often involves orchestrating external systems like Salesforce or HubSpot where you cannot control the unique index directly.

If your worker calls a slow CRM API (which might take 200ms+), you need a distributed lock to protect that entire 200ms window. Redis is the standard tool for this, using the SET command with NX (Set if Not eXists) and PX (Expiration) flags to create an atomic mutex.

// Attempt to acquire a lead-specific lock for 10 seconds
const lockKey = `lock:lead:${email}`;
const acquired = await redis.set(lockKey, 'locked', 'NX', 'PX', 10000);

if (acquired) {
    try {
        // Perform the CRM lookup and creation
    } finally {
        // Always release the lock when the work is done
        await redis.del(lockKey);
    }
} else {
    // Implement an exponential backoff or skip redundant work
}

This ensures that only one worker can process a specific lead at any given time. In our benchmarks, Redis adds approximately 1.5ms of overhead for the initial lock acquisition. The real complexity lies in the "lock-wait-retry" loop. If five signals hit for the same lead, four of them will be blocked and must wait, increasing the total processing time for the batch.

Architecture Trade-offs: Which to Choose?

Feature PostgreSQL Upsert Redis Distributed Lock
Best For Internal database ingestion Orchestrating external CRM APIs
Complexity Low (Single SQL statement) Moderate (Requires Redis + Retry logic)
Throughput Extremely High High (Limited by Redis connection pool)
Atomicity Database Engine Level Application Orchestration Level
Constraint Requires a Unique Index Requires TTL management to avoid deadlocks

Addressing the Counterarguments

"Our volume is too low for this to matter."

Low-volume systems are indeed less likely to see sub-second collisions. However, as you add automated enrichment or multi-touch tracking, you aren't just increasing volume; you're increasing burstiness. A single lead event now triggers a cascade of five or six webhooks. Concurrency is no longer a scaling problem; it's an architectural one.

"Shouldn't we just use an asynchronous queue?"

Queues like SQS or RabbitMQ are vital for reliability, but they don't solve race conditions by themselves. If you have five parallel workers pulling from the same queue, those workers can still grab three different messages for "John Doe" and attempt to process them simultaneously. A queue serializes the arrival of data, but it doesn't serialize the execution unless you limit yourself to a single, slow worker.

"Managing Redis is too much overhead."

For smaller RevOps teams, adding Redis to the stack just for deduplication might feel like over-engineering. In those cases, lean on the database. Even if you ultimately push to a CRM, ingesting first into a PostgreSQL "buffer" table using ON CONFLICT allows you to deduplicate at the edge for free, before initiating the slower API calls.

The Operational Verdict

If you are building GTM systems that you expect to last, stop trusting application-level "if not exists" checks.

  1. For local data staging, use PostgreSQL INSERT ... ON CONFLICT. It is deterministic, handled at the engine level, and requires zero extra infrastructure.
  2. For direct CRM orchestration, implement a Redis-based mutex. It prevents expensive, slow API calls from colliding and keeps your CRM data clean.

Data integrity isn't about fixing duplicates after they happen; it's about making it architecturally impossible for them to be created in the first place.

— C.B.