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

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