Backend Architecture

Reporting Queries on the OLTP Database Are an Architecture Problem

Admin exports and GROUP BY on the primary are not a PHP language problem. They are OLTP sharing a disk with analytics. Fix the path: replica, warehouse, or an isolated worker — and only then ask about Go.

By Pavel Beliaev

·

Published 22 Sept 2026

·

Last updated: 17 Sept 2026

·

9 min read

The incident looks like checkout. p95 is ugly, PHP-FPM workers are busy, the payment provider is “fine.” Then someone notices the admin user who clicked Export, or the cron that rebuilds the daily revenue table, or the Doctrine query that hydrates every Order for a date range so PHP can total them in a foreach. PHP reporting on the production OLTP database is an architecture problem. The language is an innocent bystander that happens to be holding the connections.

I keep saying this because teams reach for a rewrite when pg_stat_statements is already screaming. A reporting query that sequential-scans order_lines during business hours will be slow in Go too. It may be more dangerous in Go if you raise concurrency and run five of them at once. The order of operations is: stop treating analytics as a request on the primary, then choose SQL, a replica, a warehouse, or a worker. Go is a later lever, and only for some shapes of export.

Why reporting on OLTP hurts more than “a slow page”

OLTP (the database that takes orders, updates stock, writes payments) is tuned for small writes and small indexed reads. Reporting is the opposite: large reads, sorts, aggregations, often with a time window that grows every year.

Those two workloads fight in specific ways:

• I/O and buffer cache. A sequential scan of a fat table evicts the hot pages checkout needed. The next purchase pays for cache misses.
• CPU and work_mem. A hash aggregate or sort spills to disk. Checkpoints get angrier. Everything waits.
• Locks. Even a SELECT can conflict with vacuums, or sit behind a lock when someone wrapped a report in a transaction “to be safe.” A long ACCESS SHARE is enough to delay a migration you planned for a quiet window. Some reports still take FOR UPDATE because a developer copied a repository method.
• Connections. PHP-FPM: each worker is a connection (or several, if you opened a reporting entity manager and forgot). A 90-second export holds that connection. Pool exhaustion looks like a site outage.
• Autovacuum. Heavy reads plus heavy writes on the same table is how you get bloat and then more I/O.
• Application memory. Doctrine or Eloquent hydrating 200,000 entities to write a CSV is a memory incident that becomes a database incident when the worker dies and retries.

The business language is “the shop is slow in the afternoon.” The engineering language is “analytics shares a primary with checkout.” Until you separate them, every PHP performance pass will be a truce.

Measure before you draw boxes. pg_stat_statements ordered by total_exec_time and by shared_blks_read. EXPLAIN (ANALYZE, BUFFERS) on the offenders. Application traces that show which admin route or which Messenger handler issued the query. Fixing PHP performance before a rewrite is the same investigation; this article is the branch where the query is allowed to be expensive but not on the primary.

Read replicas: what they fix and what they do not

A streaming replica is the first real architectural move, and it is often enough.

Put reporting connections on the replica: the admin export, the cron that computes totals, the expensive GROUP BY behind a dashboard. Keep checkout, stock, and payment writes on the primary. In PHP that is usually a second Doctrine connection or a read-host in the Laravel database config, plus discipline: no “just this once” report on the default connection.

What a replica actually buys:

• CPU, I/O and cache isolation for large reads (assuming the replica is a real machine, not a tiny afterthought).
• Fewer long-lived connections on the primary.
• A place you can set a more aggressive statement_timeout without killing checkout.

What a replica does not buy:

• A better query plan. The schema is the same. A missing index is still a missing index. If the report is a sequential scan, you have moved the scan. You have not removed it. Add the index, or stop asking the OLTP schema to be a warehouse.
• A consistent financial snapshot if you need one. Replica lag is real. “Revenue as of now” on a replica can disagree with the primary by seconds or, after a blip, minutes. For an afternoon ops dashboard that is usually fine. For invoicing and tax close it may not be. Do not use a lagged replica as the system of record.
• Writes. If the PHP report also “fixes data” or writes a cache table, it is not a read. It belongs in a job with an explicit writer, not in a replica session.
• Hydration cost. Eloquent will still build objects. The replica does not make PHP use less RAM.

Operational rules that keep replicas from becoming a second outage: do not run migrations that are unsafe for replicas without a plan; watch replication lag and pause reports when lag exceeds a threshold; do not load-balance checkout reads onto a replica that is also running the nightly scan unless you like mystery latency.

MySQL and PostgreSQL both have replicas. PostgreSQL’s story (streaming, hot_standby, max_standby_streaming_delay) is the one I meet most often on European product teams. The product name is not the point. The point is a second endpoint and a hard rule about which PHP code may use it.

Warehouses, dumps, and materialized views

When the report is large, historical, or join-heavy across years of orders, a replica of OLTP is still the wrong shape. You are asking a normalised checkout schema to answer analytics questions.

Options, from cheaper to heavier:

Materialized views on the replica. Pre-aggregate nightly or hourly. The admin page reads the view. Refresh off-peak. This fails when the business wants arbitrary slices (“every campaign, every country, last 37 days”) that you cannot precompute.

A nightly logical copy. Restore a dump to a reporting instance, or use logical replication into a database that may have extra indexes you would never put on the primary. Extra indexes on a reporting copy are cheap. Extra indexes on the OLTP primary are not.

A warehouse. Snowflake, BigQuery, Redshift, ClickHouse, or even PostgreSQL used as a warehouse with a star schema. CDC (Debezium, native logical decoding, a paid pipeline) beats “mysqldump at 3am” once the volume makes dumps overlap with the business day. The PHP application should not query the warehouse on the request path of checkout. It can query it from an internal reporting app, or not at all if analysts live in SQL.

Object storage for exports. The user does not need a 500 MB CSV in a PHP response. They need a job, a signed URL, and an email. That single product change removes more production pain than a language migration.

Do not skip to a warehouse because it is fashionable. A replica plus a materialized view plus statement_timeout plus “export is async” fixes a large fraction of PHP reporting incidents. A warehouse is justified when many people need historical slices and you are tired of them running SELECT * in production.

Extract a reporting worker or a reporting service

The request path is the wrong place for a report even after you have a replica. A Symfony controller that streams a CSV still holds an FPM worker. A Laravel route that chunk()s still lives and dies with the HTTP timeout of the load balancer.

Move it:

1. User clicks Export. PHP writes a report_jobs row (small write on primary) and returns “you will get a link.”
2. A worker picks the job. The worker’s database DSN points at the replica or the warehouse.
3. The worker writes a file (disk, S3, whatever you already operate) and updates the job row.
4. The user downloads from object storage, not from PHP-FPM.

That worker can stay PHP. Laravel queues and Symfony Messenger are fine if you set memory limits, timeouts, and a concurrency of one or two for the heavy job so you do not stampede the replica. Isolation is the architecture. Language is optional.

When does a separate service help? When the reporting workload has a different deploy cadence, a different scaling axis, or a different runtime. A fleet of HTTP PHP-FPM pods should not autoscale on “someone exported accounting.” A worker deployment can. That is the same reasoning as splitting a PHP monolith without pretending that “ReportingService” must be a network hop on day one. A second process, same monolith repo, different command, is allowed.

If you later extract the worker to Go, you are extracting a consumer with a message contract, not rewriting the shop. That matches the safer sequence in the PHP to Go migration guide.

When Go helps, and when SQL helps

SQL first. Always.

If EXPLAIN shows a sequential scan, fix the index, the predicate, the date range, or the schema (partitioning by created_at is a database project, not a PHP project). If the report pulls millions of rows into PHP to sum them, write SUM and GROUP BY in the database. If you need last-30-days per tenant, a composite index (tenant_id, created_at) plus a query that cannot forget tenant_id will outperform a new language.

Go helps when the remaining problem is the process model, not the plan:

• Streaming a large export without holding a huge in-memory graph. Go can stream rows to a CSV encoder with a modest heap. PHP can too (PDO::FETCH_ASSOC in a loop, no ORM), and you should try that before you rewrite. If the codebase cannot be stopped from hydrating entities, a small Go exporter is a fence.
• Concurrency against a replica or warehouse for fan-out reports (one query per shop, many shops) with a bounded pool. PHP process-per-job will also work; it will cost more memory.
• CPU-bound transforms of data already extracted: fat XML, PDF, column encryption, compression. Do not confuse that with “Go will make GROUP BY faster.” PostgreSQL will make GROUP BY faster.
• Long-lived workers that should not recycle every N jobs because of leaked ORM state. That is a real PHP pain. It is not a reason to put Go on the primary.

Go does not help when:

• The query is wrong.
• The replica does not exist and you plan to run the exporter at higher parallelism on the primary.
• You need a perfectly current number for a write-path decision (pricing, stock, fraud). That is OLTP. Do not send it to a warehouse.
• Nobody will own SQL. A Go service that SELECT *s into structs is the same incident with a nicer deploy.

A useful test: if you paste the query into psql against a replica and it is already fast, your problem is PHP hydration, timeouts, or running it on the primary. If it is slow in psql, your problem is SQL or data distribution. Language change will not save you from the second case.

Practical rules I would write into an SOW

• Reporting connections never use the primary, except the tiny row that enqueues the job.
• Every reporting query has a statement_timeout.
• Exports are asynchronous. No “stream 2 GB through nginx” as a feature.
• ORM is banned on the export path. Arrays or a dedicated read model.
• Replica lag is visible. Financial close has a defined source, not “whatever the dashboard said.”
• New admin pages that need aggregates go through a precomputed table or a warehouse, not a live GROUP BY on order_lines.

If you want that map drawn on your actual queries — what stays SQL, what moves to a worker, what might eventually be Go — that is the kind of workload classification I do in a PHP to Go migration readiness pass. A legitimate outcome is: do not extract a reporting microservice; add a replica, kill the ORM on export, and leave checkout in PHP.

I would rather be hired to take analytics off the primary than to rewrite the shop because an export ran at noon. If your production database is dying under PHP reporting, start there. The checkout team will feel it before anyone feels a new language.