ybdb-vanguard

ybdb-vanguard

Hands-on YugabyteDB exercises covering distributed SQL, data architecture, scalability, fault tolerance, multi-region distribution, disaster recovery, CDC, observability, security, and data migration. Each exercise runs in a fully pre-configured cloud development environment — no local YugabyteDB installation required.

Table of Contents

• Prerequisites
• Getting Started
• Exercises
  • SQL Fundamentals
  • Data Placement & Architecture
  • Scalability & High Availability
  • Multi-Region & Disaster Recovery
  • Data Protection & Recovery
  • Streaming & CDC
  • Observability
  • Security
  • Search & Extensions
  • Migration (YB Voyager)
  • Ecosystem (PG Runtime-Compatible)
  • External Resources
• Devcontainer Reference
• YugabyteDB Version

Prerequisites

Getting Started

1 · Fork and clone

# Fork via GitHub UI first, then:
git clone https://github.com/<your-github-username>/ybdb-vanguard.git
cd ybdb-vanguard

2 · Pick an exercise

./launch

The launch script presents a categorised menu with role tags and resource warnings. Pick a number — the script prints a Codespaces URL, a DevPod command, and a VS Code Dev Containers instruction for the selected exercise.

3 · Alternative launch paths

gh codespace create \
  --repo <your-github-username>/ybdb-vanguard \
  --devcontainer-path .devcontainer/init-dsql/devcontainer.json

devpod up . --devcontainer-path .devcontainer/init-dsql/devcontainer.json

Exercises

Role Badge Legend

Badge	Audience
	Developer
	Operations / DBA
	Site Reliability Engineer
	Architect

Quick Reference

SQL Fundamentals

#	Exercise	Description	Roles	Notes
1	Distributed SQL Universe	YSQL & YCQL basics, sharding, tablet distribution
2	Query Tuning Tips & Tricks	Index strategies, EXPLAIN ANALYZE, join optimisation
3	Query Plan Management (QPM)	Detect plan regressions, pin known-good query plans		v2025.2.3+

Data Placement & Architecture

#	Exercise	Description	Roles	Notes
4	Colocation & Distributed Tables	Co-locate small reference tables, keep others distributed
5	Tablespaces & Online Data Migration	Placement tablespaces and live ALTER SET TABLESPACE

Scalability & HA

#	Exercise	Description	Roles	Notes
6	Data Distribution and Scalability	Tablet auto-splitting and scale-out under live load		⚠ 8 CPU · 16 GB RAM
7	Fault Tolerance and High Availability	Kill nodes, verify Raft replication and zero data loss		⚠ 8 CPU · 16 GB RAM

Multi-Region & Disaster Recovery

#	Exercise	Description	Roles	Notes
8	Geo-distribution & Tablespaces	Multi-region placement, geo-partitioning, follower reads
9	xCluster Replication & Disaster Recovery	xCluster with auto DDL propagation and planned failover		⚠ 8 GB RAM

Data Protection & Recovery

#	Exercise	Description	Roles	Notes
10	Point-in-Time Recovery (PITR)	Online restore to any second within retention window
11	DB Clone/Branch — Instant Database Copies	Instant CREATE DATABASE … AS OF snapshot copy
12	Time Travel	Historical read timestamps for forensic queries

Streaming & CDC

#	Exercise	Description	Roles	Notes
13	Change Data Capture — YugabyteDB → PostgreSQL	YB Debezium connector → PostgreSQL via JDBC sink
14	Change Data Capture — SQL Server → YugabyteDB	SQL Server CDC captured into YugabyteDB via Debezium		⚠ 8 CPU · 16 GB RAM
15	Change Data Capture — MySQL → YugabyteDB	MySQL binlog captured into YugabyteDB via Debezium
16	Pub/Sub CDC — YugabyteDB → YugabyteDB	Embedded Debezium applier, no Kafka; atomic transaction apply		⚠ 8 CPU · 16 GB RAM
17	Two-way Pub/Sub CDC — YugabyteDB ⇄ YugabyteDB	Active-active: replication origins break the loop, timestamp LWW makes the sites converge		⚠ 8 CPU · 16 GB RAM

Observability

#	Exercise	Description	Roles	Notes
18	Observability & Performance Diagnosis	pg_stat_statements, ASH, pg_locks, query diagnostics
19	OpenSearch — Log Observability	OTel tails GLog + pgaudit logs into OpenSearch
20	Elasticsearch — Logs & Metrics Observability	OTel ships GLog + Prometheus metrics into Elasticsearch

Security

#	Exercise	Description	Roles	Notes
21	Encryption at Rest (EAR) + Key Rotation	yb-admin key rotation on live cluster, no restart
22	Row Level Security & Multi-tenancy	CREATE POLICY tenant isolation with session variables
23	Data Privacy — Column Encryption & Anonymization	pgcrypto column encryption, masking, anonymized views

Search & Extensions

#	Exercise	Description	Roles	Notes
24	Full-Text Search	tsvector/tsquery, ts_rank, ts_headline, ybgin index
25	Semantic Search with pgvector	L2/cosine/IP distance operators, ybhnsw ANN index

Migration (YB Voyager)

#	Exercise	Description	Roles	Notes
26	Data Migration — MySQL → YugabyteDB	Voyager: assess → export → import → finalize
27	Data Migration — MariaDB → YugabyteDB	Voyager offline migration from MariaDB
28	Data Migration — Oracle → YugabyteDB	Voyager offline migration from Oracle Free container		⚠ 8 CPU · 16 GB RAM · 64 GB disk
29	Live Data Migration — PostgreSQL → YugabyteDB	Voyager live migration with CDC stream and cutover

Ecosystem

#	Exercise	Description	Roles	Notes
30	Keycloak — Identity & Access Management	Keycloak backed by YugabyteDB via smart JDBC driver
31	Kong Gateway — API Gateway	Kong config stored in YugabyteDB via PostgreSQL protocol
32	WSO2 API Manager — Enterprise API Gateway	WSO2 APIM two-database setup with smart JDBC driver		⚠ 8 CPU · 16 GB RAM

SQL Fundamentals

Distributed SQL Universe

README → | devcontainer: init-dsql

Get started with YugabyteDB: hash vs range sharding, YSQL and YCQL basics, tablet distribution, and fault-tolerance fundamentals.

Query Tuning Tips & Tricks

README → | devcontainer: init-qt

Query execution patterns, pushdown operations, index strategies (hash, range, covering, partial, expression, hotspot-safe range), schema design rules (explicit sharding, FK indexes, low-cardinality and redundant indexes), join optimisation, advanced SQL, and programmability — all with EXPLAIN (ANALYZE, DIST).

Query Plan Management (QPM)

README → | devcontainer: init-qpm

Detect, compare, and pin query plans with QPM (EA, v2025.2.3+). Capture every plan a query has used in yb_pg_stat_plans, spot a regression after a statistics change, and pin a known-good plan via the pg_hint_plan hint table. The database is fully QPM-ready (extensions, defaults, and seed data) the moment the container starts.

Data Placement & Architecture

Colocation & Distributed Tables

README → | devcontainer: init-colocate

Co-locate small reference tables on a single shared tablet for local joins while keeping high-volume tables distributed. Covers CREATE DATABASE ... WITH COLOCATION = true, WITH (COLOCATION = false) opt-out, yb_table_properties(), and yb_is_database_colocated().

Tablespaces & Online Data Migration

README → | devcontainer: init-tablespace

Create placement-aware tablespaces with replica_placement JSON, pin tables and indexes to specific regions at creation time, and migrate data between tablespaces online with ALTER TABLE SET TABLESPACE — no cluster downtime. Covers ALTER INDEX SET TABLESPACE and SET default_tablespace.

Scalability & High Availability

Data Distribution and Scalability

README → | devcontainer: init-scale

Explore tablet-based data distribution, automatic tablet splitting, and horizontal scale-out on a 3-node cluster under yb-sample-apps load.

Note: Requires 8 CPUs · 16 GB RAM.

Fault Tolerance and High Availability

README → | devcontainer: init-ft

Chaos engineering on a 6-node cluster across 3 availability zones. Kill nodes, observe leader election and Raft re-replication, and verify zero data loss under continuous YSQL and YCQL load.

Note: Requires 8 CPUs · 16 GB RAM.

Multi-Region & Disaster Recovery

Geo-distribution & Tablespaces

README → | devcontainer: init-geo

Multi-region data placement and low-latency reads on a 3-node cluster simulating US East / EU West / AP South. Covers CREATE TABLESPACE with replica_placement JSON, region-pinned tables, row-level geo-partitioning, yb_is_local_table, preferred zone configuration, and follower reads.

xCluster Replication & Disaster Recovery

README → | devcontainer: init-xcluster

Set up transactional xCluster replication with automatic DDL propagation between two universes (v2025.2.1+). DDL runs only on the primary — the standby receives schema changes automatically. Covers create_xcluster_checkpoint, PITR on standby, setup_xcluster_replication, role verification (yb_xcluster_ddl_replication.get_replication_role()), lag monitoring, and planned failover — all with standalone yb-admin (no YugabyteDB Anywhere required).

Note: Requires 8 GB RAM (two single-node clusters running simultaneously).

Data Protection & Recovery

Point-in-Time Recovery (PITR)

README → | devcontainer: init-pitr

Create snapshot schedules, simulate accidental DELETE and DROP TABLE disasters, and restore the database online to any second within the retention window — without stopping the cluster. Bonus: SET yb_read_time time-travel queries as a forensics tool.

DB Clone — Instant Database Copies

README → | devcontainer: init-clone

Clone a live database with a single SQL statement: CREATE DATABASE clone TEMPLATE source [AS OF '<timestamp>']. Test migrations safely, create rollback baselines, and reproduce past states — production is never touched.

Time Travel — yb_read_time

README → | devcontainer: init-tt

Read historical snapshots of your data by setting a session-level read timestamp (SET yb_read_time TO <unix_microseconds>). Audit what changed, find deleted rows, run forensic investigations — the live database is never modified.

Streaming & CDC

Change Data Capture — YugabyteDB → PostgreSQL

README → | devcontainer: init-cdc-ybdb-pg

Stream changes from YugabyteDB to PostgreSQL using the YugabyteDB Debezium connector (yboutput logical replication plugin) and a JDBC sink connector. Guided demo: register connectors, snapshot, live INSERT/UPDATE/DELETE propagation.

Change Data Capture — SQL Server → YugabyteDB

README → | devcontainer: init-cdc-sqlserver-ybdb

Stream changes from Microsoft SQL Server to YugabyteDB using the Debezium SQL Server connector (capturing SQL Server’s native CDC change tables) and a Debezium JDBC sink connector. Guided demo: register connectors, snapshot, live INSERT/UPDATE/DELETE propagation.

Note: Requires 8 CPUs · 16 GB RAM.

Change Data Capture — MySQL → YugabyteDB

README → | devcontainer: init-cdc-mysql-ybdb

Stream changes from MySQL to YugabyteDB using the Debezium MySQL connector (reading MySQL’s row-based binary log as a replica) and a Debezium JDBC sink connector. Guided demo: register connectors, snapshot, live INSERT/UPDATE/DELETE propagation.

Pub/Sub CDC — YugabyteDB → YugabyteDB

README → | devcontainer: init-pubsub-ybdb

Replicate between two YugabyteDB clusters with no middleware at all. The publisher exposes changes through the PostgreSQL logical replication protocol (yboutput plugin); a Spring Boot applier embeds Debezium’s engine in its own process and writes each change to the subscriber — no Kafka, no Kafka Connect, no sink connector. Guided demo: initial snapshot, live INSERT/UPDATE/DELETE, atomic transaction apply (one source transaction → one sink transaction), and replication origins for loop prevention.

Note: Requires 8 CPUs · 16 GB RAM (two single-node clusters plus a JVM). The applier is cloned from srinivasa-vasu/spring-boot-cdc-stream and built at container create.

Two-way Pub/Sub CDC — YugabyteDB ⇄ YugabyteDB

README → | devcontainer: init-pubsub-bidir-ybdb

Active-active replication between two YugabyteDB sites, each simultaneously publisher and subscriber. Two applier lanes point at each other; replication origins stop a change circulating forever, and last-writer-wins on a timestamp column makes the two sites agree when both write the same row. Guided demo: pre-creating origins before the slots stream (get this wrong and the loop is unbroken with no error), measured proof that 12,000 echoed rows are discarded, a partitioned write on both sites that converges rather than diverges, the losing edits recorded in a queryable conflict log, and disjoint ownership as a routing rule — no per-row site column anywhere. The workshop additionally reproduces the divergence with resolution switched off, and runs a real replication loop with the filter off.

Builds on Pub/Sub CDC — YugabyteDB → YugabyteDB; start there for snapshots and transaction-boundary apply.

Note: Requires 8 CPUs · 16 GB RAM (two clusters plus two JVMs) and YugabyteDB 2025.2.1+ — replication origins are an Early Access feature.

CDC Streaming — YSQL → YCQL

Repository →

Spring Cloud Stream microservices-based CDC from YSQL to YCQL through a supplier-processor-consumer pattern (external repo).

Observability

Observability & Performance Diagnosis

README → | devcontainer: init-obs

End-to-end performance investigation using built-in YugabyteDB SQL views: pg_stat_statements (with docdb_rows_scanned, yb_latency_histogram, P99), Active Session History (yb_active_session_history) grouped by query / tablet / node / session, EXPLAIN (ANALYZE, DIST), pg_locks, yb_cancel_transaction(), and yb_query_diagnostics. No external agents or dashboards required.

OpenSearch — Log Observability with YugabyteDB

README → | devcontainer: init-opensearch

Ships YugabyteDB structured logs into OpenSearch using the OpenTelemetry Collector contrib binary. The OTel Collector runs as a process inside the devcontainer (not a sibling Docker container) so it can read YB GLog files directly from the filesystem. A filelog receiver tails master and tserver .INFO log files with GLog multiline parsing; a second filelog receiver captures PostgreSQL and pgaudit log entries. Logs flow to the yb-logs index. OpenSearch Dashboards provides search, index-pattern exploration, and visualization.

Elasticsearch — Logs & Metrics Observability with YugabyteDB

README → | devcontainer: init-elasticsearch

Ships YugabyteDB structured logs and Prometheus metrics into Elasticsearch using the OpenTelemetry Collector contrib binary. Two filelog receivers handle GLog (master/tserver) and PostgreSQL/pgaudit log files; a prometheus receiver scrapes four YB metrics endpoints (master :7000, tserver :9000, YSQL :13000, YCQL :12000) every 15 s. Logs flow to the yb-logs index; metrics flow to the yb-metrics index via the elasticsearch/metrics exporter with mapping.mode: none. Kibana provides index-pattern search and visualization for both signals.

Security

Encryption at Rest (EAR) + Key Rotation

README → | devcontainer: init-ear

Enable and rotate cluster-level encryption at rest on a live YugabyteDB node — no restart required. Covers openssl rand key generation, yb-admin add_universe_key_to_all_masters, rotate_universe_key_in_memory, is_encryption_enabled, and a full quarterly key-rotation workflow.

Row Level Security & Multi-tenancy

README → | devcontainer: init-rls

Database-enforced tenant isolation. Covers CREATE POLICY with USING and WITH CHECK, session-variable multi-tenancy (SET app.tenant_id, current_setting()), BYPASSRLS for admin roles, SECURITY DEFINER functions, partial index optimisation for RLS predicates, and schema-per-tenant comparison.

Data Privacy — Column Encryption & Anonymization

README → | devcontainer: init-privacy

PII protection at the column level using the pgcrypto extension. Covers pgp_sym_encrypt / pgp_sym_decrypt (AES via GnuPG), digest (SHA-256 for searchable hashes), hmac (tamper-evident audit logs), masking and pseudonymization patterns, anonymized views, and column-level key rotation.

Search & Extensions

Full-Text Search

README → | devcontainer: init-fts

SQL-native full-text search without an external search engine. Covers tsvector / tsquery, stemming and stop-word removal, boolean and phrase operators (to_tsquery, phraseto_tsquery, websearch_to_tsquery), relevance ranking (ts_rank), highlighted snippets (ts_headline), persisted tsvector columns, ybgin index for single-term fast lookups, and auto-update with tsvector_update_trigger.

Semantic Search with pgvector

README → | devcontainer: init-pgvector

Vector similarity search using the bundled pgvector extension. Covers all three distance operators (<-> L2, <=> cosine, <#> inner product), converting distance to similarity, vector magnitude (l2_norm) and normalization (l2_normalize), the normalization identity that makes inner product equivalent to cosine — enabling a vector_ip_ops index as the preferred choice for normalized embeddings, hybrid SQL + vector queries, and ybhnsw approximate nearest neighbor index with ef_search tuning.

Migration (YB Voyager)

Data Migration — MySQL → YugabyteDB

README → | devcontainer: init-voyager-mysql

Offline migration from MySQL to YugabyteDB using YB Voyager: assess → export schema → analyse → export data → import schema → import data → finalise.

Data Migration — MariaDB → YugabyteDB

README → | devcontainer: init-voyager-mariadb

Offline migration from MariaDB to YugabyteDB using YB Voyager.

Data Migration — Oracle → YugabyteDB

README → | devcontainer: init-voyager-oracle

Offline migration from Oracle Database to YugabyteDB using YB Voyager. Uses an Oracle Free container as the source.

Note: Requires 8 CPUs · 16 GB RAM · 64 GB disk — Oracle container is heavyweight.

Live Data Migration — PostgreSQL → YugabyteDB

README → | devcontainer: init-voyager-postgres

Live (online) migration from PostgreSQL to YugabyteDB with minimal downtime using YB Voyager — export + streaming CDC changes + cutover.

Ecosystem (PG Runtime-Compatible)

Keycloak — Identity & Access Management

README → | devcontainer: init-keycloak

YugabyteDB as Keycloak’s backend identity store using the YugabyteDB smart JDBC driver (com.yugabyte.Driver, jdbc:yugabytedb://). Drop in the YB JDBC JAR and set KC_DB_DRIVER + KC_DB_URL — Keycloak needs no other changes. Covers Liquibase schema migration (~90 tables), real-time YSQL queries alongside Keycloak Admin API calls, realm and user creation via REST, and connection load-balancing across YugabyteDB nodes.

Kong Gateway — API Gateway with YugabyteDB

README → | devcontainer: init-kong

Kong Gateway stores its entire configuration (services, routes, plugins, consumers) in YugabyteDB. This exercise connects Kong to YugabyteDB YSQL (port 5433) — Kong uses the standard PostgreSQL wire protocol and YugabyteDB speaks it back, requiring zero driver or code changes. Covers Kong bootstrap migrations, creating services and routes via the Admin API, proxying traffic, adding the rate-limiting plugin, and reading every config object back directly from YugabyteDB with YSQL.

WSO2 API Manager — Enterprise API Gateway with YugabyteDB

⚠ Requires 8 CPU · 16 GB RAM (WSO2 APIM JVM + YugabyteDB)

README → | devcontainer: init-wso2

WSO2 API Manager stores all platform state — API definitions, subscriptions, throttling policies, users, OAuth clients, and OIDC tokens — across two YugabyteDB databases. The standard PostgreSQL JDBC driver is replaced with the YugabyteDB smart JDBC driver (com.yugabyte.Driver, jdbc:yugabytedb://) in deployment.toml, adding built-in connection load-balancing with zero WSO2 code changes. Covers two-database architecture (wso2_amdb for API management, wso2_shareddb for Carbon/Identity), DDL bootstrap from WSO2’s shipped SQL scripts, the Publisher REST API, and direct YSQL queries against both databases.

External Resources

Resource	Roles	Description
Java Microservices		Spring Boot, Quarkus, and Micronaut integration patterns with YugabyteDB
Java Testcontainers		Testcontainers integration with YugabyteDB for integration testing
Securing Spring Boot Microservices		Secure a Spring Boot application with YugabyteDB over TLS using cloud-native secret management
CDC Streaming — YSQL → YCQL		Spring Cloud Stream microservices-based CDC from YSQL to YCQL

Devcontainer Reference

All exercises default to Ubuntu-based devcontainer image on linux/amd64 and linux/arm64.

YugabyteDB Version

This repo is pinned to YugabyteDB 2025.2.7.0-b107.