How to Move Analytics from Your App Database to a Data Warehouse

Picture an events table that has grown past a few hundred million rows. The funnel query that was fast in spring now crawls, and the nightly report competes with checkout traffic for the same CPU. You have tuned what you can. At this point the question is no longer how to speed up PostgreSQL, but whether analytics belongs in the same database as your application at all. This article shows how to move analytics to a data warehouse without losing numbers on the way.

A data warehouse is a database built for large scans and aggregations, usually with columnar storage. It is a different tool from your transactional database, and the migration is mostly a data-engineering exercise, not a rewrite.

You will learn when the move is justified and how ELT differs from ETL. You will also choose between change data capture and a batch export, model events in the warehouse, and cut over safely. The hands-on path uses ClickHouse. A comparison table covers BigQuery so you can choose with open eyes.

This article stays on migration. Indexing and partitioning inside PostgreSQL belong to the PostgreSQL tuning article, which you should try first if you have not. The architecture that feeds the warehouse is explained in the event-driven analytics article.

Why Move Analytics to a Data Warehouse

PostgreSQL stores rows. Analytics reads a few columns from many rows. Row storage forces the engine to read whole rows from disk to answer “how many page_view events per day,” while a column store reads only the columns the query touches. On wide event tables, that difference is large.

The second reason is isolation. Long aggregate queries hold resources and can slow the transactions that take orders. A read replica helps, but it still stores rows and still lags under heavy load.

Move when these signs appear together:

• Dashboard and report queries take tens of seconds even with correct indexes and partitions.
• Analytics load visibly affects checkout latency or vacuum behavior.
• Storage cost for raw events dominates the database bill.
• Analysts want to join events with data from other systems, such as payments or support tickets.

One sign alone rarely justifies the move. A slow query with a missing index is a tuning problem, not a migration problem. In my experience building event pipelines, teams that migrate to escape a bad query end up with the same bad query on a more expensive system.

ELT Versus ETL

ETL transforms data before loading it. ELT loads raw data first and transforms it inside the warehouse. For events, ELT is almost always the better default, and the reason is replay.

The exception is privacy. If a field must never leave your primary system, remove or hash it in the export step. The Acme Shop events table never stores a raw IP address (the schema article rules it out), so the export can be a plain copy.

With ELT, the warehouse holds a faithful copy of events, and derived tables such as sessions, users and daily_pageviews are rebuilt from it with SQL. That mirrors the derivation described in the schema design article.

CDC Versus Batch Export

Change data capture streams every insert, update and delete from the database log. Batch export copies rows on a schedule. Choose by looking at how your data changes, not by what sounds modern.

Batch export

Your events are immutable. Rows are inserted and never updated. That makes batch export simple: copy everything received since the last successful run. It needs no special database configuration, and a failed run is rerun safely if loads are idempotent.

The cost is latency. Data arrives in the warehouse minutes to hours after capture, depending on your schedule. For daily and weekly reporting, that is fine.

Change data capture

With PostgreSQL, CDC uses logical decoding, which streams row changes through a replication slot. It requires wal_level set to logical, and tools like Debezium read the slot and publish changes to a broker.

CDC gives low latency and captures updates and deletes, which matters for tables like users that change. It also adds a real operational risk. A replication slot makes PostgreSQL keep WAL files until the consumer reads them. If the consumer stalls, WAL accumulates and can fill the disk. A mistake I have seen in production is a forgotten slot from an abandoned experiment that grew the WAL directory until the primary database stopped accepting writes. Set max_slot_wal_keep_size to cap that growth, and alert on slot lag.

If you already publish events through a broker, a third path exists: add the warehouse loader as one more consumer of the log. That is the cleanest option and costs nothing extra on the database.

Choosing the Destination: ClickHouse or BigQuery

This article uses ClickHouse for the hands-on part because you can run it locally and see everything. BigQuery is the main alternative.

Check each vendor’s current pricing and quota pages before committing, because both change. If your team has no one to run a database, BigQuery’s managed model is a legitimate reason to pick it. Buying beats building when operations are your bottleneck.

Modeling Events in the Warehouse

Do not port the PostgreSQL schema line by line. Data warehouse tables favor wide, denormalized rows and a sort order that matches your common filters. In ClickHouse, the MergeTree family is the standard engine, and its ORDER BY clause defines the sort key. The MergeTree documentation explains the sort key and partitioning options.

Use ReplacingMergeTree for the raw events table. Rows with the same sort key are collapsed during background merges, which gives you idempotent reloads. Because the ORDER BY tuple is the deduplication key, it must include event_id.

CREATE TABLE events
(
    event_id      String,
    event_name    LowCardinality(String),
    occurred_at   DateTime64(3, 'UTC'),
    received_at   DateTime64(3, 'UTC'),
    anonymous_id  String,
    user_id       Nullable(String),
    session_id    String,
    page_url      String,
    referrer      Nullable(String),
    user_agent    String,
    properties    String   -- JSON text, query with JSONExtract functions
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(occurred_at)
ORDER BY (event_name, toDate(occurred_at), event_id);

This mirrors the events table from the schema design article, including received_at, which that table already has. It leaves out the generated page_path column, which you can derive in the warehouse when you need it. And properties becomes plain JSON text, because the PostgreSQL JSONB type has no direct equivalent you should depend on. The export watermark relies on received_at, so never drop it.

Note the trade-off in the sort key. Putting event_name first makes per-event queries fast, but a query that filters only by date reads more data. Choose the order from your real queries, and revisit it early, because changing it later means rebuilding the table.

Deduplication in ReplacingMergeTree happens during background merges, which run eventually, not instantly, and only within one partition. Duplicates of an event share the same occurred_at, so they land in the same monthly partition. Until a merge happens, duplicates can appear in query results. Use FINAL for exact reconciliation queries, and accept that it is slower.

The Hands-On Pipeline: Incremental Daily Export

The pipeline has three steps: export one UTC day from PostgreSQL, load the file into ClickHouse, and reconcile counts. Using whole UTC days as the unit keeps reruns simple, because a rerun loads the same rows again and the duplicates collapse.

Use the received_at column for the window, not occurred_at. Client clocks are wrong, and mobile devices send events hours late. Windowing on occurred_at silently drops those late arrivals. Windowing on received_at catches every row exactly once.

The first version most people write uses \copy, because it is the familiar bulk-export command. It fails immediately.

// WRONG: \copy reads the rest of one line, with no variables and no line continuation
psql "$PG_URL" -v day="$DAY" <<'SQL'
\copy (SELECT * FROM events WHERE received_at >= :'day') \
  TO '/tmp/events.csv' WITH (FORMAT csv)
SQL

When I ran it, psql answered \copy: parse error at end of line. The psql documentation explains why: for \copy, the whole remainder of the line is the argument, and psql does no variable interpolation inside it. Pasting the date in by shell concatenation would run, but it is the SQL-injection habit this series avoids. The fix is a plain COPY ... TO STDOUT statement, which psql sends as ordinary SQL and which therefore does interpolate :'day'. Piping its output straight into ClickHouse also removes the temporary file.

#!/usr/bin/env bash
# export-day.sh - copy one UTC day of events from PostgreSQL to ClickHouse
# usage: PG_URL=postgres://... ./export-day.sh 2025-10-01
set -euo pipefail

DAY="$1"
NEXT=$(date -u -d "$DAY + 1 day" +%F)   # GNU date; on macOS use: date -u -j -v+1d -f %F "$DAY" +%F

psql "$PG_URL" -X -q -v ON_ERROR_STOP=1 -v day="$DAY" -v next="$NEXT" <<'SQL' \
  | clickhouse-client --query "INSERT INTO events FORMAT CSV"
SET TIME ZONE 'UTC';
COPY (
  SELECT event_id,
         event_name,
         to_char(occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.MS'),
         to_char(received_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.MS'),
         anonymous_id, user_id, session_id, page_url, referrer, user_agent,
         properties::text
  FROM events
  WHERE received_at >= :'day'::timestamptz
    AND received_at <  :'next'::timestamptz
) TO STDOUT WITH (FORMAT csv, NULL '\N');
SQL

echo "exported $DAY"

Four details protect you. First, COPY ... TO STDOUT streams rows to the client, so the database server needs no file access, and it is the fastest bulk path out of PostgreSQL (see the COPY reference). Second, :'day' is a properly quoted literal, not shell concatenation. Third, SET TIME ZONE 'UTC' makes the string 2025-10-01 mean midnight UTC, whatever the connecting role defaults to. Fourth, pipefail and ON_ERROR_STOP=1 make a failed export fail the script, instead of loading a half-written stream silently.

The NULL '\N' option matches how ClickHouse reads nulls in CSV. I ran this script against PostgreSQL 17 and a current ClickHouse server, using rows with commas, quotes, accented characters and nulls, and the loaded rows matched. Check both tools’ documentation for your versions, since format options can differ.

Schedule it with cron or any job runner for yesterday’s date, shortly after midnight UTC plus a grace period for late events. Then re-export the previous two days as well. Because ReplacingMergeTree collapses repeats, overlapping windows are cheap insurance against rows that committed late. In my test, loading the same day twice left duplicates visible until I queried with FINAL, which is the behavior described earlier.

Reconcile Before You Trust Anything

A migration without reconciliation is a belief, not a result. After each load, compare counts per day and per event name between both systems. Run this in PostgreSQL:

SET TIME ZONE 'UTC';

SELECT event_name, count(*) AS n
FROM events
WHERE received_at >= '2025-10-01' AND received_at < '2025-10-02'
GROUP BY event_name
ORDER BY event_name;

And the equivalent in ClickHouse, using FINAL to apply deduplication:

SELECT event_name, count() AS n
FROM events FINAL
WHERE received_at >= '2025-10-01 00:00:00' AND received_at < '2025-10-02 00:00:00'
GROUP BY event_name
ORDER BY event_name;

The two result sets must match exactly. If they do not, the usual suspects are time zone handling, null parsing and rows that arrived after the export ran. We once double-counted purchases because a loader treated a retried batch as new data and nothing compared totals against the source. The fix was a reconciliation query that ran after every load and alerted on any difference.

Rebuild the Derived Tables in the Warehouse

With raw events in place, recreate the rollup using warehouse SQL. This is the ELT step: the transformation runs where the data lives and can be rerun at will.

CREATE TABLE daily_pageviews_new
(
    day      Date,
    views    UInt64,
    visitors UInt64
)
ENGINE = MergeTree
ORDER BY day;

INSERT INTO daily_pageviews_new
SELECT toDate(occurred_at) AS day,
       count() AS views,
       uniqExact(anonymous_id) AS visitors
FROM events FINAL
WHERE event_name = 'page_view'
GROUP BY day;

-- Creates the live table on the first run, and does nothing afterwards
CREATE TABLE IF NOT EXISTS daily_pageviews AS daily_pageviews_new;

EXCHANGE TABLES daily_pageviews_new AND daily_pageviews;
DROP TABLE daily_pageviews_new;

This rollup is simpler than the PostgreSQL one in the schema article: it has one row per day and no page_path, and it names the distinct count visitors to match the stats API. It is rebuilt into a new table and swapped in with EXCHANGE TABLES, so a rerun never doubles the rows and readers never see an empty table. A plain second INSERT into the live table would duplicate every day, because MergeTree does not deduplicate.

Rebuilding a rollup from scratch is cheap on columnar hardware at moderate volume, so recompute fully when logic changes and measure the time on your own data. Incremental updates become an optimization, not a requirement. For sessions and retention, reuse the patterns from the earlier SQL work in the series, and expect dialect differences from MySQL and PostgreSQL, such as toDate and uniqExact above.

Cutover Without Drama

Cut over in stages, so every step can be undone.

1. Backfill. Export history in monthly or weekly chunks, oldest first. Reconcile each chunk.
2. Dual-run. Keep the nightly export running while PostgreSQL stays the source of truth. Compare daily.
3. Shadow reads. Add warehouse-backed versions of one stats endpoint, for example GET /v1/stats/funnel, behind a flag. Compare its output with the PostgreSQL version for the same date range.
4. Switch reads. Flip the flag per endpoint, starting with the most expensive query. The stats API keeps its response shape, so dashboards need no change.
5. Stop writing analytics queries to PostgreSQL. Keep the collector writing to PostgreSQL for now, because it is your durable capture store.
6. Prune. After a retention window you trust, drop old partitions from PostgreSQL events and keep a recent hot window.

Keep the capture path unchanged through all of this. The collector still writes to PostgreSQL, or to a broker, and the warehouse is a downstream copy. That boundary means a warehouse outage never costs you events.

How Real Systems Do This

Snowplow loads enriched events into warehouses such as BigQuery and Snowflake, using loader components that append batches. Open-source stacks built around PostHog use ClickHouse as the primary analytical store. Managed pipeline tools such as Airbyte and Fivetran automate the extract and load steps, and dbt is the common choice for the SQL transformation layer in ELT.

These setups share your design: land raw data first, keep it immutable, and build models on top. The decision to hand-build or buy the pipeline is mostly about team size. A managed connector costs money and saves weeks of maintenance, so price it honestly against your own time.

Decision Framework

1. Have you tuned indexes, partitions and rollups, and do queries still hurt? If not, tune first.
2. Do analysts need data from more than one system? If yes, the warehouse earns its place sooner.
3. Is your source data append-only? If yes, use batch export. If it mutates, evaluate CDC.
4. Can you run a database cluster? If not, choose a managed destination such as BigQuery or ClickHouse Cloud.
5. What freshness do consumers truly need? Hours means batch. Seconds means a broker consumer or CDC.

When NOT to Use This

• Your queries are fine after tuning. There is no universal row count that forces a move, and a tuned PostgreSQL with rollups handles a lot more than most teams expect. A second system doubles your operational surface for no gain.
• You need real-time numbers inside the app. A batch-fed warehouse is minutes or hours behind. Serve live counters from the primary store or a stream consumer.
• You have no one to operate it. A self-hosted warehouse needs upgrades, backups and monitoring. Consider a hosted analytics product, which can be cheaper than the engineering time.

Common Mistakes

• Windowing exports on occurred_at, which drops late-arriving events and makes warehouse totals lower than the source.
• Skipping reconciliation, so a loader bug goes unnoticed until a finance report disagrees.
• Forgetting deduplication, which doubles counts after a retried load.
• Leaving an unused replication slot on the primary, which fills the disk with WAL.
• Porting the normalized PostgreSQL schema unchanged, which produces slow joins on a system built for wide tables.
• Switching all dashboards at once, which removes any quick way back when numbers differ.

Key Takeaways

• Migrate only after tuning has failed and scan-heavy queries still hurt production.
• Default to ELT: load raw events, transform with SQL in the warehouse.
• Use batch export for immutable events, and window on received_at.
• Make loads idempotent with event_id and a deduplicating table engine.
• Reconcile counts per day and event name after every load.
• Cut over one endpoint at a time and keep PostgreSQL as the capture store.
• Choose ClickHouse for control and BigQuery for low operations, then check current pricing.

FAQ

When should I move analytics to a data warehouse?

Move when aggregate queries stay slow after indexing and partitioning, or when analytics load affects your application. Also move when analysts need to join events with other systems. A single slow query is usually a tuning problem.

What is the difference between ELT and ETL?

ETL transforms data before loading it, while ELT loads raw data and transforms it inside the warehouse. ELT keeps raw history, so you can fix a transform bug by rerunning SQL. Use ETL when sensitive fields must be removed before data leaves.

Should I use CDC or batch export for analytics events?

Use batch export for append-only events, since rows never change. CDC suits mutable tables or needs for second-level freshness, but it requires logical decoding and careful monitoring of replication slots.

Is ClickHouse or BigQuery better for product analytics?

Neither wins everywhere. ClickHouse gives low latency and cost control but needs operations unless you use a managed offering. BigQuery removes operations and fits spiky usage, with costs tied to data scanned or reserved capacity.

How do I check that no events were lost in the migration?

Compare counts per day and per event name between both systems after each load, using deduplicated queries on the warehouse side. Alert on any mismatch, and window on received_at so late events are not missed.

Conclusion

Moving to a data warehouse is a disciplined copy-and-verify exercise, not a leap of faith. Land raw events idempotently, reconcile every load, and switch reads gradually while PostgreSQL stays the capture store.

Rule of thumb: never trust a pipeline you have not reconciled against its source.

Last updated on 9 October 2026.