Learn Vitess - Hybrid Workloads & OLAP Integration

#Introduction

Vitess is optimized for OLTP: small, frequent transactions. But business teams still need big reports — and running heavy analytical queries on top of a transactional database is a recipe for slowing down the application. Episode 17 covers how to combine the two worlds: OLTP stays agile in Vitess, while analytical loads run elsewhere without interfering.

Episode 17 roadmap: separating OLTP and OLAP, read replicas for reporting, snapshot export for ETL, then data warehouse integration.

#Separating OLTP and OLAP

The first principle: never run heavy analytical queries on the primary. Queries like large aggregations or cross-table joins drain the resources transactions use. Workload separation is an architecture decision, not just a policy.

Common separation model:

OLTP in Vitess: all application transactions, sharded and replicated.
OLAP elsewhere: data is copied to a warehouse or data lake, and analytical queries run there.
Light reporting in Vitess: small, frequent reports (operational dashboards) route to RDONLY tablets.

Workload division

Transactions (OLTP) : Vitess primary + replica
Light dashboards     : RDONLY tablets in Vitess
Large analytics      : separate data warehouse

The division table above keeps load properly directed. Large analytical queries never touch the transaction path.

#Read Replicas for Reporting

For reporting that needs near-real-time data but doesn't want to burden the primary, use RDONLY tablets. This tablet type is forbidden from being promoted to primary (episode 6), so it's safe for heavy workloads without disturbing the failover path.

Add an rdonly tablet

vtctlclient InitShardMaster -force commerce/0 -master_force \
  -accept_primary_read_only=true
vtctlclient TabletExternallyReparented <tablet-alias>

To create RDONLY tablets, configure them in values.yaml:

Add rdonly replicas

topology:
  keyspaces:
    - name: commerce
      shardTablets:
        - name: rdonly
          replicas: 2
          type: rdonly

type: rdonly creates the tablets as RDONLY. Point your reporting application connections at VTGate with a reporting user, and make sure VTGate selects RDONLY tablets for those queries.

Info

Data on RDONLY tablets can lag the primary by a few seconds. For dashboards that need near-real-time data, this is usually enough. For financial reports that must be precise, wait until replication lag approaches zero before running queries.

#Snapshot Export for ETL

For a data warehouse, Vitess provides an efficient data export path. Common patterns:

Periodic snapshots: take a consistent snapshot from an RDONLY tablet, store it in object storage, then let an ETL pipeline process it.
Streaming via VReplication: continuously copy data changes to an external target — an approach suited to warehouses that need fresh data.

Vitess backups (episode 6) actually already produce consistent snapshots — this format can be used as a warehouse data source. For streaming, VReplication to non-MySQL targets is a growing feature used for real-time integration.

Backup as an ETL snapshot source

vtctlclient Backup <tablet-alias-rdonly>

vtctlclient Backup creates a snapshot from an RDONLY tablet. An ETL pipeline can process this backup into the format the warehouse needs.

Warning

Take ETL backups from RDONLY tablets, not from the primary. Backing up on the primary adds write load that disrupts the application. This is another reason every shard should always have RDONLY tablets.

#Integrating with a Data Warehouse

After data leaves Vitess, the warehouse processes it. Several common integration patterns:

Daily batch: snapshot every night, loaded into the warehouse during low traffic.
CDC streaming: data changes flow to a Kafka topic, then to the warehouse.
Direct query: the warehouse queries Vitess through a connector — suitable for data that rarely changes.

Example of a lightweight warehouse query populated from Vitess:

Analytical query in the warehouse

SELECT region, COUNT(*) AS total_users
FROM users_dw
GROUP BY region
ORDER BY total_users DESC

The SELECT ... GROUP BY region query above runs in the warehouse, not in Vitess. The users_dw data is synchronized from Vitess through an ETL pipeline. This way, large aggregations never touch the transactional database.

To keep data quality in the warehouse, consider:

Schema alignment: make sure data types and column names are consistent between Vitess and the warehouse.
Idempotent load: the pipeline must be rerunnable without duplicating data.
Data validation: periodically compare row counts between Vitess and the warehouse.

#Closing

In this episode 17 you understood how to combine Vitess with analytical workloads: separating OLTP and OLAP at the architecture level, using RDONLY tablets for reporting, exporting snapshots for ETL pipelines, and integrating data into a warehouse with batch or CDC streaming patterns.

Key takeaways:

Never run heavy analytical queries on the primary.
RDONLY tablets are the light-reporting path that doesn't disturb failover.
Backups from RDONLY tablets can be the ETL snapshot source.
Warehouses need idempotent pipelines with periodic data validation.
Replica data can lag — choose when data is "fresh enough" for reports.
CDC streaming suits warehouses that need near-real-time data.

In the next episode, episode 18, we manage the cluster's brain: control plane and topology management — Topology Service choices, topology repair and backup, and the vtctld UI and automation. See you there!