How One Simple Database Trick Sped Up Email Processing 18x Faster

The Email Sync Problem

Imagine you're building an app that syncs email addresses from a file to a database. Your task: mark all emails in the file as active, deactivate any emails that were in the database but aren't in the file anymore, and keep a record of every change for auditing. Sounds straightforward—but when you're dealing with 100,000 addresses, the straightforward approach becomes a bottleneck.

A developer named Yaroslav Podorvanov recently shared how this exact problem took 90 seconds to solve—until he rewrote it using a PostgreSQL technique that cut that down to just 5 seconds. On 8 July 2026, the approach is worth understanding because it reveals something important about how databases work: the real cost isn't usually the heavy lifting—it's the conversation between your code and the database.

How the Slow Version Worked

The original solution broke the job into separate steps:

1. Insert or update the new email addresses from the file
2. Deactivate any email addresses that weren't in the file
3. Record each change in a history table for auditing

Each step was its own database query. That means the code had to:

• Send the email list to the database
• Wait for a response
• Get the IDs of what changed
• Send those IDs back to the database
• Wait again
• Repeat for deactivation
• Repeat again for history records

With 100,000 addresses, all that waiting added up: 90 seconds total.

The CTE Solution

A Common Table Expression (CTE) is PostgreSQL's way of letting you break a complex query into named, reusable chunks—all within a single database operation. Instead of making five separate trips to the database, Podorvanov moved everything into one query:

WITH
  data (email) AS (
    SELECT UNNEST(@emails::VARCHAR[])
  ),
  deactivated (id) AS (
    UPDATE rtt_emails
    SET active = FALSE, updated_at = @now::TIMESTAMP
    WHERE email NOT IN (SELECT email FROM data)
    AND active = TRUE
    RETURNING id
  ),
  activated (id) AS (
    INSERT INTO rtt_emails (email, active, created_at, updated_at)
    SELECT email, TRUE, @now::TIMESTAMP, @now::TIMESTAMP
    FROM data
    ON CONFLICT (email) DO UPDATE
    SET active = EXCLUDED.active, updated_at = EXCLUDED.updated_at
    WHERE rtt_emails.active IS DISTINCT FROM EXCLUDED.active
    RETURNING id
  ),
  deactivated_history AS (
    INSERT INTO rtt_email_history (email_id, active, file_id, created_at)
    SELECT id, FALSE, @file_id::BIGINT, @now::TIMESTAMP
    FROM deactivated
  ),
  activated_history AS (
    INSERT INTO rtt_email_history (email_id, active, file_id, created_at)
    SELECT id, TRUE, @file_id::BIGINT, @now::TIMESTAMP
    FROM activated
  )
SELECT
  (SELECT COUNT(*) FROM activated) AS activated_count,
  (SELECT COUNT(*) FROM deactivated) AS deactivated_count;

What looks complex is actually doing what the separate queries did—but all in one conversation with the database. The CTE chains pieces together: the incoming data feeds into the activation step, which feeds into the history step, and so on.

The Speedup

The results speak for themselves:

• 10,000 addresses: ~5 seconds (slow version) → ~3 seconds (CTE)
• 100,000 addresses: ~90 seconds (slow version) → ~5 seconds (CTE)
• 1 million addresses: ~30 seconds (CTE)

That's an 18x speedup for the 100k case, achieved purely by restructuring how the data flows—not by making the database work harder, but by making it work smarter.

Why This Matters

The lesson here is counterintuitive: in databases, the round-trip cost (the time spent sending data back and forth) often dwarfs the actual computation. By consolidating five trips into one, Podorvanov didn't just save time—he also reduced server load and made the operation more atomic (either everything succeeds or nothing does, with no partial states in between).

This isn't magic; it's a principle that applies anywhere: minimize unnecessary back-and-forth, and systems get faster. The technique works for email syncs, batch imports, or any operation that chains multiple reads and writes together.

Conclusion

When a database operation feels slow, the culprit is often not the query itself but how many separate queries you're running. CTEs let you combine multiple operations into one atomic step, slashing round-trip overhead and dramatically improving throughput. For workloads like email syncing or batch uploads, this kind of optimization can be the difference between a 90-second operation and a 5-second one.

Merits

• Reduces database round-trips dramatically, cutting latency
• Maintains data consistency—all changes happen atomically in one transaction
• Simplifies application code (fewer separate function calls)
• Scales linearly; the same query works for 10k or 1M addresses
• Leverages database strengths rather than fighting them

Demerits

• CTE syntax is more complex and can be harder to debug
• Requires familiarity with advanced SQL—not ideal for teams new to databases
• Database logs become less granular (harder to see exactly which step was slow)
• Not all databases support CTEs equally well
• May be overkill for small datasets where the overhead doesn't matter

Caution

This article is educational and draws from a real-world example. When implementing similar optimizations, replace any placeholder values (like @file_id or @now) with actual variables from your application, test thoroughly with your own data volume, and verify claims against the original source before relying on them in production. Database performance is highly context-dependent; what works here may need tuning for your specific schema, indexes, and hardware.

Frequently asked questions

• What is a CTE in PostgreSQL and how does it improve query performance?
• Can CTEs be used for operations other than email syncing?
• How do you debug a CTE query when something goes wrong?
• What's the difference between a CTE and a stored procedure?
• Do all SQL databases support CTEs the same way?
• When is a CTE the right tool versus making separate queries?
• How do CTEs affect index usage and query planning?
• What happens if a CTE operation fails partway through?

Tags

#postgres #database #performance #cte #sql #optimization #batch-processing #postgresql