PostgreSQL Batch UPSERT with CTE: From 90 Seconds to 5
An email sync process with audit history was rewritten from separate queries into a single PostgreSQL CTE, cutting 100k-row processing time from 90 seconds to 5.
A real-world Go and PostgreSQL case study looks at syncing email addresses from a file: marking listed addresses active, deactivating missing ones, and logging every change to an audit history table. The original implementation used UNNEST-based bulk UPSERT plus separate deactivation and history-insert queries, taking about 90 seconds to process 100,000 records — the bottleneck being multiple round-trips between the application and database as affected IDs were shuttled back and forth.
The fix was to consolidate all the logic into a single chained CTE query: gathering input emails, running the deactivation UPDATE, performing the UPSERT INSERT for activation, and then inserting audit history rows for both activated and deactivated records, all within one statement. Only the final activated/deactivated counts are returned to the application.
The rewrite delivered a dramatic speedup: 10k records dropped from ~5s to ~3s, 100k records fell from ~90s to ~5s, and even 1 million records processed in roughly 30 seconds. The case illustrates how minimizing database round-trips — a common concern for Go developers using type-safe query generators like sqlc — can yield outsized performance gains in bulk data synchronization workloads.