CockroachDB The Definitive Guide Distributed Data at Scale

487 Pages·2022·15.396 MB·English

by  Guy Harrison, Jesse Seldess & Ben Darnell

CockroachDB
The Definitive Guide
Distributed Data at Scale
Guy Harrison,
Jesse Seldess & Ben Darnell
CockroachDB: The Definitive Guide
Get the lowdown on CockroachDB, the distributed SQL
database built to handle the demands of today’s data- “DoorDash has been able
driven cloud applications. In this hands-on guide, software to use CockroachDB to
developers, architects, and DevOps/SRE teams will learn forklift-migrate and scale
how to use CockroachDB to create applications that scale
numerous workloads
elastically and provide seamless delivery for end users while
without having to rewrite
remaining indestructible. Teams will also learn how to migrate
applications—only small
existing applications to CockroachDB’s performant, cloud
index or schema changes.
native data architecture.
Engineers looking to
If you’re familiar with distributed systems, you’ll quickly
build and run scalable
discover the benefits of strong data correctness and
applications using
consistency guarantees as well as optimizations for delivering
CockroachDB would do
ultralow latencies to globally distributed end users.
well to read this book.”
You’ll learn how to:
—Sean Chittenden
• Design and build applications for distributed infrastructure, Core Infrastructure, DoorDash
including data modeling and schema design
“Distributed SQL systems
• Migrate data into CockroachDB
bring together the best
• Read and write data and run ACID transactions across ideas of databases from
distributed infrastructure
the last 50 years: the
• Plan a CockroachDB deployment for resiliency across single- relational model, strong
and multi-region clusters consistency guarantees,
• Secure, monitor, and optimize your CockroachDB deployment and elastic scalability.
This trinity is what modern
Guy Harrison is a software professional with decades of experience
applications need to stay
in database design, development, administration, and optimization.
VP of Education at Cockroach Labs Jesse Seldess leads the in the game.”
CockroachDB documentation and training teams. And, as Cofounder
—Andy Pavlo
and Chief Architect at Cockroach Labs, Ben Darnell built the
Associate Professor of Databaseology,
distributed consensus protocols that underpin CockroachDB’s
Carnegie Mellon University
transactional model.
DATA Twitter: @oreillymedia
linkedin.com/company/oreilly-media
US $69.99 CAN $87.99 youtube.com/oreillymedia
IISSBBNN:: 997788--11--009988--1100002245--74
Build what
you dream.
Never worry about your database again.
/* Give all of your apps effortless scale, bulletproof resilience,
and low latency performance for users everywhere. */
A hassle-free Elastic scale that won’t Compatible with
cloud SQL database break your budget PostgreSQL
Get started for free, instantly.
cockroachlabs.com/product
Trusted by innovators
Praise for CockroachDB: The Definitive Guide
DoorDash has been able to use CockroachDB to forklift-migrate and scale numerous
workloads without having to rewrite applications—only small index or schema changes.
This definitive resource is for both new engineers and seasoned veterans who want to
learn CockroachDB’s internals, deployment, and capabilities. Engineers looking to build
and run scalable applications using CockroachDB would do well to read this book.
—Sean Chittenden, Core Infrastructure, DoorDash
Modern distributed SQL systems bring together the best ideas of databases from the
last 50 years: the relational model, strong consistency guarantees, and elastic scalability.
This trinity is what modern applications need to stay in the game.
—Andy Pavlo, Associate Professor of Databases, Carnegie-
Mellon University; Cofounder, OtterTune
Distributed SQL databases combine the benefits of the relational database model with
native support for distributed cloud architecture. CockroachDB pairs the transactional
consistency of standard SQL with elastic scalability, multiregion and multicloud
deployment, and high levels of fault tolerance and availability. Developers thinking about
their next-generation enterprise application requirements will find this guide very timely.
—Matt Aslett, VP & Research Director, Ventana Research
We know that sometimes (stuff) happens. And, at scale, (stuff) is always happening.
—Spencer Kimball, Cofounder, Cockroach Labs
(as mentioned in the CockroachDB: The Definitive Guide preface)
CockroachDB: The Definitive Guide
Distributed Data at Scale
Guy Harrison, Jesse Seldess, and Ben Darnell
BBeeiijjiinngg BBoossttoonn FFaarrnnhhaamm SSeebbaassttooppooll TTookkyyoo
CockroachDB: The Definitive Guide
by Guy Harrison, Jesse Seldess, and Ben Darnell
Copyright © 2022 O’Reilly Media, Inc. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional
sales department: 800-998-9938 or [e-mail].
Acquisitions Editor: Andy Kwan Indexer: nSight, Inc.
Development Editor: Angela Rufino Interior Designer: David Futato
Production Editor: Christopher Faucher Cover Designer: Karen Montgomery
Copyeditor: Stephanie English Illustrator: Kate Dullea
Proofreader: Kim Cofer
April 2022: First Edition
Revision History for the First Edition
2022-04-08: First Release
See http://oreilly.com/catalog/errata.csp?isbn=9781098100247 for release details.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. CockroachDB: The Definitive Guide, the
cover image, and related trade dress are trademarks of O’Reilly Media, Inc.
The views expressed in this work are those of the authors and do not represent the publisher’s views.
While the publisher and the authors have used good faith efforts to ensure that the information and
instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility
for errors or omissions, including without limitation responsibility for damages resulting from the use
of or reliance on this work. Use of the information and instructions contained in this work is at your
own risk. If any code samples or other technology this work contains or describes is subject to open
source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use
thereof complies with such licenses and/or rights.
This work is part of a collaboration between O’Reilly and Cockroach Labs. See our statement of editorial
independence.
978-1-098-10025-4
[LSI]
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Part I. Introduction to CockroachDB
1. Introduction to CockroachDB. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
A Brief History of Databases 3
Pre-Relational Databases 4
The Relational Model 5
Implementing the Relational Model 7
Transactions 7
The SQL Language 8
The RDBMS Hegemony 8
Enter the Internet 9
The NoSQL Movement 11
The Emergence of Distributed SQL 11
The Advent of CockroachDB 13
CockroachDB Design Goals 14
CockroachDB Releases 16
CockroachDB in Action 16
CockroachDB at DevSisters 17
CockroachDB at DoorDash 17
CockroachDB at Bose 17
Summary 18
2. CockroachDB Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
The CockroachDB Cluster Architecture 20
Ranges and Replicas 21
v
The CockroachDB Software Stack 22
The CockroachDB SQL Layer 23
From SQL to Key-Values 25
Tables as Represented in the KV Store 25
Column Families 26
Indexes in the KV Store 27
Inverted Indexes 28
The STORING Clause 28
Table Definitions and Schema Changes 29
The CockroachDB Transaction Layer 30
MVCC Principles 31
Transaction Workflow 32
Write Intents 33
Parallel Commits 34
Transaction Cleanup 35
Overview of Transaction Flow 35
Read/Write Conflicts 37
Clock Synchronization and Clock Skew 38
The CockroachDB Distribution Layer 39
Meta Ranges 39
Gossip 40
Leaseholders 40
Range Splits 41
Multiregion Distribution 42
The CockroachDB Replication Layer 43
Raft 43
Raft and Leaseholders 44
Closed Timestamps and Follower Reads 44
The CockroachDB Storage Layer 45
Log-Structured Merge Trees 45
SSTables and Bloom Filters 47
Deletes and Updates 48
MultiVersion Concurrency Control 48
The Block Cache 48
Summary 49
3. Getting Started. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Installation 51
Installing CockroachDB Software 51
Creating a CockroachDB Serverless Instance 55
Starting a Local Single-Node Server 59
Starting Up CockroachDB in a Docker Container 61
vi | Table of Contents
Starting Up a Secure Server 62
Shutting Down the Server 63
Remote Connection 64
Creating a Kubernetes Cluster 64
Using a GUI Client 66
Exploring CockroachDB 67
Adding Some Data 67
Databases and Tables 70
Issuing SQL 71
The DB Console 72
Working with Programming Languages 72
Connecting to CockroachDB from Node.js 73
Connecting to CockroachDB from Java 74
Connecting to CockroachDB from Python 75
Connecting to CockroachDB from Go 77
Summary 78
4. CockroachDB SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
SQL Language Compatibility 80
Querying Data with SELECT 80
The SELECT List 81
The FROM Clause 81
JOINS 82
Anti-Joins 83
Cross Joins 83
Set Operations 83
Group Operations 84
Subqueries 85
Correlated Subquery 85
Lateral Subquery 85
The WHERE Clause 86
Common Table Expressions 86
ORDER BY 87
Window Functions 88
Other SELECT Clauses 89
CockroachDB Arrays 89
Working with JSON 91
Summary of SELECT 93
Creating Tables and Indexes 93
Column Definitions 95
Computed Columns 95
Data Types 96
Table of Contents | vii
Primary Keys 97
Constraints 98
Indexes 98
CREATE TABLE AS SELECT 101
Altering Tables 101
Dropping Tables 103
Views 103
Inserting Data 104
UPDATE 106
UPSERT 107
DELETE 108
TRUNCATE 108
IMPORT/IMPORT INTO 108
Transactional Statements 109
BEGIN Transaction 109
SAVEPOINT 110
COMMIT 110
ROLLBACK 110
SELECT FOR UPDATE 111
AS OF SYSTEM TIME 112
Other Data Definition Language Targets 112
Administrative Commands 113
The Information Schema 114
Summary 115
Part II. Developing Applications with CockroachDB
5. CockroachDB Schema Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Logical Data Modeling 120
Normalization 121
Don’t Go Too Far 122
Primary Key Choices 123
Special-Purpose Designs 124
Physical Design 124
Entities to Tables 125
Attributes to Columns 126
Primary Key Design 127
Foreign Key Constraints 132
Denormalization 133
Replicating Columns to Avoid Joins 133
Summary Tables 135
viii | Table of Contents