A real-scale load test: seeding, querying, and indexing a Postgres-backed media collection from 5,000 to 1,000,000+ records

Why I Ran This Test

Payload CMS shows up often in headless CMS comparisons, but almost none of the write-ups I found go beyond a few thousand records. That’s not the scale that matters for a media library; the interesting questions only appear once you’re past a million assets: does pagination hold up, do folder queries stay fast, does search degrade, and does the upload pipeline survive real concurrency?
So instead of relying on benchmarks run on toy datasets, I built my own test environment, seeded it with 1M+ media documents on real infrastructure, and measured everything myself—both before and after adding the indexes any production deployment would need.

Setup

Payload CMS, Postgres, storage (Azure Blob in my case; you can also use S3-compatible storage like Amazon S3 or Cloudflare R2), a virtual machine, a dataset (I’m using the same 6 images and looping them with different names and metadata so nothing gets overwritten), and load testing (k6 for API reads and uploads; EXPLAIN ANALYZE via a custom query-analysis script for the database layer).

My plan was to first run the test locally with 5,000 records and execute all load tests to confirm everything worked. After that, I’d move to the server, rerun the tests and analysis, and see whether Payload can actually handle the scale.

Development

So initially, I created the Payload CMS project locally and ran Postgres in Docker.

Then I connected Postgres to the CMS. For object storage, I used Azure. For the seeding script, I used the same six constant images and mapped them with different filenames and random IDs (via the crypto package). To keep Azure Blob Storage costs efficient, I stored those six images once in Azure and reused them as anchor images.

Seeding

The local smoke test seeded 5,000 media documents in 14.17 seconds (352.91 uploads/second on average). The rate climbed steadily as batching warmed up from ~218/s at the first checkpoint to ~479/s by the end. That confirmed the seeding script was solid before I let it loose on the VM.

On the Azure VM, seeding 1,000,000 records quickly stabilized at 642–649 records/second once the run was underway. Batch times stayed consistent at about 1.4–1.5 seconds per 1,000-record batch, and heap usage cycled predictably between roughly 110MB and 415MB—no memory creep and no slowdown as the table grew. That consistency mattered more to me than the raw number: it showed the bottleneck was network/DB round-trips, not performance degrading as the dataset expanded.

After seeding, the admin UI itself didn’t break — clicking around as a single user, browsing, and paginating through the media library all still worked fine. That’s a different claim from the read API holding up under load, which the next test shows it didn’t.

Read API performance

This was the most important number in the whole test and it wasn’t a good one. I ran the same k6 read test hitting the list/filter API with up to 20 virtual users over a 40-second ramp against the full 1M+ dataset on the VM. My threshold going in was p95 under 300ms.
It missed by a wide margin: p95 latency came in at 14.45 seconds. Average response time was 2.99 seconds, with a worst case of 18.24 seconds. Every request eventually returned a 200 with a valid response body — nothing errored — it was just slow enough that “eventually” is doing a lot of work in that sentence. This could be improved by exposing the CDN or storage URL directly to end users instead of serving it from our server, because by default Payload downloads the image and then serves it. If the image is too large, that can take time.

 

         /\      Grafana   /‾‾/  

    /\  /  \     |\  __   /  /   

   /  \/    \    | |/ /  /   ‾‾\ 

  /          \   |   (  |  (‾)  |

 / __________ \  |_|\_\  \_____/ 

 

     execution: local

        script: tests/load/api-reads.js

        output: -

 

     scenarios: (100.00%) 1 scenario, 20 max VUs, 1m10s max duration (incl. graceful stop):

              * default: Up to 20 looping VUs for 40s over 3 stages (gracefulRampDown: 30s, gracefulStop: 30s)

 

time="2026-07-14T14:16:00Z" level=info msg="fetching categories from <http://localhost:3000/api/categories>..." source=console

time="2026-07-14T14:16:00Z" level=info msg="Setup complete. Fetched 100 category IDs." source=console

 

running (0m01.0s), 01/20 VUs, 1 complete and 0 interrupted iterations

default   [   2% ] 01/20 VUs  00.9s/40.0s

 

running (0m41.0s), 05/20 VUs, 149 complete and 0 interrupted iterations

default ↓ [ 100% ] 06/20 VUs  40s

 

running (0m42.0s), 02/20 VUs, 152 complete and 0 interrupted iterations

default ↓ [ 100% ] 06/20 VUs  40s

 

  █ THRESHOLDS 

 

    http_req_duration

    ✗ 'p(95)<300' p(95)=14.45s

 

    http_req_failed

    ✓ 'rate<0.01' rate=0.00%

 

  █ TOTAL RESULTS 

 

    checks_total.......: 308     7.207053/s

    checks_succeeded...: 100.00% 308 out of 308

    checks_failed......: 0.00%   0 out of 308

 

    ✓ status is 200

    ✓ has docs array

 

    HTTP

    http_req_duration..............: avg=2.99s min=45.11ms  med=1.17s max=18.24s p(90)=7.02s p(95)=14.45s

      { expected_response:true }...: avg=2.99s min=45.11ms  med=1.17s max=18.24s p(90)=7.02s p(95)=14.45s

    http_req_failed................: 0.00%  0 out of 155

    http_reqs......................: 155    3.626926/s

 

    EXECUTION

    iteration_duration.............: avg=3.32s min=207.23ms med=1.46s max=18.58s p(90)=7.26s p(95)=14.67s

    iterations.....................: 154    3.603526/s

    vus............................: 2      min=1        max=20

    vus_max........................: 20     min=20       max=20

 

    NETWORK

    data_received..................: 2.7 MB 62 kB/s

    data_sent......................: 16 kB  372 B/s

 

running (0m42.7s), 00/20 VUs, 154 complete and 0 interrupted iterations

default ✓ [ 100% ] 00/20 VUs  40s

time="2026-07-14T14:16:43Z" level=error msg="thresholds on metrics 'http_req_duration' have been crossed"

This is the headline finding of the whole exercise: Payload’s default, unindexed read path does not scale to a million-plus media documents. The requests weren’t failing — they were just taking up to 18 seconds to complete, which in any real product is functionally the same as failing. This is exactly the kind of result that never shows up in a 5,000-record benchmark, and exactly why I insisted on running the full test at real scale instead of trusting smaller numbers.

Upload Performance Under Concurrency

Uploads told a different story. I ran the k6 upload test against the same VM environment with up to 100 concurrent virtual users over a one-minute ramp, threshold set at p95 under 2 seconds.

 

         /\      Grafana   /‾‾/  

    /\  /  \     |\  __   /  /   

   /  \/    \    | |/ /  /   ‾‾\ 

  /          \   |   (  |  (‾)  |

 / __________ \  |_|\_\  \_____/ 

 

     execution: local

        script: tests/load/api-uploads.js

        output: -

 

     scenarios: (100.00%) 1 scenario, 100 max VUs, 1m30s max duration (incl. graceful stop):

              * default: Up to 100 looping VUs for 1m0s over 4 stages (gracefulRampDown: 30s, gracefulStop: 30s)

 

time="2026-07-14T14:17:41Z" level=info msg="Fetching category IDs for upload test..." source=console

time="2026-07-14T14:17:41Z" level=info msg="Setup complete. 310 categories available." source=console

 

running (0m01.0s), 001/100 VUs, 6 complete and 0 interrupted iterations

default   [   2% ] 001/100 VUs  0m00.9s/1m00.0s

 

running (1m00.0s), 002/100 VUs, 4014 complete and 0 interrupted iterations

default   [ 100% ] 002/100 VUs  0m59.9s/1m00.0s

 

  █ THRESHOLDS 

 

    http_req_duration

    ✓ 'p(95)<2000' p(95)=1.09s

 

    http_req_failed

    ✓ 'rate<0.01' rate=0.00%

 

  █ TOTAL RESULTS 

 

    checks_total.......: 8034    133.374819/s

    checks_succeeded...: 100.00% 8034 out of 8034

    checks_failed......: 0.00%   0 out of 8034

 

    ✓ upload status is 201

    ✓ response has id

 

    HTTP

    http_req_duration..............: avg=451.35ms min=21.33ms med=390.41ms max=1.2s  p(90)=978.92ms p(95)=1.09s

      { expected_response:true }...: avg=451.35ms min=21.33ms med=390.41ms max=1.2s  p(90)=978.92ms p(95)=1.09s

    http_req_failed................: 0.00%  0 out of 4018

    http_reqs......................: 4018   66.704011/s

 

    EXECUTION

    iteration_duration.............: avg=552.58ms min=29.93ms med=483.39ms max=1.36s p(90)=1.08s    p(95)=1.18s

    iterations.....................: 4017   66.68741/s

    vus............................: 2      min=1         max=99 

    vus_max........................: 100    min=100       max=100

 

    NETWORK

    data_received..................: 5.9 MB 98 kB/s

    data_sent......................: 3.0 MB 49 kB/s

 

running (1m00.2s), 000/100 VUs, 4017 complete and 0 interrupted iterations

default ✓ [ 100% ] 000/100 VUs  1m0s

All 4,018 uploads in that run succeeded with no dropped requests, no 5xx errors, and latency stayed well within budget even as concurrency ramped toward 100 VUs. Writes to Azure Blob Storage through Payload’s storage adapter held up fine under load; the pain came from reads against a large, unindexed table not the storage layer or the upload pipeline.

The Database Layer

The multi-field filter is exactly what these indexes were built for: 253ms down to under 1ms once the captured_at index lets Postgres avoid a full parallel sequential scan. That query pattern filtering by tag and a date range together is what a real media library search feature would hit constantly.

What surprised me were the queries that got slower after indexing. A few of these results are misleading rather than true regressions: the "optimized" run had a slightly larger table (1,000,006 rows vs. 999,777) and, more importantly, a cold buffer cache. The baseline numbers benefited from data already sitting in shared memory from prior runs, while several of the "after indexing" queries show read= counts in the buffer stats, meaning Postgres had to pull pages from disk instead of cache. The shallow folder query going from 0.17ms to 10.76ms is almost entirely a cache-hit vs. cache-miss story (2 buffer hits vs. 2,598 disk reads), not something the new indexes caused. The full COUNT(*) climbing to 872ms is similar, compounded by JIT compilation overhead and a large number of heap fetches once the query planner switches to a different index. I’m reporting these numbers as measured, rather than smoothing them over, because a fair case study should show where results were noisy or counterintuitive, not just the parts that make the tool look good.

 

Before

After

Read API Performance After Indexing (Re-test)

I re-ran the same k6 script: same 20 VU / 40s ramp, same list/filter endpoint, same 1M+ dataset against the database after the three indexes were in place. p95 dropped to 4.65 seconds from 14.45 seconds unindexed. Average response time fell from 2.99s to 962ms, and the worst case improved from 18.24s to 5.86s. All 478 requests in this run returned 200 with a valid body; just like the baseline, indexing didn’t fix errors (there weren’t any), it simply sped up the slow path.

 

         /\      Grafana   /‾‾/  

    /\  /  \     |\  __   /  /   

   /  \/    \    | |/ /  /   ‾‾\ 

  /          \   |   (  |  (‾)  |

 / __________ \  |_|\_\  \_____/ 

 

     execution: local

        script: tests/load/api-reads.js

        output: -

 

     scenarios: (100.00%) 1 scenario, 20 max VUs, 1m10s max duration (incl. graceful stop):

              * default: Up to 20 looping VUs for 40s over 3 stages (gracefulRampDown: 30s, gracefulStop: 30s)

 

time="2026-08-14T14:12:00Z" level=info msg="fetching categories from <http://localhost:3000/api/categories>..." source=console

time="2026-08-14T14:12:00Z" level=info msg="Setup complete. Fetched 100 category IDs." source=console

 

running (0m01.0s), 01/20 VUs, 3 complete and 0 interrupted iterations

default   [   2% ] 01/20 VUs  00.9s/40.0s

 

running (0m41.0s), 04/20 VUs, 465 complete and 0 interrupted iterations

default ↓ [ 100% ] 04/20 VUs  40s

 

running (0m42.0s), 01/20 VUs, 476 complete and 0 interrupted iterations

default ↓ [ 100% ] 04/20 VUs  40s

 

  █ THRESHOLDS 

 

    http_req_duration

    ✗ 'p(95)<300' p(95)=4.65s

 

    http_req_failed

    ✓ 'rate<0.01' rate=0.00%

 

  █ TOTAL RESULTS 

 

    checks_total.......: 956     23.147724/s

    checks_succeeded...: 100.00% 956 out of 956

    checks_failed......: 0.00%   0 out of 956

 

    ✓ status is 200

    ✓ has docs array

 

    HTTP

    http_req_duration..............: avg=962.41ms min=22.14ms  med=376.50ms max=5.86s  p(90)=2.26s p(95)=4.65s

      { expected_response:true }...: avg=962.41ms min=22.14ms  med=376.50ms max=5.86s  p(90)=2.26s p(95)=4.65s

    http_req_failed................: 0.00%   0 out of 478

    http_reqs......................: 478     11.573862/s

 

    EXECUTION

    iteration_duration.............: avg=1.08s    min=112.40ms med=485.30ms max=6.02s  p(90)=2.35s p(95)=4.72s

    iterations.....................: 478     11.573862/s

    vus............................: 1       min=1        max=20

    vus_max........................: 20      min=20       max=20

 

    NETWORK

    data_received..................: 8.4 MB  203 kB/s

    data_sent......................: 49 kB   1.19 kB/s

 

running (0m42.4s), 00/20 VUs, 478 complete and 0 interrupted iterations

default ✓ [ 100% ] 00/20 VUs  40s

time="2026-08-14T14:12:43Z" level=error msg="thresholds on metrics 'http_req_duration' have been crossed"

That’s a bigger improvement than I’d predicted going in. My reasoning was that indexing wouldn’t move this number much, since the page‑1 query was already sub‑millisecond before I touched anything. I assumed most of the original 14 seconds was coming from somewhere indexing couldn’t reach—most likely the image-proxying step, where Payload downloads and re-serves images instead of pointing clients directly at the blob URL.

The result only partly backs that theory. Either the list/filter endpoint relies on the indexed query paths—particularly the multi-field tags+date filter—more heavily across a real request mix than the isolated EXPLAIN ANALYZE numbers suggested, or the database accounted for a larger share of that original 14 seconds than the query-level timings implied. I don’t have a clean way to separate those two explanations from this data alone.

Either way, it still misses the <300ms threshold by a wide margin: p95 at 4.65s is roughly 15x over budget. Indexing took Payload from unusable to slow—not slow to shippable. Cursor-based pagination and a GIN index for tag/alt-text search are still the two pieces I haven’t built, and I’d want to profile the image-serving path directly before calling this done.

Where It Genuinely Struggled

• Read performance at scale is the real weak point: unindexed, p95 hit 14.45 seconds on a plain list/filter endpoint at 1M+ records. Indexing brought that down to 4.65 seconds, a real improvement, but still roughly 15× over the 300ms target so indexing alone doesn’t make it production‑viable.

• Offset pagination has the classic trade-off: page 5000 costs meaningfully more than page 1 regardless of indexing, because Postgres still has to scan past earlier rows to satisfy the offset. Cursor-based pagination is the real fix (not something I tested here).

• Tag and alt-text filtering with LIKE/ILIKE never got fast on their own. A GIN full-text index is the right tool for that pattern, not a plain btree, and I didn’t have time in this pass to re-run the LIKE queries against the tsvector column to confirm the expected speedup.

• Adding the three indexes more than tripled the table’s on-disk footprint (410MB → 1.29GB at 1M rows), a real cost to budget for at a higher scale.

Verdict

Against the thresholds I set at the start — API list p95 under 300ms and upload success rate over 99% at 100 concurrent uploads — Payload passed on uploads and failed on reads. Concurrent upload handling was genuinely strong: 100% success at up to 100 concurrent VUs, p95 well under budget, and no sign of strain on the storage adapter talking to Azure Blob Storage.

Read performance is the real caveat. It’s not so much a Payload-specific problem as a "you must index your Postgres table for your actual query patterns" problem—the 258x speedup on the multi-field filter shows the ceiling is high once you do that work, and the retest backs it up: indexing alone took p95 from 14.45s to 4.65s. But that's still about 15x over the 300ms target, so indexing gets you from unusable to slow, not slow to shipped. Anyone evaluating Payload for a media library at this scale should treat indexing as a required first step, not the whole fix — cursor-based pagination, a GIN index for text search, and serving images directly from blob storage instead of proxying them through Payload are all still on the table before this would be production-ready.