How 150 milliseconds of network latency turned a product import into an hours-long job—and why moving the same code closer to PostgreSQL made it up to 21 times faster.
Our product catalog import was painfully slow.
We had 203 product records grouped into 167 product families. Each product included localized content, relationships, variants, ingredients, and version history. Importing a single SKU regularly took between 30 and 45 seconds. Content-heavy products could take more than a minute.
At that rate, refreshing the complete catalog took hours.
The obvious conclusion was that our import code was inefficient. We suspected slow hooks, excessive validation, poor transaction handling, and unnecessary database queries.
Some of those suspicions were correct. But after fixing them, we encountered a much more interesting performance floor.
Our seed script wasn’t spending its time computing.
It was waiting for PostgreSQL—150 milliseconds at a time.
We started with real application problems
Before blaming the network, we found and fixed several genuine inefficiencies.
An uncached slug-uniqueness hook queried the database repeatedly. Some third-party ecommerce hooks failed to pass the active Payload request into nested operations, causing them to use separate database connections instead of joining the current transaction.
One of those hooks also populated relationships unnecessarily. That triggered concurrent queries against the same transaction connection and produced warnings such as:
text
Calling client.query() when the client is already executing a query
The importer also used Payload’s full Local API lifecycle for every product and variant write. That meant hooks, validation, access control, relationship loading, and document refetching ran repeatedly during a trusted bulk import.
We were also performing existence checks before every insert, even though the documented workflow always started by tearing down the existing catalog.
Those were real problems, and correcting them produced a major improvement.
We changed the bulk path to use Payload’s database adapter directly, while preserving explicit version records for versioned collections. We pre-resolved slugs and taxonomy relationships in memory. We removed redundant database reads and divided the import into short, explicit transaction batches.
After all of that, the simplest possible SKU still took approximately 3.7 seconds.
That was much better than 30–45 seconds, but still suspiciously slow.
Timing the individual operations changed the investigation
Instead of continuing to optimize blindly, we added timing around every meaningful step.
For a simple product with one variant and no medicinal ingredients, the output looked like this:
The five database operations accounted for essentially the entire execution time:
text
613 + 622 + 768 + 909 + 768 = 3680ms
The complete product took 3,682 milliseconds.
Only two milliseconds were unaccounted for.
At this point, the database operations themselves appeared slow. But PostgreSQL wasn’t overloaded, and the rows were not computationally expensive to insert.
The next question was simple: how long does a trivial database round trip take?
The database was 150 milliseconds away
The import was running in Helsinki. The staging PostgreSQL database was hosted in Toronto.
A trivial SELECT 1 over an existing connection consistently took approximately 150 milliseconds.
That number initially did not sound catastrophic. A fraction of a second is easy to dismiss.
But a logical Payload document is not necessarily one SQL statement.
A product with localization, relationships, arrays, variants, and version history can touch several physical tables:
The main document table
Localized field tables
Relationship tables
Array-row tables
Variant tables
Version tables
Localized and relationship tables belonging to those versions
For our simplest product and variant pair, Payload executed approximately 24 sequential SQL statements.
The arithmetic was almost embarrassingly precise:
text
24 statements × 150ms = 3600ms
Our measured execution time was 3,682 milliseconds.
The “slow application” was spending almost all of its time waiting for packets to travel between Finland and Canada.
Latency accumulates when statements are sequential
Network latency behaves differently from CPU work.
If a transformation requires 24 small calculations, a modern processor may complete them almost instantly. But if a transaction requires 24 sequential database acknowledgements, the next statement cannot begin until the previous one returns.
Conceptually, the flow looked like this:
text
Helsinki → Toronto → Helsinki: 150ms
Helsinki → Toronto → Helsinki: 150ms
Helsinki → Toronto → Helsinki: 150ms
...
repeated approximately 24 times
The payload size was not the main problem. Most statements were small.
The problem was repeatedly paying the distance between the application and the database.
This is why a normal API request may feel fine while a write-heavy import performs terribly. An API request might issue a handful of queries. A bulk import multiplies the same latency across products, variants, ingredients, relationships, locales, versions, and transaction batches.
A harmless-looking 150 milliseconds can quietly become minutes or hours.
We moved the job instead of rewriting the database layer
At this stage, we had several possible optimization paths.
We could have replaced more of Payload’s adapter with hand-written SQL. We could have manually inserted rows into every underlying table. We could have attempted parallel transactions to hide the latency.
Each option increased complexity and risk.
Raw multi-table writes would tightly couple the importer to Payload’s current database schema. A future field or relationship change could silently make the importer incomplete. Aggressive concurrency would complicate transaction boundaries, shared relationship resolution, uniqueness handling, and failure recovery.
The simpler solution was to move the execution environment.
Our staging infrastructure already had a self-hosted Bitbucket runner located near the database. We added two manual pipelines:
text
codex-teardown-staging
codex-ingest-staging
The teardown remained a separately confirmed operation. It removed only the site-scoped Codex catalog data—not the complete staging database.
The ingest pipeline then ran the same importer from a fresh repository checkout, with staging secrets injected through the existing secret-management system.
From that runner, connectivity to PostgreSQL was measured in approximately 4–8 milliseconds rather than 150 milliseconds.
No database redesign was required.
The result was immediate
The simplest SKU dropped from 3,682 milliseconds to 216 milliseconds:
Once the network bottleneck was removed, the genuine remaining work became obvious: ingredient reconciliation.
Most importantly, the complete catalog import succeeded:
Metric
Result
Files scanned
203
Products ingested
203
Products skipped
0
Product families
167
Transaction batches
7
Ingredient usages
934
Unique ingredients
308
Import execution time
92.7 seconds
The full catalog went from an hours-long operation to roughly a minute and a half.
All seven transaction batches committed successfully, and the concurrent-query warning did not return.
The important lesson is not “always blame the network”
Our original importer did contain application-level problems.
Removing redundant queries, repairing transaction propagation, bypassing unnecessary lifecycle hooks, and using explicit batch transactions were all worthwhile. Without those changes, moving the runner would have made inefficient code faster without making it good.
The lesson is to distinguish application overhead from latency multiplication.
Once our per-step timings added up exactly to the total runtime, continuing to refactor application code would have delivered diminishing returns.
The measurements told us where to look next.
How to diagnose your own slow seed or import
If a seed script, migration helper, or ETL process seems inexplicably slow, start with a few concrete checks.
Measure the database round-trip time
Do not rely only on ping. Open a persistent database connection and repeatedly execute a trivial query such as:
sql
SELECT1;
This measures the latency your application actually experiences through the database protocol and connection path.
Time every meaningful import stage
Instrument parsing, relationship resolution, validation, document creation, version creation, and transaction commits separately.
If the individual timings explain the total, you no longer have an unknown performance problem. You have an accounting problem that can be reasoned about.
Count statements, not logical operations
A single ORM or CMS operation may produce many SQL statements.
Localization, relationships, arrays, version history, and hooks can expand one document write into dozens of database round trips.
Inspect SQL logs or estimate the number of physical tables involved.
Look for suspicious multiples of your RTT
If one operation takes approximately four times the measured RTT and another takes six times the RTT, that is a strong signal that they execute four and six sequential statements.
Our timings followed this pattern almost perfectly.
Preserve transaction safety
Do not “solve” latency by firing database operations concurrently on the same transaction connection.
Use short, explicit transaction batches. Pass transaction context through nested operations. Commit checkpoints deliberately so a failed import does not lose hours of completed work.
Run write-heavy jobs close to the database
Your web application, development machine, CI runner, and database do not need to live in the same place for every workload.
But jobs that issue hundreds or thousands of sequential writes benefit enormously from low-latency placement.
Moving the job may be safer and more effective than rewriting the persistence layer.
Final takeaway
Performance problems often encourage increasingly clever code.
Sometimes the better solution is architectural and surprisingly mundane: reduce the physical distance between two systems that need to talk repeatedly.
One database round trip of 150 milliseconds is not alarming.
Twenty-four round trips per product are noticeable.
Thousands of round trips across an entire catalog become hours.
Before rewriting your seed script, count the queries, measure the round-trip time, and do the multiplication.
