دانلود کتاب CockroachDB The Definitive Guide Distributed Data at Scale

فهرست مطالب :

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