Burning Money with GCP: Replication

Once upon a time, I checked GCP billing on OnlyFelines and something didn’t look right. The last 3 months had a steady, confident climb that had nothing to do with growth of kates.

Most of it was BigQuery. I dug in and classified/labeled every BQ job (which is not a small feat, more on that another day). GCP billing doesn’t tell you much. You see only “Analysis” SKU in billing for all things related to BQ. You reverse-engineer it from billed processed bytes per job to see what is what (and use job metadata / query to see where it comes from). My approximation ended up a few percent off the actual billing, which I found to be pretty darn good for mah purposes.

The biggest chunk was replication jobs. The background MERGE/APPLY operations that Datastream runs to patch incoming CDC data into BigQuery datasets. This is the managed pipeline: PostgreSQL on CloudSQL → logical replication → Datastream → BigQuery.

Here’s what the est. cost for replication APPLY looked like on monthly view:

You see, as the costs were ramping up, I had just added meowdit_log (application audit log) to the replication and triggered a data backfill. The database was a bit over 100 GB total, meowdit_log being roughly 60% of it. I expected a temporary spike in replication cost due to the backfill and then tiny increase. Insignificant enough not to even register on the global billing radar.

I did not expect 4x over 2 months. It surprised me.

Process 1000% of bytes on 1% daily diff

What surprised me more was this: It was genuinely puzzling how on earth you churn through 1,300 fucking gigabytes of data daily to replicate deltas on a database that totals just over 100 GB. That’s turning over the entire dataset more than 10x. Every single day. For weeks.

Please mind that I am leaving aside the tallest bars, reaching as high as １３００ GB on that day. This is the day when the backfill was triggered.

See processed GB daily view, only for jobs related to replication (it says per table, but I removed the grouping for this picture, sue me):

BigQuery Datastream’s merge mode runs full table scans during background MERGE operations to reconcile incoming changes. No shortcut. No partitioning trick. The docs say it plainly:

Only query jobs take advantage of BigQuery partitioning. Background apply jobs and runtime merge jobs can’t use partitioning.

I verified it, because I was desperate — added partitioning to meowdit_log, checked daily processed bytes. Zero change. Partitioning does not work, because for mutable data, changes can happen to any row, so you need to compare incoming change batch towards the full table (since you can have new rows as well as updates for records added 1 year ago).

Increasing max_staleness helped a little. Going from 15 minutes (default) to 12 hours reduced the frequency of apply jobs. Fewer scans, same scan size per run. The savings were mediocre.

Append / Merge

meowdit_log is append-only by nature. Events come in, they never get updated or deleted. This table has no business being replicated in merge mode.

I set up a second Datastream pipeline in append-only mode for meowdit_log, ran the backfill, let both streams operate side by side. The mode merge / append affects the whole stream, so to do that you setup new stream, new publication, new slot, new destination…

Then I went to compare the apply costs. There were no apply jobs for the append-only stream. Not one. Not even for the backfill. I queried INFORMATION_SCHEMA.JOBS, filtered by the service account, checked every dataset:

SELECT DISTINCT destination_table.dataset_id
FROM INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY))
  AND user_email = 'bigquery-adminbot@system.gserviceaccount.com';

Every merge-mode dataset showed up. The append-only dataset did not. The data was there, fully up to date, but BigQuery ran zero processing jobs for it. Append-only mode doesn’t run MERGE operations — rows come in, they go in. No background apply jobs. No full table scans. And the data was more real-time than the merge stream, because there’s no staleness delay to wait out.

Append-only mode is practically free, which I did not expect at all. You pay network. That’s it. Infinite money glitch.

I moved meowdit_log to the append-only stream and dropped it from merge. The daily processed bytes for replication fell off a cliff.

For the remaining tables that actually need MERGE (mutable data, updates, deletes), the next best lever is max_staleness. Cranking it from minutes to hours or even a day reduces how often BigQuery runs those full-table apply jobs. Each scan still processes the entire table, but if it runs once a day instead of every 15 minutes, the math improves dramatically — especially on large datasets where each apply is expensive. It’s a trade-off between data freshness and cost, and for most analytics use cases, day-old data is perfectly fine.

🌯 up

BigQuery Datastream’s merge mode runs full table scans on every background apply regardless of partitioning or delta size, which you maybe know, but I learned through this. Switching append-only tables to append-only mode eliminated the MERGE jobs entirely — same data, near real-time, practically zero cost. There are more ways to save, but this has been by far the most significant in terms of ROI. Until I got to the bottom of this…