Last year I worked with various databases at Square. I did:

Investigating and resolving database performance issues.

Designing data models and sharding strategies for the new applications.

Evaluating and operationalizing new databases.

Initially forced by necessity, I soon became fascinated by databases. The study of databases intersects almost every topic in computer science — its theory and implementation are both sophisticated and challenging.

However, I soon realized that this is not an universally shared passion. To many of my colleagues and friends, the database is a magical black box system, too scary and complicated to understand. I wanted to change that.

While talking about databases, the topic of distributed systems cannot be ignored. Most modern databases are distributed, either implicitly (distributed clustered databases) or externally (a single application connected to multiple databases via application-level sharding).

This post is a confession of my love for databases and distributed systems. It is mostly targeted towards programmers like me, application developers who regularly interact with databases. We program mostly in Java, Python, or Ruby, writing server side applications. I will cover:

Comparing and evaluating different databases.

How to understand and fully utilize your database.

Understanding how the database works at a high level.

First of all, What is a database?

In this post, any software which accepts and persists data for future retrieval is a database. This includes both traditional RDBMS and NoSQL databases, as well as systems such as Apache Zookeeper and Kafka.

CAP theorem

CAP theorem. It is my favorite impossibility result since Turing’s Halting Problem and P≠NP (technically not a result). The CAP theorem suggests that, at best, any distributed system can only satisfy CP (Consistency & Partition Tolerance), AP (Availability & Partition Tolerance), or somewhere between the two. As a consequence, interesting tradeoffs between consistency and availability arise.

Few important misconceptions about CAP theorem:

The traditional “choose 2 of 3" argument doesn’t make sense. You cannot give up partition tolerance, because that would mean “behavior of the operations performed during the partition is undefined”, and in that case the database isn’t really consistent. (See [1])

Reaching the limit of the CAP theorem is not given by default. There are many databases which are neither consistent, available, nor partition tolerant. Achieving the limits of the CAP theorem requires careful design and implementation.

Distributed Systems

As stated earlier, many modern databases are distributed in some way. There are two main motivations for this:

To scale beyond a single machine — storing and processing data in multiple nodes.

beyond a single machine — storing and processing data in multiple nodes. To increase availability — ensuring a database isn’t a single point of failure.

These two goals are closely related. In general, scaling a system by increasing the number of machines negatively affects availability, since the probability of encountering failures increases statistically. Thus, achieving high availability is almost a prerequisite to scalability.

Correctness vs Efficiency

Both correctness and efficiency matter, and are closely related in distributed databases.

Correctness is important in any software, but it is essential for databases because (1) databases persist data, and incorrect data will survive over the reboot, and (2) databases are regarded as the most trusted foundation of the software stack.

What does it mean for a database to be correct? Many distributed databases have exotic consistency semantics written in fine print. You can make a tradeoff here. In general, more stringent consistency makes application code easier to reason about, at the cost of efficiency and availability.

Theories aside, there are implementation and operational challenges to correctness. A distributed system is inherently complex. An algorithm like Paxos is notoriously hard to understand and implement correctly. As the system becomes more complex, more obscure failure scenarios arise. Systems like Redis and ElasticSearch suffer from the non-axiomatic design of their distributed systems.

Other than the above tradeoff, efficiency is important because of the previous difficulties. The more I learn about low-level programming, I realize how many raw operations/sec a machine can perform. In many cases, efficiency reduces complexity, making the overall systems simpler. The fact that distributed systems are harder than low-level programming motivates me to seek these efficiencies. I would gladly choose the database that requires fewer machines to operate, given the same load.

Finally, the computational and latency overhead of inter-machine coordination can be significant. In conclusion: the fewer moving parts, the better.

To squeeze more performance and efficiency out of the code, one must journey deeper into lower level abstractions, including:

Memory allocators & Garbage Collectors [2]

Filesystem schedulers & IO device characteristics

Kernel Settings

Implementation details of various system calls, (fork, execv, malloc)

Any disharmony between them can cause poor performance. You don’t have to be a kernel hacker, but you do need a high-level overview of how these components interact.

Empowering the Applications

Just like how different programming languages have their own benefits and drawbacks, databases have their own unique characteristics. It is important to fully understand them. It allows you to implement a sophisticated yet efficient application while delegating most of the complex, error-prone work to the database.

Traditional RDBMS is generally a good choice if you don’t have a stringent performance or availability requirement. ACID guarantee is very powerful and tooling is great. Sharding RDBMS is painful but it is well understood. MySQL and Postgres are two popular choices.

Full text search engines allow you to build advanced indexing & searching functionalities. Their eventually consistent nature is seldom a problem, since search is an inherently fuzzy operation. Lucene and its descendants (Solr, ElasticSearch) are popular choices.

Message queues and event processing systems eliminate error prone, hard to implement code. Kafka, Storm, Spark SQL, RabbitMQ, and Redis are popular choices.

Databases with cross-region replication make regional failover and high availability much easier. There aren’t many open source choices here, but Apache Cassandra is probably the most mature one.

Consensus, leader election, and distributed locks are hard to implement and test. Don’t implement your own. Use Zookeeper, etcd, or raft-as-a-library.

Now to the fine prints. Database is an inherently leaky abstraction. They generally do a very good job at hiding their underlying complexities, but ignoring their limitations will eventually bite you. Few important things:

Understanding correctness guarantees of databases. What does a failed operation mean [3]? Which operations are fully consistent, which are not?

Understanding how the data is persisted & retrieved. Which operations are efficient, which are not? Is there a query planner, or detailed statistics per operation?

Sharding and clustering topologies. Understanding how data is distributed amongst a cluster. Does your sharding strategy evenly distribute data, or are there hot spots?

Data modelling patterns and anti-patterns.

Operational Challenges

Once part of your software stack, Databases live and breathe with your infrastructure 24–7. They introduce unique operational challenges.

Operating a database is like sailing in the middle of an ocean. Whenever you encounter a problem, you need to fix it without sinking the database, even in the midst of a storm. Thus a database must have:

Ways to introspect & monitor the system.

the system. Knobs to maintain & administrate the system.

the system. Replication, backup, and restoration. Losing data is critically bad. Machines will crash at some point. Since databases are stateful, you can’t simply redeploy to restore failed machines.

All these capabilities while it is running. Frankly, all databases have shortcomings regarding this. Allowing every configuration to be changed while a system is running a difficult challenge. Many operations require a database-wide mutex, additional system resources, or a restart. Examples include:

In MySQL prior to 5.6, adding a column requires a full table lock. Beautiful solutions like pt-online-schema-change exists to mitigate this problem. MySQL now supports online schema migrations.

Cassandra allows you to easily add, remove, and repair nodes. However, these operations adds additional loads on the system.

Moreover, you can’t easily replace a database. Even a task of migrating the data in the same database isn’t simple. Migrating to another database is even harder, if not infeasible [4]. An application code is much easier to incrementally roll out and revert back. Data live much longer than code. Data schema and stored data are normally shared amongst the multiple applications. Thus, the choice of the initial database system and the corresponding data model is very important.

Finally, databases will eventually fail. No matter what platform/infrastructure-as-a-service you use, some failures are unavoidable:

An application bug corrupting or dropping data. [5]

Misunderstanding the safety and consistency guarantee of a database and losing writes.

Data model & database mismatch — For example, having a dataset that doesn’t fit in a single shard. Expecting compare-and-swap to work in an eventually consistent database.

Operational failure — Machines crashing. Hard drives dying. OS Upgrades.

Network Partitions [6].

Thundering herd — A single system failure cascading to an entire system.

And the worst of all — “things are suddenly slow”. “random spikes in latency”. “sporadic errors once a day”, “this record should’ve existed”.

And there is no single solution to this. The art of operating a database belongs to the art of maintaining a high SLA system, but if I need to give few tips:

Application developers understanding limitations and failure mode behaviors.

Writing a resilient application. Multi-datacenter deployment. Automatic failover between datacenter.

Having a team of competent operations engineers (Site Reliability Engineers, DBAs) who understand different failure scenarios and recovery methods.

PS: At Square, we have an awesome online data storage (ODS) team that abstracts this problem away from us

Basic Building Blocks

Abstractions provided by a database is truly magical. Data ingestion, querying, replication, and failover all in the same package? But as you get accustomed to it, you start to learn that there are basic building blocks — common patterns and components shared amongst all the databases.

First, data retrieval reduces down to one of:

key-value lookup (hash tables)

range lookup (trees and LSMs)

file offset lookup (Kafka, HDFS)

At the end of the day, computers don’t understand SQL, indexes, joins, or other fancy frills. High level operations need to be translated to something the machine can execute.

For the persistent data structure, B-trees, hash tables, and log-structured-merge-trees (LSM trees) are popular choices. Most likely, your data is stored in one of these unless it requires some special lookup (ex: geospatial queries). LSM-tree is a popular modern choice, used in BigTable, HBase, Cassandra, LevelDB, and RocksDB due to its superb write performance and reasonable read performance.

Finally, there are popular patterns and algorithms reused throughout the different systems: Paxos, Raft, Consistent Hashing, Quorum Read / Writes, Merkel Trees, and Vector Clocks are some of the fundamental building blocks.

Summary

This post is a simple, high level overview of some of the topics. There are many other topics that I haven’t covered, such as optimizing for different workflows (OLAP, OLTP, Batch Processing) and UX of the database (query language, transport protocol, client libraries), which are equally important. The implications of different consistency semantics, such as sequential consistency, read your own write, at least once delivery are also very interesting.

The best thing about a database is that it is a very mature abstraction. It mostly works, and as an application developer, it is very easy to save and retrieve data without much thinking. This is definitely celebration worthy, but peeling the skin off this sufficiently advanced technology is definitely worthwhile.

I wish more people are fascinated by this subject, and fully utilize it.