Postgres vs. a Warehouse for Marketing Reporting: When to Stop Querying Prod
The 60-second version
Your production database can carry marketing reporting further than most people assume — and then it cannot. The four signals that mean it is time, and the cheapest thing to do next.
- What happened, in one line
- What to do about it this week
- What you can safely ignore
The advice you will get online is that marketing reporting belongs in a warehouse. The advice is usually right eventually and almost always premature.
Your production Postgres can carry marketing reporting a great deal further than people assume — through a few million orders, comfortably — and moving early buys you a pipeline to maintain, a second copy of the truth to reconcile, and a per-query bill, in exchange for solving a problem you did not yet have.
What follows is the four signals that mean the answer has actually changed, and what to do at each one. Two of the four have fixes that are not "buy a warehouse."
What each system is actually built for
Two Databases, Two Access Patterns
Data JourneyPostgres (OLTP)
Optimised for reading and writing whole rows, fast, by primary key, thousands of times a second. Reads every column of every row it touches.
Warehouse (OLAP)
Optimised for scanning a few columns across billions of rows. Reads only the columns named, compressed, in parallel.
The mismatch
An analytical query on a row store reads every column of every row to use three of them, and competes with production traffic while doing it.
That last point is the one that matters. When you run a two-year revenue aggregation against production Postgres, the database reads every column of every row in range — including the JSONB blobs and text fields your query never mentions — because it stores rows contiguously. It also does that work using the same buffer cache, the same CPU, and the same connection pool that is serving checkout.
The failure mode is not slowness — it is a slow checkout. An analytical query that takes 40 seconds is annoying. An analytical query that evicts your working set from the buffer cache and makes every customer-facing query slow for the next ten minutes is an outage with a marketing dashboard as its root cause.
The four signals
Signal 1: Analytical queries are affecting production performance
The first and most important signal, and the one that has a non-warehouse answer.
How to check:
Show query
A high pct_from_disk on a long-running query means it is pulling pages from disk and pushing your hot production data out of the cache. That is the mechanism by which a report slows down a checkout.
The fix that is not a warehouse: a read replica. Point every analytical query at a streaming replica and the problem disappears — reporting load is physically separated from production, replication lag is typically under a second, and it costs one more instance. For a large share of teams this is the whole answer, and it is available the same afternoon.
Signal 2: Query times exceed what the report is worth
Not "queries are slow" — queries are always slow eventually. The signal is that the behaviour around the report has changed: someone has started running it overnight, or caching it into a spreadsheet, or simply not running it.
The fix that is not a warehouse: pre-aggregated summary tables, refreshed on a schedule. The same rollup pattern that makes warehouse dashboards cheap makes Postgres dashboards fast:
Show query
A dashboard reading this returns in milliseconds regardless of how much history sits underneath it. Postgres materialised views do not refresh incrementally — a refresh is a full rebuild — so this holds until the rebuild itself becomes expensive, which is signal 3.
Signal 3: The data you need is not in Postgres, and never will be
This is the signal with no Postgres-side fix, and in practice it is the one that actually forces the move.
Your orders are in Postgres. Your Google Ads spend is in Google's API. Meta's is in Meta's. GA4's event data is enormous, nested, and exported to BigQuery whether you like it or not. Your email platform has its own. The reporting you want requires joining across all of these, and there is nowhere in your production database that they belong.
You could ingest ad spend into Postgres — plenty of teams do, and for a modest volume of daily campaign rows it works fine. What does not work is GA4 event data: hundreds of millions of deeply nested rows a year, in a shape Postgres has no efficient representation for.
If GA4 BigQuery export is switched on, you already have a warehouse. The question stops being "should we get one" and becomes "should the rest of the data move next to the data that is already there." That is a much easier question, and the answer is usually yes.
Signal 4: Reporting load is now competing for the same instance you would rather not upsize
A specific, checkable version of signal 1: you are considering a larger Postgres instance, and the reason is reporting rather than production traffic.
At that point compare honestly. A larger instance runs 24/7 whether or not anyone opens a dashboard. Warehouse on-demand pricing charges only for bytes scanned, which for well-partitioned aggregate tables is very little. For bursty reporting workloads — which is what marketing reporting is — the warehouse is often cheaper than the upgrade, and it does not make production a shared resource.
The decision, as a sequence
What to Do, In Order
Process FlowIndex and rewrite first
A missing index on created_at or a query pulling columns it never uses accounts for a surprising share of 'we need a warehouse' conclusions. Check EXPLAIN ANALYZE before concluding anything.
Add a read replica
Separates reporting load from production entirely. One instance, an afternoon of work, and it solves signal 1 and most of signal 4.
Materialise your rollups
Dashboards read summary tables, never raw orders. Solves signal 2 and delays signal 3 considerably.
Move when the data does
When you need ad platforms, GA4 and orders in one query, the warehouse is the only place that can hold them. Move then, not before.
Steps 1–3, on a real reporting query
What the warehouse actually costs you
Worth being concrete about, since the case for moving is usually made without the other side of the ledger:
| Postgres | Warehouse | |
|---|---|---|
| Freshness | Real-time, or replica lag under a second | Whatever your pipeline schedule is — typically hourly or daily |
| Second source of truth | None; one database | Yes, and it must be reconciled against Postgres forever |
| Pipeline | None | Something must extract, load and monitor. It will break. |
| Cost model | Fixed instance cost | Per-byte-scanned, which can spike without warning |
| Joins to production data | Trivial | Requires the production data to have been loaded first |
| Scan of 2 years, 3 columns | Reads every column of every row | Reads three columns |
| Nested/semi-structured data | JSONB, workable but slow at scale | Native, and cheap |
The row that bites hardest is second source of truth. From the day you have a warehouse, "how many orders did we do in July" has two answers, and keeping them equal is permanent work. Every reconciliation post — including the one about Shopify, GA4 and Meta — is downstream of that.
A pragmatic middle: the warehouse holds only what Postgres cannot
The best-working setup for most mid-sized teams is not "everything moves". It is:
- Postgres stays the source of truth for orders, customers and products.
- The warehouse holds ad platform data, GA4 export, and anything else that arrives from an external API in volume.
- A nightly job replicates a narrow slice of Postgres — orders and customers, a handful of columns, not the whole schema — into the warehouse so joins are possible.
- Operational reporting stays in Postgres, on the replica, off materialised views. Cross-source analysis lives in the warehouse.
This keeps the reconciliation surface small: one direction, a few tables, checkable with a nightly row-count comparison. Replicating your entire production schema is what makes the second-source-of-truth problem unmanageable.
Show query
Frequently asked questions
Can I just add indexes and stay on Postgres forever?
For orders-only reporting, plausibly yes well past a million orders — with a read replica and materialised rollups. What ends it is not volume but breadth: the moment you need ad platform data and event data joined to orders, no amount of Postgres tuning helps, because the data is not there.
Is a read replica really enough?
For signal 1, usually yes. It removes analytical load from the primary entirely, which is the difference between a slow report and a slow checkout. It does not make the queries faster — the replica is the same shape of database — so it does not address signal 2 on its own.
What about Postgres columnar extensions?
Extensions offering columnar storage or distributed Postgres genuinely help, and if you are already deep in Postgres they are worth evaluating before adopting a separate system. They address the storage-format mismatch. They do not address signal 3, which is about data that lives outside your database entirely.
Does BigQuery replace Postgres?
No, and attempting it goes badly. BigQuery has no meaningful concept of a fast single-row lookup, no enforced foreign keys, and pricing that punishes frequent small queries. It is an analytical store next to your transactional one, never instead of it.
How do I get Postgres data into BigQuery?
Options range from a managed connector, to change-data-capture streaming, to a nightly scheduled export of a narrow slice. Start with the last one: a cron job exporting the tables you actually need is unglamorous, debuggable, and adequate for daily reporting. Reach for CDC when someone genuinely needs sub-hourly freshness in the warehouse.
The summary
- Postgres carries marketing reporting further than the internet suggests. Index, add a read replica, materialise your rollups — in that order — before concluding you need anything else.
- The signal that genuinely forces a warehouse is breadth, not volume: you need ad spend, event data and orders in one query, and three of those do not live in your database.
- If GA4's BigQuery export is already on, you have a warehouse. The question is only whether the rest of the data should join it.
- Moving costs you a pipeline, a per-byte bill, and a permanent second source of truth. Keep that surface small: replicate a narrow slice, reconcile it nightly.
- The best steady state for most teams is both — operational reporting on the Postgres replica, cross-source analysis in the warehouse.
BigQuery Cost Estimator
Estimate what a query will cost before you run it — from validator bytes, with a table-scan simulator and a paste-your-query cost audit. On-demand BigQuery pricing, made concrete.
Chinmay Raibagkar
About author →Founder of DataLens AI. He helps non-technical teams read their ad and database numbers with confidence — which number to trust, what to do next, and what to ignore.