Bank of America CockroachDB Comprehensive Training Day - Charlotte

You're invited to join CockroachDB for a Comprehensive Training Day.

This Training bridges the gap between high-level distributed systems theory and hands-on SQL optimization. Participants will learn how to leverage CockroachDB's architecture to build resilient, high-performance global applications.

Agenda:
Section 1: Architectural Review & "Plumbing" Kickoff

Goal: Master the underlying "plumbing" that enables CockroachDB to act as a single, consistent global database.

• 1.1 The SQL & Transaction Layers
  Gateway Responsibility: Any node can receive a request, parse the PostgreSQL wire protocol, and act as the "Transaction Coordinator."
  Write Intents: Before a commit, CockroachDB writes "intents" (provisional locks) to the KV store. These are flipped to "committed" once the transaction record is updated.

• 1.2 Distribution & Replication (Raft & Ranges)
  Ranges as the Unit of Scale: Data is divided into 512MB "Ranges." Each range is a Raft group replicated across multiple nodes (default 3 or 5).
  Leaseholders: Every range has one "Leaseholder" that coordinates all reads and proposes writes to the Raft group, ensuring consistency without a central bottleneck.

• 1.3 Time & Concurrency (HLC + MVCC)
  Hybrid Logical Clocks (HLC): CockroachDB uses HLCs to order events across nodes without requiring atomic clocks, combining physical time with logical counters.
  Multi-Version Concurrency Control (MVCC): Values are versioned by timestamp, allowing readers to view a consistent snapshot without blocking writers.

Section 2: Developer Focus & Implementation Patterns

Goal: Shift from traditional procedural database logic to declarative, set-based patterns that optimize for distributed throughput.

• 2.1 The "Short-Lived" Mandate
  Principle: Keep transactions tight. Do not include remote calls (HTTP/RPC), complex loops, or user-wait time inside a database transaction.
  Best Practice: Perform external API calls after the database commit to avoid holding locks and increasing contention.

• 2.2 Folding Logic into SQL (CTE Workflows)
  The Pattern: Replace application-side if/else checks with WITH clauses (CTEs) and EXISTS predicates.
  Impact: Collapsing multiple round-trips into a single statement reduces the contention window, often increasing throughput by over 75% and eliminating client-visible retries.

• 2.3 Set-Based Batching
  The "Unnest" Model: Instead of N individual inserts, build typed arrays in memory and use INSERT ... SELECT unnest(?).
  Best Practice: Chunk large sets (e.g., 256–1024 rows) at the application layer to keep individual Raft proposals manageable.

• 2.4 Multi-Region (MR) Mastery Regional by Row (RBR): Use computed columns to home data in specific regions (e.g., crdb_region) while maintaining global uniqueness on a primary key like idempotency_key.
  Survival Goals: Configure SURVIVE REGION FAILURE to ensure availability even if an entire cloud region goes offline.

Section 3: Demo & Hands-on Lab

• Demo 1: Optimized Loading: Seeding 10,000+ accounts using multi-row UPSERT vs. single-row inserts.

• Demo 2: Side-by-Side Performance: Running run_side_by_side_hot.sh to visualize p99 latency deltas between Explicit and CTE models.

• Demo 3: MR Setup: Using cockroach demo --global to observe how non-home gateway reads pay cross-region latency while home-region reads stay sub-10ms.

Section 4: Final Knowledge Assessment (20 Questions)