 So today's lecture is going to be about hash tables and how they are used in a DBMS. So before we get going, of course we have to talk about some of the administrative things. Project number one will be due on Sunday, September 26th at, as usual, 11.59 p.m. And homework number two, which is about indexes, which we'll cover in the next couple of lectures, is going to be released today. So it'll be up on the website. And that will be due on Sunday, October 3rd, also at 11.59 p.m. Just again, a reminder about the plagiarism warning. Your project implementation has to be your own work. You're not allowed to copy source code from other students or their groups or on the internet. And please don't publish your implementations on GitHub or otherwise make them publicly available because then that allows other people to go track them down and copy them. And again, if you're confused, you can take a look at the academic integrity policy or if you have a question about anything, please contact me or one of the TAs or post on Piazza and just ask for clarification because it's better that we can address it beforehand rather than having to find out afterwards and then deal with it then. And the last thing is the database tech talks that we're having this semester. So they take place Mondays at 4.30, right after class on Zoom. Today's talk is going to be from someone from Google Napa. Next week we'll have someone from TileDB and then the week after that someone from Bodo. So again, feel free if you're interested in the stuff to check it out, join the Zoom. Everyone's welcome. And it's kind of a cool way to see how some of the topics that we're covering in the course get applied in the real world. There's one more thing. I got an anonymous question before class. In the last class I mentioned that the student that had the highest, the most amount of extra credit points from the projects would receive a bus tub t-shirt and I made the comment that it could potentially help when you're on the dating scene. So the question was about whether or not that was due to causation or correlation and the answer is I don't know. We've never collected any data on that so I honestly can't say. So I think what we'll have to do this semester is whoever wins, we'll just have to have you fill out some survey or something at the end so we can collect some data from before and after you have the shirt so we can for future sections of the course we can be more accurate about that so I hope that addresses any questions about that. So in terms of the course, today we're going to talk about or start talking about how to support kind of the DBMS's execution engine to read and write data from pages. So kind of we've been talking about the lower levels of the stack and now we're going to move up the stack to this access methods piece. We've already talked about disk manager and the buffer pool manager and how all that works. So now we're kind of right here in the middle and the two types of data structures. There are many others, especially if you take more advanced database courses or we're looking toward advanced systems. There are many other types of data structures, pretty much the two fundamental ones and the ones we're going to cover in this course are hash tables and trees. So today we're going to be talking about hash tables. So data structures can be used in a whole bunch of different places in the DBMS and they're going to be used for a lot of different purposes. For example, they can be used to store internal metadata. We've seen some cases where we use different either a page directory or a page table to kind of perform mappings between pages in their physical locations or tuples in their physical locations. We've seen kind of that use of them. They can be used for core data storage. So for example, you could have pages or groups of pages organized as hash tables. You could have your actual table organized in some sort of data structure. They could be used as temporary data structures. So you can think about it like during query execution, there are different operations where you might want to build a data structure on the fly and then just kind of use that temporarily. And then it gets used for the duration of the query and then thrown away. So for example, during a hash join and finally we've talked a little bit about kind of how you can use data structures as auxiliary table indexes. So you can kind of have your core data storage in a table file and you can build an index on top of it to make accessing it a little bit easier. So kind of the two key design decisions that we need to make when designing data structures are the data organization. How is the data physically laid out? What are the different trade offs we need to consider there? As well as concurrency. So we've talked a lot about if you have multiple concurrent transactions or queries running in the system, how can you ensure that the data or data structures are accessed or not modified concurrently causing problems with multiple access. So we're first going to focus kind of on this organization piece. Just to simplify the discussion, we're going to assume everything. For the most part, there will be a few cases where we discuss concurrency, but for the most part we're going to assume for now that everything is single threaded and then we'll talk kind of more about this multi-threaded stuff when we discuss concurrency control in later lecture and course. So as I said, today's lecture is going to be all about hash tables. So basically a hash table, I'm sure many of you may be familiar with it from a data structures course or an algorithms course or something. But basically a hash table implements an associative array from some key to some value. So you have a set of keys, you want to map them to some set of values. So you give the hash table a key, it gives you back the corresponding value associated with that key. And the way it does that is it uses a hash function in order to compute basically an index or an offset into some array for a given key. And then you can retrieve the associated value with that key. So the space complexity for this is on. It scales with the size of the number of keys that you're storing. And the time complexity, depending on how we implement it, the average time complexity is 01. And the worst case, time complexity is on. We have to look at every single item in order to find the key that we're looking for. So you may know this from the theory perspective. But in practice, a lot of times what we care about are the constants associated with this. So these are perfectly fine complexity assessments for the abstract idea of a hash table. But if you have, for example, on the order of a billion keys and there's a look up overhead of one second per key or something, even though it's constant time, that one second adds up a billion times. So we really need to care not just about the time complexity of these operations, but specifically about the constant factors that go along with each look up. So the easiest, most basic hash table that you can think about is just to allocate this giant array that has one slot for every element or key that you want to store. So for example, just think about the keys like integers. And we have between 0 and n. And we want to slot each key into an individual slot. And we can do this by modding the key by the number of elements that we're storing. And then we can find the offset in the array. So for example, let's say we have these strings in here. So we have ABC, null, DEF, so forth. The way that we can find these again is to perform the hash that tells us, brings us to the associated values stored with that key. So hash of the key modded by the number of elements in the array gives us the offset that we're looking for. So there are some assumptions that this simple model makes. The first is that you know the number of elements that you want to store ahead of time. Some cases that might be true, I have a billion elements. I have a million elements, whatever it is. That's all I ever need to store in the hash table. In a lot of cases, that might not be true. For example, if you think about a database table that can grow arbitrarily large, so we may need to continue adding keys over time. Another assumption that's made here is that each key is unique. So you can't have, for example, duplicate keys. Of course, in SQL, which is a bag or multi-set algebra, we allow these sorts of duplicate keys. So that assumption isn't sufficient for a lot of cases. And finally, this kind of assumes a perfect hash function. So here, if key one doesn't equal key two, then the hash of key one isn't going to equal the hash of key two. So that means each hash function uniquely maps a key to some position in this array, which it's unrealistic to devise this perfect hash function that's not going to have any collisions. So when we're thinking about designing a more practical hash table, we have these two design decisions that we have to make. And this is what a hash table is at its core. The first design decision is what type of hash function are we going to use? So how are we going to map the large key space, potentially a really large key space if you have really large integers or strings or something? How can you map a very large key space into a much smaller domain, which is the array or storage that you're storing it in? And finally, we want to have this kind of balance, this trade-off between being really fast to compute so we can compute the hash really quickly versus having a lot of collisions. So if you just take, for example, the identity function that's really fast to compute and have to do anything, you just return the key. But if you have a high number of duplicates in your data, then that's going to end up with a lot of collisions near your hash table. So that's kind of the first piece is the hash function. And then the second piece really has to do with collisions, which is what your hashing scheme is going to be. So how do you handle any collisions that are produced by your hash function? And again, there's this trade-off here. In this case, it's between allocating a really large hash table so you can trade off memory or disk, depending on where your hash table lives. But you can trade off space for additional instructions or performing additional work, compute time to find and insert keys. So in both of these cases, we have to consider away these trade-offs. And a lot of the different schemes that we'll look at today will have different ways of balancing these different trade-offs. So this whole lecture is going to be about all the different aspects of building hash tables. We're going to start, again, at the first design decision, which is what your hash function is going to be. And then we'll look at different hashing schemes, first static hashing schemes. So those are fixed-size tables that are allocated versus dynamic hashing schemes, which try to trade off this larger or batch allocation versus more incremental allocation of space for the hash table. So again, the idea behind a hash function is that for any input key, we want to return some integer representation of that key. So we could be taking a key as an integer. The keys could be integers. We want to return an integer that represents some hashed version of that key. It could be a string where you use the different characters in the string to somehow compute your hash function. It could be an arbitrary data type of bytes. So basically what we want to do is just take this input key and somehow return a hash integer representation of that. So you may be familiar with cryptographic hash functions. We don't want to use those for this case. Cryptographic hash functions have a lot of properties that are nice for cryptography or cryptographic use cases. But they're pretty slow, usually, and we don't need those properties for implementing the hash tables inside a DBMS. So basically what we want is a hash function that is really fast to execute. So we want to be able to get the hash of our keys quickly and has a low collision rate. We don't want the hash function to return the same hash values for many different keys. So this is just a few popular hash functions. CRC64 has been around for a while. There are fast implementations of this for modern CPUs. I think it was originally used in networking, like error detection, computing a checksum, error detection and networking. Kind of the number of hash functions for this type of use case seems to have exploded in the last, I don't know, 15 or 10, 15, 20 years. I think because as more things have moved into memory, it's become increasingly important to have kind of these fast hash functions with few collisions. So a lot of effort has been put into this. One popular one is murmur hash. There are several different versions by now. It's like murmur hash three now. It's the most recent version, but that's kind of designed as a fast general purpose hash function, a lot of systems use that. And then there are several from Google, Facebook and other companies like that that are kind of designed for different use cases. So for example, the city hash is designed to be faster for shorter keys. You think like email addresses or something that are smaller than 64 bytes that Google has a use case for. So there are kind of all these different, more specialized hash functions, but murmur hash is a pretty popular one. So just as a kind of a high level comparison between the different functions I showed. So these just shows the throughput in terms of how many megabytes per second each hash function can execute. You'll see CRC64 is somewhere low along the bottom. It's pretty slow. There are, so the x-axis is the key size of the number of bytes in the key. And you'll see kind of these, for some of the functions, you'll see kind of these spikes. Those are usually like if they're cash line aligned or word aligned on the number of bytes you kind of get up there. And then when you're one byte too far over the performance drops off. So kind of this is the high level comparison to see kind of depending on which hash function you choose you can get pretty different performance. So the second piece to the hash table, and this is the probably more complicated piece is what hashing scheme are you going to use? So we'll talk kind of about three different approaches here. The first is called linear pro-hashing. The second is Robinhood hashing. And the third is Cuckoo hashing. And this is again the hashing scheme that you're going to use for how to resolve collisions. So linear pro-hashing is also called open address hashing. We'll see why in a second, but basically you can think of it as you just have a single giant table of slots, so like a big array. And the way that we're going to resolve collisions is by linearly searching forward in the array for the next free slot. So again, imagine you have two keys that hash to the same position. There's a collision there, so in order to find if the first key that we've come across isn't the key we're looking for then we're going to scan until we find the next key. So kind of to determine whether or not there's an element present, we're going to hash the location in the index and then scan forward for it. And if we get to a position that's empty in our array, then we know we're done, we're going to stop scanning. So insertions and deletions are kind of just a generalization of how you do lookup. So we'll kind of walk through next just a basic example to kind of see how this works. So again, let's say we want to hash these keys here, A through F, and we have our allocated hash table, it's just a whole bunch of buckets. So that we'll hash the first ones, we're going to store the hash value, it gives us position two in the table. So it's going to be A stored there which is the key we're looking for as well as the value associated with A. So there are different ways you can do it, but one common way is to just store the keys contiguous with the values. So these are both stored together in the hash table so that way when we want to go retrieve it we can get the value back as soon as we find the key. So again, we can kind of go through this, B maybe hashes to this zero position, we store the key B as well as the value. We can keep going, we get to C here, we see that there is a collision. So what C does is the hash maps the key C to the same position as key A. So the way we're going to resolve it is why it's called linear pro-passing is we just kind of scan linearly forward from the position that C should have been inserted until we find the next open position in the array. So kind of C ends up there and then if we insert D, it's the same procedure, D maps to the position where C was inserted. So now that slot's full so we have to scan forward until we find an empty slot for D and so on. So now we end up with E here that's all the way back up where A was. So we're kind of kind of look at each next slot. You see C is in the next position then D so finally E gets put at the bottom of the array there and kind of again with F, F goes in or F should be inserted, F hashes to the position where E is stored and we get F stored consecutively after E. So this is kind of how the insertion procedure here works. It's really straightforward. You don't need to keep track of anything really. You just hash to the position that you're supposed to be in. If there's already a key in that position then you just scan forward until you find an empty slot to insert into. And you don't need any like latching or anything for this. You can just do like an atomic compare and swap operation and compare and set operation to insert into the slot. And if you fail, if your atomic operation fails you can go to the next slot. You can implement it with latching but you can equally implement it with just atomic instructions. Okay, so that's kind of the insertion procedure. Does that make sense? Does anyone have any questions about that part? Yes. So the question is what about quadratic caching? Yeah, so linear probing is kind of the simplest one. It's just inserting consecutive elements you could have in an arbitrary probing scheme for your hash table. You could do it like exponentially. You could do it quadratically. So you know, first you check the next first base then you check two, four, et cetera. It depends on the distribution of your keys and the insertion order of the keys. So if you know information about that there may be reasons to choose different probing schemes. The challenge with nonlinear probing is again, remember we've talked a lot about the cost of random access versus sequential access. So if you have, you know, let's say exponential if you have to jump to the second to then the fourth and then the eighth and so on then you can kind of wind up with these bad random access patterns especially if, you know, with just linear sequential scan not only is data, you know, it's in consecutive, it's stored in pages, it's in consecutive pages. If it's stored in memory then it could be in consecutive cash lines and there's also some prefetching that can go on. So it's usually a lot cheaper to do these kind of linear scans. So there are other probing algorithms but pretty much most of the time I think unless you know something about kind of the key distribution then linear probing is common. Yes, so the question is why don't we need latching for this portion? So you could implement these insertions with latches. You could take a latch. I mean in the most extreme case you could take a latch in the whole data structure and insert one thing to prevent concurrent modifications. You could take latches on individual bins or buckets in the hash table to prevent concurrent modification. Since we're just inserting into an individual slot so like here, F wants to insert into this individual slot you can just perform one atomic instruction to try and set the F or to insert the F key into that slot and if the atomic, like a compare and swap or compare and set function. So if you compare it to empty and the bucket is empty so in this case it's gonna start out at E that's not going to be empty you're gonna move to the next one. So let's say F now issues the compare and set for to insert itself and maybe G comes along in the meantime and inserts G in there. So the compare and set function is going to fail for F and then it will just move on to the next slot in the bin. Does that make sense? Great. Any other questions? Yes. Yes, so the question is do the insertions wrap around and go back to the beginning if it reaches the end? The answer is yes. You can think about this like a circular buffer. So kind of as you're going through here if another key G came along and you got back to the beginning sorry you got down to the end where F is you may need to wrap around back to the beginning and start your scan from there. So if this fills up and we'll see that in a second but if the data structure fills up then you're going to kind of complete an entire complete loop through the hash table. So when you get back around to the position the first position that you started in position you hash to you know that the hash table is full and you need to resize it. So we'll talk about that in a few slides. Are there any other questions? Great, okay. So that was inserts and lookups work sort of the same way. So now the piece that we need to consider is deletions. So we want to remove a key from the hash table. Let's say for example and remove key C so we'll delete that and we'll you know hash C it brings us to position slot number two and that's where A is stored. So we compare C to A that doesn't match. So then we scan forward one we find C and then we can just delete C, right? Does anyone see a problem with what I just did there? Yes, right. So the comment was that if you have another value or another key that hashes to the position where A is stored and then let's say it's key E or D and then you want to scan forward then what's going to happen is you're going to see the empty slot and you're going to assume you're done, right? And that's exactly correct. So for example, if we want to find key D now after we've performed this deletion, key D is going to hash into this empty slot and it's going to say, hey, there's no key there. It's not my key D, it's not some other key and I need to scan forward now. There's just no key so it thinks that D is not included in the hash table even though we can see D is in the very next position. So the way we get around to this is there are two strategies. The first approach is what's called using a tombstone. So you insert some tombstone value, it's just some reserve value, could be a bit flag, could be some reserved key value, whatever. Basically all it does, its only purpose is to be inserted into that position where we removed C in order to let subsequent lookups know that it's not an empty bin or not an empty slot. It's actually just a removed or deleted slot. Since we removed it, we don't care what the value or what the key, we don't care what key used to be stored in there, all we care about is knowing that there used to be some key stored in this position so we don't terminate our forward scan early. So we can kind of see if we have this lookup of D again, it goes to the slot with the tombstone and it says, okay, I know there's a tombstone here, I need to start scanning forward from this position and then you find the next value D. So the second approach that you can use to get around this is what's called movement. So it's basically like a compaction phase. So for example, in this case if we removed C, we can just kind of slide all of these other values up and now fill in the empty missing hole that we created and now again, we'll find D, we can hash in and find the value there, yes. So the question is, when we're performing the movement, the compaction, how do you know when to stop sliding things up? Because for example, F might be in its correct slot but something else might not be. That's the next point I was going to make. So kind of in this particular example, kind of the mappings work correctly but if for example, we had another key where in your example, F was in the correct position, what we would need to do is figure out, okay, we can't slide F up because it maps to the correct area. So what this ends up doing is basically rehashing everything below where your compaction is. So you perform for the slots below where you're compacting, you want to perform that rehashing to see if they're in the correct position. Does that make sense? So for example, let's say we want to move D up. We rehash D and you basically just reinsert it. Does that answer the question? Yeah, so basically when you're doing this compaction, it triggers a rehashing of everything below the empty slot that you're reinserting. So for example, in this case, if F was in the correct position at the bottom, then when we rehashed F it would just map to that position again, we wouldn't move it up. Are there any other questions about that? So again, we have to kind of be mindful about this being a circular buffer, which we just mentioned in one of the previous questions. So when we were doing the shifting or sliding, kind of we would have to look at B, which is at the top of the array here. And really it should be shifted down there since we're shifting everything up by one to fill in the slots. And kind of as we just discussed, this if we're hashing the key B, it's expected to be there, but when we're doing the shifting, we would have to move it up. So again, you have to kind of be careful. You can't always move all of the keys. You essentially have to do a rehashing of all of the keys and the partition that you were shifting. So another issue that can come up in with the kind of basic or naive version of the hash table is you can't support, remember, non-unique keys. So again, if we have this bag or multi-set algebra, how do we handle keys that are duplicates in the dataset? So kind of one option is to have this separate linked list which is just going to store basically a list of all of the values that are related to a particular key. So you kind of deduplicate the common keys and then just store all of the values in a value list basically. So in this example, the key x, y, z, there are three repeats of the key x, y, z each with different values and two repeats of the key a, b, c with different values. So we can kind of store each of the keys once in our hash table and then we just store this pointer to some value list that can store variable sized number of values depending on how many duplicates of each key we have. Kind of the other approach to this is that we can just store the duplicate key entries together in the hash table and then we have to modify our lookup algorithm a little bit but basically kind of we can just get these values stored together in the hash tables and then when we go to do the lookups we can retrieve them. But that again requires us to modify the lookup algorithm to we have to scan until we find an empty slot because now we don't know when necessarily we're done. We have to keep scanning until we could potentially find more keys. For example, if we're looking for the key x, y, z in this case there could be other x, y, z keys later in the hash table we have to keep scanning until we find all of them. We can't stop after we found the first one. So are there any questions about non-unit keys? Yes. So the question is in the second approach when you get back the values for a specific key do you get back all of the values? So the answer is yes but it actually applies to both approaches. So in the first approach whatever it is whether it's the first approach or the second approach if you look up a key and there are multiple values associated. So what multiple values associated with the key means is that the keys are duplicated. So if the key x, y, z is duplicated three times you'll get back three values when you look up x, y, z. And it doesn't matter if you're storing it the first way in these value lists or if you're storing it the second way just kind of all together in the hash table. So I didn't mean like when you first went that you'd be always with the key and they'd say you're not sure so you have to just get all of the keys. So about the second, yeah. So the question is when you have the redundant keys all stored in the hash table choice number two when you do a look up do you have to scan through the whole hash table because you don't know when you're done. So it's not necessarily the whole hash table unless I guess the hash table's full but you have to scan until you find an explicitly empty bin because you don't know, like if there are guaranteed to be unique keys you can stop as soon as you find the key you're looking for, right? If there are duplicate keys you don't know, I mean it could be x, y, z and then a, b, c a hundred times and then another x, y, z again, right? So you don't know when to stop until you hit one of these empty bins. So then you know that your scanning's over you don't have to go any further. So it makes sense, right? Yes? I would always like that all of the duplicate values of the hash table. So the question is if you have duplicate keys does the hash table always return the, all of the values or does it just return the first one? I mean so I guess you could implement it either way probably what I would imagine it would look like is you'd get an iterator to like an arbitrarily sized list of values and it could be one or it could be n. Yeah, I think that's an implementation decision that you'd have to make depending on how you want the values. So you could, yeah, you could do it either way. But I think that the thing to keep in mind is that when we're building these in a lot of cases we can't guarantee that they're going to be unique keys so we need to figure out some way to handle these variable size number of values for duplicate keys. Does that make sense? Great, okay. Are there any other questions about this? Okay. So that was linear probe hashing. The next one, the next hashing scheme we're gonna talk about is called Robinhood hashing. It's named after Robinhood, the outlaw from English folklore who you may be familiar with, the stole from the rich and gave to the poor and kind of the algorithm gets its name from that kind of idea. So it's basically a variant of linear probe hashing that's going to steal slots from rich keys and I'll explain what a rich key is in a minute and give them to poorer keys. So basically each key is going to track the number of positions that they are from where they should have been inserted in the hash table. So if you think about on the previous slides when you have a collision, sometimes we had to scan forward to find an empty slot to insert a key into. So basically each key is tracking the number of positions that you had to scan forward in order to find the empty slot. And what's going to happen is on insert in the Robinhood algorithm, a key will take the slot of another key if the first key is farther away from its optimal position than the second key. So we'll kind of go through a visual example of this. I think it'll be easier to understand. So again, we have these keys here and we have our hash table. We're going to hash key A and that goes at offset two. And what we're storing now in addition to the key A and the value, we're also storing this number of jumps from the first position. So A got inserted into exactly the spot where the hash function set it should be inserted. So that has a value of zero. So now our next key B, again we insert the key B as well as the associated value as well as the number of jumps since the first position. So that's zero. Key C here, well C and A have a collision but in this case A is zero slots since it's A has a value of zero in its jump position. C also has a value of zero in its jump position. They're equal. So we're not going to replace key A. We're going to move C down to this slot and we're going to insert it with a jump value of one. So we have this value one now because C was inserted one position away from where the hash function said it should be. So D, let's say hashes to this case here. We're going to see that C has a jump position of one. D has a jump position of zero. So C is greater than D. We're not going to replace C. We're going to insert D here with a jump position of one. And now we have E, which is going to hash to let's say the slot where A is stored. So at this point A is equal to zero and E is equal to zero. So we're not going to replace A. C is equal to one and E is equal to one. So we're not going to replace C. Now we see that D is equal to one, which is richer or closer to where it should be than E, which is now equal to two because we've had to do these two jumps from where we said E should be inserted. So in this case, we're going to replace D with E and store the two jumps in the jump position. And then we're going to insert D right below it with also two jumps in the jump position. So now, again, just to finish, F comes along. D has two, F has zero. So F is going to go in that bottom slot. So this algorithm is shown to reduce the variants in the key displacements. That means the number of jumps or the number of positions. The key, the insertion position is from where the hash function says it should be. So it reduces the variance in the displacement relative to linear hashing. All of this depends on when the keys arrive. So this depends on the order that we inserted the keys. If we inserted them in a different order, we'd get a different answer. Usually this isn't very efficient in practice due to all of the branch mis-predictions that you have with the comparisons and then the excessive copying. So usually in practice, people tend to implement just plain linear hashing and then deal with the less advantageous theoretical aspects. But this is kind of how the Robin Hood hashing scheme works. So are there any questions about this? I know it was a little bit complicated to walk through here, but any questions? Okay, so we've seen linear probing, we've seen Robin Hood hashing. The next one we're gonna talk about is called cuckoo hashing. It's basically just an alternative way to deal with collisions relative to the other two. Basically the key idea is that we're going to use, instead of one hash table, a single hash table, we're going to use multiple hash tables each with either a different hash function or some different hash seed that will give us different hash mappings for keys in each of the tables that we have. So on insert, what we're going to do is check every table and pick any one of them that has a free slot for us to do the insertion. If no table is a free slot, then we're going to go through this eviction process until we get all of the keys where they need to go. And again, we'll go through a visual example of this. I think it'll be a little bit easier to understand. But what you end up with is the lookups and the deletions are always O1 because we only have to look at one location per hash table. So rather than kind of having to do these scans, we only end up looking in one location per hash table for each hash table that we have. If you're wondering where the name comes from, it's named after the cuckoo bird, which is a type of bird, where some of the species will lay their eggs in another bird's nest and then the birds, when they hatch, kick the other eggs out of the nest or the other baby birds out of the nest. It's a little gruesome, but that's where the name comes from. So basically what we're trading off here is we're making rights more expensive, so we have to do this multiple hashing in order to have faster reads. So that's the trade off that's going on here. So in the visual example, for simplicity, we're just gonna show two hash tables. In practice, it's usually more, I think the default for the lib cuckoo one, which I showed the open source implementation from CMU, is three, three tables get used, but here is just two, four ease of illustration. So imagine, again, here we wanna insert key a. So we have these two tables, each with its own hash functions. What we're going to do is evaluate the hash of a twice. So hash function one of a, hash function two of a, and that's going to map to two different positions in these hash tables. So let's say we're going to stick a in hash table one because the slot's empty, so we insert the key a and the value. Now let's come to key b, we wanna insert key b. We again do hash function one for the first hash table, hash function two for the second hash table, and we get these two positions, let's say. So we have a collision in hash table one for key b. We already have key a stored in that slot. So what we're going to do is insert b into hash table number two because the slot's empty. So a wound up in hash table number one, b wound up in hash table number two. Now key c comes along, and again we do the same two hash functions. Let's say we find out that key c maps to this position in the first hash table and with that has key a already stored there, and it also maps to the position in the second hash table that has key b stored there. So what do we do? We have two collisions, we don't know where, there's nowhere that's empty for us to insert it. So we're going to have to choose one of the values to replace, so let's replace the key b in hash table number two. So we're gonna kick out key b and insert key c there. So now we have to do something with key b. So we're gonna rerun hash one because we removed it from hash table two. We're gonna rerun hash one on key b. That's gonna tell us to go to this slot where key a is currently stored. So now we have to kick out key a from hash table one, replace it with key b, and now we end up here with key a, we have to find a place for that to go. Since we removed it from hash table one, we'll go and try and insert it in hash table two. So that's going to hash us to this position which is empty and key a can go in there. Yes. So the question is what happens if you end up in sort of a loop where, for example, the hash two of a goes back to the position c? So the answer is that you need some kind of a check to see if you're in this sort of loop. So are you back to the key that you started with? If that's the case, then what you need to do is rehash everything. So either you grow the size of the hash tables, you could add another table, you could change your hash functions, whatever it is, but kind of you, if you run into this case where there's nowhere left to insert a key, then you have to rehash all of the keys to find it to make sure that every key fits somewhere in some hash table in an empty slot. Yes. So the question is, if you're in this sort of loop, do you have to keep track of your path through the insertions to figure out if you have a cycle in the path? I don't think so, I think you can just keep track of if you've returned to the key that you started, or I guess so, yeah. So I think, I was just trying to think if you could end up with like a local cycle, like a, you know, A and B keep, yeah, yeah. I think you should just be able to see if you end up back at the key that you started with again because then you'll know that there's no kind of mapping, there's no way of arranging them or distributing them in different tables that will get you for, in this case it's A, B, and C that will get you those values distributed. So I think you can just keep track of whatever key you were inserting in this case, key C, and if you ever wind up back there then you know that there's no way of arranging the keys that is going to work. Sorry, so the question is in the worst case you might end up redoing, right. So the question is in the worst case, can you end up going through and kicking out and replacing all of the elements and all of the tables and then in the end you realize anyway that there's no distribution. Yes, that can happen. So I guess in the worst case you end up kind of with this total rebalancing anyway. And if you kind of have to go through the whole process of doing, I guess there may be some, I'm not familiar with all the theory behind this, but there may be some theory about the number of expected collisions that you end up with and a rebalancing depending on the size of the hash tables, number of hash tables and like the overlap in the collisions, but that's certainly a possibility, yes. Are there any other questions? Okay, so one observation that you might make is that all of the previous hash tables that we talked about require the DBMS to know the number of elements that it wants to store in advance. So when we allocate this array, we allocate a fixed size n and that's how many elements we can store. And then to grow or shrink the array, we kind of have to rebuild everything from scratch. We have to rehash all the elements to make sure they go to the correct bins or buckets in the next, the resized array. So sometimes this assumption holds, sometimes you can know this. So for example, like during query processing, if you're doing something like a hash join, then you can know or maybe estimate or have an upper bound on the number of keys that are going to be, or the number of distinct keys that are going to show up in your query, but there are a lot of times that this assumption might not hold. So for example, if you have just the base table storage, your data can grow arbitrarily large. So it could always be inserting new keys and over time that might fill up your hash table when in your out of room. So you have to kind of rebuild it from scratch. So ideally what we'd like is a way to, for those latter cases where we don't know the ultimate size of the hash table and the number of keys that we need to store, we'd like ideally some way to grow or shrink the table incrementally without having to completely rebuild it. So that's kind of where this idea of dynamic hash tables comes in, where they're resizing themselves on demand. And we're gonna talk about three different ways of doing this, chain hashing, extendable hashing and linear hashing. So the first approach is called chain hashing. Basically we're going to maintain a linked list of buckets for each slot in the hash table and we're going to resolve collisions by placing all elements with the same hash key into the same bucket. So sometimes it's called like a bucketed hash table. This is probably the most common one and the one most people are familiar with. You can grow the hash table infinitely just by adding new buckets to the linked list. And you only need to take a latch or use some atomic operation like a lock free operation. The store or new entry or extend the list. And you can still use kind of tombstones in this case but it's much easier to deal with because nothing needs to be rehashed. So you can add and remove elements keys from the hash table without having to worry about this forward scanning problem that we had with the static hash table. So I'll show an example of this and I think this will make sense. So we have these buckets allocated here. Again, we have our keys and we have this thing called the bucket pointer array or just think of it like a slot array that's going to map slots to these buckets that we have stored. So in this case, let's say we wanted to insert key A so we're gonna hash it to the first bin or a slot in the bucket pointer slot array and that tells us to go into this bucket here. So A goes in, B goes let's say in the zeroth bucket there. Let's say C also goes into this bucket with that A is stored in. So essentially have allocated enough room for two keys, key value pairs in there. And now we have key D. Well, the problem is we're out of room here in the bucket with A and C in it. So all we need to do is kind of allocate another bucket and we just create basically a linked list where now we've updated this first bucket with a pointer to the next bucket on the linked list. And these buckets can be arbitrarily sized. I mean, in the simplest case, it could just be a bucket of size one. So you have this kind of linked list chain of individual values for a particular slot in the slot array. In this case, it's two, it could be larger, they could be like a page size or something. And basically we can just fill up these buckets as we go along until we run out of room and then we just add a new bucket and connect the pointer to the next bucket in the linked list. So then F goes down there. So this is pretty straightforward. Does anyone have any questions about this? Yes. So the question is, instead of a linked list, what if you had a tree? That would be fine. I think there's no semantic difference other than whatever algorithm you have for searching your buckets after they come up. So you could maintain a tree. You could maintain another hash table, whatever. But then every time you add some layer of complexity like that, there's additional overhead in now. Instead of just inserting something at the end in whatever the next bucket is at the end of the linked list, you now need to maintain a tree and you may have to rebalance the tree so it's sorted or whatever like that, right? So there's trade-offs here. This gives you pretty low overhead inserts. You just append something to the end. You don't have to worry too much about the internals of the bucket layout. Yes. So the question is, the length of the bucket pointer's list fixed. Yes, the length is fixed. So what's going to happen is, as you increase the number of buckets, you may need to reallocate that array to get larger. The problem, and we'll talk about kind of rebalancing later, but if you just keep the number of buckets fixed and keep adding in this linked list fashion here, then you can grow arbitrarily large without having to change your bucket pointers. So kind of, there's again this trade-off between fast inserts versus now, if I need to go hash to, let's say, I insert a million more values and they all go in the same bucket as A, C, et cetera. Then in order to find anything, you're gonna have to scan this really long linked list. So again, there's this trade-off between kind of faster inserts without having to rehash everything and then look up performance. So we'll see in some of the other schemes how they get around that. Yes. Sorry, I didn't get the second part. So the question is, is it easier to implement to concurrent hash table data structure using chained hashing versus linear probing? I don't see any fundamental, or I can't think of any fundamental reason why it would be. I guess you can amortize the cost of an insert. Like you can do these inserts here the same way. So you could just use an atomic, if you wanna stick something in the last slot there, you could just use an atomic compare and swap and then when once E gets in, now you need to, for your next insert, you need to expand the length of the linked list. In the linear probing case, you're sort of doing the same thing, right? You're for every slot, just doing an atomic check to see is the slot empty? If not, then move on to the next one. If it is, then you can insert. So I don't, I mean, yeah, I'm not sure that there would be any inherent advantage to either one of the concurrent setting. Yes? So the question is, what about the cache performance? So like the CPU cache, you mean? Are you asking how does the cache performance of this approach compare to one of the other, like the, right? So the comment is that because it's a linked list, it could be random accesses, the nodes could be anywhere in the linked list, so you might have a bunch of skipping around. That's true. I think you can, so in the linear probing case, you just have to scan forward. In this case, you have these linked lists with the pointers you need to follow. There are ways that you can work around it. So for example, if you make these buckets like a large, yeah, so if it's like a large size, like let's say a page size or something, then instead of having individual linked lists, nodes that you have to traverse, you're now traversing something that's like four kilobytes or something big, or it could be even larger. You can make them arbitrarily large, then there's a trade-off between wasting space and allocation. So there are a lot of trade-offs you have to consider. You could also probably do something with prefetching. If you know that you're scanning to the end of the page, you have to go get the next linked list node in the list. You could issue some kind of prefetching instruction to get that loaded for you, so it's ready for when you need it. Okay, so extendable hashing is basically a chain hashing approach where we're going to split the buckets instead of letting the linked list grow forever. So one of the problems with the previous case is that we had linked lists that could become arbitrarily large. If we wanted to stop that, we had to resize that bucket slot array thing to make it bigger, to cut down on the length of the linked lists, but that kind of defeated the purpose of having this dynamic hash table in the first places. So kind of the idea of extendable hashing is that we can grow the slot array, the mapping array incrementally, and we're going to split buckets only when they overflow. And the key point here is that multiple slots in the slot array can point to the same bucket chain. And I'll show another example here what this means. So then you kind of have to reshuffle the buckets during the split, and then we increase the number of bits that we need to examine for the split location. So I think this will make sense with this visualization. So this extendable hashing table, we again have this kind of slot array or mapping array in which we're storing the first two bits, or we need to look at the first two bits of each of the hash values. So what the numbers here mean, we have what's called this global counter, which is telling us how many bits we need to care about and then for each page, just conceptually, I'm showing the number of bits here, you don't need them for the algorithm, but these are like the local count of how many bits we need to care about for each page. So what this means is, for example, in the first two slots there, 0, 0, and 0, 1, if we look at just the first bits, that's what the local one means on the first bucket, we look at just the first bit, we know that those values map to that bucket. In the bottom two, we need to look at two bits and that's why each of their local counters have a two in it. We need to look at two bits to get to each of those. So the global counter is the maximum of all of the local counters that we have. So the way that this is gonna work is, let's say we wanna find key A and that's going to hash to those values, 0, 1, 1, 0, some other stuff. The way that we're gonna do it is we're gonna look up the first, the global counter says that we need to look at the first two bits of that hashed value. So we're gonna go over here and look at that position in the slot array, the mapping array and it's going to tell us that we need to go to that bucket. So similarly, if we wanna insert key B and the hash function says it's 1, 0, 1, 1, 1, whatever comes after that, then again, the global counter says we have to look at the first two bits. That's going to tell us to go to this position in the mapping array and it tells us to go to that bucket. So now finally, if we wanna insert key C, again we get the hash value and look at the first two bits and the problem that we're gonna see here is that our bucket is now full. So I said kind of there's going to be this incremental resizing. So what we need to do is increment our global counter to three now, so that we're looking at the first three bits and we're going to have to split this bucket that's full into now smaller buckets. So we've incremented the counter, we need to reallocate this array. So now we have the first three bits of the numbers. I realized that these are, if you're particularly eagle-eyed, you may notice they're out of order, but they're just the values from zero to seven. The arrows and the animation go too messy, so but they're just all the values from zero to seven so you can figure out the slot that you need to go to. And now we add a new, split that bucket into two and we redo the mappings. So now, for example, key C, we need to look at, the global counter says look at the first three bits. So we're going to go to that position in the mapping array and it tells us to go to this bucket there. So I know that was kind of a little complicated to go through. Are there any questions about how this extendable hashing works? Yes. So the question is if you delete elements, can you shrink the list? Yes. So the shrinking, we're not going to talk about it here, but shrinking is just like the inverse of insertion. So you could shrink the list, yes. Okay, so linear hashing. What's going to happen is the linear hashing algorithm is going to maintain a pointer that tracks the next bucket to split. So rather than splitting a particular bucket, as we did in the previous case, one of the buckets got too full and we split that bucket. We want to split any time that any bucket overflows and we're going to maintain a pointer to the next bucket that we want to split. So this is going to solve one of the problems with extendable hashing was that occasionally, we have to end up doubling the size of this mapping array every time we need to add a new bit to consider. We have to increase the size of the mapping array so to get around that, linear hashing is going to do it kind of incrementally. So it's going to do the resizing just by adding one new bucket, one new slot in the array at a time. So the way that we're going to handle that is by using multiple hash functions, two is sufficient to find the right bucket for a given key and we use different overflow criterion, but let's just say it's when the size of the bucket fills up. You can use different conditions to decide when to trigger this, but in the example in our show, it's just going to be when a particular bucket fills up. So what this is going to give you is like a smoother growth policy by splitting buckets irrespective of which one overflows. So you don't run into this case where you kind of have to resize or double the mapping array when individual buckets fill up. So again, here's an example. We have these different array buckets and we have this mapping array and I mentioned we're going to keep this split pointer that tells us which bucket we want to split next. So the hash function that we're going to start with is just going to say hash function of the key is the key mod n, which is the number of buckets that I have. So for example, to find six, we're going to say six mod four is two and that gives us bucket two to look in. So we're going to go and retrieve it from that bucket. That's okay. We're going to insert 17. Well, okay, here we get bucket one that we need to look in. We see bucket one is full. So what we need to do is perform a split in order to do the incremental resizing that we want to perform. So again, the split pointer keeps track of the bucket that we're going to split, not necessarily the one that got too big. So the first thing we need to do is add this overflow bucket similar to the chain hashing that we had. So 17 goes in there. The first part is done, but now we need to do the splitting. So the split pointer points to this first bucket. We're going to split that bucket into two and we're going to add a new slot on the end. So slot number four and we're going to create this new hash function that's now key mod two n, so double the n. So we rerun all of the keys in that first bucket, eight and 20, eight still lands in the first bucket, 20 now goes into this new bucket that we've created and then we're going to advance our split pointer. So now what's going to happen is for all of the subsequent calls, we need to decide whether the keys are above or below this split pointer line. So in this case, we have key 20. If we come in here and we try and use the original hash function, it's gonna say that we should go to bucket zero. It's gonna be a problem because we split that bucket and we moved key 20 to this bucket four. So we need to keep track of this line and we need to say, okay, is the key that we're looking for if we perform the first hash function is it above or below the line? So if it's above the line, in this case, like 20, we know that we need to rerun using the second hash function and we end up correctly down in this bottom new bin that we've added. So now if we want to find another key like key nine, so that winds us up at the bucket one there, we know that that's below the split pointer line. So all we need to do is go and look it up in that table. So what's gonna happen is over time, we're going to keep moving this split pointer line down, keep splitting buckets. Eventually, this one that's overflowed here will split up when some other bucket fills up, we need to split it until we, again, wrap around. You can think of this like a circular buffer. So we get down to the bottom, when we're done, we've essentially doubled the size of the array. We'll start again at the beginning and we can get rid of the first hash function, hash one and replace that with hash two. Then we're back to kind of the same place we started. So kind of the idea is that we're splitting buckets based on the split pointer. Eventually, we get to all of the overflowed buckets and then when we reach the last slot, we loop around and get back to the beginning. Are there any quick questions about that? Great, okay, so just to wrap up, kind of the hash tables that we talked about today are fast data structures that support 01 lookups, which the time complexity, space complexity are important, but again, we really care about, where we care a lot about the constants that are associated with them. They're used all throughout DBMS internals. We have these different trade-offs we need to consider between speed and flexibility, but hash tables are usually not what you want to use for a table index. So they have a lot of uses, in particular for things like joins or other intermediates, maybe for tracking metadata, but usually for table indexes, what you want to use is what we'll talk about in the next class, B plus trees, which have been described by some as the greatest data structure of all time. I happen to share that opinion and I'm really excited for Wednesday's lecture. So I will see you all on Wednesday. Yeah! I see Jay talking about the Sainad's groove, one through a can of two, shared with my crew is magnificent, must as mellow, and for the rest of the commercial, I'll pass the microphone to my fellow. For a mic check, plus it, the fees are set to grab a 40, to put them in the York and snap the snacks in on. Take a sip, then wipe your lips, cue my 40s getting warm, I'm out, he got spit-tip. Drink it, drink it, drink it, then I burp, after I slurp, ice-cube, I put in much work, with the BMT and the e-drub, get us a Sainad's groove on the gum.