book

CockroachDB: The Definitive Guide

by Guy Harrison, Jesse Seldess, Ben Darnell

April 2022

Intermediate to advanced

485 pages

12h 4m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Why Cockroach?Building CockroachDBNext StepsWhy We Wrote This BookWho This Book Is ForHow This Book Is OrganizedConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
A Brief History of DatabasesPre-Relational DatabasesThe Relational ModelImplementing the Relational ModelTransactionsThe SQL LanguageThe RDBMS HegemonyEnter the InternetThe NoSQL MovementThe Emergence of Distributed SQLThe Advent of CockroachDBCockroachDB Design GoalsCockroachDB ReleasesCockroachDB in ActionCockroachDB at DevSistersCockroachDB at DoorDashCockroachDB at BoseSummary
The CockroachDB Cluster ArchitectureRanges and ReplicasThe CockroachDB Software StackThe CockroachDB SQL LayerFrom SQL to Key-ValuesTables as Represented in the KV StoreColumn FamiliesIndexes in the KV StoreInverted IndexesThe STORING ClauseTable Definitions and Schema ChangesThe CockroachDB Transaction LayerMVCC PrinciplesTransaction WorkflowWrite IntentsParallel CommitsTransaction CleanupOverview of Transaction FlowRead/Write ConflictsClock Synchronization and Clock SkewThe CockroachDB Distribution LayerMeta RangesGossipLeaseholdersRange SplitsMultiregion DistributionThe CockroachDB Replication LayerRaftRaft and LeaseholdersClosed Timestamps and Follower ReadsThe CockroachDB Storage LayerLog-Structured Merge TreesSSTables and Bloom FiltersDeletes and UpdatesMultiVersion Concurrency ControlThe Block CacheSummary
InstallationInstalling CockroachDB SoftwareCreating a CockroachDB Serverless InstanceStarting a Local Single-Node ServerStarting Up CockroachDB in a Docker ContainerStarting Up a Secure ServerShutting Down the ServerRemote ConnectionCreating a Kubernetes ClusterUsing a GUI ClientExploring CockroachDBAdding Some DataDatabases and TablesIssuing SQLThe DB ConsoleWorking with Programming LanguagesConnecting to CockroachDB from Node.jsConnecting to CockroachDB from JavaConnecting to CockroachDB from PythonConnecting to CockroachDB from GoSummary
SQL Language CompatibilityQuerying Data with SELECTThe SELECT ListThe FROM ClauseJOINSAnti-JoinsCross JoinsSet OperationsGroup OperationsSubqueriesCorrelated SubqueryLateral SubqueryThe WHERE ClauseCommon Table ExpressionsORDER BYWindow FunctionsOther SELECT ClausesCockroachDB ArraysWorking with JSONSummary of SELECTCreating Tables and IndexesColumn DefinitionsComputed ColumnsData TypesPrimary KeysConstraintsIndexesCREATE TABLE AS SELECTAltering TablesDropping TablesViewsInserting DataUPDATEUPSERTDELETETRUNCATEIMPORT/IMPORT INTOTransactional StatementsBEGIN TransactionSAVEPOINTCOMMITROLLBACKSELECT FOR UPDATEAS OF SYSTEM TIMEOther Data Definition Language TargetsAdministrative CommandsThe Information SchemaSummary
Logical Data ModelingNormalizationDon’t Go Too FarPrimary Key ChoicesSpecial-Purpose DesignsPhysical DesignEntities to TablesAttributes to ColumnsPrimary Key DesignForeign Key ConstraintsDenormalizationReplicating Columns to Avoid JoinsSummary TablesVertical PartitioningHorizontal PartitioningRepeating GroupsJSON Document ModelsJSON Document AntipatternsIndexing JSON AttributesUsing JSON or Arrays to Avoid JoinsIndexesIndex SelectivityIndex Break-Even PointIndex OverheadComposite IndexesCovering IndexesComposite and Covering Index PerformanceGuidelines for Composite IndexesIndexes and Null ValuesInverted IndexesPartial IndexesSort-Optimizing IndexesExpression IndexesSpatial IndexesHash-Sharded IndexesMeasuring Index EffectivenessSummary
CockroachDB ProgrammingPerforming CRUD OperationsConnection PoolsPrepared and Parameterized StatementsBatch InsertsPagination of ResultsProjectionsClient-Side CachingManaging TransactionsTransaction Retry ErrorsImplementing Transaction RetriesAutomatic Transaction RetriesAvoiding Transaction Retry Errors with FOR UPDATEReducing Contention by Eliminating Hot RowsReducing Transaction Elapsed TimeReordering StatementsTime Travel QueriesAmbiguous Transactions ErrorsDeadlocksTransaction PrioritiesWorking with ORM FrameworksSummary
Loading DataFile LocationsImporting FilesImporting from Cloud StorageImport PerformanceMigrating from Another DatabaseExtracting and Converting DDLGeneral Considerations When Converting DDLExporting DataLoading Data Into CockroachDBDirectly Importing PostgreSQL or MySQL DumpsSynchronizing and Switching OverUpdating Application CodeExporting CockroachDB DataChange Data CaptureCore Change Data CaptureUsing the Changefeed ProgrammaticallyEnterprise Change Data CaptureChange Data Capture to KafkaChange Data Capture to SnowflakeSummary

Finding Slow SQLExplaining and Tracing SQLEXPLAIN ANALYZEEXPLAIN OptionsEXPLAIN DEBUGChanging SQL ExecutionOptimizing Table LookupsOptimizing JoinsJoin MethodsOptimizing Sorting and AggregationOptimizing DMLOptimizing the OptimizerOptimizer StatisticsViewing StatisticsAutomatic StatisticsManually Collecting StatisticsSummary
Know Your RequirementsComparison of Deployment OptionsServerless DeploymentsSingle-Region Dedicated DeploymentsCommon Planning Tasks—Dedicated DeploymentsBenchmarking and Capacity PlanningCockroachDB Cloud DeploymentsSelf-Hosted on a Cloud PlatformSelf-Hosted “Bare-Metal” On-PremiseOther Self-Hosted ConsiderationsSelf-Hosted KubernetesConfiguring for Self-Hosted High AvailabilityDisk FailureNode FailuresNetwork FailureZone and Region TopologiesSummary
Deploying On-Premise or On-CloudFirewall ConfigurationOperating System ConfigurationClock Synchronization On-PremiseClock Synchronization on Cloud PlatformsCreating CertificatesConfiguring the NodesCreating a Ballast FileInitializing the ClusterCreating the First UserInstalling a Load Balancer (On-Premise)Cloud Load BalancersConfiguring Regions and ZonesDeploying on KubernetesInitializing the OperatorInitializing the ClusterCreating a Client PodLoad BalancingOther Kubernetes TasksSummary
Multiregion ConceptsRegions and ZonesSurvival GoalsLocality RulesPlanning Your Mutliregion DeploymentDeploying in MultiregionConverting to a Multiregion DatabaseConfiguring Regional by RowSetting Regional Survival GoalPlacement Restricted DatabasesSummary
BackupsThe BACKUP CommandBackup DestinationsFull BackupTable- and Database-Level BackupsIncremental BackupsAS OF SYSTEM TIME BackupWITH REVISION HISTORYSHOW BACKUPManaging Backup JobsScheduling BackupsLocality-Aware BackupsRestoring DataExporting DataDisaster Recovery Best PracticesBackup Scheduling and ConfigurationRecovering from Human ErrorsSummary
Firewall ConfigurationIP Allowlist with CockroachDB DedicatedVPC Peering and PrivateLink with CockroachDB DedicatedNative Linux FirewallConfiguring a Firewall in GCPConfiguring a Firewall in AWSConfiguring Ports for Microsoft AzureEncryption and Server CertificatesEncryption at RestAuthentication MechanismsStandard AuthenticationAdvanced AuthenticationAuthorizationManaging UsersManaging PrivilegesFine-Grained Access Control with ViewsLogging and AuditingSecurity Best PracticesSummary
MonitoringCockroachDB Dedicated AlertsCockroachDB Serverless AlertsAvailability MonitoringThe Cluster APIMonitoring and Alerting with PrometheusMonitoring and Alerting with DatadogLog ConfigurationLog ChannelsLog FormatFilter LevelsLog DestinationsLogging to FluentdRedactionLogs in Cloud DeploymentsCluster ManagementUpgrading the Cluster VersionAdding Nodes to a ClusterDecommissioning NodesTroubleshootingClock Synchronization ErrorsNode LivenessNetworking IssuesLoss of Client ConnectivityRunning Out of Disk SpaceWorking with CockroachDB Support ResourcesSummary
Tuning Versus FirefightingWorkload OptimizationDetecting Problem WorkloadsReview of Workload Optimization StrategiesAd Hoc or Analytic QueriesCluster BalanceCauses of ImbalanceHot RangesLoad BalancingChanges in Cluster TopologyAdmission ControlNetworkMemory OptimizationKey-Value Cachemax-sql-memoryHost MemoryDisk I/OScaling OutSummary