OLTP vs OLAP and the row / column storage tradeoff — Transcript

So the question that we're trying to answer is what kinds of data structures are used between OLTP databases that are doing more transactional processing and analytics databases. And often the simple way to think about this is the difference between row storage and column storage. One of the kind of core components of this whole discussion of what database are you choosing, what database engine are you going to choose is there are three broad categories that databases fall into. And even this doesn't really capture them all, but the three broad categories that databases fall into are OLTP, OLAP, and HTAP. And we'll draw boxes here on the screen for each of them. So these databases are OLTP, that stands for online transaction processing. And basically what that means is it's kind of optimized for a lot of very small short transactions where you're reading small amounts of data. And this is the kind of database that powers most applications, right? Um, if you, you know, are on Twitter right now, right, or you're on YouTube or whatever, there's almost always some form of OLTP database. It might be MySQL, it might be Postgres, it might be some, you know, on Google, right, since Google owns YouTube, it's probably something like Google Spanner that's powering that or maybe something custom for YouTube, but it's a database that's designed to do things like look up users when they log in or when you load your profile, load the user preferences or load the users, um, you know, whatever light mode, dark mode settings, right? All the different things that you need to be able to fetch about users and about profiles and about accounts when you're interacting with an application typically get stored in an OLTP database because they're good at storing those, structuring those. Often it's a relational database, but even databases like MongoDB that aren't relational kind of fall into that OLTP category. So we could kind of just start writing a list, right? Like MySQL is in here, Postgres is in here. Uh, let's see, technically, uh, SQLite, although like SQLite maybe isn't the best for really high scale, but, you know, technically it falls into this category here. So like these databases fall nicely into the OLTP category. The other category or the next category is OLAP and the A there stands for analytics. So essentially this is instead [clears throat] of a database designed for inserting lots of small things, fetching lots of small things in a semi-random pattern, um, it's designed to do analytics on often big data sets, right? So if you think about the information that's displayed in a Spotify Wrapped or YouTube Wrapped, it's kind of an analysis of an entire year of your specific data set. But still, if you're a heavy user of Spotify or YouTube or whatever, that's like kind of a big chunk of data that it needs to do analysis on to figure out what was Ben's most listened to artist or most watched YouTube channel. But then there's also the bigger picture of just like as a business, a lot of businesses want to do analytics not just per user, but like over their whole user base, right? Like what's the average age of our user base so that we can better target that user base or what is, um, you know, what's the average amount of, you know, what time of day do people spend the most time on Twitter or on YouTube, right, or watching streams? So doing these kind of deeper analytics where you might have to analyze many gigabytes or even many terabytes worth of your users' data to come up with answers like to better run your business, to better help your customers, right, to do reporting at the end of the year and all of this kind of stuff. Although you can run those kind of queries on MySQL and Postgres and SQLite, they're not really tailored in terms of how they store the data on disk. Even within OLAP, there's kind of different subcategories, right? There's like, well, databases that work well for time series analytics or databases that work more for generic analytics and all this kind of stuff, right? Some examples are things like ClickHouse is a popular one for time series analytics, but also just analytics in general. Um, DuckDB. This is one actually. Why can't I spell? Apparently I can't spell. DuckDB. Um, most of you know I work at PlanetScale and we recently like released an integration with them and there's the MotherDuck cloud that you can use with them, right? Google BigQuery is what it's called, right? I always get BigQuery and BigTable, but this is like a Google one. Why am I forgetting what Amazon's, I think Amazon's is it called Redshift? Redshift. So all of [snorts] these kind of things, right? So these are like standalone either query engines or databases as a whole, uh, for doing analytics type stuff and again these are not comprehensive lists, right? There's more in this list, there's more in this list and then HTAP is kind of this hybrid, right? There's this, uh, sort of idealized goal out there and I like honestly don't really have much experience at all directly using HTAP databases but there's this idea that maybe you can build databases that take a hybrid approach and do both well. Um, the only one that obviously comes to my mind is like SingleStore. Especially when you're at scale, the idea of just putting everything in a single database and having just this one giant unified place where all your data is. It's a nice idea in theory, but in practice, you run into a bunch of issues with compute competing for each other or there's a lot of data duplication. So, how do you make sure all of the different formats and layouts that you have data stay in sync? Um, so like obviously there's still people that use these products, but I think, um, generally if you're at a large enough scale, a better approach is to kind of have separate databases or data pipelines for your OLTP and OLAP. Let's look here. We got an interesting question. So, do OLAP databases still use the same data structures in [snorts] OLTP like B+ trees and LSM trees? Good question. The answer is again unless you're doing a hybrid approach, the answer is generally no. Um, and let's talk about that. Actually, I have that on the list of things I wanted to talk about. There's a lot of nuance to this question because even like within OLTP, there's different ways you can lay data out. Within OLAP, there's different ways you can lay data out. You can have things that kind of borderline and take approaches from both. Um, but the big, the big difference, kind of the general broad way of thinking about the difference is row storage versus column storage and generally row-based storage aligns well with OLTP workloads and column-based storage aligns well with OLAP workloads.

So the question that we're trying to answer is what kinds of data structures are used between OLTP databases that are doing more transactional processing and analytics databases. And often the simple way to think about this is the difference between row storage and column storage. So to take a look at this, we've got this sample table up here.

And what I've got is just like a really basic user table, right? Most applications need something like this. A table that has your usernames, your emails, maybe you store addresses, sign up dates. This one's small. Often you'd have many more columns on a user table.

Um, but let's think about how we would lay this out physically on disk to optimize for different circumstances.

So, we think back to what we talked about before. If you're an application like, let's say you are, um, YouTube, if we're YouTube and we want to look up, um, extra large text, we want to be looking up, right? A user logs in. Okay, when a user logs in, you're going to make a request to go and look up. Oh, that's not the line that I want. I want this.

You're going to make a request. Okay, so Ben logged in, right? So, you're going to look up Ben by ID. You know, find Ben's user profile. Make sure the password lines up,

load the users um you know whatever light mode, dark mode settings, right? All the different things that just you need to be able to fetch about users and about profiles and about accounts when you're interacting with an application

typically get stored in an OLTP database because they're good at storing those structuring those often it's a relational database but even databases like MongoDB that aren't relational kind of fall into that OLTP category. So we could kind of just start writing a list,

right? Like my SQL is in here, Postgress is in here. Uh let's see, technically uh SQLite, although like SQLite maybe isn't the best for really high scale, but you know, technically it falls into this category here. So like these

databases fall nicely into the OLTP category. The other category or or the next category is OLAP and this the A there stands for analytics. So essentially this is instead [clears throat] of a database designed for inserting lots of small things,

fetching lots of small things in a semi- random pattern, um it's designed to do analytics on often big data sets, right?

So if you think about the information that's displayed in a in a Spotify wrapped or YouTube wrapped, it's kind of an analysis of an entire year of your specific data set. But still, if you're a heavy user of Spotify or YouTube or

whatever, that's like kind of a big chunk of data that it needs to do analysis on to figure out what was Ben's most listened to artist or most watched YouTube channel. But then there's also the bigger picture of just like as a

business, a lot of businesses want to do analytics not just per user, but like over their whole user base, right? like what's the average age of our user base so that we can better target that user base or what is um you know what's the

average amount of you know what time of day do people spend the most time on Twitter or on YouTube right or watching streams so doing these kind of deeper analytics where you might have to analyze many gigabytes or even many

terabytes worth of your users data to come up with answers like to to better run your business to better help your customers right to do reporting at the end of the year and all of this kind of stuff. Although you can run those kind

of queries on MySQL and Postgress and and SQLite, they're not really tailored in terms of how they store the data on disk. Even within OLAP, there's kind of different subcategories, right?

There's like well databases that work well for time series analytics or databases that work more for generic analytics and all this kind of stuff, right? Some examples are things like Clickhouse is a popular one for time series analytics, but also just

analytics in general. Um, duck DB. This is one actually. Why can't I spell? Apparently I can't spell. DuckDB. Um, most of you know I work at Planet Scale and we recently like released an integration with them and there's the

mother duck cloud that you can use with them, right? Google Big Query is what it's called, right? I always get big query and big table, but this is like a Google one. Why am I forgetting what Amazon's I think Amazon's is it called

Redshift? Redshift. So all of [snorts] these kind of things, right? So these are like standalone either query engines or databases as a whole uh for doing analytics type stuff and again these are not comprehensive lists right there's there's more in this list there's more

in this list and then HTAP is kind of this hybrid right there's this uh sort of idealized goal out there and I like honestly don't really have much experience at all directly using HTAP databases but there's this idea that

maybe you can build databases that take a hybrid approach and do both well um the only one that obviously comes to my mind is like single store. Especially when you're at scale, the idea of just putting everything in a single database and

having just this one giant unified place where all your data is. It's a nice idea in theory, but in practice, you run into a bunch of issues with compute competing for each other or there's a lot of data

duplication. So, how do you make sure all of the different formats and layouts that you have data stay in sync? Um, so like obviously there's still people that use these these products, but I think um, generally if you're at a large

enough scale, a better approach is to kind of have separate databases or data pipelines for your OOLTP and OLAP. Let's look here. We got an interesting question. So, do OLAP databases still use the same data structures in [snorts]

OLTP like B+ trees and LSM trees? Good question. The answer is again unless you're doing a hybrid approach, the answer is generally no. Um, and let's talk about that. Actually, I have that on the list of things I wanted to talk

about. There's a lot of nuance to this question because even like within OOLTP, there's different ways you can lay data out. Within OLAP, there's different ways you can lay data out. You can have things that kind of borderline and take

approaches from both. Um but the big the big difference kind of the general broad way of thinking about the difference is uh row storage versus column storage and generally rowbased storage aligns well with OLTP workloads and columnbased storage aligns well with OLAP workloads.

So the question that we're trying to answer is what kinds of data structures are used between OLTP databases that are doing more transactional processing and analytics databases. And often the the simple way to think about this is the

difference between row storage and column storage. So to take a look at this, we've got this sample table up here.

And what I've got is just like a really basic user table, right? Most applications need something like this. A table that has your usernames, your emails, maybe you store addresses, sign up dates. This one's small. Often you'd have many more columns on a user table.

Um, but let's think about how we would lay this out physically on disk to optimize for different circumstances.

So, we think back to what we talked about before. If you're an application like let's say you are um YouTube, if we're YouTube and we want to look up um extra large text, we want to be looking up, right? A user logs in. Okay, when a

user logs in, you're going to make a request to go and look up. Oh, that's not the line that I want. I want this.

You're going to make a request. Okay, so Ben logged in, right? So, you're going to look up Ben by ID. You know, find Ben's user profile. Make sure the password lines up, all that kind of stuff. You might even update some rows

like this has sign up date, but you might also store last logged in date.

And so over here further along, you're updating like when Ben last logged in, these kind of things. And then if you if I navigate to Alli's profile, YouTube, the YouTube app might need to go look up Alli's profile row and then from there

find Alli's like homepage row to figure out what banner to display and what profile image to display. Right? So essentially as YouTube is doing things, a lot of the nature of loading a profile and loading a video is find a row in a

database and grab a bunch of things from that row and maybe even update some of the things in that row. So when I log in, it needs to fetch my name so that it can display it up in the corner. It

needs to fetch, you know, update my my last loggedin date. It needs to grab my ID so that it knows as I make actions, it can uh store things based on ID there in the future. it needs to load my email

so that if I go and look at my profile page, it sees my email there. So, I need to do a bunch of stuff that's sort of clustered together by these rows. And because of that, it makes sense for a

transactional database like that to store everything that's in one row together. So, in like let's say I have a file that is storing everything in this user's table, right? Literally the first bytes of the file, probably some metadata, but then once we get to the

actual table data, is the ID1 as an integer and then the string ben and then the string benhi.com and then this street address with, you know, delimiters, whatever, and then the date and then the next bite is this two and

then the next user and the user's email. Right? So, we're we're clustering all of this stuff together based on right like if if I'm Ben, I want to put Ben's email nearby. I want to put Ben's address nearby. I want to put Ben's, you

know, last or signed up date nearby. So, one row, all of the data is near each other on disk. Right? And this is what we would call rowbased storage. And this is what's used in MySQL and in Postgress

and most of these relational databases because there's kind of this assumption that when you're looking up a row, you might not need everything in that row, but you're probably going to need at least a few bits of other information in

that same row. And so we should store it all together. And especially because when you're a hard drive disc, you don't read single integers at a time. But what you're actually doing when you're a hard drive is you read pages, which are often

4 kilobytes or 8 kilobytes or 16 kilobytes. And so a single row often fits in a page. In fact, often multiple rows fit in a page. So, if you're going to have to read, you know, Ben out of

this row anyway, it's likely that from the hard drive or solid state drive, you're going to have to fetch a 4 kilobyte chunk. So, all this other stuff is probably going to come with it anyway. So, let's organize the data.

Let's leverage that in how we're laying things out on disk. So, next, column storage. Let's look at column storage.

If you think about doing analytics, actually, I'll just copy this over here. If you think about analytics questions, so things like we're doing some business analytics. We've just we're at the end of our year like we are now. We want to

figure out what time of year did most people sign up for new YouTube accounts because maybe we can draw some business ideas from this, right? Like maybe most people sign up for YouTube in the summer when they're out of school and they're

bored. And so maybe that can lead us to say, "Hey, we could like advertise more in the summer because we know that's when people are more likely to want to make a new account." Or maybe we're surprised and people sign up mostly

during the school semester for students because they're trying to look up videos on how to learn and do well in their classes or whatever, right? So, we're trying to figure out glean some insights for what time of year the most people

sign up for new accounts. What we would probably want to do is go to our user table because that's where we've stored the information about when people create their accounts. And we would want to basically do a big scan over the whole

table. And if you're YouTube scale, right, you actually have like two decades of data and you've got, you know, hundreds of millions or even billions of user accounts. So that's a big table to scan over and get all of

this information for. But if I'm just trying to answer that one specific question, I don't actually care what address people live at. But for the question at hand, all you care about really is this column. We want to see

this column and then we basically want to build like a line plot to show what times of year people signed up most. So in this case I only really care about scanning on this data here but there's billions of these rows. So it actually

if I had to read every time I wanted to read Ben signup date if I had to read all of this from disk and then zed signup date I had to read all of this from disk and then Ali's had to read all

of this from disk. I would end up doing a lot of IO's that I didn't actually have to do. So, the better way to organize this often for analytics queries is to group things together by column. So, all the ids go together.

Let's move this uh over here. So, all the ids go together and then right after again there would be a lot more. We're just doing three for as a simple example. And then once I've written all of my IDs in disk, then I

have all of my names. And then I have all of my emails together. And then I have all of my addresses together.

And then finally, I have all of my signup dates together. So then if I wanted to do this analytics query where I was trying to figure out, you know, what times of the year are the most signup dates, I could do a big long

sequential scan of contents on my hard drive, but just the section that has all of the dates in it. And then I basically skip having to ever read all of this other stuff. There's another advantage too that's maybe a little bit more

subtle which is there's only 365 days in a year but I have a billion users on YouTube. So I have a billion records that the cardality the number of different values for well I guess technically here we're including the

year as well. So but still over the course of only two decades that YouTube has been around what is that? That's maybe is that like 10,000 days or something like that? Let's see. 365 * 10 would be 3,600 * 20. So that's like

7,000 days, right? So a billion people, there's only 7,000 possible values. So the other thing that you could end up doing, I'll just copy this down to a new spot down here, is what you actually would end up with if this data is

sorted. So this means it would have assumes it would have to be sorted by date. So you actually end up with and often this is the case when you have analytics data a bunch of repetitive values. This might not be the case for

addresses, right? Like technically you still would probably have some repetition in there because addresses change hands over time or multiple people can live in the same home, but there's going to be less so because there's, you know, millions or billions

of different addresses out there, right? But for sign updates, there's a lot of repetition. And so what you could actually do is do sort of a compression optimization. And you could use different compression techniques, but like a really basic thing would be I'm

going to plop this down. And then I'm just going to say like this, you know, repeated time 4. We should put a space there. So that time 4. And then 1026.

How many were there? Time two. And then 1027. I think there were three of those.

So times three. Let's get rid of these here. So basically I have I have both saved disk space. But then also this would facilitate my analytics query running faster because I've kind of already compressed all of this into how many

people on 102524, how many people on 102624. Uh so that the query itself could run faster because it has to do less IO. and I've kind of already done some like pre-agregation of the results. So that is kind of one of these fundamental

trade-offs, right? If I know my goal is doing lots of analytics, figuring out what day of the year people sign up the most, figuring out what parts of the world, right, I could do analysis on the addresses, figure out what parts of the

world is are most popular for YouTube watching, and then I could even correlate that to the time of day, right? like you know what what's the country watching the most YouTube videos at 11 p.m. ST, right? Um, if I wanted to

do analysis on email domains, I could even join across tables and do analysis. Um, often times this rowbased layout, which works great for just generally powering your application, but it's not necessarily the best for analyzing user patterns and trends and all of this uh

in your app. So this is one of the big reasons why there's a whole separate category of databases that optimize for this kind of work versus optimizing for computing uh analytics on what you're doing. And there we go. That's the difference

between these two. There's a lot more detail into like how these are implemented and and just looking at this is an oversimplification because there's lots of ways that you can optimize this and do this. There's lots [snorts] of

different ways that you can optimize this. There's different trade-offs. And again, there's ways to sort of make a hybrid approach, but this is the basic like trade-off between analytics and OOLTP.