Do You Need a Data Warehouse? Seven Signs You've Really Outgrown Postgres

If your data lives in PostgreSQL and the problem is slow reports, you probably don't need a data warehouse yet. Most slow reports are running in the wrong place, the wrong way, and both can be fixed inside Postgres.

The warehouse advice tends to arrive when the monthly sales dashboard takes a minute to load and someone's query has just frozen the app. Sometimes it's right. Often it's a year or two early, and it brings a second database, a pipeline to feed it, another bill and someone to look after all three.

So do you need a data warehouse yet? Below is the order we'd work through with you: what to try in Postgres first, then seven signs it's really time to move. We'd start any data warehousing project with this check, so you only build a warehouse once you need one.

Why reports slow down in the first place

Your application database is built for many small, quick operations: save an order, update a customer, fetch one invoice. Reports do the opposite. They read and summarise huge numbers of rows at once.

Run both on the same database at the same time and they compete. The report crawls, and while it runs, the app slows down for everyone.

Step 1: stop running reports on the main database

Add a read replica: a copy of the database that stays in sync and takes the reporting queries, leaving the primary to your app.

The PostgreSQL docs spell out one catch. On a replica (a "hot standby"), long queries can be cancelled when the primary cleans up old row versions they still need. By default the replica waits 30 seconds before cancelling them (max_standby_streaming_delay).

On a reporting replica you can raise that limit, which lets the replica fall further behind the primary while a long query runs, or turn on hot_standby_feedback, which stops most of these cancellations but makes the primary keep old row versions longer. For most reports, a few minutes of lag is fine.

Give reporting its own database role, too. In a multi-tenant app, a report that runs as a superuser skips row-level security entirely, a trap covered in our guide to multi-tenant SaaS architecture on PostgreSQL.

Step 2: summarise once, read many times

If ten people open the same dashboard, there's no reason to add up a year of orders ten times.

• Summary tables hold the pre-calculated numbers ("sales by day by product"), updated by a scheduled job.
• Materialized views do the same with less code. REFRESH MATERIALIZED VIEW CONCURRENTLY updates them without blocking readers, as long as the view already holds data and has a unique index on plain columns (no expressions or WHERE clause).

Dashboards then read a few thousand summary rows instead of millions of raw ones, which can turn minutes into milliseconds.

Step 3: organise the big tables

Event, log and transaction tables grow forever. Two built-in features help:

• Partitioning by date. You split one big table into monthly or weekly partitions, and Postgres puts each row in the right one. Queries for "last month" only touch last month's piece, and deleting old data becomes dropping a partition instead of a slow delete.
• BRIN indexes. For tables where rows arrive in time order, a BRIN index on the timestamp is very small and lets date-range queries skip the parts of the table that can't match.

Also check the basics: the slowest queries (the pg_stat_statements extension lists them), missing indexes on columns you filter by, and whether VACUUM and ANALYZE keep up.

Step 4: upgrade, then consider a columnar engine inside Postgres

PostgreSQL 18, released in September 2025, added asynchronous I/O for sequential scans, bitmap heap scans and vacuum. The project reported up to 3x faster reads from storage in some scenarios. Reporting queries scan a lot, so the upgrade is worth testing.

For heavier analytics, you can now add a column-oriented engine without leaving Postgres. pg_duckdb, which reached version 1.0 in September 2025, runs DuckDB's analytical engine inside the Postgres process and can also query Parquet files in object storage.

Its makers are careful to say it won't turn Postgres into a full data warehouse. It also uses a lot of memory and CPU, so run it on a replica, not the database your app depends on. (Extensions must be installed on the primary too, so restrict who can use it there.)

Do you need a data warehouse? Seven signs you've outgrown Postgres

If you've done the steps above and any of these are true, a separate warehouse starts to earn its cost.

1. You need to combine many systems. Sales from your app, payments from your processor, spend from three ad platforms, leads from the CRM. This is often the real reason, and it's about integration, not size.
2. Reporting hurts even on a replica. Queries get cancelled, or replicas fall too far behind or need so much capacity that they cost more than a warehouse would.
3. Questions span your whole history. Even after summary tables, partitions and indexes, important questions scan most of your data and take minutes.
4. Many people explore the data freely. Self-service dashboards with unpredictable queries from dozens of users are what warehouses are designed for.
5. You need history your app throws away. What a customer's plan was last March, or a price before it changed. Warehouses keep snapshots; application databases usually overwrite.
6. Storage costs are climbing. Years of raw events on fast application database disks get expensive. Warehouses and data lakes usually store them more cheaply.
7. Analytics access needs separating from production. For security or compliance, analysts shouldn't touch the database holding live customer data, even read-only.

One sign or two mild ones usually mean "not yet". Several strong ones mean it's time.

If you do move, move gradually

A warehouse project doesn't have to be big:

• Keep Postgres as the source of truth for your application. The warehouse is a copy for analysis.
• Load first, transform after (ELT). Copy raw data into the warehouse on a schedule through a data pipeline, then shape it with version-controlled SQL models. Tools like dbt do this well, with Airflow or a simple scheduler to run it.
• Start with one question that matters, such as revenue by channel, and build the pipeline for that before adding more sources.
• Define each metric once. "Active customer" should mean the same thing in every dashboard.

Frequently asked questions

The bottom line

Slow reports are usually fixed by moving them off the main database and summarising instead of recalculating. A warehouse pays for itself when the real problem is combining many systems, keeping history or serving many analysts.

Not sure which side you're on? Book a free consultation and we'll look at your reporting setup with you and suggest the smallest change that would fix it.

Sources

• PostgreSQL Global Development Group, PostgreSQL 18 Released (September 2025): https://www.postgresql.org/about/news/postgresql-18-released-3142/
• PostgreSQL 18 documentation, Hot Standby (query conflicts on replicas): https://www.postgresql.org/docs/18/hot-standby.html
• PostgreSQL 18 documentation, Replication settings (max_standby_streaming_delay, hot_standby_feedback): https://www.postgresql.org/docs/18/runtime-config-replication.html
• PostgreSQL 18 documentation, REFRESH MATERIALIZED VIEW: https://www.postgresql.org/docs/18/sql-refreshmaterializedview.html
• PostgreSQL 18 documentation, Table Partitioning: https://www.postgresql.org/docs/18/ddl-partitioning.html
• PostgreSQL 18 documentation, BRIN Indexes: https://www.postgresql.org/docs/18/brin.html
• PostgreSQL 18 documentation, pg_stat_statements: https://www.postgresql.org/docs/18/pgstatstatements.html
• PostgreSQL 18 documentation, Row Security Policies (superusers and BYPASSRLS roles bypass row security): https://www.postgresql.org/docs/18/ddl-rowsecurity.html
• MotherDuck, Announcing pg_duckdb Version 1.0 (September 2025): https://motherduck.com/blog/pg-duckdb-release/