CockroachDB: The Definitive Guide: Guide: Distributed Data at Scale. 2 Ed

Preface xvii
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 17
CockroachDB at Netflix 17
CockroachDB at Devsisters 18
CockroachDB at DoorDash 18
CockroachDB at Bose 18
CockroachDB at Form3 19
CockroachDB at Hard Rock Digital 19
CockroachDB at Spreedly 19
CockroachDB at Route 20
Summary 20
2. CockroachDB Architecture 21
The CockroachDB Cluster Architecture 22
The CockroachDB Software Stack 24
The CockroachDB SQL Layer 25
From SQL to Key-Values 27
Tables as Represented in the KV Store 27
Column Families 28
Indexes in the KV Store 29
Inverted Indexes 30
The STORING Clause 30
Table Definitions and Schema Changes 31
The CockroachDB Transaction Layer 32
MVCC Principles 33
Transaction Workflow 34
Write Intents 35
Parallel Commits 36
Transaction Cleanup 37
Overview of Transaction Flow 37
Read/Write Conflicts 39
Clock Synchronization and Clock Skew 40
The CockroachDB Distribution Layer 41
Meta Ranges 41
Gossip 42
Leaseholders 42
Range Splits 43
Multiregion Distribution 44
The CockroachDB Replication Layer 45
Raft 45
Raft and Leaseholders 46
Closed Timestamps and Follower Reads 46
The CockroachDB Storage Layer 47
Log-Structured Merge Trees 47
SSTables and Bloom Filters 48
Deletes and Updates 49
Multiversion Concurrency Control 50
The Block Cache 50
Summary 50
3. Getting Started 53
Installation 53
Installing CockroachDB Software 53
Creating a CockroachDB Cloud Basic Instance 57
Starting a Local Single-Node Server 59
Starting Up CockroachDB in a Docker Container 60
Starting Up a Secure Server 62
Shutting Down the Server 63
Remote Connection 63
Creating a Kubernetes Cluster 64
Creating a Cluster with Terraform 66
Using a GUI Client 70
Exploring CockroachDB 71
Adding Some Data 71
Databases and Tables 73
Issuing SQL 74
The DB Console 75
Working with Programming Languages 75
Connecting to CockroachDB from Node.js 76
Connecting to CockroachDB from Java 77
Connecting to CockroachDB from Python 78
Connecting to CockroachDB from Go 79
Summary 81
4. CockroachDB SQL 83
SQL Language Compatibility 83
Querying Data with SELECT 84
The SELECT List 85
The FROM Clause 85
Joins 85
Anti-Joins 86
Cross Joins 87
Set Operations 87
Group Operations 88
Subqueries 88
Correlated Subquery 89
Lateral Subquery 89
The WHERE Clause 90
Common Table Expressions 90
ORDER BY 91
Window Functions 92
Other SELECT Clauses 93
CockroachDB Arrays 93
Working with JSON 95
Summary of SELECT 97
Creating Tables and Indexes 97
Column Definitions 99
Computed Columns 101
Data Types 101
Primary Keys 103
Constraints 103
Indexes 104
Vectors 107
CREATE TABLE AS SELECT 109
Altering Tables 109
Dropping Tables 110
Views 111
Functions 111
Procedures 114
Inserting Data 115
UPDATE 118
UPSERT 119
DELETE 120
TRUNCATE 122
IMPORT INTO 122
Transactional Statements 122
BEGIN Transaction 123
SAVEPOINT 124
COMMIT 124
ROLLBACK 124
SELECT FOR UPDATE 124
SELECT FOR SHARE 125
AS OF SYSTEM TIME 126
Other Data Definition Language Targets 127
Administrative Commands 128
The Information Schema 129
Summary 129
Part II. Developing Applications with CockroachDB
5. CockroachDB Schema Design 133
Logical Data Modeling 134
Normalization 135
Don’t Go Too Far 136
Primary Key Choices 137
Special-Purpose Designs 138
Physical Design 138
Entities to Tables 139
Attributes to Columns 140
Primary Key Design 141
Foreign Key Constraints 146
Denormalization 147
Replicating Columns to Avoid Joins 147
Summary Tables 149
Vertical Partitioning 149
Horizontal Partitioning 150
Repeating Groups 151
JSON Document Models 151
JSON Document Antipatterns 153
Indexing JSON Attributes 153
Using JSON or Arrays to Avoid Joins 154
Indexes 156
Index Selectivity 157
Index Break-Even Point 157
Index Overhead 159
Composite Indexes 160
Covering Indexes 161
Composite and Covering Index Performance 162
Guidelines for Composite Indexes 163
Indexes and Null Values 163
Inverted Indexes 163
Partial Indexes 164
Sort-Optimizing Indexes 164
Expression Indexes 165
Full-Text Indexes 167
Spatial Indexes 169
Hash-Sharded Indexes 170
Measuring Index Effectiveness 170
Summary 172
6. Application Design and Implementation 175
CockroachDB Programming 175
Performing CRUD Operations 176
Connection Pools 179
Prepared and Parameterized Statements 183
Batch Inserts 185
Pagination of Results 188
Projections 191
Client-Side Caching 192
Managing Transactions 194
Transaction Retry Errors 195
Implementing Transaction Retries 197
Automatic Transaction Retries 198
Using FOR UPDATE to Avoid Transaction Retry Errors 199
Reducing Contention by Eliminating Hot Rows 201
Reducing Transaction Elapsed Time 202
Reordering Statements 203
Time Travel Queries 203
Ambiguous Transactions Errors 204
Deadlocks 205
Transaction Priorities 205
Working with ORM Frameworks 206
Row-Level TTL 210
Summary 212
7. Application Migration and Integration 213
Migration Objectives 214
Database Consolidation 214
Failover Regions 215
Fragile Data Integrations 216
Unnecessary Caching Tier 217
Unnecessary Data Warehouse Workloads 218
Application Silos 218
Loading Data 218
File Locations 219
Importing from userfile Storage 220
Importing from Cloud Storage 222
Import Performance 223
Migrating from Another Database 224
Extracting and Converting DDL 224
General Considerations When Converting DDL 230
Exporting Data 231
Migrating Schemas to CockroachDB 232
Loading Data into CockroachDB with MOLT Fetch 233
Loading Data into CockroachDB with IMPORT INTO 235
Directly Importing PostgreSQL or MySQL Dumps 236
Synchronizing and Switching Over 237
Updating Application Code 241
Mainframe Migrations 242
Exporting CockroachDB Data 243
Change Data Capture 245
Core Change Data Capture 245
Using the Changefeed Programmatically 248
Enterprise Change Data Capture 249
CDC Queries 250
Change Data Capture to Kafka 256
Change Data Capture to Pulsar 257
Change Data Capture to Snowflake 259
Summary 264
8. SQL Tuning 265
Finding Slow SQL 265
Explaining and Tracing SQL 267
EXPLAIN ANALYZE 272
EXPLAIN Options 273
EXPLAIN DEBUG 276
Changing SQL Execution 278
Optimizing Table Lookups 279
Optimizing Joins 289
Join Methods 290
Optimizing Sorting and Aggregation 300
Disk Sorts 303
Optimizing DML 305
Optimizing the Optimizer 307
Optimizer Statistics 307
Viewing Statistics 307
Automatic Statistics 308
Manually Collecting Statistics 309
Summary 310
Part III. Deploying and Administering CockroachDB
9. Planning a Deployment 313
Know Your Requirements 314
Comparison of Deployment Options 314
CockroachDB Cloud Basic Deployments 317
CockroachDB Cloud Standard Deployments 319
Single-Region Advanced Deployments 319
Common Planning Tasks—Advanced Deployments 319
Benchmarking and Capacity Planning 320
CockroachDB Cloud Deployments 322
Self-Hosted on a Cloud Platform 324
Self-Hosted “Bare-Metal” On-Premises 325
Other Self-Hosted Considerations 327
Self-Hosted Kubernetes 327
Configuring for Self-Hosted High Availability 329
Disk Failure 329
Node Failures 329
Network Failure 330
Zone and Region Topologies 331
Summary 332
10. Single-Region Deployment 335
Deploying On-Premises or On-Cloud 335
Firewall Configuration 336
Operating System Configuration 336
Clock Synchronization On-Premises 337
Clock Synchronization on Cloud Platforms 338
Creating Certificates 340
Configuring the Nodes 342
Creating a Ballast File 343
Initializing the Cluster 344
Creating the First User 345
Installing a Load Balancer (On-Premises) 347
Cloud Load Balancers 349
Configuring Regions and Zones 350
Deploying on Kubernetes 350
Initializing the Operator 351
Initializing the Cluster 352
Creating a Client Pod 354
Load Balancing 354
Other Kubernetes Tasks 356
Summary 356
11. Multiregion Deployment 359
Multiregion Concepts 359
Regions and Zones 359
Survival Goals 361
Locality Rules 363
Planning Your Multiregion Deployment 365
Deploying in Multiregion 365
Converting to a Multiregion Database 368
Configuring Regional by Row 369
Setting Regional Survival Goal 372
Super Regions 373
Summary 374
12. Backup and Disaster Recovery 375
Backups 376
The BACKUP Command 377
Backup Destinations 378
Full Backup 379
Table- and Database-Level Backups 379
Incremental Backups 380
AS OF SYSTEM TIME Backup 380
Encrypted Backups 381
WITH REVISION HISTORY 381
SHOW BACKUP 382
Managing Backup Jobs 383
Scheduling Backups 384
Locality-Aware Backups 387
Restoring Data 387
Backup Validation 390
Exporting Data 391
Disaster Recovery Best Practices 392
Backup Scheduling and Configuration 392
Recovering from Human Errors 393
Two–Data Center Replication 394
Physical Cluster Replication 394
Logical Data Replication 398
Summary 402
13. Security 403
Firewall Configuration 404
IP Allowlist with CockroachDB Cloud 404
Egress Perimeter Controls 405
Private Connectivity and VPC Peering 406
Native Linux Firewall 410
Configuring a Firewall in GCP 412
Configuring a Firewall in AWS 413
Configuring Ports for Microsoft Azure 414
Encryption and Server Certificates 415
Encryption at Rest 416
Customer-Managed Encryption Keys 418
Authentication Mechanisms 423
Standard Authentication 423
Advanced Authentication 424
Authorization 424
Managing Users 425
Managing Privileges 425
Fine-Grained Access Control with Views 427
Logging and Auditing 428
Security Best Practices 431
Summary 432
14. Administration and Troubleshooting 433
Monitoring 433
CockroachDB Cloud Advanced Alerts 434
CockroachDB Cloud Standard Alerts 435
CockroachDB Cloud Basic Alerts 435
Availability Monitoring 436
The Cloud API 437
The Cluster API 441
Monitoring and Alerting with Prometheus 443
The Metrics Export Endpoint 444
Monitoring and Alerting with Datadog 445
Log Configuration 446
Log Channels 449
Log Format 450
Filter Levels 451
Log Destinations 452
Logging to Fluentd 452
Redaction 453
Logs in Cloud Deployments 453
Cluster Management 454
Upgrading the Cluster Version 454
Adding Nodes to a Cluster 456
Decommissioning Nodes 457
Troubleshooting 460
Clock Synchronization Errors 461
Node Liveness 461
Networking Issues 462
Loss of Client Connectivity 464
Running Out of Disk Space 464
Working with CockroachDB Support Resources 464
Summary 465
15. Cluster Optimization 467
Tuning Versus Firefighting 467
Workload Optimization 469
Detecting Problem Workloads 469
Review of Workload Optimization Strategies 472
Indexing 472
Ad Hoc or Analytic Queries 473
Cluster Balance 475
Causes of Imbalance 476
Admission Control 485
Network 486
Memory Optimization 488
Key-Value Cache 489
max-sql-memory 490
Host Memory 491
Disk I/O 491
Scaling Out 494
Summary 494
Index 497

 CockroachDB is the distributed SQL database that handles the demands of today's data-driven applications. The second edition of this popular hands-on guide shows software developers, architects, and DevOps/SRE teams how to use CockroachDB for applications that scale elastically and provide seamless delivery for end users while remaining indestructible.

 Data professionals will learn how to migrate existing applications to CockroachDB's performant, cloud-native data architecture. You'll also quickly discover the benefits of strong data correctness and consistency guarantees, plus optimizations for delivering ultra-low latencies to globally distributed end users.

• Uncover the power of distributed SQL

• Learn how to start, manage, and optimize projects in CockroachDB

• Explore best practices for data modeling, schema design, and distributed infrastructure

• Discover strategies for migrating data into CockroachDB

• See how to read, write, and run ACID transactions across distributed systems

• Maximize resiliency in multiregion clusters

• Secure, monitor, and fine-tune your CockroachDB deployment for peak performance