CockroachDB: The Definitive Guide

Chapter 5. CockroachDB Schema Design

A sound data model is the foundation of a highly performant and maintainable application. In this chapter, we’ll review the fundamentals of relational schema design, with a particular focus on aspects of schema design that bear on distributed database operations and on advanced CockroachDB features such as column families and JSON binary (JSONB) support. We’ll cover the creation of tables, indexes, and other schema objects that support a well-designed CockroachDB application.

Although CockroachDB supports mechanisms for efficiently altering schemas online, schema changes to production applications are nevertheless high-impact changes, typically involving coordinated changes to application code and production database configuration. If done poorly, there’s the risk of loss of application functionality, availability, or performance. Therefore, although it’s quite possible to alter CockroachDB schemas in production, it’s far better to get the schema right during application design.

Relational database design is a big topic and has been the subject of many books and continuing debate. We don’t want to try to cover advanced design principles here, nor do we want to engage in any debates about the purity of various design patterns. Most database models are a compromise between the mathematical purity of the relational model and the practicalities imposed by the physical database system. Therefore, in this chapter, we’ll attempt to briefly cover only ...