Data Warehouse: When to Stop Querying Production and Build a Warehouse

Every organization I've worked with starts the same way: someone needs a report, someone else knows enough SQL to write a query against the production database, and it works. It keeps working for a surprisingly long time, right up until the day it doesn't, and the move from "querying production is fine" to "querying production is actively dangerous" is gradual enough that most teams cross it without noticing. Building a warehouse is a real project with real cost, and I've seen it justified both far too early and far too late. Here's how I actually decide, with the queries I use to replace gut feel with evidence. The examples use PostgreSQL because that's where I see this most often, but the reasoning carries over to any OLTP database.

The tell isn't data volume, it's query shape

The instinct is to think about warehouses in terms of row counts: "we have ten million rows now, time for a warehouse." That's rarely the actual trigger. A well-indexed production table can serve point lookups against ten million rows all day without anyone noticing. What breaks things is query shape: a report that scans a large fraction of a table, joins across several large tables, or aggregates over a long time window. That kind of query competes with the transactional workload for the same buffer cache and the same I/O. A full scan against a hot table during business hours is what shows up as latency spikes for everyone else using the application at that moment.

Measure it before you argue about it

The conversation gets much shorter once you can show which queries are doing the work and who is running them. Two built-in tools cover most of it.

pg_stat_statements keeps cumulative execution statistics for every normalized statement. It has to be preloaded, which means a restart:

ini

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Then create it in the database you want to watch:

sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Slow query logging catches individual long runs, including the one-off ad hoc query that never repeats often enough to top the cumulative stats. log_min_duration_statement logs every completed statement that ran at least that long. It's off (-1) by default, and it can be changed with a reload:

sql

ALTER SYSTEM SET log_min_duration_statement = '2s';
SELECT pg_reload_conf();

The most useful single step is giving reporting its own login role, even if it's the same people. Every statistic below can then be split by role, and you get a place to hang guardrails. pg_read_all_data (PostgreSQL 14 and later) grants read access to every table, view, and sequence without handing out ownership. ALTER ROLE ... SET puts a hard ceiling on how long any one reporting query can run:

sql

CREATE ROLE reporting LOGIN PASSWORD '<password>';
GRANT pg_read_all_data TO reporting;
ALTER ROLE reporting SET statement_timeout = '60s';
ALTER ROLE reporting SET default_transaction_read_only = on;

With that in place, this query shows how much of the server's execution time and disk reads each role accounts for since the stats were last reset. Viewing other roles' query text needs superuser or membership in pg_read_all_stats.

sql

SELECT r.rolname,
       sum(s.calls)                                        AS calls,
       round(sum(s.total_exec_time)::numeric / 1000)       AS exec_seconds,
       round((100 * sum(s.total_exec_time) / sum(sum(s.total_exec_time)) OVER ())::numeric, 1) AS pct_exec_time,
       sum(s.shared_blks_read)                             AS blocks_read_from_disk,
       sum(s.temp_blks_written)                            AS temp_blocks_written
FROM pg_stat_statements s
JOIN pg_roles r ON r.oid = s.userid
WHERE s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY r.rolname
ORDER BY exec_seconds DESC;

shared_blks_read counts blocks that weren't already in shared buffers, which is a decent proxy for "this query is pushing the application's working set out of cache." temp_blks_written means sorts or hashes spilled to disk, the classic sign of a big aggregate or join.

To catch it in the act, check pg_stat_activity for long-running active queries:

sql

SELECT pid, usename, now() - query_start AS runtime, wait_event_type, wait_event,
       left(query, 100) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY runtime DESC;

A script to run weekly

I run this every week (cron or a CI schedule) and paste the output into the thread whenever someone asks "is reporting hurting production?" It reports each role's share of execution time and disk reads, the top statements for the reporting roles, and anything running long right now.

It needs Python 3.10+, psycopg2-binary, the pg_stat_statements extension, and a role with pg_read_all_stats.

python

#!/usr/bin/env python3
"""
reporting_pressure.py

Summarizes how much of a PostgreSQL database's execution time and disk reads
come from reporting roles, using pg_stat_statements and pg_stat_activity.

Usage:
    python reporting_pressure.py --dsn <dsn> --reporting-roles reporting,metabase [--top 10]
Needs: pg_stat_statements installed in the database; a role with pg_read_all_stats.
"""

import argparse
import sys

import psycopg2

DB_FILTER = "s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database())"

SHARE_SQL = f"""
    SELECT r.rolname = ANY(%s)          AS is_reporting,
           sum(s.total_exec_time)       AS exec_ms,
           sum(s.shared_blks_read)      AS blks_read,
           sum(s.temp_blks_written)     AS temp_written
    FROM pg_stat_statements s
    JOIN pg_roles r ON r.oid = s.userid
    WHERE {DB_FILTER}
    GROUP BY 1
"""

TOP_SQL = f"""
    SELECT r.rolname, s.calls,
           round(s.total_exec_time::numeric / 1000, 1) AS total_s,
           round(s.mean_exec_time::numeric, 1)         AS mean_ms,
           s.shared_blks_read,
           left(regexp_replace(s.query, '\\s+', ' ', 'g'), 70) AS query
    FROM pg_stat_statements s
    JOIN pg_roles r ON r.oid = s.userid
    WHERE {DB_FILTER} AND r.rolname = ANY(%s)
    ORDER BY s.total_exec_time DESC
    LIMIT %s
"""

LONG_SQL = """
    SELECT pid, usename, date_trunc('second', now() - query_start) AS runtime,
           left(regexp_replace(query, '\\s+', ' ', 'g'), 70) AS query
    FROM pg_stat_activity
    WHERE backend_type = 'client backend' AND state = 'active'
      AND now() - query_start > interval '30 seconds'
    ORDER BY runtime DESC
"""


def pct(part, whole):
    return 0.0 if not whole else 100.0 * float(part) / float(whole)


def main() -> int:
    parser = argparse.ArgumentParser(description="Measure reporting load on a PostgreSQL database.")
    parser.add_argument("--dsn", required=True)
    parser.add_argument("--reporting-roles", required=True, help="Comma-separated role names")
    parser.add_argument("--top", type=int, default=10)
    args = parser.parse_args()
    roles = [r.strip() for r in args.reporting_roles.split(",") if r.strip()]

    conn = psycopg2.connect(args.dsn)
    try:
        with conn, conn.cursor() as cur:
            cur.execute(SHARE_SQL, (roles,))
            totals = {row[0]: row[1:] for row in cur.fetchall()}
            rep = totals.get(True, (0, 0, 0))
            other = totals.get(False, (0, 0, 0))
            print("Share of work from reporting roles since last stats reset:")
            for i, label in enumerate(("execution time", "blocks read from disk", "temp blocks written")):
                print(f"  {label:<22} {pct(rep[i], rep[i] + other[i]):5.1f}%")

            cur.execute(TOP_SQL, (roles, args.top))
            print(f"\nTop {args.top} reporting statements by total time:")
            for rolname, calls, total_s, mean_ms, blks, query in cur.fetchall():
                print(f"  {rolname:<12} {calls:>8} calls {total_s:>9}s total {mean_ms:>9}ms avg {blks:>10} blks  {query}")

            cur.execute(LONG_SQL)
            running = cur.fetchall()
            print(f"\nActive queries over 30s right now: {len(running)}")
            for pid, user, runtime, query in running:
                print(f"  pid {pid} {user} {runtime}  {query}")
    finally:
        conn.close()
    return 0


if __name__ == "__main__":
    sys.exit(main())

Sample output:

text

Share of work from reporting roles since last stats reset:
  execution time          41.3%
  blocks read from disk   67.8%
  temp blocks written     88.2%

Top 10 reporting statements by total time:
  metabase         1440 calls    9312.4s total    6466.9ms avg   48211904 blks  SELECT t.client_id, date_trunc($1, t.created_at) AS month, count(*) ...
  reporting          96 calls    2210.7s total   23028.1ms avg   20113310 blks  SELECT * FROM tickets t JOIN ticket_events e ON e.ticket_id = t.id ...

Active queries over 30s right now: 1
  pid 48122 metabase 0:01:12  SELECT t.client_id, date_trunc($1, t.created_at) AS month, count(*) ...

Those numbers are illustrative. The pattern to look for is a small number of reporting calls accounting for an outsized share of disk reads and temp spill. That shape means a handful of reports is doing most of the damage.

Try the cheap fixes first

Each of these patches one symptom, and each one is much cheaper than a warehouse:

A read replica. Streaming replication gives you a hot standby that accepts read-only queries. The catch is documented plainly: when replaying WAL on the standby conflicts with a running query (vacuum on the primary removing rows the query can still see, for example), the standby waits up to max_standby_streaming_delay and then cancels the query. Setting hot_standby_feedback = on prevents those cleanup conflicts, but it delays dead-row cleanup on the primary and can cause table bloat there. Long reports on a replica are a trade-off, not a free lunch.

Logical replication of a few tables. If reports only need tickets, clients, and time_entries, publish just those to a separate reporting database, where you're free to add indexes and materialized views production would never tolerate:

sql

-- on the production (publisher) database; requires wal_level = logical.
-- Each table needs a primary key (or REPLICA IDENTITY), or UPDATE and DELETE on it fail.
-- repuser needs LOGIN, REPLICATION, SELECT on the tables, and a pg_hba.conf entry.
CREATE PUBLICATION reporting_pub FOR TABLE tickets, clients, time_entries;

-- on the reporting (subscriber) database, after creating the same tables
CREATE SUBSCRIPTION reporting_sub
    CONNECTION 'host=<prod-host> dbname=app user=repuser password=<password>'
    PUBLICATION reporting_pub;

The PostgreSQL docs point out that schema and DDL changes are not replicated. Every production migration that touches those tables has to be applied to the subscriber by hand.

Materialized views on the replica or reporting database for the few heavy aggregates everyone reuses, refreshed on a schedule. REFRESH MATERIALIZED VIEW CONCURRENTLY keeps readers unblocked, but it requires a unique index on the view that uses plain column names and covers all rows.

Schema-for-writes and schema-for-reading are different jobs

Production schemas are normalized to make writes safe and consistent, which is exactly the wrong shape for the ad hoc, wide, historical queries reporting wants. I've watched analysts write eight-way joins to answer a question that a denormalized fact table would answer in one. That's not an analyst skill problem. It's a sign the two workloads want different physical layouts of the same data, and serving both from one schema means one of them is always going to be awkward. A replica doesn't fix this, since it has the same schema. Logical replication into a separate database starts to, because you can reshape the data there.

Historical questions are a different signal than current-state questions

Production databases are optimized to answer "what is true right now," and most are actively hostile to "what was true a year ago." Old rows get updated in place, soft-deleted, or archived out as normal operational hygiene. Once a question needs trend data, period-over-period comparison, or "what did this record look like before it changed," you're asking production for data that no longer exists. No index fixes that.

The honest workaround is to start capturing history yourself, and the moment you do, you've started a warehouse. For example, a nightly snapshot of open tickets is enough to answer "how big was the backlog on the first of each month" going forward:

sql

CREATE TABLE IF NOT EXISTS reporting.ticket_daily_snapshot (
    snapshot_date date NOT NULL,
    ticket_id     bigint NOT NULL,
    client_id     bigint,
    status        text,
    priority      text,
    assigned_team text,
    PRIMARY KEY (snapshot_date, ticket_id)
);

INSERT INTO reporting.ticket_daily_snapshot
SELECT current_date, t.id, t.client_id, t.status, t.priority, t.assigned_team
FROM tickets t
WHERE t.status NOT IN ('closed', 'cancelled')
ON CONFLICT (snapshot_date, ticket_id) DO NOTHING;

It can't reconstruct the past, which is exactly why it's worth starting before anyone asks.

The actual trigger, in practice

The point where I stop resisting a warehouse project is when at least two of these are true at once:

Reporting queries show up in the slow query log, or at the top of the pg_stat_statements breakdown, against tables the application also writes to, and the replica or timeout patches have already been tried.
Someone is asking a question that needs data production has already purged or overwritten.
More than one team wants a version of "the same" report with slightly different definitions of the same metric. That's really a request for a shared, agreed-upon source of truth, not another one-off query.

Any one of those alone can usually be patched: a read replica, an archive table, a shared view. All three together mean the patches have stopped being cheaper than the project, and it's time to build the thing properly instead of adding one more workaround to production.

When it is time, the warehouse doesn't have to be big. A second Postgres fed by logical replication or scheduled loads works. So does a managed warehouse: BigQuery, for example, doesn't charge for batch load jobs on its default shared slot pool, gives 1 TiB of on-demand query processing free per month, and lists $6.25 per TiB after that in US regions as of this writing (check the pricing page for your region). The loading side is covered in incremental loads from Postgres to BigQuery. What goes on top of it, when there's no BI team to own it, is in building a reporting layer without a BI team.