PostgreSQL 13 Has Some Performance Boosts! Let us discuss it!

Key Takeaways

what's going on guys my name is hussein and i usually do this uh uh bit of news every time postgres could release but i missed it some how oscars 13 was released september 24 and i missed it oscar is not making fuss about their releases they just like to eat yeah we just released postcards 13. no big deal yeah and they just move on they need to make bigger fuss about this i didn't know about this i was forgot i was like i was using positive 13 without knowing because i was i was making my database um introduction to database engineering course and i was using docker and docker by default post the latest and i was using possibility without even knowing so yeah that was pretty cool so let's go through this i usually go through the um the press release uh summary and then go through the matrix and then just scroll down and and i try to explain whatever i understand obviously i don't understand everything right so that's why i leave it to you if anyone um expert in database engineering or specifically impulse chris and you think some of these features might really you might benefit from that will be awesome how about we jump into it guys so postgre 13 was released on september 24th and uh there is a there's a little bit some themes it looks like they focused on performance gains in this release uh minimum security uh features and uh a little bit extensions and optimization when it comes to vacuuming which is i i throw this and under performance if you ask me so so in general uh they they made uh improvement when it comes to vacuuming specifically like parallel vacuuming uh for indexes and uh what i liked about this is you can use they are using now multiple threads to vacuum bad indexes and this is enabled by default you have you don't have to do anything right so so so postgres guys if you if you update anything if you don't know if you update or delete anything the index still points to this dead draw right so there is a you're going to start seeing this did topples all of the time and the cost of reading the index was that that couple can be if if they are a lot can be a little bit deadly right because you're reading this and then you find out you're gonna find out it's dead it's postgres knows it's dead but the cost of reading it while finding out that is dead that's expensive so vacuum usually clean this up if you do vacuum full i believe that's what you call it which i believe uh does an exclusive lock in this right it it will do all of this stuff for you if you do vac normal vacuum that also cleans up the the indexes in a parallel manner so that's that's pretty neat i think incremental sorting uh all this this is an apparently this i i didn't know what it says incremental sorting it's actually a new sorting algorithm apparently that uh that possibly has implemented so i had to wikipedia so there's like partial sorting and there is this incremental sorting which which ever apparently this does things much much faster another performance gain uh the data duplication and deduplication of b3 indexes so you know guys like let's say you have uh this is what i created on my course right i had um i had a table with grades right so and and uh the grades were an integer and their name field and then grade and you know the grade is like from zero to 100 and grades are are doomed to be duplicated right so in this case if you create an index on the grades you will have uh multiple entries in the index that points to the same exact value but they are different rows right so today in postgres 12 what they did is is every index is it's an in it's a every uh entry in the index points to that row right so to that table in the row so now if you have like i don't know let's say the grade 70 and you have like 100 000 for of those so you're gonna have 100 000 of those entries right and they are organized in that way but what they fixed in posgus 13 or they enhanced is they made a single entry with an array of row ids or i think they call it t ids right which points to the table which is pretty efficient so they deduplicated the state so instead of duplicating these values the 30 the 30 30 30 30 all the time they just basically have one or 70 17 hour case they have one value and then on that right they made all of this stuff and so pretty cool they made a pretty good enhancement to partitioning which is pretty neat um partitioning if you don't know guys is the idea of splitting the table either by range by list or by hash into multiple tables so it is still to you as a user uh it's a one table but behind the scenes there are multiple tables and you can access those tables if you would like to and the trick here is they fixed some performance past where they were accidentally scanning multiple partition unnecessarily so they they pruned they called this pruning they pruned this partition to focus on only things they need absolutely your scan which is very very very important so partition pruning pruning has to be enabled by default some administrative sub parallel vacuuming we talked about that a little bit uh right ahead logs uh improvements and yeah some stuff yeah so here's here's the matrix guys so if you see this is the matrix you can see this the gradual values 9 5 9 6 10 11 12 13. you can see anything that is has a yes in it here that means it just just introduced this release right while it says no that means it wasn't introduced in possibilities 12. so you can see that explain wall support so now you can actually explain with the option to do a right ahead log so how about we actually demo this stuff right so let's clean this up and write ahead log has to do only shows up when you actually write right so if i do explain and a lot um let's do this analyze true uh right ahead log true and i do insert into i don't know i have a grade stable here i think let's say name or i don't know what's what's now i think it's just like it's just a good g values i know 10. if i do that you can see that the wall right ahead loads record two i forgot what's that this stands for images something like that i forgot what stands for and the number of bytes required for that that this if i did it again this goes down it's just uh uh i don't know how exactly postgres does this but this is now more information for you to to look at which is which is pretty cool right ahead log uh inserts data can trigger auto vacuum which is also pretty neat and the idea of if you here's the thing guys so i might say hey i'm inserting new data why i need to vacuum a table right because why would you need to vacuum and postgres and we're going to begin with when you update a row or delete a row that row is orphaned and a new row is inserted right and that when you vacuum those bad entries those the tuples are just more physically from uh from the table well it depends uh it depends if you do full which is physically removing it from the page or vacuum which is just marking them as bad so when you insert these are just new stuff right so nothing to clean up but we pulse chris is a multi-version concurrency control system so that if you insert the row and there are uh isolation we talked about isolation many times in this channel right check out the video right here acid so if i insert a new row and there is like an existing transaction that is running that transaction should not see my role because i inserted it in in another transaction right unless unless it's committed right as long as i didn't commit if i insert it this transaction is still running so it needs an an isolated lens it needs to see its own stuff if if if if that that old transaction so my raw that that's a dirty read right bad idea so so so postgres sets these values that says okay this row existed from this transaction id uh and did it it's still alive right and the old transaction id obviously when you start to read information if you want to read that role oscars does some magic says okay i am a transaction i'm about to read am i allowed to see the raw or not it checks if my transaction id is older than this transaction that the throw was created him i should not see it right else uh else okay i should see it so this check if check that is extremely expensive because we have to go on and that select that select statement can actually change the state of the page to to to being saying that hey by the way this row is actually now visible and there are no transactions that are alive currently beyond this the existence of this row so this row should be visible so we don't need to do this stupid check anymore right so that the act of selecting the row triggers funny enough triggers are right to the page which then if you do it so many times you're inserting so many rows then you're doing these selects the first select will trigger a lot of write and that will slow things down because if you write you need to if you write anything to the disk right uh if you write to a memory you have to write it also to disk which which creates io which is weird right i'm selecting why is this writing so to avoid this people told us all the time after insert always run full vacuum or at least vacuum so that we because no no other transaction i'm doing bulk loading right no no one is connecting to the system and then doing reading as as i'm doing with this stuff right so there is no isolation there is no any of that stuff so just let her just let everybody know that all these pages are visible so hopefully i didn't screw this up and i it's this right what do what do postcards say the new behavior reduces the work necessarily when you when the table needs to be frozen and allow pages to be set at exactly it allows pages to be set as all visible if you read this you will be very confused right the page is all visible means hey there are no all transactions that need to be isolated from this it's it's visible to everybody make it visible so vacuum does that so i'm saying what are you doing now what's this feature inserted data can trigger auto vacuum is a new feature that after insert an auto vacuum will be triggered which is very useful because for people who don't know to vacuum their table after insert pulse 13 is pretty neat right and you can configure how many rows you want and all that stuff pretty neat right okay for example trigger an auto vacuum after a thousand rows trigger auto vacuum after i don't know this much right which is pretty neat parallel vacuums for indexing for indexes so you can vacuum indexes in parallel which is pretty neat right so it's faster right because vacuum prior to this was i guess it's a single thread in and it's hard to vacuum and pedal man just the work required to do this man and i think you can also configure how many threads you want to uh postgres to to do this stuff when it does this thing so it's pretty neat-ish stuff pretty neat-ish cool very cool stuff so yeah parallel index and so i i bet this this is kind of goes a lot of bugs for some reason i have a feeling let's scroll down what do we have see called jason so this is postgres attempt to be to pretend to be a nosql database which i wish i would which i don't really i don't know why why people why postcards just stick to being a relational data it's trying to like it's like mongodb trying to be as a database just stick to what you do best right just postgres i i disagree with this are you wasting why do would i pick oscars to start json values like and then you're gonna start supporting uh search on json i think they did already so this is now date time they just added the function date time to json for some reason it's on sql or am i wrong here let's see what is this the sql json path yeah that's the path thing that they invented i mean let me know guys what do you think about postgres implementing these json features do you find it useful or do you think it's uh it's doing is doing posca is just doing too much and they should focus on performance and other stuff let me know i would like to see your opinion v3d duplication we just talked about this right so now you can actually in a b3 this is like this is a very very good read um i'll leave all these links below for you guys so deduplication is the idea of like as we said we have we have a table that has duplicate values by default but those rows are unique obviously right because every row is unique like student ali and student roger have both got an 80 grade right so if you get an index on grade you have two values 80 but how do you present them in the index that's the trick so they they saved uh they by doing deduplication they minimize the index size and guys if you minimize the index size that's the best thing you can do why because now uh if your index for example went from one gig to 500k a meg or or from if you're if it's too big it went from like i don't know five gig or 20 gig to 3 gig you can fit it in memory and when you fit in memory you can do memory scans if and if you have only index only scans and your index fits perfectly in memory you're just cpu bound i mean it's not a really big deal if you have a good powerful cpu then but then you're unstoppable man if you put your index entire index and the memory that's the best thing you can do if your data and the index does not fit the memory then the person system will start doing all this stuff like a paging to disk and because you're treating the the disk as memory and then you can see spikes and i o and then stuff like that and if you have an ssd god knows what will happen over there yes this is going to be faster but it's got i think i think it's going to reduce its life with time if you're writing and reading writing reading writing reading writing reading to disk ssd can only have a little bit limited number fetch first with ties this is actually a new sequel standard uh it used to be called limit guys when you remember it's like oh select star from this table limit seven give me only the first seven rows and if you want do you get your f's offsets zero limit seven right give me the first row is farther for give me seven rows starting from uh row zero and if you see like for example for example select star from grades where uh what or or limit 100 no offset hundred limit seven that means start from row 100 and give me seven rows so this is very very powerful feature for paging if you want to page stuff right like give me this page and then this page like if you like you have an app that selects okay give me this page from zero to 100. there you go next next thing next page obviously when a web application that's very tricky to do because how would you know if your application is stateful this is the best right but if your application is stateless with http then the next call http call to fish the next page like i say i'm going to fetch the first page right you do this query right limit 0 whatever 10 rows and then the next call so the database took the hit and queried the table but stopped in this in this limit after fetching 10 rows however if you want to fetch the next one you got to do it all over again e right because that next http get call will hit a completely different process it's not going to hit the same process it's not going to have the same server especially if you have a reverse proxy or load balancer it's going to have a completely different server and if you hit a completely different server you're going to have to execute the same exact query again and if you don't pool you have to execute the same you have to establish the tcp connection over the postgres database to begin with and then follow up with the query and then do this query again to go to uh offset 10 or if set 11 page limit 10 right so that's why people now are putting layer 4 proxying like invoice and and traffic and aj proxy on top of their um postgres and mysql databases so they can be a little bit stateful when it comes to paging so only in certain situations like hey if i can benefit from a stateful app where i can point my data my queries onto the same process that has this cache data already let me do it right so we didn't talk about this feature now pitch first with ties so this switch first is a replacement apparently for this offset limit right and they just implemented there with ties option and with ties is basically if you do an order by and let's say you're you're you're uh you're giving me give me the first uh first 10 rows right and the last row the grade is let's say 90 right but the next row after it is also 90. so this is called a tie right because that last store okay if you want actually both of them like i said i don't know you want to announce a winner and with die right so that particular last row is give me all the uh that this the first 10 rows with ties that means the last row if it if if they're like another row that exactly equals it the same value returns it to me even if it's one or two or three or four give all the ties back to me so that's what it means i have never had to use something like that but when i realized like okay i guess i understand no new ddl work for postcards 13 actually no new ddl pulse no new ddl work since posgus 11. i guess um it's feature complete though the last thing they did was uh create access method so i mean yeah i guess reindex concurrently was done actually in in postgres 12. i spoke about that in my old video in the original video yeah that was a pretty neat three index concurrently as a it's pretty it's brilliant it creates i think um you can re-index concurrently without actually locking the table which is wow this is pretty neat uh create statistics uh some statistics uh stuff uh hash aggregation can use disk uh the planner can now and instead of doing planning uh if you're doing aggregate like a group buy or count or stuff like that max which uh hash aggregate can create a table temporary table it used to be only on memory uh puzzles 13 now allows you to use disk if if the if the aggregates is so big that doesn't fit in memory right i don't know was it if it didn't fit me were we getting an error before let me know if you guys use any of this stuff incremental sort again we talked about this a little bit but yeah guys this is it security some security stuff apparently it's uh posgus now is doing um authentication uh for certificate which is pretty neat some extension stuff and uh yeah all right guys what do you think of post 13 do you think it's a big release i don't actually think it is but i think these features are really hard to work with really hard to build in my opinion uh if you ask me but uh i'll leave this opinion to you what do you guys think let me know in the comment section below i'm gonna see you in the next one you guys stay awesome and make sure to subscribe for more uh back indie stuff and then discussions we have a lot of fun discussing this stuff um check out the other content of the channel i'm gonna see you in the next one you guys stay awesome goodbye