The Details: production databases (SQL and NoSQL)

A deep dive into the databases that power our apps

Hello there, lovely paid subscribers. First of all, thank you for all of your insightful questions from last week – I’ve got them all gathered in a doc and I’ll pick off a few every week in a paid-only post. If you have any more questions you’d like me to answer, just send them over whenever. But this week we’ve got a more in depth paid post!

This week’s 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. 

🔍 Deeper Look 🔍

When you load your orders page on Amazon, it isn’t issuing a query to the database directly. Amazon (and most other companies) set up API endpoints as an intermediary between their frontend and their database. Fundamentally though, learn to think about this page – and pages on any app you're using – as data.

🔍 Deeper Look 🔍

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.