t fails so now you start to see that if you're using composite types anywhere understanding this becomes really important because there is no way to enforce on the creation of a type that that a field within that type is not null right I actually reported this as a bug back in 8.4 I think and I was told can't fix it because if you do that you will break everything that happens with outer joins yes an array of nulls is not null arrays behave differently than tuples because yes go ahead no you can't and the other thing is that no it gets even worse than that because suppose you go oh wait but we can create domains and domains can have a not null constraint to them it doesn't you can use a new method to enforce it when you're putting it in this way and that was what I reported in 8.4 and I was told this if you try because the problem there is that if you have a domain that that can't be null and you ever query it in a left join then the return type will still be null so there is on type creation that the type is actually not null go ahead so the question was if the food type was not a composite type could you still do this with a row the answer is no because a row only works for a composite type so this is the syntax of how you would insert a composite type into an insert so this is the first this is the first beginning of some of the more advanced problems one of the other problems that people run into here and we're going to talk about a few different variations on this are anti-joins anybody here not familiar with the term anti-joy I will explain it sorry I managed to pull off the thing again don't worry about it I'm just going to keep going at any rate at any rate now this is an anti-join an anti-join the simplest form of an anti-join is a not in clause we want to select rows from this table where a join condition to this table is not met okay that's what an anti-join is so the simple thing is to use a not in clause now I just want to show of hands how many of you understand the problem with a simple select like this with not in if you have nulls only a few of you have probably been bit by this so far if some vowel in B has any nulls in it anywhere this will return exactly zero rows it will return a null set why? because not in will treat null as a unknown and we don't know if this matches or not so therefore it might and we can't return to anything okay so this form is bad it will bite you and people usually get bit by this at least once in their database careers so the minimal fix here is just to do where some vowel is not null go ahead okay now this kind of this works it fixes the proximal problem but problem here is that if you standardize on this format sooner or later you're going to forget that not null is not null and you're going to go back to the same problem in the first and the really pernicious part about this is when you test this you might not have nulls in your data later you might and then suddenly your query breaks and you don't know why so this is why this particular fix is bad did you have a question? okay so not exist is a little better but it has some problems with it so not exist will basically run this query in what is effectively a lateral join and then discard cases where where the lateral join condition is met right the problem with not exist is a little bit more abstract not exist is a perfect general tool but that sub query could be anything right and as a result there is very little that you can do to make sure that the planner can effectively choose the best plan in this case right if you're just doing an anti-join not exist is difficult for the planner to deal with I have very rarely seen not exist perform anywhere near as well as I thought it should so not exist is better it's going to get you out of this trap but it's going to create a whole bunch of performance problems in the meantime go ahead so the problem here is again this gets treated as a lateral join and a discard so whatever is evaluated in that loop it's hard sometimes for the planner to compose it into the plan okay yes I have found not exist to usually perform at least on Postgres slower than not in but there's a faster version that I'll get to in a moment okay um the best approach sorry it got rid of the dot a dot star so that should be select a dot star from a left join B using some vowel where B is null this one I usually find performs way better than not is and it even performs usually better than not in sorry not exist and it usually performs better than not in and the reason is that that the left join side is much better optimized and consequently it tends to be a lot easier to compose this on the planner side it's a little bit more um it's a little bit more how should I put it verbose and it's also a little bit more limited in other words this only works on anti joins you can't do some of the more complex logic you could do with not exist right but because it's more limited the planner can make some better assumptions so my general recommendation actually is to write anti joins as left joins where the left side is null and then it will simply do the left join and it will discard it anywhere that join condition is met and it will do it as a left join rather than as a lateral join so this is so this is sort of the initial piece here I have a few more examples that I'd like to get to if we don't have questions are there questions at this point or should I just kind of continue to a few more unexpected pieces okay at this point I think I'm going to pull up a let's see this is probably too small for everybody to see here so let's see it's control shift let's do this okay so since many of you have not seen Bruce's talk anybody I just want to see a show of hands how many of you think select row parentheses is null will return true show of hands how many people think it will return false show of hands false people have it let's see oh it returned true how many people here think this will return true nobody how many people think it will return false it returns true also the null tuple is both null and not null this is Schrodinger's tuple if you understand the rules by how null tuples get get or is null gets evaluated with a row type then this actually kind of makes sense but it also really doesn't and it's a really good example of the lack of logic behind nulls per se so the basic thing is if I do if I put a comma in here so now I have a two value I have a tuple with two entries now we get what so why didn't yeah I think I'm going to just create a type here oh yeah as probably is just I can probably do this why is this okay so I'm not sure why it's not accepting the comma in here oh you're probably right no oh yeah okay let's yeah I'm just going to create a type foo too I should probably clear these up between talks so as we have foo three now for some reason this has worked in the past I'm really not sure what I'm doing wrong but I don't think so because so if I just do select this I still get the same problem if I still do this I still get the same problem just a second yeah it should be a row I'm probably I'm probably missing something really silly here probably miss typing something or so forth but the general rule is that a row type is null if and only if all fields are null right yeah there we go see so this is now a null and this is now and this is not not null right so in the case where we have so in the case where we have a null row right if every field is null then it's then it is a null if any field is not null then it is yes but it shouldn't be reporting false on both of these what I'm noting here is that for some reason it is it is casting it is returning I know what the problem probably is so I'm not entirely sure why this is one of the things which I find about nulls is I sometimes get blindsided by my behavior the rule is supposed to be is I understood it that if you have any non-null field then the row type is not null but here we're seeing that it is neither null nor not null yes and now it seems to behave behave as we expected before so now what we're seeing here is that there are values which are neither null nor not null and there are values where if every field is not null then it is not null right so now what you find is that if you have a tuple which has zero fields in it then it is true that every field is null and it is also true that every field is not null so it is null and it is not null but if we have a field where if we have a row type where one piece is null it seems to evaluate not only to it is not not null it is also not null yes yes exactly yes so this is actually a really good example of why I usually test almost every weird case of nulls before I demonstrate them and I seem to have missed one of them today and I actually learned something up here but this is also a really good example of how these rules often don't make sense so let's see I had so this is actually interesting because if you look at a row where you have both of these is null right then it is null but if we do this array of nulls is not null again there's no consistency and the reason why there's no consistency is all null actually means normally is no data here it is just a null reference and then when we started to say okay let's determine whether something is null or not null now we have made a mess of things which we are never ever going to be able to get out of yes well if we do an array with this it will still be false an array which even if I have a zero form array is also not null so a zero member array is also not null arrays are only null if they are explicitly null any other value is not null row types have extremely inconsistent logic and lead you to cases where things may be not null and may also be null in other cases where they may be not null and at the same time not is null so this is these are pieces which really kind of highlight some of the difficulties again one of the really cool things you can do here is you can actually leverage this when you're doing anti-joins like I mentioned before you can say we only want we only want those cases where we actually have a match on the row but now I'm starting to wonder about that because even though I use that all the time this row handling is weird and it's not what I expected so I'm going to actually have to go back and double check so to speak however just a second I want to see something here if I do this so that is still okay so I'm going to just check this also maybe it may be about casting nope I'm stumped by this this is this was this is this was not expected for me so we've kind of talked about like how nulls are stored and some of these things we've talked about the three-valued logic we've talked about the history and the fact that these are null references that they don't actually necessarily mean anything are there any questions on this side at this point I know I'm quite a bit early I'm trying to think if there are any other interesting examples that I can go over I have a question when did you actually use the array type in a collection type when is it useful besides why can't you just create more columns in a table so so what I usually suggest here is thinking so arrays are useful in one of two specific kinds of cases okay the first case is it may be useful to represent a single value as a series of sort of sub-values so as a completely contrived example and then I could give a I'll give a more specific example it's a completely contrived example you could break down an IPv4 address into four octets and so you could store that as as a four member array right that sort of thing can be very helpful if you needed to do processing on one piece of it like if you want to you know figure out whether something here has one of those members being a particular value a real-world case I would give on this would be when I worked on a big life sciences database we would generate something called BLAST reports and I'll just give you an example of what a BLAST report is BLAST is I forget what it stood for I think it was like basic something basic lateral alignment search tool I think is what it stood for and the idea is that you have protein sequences right or genetic sequences and you're looking for similar sections in different ones and there may be offsets and things like that and so what we would do is we would run the tool that would do these searches and then we would store like the top 10 results in an array we could have stored it in a more normalized form but the problem with doing that with the size of the data that we were dealing with storing it in an array just simply made performance a lot better because it's made performance a lot better because instead of finding rows that might be on different pages you'd get the whole row set once out of a single page and so that helped a lot in that case over time so in this particular case we would we didn't index the individual entries if you wanted to do that that would be possible but it would be a lot more difficult you'd have to probably write a function to extract what you want to do index out of each entry turn that into another array and then you would probably have to take that and generate a GIN index on that out of that function so it's possible but it's we were in this case only looking up the last reports by ID and then we were dealing with deletion anomalies by doing a further expansion and join out of that to remove things that have been previously deleted where I used it in many places especially to create big tags for example in an e-commerce site each product had a bunch of different tags let's say for a candle's e-commerce website so it was red small six by four and a bunch of different tags and attributes and that allowed to write a query that was very efficient to find now all the items that have the tags let's say red and small red and voted so because it is indexable so it was very effective very efficient also in blog posts when you many times put tags on them so filtering when you want to filter the result set according to attributes because yeah because you know it's four but if it's tags you don't know how many tags you have but like with okay so with the IP example I mean in actual practice it probably doesn't make that much of a difference but one thing that it would make is that if you were to create for example a domain that said that we have four entries here then you could create functions on the domain that could present it as an IP address so you could do things to it right if you store it in columns there are ways to do this with inheritance they're ugly but you could still attach functions to the record type to preserve it to present it however you'd like right I wouldn't recommend doing it that way because Postgres has an IP tables type no an IP type but as I say that's a contrived example so again if you have something that's structured and you need to present it as substructures that's a good option another thing to keep in mind is that SQL arrays are not like programming arrays in one really important respect and that is that SQL arrays are strictly rectangular right so if you have a two dimensional array in SQL it is not like an array and choose your favorite programming language I think I think I'm not sure I think Fortran arrays may be comparable but I can't think of any other language where arrays are comparable and what this means is if you have a two dimensional array you can't have the first the first ordinal being four elements and the second ordinal being three everyone has to have the same number right and what this means is that arrays are actually matrices so if there's any data you want to store as a matrix or you want to model as a matrix an array is actually a really good data type for that an example that I would give that I pushed for in a previous place I worked was that we would get some financial risk matrices from an upstream provider and that would have been a perfect example to use a SQL array to store just simply because the data type matches what you're getting and there isn't really a normalization you can do to it so it would just typically be like a set of it'd be strictly rectangular usually like 5x5 and I think if I remember right they would be they would be all floating points so it'd be like 0.2, 0.3, 4, 0. and then this would allow you to look at like severity and over time of a particular kind of incident that was likely to affect certain kinds of markets right and I don't really remember where we got these from but just because the data was like a matrix and then it was processed as a matrix it would make perfect sense to store that as a SQL array yeah, exactly but yeah, but JSON B arrays are not like SQL arrays right so the thing is like JSON B arrays are JavaScript arrays they can be completely heterogenous they are one-dimensional and if you want to have two-dimensionality into them then what you do is you basically have elements which are also arrays so what this means is you can have a JSON B array that's object, object array, object array, object, object value and so that's that's something where it looks more like our tuple types or record types than it but you can't store a you can't store a record type without any other type information in Postgres because if you try to do that then there's nowhere to decode the record header from so I would definitely say JSON B arrays are also useful they're relatively different from they're completely different from use cases, wise from SQL array I would say if you're storing actual array data then I would definitely use a JSON B if you're wondering where I would probably do that if I'm building for example a large log management system then that data that's coming in maybe relatively unstructured and consequently putting it in in a JSON B object makes perfect sense the question is about compression on it and yes compression would help also although interestingly enough when I was working in a previous place the compression on JSON B we could get on a column wasn't anywhere near what we could get on disk if we put it on a compressed file system so since this was almost exclusively in a PEND only workload we just put everything on ZFAS compressed that way we could ingest faster data just simply because we could trade CPU cycles for IOPS JSON B yes actually I can show you I have the ways of converting JSON to relational normalizing back because there is one function which converts record set to JSON and back and most times all the time I hear customers are saying that they need JSON because they do not know what type of data they receive it usually overrated in real life they know what they receive at least like something and it's more faster and more economical to convert them when you receive it from application to relational and then convert it on the if you're interested I will show you because I have it no I can convince everyone I can convince everybody Omar I'm just a moderator so the question was between choosing JSON B and plain JSON this is a slightly different topic but there are actually several cases where I have seen things that are supported in JSON that are not supported in JSON B and if you're hitting those cases and you don't need the gin indexing then it makes sense to use JSON instead of JSON B I'll give you just the two top examples that I have off the top of my head JSON as a specification allows duplicate keys in case you're happening to use duplicate keys JSON B will not work for you you probably should not have duplicate keys if you're using duplicate keys for something where you actually need duplicate keys then sure but there's probably better ways of doing it than that the second one is actually a really interesting issue with the internals of how things are stored the specification allows you to do unicode escapes inside of JSON identifiers and value strings JSON B when you store it stores actual strings so if you have embedded null characters which might happen for example if you're actually like trying to store actual binary data then JSON B will not work for you yes so if you do a unicode backside U0 you will get an error I can probably I'm trying to remember how this works I'm trying to remember if this is right wait I don't think that's right so I think that's the wrong so let me try this again select might be okay so we have this this will throw an error so yes don't do not ask me how I happen to know this yeah I mean the only way anybody knows this is by stepping on it right unless you just happen to be at this talk and you're going huh that's really interesting I'm going to remember that so the fundamental problem here is that the way strings are stored in Postgres internally in memory is as null terminated strings and so what happens here if it tries to if it were to try to convert it is that you would end up with possibly a null termination character inside the middle of your string and this then causes a problem and so the safety is there to keep that from happening there isn't a way of really handling it unless you want to unless you want to redesign your JSON documents to for example store things in base 64 encoding or something but that's generally speaking not the best way forward so so those are the two cases where JSON works and JSON doesn't I think there is probably a solution if you want to hear my solution you will not like it you can probably guess what it is and it will not work on fully managed Postgres instance services but your only other option is figure out how you actually want to represent this on disk and in memory and write your own type which the code is mostly there for you in Postgres you can modify but most people aren't going to want to do that can I try the same thing but cast it as text yes because this is a specific aspect of how JSON is supposed to encode unicode characters so that's a bit of a digression but are there any other questions on the null side at this point I guess not yes so if you're using composite types one more time if you're using composite types you want to make sure that you don't have a null type then you put is you put a check is not null you don't just rely on the not null on the column definition that's how you have to handle that yes all right so Chris promised that everybody is going to be stumped by nulls by the end of the talk I think he kept his promise thank you Chris hello folks up for our next talk Postgres memory internals well memory management internals please welcome Krishna Kumar hello everybody welcome to Postgres memory management internals I am Krishna Kumar all of you can call me KK I'll start off with a bit of introduction about myself and how this talk has come about primarily so I'm engineering manager for Postgres open source contributors and committers team in Microsoft I started roughly like 22 years back born into Linux and was doing embedded system porting on things like MIPS and SS4 boards I don't know how many of you probably are aware of those boards but I know that SS4 support got taken away from Postgres sometime back so that's when all this was going on from there I joined HP worked in high performance computing for a while, scalable file systems did join a startup of hybrid cloud storage and it got acquired by Microsoft that's how I landed in Microsoft continued doing storage stuff slowly moved into Kubernetes side of things for some time did a lot of open source work around authentication storage and things like that and that's how I landed on Kubernetes side in the last three to four years I was part of the team which built the managed cloud service called flexible Azure flexible server in Azure so we have a managed offering which I helped build recently roughly like last year middle I joined as the engineering manager for contributors and committers team and a lot of my job is the people I interact with and these are the folks who have commit privileges to Postgres who contribute a lot of code like Thomas Monroe, David Rowley Daniel Gustafson, Anders Froin, Melanie Bilal and Melly so all of these folks I interact on a day to day basis as part of my job so a lot of things to learn via them and this is my attempt to share those learnings with all of you and as much as this is a sharing session I would like to learn from all of you as well so continuing on this is roughly how the talk is structured will quickly touch upon the architecture just in the memory context of memory we'll talk about how Postgres uses the shared aspect of the memory how between processes it uses those then go into a little bit details of the types of shared memory that's around in Postgres then we'll talk a bit about the local aspects of it how Postgres even though really it's return in C it still offers a sort of library which kind of gives a managed language feeling when you write code in Postgres we'll talk a bit about the kernel interactions that Postgres has we want big deep but still we'll go through some of those aspects then we'll talk a bit about how to debug some of these issues in terms of not bringing many issues into the picture but just enabling all of you to be aware of these tools and how to use them and things like that we'll touch upon configs which matter and it's not like a comprehensive thing again it's more like how can we like what are the interconnected pieces here with memory and the configurations then we'll talk about projects some of this are currently out there in the mailing list some of this have patches out there some of them don't have and some of them is close to my heart so I just want to talk about it so that people if they want to join and collaborate would like to engage and then as anything else in the open source world nothing stands on its own there's a village behind all of these things these are learnings from a lot of people just want to say thanks at the end to all of them as well so we'll start off with the usual you know quote which is obligatory to put in that for a long long time the promise that Postgres at some point will become trading like will be based on trading but at this point in time Postgres is still process based there's a lot of momentum at least around the last year we have started seeing more focus on making this happen but you know it's not really happening for a long time since 1990 promises at least so at the end of the day Postgres is a process based architecture Postmaster spins up the rest of the processes and everything out there is delta's process so when new connections come in there are more processes getting created and there are like some default ones few of them I picked and put it here so the lot of memory management process of Postgres is based on this architectural fundamental right like if we had process if we had threads some of them is not needed if since we have process some of them is designed a certain way so that's how this context is important so we'll talk about in this talk we'll talk the structure of this talk is like about the shared memory aspects of it the shared process different processes sharing the same memory we'll talk about local memory aspects and the kernel memory aspects of it so just in terms of this is just a pmap output from one of the Postgres processes just to give you a glimpse into how this shared memory and like shared memory and local memory appears so in this case like the shared memory we can think of shared memory as static in the sense that there's not a typical static memory I'm talking about the static here means that when the startup happens you already you know like allocated this memory and then you continue to use it in between the processes and the dynamic side of things is when after you have a certain execution going on let's say you had a query which which Postgres wants to make parallel at that point it needs to generate new processes and then have new shared memory attached to it so that's the concept of when I'm talking static and dynamic I'll just give it a name it's not the typical C static dynamic thing but just so that you're aware in shared memory so here the picture that is there I'll go into details of this later but just to give you a sense if you run pmap here's how you will typically see this one of them is static type which is declared in the beginning and the other one is like as we go through we'll attach as needed so in the static memory side of things we will talk about how it's created that is when the startup happens how it's created and things like that we'll talk a bit about how locking is done in this framework because as you can imagine the locking will be totally different if we were to do threading so just want to touch upon that aspect of it like how locking is done in Postgres within the in terms of Postgres we'll go through some examples primarily we'll go dig a bit on what buffer pool is and how it's managed and things like that and what could potentially be the enhancements that we could make in that area we'll quickly briefly look at like SLRU and wall buffers but primarily we'll dig more into the buffer pool side of things at some point in time Postgres used to be system 5 shared memory mapping like that's what it used to use over course of time we have gotten to a point that there's a little bit of memory that we use with system 5 memory for making sure that there's no two processes which are running at the same time or two start-ups which are happening but outside that everything has become more and more of M-map stuff and anonymous allocations so as you can see in this address space the M-mapped shared memory that we have which is like typically if you take a file and then memory map that's what generally you do but in this case we don't have a back of anonymous memory that we map we M-map this area give the shared flag attaches to the primary process write the postmaster so from here on anytime there is someone some process which folks it also gets this address space with it which means that it will get access to the shared memory and then from then on it gets to use these areas so that's the way in which the shared memory is created and that's the way in which fork happens you have multiple choices if you want you can go back but typically this M-map system is what default comes and most of the times we end up using this I just want to touch upon a bit here typical allocations you can do with regular memory but if you have a huge memory and huge pages enabled in your system you can use those flags I'll talk about the configuration in a bit but you can use those to make sure that this memory is allocated in the huge pages as well so that's the creation part and how the child processes and rest of them get it like as promised we'll look at locking a bit so because this is shared area there's a lot of things that happen back and forth that happens between these processes and locking as you can imagine is one of those things that we really need to happen when we are coding in this space so in just to give the context this is not the row locking or a transaction locking that I'm talking about I'm just talking about the lightweight locking which you use when you're coding so lightweight locks are implemented in a very straightforward fashion although as you get into the nitty-gritty it becomes complicated but the way that this happens is first there is atomic operations that's possible on the memory so you use that to lock the memory if attempt fails then you get into what is shown in this you first the process first puts itself into a queue then it tries to lock again if it doesn't work it will wait and in Postgres currently we use semaphores to kind of like make sure that all of them are waiting on the semaphore and when unlock happens we go and like wake the rest of them up to see if they can lock again the semaphores currently we use semaphores but there are proposals around whether we could use the more modern stuff like few-texts and stuff like that so one thing that I missed mentioning was that the whole talk is about how Postgres is done on Linux not on other operating systems so that's why I'm using few-texts and other things just so that we have the setting but in some cases we use something like few-texts semaphores itself in Linux is implemented on top of the few-text system calls nowadays so there is trade-offs around that but in general the process is it depends on the atomic locking and it depends on the semaphore as well and there is a wake-up process that goes through in locking so this is how the locks are designed and this is what gets used that Postgres processes have to communicate between each other a bit of detail around this that it doesn't have sophisticated things like deadlock detection and things like that so when you are dealing with this please be aware of those things so that as a programmer you have to deal with those things there is shared an exclusive mode in this locking available and typically in literature the latch is what folks use but I'm just calling it lock because of the name of the functions and a lot of this is like in the short-lived situation like I was saying in lightweight locking scenario so that's about so we covered the shared memory aspect of it how it's created now we talked about locking a bit we'll go into one of the examples that is front and center of a lot of usage of this so Postgres as you can imagine needs to be performant so anything we access from the disk we want to be able to cache it also we don't want to operate we want to make sure that we operate more efficiently which means we have something called buffer pool in Postgres and this buffer pool is implemented on top of the shared memory that is there and statically declared one and here the entire buffer pool allocation is done into blocks of areas you know blocks of sizes and then there is like descriptors which are defined on top of it which is just like metadata for each of these blocks a lot of times this particular buffer pool that we have in Postgres is optimized to get to a place where we have the most recently most recently used like page as well as the most frequently used page itself so it's a give and take kind of scenario where we have to balance these things out but at the end of the day currently Postgres users are very let's say it was somewhere I think in 1960s or so the page replacement algorithm that's there we will give a brief about that like effectively we do something called a clock sweep where every time we need to evict a page from this buffer we go around through the buffer the ring buffer actually doesn't exist but based on the descriptors we go through this buffer and then we keep reducing the usage of this each of these block buffers if it gets to zero then we can evict it because recently nobody has come and updated it so that's kind of the basic logic that we have and there are a bunch of discussions around this on whether we can improve this part of it but right now we still are using the clock sweep algorithm one challenge with this is that in Postgres there is a lot of activities which could actually trash the entire buffer like vacuum and sequential scan is a two set of such things where if we start doing the sequential scan we could actually just fill it up the entire buffer pool and trash the regular rest of the IO patterns that were there so in that context we use something called buffer strategy control wherein fixed set of blocks from this block for blocks that are there are the only ones that we can touch so which means that when vacuum comes up and tries to do some of these reads we do this buffer strategy so it doesn't go and uncontrollably trash the entire buffer pool it gets fixed size that it can operate on so that is how we tend to make Postgres tends to make the scanning like Postgres tends to make scan resistance in this buffer pool but at some point in time a sophisticated algorithm probably something like CAR which has built in scan resistance could help here as well but overall the theme to take away is that the shared memory that we have here is used for buffering the contents from the disk and we do have a page replacement and we will go to them built into this so just to give a picture here this is the buffer pool content how it looks like so one thing that I didn't quite touch upon was there is this buffer hash table so whenever we have a need for the buffer we need an easy way to look up the buffer so if you look at the buffer pool the rel file node and like these IDs right the 5 IDs after buffer ID those are the ones which are used to map in that buffer hash table because of that we have a very fast look up mechanism as well we use this fightable look up the table and then figure out the blocks from which we access in general in the operations that we deal with that's an optimization we have as well the rest of it is all like this is from a table which I recently populated so as you can see there are fields which says whether this buffer block is dirty the buffer IDs of this and a bunch of these other values that are required to maintain the buffer pool the other many of the things which use this shared memory while buffer being one of them SLR use another thing which again uses a page replacement algorithm and there's a lot of discussion which has happened in the past on whether the SLR use can go into the buffer pool itself because they kind of solve the same problem but in a different way can we enhance that to focus on one algorithm or usages another thing so just laying out these are the other SLR use which are there which we which we use in the shared memory as well before I move on to the dynamic part of it any questions yes unfortunately I don't know but I will get back to you because I know someone who really knows this yeah Thomas Monroe knows Thomas Monroe like he knows it I mean like not like he's working on that but he knows all of these details so one okay yeah I don't know that part but I was just using like the user session like I didn't no this is stat this is not no extension sorry yeah I do know that just that this is available I don't know which version is available I was just using the head of the tree so let's move on to the dynamic shared memory part of things so there are scenarios which we really need dynamic shared memory like parallel query execution being one of them where we have not allocated the memory in the beginning so we can't use the you know fork magic that we rely on we need some some way in which we newly allocate this memory then attach it to the the processes that are there so the way in which so there are two types of dynamic shared memory that is the names I just there's DSM and DSA so dynamic shared memory like DSM was the one that first came so and then DSA was built on top of that for more surface together operations like if you want multiple DSM segments and want to manage the allocation better things like that is where DSA came into picture in DSM scenario like if you look at the right side of the slide this is how the operations really happen we take a file give it and like an ID at the end like inside DSM then we map that into the system and then pass this ID to the worker processes or the next set of processes which has been in the process and then attach this to there on address space as well. So the secret source here is really that shared memory attach and making that happen here I'm showing you something which is not recommended when you deal with things but just enabling parallel query by default and just running a select thing so that it spins off some workers and opens it then M maps it and later when the query is over it unmaps it and then unlinks it so this particular shared memory file is what gets M mapped so the processes will get with each other. One of the like one of the salient problems that is there with this is that when you do this dynamic shared memory the process like in terms of address will be different in different processes previously we could have relate on that but here we need to actually make mechanisms to make sure that we have a different way in which we can map and figure out where the memory was. So in both DSA and DSM this kind of works out fine but DSA has more like friendly usage of segments and things like that and it was developed both of them like the original one I believe was developed when we were starting to get to parallel queries and parallel has joins when we go to it that's when DSA was developed as well so that's the way in which dynamic shared memory is dealt with so we crossed over the shared memory the static and the dynamic part of it went through an example now we'll talk about how local memory is operating local memory is like think of it as maloc when you do in a typical application where you need objects to be allocated in the memory to make things happen and that is where it's used so Postgres offers this I would say infrastructure where there's something called memory context and memory context has various APIs like allow, realloc, reset, delete all of these APIs and it is sort of an interface kind of model where there is memory context and then you have implementations of this so a typical implementation of this is aluxet which is what is used in most cases then there are like specific implementations which folks have developed over course of time to improve the memory management part of it slab is one of them where we had to do like a large equally when we are doing a large equally sized objects slab is what we should be using and I believe in logical application we had used that significantly in the past the one important aspect which comes in very handy is that there is something called current memory context which always will point to your current context that is there and I'll explain the next slide why that becomes important so this is how the memory context itself is built with a hierarchy in mind so the right side shows the top memory context which is what we start with in every process that we build we have top memory context and then from there we have a child memory context from there and so on and so forth we can go to levels multiple levels of this so at any point in code so we don't have to pass the memory context a separate function parameters and things like that it's all there declared so that whenever you are in a particular function and then you want to look up what's the current memory context you always have like I was saying the current memory context all of them is hierarchically tried in the way that when you from a parent you can get to the children that have been allocated the way that this comes in handy is that you don't have to manage the memory that if you had an error you need to come back and free this up like you just can free up the parent context at wherever you are and the children will get freed as well so this is already available within postgres so you don't I mean postgres core code so you don't have to think about memory management a lot of times and the malloc portions of this are the internal of how the memory is managed and whether we should free it up all of that is handled within the memory context so we don't have to really do this we can call the free thing but that the implementation will determine what's needed want to touch upon a bit on how exceptions are handled exception is not really exception handling in other languages kind of exception but I'm talking about if you had a particular location we use this set jump kind of mechanism where if it goes back into the stack at a certain point it still will free up the memory because we would put hooks there which make sure that the set jump like the capture of that the handler of that will make sure that we free up the memory there as well the last point was what I mentioned earlier the last point is another technique which we use where we don't when we start allocating we start allocating the contents of this like the memory context of this until we get to a point where we can move forward we don't assign it to the parent process what that means is that sorry parent context what this means is that we can handle the errors very easily if you have an error we can just free up the entire block instead of like trying to free up these by piece and at the end when we are safe and steady we can do a setting of parent context as well so that's how this is managed yeah so that's the local memory context part of it before we move on to the current side questions yes so the test environment there is an option to enable asserts by default for example when I change code I enable them by default and then when you test them these things like get caught up but like if someone has put the assert there you would get it so it's a little bit like context specific that we'll have to look at this case and see it but there is an option to enable this like while compelling the nowadays mission what we all use so while configuring we can set it and then run it so because we have processes as is the case with anything in Linux there is some amount of memory conception that every process takes something like the page tables there's memory for stack and things like that but I'd like to talk about this concept of double buffering that typically happens in the Postgres world so when we talked about the buffer pool the idea was that we would have a cache for everything every content that was there that we deal with from storage but most of the time Postgres is on top of file system so at that point in time when we are dealing with when we are reading from storage like reading a file for example it also gets cached in the internal page cache and this is one of those things where we have the we potentially could have the same copy in memory inside Postgres as well as inside kernel right like page cache inside kernel and buffer pool inside Postgres as well so that like it's sometimes very useful sometimes not very useful but I just wanted to bring that topic here so that I can show some of these ways of debugging so basically Fincore is one of those tools that you can use to figure out the contents from the buffer pool in this example I am looking at a table called my large table and then I filled it up with some large amount of data recently written and then looked up the Fincore to look through the buffer cache and sorry page cache of the kernel and then as you can see the number of the so when we eventually look at the file system layout the already of this will be what will be there in the file system so that one when we look at the file node relation we can find that the pages are cached appropriately inside the page caches now off late at least few years since we have been working on this thing making Postgres to go to a place where we have Asynchronous and DirectIO built in both are things that we really need eventually for better performing you know like better performing Postgres a lot of the group in Microsoft like we work on that like Anders Thomas Melanie for example work on it and David too so when that DirectIO comes what you will start to see is that this page caching is not like the pages in the cache that is shown on the right from the kernel will no longer be there as well I was trying to show a screenshot of that but didn't get a chance to put it in but right now in Postgres 16 you can enable DirectIO in debug mode then you will start to see that we no longer cache it inside the kernel there are performance aspects of this it cannot be used directly and that's the part where the development phases of Postgres is taking us through some project milestones where we will get to a place where we have AsynchronousIO and DIO built in and then we will solve some of these performance challenges with buffer pool as well so that kind of concludes the type of memory we talked about shared we talked about private and local and we talked about kernel memory consumption we will talk a bit about configuration a lot of talks here already covers a lot of this so I am not going to be comprehensive around this just touching upon some things which really are interesting and curious when it comes to memory management so shared buffers is the first thing it will determine the buffer pool aspect of it make it big then you might actually consume memory in a way that kernel itself won't have memory to do stuff for its page caching so there needs to be a balance in that that's the one thing to remember but some tweaking here could help as well in a lot of scenarios when we have very IO bound workload we have work mem which typically is the internal limits around this but the fact of the matter is there is really no limit in most cases because every process that kicks in let's say parallel workers or every user session or partitions that kicks in it thinks that work mem is its limit so that means that if you add three or four of them all four of them would be actually looking at the work mem so in a simple configuration this kind of works by setting the work mem but you have to be aware of these other corner cases to currently make sense on how much actual consumption of memory is out there and what the limits of them are huge pages is yet another thing there is on, off and try and on off is like it will try to allocate if there is huge pages available if not it will move on and try to do the regular allocation the important thing to remember is that huge pages might be needed to be reserved in advance and that will help you to make sure by the time postgres comes up it gets access to the huge pages by default the next setting is something called overcome it setting this is by default postgres documentation is suggested when you have a regular Linux machine you don't have this setting on and postgres says hey set this up kind of thing but the thing to remember here is that in a typical Linux system when you let's say you do a malloc and then you you try to write to the malloc that is when actually the pages would get faulted in and allocated properly and there is no limit you can do like mallocs for a large amount until your virtual memory stuff but you could have a place where your physical memory is still not used and you will be fine but when we use this overcome it setting that is typically recommended in postgres systems if you do a malloc and every time you do malloc the memory gets reserved by the kernel what that means is that postgres who can expect the behavior where when we are running out of memory we will end up in a scenario where malloc would return us error rather than a fault right like out of memory fault kind of scenario so that is the kind of expectation of this that there is a more graceful behavior somewhere I think folks had the expectations that when a new user session connects and it's not able to allocate memory it should be killed and stuff like that but typically if you run into this situation then the entire thing goes bad but the good part of this is that we won't have to actually do a lot of scenarios that we end up we don't have to do recovery right like that's where it will be a more graceful shutdown because you get a proper error return from malloc so that's the story behind the setting but something to be careful around when you are dealing with many many instances of postgres in a cloud environment if you are setting this there are consequences of this especially in low-end skews and things like that so please be aware of this setting yep yep no magic yeah but work mem is tricky that's the message I want to I mean typically when we configure it we expect it to behave all fine but there are scenarios where we are not calculating max connections is not enough actually but I would say a typical user we don't have to worry about it but as we get to sophisticated users of let's say we had a lot of parallel queries going on yeah there you go so a bit of back and debugging sort of thing like things which we could use to debug some of these things where actually our memory went to the end of the scenario like here is a couple of things we can use which is one is as I was explaining earlier the shared memory allocations the PGSMEM allocations view so I've put that on the right like there you can see all the shared memory allocations like it got snipped at the end but like all the shared memory allocations that potentially you can get using this you will be able to understand like what statically and what dynamically these things will help you to get a sense of it back and memory context is the local memory context that is there the thing to remember here is that we will be getting for that particular connection like what's the back end thing you can give say that hey I want it for another process let's say you want it to figure out what the memory conception of that is that also you can send but it will be logged inside the system so combining these two you should be able to figure out what's going on in the system in general because allocations are happening in these two areas this still doesn't cover the kernel side of things like you still need some more magic to make that happen but this should be good for us to debug some tools and extensions like pg buffer cache is the extension I was demonstrating earlier which can tell you what is the content of the buffer cache that is there the pg pre-bomb is another tool typically people use to pre-load the tables into the buffer pool as well PMAP is another tool which you can use to actually map the entire process on the right here and then figure out what is allocated locally what is allocated like in shared memory and things like that so these are tools which when you end up in situations where things are not going well life-saving tools at the end of the day now that we have covered some of the fundamentals of all of this let's talk a bit about some of the projects some of this are ideas which are floating around some of this have consensus some of them don't have they are very contentious in the English so just wanted to lay out some topics around this so there is a patch which is coming up in PG 17 which we did as an internet project and we have a group and Thomas helped guide this and we are working on this is to get to a place where we can invalidate the buffer cache like if this tool can be like it's part of the core postgres you don't need extension but then you can call this invalidate buffer cache and then you can get the buffer cache fleshed or dealt with later so this is not to be used in when you have to debug something as well as this is a building block to some of the other things that we will talk about so the next topic is like the memory shrink and expand scenario where right now everything is kind of like static if you have a buffer pool that is actually static when you want it later you still need to restart and throw away your context and build this thing whole later but the place where we could reach is that postgres just stays on and you can just expand or shrink this memory and that's the idea behind this so we could use it in let's say serverless environment where we want to decrease the usage of memory as the more and more of these user interactions reduce we can decrease the memory and as the user comes in we can reduce the memory so right now postgres doesn't have such things it's all very static so that's potentially one area that we could over booking is another scenario which comes up when you have a multi-tenant scenario and you want to make sure that you can put two or three sorry not two or three you can put multiple instances of postgres into one let's say code node one of these instances we can free up and give it to another instance and things like that which involves kernel at that point which involves hypervisor at that point and the whole stack but this is another area where something like memory shrink expand could be useful and anyone interested in this space please let's talk I'm very very thrilled to talk about this to get it to postgres we're counting and limiting this is a bit of tricky situation that we really don't have a good way to limit postgres memory right in the sense that let's say we want that to say this query should not be using beyond a certain memory something like that some sophisticated limiting that we could use where we can manage the expectations of the users or wait till some memory is available right where other users are using the instance and things like that so right now that facility is not there there are patches in the mailing list where discussions are going on especially on accounting and limiting but they are not yet concluded so we are still in the stages SLRU was another topic which I touched upon earlier where could we combine SLRU into buffer pool and have just one single way to manage the things which are just backing in memory there are scenarios where we have caches when we execute a query we end up with caches but we don't free up that well on time and that's another area we could do there's a lot of performance improvements possible in allocation speeds every version gets it pg17 has a bunch of Palock allocation speed improvements memory allocation speed improvements are coming up as well and the other thing is like we could do more sophisticated buffer pool replacement algorithms so those are the projects ideas which I want to spill out in the world and have folks look at it and let's collaborate on this so I just want to thank first of all I want to thank all of you for coming to the session it's been a long time since I've given a talk so I've been thank you for the participation I want to thank our group inside Microsoft which consists of both comiters as well as contributors so we have a group which focuses solely on making improvements in Postgres, adding features and improvements only on specific areas but we do things like CSED improvements, we do things like really AAO, DAO and things like that so the whole spectrum of things we do and I get to have fun with my group here and a lot of this has come from that so thanks for, thanks to them special thanks to Thomas Mandraud, David, Roli and Anders Rowan because they helped me gaining the knowledge and kind of like brainstorming on some of these aspects Teresa from Microsoft helped me build the graphics around it so thanks to all of them thank you and that's about it from the session so the double buffering do we encounter the double buffering for the huge pages also? Yes I believe so okay thank you the page cache still has it like when we take it from the thing, I believe so okay so just a quick comment and then a quick question the comment is one thing I wanted to just kind of mention about enable see asserts is that it also when you do that it also enables zeroing out each of your allocation sets, your memory contexts when it clears them so that gets rid, that allows you to turn problems into crashes but my question was you mentioned huge pages have you seen any particular cases, any particular parameters where huge pages make a particularly big difference to performance or is that something that's maybe outside of your area of work? I mean, we do recommend that in general I have not dealt with a specific workload which dealt with it but in the past I've read about workloads which are out there which benefits from this but not directly dealing with it yeah the older databases and the incumbents okay so Krishna Kumar he was getting up on the stage told me that this is the first time in 18 years that you're giving a talk and I told him that this is just like riding a bike and I think that he proved that another round of applause for him thank you


I am Dr. Sriman, concerted in Autabadi, Hongkong region at Manipal Comprehensive & Center Manipal Hospitals, Bangalore. So as per initiative of July being a sarcoma awareness month, I'd like to briefly take you through as to what is a sarcoma, how do we identify an individual as a sarcoma and what are the treatment options for the same. So sarcoma is a rare group of cancers which arise from the bone soft tissue and the other components of the muscular schedule system. We often see this more in the pediatric and recently age group, but there is a widespread group of involvement of all the ages also. How does one identify or one get to know that there is a sinister part of a sarcoma being in a person. So two components have we have identified as one is pain which is without a reason and explanation tends to be there for a short duration but rapidly progresses and one experience is a lot of rest pain or night pain and the second component is swelling. A swelling which is rapidly grown in size over the recent weeks or months duration is also another red flag symptom. So one needs to be aware of these two symptoms and try to meet the doctor at the earliest to identify what is a positive factor behind it. What are the tests involved in identifying sarcoma. So we do blood test imaging which is X-ray, MRI and CT scan in some situations and once we do these tests we do find out an individual may have such problems or lesions on the imaging. And based on this we do a minimally invasive procedure called as an image-dieted biopsy where we use a small needle under aseptic and anesthetic infiltration and try to take the sample and provide it to a pathologist to help us with the diagnosis and once a sarcoma is diagnosed we further do a scan called as a whole body PET CT scan. This scan tells us whether the sarcoma or the cancer is localized to only one part of the body or as it is spread to anywhere else in the body. So once we diagnose and try to stage the person with sarcoma what do we do next. This is where teamwork comes into play. So myself and our team of oncologist, medical, radiation pathologist, radiologist and other ancillary team members get together formulate a personalized or individualized plan approach for the person suffering with sarcoma and we try to provide a single or a combination of treatment options to the person involved. So what are these options again, one is chemotherapy, one is surgery which is often a limb salvage surgery in 90 to 95% of the situations where we save the limb which has been affected with the sarcoma. Rarely we do amputations and we follow this up with radiation therapy also. So these can be given in a single or a combination based on the type and sarcoma and the extent of the disease involved. So a few options which you can also see here are also illustrated. I would like to end this video of sarcoma awareness by highlighting two points. Pain and swelling which are of not unexplainable identity or an origin needs to be investigated. So kindly meet the nearest doctor at the earliest to identify the causative factor behind the same and rule out sarcoma even though it is rare. Early identification and prompt treatment always gives the best results in our experience.