Rob Reid - Practical CockroachDB: Building Fault-Tolerant Distributed SQL Databases

Practical CockroachDBBuilding Fault-Tolerant DistributedSQL DatabasesRob ReidPractical CockroachDB: Building Fault-Tolerant Distributed SQL Databases Rob Reid Liss, Hampshire, UK ISBN-13 (pbk): 978-1-4842-8223-6 ISBN-13 (electronic): 978-1-4842-8224-3 https://doi.org/10.1007/978-1-4842-8224-3 Copyright 2022 by Rob Reid This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made.

The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover image photo by Maximalfocus on Unsplash Distributed to the book trade worldwide by Springer Science+Business Media LLC, 1 New York Plaza, Suite 4600, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm. com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.

For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub. Printed on acid-free paper For Emily, Ruby, and our little one on the way.Thank you for giving me the space and patienceI needed to put this wonderful project together.Table of Contents About the Author xi About the Technical Reviewer xiii Acknowledgments xv Introduction xvii Chapter 1: The Reason for CockroachDB 1 What Is CockroachDB? 1 CockroachDBs Architecture 2 What Does CockroachDB Solve? 2 Who Is CockroachDB For? 4 Chapter 2: Installing CockroachDB 5 Licensing 5 Free 5 Paid For 6 CockroachDB Core 6 Local Installation 6 Binary Install 7 Docker Install 8 Kubernetes Install 9 Multinode Clusters 20 Multiregion Clusters 21 The demo Command 28 v Table of ConTenTs CockroachDB Serverless/Dedicated 29 Creating a Cluster 29 Connecting to Your Cluster 29 Summary 30 Chapter 3: Concepts 31 Database Objects 31 Data Types 33 UUID 33 ARRAY 34 BIT 37 BOOL 38 BYTES 39 DATE 39 ENUM 40 DECIMAL 41 FLOAT 43 INET 44 INTERVAL 45 JSONB 46 SERIAL 48 STRING 49 TIME/TIMETZ 50 TIMESTAMP/TIMESTAMPTZ 51 GEOMETRY 53 Functions 55 Geo-partitioned Data 58 REGION BY ROW 59 REGION BY TABLE 63 vi Table of ConTenTs Chapter 4: Managing CockroachDB from the Command Line 67 The Cockroach Binary 67 The start and start-single-node Commands 68 The demo Command 69 The cert Command 70 The sql Command 71 The node Command 73 The import Command 78 The sqlfmt Command 78 The workload Command 80 Chapter 5: Interacting with CockroachDB 83 Connecting to CockroachDB 83 Connecting with Tools 83 Connecting Programmatically 88 Designing Databases 96 Database Design 96 Schema Design 98 Table Design 101 View Design 109 Moving Data 114 Exporting and Importing Data 114 Watching for Database Changes 116 Chapter 6: Data Privacy 123 Global Regulations 123 Location-Specific Considerations 125 UK Company with UK and European Customers 125 European Company with European and American Customers 126 American Company with American Customers 127 American Company with American and European Customers 127 A Non-Chinese Company with Chinese Customers 128 vii Table of ConTenTs Personally Identifiable Information 129 Encryption 129 In Transit 130 At Rest 133 Chapter 7: Deployment Topologies 139 Single-Region Topologies 140 Development 140 Basic Production 140 Multiregion Topologies 143 Regional Tables 143 Global Tables 149 Follower Reads 151 Follow-the-Workload 152 Antipatterns 152 Summary 157 Chapter 8: Testing 159 Structural Testing 161 Functional Testing 165 Black Box Testing 165 White Box Testing 184 Nonfunctional Testing 194 Performance Testing 194 Resilience Testing 203 Chapter 9: Production 207 Best Practices 208 SELECT Performance 208 INSERT Performance 209 UPDATE Performance 214 Cluster Maintenance 220 Moving a Cluster 227 viii Table of ConTenTs Backing Up and Restoring Data 231 Full Backups 233 Incremental Backups 236 Encrypted Backups 237 Locality-Aware Backups 240 Scheduled Backups 241 Cluster Design 243 Cluster Sizing 243 Node Sizing 243 Monitoring 244 Index 249 ix About the AuthorRob Reid is a software developer from London, England.

In his career, he has written back-end, front-end, and messaging software for the police, travel, finance, commodity, sports betting, telecom, retail, and aerospace industries. He is an avid user of CockroachDB and has worked with the Cockroach Labs team in recent years to promote the database and embed it into development teams in the United States and the UK. xi About the Technical ReviewerFernando Ipar has been working on and with open source databases since 2000, focusing on performance, scaling, and high availability. He currently works as a Database Reliability Engineer at Life360. Before that, he has worked at Perceptyx, Pythian, and Percona, among other places. xiii Acknowledgments Im incredibly grateful to the following people. xiii Acknowledgments Im incredibly grateful to the following people.

Their contributions to this book have been invaluable to me. Kai Niemi (Solutions Engineer (EMEA) at Cockroach Labs) I met Kai when he was a customer of Cockroach Labs and have witnessed him transition from being a CockroachDB expert at one company to an expert the global CockroachDB community can be grateful to have. Daniel Holt (Director, Sales Engineering, International (EMEA and APAC), at Cockroach Labs) I worked very closely with Daniel from the moment he joined Cockroach Labs and have often marvelled at his comprehensive knowledge of the database. Katarina Vetrakova (Privacy Programme Manager at GoCardless) Katarina is quite possibly the most enthusiastic data privacy specialist you could ever hope to meet. Shes completely dedicated to the art, and since working with her at Lush, her passion and knowledge have been inspiring to me. Jonathan Gennick (Assistant Editorial Director of Databases at Apress) Id like to thank Jonathan Gennick for approaching me to write this book.