And we move on to Alessandra de Molde from the University of Antwerp, which is a member of the REIT Co-op. Okay, good afternoon. I am Alessandra de Molde, and today I will talk about creating a workflow to trace consumer values in London auction ads. Let's delve right in. Very shortly put, my research is about why people decided to buy what they did. And for this I look at both words in auction ads. These auction ads were placed after debt, bankruptcy, or moving abroad, and basically listed the possessions of the person that left died or went bankrupt. And, for example, we could find a test bet, as you can see on the left, I think, for you guys, which might be described as having beautiful chins, cotton hangings, or a sideboard, like you can see on the right, which might be described as being from Mahogany, having an inlaid top, or having a beautifully carved feet. I look at 18th century London, because this is a time and place where new and fast-paced consumption patterns emerged. I work inductively, I let the sources speak for themselves, so to say, and I work on a large scale. To get this large scale, I created, I gathered a lot of data manually, and I also cleaned it manually. I have over 5,000 pages of printed auction ads, which I supplemented with other printed sources, such as philosophical treatises, furniture manuals, which are basically interior guidebooks according to one design guru, and auction catalogs. I also use handwritten account books as well. The printed material I ran through Transcribers, I trained a model for which I achieved a 15% character error rate on the training set and a 1.89% character error rate on the validation set. My methodology is too prompt. First, I do a distant reading in the form of digital text analysis, neighbor analysis, amongst other things, and then I do a close reading. I do keyword search and other things for contextualization. Now, after performing one of these digital text analysis, I found about 500 bores being offered for sale. Classical bores, bore cases, bores of prints, collection of bowls, valuable stock of modern bowls, and these appear consistently throughout my research period. So, I opened up Transcribers again. I did a simple search with word preview and found that bowls were actually books being misread. We can see here books of prints, collections of books, valuable stock of modern books. Now, in the first instance, I can see why this has happened. The other ones are a bit more of a mystery, but this example shows that next to spelling variations, it's actually a transcription variation also can have major implications for corpus analysis, both distant and close. Unfortunately, not all of these conundrums can be solved by a simple search. For example, my second example will show this. Here, I created a scatter plot which shows the semantic shift of beautiful between 1760 and 1780. What we would expect here is beautiful to shift away from words related to making an object, such as handmade or finished, to the objects themselves as being described as beautiful. We can read about beautiful diamonds, wardrobes, and butts. Now, I think we all know that sex is a popular marketing strategy nowadays, but I find it a bit odd that this would be the case in the 18th century, so I delved a little deeper and I found two more plausible explanations. On the one hand, it turns out that butts was a popular add-on for locations, such as Lambert butts here, where the auctioneer, Mr. William, held his viewings. On the other hand, it is also a unit of volume or a large cask used to store liquids, typically varying in capacity from 108 to 140 gallons. These casks would probably show up in the possessions of a brewer or a wholesale wine merchant that went abroad bankrupt or died. Thank you for your attention and I look forward to any remarks or questions after this session or at another point at this conference. Perfect. Thank you very much for your presentation.


DJ 2PL. So real quick, sorry. Some girl came to my office, I was looking for you today. What? One more? Oh, f**k, that's not... I just... She was trying to find where you were. That's weird. I should find a better hiding place in the lab now. Yeah. So real quick, actually, the facilities people actually just came to be and they said that the governor is here. Shapiro, I voted for him because he's not a Trump supporter, but the main entrance is blocked off. So when the class is over, you can't go through that entrance. You've got to take the elevator and go upstairs. Okay? That's why all the federalities and the cops are out there. All right, so again, for the class, again, we had the recitation on Monday that's been posted on Piazza as a video. Project 1 is still due on October 7th, sorry, October 2nd. And then we will have, again, the special office hours on Saturday on the 1st, and then homework 2 has been bumped to be due on October 4th, and that's a Wednesday and not a Sunday. Okay? Any questions about the homework or the projects? Yes. All right, so it's due on the Sunday and then the office hours are on the Saturday. So whatever that real, what are those real dates are? Yes. The website and grade scope should be correct. I'm not. Other questions? All right, cool. So then two sort of sort of, or one sort of fun thing to bring up. Someone said, hey, what about internships or these companies hiring? The answer is yes. And actually somebody posted out on Twitter that, you know, if you take in my class, they're hiring. And so Spacetime BP is another time series database system. I think it's out of Europe. All right, I don't know this dude, you can contact him if you want, but we'll post on Piazza how you can get me your CV and then we can send it to the various database companies that we know and that are friends with us. And again, if you haven't yet, please apply to single store. And there's that special email address that's just for CMU students, and that'll go directly to the hiring people and not the recruiters. So if you don't want to do an internship, another way to make money through databases is that somebody actually posted on Upwork, and this is real, that they're looking for someone to basically design database projects that are basically bus tub and class projects. So if you like the stuff you're doing, you can get paid $100 by this guy to go reimplement that. And the way we found this was somebody actually emailed Qi, the TA, me like, hey, I can do this job for you. And he's like, what are you talking about? Because they thought we posted this. This is not us. This is some rando. $100 is definitely not enough. Again, you should be making $100 an hour in databases, if not more. So where are we at in the class? We spent the last week or so talking again about the storage layer and then putting the buffer pool on top of it to actually manage memory as we get pages in and out of disk. And so now we're continuing up the stack and are now going to talk about different parts of the system that can operate and execute and process those pages that we've brought into our buffer pool that we've retrieved from disk. And so we're sort of in this middle layer here in the access methods. And now we're going to start talking about how do we construct the execution engine that's going to be responsible for exiting these queries. And so the access method is going to be the mechanisms for actually accessing the data. And it can either be through an index or through the tables and cells and potentially other mechanisms. So to do that, we need to talk about what kind of data structures we would have at these sort of this part of the system. And so this class will be on hash tables, which is an unordered data structure. And then we'll spend all of next week talking about tree data structures, which will give you ordering keys. So we're just slowly building up, making our way to the top to actually produce results for our queries. So it goes with sort of saying, I'm assuming everyone here is taking a data structure class or algorithms class. Data structures are going to be used all throughout the system. And we've already covered in some ways, in some parts of the system so far, where we're going to use these things. But there'll be other parts that we need to have high performance, safe and correct data structures to represent state of the system or the data of the system. So we've already seen how we can use this for internal metadata. We talked about the page directory or the page table, that's more or less a hash table being used to map page IDs to some location on disk or some location in memory. We could use these data structures for the core storage of the tables themselves. Remember we talked about the index organized tables where the actual tuples themselves would be in the leaf nodes of the B plus tree. So you could have your tables actually just be represented directly in a data structure rather than unordered heat files. We could also use these data structures for query execution to generate ephemeral or temporary collections of data that allows execute queries more efficiently. This is basically how we're going to implement hash joins very fast or how implement joins very quickly using hash joins. So we'll build a hash table on the fly, populate it with the data from the tables we're scanning, do the join, and then throw the hash table away. So just because we're building a hash table doesn't mean it's going to stick around for a long time. And then probably the one you're most familiar with is using these data structures for table indexes. Like when you call create index, that's essentially going to create one of these data structures and populate it with the keys and map them to the tuples. So you do faster lookups like a glossary in a textbook. Right so again we'll see these data structures being used throughout the rest of the semester in different scenarios that are covered in this list here. So now what do we care about when we design our data structures? Like what are the things we need to be cognizant of to make sure that we have an efficient database system that actually is also correct, which is very important, right? So the first thing we got to worry about is how are we actually going to organize the data structure itself in either in memory or pages that will be in memory but backed by disk in the buffer pool. And remember I said in the beginning we want to make design choices in how we implement our system if we know it's going to be backed by pages on disk that we maximize the amount of sequential IO. So maybe we'll lay out the pages in such a way, the data structure in such a way that we have long strides of data that we can read and write through multiple pages instead of doing random IO. And then we'll talk about how do we actually make our data structures thread safe. And so for this class we won't really worry about it but we'll spend a whole lecture next week on Thursday or Wednesday next week talking about how do we make sure that the data structure is correct in sound if we have multiple worker threads or processes coming in and reading writing or modifying the data structure at the same time, right? And this last one's going to be tricky because we're going to care sort of two kinds of correctness in our data structures if you want to make them multi-threaded. There's the obviously the physical correctness of making sure we don't have a pointer that goes nowhere or a page ID that doesn't exist, right? If we have one thread accessing a page another thread is updating it and that the accessing thread reads something that the guy wrote but it hasn't been you know it's not you know safely committed yet or it's not saved correctly then we may end up you know falling a pointer to nowhere and the system would crash so we have to avoid that. But then there's another kind of correctness we'll get to after the midterm at sort of the logical level to make sure that if we make changes to our data structures that our own thread can see those changes or that it looks correct to it meaning like if my thread deletes a key from an index if I then go back in that same thread and try to read that key in that index I shouldn't still see it. The bits may still physically be there right because maybe we haven't run garbage collection and maybe there's a little flag that says this thing's been deleted so physically it's still there but logically it's not we need to make sure that we don't see things we shouldn't be seeing, right? Again so we won't focus too much concurrency in this week but we'll cover this in more detail next week and certainly this will be a big issue also too when we talk about concurrency control at the again at the logical level having transactions and making sure we provide asset guarantees but again that that'll be after the midterm. All right so today's class we're focusing on on hash tables and again this because it's a it's a low-level building blocks that we can reuse throughout the the rest of the system and again this shouldn't be news to anyone here a hash table is just going to be an associated array that can map keys to values. You guys okay or you're good? What's that? Oh yeah a lantern fly okay sorry I thought you said you started the fire I was like okay that's even worse. Second time? Oh today wow it was a fire lumber in geese yeah which I didn't cause I caused one last year not this year. All right so uh with the last DJ all right so the way the hash table is going to work is that there's going to be it's going to be this mapping from keys to values and we're going to use a hash function that's going to allow us to essentially compute some offset within an array and then it's basically reducing down the the an arbitrary key to this integer domain that we can then jump to some some location in our our hash table to find out things that we're looking for right and the the the this hash function has to be able to take any possible key because again think of any column type you can define in your database system also any any internal metadata we'd have in in the system itself we need to be able to take that you know a hash function needs to reduce that down to an integer so in a hash table the space complexity is going to be roughly big n or big o n because we're going to have to store a a slot for for every possible key we want to have right the time complexity is nice because on average we're going to get o one lookups meaning we hash a key jump to some location in this hash table array and then ideally there's the thing that we're still there here just kill it all right so that's why two kills for the semester that's not bad um all right so again no an average is going to be one because it's going to be like again hash take your key hash to some location and then you land the thing that exactly what you're looking for worst case will will be big o n because because what we'll have to deal with collisions it may be the case that we hash our key land in some location and then the thing we're looking for is not there and we've got to scan along on our hash table till we find the thing we're looking for and it may be the case that all the slots in our hash table are full and we have to wrap around it's basically the it's one above the one where we land or the hash function but we had a loop around to find it right and so the way you sort of handle this and we'll see as we go along is you size the hash table to be roughly two n the number of keys you expect now you may say okay and Andy how do you know how do you know what n is well this this is what we'll get through that's the semester like the daily system is going to try to make a decision or try to predict how many keys you're actually going to have and and size it accordingly so o one sounds great and if you're going to take an algorithms class this is the holy grail you want this right you want o one because again it's constant time but in actuality again in a real system the the constants actually matter a lot so even though it's o one you could have one hash function that maybe takes 10 milliseconds to compute another hash function takes one millisecond compute and obviously the one millisecond ones meet a lot faster if you think in large scale uh tables like billions of keys so again just because the algorithm of complexity is is ideal on average is one we have to still care about the implementation to make sure we're as efficient as possible all right so let's look at a the sort of the sort of toy example what a hash table looks like and let's see all the problems that you can have with it and then we'll build up this look at more sophisticated schemes that are actually used in in real-world database systems so the easiest hash table to build is a static hash table where you just call malloc generate a giant array where you have one slot in your in your array for every for every key i could possibly have and then to find an entry for a given key you just take the you mod the key by the number elements you have and you land in some offset in the array right so here's my offsets and then any key shows up i i know exactly where to go go find it right and you don't start the keys in this array it's essentially just a a pointer to uh some other location that's going to have the the key and the value together all right the reason why you need to start the original key is because uh since the hash may you could have collisions which will get in a second like you need to check whether the key you're looking that you land on through your hash table is actually the key you're trying to find and the value here could be a pointer to the tuple like a record id or it could actually be some some additional values for our for our purpose today we don't actually care so what are some problems with this this approach yes he said what do you have what do you have uh well i'm assuming it's static but so what if you have n plus one keys how do you resize this thing right and you have basically in this scenario you have to rehash everything so that that sucks what's other problems yes does it handle collisions what is collision yes that's correct yes so you have two keys that have the same value they're going to land in the same location in the in in our array even though they're not the same but i'm assuming that you know that everyone it has to be unique and you can't have collisions and this example doesn't handle that there's one more problem i'm assuming that the keys are unique right i could have key value equals one and and key value equals two like same key but different values in my in my my sort of toy example here it doesn't handle this right so this is unrealistic again for these three assumptions so the first one is like you have to know all the keys ahead of time in some cases you do other cases you don't in the case of the buffer pool and then we talked about last class that if you assume that the you you know the the size of your buffer pool is fixed you're going to have a fixed number of frames in your buffer pool therefore you know the exact number of slots or you need in your hash table but if i'm if i build a hash table index and i keep inserting tuples now my the number of keys is growing as i insert new tuples every key is unique in this scenario here again how do we need a way to handle keys that you know you have duplicate keys we have different values got to handle that and then the the thing that he brought up is that we're assuming here we have what is called a perfect hash function that guarantees no collisions which does not exist in the real world it exists in the real world but it's basically toy implications no database system can actually do this because again you need to know the key domain ahead of time right there's no magical hash function that guarantees for any given key you can generate a a unique unique hash value right the way to actually implement implement one of those is through a hash table you basically need a hash table for your hash table to do this which uh for it some systems do do that but not for a perfect hash function okay so we got to be smarter and we got to make sure that we deal with the environment that that we're operating in with databases all right so there's two decisions we have to make when we have people on a better hash table so someone says the basic i have a hash table it's sort of two parts there is the the hash function itself again how to map a a large key space down to a finite smaller domain right based on the number of slots i'm going to have in in my array and there will be this trade-off between how fast we want our hash function to be versus uh the how likely it is that two keys two distinct keys will will collide all right what's the hash what's the fastest hash function i could i could build what's that it's identity you can go faster than that so he basically says for a given key you spit out the same key but if you have a string key and i got to make it an integer how do i do that instead of take the first bit uh yeah that would that would be pretty fast too uh obviously you you just can return one right like that'll sit in the stack and register that'll be super fast now that's it's the worst hash function in terms of collision because everything's going to map to one but it'll be fast right so it's this trade-off trying to figure out and you sort of think of the perfect hash function is the other other end the collision rate is is zero but it's super slow because you have to do this actual lookup right so you want something in the middle that's going to be fast and and have a low collision rate right and then and then the hashing scheme is going to be the mechanism we're going to use to handle uh collisions after we don't are hashing and the the way the trade-off here is going to be again sort of the class of storage versus compute and computer science like i could i could allocate a two terabyte hash table and i'm pretty pretty unlikely going to have uh i'm not likely going to have collisions for my key set is super small but i allocated this massive hash table or i can have a smaller one but i have a lot of collisions and therefore i have to spend more compute to handle those collisions so again it's trying to figure out how to do the right get the right trade-off between uh not over allocating but then also not waiting wasting a lot of instructions to deal with with collisions all right so today's talk with we'll talk a little bit about about hash functions um just to sort of show you what the state of error it is i'm not going to say how they work just tell you that they exist again we're data people we're not in the business of writing hash functions we'll let other people do that for us um and then we'll talk about the sort of the classic static hashing schemes where you know the number of keys ahead of time and then we'll talk about dynamic hashing schemes where you the hash table can actually grow and shrink uh based on the number of keys okay all right all right so again we're not in the business of writing hash functions other people that are smarter than us in the space have done it for us um so we're just going to rely on them again the basic idea of a hash function is that we have some input key any arbitrary number of bytes of any type and we need to return a integer that represents that key typically is 64 bits um there are 128 120 bit hash functions but i don't i don't think databases use those there are 32 bit hash functions as well but anyway so we're going to return the integer so in this scheme or in this in a database system we don't care about uh any sort of protection privacy mechanisms uh for a hash function mean we're not going to use anything that has cryptographic guarantees so we're not using shah 256 or whatever like we don't we don't care about those things because we're running on the inside of the system uh it's it's we're not worried about leaking anything why we can you know build a hash table to do a joint because no one on the outside of the system can see that data structure so we don't care about any of those things and and as a result we can actually run a lot faster right shah 256 will be really slow versus something like like you know murmur hash or xx of hash um as i already said before we want something that's fast and it has a low collision rate so this is just a quick uh quick overview of what other hash or hash function systems are using um sometimes it's like postgres roll their own hash function but a lot of the more modern systems they're going to use something off the shelf like xx hash or murmur hash or the spooky hash um so basically the the main takeaway from this is that the steady art one is xx hash from facebook right with the third version xx hash three this one is shown to have the uh some of the fastest performance and also the lowest collision rate um there are some systems that use crc 32 or 64 um for hashing link integers because they're actually cpu instructions in x86 to do that in you know in a few number of cycles um so that there's some systems that do that but in in terms of like random strings you typically want to use this so murmur hash is interesting because it was murmur hash was written by this random dude on the internet he had a good fast general purpose hash function google took that and made city hash by forking it and then they have a newer version called uh farm hash um that has even better collision rates there's a bunch of different sort of hash functions out there um but xx hash three is what you want to use and so there's a bunch of these repositories on github or people have written basically torture chambers or benchmarks to uh to run all possible hash functions that are out there and see what the collision rate is see what the performance is um so this is this m hasher sm hasher there's another one written by the murmur hash guy and there's another one but that's a fork at this uh that's only for it's not where you have room is all the cryptography stuff but for this repository here they had this like nice summary here that says these are the ones that work the best and have good uh good collision rates and then the the the top one here is xx hash three the the facebook one right so again we don't care it's a hash function keys in integer out we'll just use whatever whatever they have right and then there's the full list of these of all the different hash functions some are tailored to arm some are tailored to x86 or whatever like you you can uh you can get you know more low level details based on the environment but xx hash three is going to be a good default choice okay all right so now assuming you know assuming running xx hash three we want to talk about what the hash table is going to look like and how do we handle collisions so for this lecture i'm going to focus on the public the two most common ones but number one is actually going to be the most common one of all the systems linear probe hashing um it's the simplest uh and it seems kind of brain dead in some ways uh but because it's so simple it is actually the fastest right and then cuckoo hashing is a is a variant of this that basically does multiple hash functions so there's a bunch of other techniques robin hood hashing hopscotch hashing swiss tables uh from google we won't cover that in this semester but if you take the advanced class we will cover those things and i would say that the the current research basically shows that the linear probing stuff and the swiss tables are the fastest ones all these sort of extra fancy fancy versions um are uh as they try to be they try to be more performance because they avoid having to spend longer time looking for for keys by moving things around when you when you insert but all that work of moving things around is is a performance penalty and you're better off just kind of doing the the naive thing in in your hashing yes this question is the reason why we're not talking about chain hashing because that's dynamic that'll be next yeah right because chain hashings can grow this is fixed size that we'll cover that later this semester or later this class right these are all static hashing schemes that there's variations of linear probing you can do quadratic quadratic probing uh we can ignore that for now let's keep this simple all right so linear probing hashing is uh is is really simple it's a giant array of of of slots and we're going to hash into it um you know if we want to insert we hash into it if the slot is free we insert the thing we're looking for if the slot is not free we just look at the next slot and and and insert in there if we can or we keep looking until we have a free slot potentially wrapping around uh until we find a free location and then if we you know loop back around realize where we're at the slot where we started that then we know the hash table is full and we have to uh abort it and and you know abort it double the size and and rehash everything right it's a simple way to grow it right so the state of implementation for this or one of the state of implementation is this appicell thing from uh from google um and they have the it's the flash hash flat hash map uh type for data structure um and they have pretty good documentation to describe how actually how it works and some of the optimizations they do will will cover so this is sometimes called an open addressing open addressing hashing because the the idea is that it there's no guarantee that for a given key it's going to always be in the same address or same location in the slot depending on what what got inserted before it it may it may get moved around right if you get a dictionary in python this is essentially what you're getting as well all right so let's see how it works so say we want to insert key a right so we hash it mod it by the number of slots that we have and then we hit land there's a location here so we insert our our key along with along with the value together right again the reason why we need the key is because if we go do a look up again for looking for a we need you know we'll hash to the same location but now we got to do an equality check to see whether the key that we're looking for is the key in a given slot same thing so if you want to hash b same thing hashed here uh mod by the number of slots we end up here and we sort at the top so now we now we want to start insert c uh so when we hash c it lands to the same location where a is but but that slot is is occupied so we can't insert it there so we just follow down to the next slot and insert our key there right same thing with d d wants to go where c is you can't because that slot's occupied so it just moves down to the next one and inserts it there right and we just keep going down for all the other keys we want to store right and in this case here if say if f one in the this space was occupied you know f kind of wrapped around start start at the beginning and insert at the top right think think of it as a giant circular buffer pretty simple right what are what are some potential problems with this yes he says deletion stocks because you lose the whole chain what do you mean like if you delete c and then you look at something that where c is so you don't like think right so he says uh i don't see these old slides ahead of time what happens sorry what happens if you delete c right so delete c we hash it we land where a is right now we do that the quality check to see this does a equal c no so we know that's not the key we're looking for and then we keep going until we find empty slot or the key we're looking for so in this case here after jumping down we find c and now we need to delete it but now we have an empty space as he said so if i try to go do a look up on something like d d is going to hash to this empty spot and it's gonna say oh well nothing's here right but it really is the you know it's the next slot down but because the way the protocol works the scheme works if i see an empty slot then i know i'm done right so what's one way to handle this gravestone tombstone that's one approach yes uh we'll get we'll get to there which that is the correct answer um so you could do this you could just like rehash or move over and up right is that a good idea a bad idea well clearly it's a bad idea because i said no one does this right but why is it a bad idea it to move everything again think huge now a billion keys i gotta go rehash everyone that'd be terrible right so that so it's super expensive and no one does this um let's get through this right so yeah this is not the sense you don't want to do this the the correct solution is what he was saying is was what it's called a tombstone and the idea here is that i delete c uh but instead again instead of setting it as empty i'm gonna put a little marker here uh to say this slot there was a key here and now it's been deleted so that way if anybody comes along like doing a look up on d it sees the tombstone says well it's not empty something was here uh but there's nothing here that i'm looking for so let me let me then look down and uh and keep scanning along until i find the thing i'm looking for right so essentially you can reuse these these uh you can reuse the slot with the mark of the tombstone for new keys go you just insert over top of it and that doesn't break the flow or break anything else in uh in the hash table right now maybe the case you want to periodically run garbage collection because you can start accumulating much of these tombstones and it's just wasted space if you're not reusing them uh but for our purposes we you know we we can ignore that right so i want to put say g g can go right here and that's fine now i'm not gonna not going to discuss this too much details but like there is a challenge though how you actually want to represent these tombstones and also represent something that's that that it's empty and potentially also represent that i have a null key which you can do in in a database system right so we could do the trick we talked about before with slotted pages where we could have a bitmap in front of uh at the top of every header every page in our hash table like i'm not showing the division here between pages but think of like for simplicity every page is to these slots so in the header that page i could keep track of like okay here's the slots that are empty here's the slots that are null or here's the slots that are that are um that are marked with the tombstone right so i i need some additional metadata to keep track of these things and you obviously don't want to do it on a per key basis because uh that that could mess up with the alignment of things or in waste space all right so the other thing we got to deal with now is is non-unique keys right so there's two approaches to do this one is that instead of storing the value in our our giant hash array or or array along with the keys instead the value would just be a pointer like a page page id to some other location that will store my list of keys or sorry yeah list list of values right so for the key xyz there's a pointer to some some basically a link list that houses all the possible values and then for the other key the same thing right what's nice about this is because as i insert new keys uh insert duplicates and remember again i'm not really changing the the main hash table i'm sort of pending to this this sort of link list it's like the chain hash table that that he talked about before or he asked about before we'll get in a second the more common approach is to do just store redundant keys together right and again this doesn't break the open addressing of the linear probe hashing scheme is that i i'm always hash you know hash on the key i land us on location and i find a you know i just find a free slot and i insert the thing i'm looking for this does make a little bit more tricky when you would do your look-ups like give me all the give me all the keys of xyz uh key value pairs because now i gotta i know like i gotta keep i gotta keep scanning until i find an empty location empty slot to know that i'm not going to see xyz ever again whereas in the first the first scenario i find xyz in my hash table then i then i land at the you know follow the pointer to the the list of values and i know that's all the possible values i could have for that given key right but for simplicity reasons instead of having to maintain the sort of separate link list for non-unit keys and the the non the in the in line version for uh for for unique keys most systems just store that use the redundant key approach because you don't you don't have to you don't have to have multiple invitations yes your question is how would you differentiate between so this question is how how would you differentiate between an update of a value versus insert of a value um yeah for for hash tables you really really don't do updates it would be a delete followed by an insert right um and of course now the tricky thing is like if i want to delete key xyz with value two like uh i can't look sorry if i if i just say delete if i only want to delete one of these like i have to know what the value is and to make sure i only root that one because i you know i'm i'm delete all xyz which may not be what i want yes the question is if you have so if you have a key with multiple values you just hash the entire table what do you mean by that because like you're searching for yes so like yeah so statement is like if i'm looking for if i'm looking for an exact match in that case like i don't need the hash table because if i have right but like if i'm trying to remove it from the data structure like this exact like key value pair right then you you basically like how does this you you you need to find that exact pair you need to have it at the time and so it's just maintaining the data structure i guess my question is what why do this yeah so like if i'm doing a join right the the relationship between the two join tables is that one side might not be unique right so i need to have this and so i want to get all of the like you would have basic and iterator says give me all the values where key equals xyz and the card starts spitting those things out as as i'm as i'm traversing the hash table because i'm doing the join yes would you rehash this question is would you rehash when it's completely full or 80 full so the different systems have different um there's like a threshold let's say if i go above this i know i'm going to overflow or run out of space so i'm gonna go ahead and trigger a rehash well i just meant yeah it's just point yes like the you get closer to that worst case scenario where like if it gets it starts to get full so rather than waiting till it's like 100 full maybe go to 80 percent because the it's better off to pay the penalty to resize the hash table which is doubling you so you have to resize hash double the size of it go through all your keys and rehash them put them into the new hash table and then throw away the old one that's not that's expensive it's a lot of it's a large hash table so but there's a trade-off of like okay well if i'm at eight percent full i rather pay that penalty to double the size rather than all the additional operations that i need to do spend a long time searching through it there's no there's no like one answer yeah but that's that's why there's a there's a there's usually a tunable threshold whether or not they expose expose that to you uh as like a user of the devia system it depends on the implementation but there's usually a threshold to say when do you want to go ahead and resize all right so some other organizations we can do that one is you could have different hash table implementations that have these different mechanisms or like you know decisions about when to split how to store things and what not based on the the data type you're storing so an obvious thing would be like if i have i want to be able to hash tables that support string keys if my strings are very small like 64 bits or 64 yeah bits or 64 bytes or 64 bits or bits or bytes less then i can store that inline my hash table but it's a really large string i don't want to store that in my hash table maybe i just want to have a pointer to the actual string itself so now i could have a 64 bit pointer um but now it's going to be expensive to do that look up to see whether i have a match so maybe i actually want to store the the hash of that string as part of the key in my hash table so avoid having to do that that look up right um we talked about storing the metadata like it's something a tombstone or something a null value or a or an NFT slot you could store that in the page header because now you have a bunch of packed bits you'd actually store that in an entire hash table itself right so the the google hash map does this where they have a separate hash table just for the metadata that's much smaller and compact you do a look up in that to tell you what is the thing you're about to go look up in there in the real hash table is that thing you know null or or or empty or not and then this one is interesting this is from this one comes from click house uh the olap system that came out of the index in russia um so they talk about how they want to be about since it's so expensive to allocate the memory for a hash table you don't want to just you know allocate a bunch of memory use it once and then throw it away which you actually want to just reuse that that memory over and over again but you need a fast way to clear it out so instead of going through and marking all the slots as deleted you just maintain a version counter a version id and whenever you say i want to delete the the contents of this table you just increment that that version counter on the table and then now any look up you do inside of a slot inside that table if the version ids don't match then you like if you're this the slot version number is less than the table version number then you know it's been deleted and you can ignore everything in there and that clears it out and then uh and you increment the version id all right so the bunch of different tricks you can do in different scenarios to to make these things run more efficiently and the various systems do different things click house in my opinion there's a that link there will take you to the blog article they claim they have 30 different implementations of hash tables um a lot of his templatize based in c++ right based on the data type and they do a bunch of compiler tricks to to to remove code you don't need uh if you know like something cannot be gnarled or is a string of certain size um they probably in my opinion of all the um source systems i looked at they're probably the most sophisticated one that i have the most sophisticated hash tables um all right so one variant of linear hash linear prep hashing is a technique called cuckoo hashing and the idea here is that instead of having a single hash function to do a look up to one location in my in my hash table what if i had multiple hash functions and i hashed up multiple locations and i find whatever one has a free look free slot and i use that one instead of having to scan through now uh until i find a free slot for for my for my key so this is going to guarantee that all my lookups and deletions will be o1 because you know no matter how many hash functions i have um you know i i don't have to scan through i'm going to land at some location in my in my hash map or my my hash table that has the data that i'm looking for or it doesn't exist inserts are going to be more expensive because we'll see in a second you may have to start moving things around and reorganizing stuff so there's only one system i know that does cuckoo hashing the public he talks about it and that's this olap accelerator from ibm called called blue blu um and in their paper they talk about how they make heavy use of of cuckoo hashing and as far as you know the best open source implication of a cuckoo hash table is actually from dav anerson uh from from cmu um i think google you he said he dav claims google uses a lot of it and so the name has to do with the cuckoo has to do with like uh awesome okay let's take google that's why yeah so uh so uh where was it yeah then so the name cuckoo has to do with his bird where they they lay their eggs they can lay their eggs on another bird's nest right and so the idea is again my key may end up may end up stealing somebody else's slot uh in my hash table if i try to go there and they're using it so let's see examples let's say we have again we have a same a single hash table but now when it anytime we do an operation we're gonna have two hash functions so it's going to be the same hash function implementation that we talked about before like xx hash murmur hash spooky hash it doesn't matter but we'll just give it a different seed uh to the hash function that guarantees that for a given key it doesn't guarantee but it's very likely that that for a given key it's going to it's going to produce two different hash values so i hash a and i have these two locations here so in the very beginning my hash table is empty so i could either flip a coin or pick the first one it doesn't matter and so i'll aside that for inserting a uh it goes in this the first slot here now i want to i want to put b in um and so the first hash function has to do with a is the second hash function goes to an empty slot so because the the other one is occupied i'm going to always choose the empty one and i'll put b at the top like that now where things get tricky is that we have multiple our two hash functions or multiple hash functions has to do two locations that both have uh that are both are being occupied so in this case here for whatever you know whatever protocol whatever scheme you want to use say we can flip a coin we decide we want to evict b so we'll go ahead and bash b on the head take its location put c in there but now we got it you got to put b back in so because b landed on this using the second hash function after we after we we take it out and put it back in we need to use the first hash function but then that takes us to the location where a is located so b is allowed to steal from a so b goes there a comes out we hash a with a second hash function and then we land to another location and again just like before in leaner perhashing you need to keep track of if you're stuck in a loop right so you just got to keep track of is this is the key i'm putting in the same key i try to first put in the very beginning and i've just looped back around and i'm stuck in an infinite loop and therefore i need to abort double the size of the hash table and rehash everything so now when i want to do a look up on b right i take b hash it twice and i get two different locations and now i do my check to see is the key stored in this in this slot the key i'm looking for if yes then i have the thing i'm looking for again now and i don't need to do that leaner probe scanning of looking for an empty slot or the key i'm looking for because i'm guaranteed either the key is going to be there after hashing or does not exist in the table so it's a good idea a bad idea it seems like there'll be more collisions um well no right because if so like there's trade-offs right so like yes could be more collisions but like these in leaner probe hashing you're guaranteed to always put something in there right it may be the worst slot maybe the you know the the the slot right above the one you try to go into and you loop back around uh but at least if there's a free slot you'll you'll get it yes i'm yeah he's absolutely right so this is doing random ios because i'm jumping around well the hash table is doing essentially random ios but once i land somewhere doing a random look up then it's sequential scan this is always random right yes what i yeah i mean that's that's this is what i face okay don't you have a girlfriend i do i mean this is the this every conversation every night explaining to her is the most i mean all right that's the life i guess it's impressive yeah the really nice flower okay i i i mean you're a good dj it's not and you do databases so it makes sense right databases are the key yeah all right congrats all right so your question was uh data this is yeah yeah so this question is uh is it possible to paralyze the access to different locations so yes you could do that uh like there's two different ways to paralyze we'll eventually get to that yeah like you can have multiple threads or single threads but do vectorized instructions simd instructions like uh and for simd this won't uh you could do this but it requires you moving data around a bit much because you have to make sure things are aligned um but so you may be able to do this with with a single thread with vectorized instructions uh i know there's techniques exist i don't know about cuckoo hashing though um but for for me to make this multi-threaded it'd be so much work or just too much work to tell two threads okay we're looking at this key you hash it this way i'll hash it this way and then to then uh synchronize on who produces a back result that is just not worth it yes so your question is um do you have to guarantee that the uh the hash functions always go to different uh yes so it's like so do you have to guarantee your hash function can't do that i you can't right that's why again that's why i'm saying like you want to choose a hash function that has a low collision rate so the like you can't guarantee that won't happen but the likelihood that that that it will happen uh is is low okay the only thing you can guarantee is a perfect hash function this question does it fall to the linear probing after that what do you mean yes yes oh is it if i if i if i run out locations for this either because all the slots are full or i get a wrap around when i try to do this the cuckoo thing i'm taking right you double the size of it yes so this increased the cost of yeah so this question is is there a defined order such that like you can maybe just always check the first hash like i'm showing two lines coming out of it but in in a in a assuming it's not parallel it is executing sequentially like could i uh is there some protocol say check this and then only fetch the page for the second one if i if i know it doesn't it's not going to be there i mean you can do a bunch of things you could prefetch the second page right because the hashing is actually cheap it's the lookup is expensive right so maybe i i i could choose one that i have two page ids i went into a lookup and so if i have a way to go peek in which one actually exists first maybe go check that one prefetch the other one again it depends on the implementation so you don't have to check but but depending on what got inserted and how things are moving around like uh you know right right but the fact that we're coming up with sort of so many is there another woman is it just he's going to bathroom what's he doing all right anyway uh yeah so the the fact we're coming up with different ways to do this this shows you how complicated this where it's like linear prepassion you just kind of rip through it right all right quick question this question is in a general system what what is the default i don't know like i don't we can go look up on dave's code with the default it might be three i have no idea yeah all right i want to get through the chain hashing linear hashing and extendable hashing because you know we'll need this for one of the projects um so again all of these protocols i've showed so far these are all static hashing schemes again if we run out of space or we loop back around then we we need to you know double the size of the hash table and repopulate and that's expensive so we'll now talk about different techniques to incrementally resize the hash table without having to rebuild the entire thing so the most common one's going to be chain hashing and again this is what most people think of when you think about hash table sometimes um and then but then we'll look at two more advanced techniques uh that uh that actually are used in in real systems so chain hashing the basic idea is that instead of having this giant array of of all the slots where we actually insert keys our array is just going to be pointers to essentially link lists or chains or buckets where all the keys that map to that slot in our hash table will be found in that in that link list right if you allocate a hash map in java this is essentially what you get and so the the link list part can essentially grow infinitely because again if in the worst case scenario all my keys hash to the same slot I'm I'm just appending to this giant list and I'm falling down or basically end up with a sequential scan but again I ideally if I have a hash function that's good I won't I'll have a good distribution of uh of keys the way to think about this is that we're essentially partitioning our our giant hash table we have before into uh to smaller hash tables themselves or smaller tables um we can get unique keys doing the same tricks we did before right just keep appending the redundant keys to this giant list all right we can still use tombstones but oftentimes compaction is just it's just faster in this case all right so now again we have here we have our bucket pointers and this is where hash functions are going to hash into and then these are just be pointers to uh the different buckets that exist right so if we want to put a in we hash it mod in by the number of bucket pointers we have and then we land in that bucket we find the first free slot and we just insert it all right say anything we're going to put b b goes to the top here just just as before and then now in case of c c mash it hash it to the same bucket uh where a is located we just scan through sequentially till we find the the first free slot you had to call yeah all right you put d in uh d goes where where a is it scans through all the slots are empty you can put something in the page header and say i have no more free slots therefore always expand me when you get to me it doesn't matter and then uh basically the this page here will then point to another page where you can find d and then we want to put e and follow through till we find find e here all right and f f can go here so again the nice thing about this is that i can grow the the the key list within a bucket without affecting other parts of the of the of the table but you can have a sort of like two level two level hash tables where like this is a hash table that takes you into these buckets another hash table um or simplicity we're just showing it as a link list like this but doing that yes question this question is when you create a new bucket how do you determine the size of it uh so we're not talking about whether for this lecture we haven't talked about whether something's like backed by pages on from on disk or in memory but like if it is a uh assume it's backed by pages that are on disk and are buffer pool so if whatever the page size in the database that'll be the page size of a bucket so in post goes as eight kilobytes my sequel is 16 kilobytes right yeah i mean i'm showing within one page two two keys because it's a power point right so again if i have a lot of keys hashing the same location this this linear scan here can be expensive so actually a really simple optimization you can do is in your bucket pointer list you all store a bloom filter that just tells you whether a key exists in in my link list so if i want to look up now and g i first check the bloom filter i ask it whether it exists or not if it if yes then i then i'll keep following the pointer and go then scan along till i find the thing i'm looking for if not if it says no then i don't do that scan so that avoids that having to do that that extra traversal so i don't know what a bloom filter is no okay so i ask hold up bloom filters are awesome and they'll be they'll be useful for a bunch of things all right quickly a bloom filter is a probabilistic data structure that can tell you they can answer set membership queries so a filter is different than an index an index tells you for a given key where is it in this record id or in this page a filter can only say does the key exist yes or no can't tell you where it is it just tells you whether it exists right so a a bloom filter it's the guy was named bloom i think from the 70s um so the the the bloom filter is a probabilistic data structure meaning like it can tell you with with absolute 100 correctness that a key does not exist but if you say it can tell you that a key does exist and it might actually be wrong right so it can give you false positives and you can only do two operations on the basic bloom filter you can do an insert and you can do a lookup you can't do deletes we'll see why so it's basically just think of like most of all it's just a bit map right and a bit will be set based on uh the keys that get inserted so say i start inserting members of the wu-tang clan right so i insert rizza and so i'll have the some hash functions i'll hash it again same hash implementation just a different seed i get some hash value out and then i mod it by the the number of bits that i have in my my bloom filter and then whatever that number is i set those bits to one right flip it from zero to one i insert just same thing hash it mod it by the number of bits and set those bits to one now if i want to do a lookup like on rizza same thing i just do a uh hash the key i'm looking for mod by the number and then i go check to see whether all the bits uh bit locations that i've i've hashed to if they're set to one if they're set to one then i know uh you know that this was set you know i know that i sorry if it's set to one then i i i think it could exist but i could be wrong because something else might have set those bits right so i'll get back true for this if i do a lookup on reqan the chef again when i do a lookup one of the bits has set to zero so i know that cannot have been inserted because otherwise there's one of those bits when all those bits would have been set so i get false but i i look up odb rest in peace again now i can get a false positive because i never inserted it but it's you know his bits were set to one so therefore it's uh you know it's it's true but it's actually wrong so you can put that bloom filter in front of the the your bucket chain and that and it'll be populated with the bits set for the keys that were actually inserted into it and i can maintain it incrementally because every time i add a new insert a new key into my that bucket list i update my bloom filter right there's different variations of bloom filters you can have different levels of them you have decaying ones the the size of the bloom filter can vary their hash functions there's a whole bunch of different things but like this data structure is super useful as we use all throughout the system yes this question is how does rate false rate of false positive change as you sign the blue book there's a some formula that says like for if you want like a one percent false positive rate you need to have a bloom filter this size and and but this number has functions this question is it exponential linear i don't know i remember but there's this website here the bloom filter calculator you you say what false positive rate you want how many keys you have and it'll tell you the size of the the bloom filter you want and then the uh in the number hash functions yes so the question is how does bloom filter attain the relation they don't right there are variations of them that like with multi-levels you can do it for a basic one they don't bloom filters are super useful we'll use this throughout the system in a bunch of ways we'll use it for hash joins because again it's a lot cheaper to go look up to see is it in my bloom filter then go look up you know actually follow a page and look on this to see whether something exists or not okay so a more sophisticated scheme is called extendable hashing and this is gonna be like chain hashing but uh we're gonna allow the the we're gonna be able to split the buckets to avoid these infinitely long uh bucket lists and we're gonna split it in such a way that we only uh we only need to do it incrementally in a small part of the hash table rather than having to rehash everything and the key idea of this is going to work is that we're going to expand the number of bits we have to look at when we do lookups in our in our bucket list or bucket hash table to go find the the bucket chain that that we're looking for and we can vary this per um uh sort of per per value per key type not key type we can vary this based on uh what bucket list we're looking at so it may be the case that two different location multiple locations in our in our bucket array will point to the same bucket list but then that can expand and break up as as we need as we go along um so i i didn't actually think this is this is a bit complicated and i didn't think actually any system actually uses it um but it turns out gdbm which is a new database manager think of like you know uh a key value store that you like sort of like rocks db or sql light you can run this embedded in your system they that's based entirely on like cinema hash tables and then asterix db is a was a big data project at a uc urbine um and they have a they're using cinema hashing in their implementation right so let's see how this works right so the first thing we're going to have is that we have our again our slot array and it's going to point to our bucket list and then we're going to have this this global identifier that tells us how many bits we need to look at for our hash values to determine how we do our lookups in our in our bucket array and then for sort of bookkeeping reasons every bucket list as well will also have what with our local bit uh bit sizes the number of bits they need to look at so you can see in the case here uh these first two slots here they're both going to be pointing to the same bucket list whereas these two ones at the bottom they're going to be pointing to different locations right and this is because the the we need to look at globally we're going to look at two bits but for the the first two entries when the when the the significant bit is zero they're going to reuse the same the same bucket list identified by the the local identifier up here right so let's say now i want to do a lookup on on this key here i hash it i then look at the the top two bits because that's what's set in my global identifier global counter um and then i hash at this location i just follow the pointer and i land in that bucket and i can just do the linear search to find the thing i'm looking for say now i want to put b b again i globally i know i need to look at the top two bits i do a lookup in my my my my bucket my bucket list uh based on those two bits then i land to this location here and i go ahead and insert it but now i want to put c in and if i look at the last two bits it lands in the same location when i started b but now this bucket is full i can't put any more entries in so i need to expand the number of bits i'm looking at to now expand the the number of options that i have so i'm going to increment the global counter to from two to three i'm going to double the size of the number of pointers i have in my my bucket array but then the and you can create the new entry but then in the the when the bit is set to zero they're all still going to point to the first bucket here because i haven't that one yet so i only i need to look at one bit for that for the the next when the bits are one one that points to this other bucket down here and the same thing for these these other ones right so now when i want to do a lookup to put c in i need to look at three bits i followed that pointer here that then takes me to this bucket location right so going back here when i did my split i had to resize these guys just slid down and i only had to insert uh one new bucket uh but i took what was here because this one was full and i just split that one and created the new bucket for it i didn't have to touch the one at the bottom and they had to touch the one at the top i do have to double the size of this but like that's uh you have to take a latch on it when you do it because you have to make like a copy and resize it but it's not it's not that big of a deal you can do that pretty quickly any questions about this yes is this good so resizing this lot of rays relatively cheap the it's clever it's a good idea sorry it's a clever idea whether or not it's good or not um it engineering wise it's a bit tricky to keep track of all the metadata where like you know what you know what bits i need to be looking at as a hash into it um but it's basically just chain hashing so all the benefits i get from chain hashing are applicable here it just i have an extra mechanism now to split things up so i don't have this infinitely growing uh you know uh linked list so it's just a way to to handle incremental resizing in a way you couldn't do in uh in regular chain hashing yes i think yeah good linear probing is probably the easiest thing to do and the lock the whole table and double the size of it is sometimes is good enough right so the last one is linear hashing um and this is actually what postgres does um or it's something very close to this and the reason why postgres uh well there's another system called berkeley db that also does this um the the the company that built berkeley db was a company called sleepy cat software so the people that build wire tiger they they they they they originally started sleepy cat that got sold to oracle so oracle owns berkeley db and then they went out started a new company instead of calling it sleepy cat called a wire tiger like a like a you know tiger and cocaine or whatever um it was trying to be the opposite but but the the woman that wrote the linear hashing implementation in postgres in the early 90s was the founder of berkeley db so she wrote it for postgres and then wrote it for berkeley db all right um and she was uh one of stonebreakers pg students at at berkeley so the way linear linear hashing is going to be more complicated than maximal hashing potentially but um the basic idea is that we're going to keep track of uh uh the next next bucket list we want to split um and that when anytime there's an overflow in our in our in our our bucket list chain and anywhere in our hash table whatever we're pointing at with our split pointer that's the one we're we're going to split and the idea here is that again we want to do this incrementally and not have to lock the whole table while we resize so we can make small changes as as we go along and the idea here is again you're amortizing the cost of resizing so like it's sort of shared across multiple workers so there's not like one worker who's the unlucky one that shows up tries to insert something and then you know they draw the short straw and they're responsible for resizing the whole thing you do it and incrementally as you go along and that sort of smooths out performance so again the idea here is that we're going to uh we're gonna we're gonna be able to split a uh split whatever the next one we need to split which may not be the one that overflowed it should be whatever the next one is in in our incremental order and then we'll have maintain multiple hash functions that that are going to help us determine uh which location within our our our bucket list we should be looking at let me show the diagram and this make more sense so again just before we have we have our our bucket list here and that's going to map to uh you know bucket chains and then we're going to have a split pointer that's going to say here's the next thing we want to split anytime any anything overflows in our hash hash table and then we have at the very beginning we assume we have one hash function that's just the key say it's the you know the identity the key mod by the by n for simplicity reasons but again assuming like it's taking any arbitrary string or any arbitrary byte sequence and spitting out integer all right so say I want to put I want to get six uh I do my lookup uh and at two and I follow along and I find the key I'm looking for like that that looks just like before nothing nothing special but now I want to put 17 and it should go into this bucket here but that thing's full so we're just going to do an overflow just like chain hashing we're going to extend it with another another another bucket and insert it into the uh insert it into that that new page but now because we overflowed we need to split whatever the the split pointer was pointing at so in this case here it's pointing to uh to bucket zero bucket list zero even though that didn't overflow so what we need to do now is look at all the entries inside this bucket list and we're going to rehash them based on the uh based now on two n because we're going to incrementally grow the size of the the bucket list by one each time right so we had we had four entries now after we got a split now we'll have five so we go through and and this point's there for every single key we're going to rehash it based on on instead of mod n but mod two n so eight mod eight is zero so that stays where it was 20 mod eight is now four so that's going to get moved down to this this new page down here right and then now the split pointer just moves down by one uh and we continue doing whatever you know uh continue operating on the hash table right so now i do a get 20 i first uh when i first hash it i would get zero but then i know that the that location in my in my bucket list here is above where the split pointer is going to be pointing at so i know i've already split everything up above it so after i mod it by four i got a mod it by eight now to figure out where it really is and then that's how i can find it down here at the bottom so i want to get nine in this case here it's pointing to exactly where the bucket the split pointer is pointing at so i know i haven't split it yet so i i can just only hash it once and i scan along the the link lesson till i find the thing i'm looking for right and at some point the split pointer will get to the bottom and i'll have i'll have eight slots and i just loop back around and start all over again so this seems kind of again counter intuitive that like i'm not splitting the thing that overflowed i'm splitting to whatever the split pointer points at but the idea is again that like if you know say this this location or this you know slot one this thing is super hot and i keep overflowing overflowing uh well i'm eventually going to split it right so eventually everything everything gets split out into a re you'll resize correctly yes uh so the question is when when would i actually wrap around because you each have one so you would get to the point where like so you'd be like five six seven and then you'll be seven and then you'd have to um you'd you'd loop back around to zero because you know that like from from when it's here when it's only from zero to three you yeah once you get past seven you know where you start at starting point that that's to end from where you started that so then you loop back around yeah you add a new page but like i know that i should wrap around when i get uh when i go to eight at position eight because i when i started i had four so two times four it's eight so when i get past eight i loop back around then then you do that until you get sixteen and loop back around good idea or bad idea it's clever right again it's a nice technique to again do this incrementally uh but again there's a lot more bookkeeping a lot more machinery in order to actually implement those yes and this his question is if you only do when you do a look up you only hash it most twice in this scenario yes like if like if this thing is massive i could have like but yeah so actually what happens is once i get to say i got to eight and i wrap back around i can drop the first hash function yeah so in this case there you would add most two yes yes about deletes his question is at here like i'm trying to mod by by eight but what if i get into seven and i don't have it but again you you wouldn't be able to get seven because uh you'd be below the split pointer and you'd only hashed by by four not eight right so so this demarcation line it says i've split everything above and nothing below avoids that problem that like you don't land here and you really like you don't you don't hash first and land here but if you hashed by two and you land something here that you haven't split yet the split pointer waterline avoids that problem right so um splitting buckets based on the flip water eventually get you all overflowed buckets again when i've already said this when you reach the bottom you just drop the first hash function and loop back around um the in this technique also allows you to do contraction or coalescing as well because you could identify that a bucket list is empty and you could do the reverse you could could could throw it away consolidate the the well one bucket's empty so like you just throw it away and you move the split pointer back up and that allows you to actually shrink the size of the hash table right it's going back here say i delete 20 uh i mod it by four but then i realize that's below the split pointer and i got to get down to the bottom here and i go ahead and delete it but now this page is empty so if i wanted to i could just move the split pointer back up and then drop that last entry and drop the last hash table right and obviously you need to be clever and make sure that like i don't oscillate like insert 20 delete 20 insert 20 like i keep splitting it and coalescing that would be bad uh but you could contract the the data structure based on this right you don't want to insert 21 then overflow and split all over again right i don't think postgres supports uh shrinking the size of the hash table as far as i know without this rebuilding the whole thing all right so hash tables again super useful most systems are going to implement the linear probe hashing but again you can still specialize it based on the data type and other aspects of how it's going to be used and click house is probably the best example of this um for a lot of commercial systems it's very hard to know what their what hash table they're actually using uh unless there's a paper talking about it or we know people that work there that can tell us uh you know this is not something you as like you know someone using sql an application developer you should know or care um but it's nice to know what sometimes what how these systems are implemented um because nicely my hash functions again it'll be fast plus for o1 lookups in the best case scenario but again we need to be able to make sure that we can um you know we may need to grow efficiently if if we estimate the size incorrectly and we'll see how we do those estimations later on so some systems will give you hash tables when you call create index the postgres will let you do this postgres if you call create index you can say using hash and you'll get a hash table you'll get their uh their linear hash table implementation right but this is not the default for for almost all systems when you when you call create index they don't really know why no range scans exactly yes the only thing you can do with the hash table is a quality lookups and you need to have the entire key right if my key is on uh column a and column b i can do a composite keys if i don't have a or i don't have b i can't do a lookup in a b plus tree which we'll discuss in the next class uh you can do these prefix lookups and it is the best data structure of all time for databases um tries are actually pretty good too um but you can put tries in your b plus trees uh you do a bunch of things like that um so anyway so so the the default choice for most new systems are going to be a b plus tree and that's what we'll discuss uh next week but again we'll assume it's single threaded uh on monday and then on wednesday we'll see how to make it multi-threaded okay all right hit it still got you shook up i smack you with the bottom of the clip to tell you look up show me where the safe side for i blow your face back i got a block on taps the feds can't trace that style is like tamper proof you can't lace that at the dominican or you could call me dominican black skelly black leather black suede timmelins my all black dirty haters send you to the peri gates you get gizama trying to skate and that's your first mistake i ain't lying for that cake your fam i see you wait my granddad's heavy weight then ran through every stake when he asking how i'm living i tell him i'm living great