Choosing Between Materialized Views and CDC Streams with Vitess VReplication

If you run Vitess and need data from one keyspace available somewhere else — either as a queryable denormalized table or as a stream of change events — the built-in answer is VReplication. The decision you actually face is not whether to use it, but which consumption model fits: a continuously maintained materialized view that your application reads directly, or a change data capture (CDC) stream that an external consumer processes. They share the same engine but differ in consistency expectations, schema control, and operational burden. This guide compares them and shows a concrete setup for each.

The decision and its constraints

VReplication copies data from a source table to a target, starting with a full copy phase and then staying current by streaming the MySQL binlog. Before picking a model, confirm these hard constraints:

• The source table must have a primary key or non-nullable unique key. Tables without one cannot be replicated.
• Source and target keyspaces must live in the same Vitess cluster (same topology). Cross-cluster replication needs external tooling.
• All DDL on the source table must go through Vitess Online DDL (vtctlclient ApplySchema). A direct ALTER TABLE on the underlying MySQL instance will break the workflow.
• Filter expressions must be deterministic — no NOW(), RAND(), or similar — because rows are re-evaluated during binlog replay.

Comparing the two consumption models

The short version: if the consumer speaks SQL and tolerates brief staleness, build a materialized view. If the consumer is not a MySQL table at all, or you need every intermediate change rather than just current state, build a CDC stream.

Implementing a materialized view

Suppose an orders table lives in the sharded commerce keyspace, and your analytics workload needs a filtered projection of it in the reporting keyspace. First create the target table in reporting with whatever indexes the analytics queries need — it does not have to match the source schema beyond the selected columns. Then start the workflow from a machine with vtctlclient access to the cluster's vtctld (no special MySQL privileges are needed; the workflow runs under Vitess):

vtctlclient VReplicationExec '{
  "workflow": "orders_reporting_mv",
  "source": {"keyspace": "commerce", "shard": "-"},
  "target": {"keyspace": "reporting", "shard": "-"},
  "tablet_types": "PRIMARY",
  "tables": [{
    "table": "orders",
    "select": "order_id, customer_id, status, total_cents, created_at",
    "filter": "status IN (\u0027paid\u0027, \u0027shipped\u0027)"
  }]
}'

Exact JSON field names vary between Vitess releases; check vtctlclient VReplicationExec --help or the workflow reference for your version before running this in production. The workflow first runs the copy phase, then switches to binlog streaming. Monitor progress:

vtctlclient VReplicationExec '{"workflow": "orders_reporting_mv", "action": "Status"}'

Watch the reported replication lag. Once the copy phase finishes and lag stays near zero, route reporting queries at the target keyspace with replica or rdonly tablet types so analytical reads never touch primaries.

Controlling copy-phase load

The initial full copy reads the source table in bulk and can hurt source tablet performance on large tables. Throttle it with the row-rate flags (for example -min_rows_per_second and -max_rows_per_second) when creating or tuning the workflow. Start conservative, watch source replica lag and query latency, and raise the ceiling if there is headroom.

Validating consistency

Do not trust lag metrics alone. Run VDiff to compare source and target row content:

vtctlclient VDiff commerce.orders reporting.orders

VDiff reports row counts and checksum mismatches. Run it after the initial copy and periodically thereafter, especially after any source DDL. A clean VDiff plus near-zero lag is a reasonable definition of "the view is healthy." Note that because the view is eventually consistent, reads can briefly return stale rows — if a workflow requires read-your-writes, that query must go to the source table on the primary, not the view.

Implementing a CDC stream

For CDC, the same workflow machinery streams binlog events, but the consumer tracks GTID positions so it can resume after a restart. A minimal validation loop looks like this:

1. Create the workflow targeting either a staging table or a stream consumer, as above.
2. Insert a known row into the source table through VTGate.
3. Read the stream or target and confirm the event arrives, recording the GTID position.
4. Restart the consumer from the recorded GTID and confirm no events are lost — and that duplicates, which at-least-once delivery permits, are handled idempotently (for example by keying downstream writes on the primary key plus GTID).

The consumer contract you must implement: checkpoint GTIDs only after downstream writes are durable, and make every apply idempotent. If you skip either, restarts will silently lose or duplicate changes.

DDL resilience and ongoing operations

Schema changes are where both models most often break. Always apply source DDL through Vitess:

vtctlclient ApplySchema --sql="ALTER TABLE orders ADD COLUMN discount_cents INT" commerce

VReplication coordinates with Online DDL so the workflow survives the change; a direct MySQL ALTER TABLE does not. After any DDL, re-check workflow status and run VDiff again. If you add a column the view should include, update the workflow's select list and expect a resync of affected data.

Limitations to plan around

• Cross-cluster replication is out of scope; you would need an external CDC pipeline (for example, a binlog tailer) instead.
• Non-deterministic filters are forbidden, which rules out time-window filters like "rows from the last 24 hours" — maintain those with a retention job on the target instead.
• Materialized views are not a consistency primitive. Anything transactional must read the source.
• VDiff on very large tables is itself expensive; schedule it off-peak.

Choose the materialized view when the consumer is SQL and staleness of seconds is acceptable; choose CDC when you need every change event or a non-MySQL destination. In both cases, the operational core is the same: throttle the copy phase, route DDL through Vitess, and verify with VDiff rather than assuming the stream is healthy.