How the Parquet copy works

Where your rows live, what an update writes, what it costs, and what happens if something dies.

Five cards. Your MySQL: nothing installed, DBTrail reads the binlog like a replica. Live index: DBTrail's own MySQL with the before and after of every row for the last hours, yours to back up, capture resumes from here. Refresh: previous snapshot plus changes go through a fold into the next snapshot every five minutes; a changed table writes one small pair beside its file, zero changes is a free hard link, and the table file is rewritten only after a day or when the pairs outgrow it. S3: one Parquet per hour of changes, and per snapshot one Parquet per table with the pairs beside it, never edited, only added. If the node dies: the process restarts with nothing lost, the index MySQL loses recent hours unless backed up, S3 is safe.

Three places

The index. DBTrail's own MySQL. Every row change lands here within seconds, before and after. It holds the last hours and the point capture resumes from, so it is the one thing you back up.
Hourly archives. Each hour older than that becomes one Parquet file in S3.
Snapshots. Every table as one Parquet file, one folder per moment. The first comes from a full read of your database; every later one is the previous snapshot plus the changes since, without reading your database.

When an update reads your database instead

You pick how often a snapshot is made. DBTrail picks how, on every run.

Each scheduled run picks one. Update, the normal case: the previous snapshot plus the changes DBTrail recorded since; your database is not read. Full read with mydumper, only when one of these is true: 1, there is no snapshot yet, the first run; 2, you asked for one, with the Read database now button; 3, an update would cost more, measured on the last runs; 4, the changes can't rebuild a table: a schema change, a TRUNCATE, or a gap in changes. One full read at the start, then updates. You never have to schedule full reads.

No snapshot yet. The first run reads everything. There is nothing to update.
You asked for one. The Read database now button on the Snapshots page reads the whole database once, right away. A schedule saved by an older version can also carry a full read on a timetable; its card then says so (for example "with a full read every 7d").
An update would cost more than a full read. DBTrail times its recent updates and its last full read, and counts the changes waiting. When the update is estimated to take longer, it reads instead. On a big database a full read takes hours, so this only happens after a long stop has piled up more changes than that. Before anything is measured, age decides: a previous snapshot older than 2 hours, or 6 times the interval if that is longer, is read again rather than caught up.
The recorded changes can't rebuild a table. DBTrail starts the update, and stops it when the changes it recorded can't rebuild a table exactly. It publishes nothing rather than a copy that looks right and is wrong. Three things cause this:
- A column was added, dropped or retyped. The snapshot and the new rows no longer have the same columns. Expected after a schema change; nothing to do.
- A table got TRUNCATE, DROP or RENAME. MySQL logs these as one statement, not row by row, so there are no rows to apply. Nothing to do.
- Some changes are missing. DBTrail was stopped longer than MySQL kept its binary logs, so applying the rest would silently miss rows. To avoid it, keep binary logs longer than DBTrail could be down (binlog_expire_logs_seconds, or binlog retention hours on RDS).
If full reads are allowed (the setting that shows the Read database now button), that run reads the whole database instead, in the same slot. If not, the slot is skipped, the Snapshots page says why, and the copy waits for the next full read.

Anything else is an update. A run with nothing new is skipped, not repeated.

Snapshots only in S3, no Local folder: updates work the same way (since 0.101.0). Each one is built in DBTrail's staging folder, uploaded, and the folder is emptied. Tables that changed are downloaded, rewritten and uploaded; tables that did not change are copied inside S3, without being downloaded (except on the first update after a full read, which rewrites every table). Before 0.101.0, every run on such a server was a full read.

Snapshots shows, for every run, which kind it was and why, and what the next run will be. A full read every few minutes on a database that hardly changes means one of these keeps firing: the reason on that page names which. An update publishes all tables at once or nothing.

What an update writes

Every table stops at the same point in the binlog. Per table:

Nothing changed: the previous file is linked. Free.
Something changed: a small pair of files beside the table holds the changes. The table file is not touched.
Rewritten in full once the pairs are a day old or grow past a quarter of the file, and in a few rarer cases. The chain starts over. This rebuilds the file from the previous snapshot. It does not read your database.

One table file, small pairs beside it, DuckDB joins them on every query. The table file orders.parquet holds three rows when the chain started; two are struck through as dead. Refresh 1 writes orders.000001.posdel naming row 1 and orders.000001.upserts with beto 300 and dani 500. Refresh 2 writes orders.000002.posdel naming row 0 and orders.000002.upserts with a delete marker for key 1 and dani 520. Arrows lead to what SELECT * FROM state_shop_orders returns: beto 300, carla 80, dani 520. Three steps run by DuckDB, written in views.sql: table file minus dead rows; newest version per key; add the winners, skip the markers. Who does what: the full backup writes the table file and an empty pair; each refresh writes one new pair, older pairs linked not copied; views.sql holds the recipe; DuckDB runs it on every query.

DuckDB joins the file and its pairs on every query, through views.sql. Another tool reading the files directly, or a copy that takes only the *.parquet files, sees the table as it was when the chain started, with no error.

What it costs

Measured, on synthetic data unless it says otherwise.

Two bar charts, measured on a 2M-row table. What one refresh writes, with 1,500 rows changed: rewrite the table 2,000,000 rows; write one pair 1,500 rows; nothing changed 0, a hard link. What one query reads, seconds for a full-table sum in DuckDB on local disk: flat file 0.02 s; file plus a day of pairs, 288 refreshes, 0.10 s; after the fold 0.02 s. Five times, for a day at most. A lookup by key through the chain costs the same as the full sum. On S3 the number of files counts, one request each.

Size. 1.4 GB of InnoDB became 180 MB of Parquet, 7 to 12 times smaller. Real text compresses less.
Memory. About 4 KB per changed row for narrow rows, 19 KB for wide ones. A long catch-up is the risk; updating more often keeps each window small.
Freshness. Your interval, plus the update's own time, plus capture lag. Measured once on RDS MySQL 8.4 at about 340 transactions per second: 2.7 to 13.2 minutes from commit to DuckDB, median 5.9.

Sizing a machine: Capacity Planning.

Good to know

S3. Hourly archives go up as they are written; snapshots, when the server's schedule has an S3 location. Local snapshots are removed only after S3 confirms.
The newest snapshot. A current pointer beside the snapshot folders moves in one step per update. views.sql reads through it, so it never changes.

If something dies

The DBTrail process: restart it. Capture resumes where it stopped, as long as MySQL still has the binlog.
The local snapshot disk: the next update reads from S3 and rewrites every table once.
The index MySQL: the last hours are gone unless it had backups. restore-index rebuilds the rest from S3.
A power cut: a file cut short fails its check instead of being read.