Things I Wished More Developers Knew About Databases

  1. You are lucky if 99.999% of the time network is not a problem.
  2. ACID has many meanings.
  3. Each database has different consistency and isolation capabilities.
  4. Optimistic locking is an option when you can’t hold a lock.
  5. There are anomalies other than dirty reads and data loss.
  6. My database and I don’t always agree on ordering.
  7. Application-level sharding can live outside the application.
  8. AUTOINCREMENT’ing can be harmful.
  9. Stale data can be useful and lock-free.
  10. Clock skews happen between any clock sources.
  11. Latency has many meanings.
  12. Evaluate performance requirements per transaction.
  13. Nested transactions can be harmful.
  14. Transactions shouldn’t maintain application state.
  15. Query planners can tell a lot about databases.
  16. Online migrations are complex but possible.
  17. Significant database growth introduces unpredictability.

You are lucky if 99.999% of the time network is not a problem.

It’s an open debate how reliable today’s networking is and how commonly systems experience downtime because of networking outages. The available research is limited and is often dominated by large organizations who have dedicated networking with custom hardware, as well as specialized staff.

ACID has many meanings.

ACID stands for atomicity, consistency, isolation, durability. These are the properties database transactions need to guarantee to their users for validity even in the event of crash, error, hardware failures and similar. Without ACID or similar contracts, application developers wouldn’t have a guidance on what’s their responsibility versus what the databases provide. Most relational transactional databases are trying to be ACID-compliant, but new approaches such as NoSQL movement gave birth to many databases without ACID transactions because they are expensive to implement.

An illustration of data loss if MongoDB crashes before it writes to the physical disk.

Each database has different consistency and isolation capabilities.

Among ACID properties, consistency and isolation have the widest spectrum of different implementation details because the spectrum of tradeoffs is wider. Consistency and isolation are expensive capabilities. They require coordination and are increasing contention in order to keep data consistent. When having to horizontally scale among data centers (especially among different geographic regions), the problems become significantly harder. Providing high levels of consistency can be extremely hard as availability decreases and networking partitions happen more often. See the CAP theorem for a more general explanation of this phenomena. It is worth to also note that applications can handle a bit of inconsistency or programmers might have enough insights about the problem to add additional logic in the application to handle it without heavily relying on their database.

An overview of the existing concurrency models and the relationships between them.
  • Serializable (most strict, expensive): A serializable execution produces the same effect as some serial execution of those transactions. A serial execution is one in which each transaction executes to completion before the next transaction begins. One note about Serializable level is that it is often implemented as “snapshot isolation” (e.g. Oracle) due to differences in interpretation and “snapshot isolation” is not represented in the SQL standard.
  • Repeatable reads: Uncommitted reads in the current transaction are visible to the current transaction but changes made by other transactions (such as newly inserted rows) won’t be visible.
  • Read committed: Uncommitted reads are not visible to the transactions. Only committed writes are visible but the phantom reads may happen. If another transaction inserts and commits new rows, the current transaction can see them when querying.
  • Read uncommitted (least strict, cheap): Dirty reads are allowed, transactions can see not-yet-committed changes made by other transactions. In practice, this level could be useful to return approximate aggregates, such as COUNT(*) queries on a table.
An overview of concurrency anomalies at different isolation levels per database.

Optimistic locking is an option when you can’t hold a lock.

Locks can be extremely expensive not only because they introduce more contention in your database but they might require consistent connections from your application servers to the database. Exclusive locks can be effected by network partitions more significantly and cause deadlocks that are hard to identify and resolve. In cases where being able to hold exclusive locks is not easy, optimistic locking is an option.

UPDATE products
SET name = 'Telegraph receiver', version = 2
WHERE id = 1 AND version = 1

There are anomalies other than dirty reads and data loss.

When we are talking about data consistency, we primarily pay a lot of attention to possible race conditions that can lead to dirty reads and data loss. But anomalies with data are not just limited to them.

BEGIN tx1;                      BEGIN tx2;SELECT COUNT(*) 
FROM operators
WHERE oncall = true;
FROM operators
WHERE oncall = TRUE;
UPDATE operators UPDATE operators
SET oncall = TRUE SET oncall = TRUE
WHERE userId = 4; WHERE userId = 2;

My database and I don’t always agree on ordering.

One of the core capabilities databases offer is the ordering guarantees but ordering may be surprising to the application developer. Databases see transactions in the order they receive them not in the programming order developers see them. The order of the transaction execution is hard to predict especially in high-volume concurrent systems.

result1 = T1() // results are actually promises
result2 = T2()

Application-level sharding can live outside the application.

Sharding is a way to horizontally partition your database. Even though some databases can automatically partition data horizontally, some don’t or may not be good at it. When data architects/developers can predict how data is going to be accessed, they might create horizontal partitions at the user-land instead of delegating this work to their database. This is called application-level sharding.

An example architecture where application servers are decoupled from the sharding service..

AUTOINCREMENT’ing can be harmful.

AUTOINCREMENT’ing is a common way of generating primary keys. It’s not uncommon to see cases where databases are used as ID generators and there are ID-generation designated tables in a database. There are a few reasons why generating primary keys via auto-incrementing may not be not ideal:

  • In distributed database systems, auto-incrementing is a hard problem. A global lock would be needed to be able to generate an ID. If you can generate a UUID instead, it would not require any collaboration between database nodes. Auto-incrementing with locks may introduce contention and may significantly downgrade the performance for insertions in distributed situations. Some databases like MySQL may require specific configuration and more attention to get things right in master-master replication. The configuration is easy to mess up and can lead to write outages.
  • Some databases have partitioning algorithms based on primary keys. Sequential IDs may cause unpredictable hotspots and may overwhelm some partitions while others stay idle.
  • The fastest way to access to a row in a database is by its primary key. If you have better ways to identify records, sequential IDs may make the most significant column in tables a meaningless value. Please pick a globally unique natural primary key (e.g. a username) where possible.

Stale data can be useful and lock-free.

Multi-version concurrency control (MVCC) enables a lot of the consistency features we briefly discussed above. Some databases (e.g. Postgres, Spanner) uses MVCC to allow each transaction to see a snapshot, an older version of the database. Transactions against snapshots still can be serializable for consistency. When reading from an old snapshot, you read stale data.

Application server reads 5-second old stale data from local replica even though the latest version is available on the other side of the Pacific Ocean.

Clock skews happen between any clock sources.

The most well-hidden secret in computing is that all time APIs lie. Our machines don’t accurately know what the current time is. Our computers all contain a quartz crystal that produces a signal to tick time. But quartz crystals can’t accurately tick and drift in time, either faster or slower than the actual clock. Drift could be up to 20 seconds a day. The time on our computers need to be synchronized by the actual time every now and then for accuracy.

  • TrueTime uses two different sources: GPS and atomic clocks. These clocks have different fail modes, hence using both of them is increasing the reliability.
  • TrueTime has an unconventional API. It returns the time as an interval. The time could be in fact anywhere between the lower bound and the upper bound. Google’s distributed database Spanner then can wait until it is certain the current time is beyond a particular time. This method adds some latency to the system especially when the uncertainty advertised by masters are high but provides correctness even in a globally distributed situation.
Spanner components use TrueTime where returns an interval, so Spanner can inject sleeps to ensure the current time has passed a particular timestamp.

Latency has many meanings.

If you ask ten people in a room what “latency” means, they may all have different answers. In databases, latency is often referred to “database latency” but not the latency client perceives. Client will see a latency of database latency and network latency. Being able to identify client and database latency is critical when debugging escalating problems. When collecting and displaying metrics, always consider having both.

Evaluate performance requirements per transaction.

Sometimes databases advertise their performance characteristics and limitations in terms of write and read throughput and latency. Although this may give a high level overview of the major blockers, when evaluating a new database for performance, a more comprehensive approach is to evaluate critical operations (per query and/or per transaction) separately. Examples:

  • Write throughput and latency when inserting a new row in to table X (with 50M rows) with given constraints and populating rows in related tables.
  • Latency when querying the friends of friends of a user when average number of friends is 500.
  • Latency of retrieving the top 100 records for the user timeline when user is subscribed to 500 accounts which has X entries per hour.

Nested transactions can be harmful.

Not every database supports nested transactions but when they do, nested transactions may cause surprising programming errors that are not always easy to identify until it becomes clear that you are seeing anomalies.

with newTransaction():
with newTransaction():
throw Rollback();
function newAccount(id string) {
with newTransaction():
function newAccount(id string) {
// In main application:with newTransaction():
// Read some data from database for configuration.
// Generate an ID from the ID service.
Uploads.create(id) // create upload queue for the user.

Transactions shouldn’t maintain application state.

Application developers might want to use application state in transactions to update certain values or tweaks the query parameters. One critical thing to consider is to having the scope right. Clients often retry the transactions when networking issues happen. If a transaction is relying on state that is mutated elsewhere, it might pick the wrong value depending on the possibility of the data races in the problem. Transactions should be careful about in-application data races.

var seq int64with newTransaction():
newSeq := atomic.Increment(&seq)
// Other operations...

Query planners can tell about databases.

Query planners determine how your query is going to be executed in the database. They also analyze the queries and optimize them before running. Planners can only provide some possible estimations based on signals it has. How to tell how to find the results for the following query:

SELECT * FROM articles where author = "rakyll" order by title;
  • Full table scan: We can go through every entry on the table and return the articles where author name is matching, then order.
  • Index scan: We can use an index to find the matching IDs, retrieve those rows and then order.

Online migrations are complex but possible.

Online, realtime or live migrations mean migrating from one database to another without downtime and compromising data correctness. Live migrations are easier if you you are migrating to the same database/engine, but can get more complicated when migrating to a new database with different performance characteristics and schema requirements.

  • Start doing dual writes to both databases. At this stage, new database won’t have all the data but will start seeing the new ones. Once you are confident about this step, you can move on to the second.
  • Start enabling the read path to use both databases.
  • Use the new database primarily for reads and writes.
  • Stop writing to the old database although keep reading from the old database. At this point, new database still doesn’t have all the new data and you might need to fallback to the old database for old records.
  • At this point, old database is read-only. Backfill the new database with the missing data from the old database. Once migration is complete, all the read and write paths can use the new database and the old database can be removed from your system.

Significant database growth introduces unpredictability.

Database growth makes you experience unpredictable scale issues. The more we know about the internals of our databases, the less we might predict how they might scale but there are things we can’t predict.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store