Transactional vs. analytical databases
To get the most out of this post, you’ll need to understand what a database is, how schemas and relations work, and how software runs in the cloud.
The core of whatever app you’re reading this in is a production database: it stores user information and other backend stuff that populates whatever you see on the screen (unless you printed this out, loser). That database is all about transactions:
- Every time a new user signs up a new row gets added
- If you change your password, your row gets updated
- If you shut down your account, your row gets deleted (sometimes)
These kinds of processes and how they interact with a database are called OLTP, or online transactional processes.
Because a lot of these transactions can be happening at once (apps with many users or page loads), popular databases like MySQL are built with that in mind. They usually have special features that make sure these transactions don’t get messed up, and optimize for that kind of use case. It’s all about transaction after transaction, boom boom boom, making sure your app keeps working without getting messed up.
You can think of OLTP operations as text messages. It’s a great medium for short, clear directives. Need a quick favor. What’s Jason’s number? Are you going to the party tonight? Texts are about speed and efficiency.
There’s a whole other use case for databases though: analytics. If you have big questions you want to ask of your data, like how many users you have, how many orders get cancelled each month, or how much money you made last year, you use the database in a very different way. Analytical queries typically require looking at a lot more data, take a much longer time to run, and join a bunch of different sources together. The process is usually called OLAP, or online analytical processing.
These kinds of analytical transactions are like email: it’s a much better format for longer form, more involved communication than texts are. Emails take longer to send, but they’re more organized and professional for when you need to send multiple paragraphs and embedded media.
