The Details: production databases (SQL and NoSQL)

This post is going to dive deeper into production databases. We’re going to look at what they’re used for, popular options (SQL vs. NoSQL – what’s the difference?), challenges with scaling applications, and where databases are going (serverless, basically). 

The first “Details” post covered ETL, and the last one covered data warehouses. 

Refresher: what’s a production database?#

There are a lot of different types of databases out there. They come in all different shapes and sizes based on what engineering teams use them for. A few that you’ve probably come across:

• Analytical databases: for storing analytical data and writing long, complex queries. E.g. Snowflake, BigQuery, Redshift
• In-memory databases: for quick operations in memory. E.g. Redis, Memcached
• Streaming databases: for streaming data from place to place. E.g. Kafka (sort of)

The largest category of databases though – both in terms of number of available options and total market size – is production databases. And those are the databases that teams use to store application data, the data that backs the apps we use every day like Gmail, Slack, Whatsapp, and even your Bloomberg Terminal.

For a quick refresher on what production databases do, now would be a good time to read the original post here. In short, production databases are optimized for transactions – small, highly frequent little operations that your app runs to keep things moving. For a live example, let’s take a look at my orders page on Amazon. 

Each one of these orders (and items too) is sitting in a database on Amazon’s servers. When I load this page, the site sends a request to Amazon’s backend saying “I need data about what Justin ordered recently.” Amazon’s backend sends that data back, and then the app formats it in a nice, reader friendly way with links, images, and all of that jazz. 

This database “read” that you’re doing when you load this page is an example of a transaction. It might look something like this, if Amazon is using a SQL database behind the scenes:

SELECT  
  name,
  link,
 date,
 price
FROM orders
WHERE customer = ‘justin’

The same kind of thing happens when you buy an item on Amazon. Once I’ve loaded a product page (which, by the way, is also just data being read from a database), clicking “buy” will insert a new row into Amazon’s database and record that I just purchased this riveting read. 

That transaction might look something like this:

INSERT INTO orders (id, price, purchaser)
VALUES (ad73hfikd8d, $13.99, ‘justin’)

Transactions are the central tenet of how relational databases – the most popular type of production database – work. Most relational databases guarantee something called ACID, which is a rigid standard for transactions. It means that you can always count on your database to execute transactions in order, without them impacting each other. To understand why that’s important, let’s keep running with our Amazon example.

Imagine you (a sensible, rugged individual) want to purchase a copy of How to Win Friends & Influence People. You want to win friends and influence people. But it turns out Amazon only has one copy left. Now given that Amazon is a very popular website, there’s another person who also wants to buy it, right now (cheeky bastard). Unfortunately, they click “buy” before you’re able to; and that means you can’t buy it anymore. If Amazon isn’t able to update you quickly enough as to that unfortunate reality, you might also click buy, and pay them for something they can’t deliver to you. So as Amazon is executing the first transaction (inserting a row into the orders table), they need to lock the database and make sure you don’t see “in stock” for that particular book, or worse, pay them for it. 

This is called a dirty read, and it’s one example of where transactional integrity is important. Remember that Amazon has hundreds of millions of customers, so it’s pretty likely that stuff like this could happen all the time. Hence, the need for rigid database structure.