DEV Community: Aleksander Frolov

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.