CockroachDB

Cover
Cockroach Labs
Copyright
Table of Contents
Preface
	Why Cockroach?
	Building CockroachDB
	Next Steps
	Why We Wrote This Book
	Who This Book Is For
	How This Book Is Organized
	Conventions Used in This Book
	Using Code Examples
	O’Reilly Online Learning
	How to Contact Us
	Acknowledgments
Part I. Introduction to CockroachDB
	Chapter 1. Introduction to CockroachDB
		A Brief History of Databases
			Pre-Relational Databases
			The Relational Model
			Implementing the Relational Model
			Transactions
			The SQL Language
			The RDBMS Hegemony
			Enter the Internet
			The NoSQL Movement
			The Emergence of Distributed SQL
		The Advent of CockroachDB
			CockroachDB Design Goals
			CockroachDB Releases
		CockroachDB in Action
			CockroachDB at DevSisters
			CockroachDB at DoorDash
			CockroachDB at Bose
		Summary
	Chapter 2. CockroachDB Architecture
		The CockroachDB Cluster Architecture
			Ranges and Replicas
		The CockroachDB Software Stack
		The CockroachDB SQL Layer
		From SQL to Key-Values
			Tables as Represented in the KV Store
			Column Families
			Indexes in the KV Store
			Inverted Indexes
			The STORING Clause
			Table Definitions and Schema Changes
		The CockroachDB Transaction Layer
			MVCC Principles
			Transaction Workflow
			Write Intents
			Parallel Commits
			Transaction Cleanup
			Overview of Transaction Flow
			Read/Write Conflicts
			Clock Synchronization and Clock Skew
		The CockroachDB Distribution Layer
			Meta Ranges
			Gossip
			Leaseholders
			Range Splits
			Multiregion Distribution
		The CockroachDB Replication Layer
			Raft
			Raft and Leaseholders
			Closed Timestamps and Follower Reads
		The CockroachDB Storage Layer
			Log-Structured Merge Trees
			SSTables and Bloom Filters
			Deletes and Updates
			MultiVersion Concurrency Control
			The Block Cache
		Summary
	Chapter 3. Getting Started
		Installation
			Installing CockroachDB Software
			Creating a CockroachDB Serverless Instance
			Starting a Local Single-Node Server
			Starting Up CockroachDB in a Docker Container
			Starting Up a Secure Server
			Shutting Down the Server
			Remote Connection
			Creating a Kubernetes Cluster
		Using a GUI Client
		Exploring CockroachDB
			Adding Some Data
			Databases and Tables
			Issuing SQL
			The DB Console
		Working with Programming Languages
			Connecting to CockroachDB from Node.js
			Connecting to CockroachDB from Java
			Connecting to CockroachDB from Python
			Connecting to CockroachDB from Go
		Summary
	Chapter 4. CockroachDB SQL
		SQL Language Compatibility
		Querying Data with SELECT
			The SELECT List
			The FROM Clause
			JOINS
			Anti-Joins
			Cross Joins
			Set Operations
			Group Operations
			Subqueries
			Correlated Subquery
			Lateral Subquery
			The WHERE Clause
			Common Table Expressions
			ORDER BY
			Window Functions
			Other SELECT Clauses
			CockroachDB Arrays
			Working with JSON
			Summary of SELECT
		Creating Tables and Indexes
			Column Definitions
			Computed Columns
			Data Types
			Primary Keys
			Constraints
			Indexes
			CREATE TABLE AS SELECT
			Altering Tables
			Dropping Tables
			Views
		Inserting Data
		UPDATE
		UPSERT
		DELETE
		TRUNCATE
		IMPORT/IMPORT INTO
		Transactional Statements
			BEGIN Transaction
			SAVEPOINT
			COMMIT
			ROLLBACK
			SELECT FOR UPDATE
			AS OF SYSTEM TIME
		Other Data Definition Language Targets
		Administrative Commands
		The Information Schema
		Summary
Part II. Developing Applications with CockroachDB
	Chapter 5. CockroachDB Schema Design
		Logical Data Modeling
			Normalization
			Don’t Go Too Far
			Primary Key Choices
			Special-Purpose Designs
		Physical Design
			Entities to Tables
			Attributes to Columns
			Primary Key Design
			Foreign Key Constraints
		Denormalization
			Replicating Columns to Avoid Joins
			Summary Tables
			Vertical Partitioning
			Horizontal Partitioning
			Repeating Groups
		JSON Document Models
			JSON Document Antipatterns
			Indexing JSON Attributes
			Using JSON or Arrays to Avoid Joins
		Indexes
			Index Selectivity
			Index Break-Even Point
			Index Overhead
			Composite Indexes
			Covering Indexes
			Composite and Covering Index Performance
			Guidelines for Composite Indexes
			Indexes and Null Values
			Inverted Indexes
			Partial Indexes
			Sort-Optimizing Indexes
			Expression Indexes
			Spatial Indexes
			Hash-Sharded Indexes
			Measuring Index Effectiveness
		Summary
	Chapter 6. Application Design and Implementation
		CockroachDB Programming
			Performing CRUD Operations
			Connection Pools
			Prepared and Parameterized Statements
			Batch Inserts
			Pagination of Results
			Projections
			Client-Side Caching
		Managing Transactions
			Transaction Retry Errors
			Implementing Transaction Retries
			Automatic Transaction Retries
			Avoiding Transaction Retry Errors with FOR UPDATE
			Reducing Contention by Eliminating Hot Rows
			Reducing Transaction Elapsed Time
			Reordering Statements
			Time Travel Queries
			Ambiguous Transactions Errors
			Deadlocks
			Transaction Priorities
		Working with ORM Frameworks
		Summary
	Chapter 7. Application Migration and Integration
		Loading Data
			File Locations
			Importing Files
			Importing from Cloud Storage
			Import Performance
		Migrating from Another Database
			Extracting and Converting DDL
			General Considerations When Converting DDL
			Exporting Data
			Loading Data Into CockroachDB
			Directly Importing PostgreSQL or MySQL Dumps
			Synchronizing and Switching Over
			Updating Application Code
		Exporting CockroachDB Data
		Change Data Capture
			Core Change Data Capture
			Using the Changefeed Programmatically
			Enterprise Change Data Capture
			Change Data Capture to Kafka
			Change Data Capture to Snowflake
		Summary
	Chapter 8. SQL Tuning
		Finding Slow SQL
		Explaining and Tracing SQL
			EXPLAIN ANALYZE
			EXPLAIN Options
			EXPLAIN DEBUG
		Changing SQL Execution
			Optimizing Table Lookups
			Optimizing Joins
			Join Methods
			Optimizing Sorting and Aggregation
			Optimizing DML
		Optimizing the Optimizer
			Optimizer Statistics
			Viewing Statistics
			Automatic Statistics
			Manually Collecting Statistics
		Summary
Part III. Deploying and Administering CockroachDB
	Chapter 9. Planning a Deployment
		Know Your Requirements
		Comparison of Deployment Options
		Serverless Deployments
		Single-Region Dedicated Deployments
			Common Planning Tasks—Dedicated Deployments
			Benchmarking and Capacity Planning
			CockroachDB Cloud Deployments
			Self-Hosted on a Cloud Platform
			Self-Hosted “Bare-Metal” On-Premise
			Other Self-Hosted Considerations
			Self-Hosted Kubernetes
		Configuring for Self-Hosted High Availability
			Disk Failure
			Node Failures
			Network Failure
			Zone and Region Topologies
		Summary
	Chapter 10. Single-Region Deployment
		Deploying On-Premise or On-Cloud
			Firewall Configuration
			Operating System Configuration
			Clock Synchronization On-Premise
			Clock Synchronization on Cloud Platforms
			Creating Certificates
			Configuring the Nodes
			Creating a Ballast File
			Initializing the Cluster
			Creating the First User
			Installing a Load Balancer (On-Premise)
			Cloud Load Balancers
			Configuring Regions and Zones
		Deploying on Kubernetes
			Initializing the Operator
			Initializing the Cluster
			Creating a Client Pod
			Load Balancing
			Other Kubernetes Tasks
		Summary
	Chapter 11. Multiregion Deployment
		Multiregion Concepts
			Regions and Zones
			Survival Goals
			Locality Rules
			Planning Your Mutliregion Deployment
		Deploying in Multiregion
			Converting to a Multiregion Database
			Configuring Regional by Row
			Setting Regional Survival Goal
			Placement Restricted Databases
		Summary
	Chapter 12. Backup and Disaster Recovery
		Backups
			The BACKUP Command
			Backup Destinations
			Full Backup
			Table- and Database-Level Backups
			Incremental Backups
			AS OF SYSTEM TIME Backup
			WITH REVISION HISTORY
			SHOW BACKUP
			Managing Backup Jobs
			Scheduling Backups
			Locality-Aware Backups
		Restoring Data
		Exporting Data
		Disaster Recovery Best Practices
			Backup Scheduling and Configuration
			Recovering from Human Errors
		Summary
	Chapter 13. Security
		Firewall Configuration
			IP Allowlist with CockroachDB Dedicated
			VPC Peering and PrivateLink with CockroachDB Dedicated
			Native Linux Firewall
			Configuring a Firewall in GCP
			Configuring a Firewall in AWS
			Configuring Ports for Microsoft Azure
		Encryption and Server Certificates
		Encryption at Rest
		Authentication Mechanisms
			Standard Authentication
			Advanced Authentication
		Authorization
			Managing Users
			Managing Privileges
			Fine-Grained Access Control with Views
		Logging and Auditing
		Security Best Practices
		Summary
	Chapter 14. Administration and Troubleshooting
		Monitoring
			CockroachDB Dedicated Alerts
			CockroachDB Serverless Alerts
			Availability Monitoring
			The Cluster API
			Monitoring and Alerting with Prometheus
			Monitoring and Alerting with Datadog
		Log Configuration
			Log Channels
			Log Format
			Filter Levels
			Log Destinations
			Logging to Fluentd
			Redaction
			Logs in Cloud Deployments
		Cluster Management
			Upgrading the Cluster Version
			Adding Nodes to a Cluster
			Decommissioning Nodes
		Troubleshooting
			Clock Synchronization Errors
			Node Liveness
			Networking Issues
			Loss of Client Connectivity
			Running Out of Disk Space
			Working with CockroachDB Support Resources
		Summary
	Chapter 15. Cluster Optimization
		Tuning Versus Firefighting
		Workload Optimization
			Detecting Problem Workloads
			Review of Workload Optimization Strategies
			Ad Hoc or Analytic Queries
		Cluster Balance
			Causes of Imbalance
			Hot Ranges
			Load Balancing
			Changes in Cluster Topology
			Admission Control
		Network
		Memory Optimization
		Key-Value Cache
			max-sql-memory
			Host Memory
		Disk I/O
		Scaling Out
		Summary
Index
About the Authors
Colophon