DEV Community: Aleksander Frolov

Tests green, queries not: what building an N+1 detector for PHPUnit taught me

Aleksander Frolov — Mon, 05 Oct 2026 14:33:13 +0000

An open-source time tracker built on Symfony has 4343 tests, and every one of them passes. Yet each time entry it saves sends three extra SELECT queries to the database: the rate of the entry itself, the rate of the project, and the rate of the activity. With 50 entries in one flush(), which is exactly how many rows the bulk edit page shows, the rates alone cost 150 queries. The tests cover this code, and none of them fails: not one of them checks for problems with database queries.

To solve this problem, I built query-guard, a PHPUnit extension. Before it ever looked for N+1 in other people's projects, though, it managed to fool me several times, and always the same way: it showed green where it was not working at all.

A ready-made package said green

First I looked for existing solutions. The closest in spirit was a popular package: a PHPUnit trait with query counters, duplicate detection, and plan analysis, advertised as supporting Laravel and Doctrine. I installed it on my own project (Symfony, Doctrine, PostgreSQL 17) and wrote a trial N+1: five lots, each with its own tender, findAll() and a read of the tender in a loop.

read phase, queries: 6
  1) SELECT ... FROM lots t0
  2) SELECT ... FROM tenders t0 WHERE t0.id = ?
  ...
  6) SELECT ... FROM tenders t0 WHERE t0.id = ?
duplicates found (SQL + bindings match): 0
assertQueriesAreEfficient() on a plain N+1: PASSED
assertNoLazyLoading(): PASSED

Six queries instead of two, and both assertions green. The package looks for duplicates by matching SQL and values, and in an N+1 the values differ by definition. The lazy loading check returns green on Doctrine without checking anything. Plan analysis on PostgreSQL switches itself off silently and also reports green, even though it has not parsed a single query.

Then I repeated the check on MySQL, where everything in the package works, and the error flipped sign. The N+1 stayed invisible, because the plan of each individual query is flawless: const, PRIMARY, one row. Instead, a table of five rows got error: Full table scan, and the file:line in the report pointed inside the package itself.

Static analysis such as phpstan-dba does not give the answer either: with an ORM, the code contains almost no "raw" queries for phpstan-dba to analyze, and the queries only appear at runtime.

One process, one trace

The first design came out impressive: three packages, runtime query collection with a dump to a file, a separate PHPStan rule that reads the dump and maps findings to code, plus a hash and a git revision so that the dump would not drift away from the code.

Everything rested on a single reason: collection and analysis lived in different processes. If you do everything in one process, right during the test run, the call stack is alive, file:line is available on the spot, and there is nothing to serialize or reconcile.

That is how the tool became a PHPUnit extension. In the PHPUnit 10+ event system one test gives one trace, and N+1 can only be seen in a trace. There is a limitation as well: the tool sees only what the tests cover.

The first thing to decide was where to open the trace. If it opens at the very start of test preparation, setUp() ends up inside it, and a factory that creates 50 entities in a loop produces 50 identical INSERTs from one place, a perfect false positive. So the trace opens on the Test\Prepared event, after setUp(), and fixture queries are collected separately.

Then came strict mode. I wanted a finding to fail the test. The PHPUnit 10–13 event system lets an extension neither mark a test as failed nor change the exit code. The only thing that worked was register_shutdown_function, which runs after PHPUnit has finished. As a result PHPUnit prints OK while the process exits with 1. CI understands that; a human stares at the output in confusion.

EXPLAIN on an empty database

The hardest thing to accept was that EXPLAIN on a test database is useless. A test database holds three fixture rows. With three rows the optimizer honestly picks a full scan because it is cheaper, and the plan tells you nothing about production.

So the rules had to be split into two tiers. The first tier does not depend on data volume and works right away: N+1, duplicates, queries in a loop, a missing LIMIT, a query budget per test. The second tier reads plans (table-scan, filesort, temporary-table, no-possible-index); it is off by default and turns on together with a pointer to a database that has real volume.

The N+1 rule

The rule itself is simple: one query shape with the values stripped out, one place in the code, different values, at least three repeats, and reads only. Batched lookups with IN (?, ?, ?) do not count, since that is exactly how N+1 gets fixed. For Doctrine, a lazy collection being initialized leaves a PersistentCollection in the stack, so such a finding gets the error level and names the association.

query-guard
  tests traced: 404, queries: 25736 (in setUp: 0)

  findings: 207

  * [error] n-plus-one — App\Tests\Controller\EntryControllerTest::testExport
    App\Entity\Entry::$tags — lazy-loaded association, 10 queries
    src/Entity/Entry.php:418

  * [warning] n-plus-one — App\Tests\Controller\EntryControllerTest::testSaveRates
    50 queries of the same shape from one place, different values: SELECT ...
    src/Repository/EntryRepository.php:810
      from src/Pricing/RateService.php:96 App\Repository\EntryRepository::findRates
      from src/Controller/EntryController.php:212 App\Pricing\RateService::calculate

(Class names and paths replaced.) A legacy project needs a baseline, otherwise the first install produces hundreds of findings and the tool gets removed the same day. On the time tracker, 75 hits across 43 tests collapsed into 24 signatures, and the rerun came out clean.

Seven projects that were not mine

The CMS: I blamed the wrong code

There was one confirmed lazy load: the collection of file versions on a media item. The report showed only the place the query came from, an entity getter called from everywhere, so I filled in the gap myself and wrote in the issue that the N+1 happens when the API reads a list of media.

The maintainer objected, and with good reason: on read, the needed file version is fetched by the same query through a JOIN. I reran the tests, saving the full stack of every query. Every lazy load happened on save, not on read: a PATCH request that adds or removes media on a contact creates an activity log event for each item, and each event loads the file versions with a query of its own.

That mistake is why the summary now has from lines: the chain of callers leading up to the query.

The time tracker: where 150 queries come from

The full suite, 4343 tests and 38,103 queries: 1182 hits in 84 places, 9 of them with confirmed lazy loading. Three made it down to production code, and I filed an issue for each. The largest counters came from a fixture, so I measured the production bulk save path separately:

Entries in one `flush()`	Queries
1	7
5	25
10	45
25	105

Exactly 4N + 5: one INSERT and three rate SELECTs per entry. Schematically:

// onFlush subscriber
foreach ($scheduledEntries as $entry) {
    $this->rateService->calculate($entry); // three separate SELECTs inside
}

The online store on Pest: green and empty

A Laravel project on Pest with 1477 tests. The run was green, but no report file appeared, and query-guard printed not a single line. I put file_put_contents on the first line of the extension's bootstrap(). The file appeared, so the extension was loading, but it quit on the very next line. Pest always adds --no-output to the PHPUnit arguments, because it prints the output itself through Collision, and I had an early return on $configuration->noOutput(). For PHPUnit that flag means "the user asked for silence"; for Pest it means "PHPUnit is not the one printing".

The same run exposed a second silent failure: under pest --parallel every ParaTest worker wrote its report to the same file, and on a sample of 34 tests the report kept 7.

The music server: 1611 warnings for 1612 tests

OK, but there were issues!
Tests: 1612, Assertions: 11785, PHPUnit Warnings: 1611.

Laravel empties the container in tearDown(), but until the next test's setUp() the facade keeps pointing at the previous test's DatabaseManager. The adapter asked is_callable([$manager, 'listen']) and got true, because the manager has __call. The call went into an empty container. Rechecking the CRM on Pest showed the same exceptions there all along: Pest prints a single WARN line with no counter, and among 42 tests I missed it.

Why silence is worse than a crash

A tool that crashes gets fixed the same day. A tool that stays silent gets removed a month later with "it never found anything useful", and nobody ever learns that it simply was not working.

So the package's main rule is this: a green report and "we did not look" must not look the same. The summary tells you when no ORM was found or interception did not start, when not a single query arrived, when a plan rule cannot work on this platform, and when a third of the findings point into vendor/ or at fixtures.

Try it

Requirements: PHP 8.2+, PHPUnit 10.5–13, Doctrine ORM 2–3 with DBAL 3–4 or Laravel 11+. The cost per query inside a test is about 0.006 ms and roughly 0.5 MB of memory per thousand queries.

composer require --dev alex-frolov/query-guard

The phpunit.xml wiring and the Doctrine setup are in the README. Look past the color of the run and read the summary: how many tests were traced, how many queries, and which three places top the list.

The full story, with plan parsing on MySQL and PostgreSQL and the JSON report for CI: frolov.guru/en/writing/query-guard-n-plus-one.

I'm Aleksander Frolov, a senior/staff backend engineer building highload PHP systems. I write about architecture and performance on frolov.guru.

One CAST made a view 13x slower in PostgreSQL. So I benchmarked MySQL 8.4 against PostgreSQL 17

Aleksander Frolov — Mon, 14 Sep 2026 18:59:09 +0000

At one interview I was asked about VIEWs. I answered honestly: in real projects I had barely run into them; for aggregates it is safer to keep a separate table. One of the interviewers said, “You understand nothing about VIEWs,” and everyone laughed.

A lot of time has passed and the number of VIEWs in my code never grew, but the question stayed with me: what if things have changed? New engines have shipped. So I brought up MySQL 8.4.11 and PostgreSQL 17.11, loaded byte-for-byte identical data into both — a million orders, two million line items, 780 thousand payments — and ran the main scenarios one after another.

The test rig is public and reproducible: github.com/alex-frolov/mysql-postgresql-view-test.

Here is what came out of it.

Methodology

I measure server-side time: the root actual time from EXPLAIN ANALYZE in MySQL, Execution Time in PostgreSQL. Two warm-ups, seven measurements, take the median. Comparing absolute milliseconds across machines makes no sense; only ratios between identical queries matter. The query window is the same everywhere — June, merchant with ID 42.

A simple view costs nothing

A wrapper over orders with no aggregation runs at one and a half to two milliseconds in both engines, the spread stays within noise, and the plans match the direct query. MySQL folds the definition into the query (the MERGE algorithm), PostgreSQL expands it through the rewrite rule before the planner even sees it. The optimizer simply does not notice that you queried a view.

A reusable filter and a stable read contract are free. Boring — and boring is the best case here.

One CAST, thirteen times more expensive

An aggregating daily-revenue view:

CREATE VIEW v_merchant_daily AS
SELECT o.merchant_id,
       CAST(o.created_at AS DATE) AS day,
       COUNT(*)                   AS orders_cnt,
       SUM(o.amount_total)        AS revenue
FROM orders o
WHERE o.status IN ('paid', 'shipped', 'completed')
GROUP BY o.merchant_id, CAST(o.created_at AS DATE);

The query on top is exactly what anyone would write — filter by merchant, filter by day, order by day.

PostgreSQL returns it in 24.5 ms. The same result fetched directly from the table takes 1.85 ms. A thirteenfold difference out of nowhere.

My first explanation was the standard one: “the aggregate computes before the filter, the engine groups the whole table.” I opened the plan and found out I was wrong. No full-table aggregation anywhere: both predicates get pushed below the grouping, exactly 469 rows are aggregated — the same as in the direct query.

The difference sits in another line: Rows Removed by Filter: 9531. The direct query uses the (merchant_id, created_at) index on both columns. Through the view, the date condition arrives as an expression — CAST(created_at AS DATE) >= '2026-06-01' — and an expression cannot serve as a range bound on the index. Only the merchant condition survives down to the index: all ten thousand of that merchant's orders get pulled out of the heap from scattered pages, and there the filter throws away 9531 of them. That comes to 8971 buffer accesses against 440.

MySQL spends 4.85 ms against 2.16 ms in the same spot — a little over two times. It evaluates the CAST right inside the index (index condition pushdown), cutting candidate rows off before they ever leave the table.

A subquery with the same text took 26.9 ms, a CTE took 24.6 ms. The problem is not CREATE VIEW; it is the semantics. Any construct with the same grouping behaves the same way.

The takeaway I keep: the view exposes a column called day while the index lives on created_at. It hands you an interface that looks like a table but cannot reach the index — silently. Ever since, whenever I see CREATE VIEW ... GROUP BY in a pull request, I ask which columns it exposes and whether indexes exist underneath them.

The cascade that beat the handwritten query

Three views stacked: a daily aggregate, a sum on top, a join with the reference table at the very top. Every level recomputes on every access. The obvious expectation: the deeper the stack, the worse.

PostgreSQL confirmed it: 832 ms against 243 ms for the single-pass query.

MySQL refuted it: the cascade took 1436 ms while the direct query took 2432 ms. The stack of three views was almost twice as fast as the handwritten single-pass report.

The reason is the shape of the join, not the depth. The direct query joins orders with the reference table before grouping — a nested loop doing 750000 point lookups by primary key. In the cascade the join moves to the top, where after two aggregations only 75 rows remain out of 750 thousand.

PostgreSQL's cascade loses parallelism, and its intermediate 48 thousand groups do not fit into the default 4 MB work_mem: 30 MB spilled to disk per execution. Across nine runs the temp_bytes counter grew by 278 MB — for a report that returns ten rows.

Whether a cascade is expensive is a question of join shape and memory settings, not depth. You cannot predict it; you can only test it, and at production volumes.

ORDER BY inside a view is a time bomb

A codebase classic: CREATE VIEW ... ORDER BY created_at DESC — “so it’s definitely sorted”. Today both engines return sorted data instantly, reading the index backwards. Tomorrow the optimizer recalculates statistics, picks a different access path, and code that silently relied on ordering starts returning a random ten.

The SQL standard does not guarantee row order for a view, and the MySQL documentation says so in plain text. Such a bug does not crash, does not log anything, and surfaces six months later as a user complaint.

Where is the cache?

Reading a materialized view with a unique index takes 0.24 ms against 24.5 ms for the live aggregate — a hundred times faster. Tempting to credit caching. But neither engine has a result cache: MySQL removed the Query Cache in 8.0, PostgreSQL never had one. Thirty repetitions of the same query produce a flat series — every run computes again.

A materialized view is faster not because it caches, but because there is nothing left to compute. The price sits elsewhere: REFRESH on a million orders takes 1146 ms and is always full — no incremental refresh in PostgreSQL.

What reading does to writing

Fifteen-second insert windows, batches of eight hundred rows. With two background readers hammering the aggregating view, write throughput dropped by 7% in MySQL and by 16% in PostgreSQL. Replacing the live view with a summary table maintained incrementally by the writer removed the drop entirely.

A view itself does not get in the way of writes. A constant reader of a heavy aggregate does.

The number that matters: scale

Orders	MySQL, view/direct	PostgreSQL, view/direct
10,000	1.6×	2.7×
1,000,000	2.3×	10.7×
10,000,000	2.5×	50.4×

MySQL holds steady across the whole range — index condition pushdown works at any volume. The PostgreSQL gap grows with the data. And a summary table maintained by the writer does not depend on volume at all: 0.02–0.20 ms from ten thousand to ten million orders — thousands of times faster than a live view at the top end.

Up to tens of thousands of rows, choosing between a live view and a summary table is a matter of taste. Closer to a million it becomes a matter of architecture. Past a million there is no choice left.

Takeaways

A simple view really consumes nothing; both engines look straight through it.
An aggregating view in the hot path is a bad approach: its cost grows faster than the data.
Check which columns a view exposes and whether indexes sit underneath.
ORDER BY inside a view breeds silent bugs instead of convenience.
A materialized view pays off only where reads greatly outnumber writes and data lag upsets nobody.
A summary table with incremental maintenance is orders of magnitude cheaper and volume-independent.

Full walkthrough with query plans, memory measurements and the write-load scenarios: frolov.guru/en/writing/mysql-postgresql-view-test. The rig reproduces from github.com/alex-frolov/mysql-postgresql-view-test.

The list of niches I named at that interview has not changed by a single item. What changed is this: behind every item there is now a query plan instead of a habit.

I'm Aleksander Frolov, a senior/staff backend engineer building highload PHP systems (Symfony, payments, auctions). I write about architecture and performance on frolov.guru.

A modern API reference for Symfony with Scalar

Aleksander Frolov — Sun, 30 Aug 2026 07:21:55 +0000

Symfony had no official integration with Scalar — the open-source API Reference renderer that makes Swagger UI look its age. Laravel got one back in 2024: scalar/laravel has 231,862 installs on Packagist. Symfony got nothing — not a single package on Packagist, and zero in Scalar's official list of 30+ integrations (Express, FastAPI, NestJS, Spring Boot, Laravel — but no Symfony).

I work with Symfony daily, so I closed the gap: the alex-frolov/scalar-symfony bundle.

What the bundle does

It renders Scalar API Reference from any OpenAPI document. One route, zero coupling to how the spec was generated: a static openapi.yaml, swagger-php, NelmioApiDocBundle, or API Platform all work — because the bundle never parses or proxies the document; the page loads it client-side.

Install and configure in two files:

composer require alex-frolov/scalar-symfony

# config/packages/scalar_symfony.yaml
scalar_symfony:
    url: '/openapi.yaml'          # your OpenAPI document (required)
    path: '/scalar'               # route (default: /scalar)
    cdn: 'https://cdn.jsdelivr.net/npm/@scalar/api-reference@1.65.1'

    configuration:
        theme: 'default'
        metaData:
            title: 'API Reference'

    scalar_options:               # any Scalar option, passed through as is
        darkMode: false
        layout: 'modern'

    access_control:
        mode: public              # or 'attribute' + security attribute

That's it — the reference lives at /scalar. The config is serialized XSS-safely (JSON_HEX_TAG/APOS/AMP/QUOT), so even a malicious title can't break out of the <script> context.

Documentation that works as a client

The bundle serves the API reference of the Tender Platform (Symfony 8.1, highload auction API, OpenAPI 3.1 spec). Through Test Request I'm hitting POST /auth/register, filling the JSON body, and pressing Send:

HTTP/1.1 201 Created  (794 ms)
{
  "company_id": "0c7702c6-9667-4ea3-8caa-df4990522ee7",
  "user_id": "86a40d70-5ef9-44fd-882b-8f703e10df7e",
  "verification_status": "pending"
}

Real UUIDs, real latency, real backend: the docs page doubles as the tool you test the API with. That's what Swagger UI never delivered.

Quality bar

The first commit was small; the release survived a review against Symfony's official bundle best practices and external hardening passes.

Functional tests: 16 tests / 46 assertions — routes 200/403/404, config validation errors, XSS-escaping, real SecurityBundle integration;
Static analysis: PHPStan level max, 0 errors;
CI matrix: PHP 8.2/8.3/8.5 × Symfony 6.4/7.2/7.4/8.0, including --prefer-lowest;
Config hardening: attribute mode without Symfony Security fails cache:clear at compile time with a clear message;
Security docs: SRI (SHA-384), CSP/nonce guidance, self-hosting recipe.

The CI war story: a token that didn't fit

Half the GitHub Actions jobs failed with Your github oauth token for github.com contains invalid characters. setup-php writes the Actions GITHUB_TOKEN (prefixed ghs_) into composer's global auth.json, and Composer 2.8 only accepts ghp_/gho_/github_pat_. The fix: delete auth.json on the runner before composer validate --no-check-publish, keep the token for dependency installation.

Making it official

I opened a proposal in the Scalar organization — Discussion #9920: "Proposal: official Symfony integration (scalar/symfony)" — https://github.com/scalar/scalar/discussions/9920

If you're a Symfony developer who wants modern API docs the way Laravel has them, a reaction on the discussion helps signal maintainer attention.

Start tomorrow: composer require alex-frolov/scalar-symfony → point scalar_symfony.url at any OpenAPI document → import routes and open /scalar.

Aleksander Frolov — Senior/Staff PHP engineer, Symfony/Laravel, highload. frolov.guru · GitHub

Idempotency and Retry in a Payment Core: Operations That Can't Be Duplicated and Can't Be Forgotten

Aleksander Frolov — Sun, 16 Aug 2026 09:16:29 +0000

Idempotency and Retry in a Payment Core: Operations That Can't Be Duplicated and Can't Be Forgotten

Build operations that can't be duplicated and can't be forgotten — that's the contract for a payment core.

On the core I built for ROSSTRAFFY (20,000+ successful transactions/day, 99.99% uptime), the most dangerous failure class wasn't crashes. It was retries.

The network doesn't guarantee delivery. A request can be processed, but the response lost — the client retries, and the server can't tell "didn't arrive" from "processed, but response lost". If a retry means "run the request again", you get double charges. If it means "resume the same operation", you get exactly one outcome per key.

The contract: every payment happens exactly once — not 0, not 2 times. Zero means a lost payment and a missed discount window; two means double charges, refunds, disputes.

The approach: the payment core as a state machine

A payment operation is not a "request" that either ran or didn't. It's a path with state: created, processing, completed, rejected, in reconciliation. Every transition must be idempotent — applying the same transition twice must not change the result.

Idempotency lives at three levels, and skipping any one lets duplicates through:

API level — one idempotency key per business operation, generated once, surviving all retries.
Handler level — the operation is a state machine; every transition checks the current state before acting.
Storage level — unique constraints as the last line of defense.

What we built

Operation = state machine: every transition idempotent.
One idempotency key per business operation, generated once, surviving all retries.
Idempotency at three levels: API, handler, storage (unique constraints as the last line of defense).
Payment = two operations: hold (block funds) → capture (charge after the external system confirms), each with its own key and retry rules.
Sync leg: bounded retries, exponential backoff, timeouts, jitter, dead-letter queue.
Async leg (ГИС ГМП): queue + workers, load balancing across 5+ acquirers (Strategy pattern), failover on degradation → 99.8% acceptance reliability.
Recurring payments stay on the same acquirer (confirmed token); switching only for new payments and emergencies.
Observability: counters + latency per stage in Grafana, alerts on anomalies. "More retries, fewer successful operations" = retry is masking the problem, not fixing it.
Reconciliations as the second safety net (ClickHouse: daily financial reports in 3–5 minutes instead of hours).

Validation and results

We validated with A/B tests behind feature flags on real traffic. Effects were fractions of a percent of conversion — only real payment statistics could see them; synthetic tests couldn't.

Metric	Result
Erroneous and fraudulent operations	−25%
Payment conversion	+~10%
Median transaction processing time	−15%

Takeaways

Idempotency isn't a nice-to-have in payments. It's the contract that makes retries safe at scale — and what turns "the user paid twice" into "the user paid once, and we can prove it".

The full article with diagrams and code (EN): https://frolov.guru/en/writing/idempotency-retry/

About the author: Alexander Frolov — Senior/Staff Backend Engineer (PHP, highload) and Team Lead, 18+ years: payment cores, multi-tenant platforms at federal scale. Articles on highload PHP and architecture: frolov.guru. If you're fighting timeouts, duplicates or lost payments — that's an idempotency problem, and it's fixable. DM or aleksander@frolov.guru.