 All right guys, let's get started. Again, hit it up for DJ Drop Tables. How was your weekend? Friend Diamond C got to the hospital for Huffin Whippets. Who's Huffin Whippets? Yeah, it's like, I don't do that stuff, but you would just, you'd Huff too many Whippets over the weekend, but I'm taking the hospital. Don't do that. That's ****. Okay, all right. So other announcements, other than Huffin Whippets, is that homework one is due on Monday at midnight. It should be spent on grade script. I want you to have already completed it. Who has not started Wednesday? What did I say, Monday? Yeah, today's Monday, Wednesday. When everything 11th is, right? Who has not started? Start. Just to make sure, okay? And the other thing, we will be releasing project number one on Wednesday as well. Again, the lecture on Wednesday will be all about what you're supposed to implement in project one. And then so at the end, we'll talk about those sort of logistics of how you're gonna go about and do this in the source code. And then again, that'll be spending on grade scope as well. Okay, all right. So the other things that might be interesting to you are we have some upcoming database talks that are sort of somewhat relevant to what we're talking about in the course. This Friday over in the CIC building, we will have a talk from people from Salesforce. This is public. Salesforce is building a brand new database and a distributed database system based on Postgres. And a lot of my former students, people have taken this class, are now working on it in San Francisco in the brand new buildings, which are amazing. And then next week at the database group meeting on Monday, we'll have Ankar Goya who is CMU alum. He was the former VP of engineering at MemSQL, which is an in-memory database that we can talk about later in the semester. So he has a new startup doing video analytic database stuff and he'll be talking on Monday next week. And then the following Mondays or two weeks from now we'll have somebody from Vertica come give a talk. So Vertica is a column store database system, one of the more famous ones that was invented by my grad school advisors and got sold by HP and they got sold off to a holding company a few years ago. But believe it or not, they actually have an office in Pittsburgh. And so he's gonna come give a talk about what kind of stuff they're doing here. What are the newer things Vertica is doing? So if I say they were a column store, it won't make sense right now. It should make sense by the end of this lecture, okay? Because we'll describe what that is. And I actually tried to get it up and running to give a demo, like too many installation errors I gave up, okay? So again, these are all free to the public. There's pizza at this one and this is just like fruit. So you can plan your meals accordingly. All right, so last class we started talking about how we would want to design a disk-oriented database system. And again, I said a disk-oriented system is one where the data system assumes that the primary search location of the database is on disk. And so we spent time talking about how we're actually gonna organize the database at different levels within files, within pages, and then within those pages, within tuples. And so the reason why we wanna do all this is because we wanna be able to support databases that are larger than the amount of memory that's available to us on a single machine. And yes, I know you can go distribute it. You can go across multiple nodes. For now we can ignore all that. Just saying you have a single box. How do we bring data in when we can't fit it all in DRAM? And so we finished up talking about slotted pages. This is just a quick refresher. So a slotted page was how we're gonna organize tuples inside of a page so that we can move things around and we start packing in as many tuples as possible. So we have the slot right at the top, the fixed and a very length tuple data at the bottom. And we just keep adding things from the end to the beginning and from beginning to the end until we reach the middle and we don't have any more space. And so I said, this is the primary way most database management systems out there that are row store systems, which again I'll explain what that is in a second. This is primarily the way most database systems actually do this. But it's not the only way and we ran out of time or we didn't discuss the other way and so I'm gonna briefly talk about that. So just again, this is putting in context. Most of what we'll talk about this semester will be this organization. The database system you'll be working on for your projects will use this type of organization. But again, it's not the only way. Another way is to do is called Log Structured File Organization. So the way this works is that instead of storing the full tuple inside our pages, we're instead just gonna store the information about how that tuple was created or modified, right? So what I mean by that. So let's say in our page, we're just gonna start appending these log records and I'll think of it like log records like a text file that are read by humans. Think of it as a log record that's some binary representation of what the change was. So we're gonna record like I inserted this tuple, I updated this tuple, I deleted this tuple, right? And we just all we have to do is just keep appending every time we fill up the page, we go create a new one and start appending more log records to that. Anybody think I guess why you'd wanna do something like this? Yes? Is it easy to roll back? Is it easy to what? Is it easy to roll back? It's easy to roll back. Potentially, yes. Yeah, if I have a thousand columns and I update one, if I need to roll back, I didn't blow away the single update record. Yeah, that's one. Yes? It's faster. She says fast writes, absolutely, yes. So remember we said that in especially it's been into this hard guys but even modern SSDs, it's much faster to do sequential writes and sequential reads or sequential access than random access. So if I'm back in this mode and let's say I update 10 tuples but they're all on different pages, now I have to go write and update the tuple on across 10 different pages. But if I'm doing the log structure organization then I just put my 10 writes into my single page and I can write that out in one go. So you see, so this idea is not new, like it came out in the late 1980s or late 1990s, log structure file systems or log structure mergers trees but it's really probably in the last 10 years that this has taken off. In part this is because there are, things like HDFS or S3, there's all these distributed file systems where they're append only. You can't do random updates, you can only keep appending records. So this style of storing your tuples works great for that. So what's one obvious downside with this? She says redata, absolutely, yes. So if I had to read a tuple now, I got to go back in time and look at the logs and try to figure out what does the tuple look like? Wait, what was the final result of the tuple? So if I'm updating some tuple here and I have 1,000 columns but I don't update one of them, I got to go back and try to find where it inserted, updated the other 1,000 columns to put it back into the form that you want. So there's ways to sort of speed that up. You can build indexes and say, well, if I'm looking for a particular tuple, here's how to jump to the particular offset in the log that has the data that I want. Or another thing you could do is say, just go and actually replay the log and compact it down into just one record per tuple. So I can take all of these guys and then convert it back into almost their tuple form. So as I said, this is more common and more recent systems. Some of these you probably have heard about, HBase, Cassandra. There's a bunch of these distributed systems that are out there that are written in Go, things like CockroachDB, where they're all using RocksDB as the underlying storage manager. So the distributed execution layer is all in Go, but then underneath the covers, RocksDB is in C++. And so rather than writing your own storage manager, they just rely on this as an embedded system. So RocksDB came from Facebook. Facebook, actually RocksDB is originally based on LevelDB. LevelDB was written by Google. Then Facebook took it. First thing they did was remove Mmap, right? And then they really re-released it as RocksDB. So LevelDB is still out there, but pretty much everyone uses RocksDB. So again, like so we're not really gonna cover this the rest of the semester. It'll show up when we talk about distributed databases later on at the end. But for our purposes, we'll just assume that we're dealing with entirely slotted page systems, okay? All right, so for today's class, we wanna now go a little bit deeper and talk about how we're actually gonna represent the data in tuples. So again, we said the database is represented by a bunch of pages. So then we discussed how to break up the heat file into pages. And then with each page, we talk about how to represent the slotted array. And then we said roughly inside each slotted array, you have these slots, then you have your tuples. I have a header. And now inside the tuples, we wanna say what does the data actually look like for individual attributes or columns? How are we actually gonna represent that? Then we'll go on and talk about how we actually store the metadata about what our tables look like. And then we'll talk about the storage model, the row store versus column store stuff. Okay, so at a high level, a tuple is just a sequence of bytes. It's just a byte array, right? And it's up to the database management system to be able to interpret that byte array and make sense of it and say, oh yeah, this is an integer, this is a flow, this is a string care attribute. So that's essentially all we're doing here. We're just organizing our tuples as these byte arrays. And then when it comes time to execute a query, we need to interpret what's actually in those byte arrays to produce the answer that we're looking for. And so this is what the catalog stuff we'll talk about in a second. This is how they're gonna figure out, oh, I have 10 columns. First one's a 32-bit integer. The next one's a 64-bit float. It uses that information to decide how to interpret and decipher those bytes. So the way we're gonna use, for most database systems, the way we're gonna represent data is for fixed-length things like integers and flows, is usually the same way that we would represent this in C or C++. This is usually defined by what's called the IEEE 754 standard. Who here has heard of that before, the 754 standard? All right, a little bit less than the last year. So the IEEE 754 standard is basically, for the industry, it's the specification of how to represent numbers in CPUs, like integers and floats and things like that. How many bits? Is it big Indian, little Indian? Have the two's complement in the front? All that is represented in that standard. So for fixed-length types and integers, big ints, small ints, tiny ints, and then float and reels, we'll just follow the 754 standard. We'll discuss in a second about the fixed-point decimals, but basically these are floating point and then these are fixed-point and this is something we in the database system will have to implement. For variable-length things, var-chars, var-binary, tex, and blobs, typically there's a header that says, here's the length of the blob I'm storing or the variable-length field I'm storing. Maybe it checks some if it's a really big value and then you have the sequence of bytes. This is different than representing strings and c's where you have the null terminator character or instead you have a prefix that tells us how big it is actually gonna be. For dates and timestamps, this varies wildly across different database systems. There's no one way to actually do this. Most of the systems usually just store the number of seconds or microseconds or milliseconds since the Unix epoch, which is like January 1st, 1970. For Windows, I don't know what they do. And so in a bunch of systems too also, you can say, oh, I want the date without the time or I want the time without the date. Only the cover is that it's still gonna store the full timestamp. It's just the API that you use to access that data knows to strip out whatever part you don't need. So some systems will actually just pack in just the date and store that as a smaller value. A bunch of systems actually don't do anything. So again, this is something that you'd have to implement in our database system. This is something we implement in our database system. But for the fixed point values, it's what does rely on whatever C++ gives us, which is the underlying hardware. So the thing we're gonna go talk about now that's more interesting is again, how do we actually compare these two, the fixed point versus floating point decimals? So if you wanna have floating point decimals or variable precision numbers, these are inexact numbers that the CPU gives us or like your C++ gives us. Like if I have a C program and I call a declared variable, float whatever and give it a variable name, that's what we're getting when we declare a real or double or float in our database system as like the SQL type. Again, this is specified how you actually represent this like the decimal point and the scope and the precision, all that's defined by the 750 board standard. So these are gonna be much faster to execute or to operate on than the fixed point decimals that the database system provides because the CPU has instructions to operate on these very efficiently. It's one instruction to take two floats and add them together or subtract them. But when we talk about dealing with the fixed point ones, that's a whole bunch of stuff we have to write and that's gonna be way more instructions. So this sounds like what we'd wanna use, right? Cause it's fast. The problem is though, there's gonna be rounding errors because the 74 standard, like there's no way to exactly store decimals in hardware. So they have to approximate this. All right, so here's a really simple C program. I normally don't like the show code in class other than SQL, but this is simple enough I think you should be able to comprehend it from your seat. So all we're gonna do is we have two floats, X and Y, and then we're gonna print out the value of X plus Y and then we're just gonna print out the constant 0.3. So you pick your favorite compiler, I use GCC and when you compile it, you get this answer here, right? That looks, you know, that's correct, right? That's what we would expect. But all I'm doing is just doing, you know, percent sign F. I'm just asking the language just to print out the floating point and let it do whatever rounding it wants to do. When you specify what precision you actually want, so I'm gonna go to 20 decimal points, then you see that you get a totally different number. Same exact code, same exact values, it's just when I represent it in a human readable form, now I'm seeing I'm way off, right? I can't even get 0.3 correct, right? And this is because the hardware can't exactly represent floating point numbers to, you know, precisely, right? So again, this will be faster for us to execute but we're gonna have rounding errors. So now, you know, this may, you may think, all right, 0.3 in my little example here, who cares if there's a rounding error? But if it's your bank account, then you start to care, right? Or if it's a scientific, you know, instrument where you're trying to send something into space, these rounding errors cause real problems. So to avoid this, you use what are called fixed precision numbers or fixed point decimal numbers. So again, these are something that the database system has to implement to represent these values. It's a bunch of extra code that can take care of all the, you know, arithmetic operations or aggregations you normally wouldn't wanna do on any kind of number, right? So the way, you know, I'll show how Postgres is gonna do this in a second but the basic idea to think about this is you're gonna store the value as like a var char, the actual like human readable representation of the value and then some extra metadata to say, here's what the decimal point is, here's what the scope is, here's the rounding information, right? And that's all packed in with the tuple itself just as part of that biter rank. So I always give this demo every year of Postgres and SQL Server, right? I normally just give it for Postgres but we'll try it for Oracle and SQL Server as well. So let's see what the performance difference is from these different, for these different types, let me turn this off. Is that readable? All right, so what I've done is I've created a, I wrote a simple Python script and all it did was create a giant CSV file that has 10 million rows of two floating point numbers, right, that's all it is, just random numbers. So I can load this, I'm gonna create two tables in Postgres, I'm gonna create one that uses reels and one that uses the fixed point decimals. All right, so there's one for reels, there's one for decimals. And then Postgres has a nice command called copy that will take a file that's on local disk and then take the output and write it into the table. Various database systems have various commands and SQL Server is called bulk, in my SQL it's called load into whatever. Oracle was a pain to set up, but I got it working. So now what we're gonna do is we're gonna run a query that just takes the two numbers and adds them together. So let me turn on timing as well. And then because this is Postgres 10, Postgres 10 added support for parallel queries. So like, you know, take a single SQL query and split it across multiple CPUs and run them in parallel. So I'm gonna turn that off as well just so we see like the performance of a single CPU. And we'll do this for all the other systems as well. So let's see how long it would take if I do it with the reels. So if you've never seen this, oh, let me go back to the syntax, sorry. So, up here it says explain, analyze. So if you've never seen explain, what explain does, you put it in front of any SQL query and instead of actually running a SQL query, it tells you what query plan it's gonna use to execute this, the query. It doesn't actually run, it says, here's what I'm gonna do if I ran it. Different data systems have different syntax. This is what Postgres and MySQL do, right? We'll explain what a query plan is, we'll explain what an optimizer is later in the semester. But basically what happened, you know, just saying like, you wanna run this query, here's how we're gonna do it. So, but if I add the analyze clause in front of it, then this is actually gonna give you the query plan and also run it for real. So you see that it basically took 1200 milliseconds, so 1.2 seconds to run this. And this to show you, if it's not a caching effect, I can just keep executing over and over again and the performance, I got a little faster because it got into cache, but it should stabilize. Yeah, about 800 milliseconds, all right? So let's do the same thing now for the decimal one. So 2.4 seconds, I run it again, should get a little faster because it's in cache, not much, right? So again, two same values, same data set loaded at different data types, but the one query is twice as slow because we're doing all this extra stuff to deal with the rounding and other things, all right? And you can see that, let me see if I try to run this again. Are they getting the same values? All right, they're getting different values here, right? Because there's some rounding issues. So we can try to cast this as a decimal and then it'll be human readable, right? So this is much different than this one here, right? This is one and this starts with a nine. So the real one is having rounding issues. So let's try the same thing in SQL Server. The data's already loaded, so we don't need to bother loading it again. So let me run this. So this will be with the reels, produces a result, told me it took 1.5 seconds. Just try it again to see whether it gets faster, not much. And then I'll run the same thing now with the on decimals and it should be slower. Yeah, twice as slow. Run again, just to prove, right? And this little max DOP, it's a degree of parallelism. It's basically telling SQL Server again, run it with one thread. The last one I'm gonna show is Oracle and I had a breakthrough this weekend. I figured out how to get the up key to work, so it was not by default. All right, so it's already loaded. We'll do the same thing. Where's it, Oracle? So we'll turn timing on, run it with the reels and you get 0.53, run it with decimals. The same, slightly faster even. So the way what's happening here is that Oracle actually gives you the fixed point decimal no matter what. Even as you want the real or the decimal, it always just gives you the decimal, right? And before you say, oh, look how much faster Oracle is than the other ones, again, for this one here, like I didn't turn off multi-threading. But it also looks like it's rounding off a lot, right? This looks way off than what we'd expect from Postgres and SQL Server and that's because Oracle has this thing where if the size of the output is not, doesn't fit in whatever characters you specify, but it's numb with thing, then it rounds it for you automatically. Took me a while to figure that one out, but here's actually what you get when you have the real number. So that looks like what we'd expect. So again, this is something that, just be mindful that this is, we have to implement it in our database system. This is not something that will magic go faster. It's not something that we can rely on hard to provide for us. Yes? I'm right, I was gonna, so does it do like rounding along the way or does it only round at the end? This question is it doing rounding along the way or is it only at the end? As far as I know here, for this stupid numb with thing, it's rounding on the client side. So the server is giving you this and then it rounds when it lands on the client. Why? For whatever reason, I don't know. Right? And in Postgres and MySQL, you can, so Postgres and SQL server, like you can specify the round. There's a round function we could do on the server side. So I think we can do something like this round. And then you say to what precision you want. So like two. Nope. Difference systems have different things. I think that's MySQL syntax. I don't know what Postgres. All right. So you do, in your application, you wanna do it client side, sorry, server side. You want the server to do it for you. You don't wanna assume the client's gonna be formatting whatever for you. Yes. So it looks like in the Oracle one that the decimals is giving the same value as the reels in Postgres? Let's see here. So his question is, it looks like Oracle is giving us the value of the real and not the floating point. Can we do something 1538? Yeah, hold up. It was three. And then in the Oracle. Oh no. That's Oracle. Okay, never mind. That's SQL. Why in the Oracle does it look not equal to the? Let's try SQL server. So that's, we'll assume that's correct because it's SQL server. So that was nine something. And then let's see what this gives us. Nine something. It looks the same, right? Yeah, that's different than what the reels gave us. I think reels was giving us like 7.7 and this is 7.5. So going back to Oracle. All right, so that's Postgres. Too many terminals. Yeah, but it's the same. I don't wanna decipher this live, but thank you. So maybe that is, it's always a decimal. Sorry, it's always a real, not the fixed point. Okay, I'll double check that. I declared, yeah, definitely declared it as a decimal. All right, let me figure out what's going on. I'll place it on Piazza, okay? Any other questions? All right, cool. So what is Postgres actually doing? Can Postgres, SQL server and Oracle are not in the source. Postgres is, we can actually look at it. So this is actually from the Postgres source code, version 9.6 I think. And so when you declare a fixed point decimal, this is what it stores. It stores this struct. So again, you have all this extra metadata about where the decimal is, what the sign is, and so forth. And then this part here, as I said, this is just a string representation of what the real value actually is. So then at runtime, they know how to take this and decipher it based on what these values are set to, to ensure that you have the correct computation. So now, why is it running twice as slow? So when you actually get and look at the source code to say how it's actually doing addition, you see it's not just one instruction, number plus number, it's just giant switch statement, one check for the stuff to try to figure out if it's negative or non-negative, it's zero or they're equal to each other. So we're executing this for every single time we compute those number plus number. Whereas if it's a real, if it's a floating point number, it's one instruction on the CPU. So we don't have the source code for SQL Server and Oracle, but I guarantee you they're doing something similar. Roughly. Okay, so this is clear. So if we don't wanna lose data due to imprecision, we use a fixed point decimal. But this is something we have to implement in our database system for us. Okay, so now we wanna talk about what happens when the value of the thing you're trying to store is too large and doesn't fit in a single page. There's two ways to do this. So in general, as I said last time, the size of a page is gonna be fixed throughout the entire table, mostly throughout the entire database. This is something you set when you turn the system on, you say I wanna have four KILI pages or eight KILI pages. DB2 allows you to play around with the page size per buffer pool, but in general, let's assume that's the case. So now what do we do if the thing we're trying to store doesn't fit in a single page, right? Well, an obvious thing to do is have what's called an overflow page. So basically in our table, say this value, this attribute C here doesn't fit in the page, so we'll just have a pointer now to some other overflow page that'll have the data that we want. So this could just be another record ID, like a page number and an offset to tell us where to find this particular data that we would need. So then if we now have a query and we need this attribute or value as part of the output, we'd have to follow this pointer and go bring that page in, copy the data out and produce it as an output. And now if this data doesn't fit in this page by itself, too, it can have another overflow page pointer to some other page and we just chain them all together to produce the output that we're looking for. So different data systems have different names for this and Postgres is called Toast in SQL Server and MySQL, the card overflow pages, and they have different specifications of when they would actually use something like this. So in Postgres, if the value trying to store is larger than few kilobytes, then it always goes to this other thing. In SQL Server, it's just, if the tupper doesn't fit in the page, it pulls it out and puts it into another page. And MySQL is half the page. So the reason why you'd wanna do something like this is because you get all the protections you normally would get when these overflow pages with your regular data, meaning if I'm writing to this overflow page and I crash and come back, I don't wanna lose anything, right? There's other optimizations you can do with these overflow pages that aren't easy to do in the regular slot of pages as well. Like in Postgres, for example, since most of the time these overflow pages are read-only or read-mostly, like thinking of like on Wikipedia, you update an article or update an entry, but most of the time people are just reading it. So therefore, I could just compress this when I put out the disk or keep it in memory and because most of the time I'm never gonna have to decompress it to update it. So there's a bunch of optimizations like that and they all come under the same protections as you normally would with regular data pages or regular tuple pages. Another alternative instead of storing it directly inside the database is to use what's called external storage. And the basic idea here is that we're not actually gonna store the data for this particular attribute in the tuple itself. We're just gonna store a pointer or a file path to somewhere on the local disk or network storage or some external storage device where this data can be found, right? So in this case here, going from C, this could just be a file path on the local disk and say, here's where to find this particular attribute if you ever need it, right? So in the systems that do support this, like Oracle and DB2 and Microsoft, you can't actually modify what's in this file, right? You can read it, but you can't manipulate it, right? Yes? So the overview pages reside in the memory, the similar, the plain similar where the tuples are there and the external finds are in the separate index. So his question is, or statement is for the overload page, this gets brought into memory just like a regular tuple page, correct? In the case of these external files, where do these things reside? So if I run a query, like select star from this tuple here and sees in this external file, if I need to produce it as an output, I gotta go read it in. So it could page it in just like another tuple or other tuple pages. It could be ephemeral, meaning like I'm gonna read it and then immediately discard it rather than polluting my cache. There's a bunch of different ways to do this. But the key thing to think about is like, if someone now outside the data system modifies this file, we'll see that change inside of our data system anytime we go to read it. Because it's outside the control or the protections of our data system, right? So, the only thing I guess, why do you wanna do something like this? Or what's an example of a file maybe you don't wanna store in the data system? Say you're building a website, right? And you have a bunch of video files. You have a tuple that says, this person uploaded this video, you don't wanna store the video in the database itself because that could be gigabytes. So it's very common to see that in those kind of things. The application framework like Django, Node.js and things like that, they have built-in ways to store data outside the database system for images and other things. So there's no set and stone rule to say how big a file should be to put it out as an external file versus keeping it in an overflow page. I'll say also too, for the overflow pages, this is transparent to you as the application. So you don't know that you've gone to an overflow page, right? You can go do what we did before and look at the actual layout in low level information about where our data is actually stored like we did with the CTID and Postgres and the other systems. But most applications don't know, don't care that it's stored in an overflow page. Like I wanted to just get my data out. For this thing, again, depending on how it's actually implemented, you could go through the data system or you could just jump to the file and go get it directly if you wanted to. So there was a paper written almost 10 years ago, over 10 years ago, by some famous data these people at Microsoft. The name of the article was to blob or not to blob. A blob is a binary large object. It's a variable length binary data. And they basically found back in the 2000s that anything below 256 kilobytes you want to store as an overflow page, anything larger than that you want to store in external file storage. We had the guy that invented SQLite come to CMU a few years ago, came and gave a talk here. And he said that for a lot of cell phone applications it's actually better off to store the thumbnails of images, even up the one megabyte inside the database system. Because that was much faster to read those records from the database system because they already had the file open rather than having to follow this pointer to the file system and then do the F open to go get the data. So again, there's no hard and fast rule of what to do. This is also more common when the database storage is super expensive. If you really care about your database you're usually gonna run on high end hardware. And therefore storing a bunch of video files in some really high end enterprise disk is probably not a good use of your money. So you can take these files, chuck it in HTFS or cheaper stores like S3 and then now the data system is not overburdened with trying to maintain your files. So again, it's not just for performance reasons it's the other economic reasons why you want to do something like this. But this paper summarizes a bunch of the issues and that's why I like it. So again, so any questions how we're gonna represent data? Most of the times for fixed length data it's just whatever our programming environment gives us for anything that's verbal length or if you want fixed point precision that stuff will have to implement ourselves. Okay. So now let's talk about how we actually figure out what our tuples look like. So again, this is what the system catalogs are for. It's the metadata about the data, the metadata about the database. What comms I have, whether table names, what indexes I have and so forth as well as some other things like user permissions and security stuff which I don't care about. And then this will come up later on when we talk about query optimization but also internal statistics about what your data looks like. How many unique values do I have? What does the distribution of those values look like? So pretty much every single database system is gonna store their catalog inside itself as just another table. So like eating your own dog food. So I'm gonna store all the metadata about my tables in just tables themselves. And so inside the source code you obviously don't wanna write SQL query to say what's the name of this table because it's chicken for the egg problem. Like how do I do a SQL query on a table to find out the table name if I need to know a table name? So you usually have like some C++ code or whatever your data system is programmed in to wrap around the low level access methods to go access the catalog, all right? So the most database systems will expose the catalog through the standard information schema API. So in the 1980s all these different database systems all had their own way of saying here's my catalog, here's how to access it. And that became real pain in the ass now if you wanna take your application and import it from one database system to another because now all the catalog stuff is different and you gotta rewrite all your code again. So in the ANSI standard and I think also in the SQL standard by now they specify this thing called the information schema that every data system has to support to say here's the metadata about my tables. But we'll see in a second they don't always expose the same information in these tables. All the database systems are all gonna have their own sort of shortcut ways to go get this information as well. So for example, say you wanna get all the tables we have so the SQL standard would say you write it with this information schema dot tables which is just a view on top of the real catalog and you give it the catalog name, right? Or the database name. In Postgres you use slash g and my SQL you show tables, SQLite is dot tables and then again all the data systems all have their own shortcuts. And essentially what they're doing underneath the covers is converting this command into something like this. Same thing now if I wanna get the schema for a table. So again, this is how we do it in the ANSI standard and then the various systems all have their own way of doing this. So I wanna go a quick demo of Postgres and my SQL. Again, just to show you what's actually going on. All right, so again Postgres if I do dash d I get the list of all my tables. I can do dash d plus and get more information and then if I pick a table it'll tell me what the metadata looks like. So here's all the comms that I have. Here's the types I have. So now when I run my query I look at this information and say all right the first attribute is an integer that's gonna be 32 bits. The next attribute is also an integer that's gonna be 32 bits. And then I have code inside to say all right if I'm operating on this tuple what is the schema? I know how to again do the conversion of the raw bytes of the byte array for that tuple and put it into the form that I would expect. All right, so let me see if I can do this for my SQL. I think I destroyed my SQL. Let me do this on another machine. So I can say show tables. It tells me what tables I have. I can say show databases. Same thing, here's all the different databases I have. And then for a given table, I can say describe knobs, right? And I'll just say again, same information. Here's the name of the field. Here's the type and then some extra metadata. So this is my SQL 5.7. The newer version actually stores the tables in the catalog itself or sorry, stores the catalog in the tables itself. In this version here they didn't do that. All they would do for the catalog is just read the directory of where the database is stored and use that to figure out what databases are there and what tables are there. And so we can actually break it or fake it out by putting things that shouldn't be there in that directory, right? So if you go back here, let me split it. So do show databases and it thinks I have a bunch of these here, right? So now if I go back to this machine, log in as root, go into where my SQL stores this data, lib my SQL, right? And roughly you see that there's a bunch of, there's a bunch of directories here for the databases that it knows about, right? There's a database called test, there's a directory called test. So what happens if I call now make directory XXX? Well, when I go back up here, my SQL thinks there's a directory called XXX. So this is a good example of where, if we rely on things external to the database system we can't fully control that. So my SQL can't prevent anybody from going to that directory and putting better at once in there. But it's relying on that to figure out what's in my database. So from an implementation standpoint, it might be easier, but from a correctness standpoint, that's problematic. They may say who's gonna be stupid enough to go create directories to screw around with my SQL. Well, what about other things like I'm writing the files that I don't have the regular protection I would for my regular data, because I'm not logging things correctly, right? We wanna put as much as possible inside the database system because then we can rely on that to perform correctly for us, okay? All right, so that's all we really need to cover for catalogs this semester. Just be aware that there's something that inside the database system we're gonna keep track of what our schema looks like. And we're gonna use that when we execute queries, use that when we build indexes to determine what should we actually be doing. And the way to think about this, the different types is that in the, in the easiest way to implement this, and you'll see this in the bus top code that you guys work on, when you look at the type system, it's gonna be a giant switch statement, right? If the type is integer, do this. If the type is a float, do that. And so you're doing that for every single tuple and that's actually gonna be really slow because there's actually interpreting what the layout should be. And in the more advanced systems, you can actually compile or do cogeneration to compile on the fly, like just in time compilation in the JVM, to actually compile those operations so that you don't have to do that interpretation every single time. My SQL doesn't do that, new versions Postgres does that, but Oracle and SQL Server should do that as well. All the major commercial systems do that. That's not something we're gonna cover in this class, but when we cover query execution, I'll bring that up to say, this is a way to actually make this run faster. All right. So the next thing we're gonna talk about is the storage models, is the first thing that they realize that we've covered in the first lecture is that the relational model doesn't say anything about how we actually wanna store data. Doesn't know about types, doesn't know about byte arrays and so forth. And it doesn't necessarily even say that we have to store all the attributes of a tuple together, either in memory or on disk, right? And so, again, when any time we, so far in the class, when we visualize databases, now I'm saying here's the row and here's all the attributes for it, right, for a tuple. But that may not be the best way to do it for some workloads. So let's look at a really simple database example here. This is actually derived from the MediaWiki software that runs Wikipedia. Like if you go look at the source code, it's all PHP with my SQL. You look at the DDL file, the SQL file, it'll look roughly like this. So we have three tables. We have user account, pages and revisions. And so the revisions table is where we're gonna store all the new updates for every single article. So it's a kind of a foreign key of reference to the user that created, that made the change and then a page ID that corresponds to the article or the page. And this guy then also has a foreign key of reference to say, here's the latest revision for this particular page. So you don't have to do a scan, you just can jump directly to it. Again, this is sort of an approximation, a cleaned up version of what Wikipedia actually does. But for our purposes in this lecture here, it's fine. So there's two sort of general class of workloads we're gonna care about in database systems. There's certainly not the only ones, there's machine learning and streaming stuff, but for now there's just focus on just two. So the first is called online transaction processing, or OLTP. Who here has ever heard that term before, OLTP? Phew, okay, good. So this is usually what you're gonna end up with this type of application, any single time you're building a new application. If I'm building a new website, I'm building a new iPhone app or whatever, you're typically gonna building one of these. And so for transaction, OLTP or online transaction processing, the idea is that this is where we're getting new information, we're ingesting new data from the outside world and putting it into our database system. So these queries can be really simple, they're only gonna read a small amount of data or update a small amount of data, ever gonna be doing the same operations over and over again. So the example I always like to give is like the Amazon storefront. The website you go to when you buy stuff, that's considered an OLTP application because I'm adding stuff to my cart, I'm making purchases, I'm updating my account information. For each of those single operations, they're doing a lot of them because they have a lot of people buying stuff, but for you as one customer, you're not updating a lot of data, you're updating your account information, you're updating things to your shopping cart. So the queries that are running are only accessing a small portion of the database. So the type of queries you would see, again, going back to the Wikipedia example, so here's go get the current revision for given page, here's update my user account to say that when I logged in and here's a simple insert query to insert a new revision. Each of these things are accessing a small number, small number of tuples at a time and what we're doing these things over and over again. So now the other type of workload is called OLAP, online analytical processing. And this is when you've already collected a bunch of data from your OLTP application and now you want to analyze it and extrapolate new information from it. This is sometimes called data science, but that realm of like taking a bunch of data you have and trying to derive new information from it. What's that? Data. Yeah, I mean, it's in the name, online analytical processing. Business intelligence is another phrase for this. Decision support is another one. Big data if you want to call it that, right? Again, in this workload, in this environment, we're not updating data, right? There's OLTP side is getting that new information for us and now we're trying to make sense of it. So a query might be on a Wikipedia example. Say you want to count the number of people that have logged in per month that where their host name ended with .gov. There was a scandal a few years ago where they found members of Congress or having their employees go to Wikipedia and scrub them clean to remove all like, whatever scandals the Congressman was involved in, right? So you want to figure out all the people that are logging in from sitting at the government doing this. So these types of queries are going to be read only. They're going to read a lot of data. Like I'm going to scan the entire table, right? As opposed to OLTP where I'm updating one thing. I'm going to just do a lot of joins, right? In OLTP, you usually don't see a lot of joins. So one way to, again, to grossly characterize these workloads is that on one axis you say, how complex are the queries? Are they really simple? Like, you know, they're only accessing a single table or are they doing complex joins? And then what are the right heavy or read heavy? So OLTP would be down in this end of spectrum. They're pretty simple queries and they're doing a lot of writes. OLAP would be doing a lot of reads but they're more complex. And then there's sort of this new class of workload called HTAP or Hybrid Transaction Analytical Processing. That sort of is trying to do both of them, right? You still want to ingest new data but you want to analyze it as it comes in. You see this a lot in anybody who wants to do decision-making on the fly, you know, as people are browsing websites. You see this a lot in like internet advertising companies. So now, given that we know about these different workloads, now we can talk about what is the right storage model to support these workloads more efficiently? So again, the relational model doesn't say anything about the layout but we can be mindful of this when we decide how we want to build our data system. Yes? So what is the relationship on SQL or new SQL systems? Okay, so this question is, what is the relation of OLAP to no SQL or new SQL systems? So I would say, so, then we don't know what no SQL is. Who here doesn't know what no SQL is? Two, okay, that's fine. So most of you haven't heard of no SQL or new SQL. So these are workload types. So and what you're describing new SQL versus no SQL, those are sort of system categories. So the other question is, what is MongoDB for? What is the no SQL good for? So the traditional no SQL systems, MongoDB, Cassandra, Redis, they would be at this end of the spectrum. They're about ingesting new data, right? Mongo has some support to do some analytics but when we talk about the column store stuff, they're not a column store, they're gonna get crushed by any column store database. You wouldn't wanna do hardcore analytics on MongoDB. You can, we'll support some queries to do this. My SQL Postgres will support some queries that would fall under the OLAP category, but they're not gonna be as efficient as running on a column store system. So no SQL, basically, there was this movement in the late 2000s where all these companies are basically saying, look, Google is making a ton of money and they put out this system called HBase or something like HBase, big table and this thing called Hadoop and they're not doing SQL, they're not doing transactions, they're not doing joins and that's how they're able to scale. So all these people ended up building these no SQL systems like Mongo and Cassandra that sort of fall under, try to follow those edicts or design patterns to support sort of modern software 2.0 or web 2.0 applications, but they would fall under this. Hadoop is OLAP, but big table, Sanjay, MongoDB and those guys are over here. Then what happened is people realize, oh, well, I actually do want transactions, I do want SQL, I do wanna do some joins and that's where the new SQL movement came along and this is what I was working on when I was in grad school and actually if you go read the Wikipedia article for a new SQL, it talks about my system was one of the first new SQL systems and this is because I wrote the new SQL article of Wikipedia, so I could say whatever I wanted. But the idea was they were trying to do, they're trying to do fast transaction processing and OLTP without giving up transactions or giving up joins the way the no SQL guys did. Now there's other no SQL systems you could say are like, there's a bunch of systems out there that don't do relational model that you wanna do analytics on but primarily most people think of these guys down here. I would say that the claim no SQL, first they're like, oh, we're not gonna do SQL, SQL's stupid and then it came out, everybody but Mongo now supports some variant of SQL. So then they said, oh, no SQL really means not only SQL but in some of them actually are starting at transactions like MongoDB has support for full-fledged distributed transactions. So all the things they claim that were a bad idea 10 years ago, turns out it is a good idea. SQL's not gonna die anytime soon. People have tried to replace it, right? People have felt it was a bad idea in the 1970s, 80s, 90s and 2000s. It always comes back, right? It's what people want. It's not, I like it because this is what I grew up with but there's certainly ways to improve it and some people have tried to do this but the core idea of declarative language on top of your data is, I think is one of the major contributions of Ted Kahn's work in the 1970s. Does that, that was a long, so luckily, does that answer your question? Okay, we can talk offline about, if you want my opinion of other systems. Okay, so again, everything we talked about so far, when we show rows and tuples or tuples, and this is why I don't wanna use the term row when we talk about a column store, doesn't make any sense. But every time I showed a tuple, I showed it as a row and this is called the N-Ary storage model. And so basically the idea here is that we're gonna take all the attributes for a single tuple and we're gonna store them contiguously in our pages. Again, we can have the overflow pages for larger objects but in general, it's all gonna be aligned together, right? So this is gonna be an idea for OLTP because again, the amount of data we're gonna access is gonna be small in these OLTP queries and it's gonna be accessing for single entities. Go get my account information, go get my orders and I want all the data for that, for my account. I don't care about all the other millions of customers, I do want my information. So for a row store, that's actually really efficient because I just jumped to the one page that has my data, I get it and I'm done. All right, so let's see what this looks like. So again, using the Wikipedia example. So say this is a single page, so we have a header. Again, assume this is in the slotted page format. We have our header and then we have the user ID, user name, user pass, host name and last log in. And so only after we have the last attribute for our tuple, then we have all the other tuple data. Right, everything is contiguous to each other. So again, so now if I store this in my database, I can represent this in a single page. So now if I have a query that says, get all the account information for a given username and password, I can do a lookup and index, which we'll cover in lecture seven. But that's basically gonna tell me, hey, here's the page ID and slot number that has the tuple that you want. I do one seek, I do one read to fetch that page, bring it into memory and I can jump to exactly to the location that has the data that I want. Right, so again, all the two workloads are gonna look a lot like this. Go getting the data for single entities or small number entities. So having all the data for a tuple contiguous to each other is the most efficient way to do this. Right, same thing if I wanna do an insert, my insert query is gonna have all the data contiguous anyway, so I just find a free slot and just write it all once. And now I can flush this out and there's one disk right, ignoring the log. Yes. What purpose is it useful to segregate that data into different pages? So his question is, for what purpose would it be useful to segregate the data in two pages? Okay, so let's look at why this is the bad idea for some queries and then we'll see why it's a good idea to segregate it. When we talk about the decomposition storage model, the column store stuff. All right, so let's look at an example where the row store is a bad idea. So let's take that query I showed in the beginning where we wanna get all the people from the government that are modifying Wikipedia pages when they shouldn't. So if we break down this query, right, we look at it, we realize we're actually gonna need to touch all the data, right, because there's a full sequential scan across the user account table to find all the people that, you know, look at all the user accounts and look at their host names. I assume we don't have an index. For OLAP, you usually don't have indexes for these types of queries. All right, so now if I go read, say the first page I read is this one. Again, we're in a row store here. So if I look at my query, I wanna first do a where clause and look up the host name and try to match it on my pattern if it ends with .gov. So that means I basically just want these values here. So as I'm scanning along, I look at my catalog and says, well, I know I have, for this table, I have five attributes and you want the host name, so that's at this offset. So I go read the what I want. And then I get to the end and then I'll jump to the next one and so forth. The other part of my query is that I have this group by where I wanna aggregate them together based on the login because I wanna get it per month. And then produce that as my final output. It's the count number of government employees that are logging in for each month. Now, so to satisfy this part of the query, I only need this column here, this attribute, just the last login field. So what's the problem? What's that? Exactly, so I have to read this entire page. Again, I can't, again, remember I said in non-volatile storage devices, it's a block-based API. So I can't just say, just get me exactly this data. I gotta go bring in the entire page. So now you have all these columns here that I never even accessed at all in order to execute this query, but I had to bring it into memory from disk to in order to get the two columns that I actually needed. So doing analytics on a row store is gonna be painful if you have a lot of data, right? My example here, I have six pages, who cares? But if I have, you know, petabytes of data, and in this case here, three out of the five columns that I'm bringing in or attributes I'm bringing in is useless for the particular query, then that's a bad idea. That's the inefficient use of the hardware. So again, the NRE storage model, the row storage model is we really fast for any inserts or updates or deletes when we're accessing the entire tuple, right? We want all the attributes for a single tuple and there's usually just a small number of tuples at a time. But if we have to do analytical queries in the ODOT workloads, when we scan large portions of the table, then this is gonna suck because we're gonna bring a bunch of data in that we may not actually need for our query. So now it should be sort of obvious that this is where the column store itself comes in, where instead of storing all the attributes for a single tuple together in a single page, we're actually gonna store all the values for a single attribute across all tuples in a single page. Right, so it's, again, this is where the column name goes. We're just storing all the columns together contiguously. Sorry, all the values within a single column contiguously. So this is gonna be fantastic for our ODOT workloads where we're read-only and we only wanna read up a subset of the attributes for a given table, right? So again, going back to our example here, so this is what it looks like as a row store, but so say now we just take every single column and we're gonna split that up and now within a single page, we have just the data for that column. So here's all the host names together and we have the same thing where user ID, last log, and the other attributes for this table here, right? So forth like that. So now I come back to this query we had before. So the first thing I need to do is do my where clause on host name. So now I just need to know, all I have to do is go bring in the host name page. Wrong color, but ignore that. I just bring the host name page in. I can then rip through that quickly and say, look at every single host name and do my predicate. Now I have a bunch of tuples that matched. So then I go back and bring in the last log in page and just jump to the locations that I need within that and then get the last log in information that I want to produce my answer. So say in a real simple case here that the last log in page is one, the last log in data is one page, the host name is another page. So before I had to scan all the pages and this one I didn't only have to scan two. Again, think of an extremes, if I'm talking about billions of pages, then that's a big difference. Yes? Are you storing the primary key with each of these? So this question is, how do I find the primary key with each columns? Your real question is how do I figure out, I had the host names that match, how do I then go look up in the last log in column and figure out how they match? Next slide, perfect. Any other questions? So those are all the stuff we can do with this that we're not gonna cover in this class, but the bunch of other advantages you can get, and actually if you come to the Vertica talk in two weeks, Vertica is super famous for this. So with the row stored model, all the values within, or the attributes within the tuple, they're all roughly different domains. This is gonna be a user name, this is gonna be host names, this is gonna be the last log in, we're just gonna be like a timestamp. It's all sort of jumbled together. And so if I can then pack them all this data together that are the same column, now there's a compression techniques I can do because I know they're gonna be all the same type. So let's say that I'm storing temperatures of the room, and it's 70 degrees now, maybe 70.1, 70.2, like it's not gonna fluctuate that much. Instead of storing that full temperature every single time, what if I had to store a small delta of what the base temperature was when we first started taking measurements? And now I don't need to store the entire value all over again, I just store that smaller representation. I think of like if you run like GZIP or Snappy or whatever your favorite compression algorithm is, you can't compress an MP3 really well because it's already sort of compressed. But if it's a text file, then you can compress the hell out of that because there's gonna be a bunch of characters repeating over and over again. So if you have repeated values in your attribute, then you can compress the hell out of it and get much better performance. So now when I go wanna go do a read, again, with every page fetch, instead of maybe getting 1,000 tuples, I could get like 10,000 tuples because it's in compressed form. And some systems actually can operate directly on compressed data without needing to un-gopress it, which is a big win. Okay, we don't cover, we're not gonna cover compression in this class. We spend a whole lecture in the advanced class, but I'm happy to talk about more about it if you want. Okay, so now the his question is, how do I figure out I had a match in one page? How do I find a match in another page? So in general, there's two approaches, but everyone pretty much does the first one. So the first choice is to have fixed length all sets. So what that means is that for every single value in a column, it's always gonna be a fixed length. So again, think simple, a 32 bit integer. So each of these values would be 32 bits. So now if I have a match, say in this column at offset one, and I need to find the corresponding tuple in this column, I know that, say this column is also 32 bits, and I can just do a simple arithmetic and say, I want offset one times the size of each attribute, and then I know exactly where I need to jump to, or translate that to the row ID, or the page number and slot number that has the data that I'm looking for. So this is probably the most standard approach. And of course, now the tricky thing is say, well, what if I have a bunch of strings that are very length field? Then you get into like, all right, can I compress it to a fixed length field, or can I just pad it out so it always fits in whatever the max size is? Different data systems do different things, but overall, this is the most common approach. The other approach, which I forget, there's like one system that does this, which I think is a bad idea. They might have gotten rid of it, but I forget who it is. Where you actually store, for each value in the column, you store the primary key or an identifier for it. So then you say, all right, I'm at, for the column one, I'm looking at tuple one, I don't want to get to tuple one in column B, I have another map, or another way to do a lookup and say how to go find the offset location for that particular tuple in this column. Of course, obviously this has huge storage overhead because you're storing this extra 32-bit or 64-bit value, or ID, for every single value, which is wasteful, right? All right, so the advantages of the column store is that we can reduce amount of waste IO for these OLAP queries, because we're only reading the bare minimum amount of data we actually need. We're not bringing in things we're never gonna need at all. We'll get better compression, we'll get better query processing, which we will cover in a few more lectures, because we know we're operating on columnar data. The downside is obviously that for anything that needs access to single tuple, it becomes more expensive, because now you essentially need to put together the tuple from the different columns back together, whereas in the row store, it's all just in one location for you. And then any time you update or sort of delete, this becomes more expensive because, again, because it gets a split of OLAP. So I would say that column stores are not a new idea. They go back to the 1970s. There was like this Swedish military division built this thing called Canter, which essentially was, they didn't call it a database system because they used different language back in the 1970s, but if you go sort of read between the lines, at its essence, it is a column store database system. It was never released, never made public, it was only this internal project, but that's the first known implementation of a column store. The 1980s, there was a paper that described the decomposition storage model in more formal details to say, what's the storage format look like? What is the implications of having this storage model? The probably most famous commercial implementation, one of the first commercial implementations, was this thing called Sybase IQ. It was an in-memory column store that Sybase released as an accelerator for their regular row store database system. So it had a new extra work to keep the two in sync. It never really got big adoption because again, it was sold as an add-on to their row store database rather than its own standalone thing. But it was the 2000s when the column store stuff really took off. Vertica, again, was founded by Mike Stoenbraker, the guy who met at Postgres and Ingress. That was his company that got bought by HP. Vector-wise is an in-memory version of Mone ADB. Mone ADB is out of Europe, it's an academic project that's still around today. I see these as sort of the first column store systems that were made in 2000s, but then it quickly became obvious that this is the right way to build database systems for analytics. So pretty much everyone now has their own column store system. And actually, I wanted to give a demo of Vertica today, I couldn't get it running. I did get the MariaDB column store working. It's definitely a column store, but it doesn't mean it's actually good. So there's a bunch of stuff we'll cover as we go along for query optimization and query execution. Just because you're a column store, it doesn't mean you're magically gonna go faster. I was actually able to get Postgres to beat the column store for analytical queries because of how you actually get the queries, how you actually look at the data and what the query plan looks like. So there's a bunch of extra stuff we have to do that we'll cover throughout the semester that you want to do if you're a column store, that not everyone does. Okay? So any questions about column stores? So if you go off and graduate from CMU and you want to do analytics and someone's like let's do it on Postgres, but it's a row store, don't do that. There's enough column store systems that are out there that you want to look at. They're not cheap though, at least for the commercial ones, but there's some decent open source ones. Okay? All right, cool. So the main takeaways from this is that as we show the underlying representation of the storage of the database is not something we can just sort of put in our storage manager and not expose any other part of the system. As we go out the rest of the semester you'll see that a lot of times I'll say like, all right, this is the way to do it if you're a row store, this is the way to do it if you're a column store. And that's because, again, if we know the data system knows more about what it's actually doing, what the data looks like, it's going to make better decisions, better design choices, and in order to get more efficient execution. The other thing to also remember too is basically for OOTP, you want to use a row store, for OLAP you want to use a column store. This simple rule will carry you out the rest of your life and make your life easier. All right, so now, the last two classes we covered this problem here how to actually represent the data in the database. So now on starting on Wednesday, we'll talk about how do we actually bring the data in and bring it to memory and manage that. Yes? Is there any good reason to do a mix of the two? This question is, is there any good reason to do a mix of the two? So we actually built our data system that did a mix of the two. We threw that away and started over because it was a bad idea. It was too much engineering overhead. There are some database systems that will give you both. They'll expose like, so MemSQL for example, you can say create this table and it's a row store. Create this other table and it's a column store. And they have essentially two separate storage managers, two separate execution engines to operate on them. So those are sort of called hybrid storage systems, hybrid data systems. We were all in. I thought that was a good idea. I think it's a bad idea now. For end memory, we actually can do, we think we can do fast enough transactions on a column store. For disk it's a little bit more complicated. So there are systems that do both. They're not, they didn't really take off as much. So usually you see things like, you'll have, you could have a single interface where they have, you write one query and then underneath the covers, it figures out where you wanna go, the row store or the column store side. There's ways to do that but having a single software architecture that can manage both, I think is rough. Why don't we store two copies of the same data? He says, why don't we store two copies of the same data? Great. Think of it extremes. My database is one petabyte. So. Lower data. I don't have slides ready, but like that could easily find. But I can cover this next class. But basically what people do is you have your front end LTP systems and that's running my SQL or Mongo, whatever you want. And then you stream the data out over time to a backend data warehouse. And then you basically can prune out the latest data on the old data on the LTP side when you don't, you know you don't need it anymore. So you see this in like eBay. eBay only retains the last 90 days of auctions. And after that, they prune it out. And that's because they wanna keep the OTP side nice and trim and fast. But then they still retain everything else in the backend data warehouse where they do all the analytics to figure out what people are buying and what they're doing. That's the standard setup everyone does. And whether or not that's like my SQL plus Vertica like two separate database installations or whether it's a single hybrid database like Splice Machine can do this or MemSQL could do this. Depends on what you want, how much money you have and what you're willing to do. I think that what we found for our own system is that building, having a single storage manager try to manage both of these things was a bad idea among other things. Okay, someone brought up testing last time and I really wanna spend time talking about that but we don't have any time today. But again, next class we'll start talking about the buffer pool and hopefully we can talk about testing a little bit at the end, okay? Any other questions? Hit it. Oh yeah, coming through with my shell and crew. Two cent for a case, give me St. I's crew. In the mix of broken bodies and crushed up can. Met the cows in the jam, oh I'll try, yeah! It's with St. I's in my system. Crack another, I'm blessed. Let's go get the next one and get over. The object is to stay sober, lay on the sofa. Better yet, damn I'm sure. I'll be Tim's stressed out, could never be son. Rick is a jelly, hit the deli for a quote one. Naturally blessed, yes. No rappers like the laser beam, the forge and the bushes. St. I spent like a king. Crack the bottle of the St. I, sippin' through those but you don't realize that drinkin' ain't only to be drunk. You can't drive, keep my people still alive and if the St. don't know you're fuckin' can of pain, pain.