 Howdy folks Hopefully everybody's doing okay. Welcome to Postgres 12 all demos edition here at open source summits Like I said, I hope everyone's doing okay. We're settling in. I'll give folks a couple of minutes to join in Whatever move their cats out of the way whatever life troubles that you have when conferencing at home Hopefully Everyone's having a good day. I think this is like pretty close to the last session on the last day I'm not sure like I know at a normal open source someone I feel like usually at that time my brain is completely melted It's a different experience here doing this at home. So Hopefully it'll go well for everybody and won't have too many issues There's a little bit way of intro So this talk is gonna be mostly about new features in Postgres 12 It's generally designed. It can be very interactive. So if you want to, you know, send Sort of questions or comments or whatever is I'm going through it. That's probably fine It's a little bit of a new setup. So hopefully I'll see it all and we can make that work But the idea behind the talk essentially is We're gonna show you features of 12 things that are new and 12 and I think in many talks that you see that kind of a thing You know, it's just kind of a series of slides and whatnot Showing different features and how they work. I wanted to I Wanted to see if we could do something. It was a little more interactive So I'll show you I've got sort of demos that are available and you could actually do this yourself if you wanted to By getting you need a Postgres 12 In order to do it and then there's a sample database that we have and I'll show you some links to that in a second and then a demo script so The demo script freely available, you know, open source and whatever So if you later want to go through and do these yourself at home or whatnot You could do them at home and that'd be fine and you can play around with it That's how I find is kind of the best way to do this kind of stuff is to play around with it So So we'll go ahead and the way this is gonna work also, I guess just say you're aware Gonna hit the magic screen share button and then we're gonna go into a series of terminal windows It is it is actually that so there's gonna be I wouldn't say there's a lot of code necessarily depending on your background But it is like stuff in terminals. We're gonna try it that way for this particular edition And see how well this works. Hopefully this works well and everyone can see everything and all that kind of stuff, so I Guess one thing if you want to shoot which angle you're coming from this is a mix of stuff for developers and DBAs and people just working with Postgres So it covers a little bit of the map and now honestly, we probably won't get to everything that's in that Demo script like it's pretty long. So Again, if we don't get to something you want to see it's available publicly you can go back through it and see how it works So why don't I hit the button and we'll get into the terminals and then I will keep talking and we'll start on our path here I think that's the one all right So what you should see Move the cursor around here. So you should have a terminal windows What's new in Postgres 12 the all demos edition again? I'm Robert treats You can find me on Twitter if you have questions later need help at Rob treat to is a good way to To track me down Like I said requirements wise We're using Postgres 12. So any Postgres 12 will do I'm using I guess it's probably 12.3. I keep a demo version that I compile on my Many of my computers, but in this particular case it's just on my desktop here so but any 12 will do it should work whether you're on You know anything we're gonna do here is one or two maybe that they might have an issue But if you're using RDS, that's fine, you know, or some other cloud thing. That's fine I think any Postgres 12 doesn't really matter what platform you're on for for 99% of the stuff that should work fine. I mentioned there's a sample database. It's a database called the Pajila sample database It is on my github which github.com exhila Pajila and what this is is basically it's it's a Sort of features oriented sample database used to showcase features of Pesgres and different things that are out there And and work like that. So you can go ahead and download that and then You know, you see what's in there and there's a script in there It's basically modeled after an online DVD store That type of thing actually and I've learned today that that's still a thing I used to say like, you know, if you're of a certain age You wouldn't remember maybe what DVDs were But actually as it turns out if you go to dvd.com and Look at that it'll redirect you to Netflix's old DVD by mail business is the thing I just learned So if you can imagine this is that kind of a thing There's films and movies and actors and I'll show you some of the tables as we go through it And then like I said the the demo script is publicly available if you go to exhila net Slash Postgres 12 demos HTML that will redirect you over to a gist and it's actually all in one one big gist So you can see what we're gonna do there and this is actually the script here that I'm in right now So there's a table of contents and then all of the different features are laid out And you can look at those and and see what's in there. So And these other windows So this one here is we're gonna use this gonna be Postgres 12 And as you can see psql is 12.3 So like I said, that's the latest version mine is pretty compiled It doesn't really if you had 12 or 12.1 that would be fine as well any 12 should work for this up And some of doing a little bit differently here for this one just because I figure it's always nice to up the ante when you're doing these live demos I've got a window down here, which is actually Postgres 11 And some of these Demos actually it's nice to be able to look at a Postgres 11 and see how that behave versus what you're gonna see in Postgres 12 So we'll do a few here. I've got like the first like five or six We're gonna do is gonna show you some stuff in Postgres 11 versus 12 and you can see the difference and then once we're done with that I'll probably just close that out and we can focus on some more Postgres 12 stuff But I think it'll better highlight some of the changes that are in there Now with most releases like what you usually hear about they're sort of big features or things that get you know sort of talked up in the marketing press 12 is a pretty interesting release because a lot of it is is focused on refinement and that usually is the case for a lot of You know for a lot of different You know releases that Postgres does like some of them add like a new big feature And then other ones are sort of more focused on refinement. There's a little bit of a mix of all that so it always makes it a challenge for Being able to you know put together demos and show things because some things don't really lend themselves to being a visible thing Even though it can be very nice to see it So we'll go through and like I said, I'll show you some of these And then and then we'll kind of get to that. So All right, so we're gonna start with just so you can see how this is gonna work Let me jump down to Automatically handling extra spaces in to date. So there's a function in Postgres called to date and And it used to have an issue and this is one where I'll show you this. Oh, I'm sorry before I said this I don't actually have the sample database built. It is really a lot of demo Just in case you're wondering. This is a fresh instance. I have no databases here. Let me just create this real quick You can see it's pretty easy to do Create database Pajula So it's created and I'm gonna jump over there I need What Pajula schema, so this is gonna load in And that was pretty quick. So all the tables are there now and then let me Do Pajula? Data We're gonna load up a bunch of data and now we've got data in there. Okay. We're good. Everybody's happy And you can see the tables are there. So I guess an actor address category that kind of stuff We have a real table or a real database I'll leave that alone for now. Okay So let me show you how this works in Prescrust 12 Take the command run it over here and this looks like what you would think it was So basically the function the purpose of the function is to do formatting On You know the data that's being passed in so we're passing in something that looks like a date Which is october 3rd of 2019 Back on the good timeline And then we have this formatting that we're putting in which is these y y's mms dds and The feature if you want to call it that Is that in this formatting? I've got little spaces here Right between the m's and the y's and it used to be that if you ran that you would get something that was not Correct. Basically it would kind of mungle up your data a little bit If I run this now this looks fine You get back 2019 10.03 and you could then obviously format this In any way that you wanted to to make that work But this is one where like if I show you what happens if you're doing this in postgres 11, you'll be horrified To see the results that you get back So here I actually get back You know 0019 0103 And the problem is that because those spaces are in there in older versions of postgres It didn't understand what you were trying to put in And that's actually a pretty common and easy mistake to make when people are programming these things And trying to make you know, sort of adaptable on the fly formatting of the stuff And the problem is that like that's that is actually a valid date, right? Like it's 2000 years ago, but it's a valid date So if you were putting that into your database, like you're potentially getting mangled up data, so Cause for concern would would be one way to put that So those are kinds of like those little features that postgres is always kind of looking to do And make those work better so that that those kinds of problems go away So that's that's one type of example of that kind of refinement thing That if you were using today a lot and you're having that issue just makes it a little bit easier to work with The next one I want to talk about is oh id is being removed. This is kind of a backwards compatibility thing It we'll go ahead and run this command here Um, just as an example, I'm going to create a table single column table with an integer and I'm going to do it with oh id's Let me run that on my postgres 12 And ah, it gets an error, which is actually what we want because that's the thing So oh id's have been removed in postgres 12 And if you're unfamiliar with oh id's in postgres, they basically were like an internal Kind of special column type that postgres used to use and they use this is uh referring And I if I run this same thing Not that same thing If I run this same thing In postgres 11, you'll see that this will actually work and it should be okay Yeah, so as you can see it created the table is fine with that And generally speaking You don't really want to use oh id's. This is kind of a legacy implementation thing and it's you know Not been it's been recommended to not use these. I don't know probably for at least a decade or so But postgres was using them internally So that was one of the reasons why it hadn't really ever been removed Just because the system catalogs and postgres were using them and they basically use them as You know as a an internal kind of like a secret id column if you will For the internal catalog. So if I if I look at Something like pg class a pg class is an internal table If I just run that real quick, you see that there's a column called oh id in here Uh, and so that is is kind of an identifier an object identifier for tables in there So rel name would be like your table name And oh id is like the secret identifier that postgres is using behind the scenes if I do that same thing in uh In postgres 11, oops, sorry So you'll notice that that column is not there, right? We just do rel name rel namespace and all that so it it doesn't quite match up with what we see up here, but It turns out that actually is there. We can do this same query oh id Rel name from pg class limit five, right? So I'm just going to grab five rows out of the table Now you would expect us to work in 12 because we know the oh id column is there And you can see I've got some sequences and tables and that kind of thing So that so that query works fine with the oh id's but you can do that same thing actually in 11 Let me grab that again And let's do it in 11 and see It does actually work. There is a no id column And the results look just about the same. It's pretty similar at least right? So they're it's it's pretty close Um, and like I said, these are internal id's that are used that within the system tables They reference things so we had to actually keep that within the system to have postgres continue to work The problem is that some people use those for user space tables as well So they would use these hidden columns For that kind of a thing and if you are still doing that it'd be pretty rare To find anyone who is actually still doing that um, I guess probably not impossible, but You know if you are actually I'd be interested in talking to you just to see what the use case is for that But now we've it's been changed in 12. So there's no longer a special column So you might see those around but if you try to create your own tables with those With oh id's as and that's that special syntax that will air out and that won't work anymore. So That's been a change in 12 if you're building tooling around databases like you might run into some issues there But that's like I said, that's one of the things it's a backwards incompatibility issue That you might run into Another one I wanted to show I'm just gonna go look for foreign key naming has also been changed a little bit in In postgres 12. So this is foreign key names. They're now named with all columns I'm gonna make a table and I just want to show you Let me go over here real quick and I'll show you the film actor tables It's pretty straightforward It's just a simple table. It has an actor id and a film id But it has a dual column primary key Right, which is combined of the actor and film id Uh, and so normally if you were in older versions, I guess if you were to make a reference to that primary key And you don't explicitly name it postgres tries to name it for you And that's true of most things when it comes to like foreign key names and index names that kind of thing So we've changed the naming a little bit here. So you can see the difference And I will I actually run this in both places On its surface, this will probably look about the same Right. So we got a create table that's run there No Sorry go back up here and grab that again and Gotta create table. So we've created table on both of these and so this looks like it's done the same thing But if I actually show you the table description now So we've created a foreign key constraint Uh, that is called fk naming af F key right so an a column and an f column which is based on We created an a column and an f column and we foreign keyed that to the Film actor table right for actor id and film id And I made those sort of hard to read just so they'll fit on the screen But so hopefully that makes sense. We can look at the same thing over here Um, right. So we'll do the backslash d to show you the foreign key naming And now you can see here So the old way it just really would usually grab the first column And then build the name of that foreign key constraint out of the first column And in most cases this probably doesn't matter Uh, but the ones where I've seen that this is sort of cropping up for people Is that there there are some cases where people are doing sort of automated test runs where they're, you know Using ci or something like that Uh, and then they're building it out and they're going back to verify that the foreign key was created correctly And these names change so then it breaks their ci runs And if you don't realize, you know, it's kind of almost an obscure thing to then go look for Because actually the command itself worked right the foreign keys were built correctly But it's the name had changed So if you're testing the output of it to see like did it get correct like oh, it's not going to match anymore So and simple right you just update the output and then your ci will work again But you know, if you don't know then you don't know and and it can take you a while to kind of track something like that down So, um, that's one I like to highlight and that's you know, it's always important Anytime new software comes out you got to read the release notes. I know Everyone probably only says that but It's true. It is important Let's see another one here. We've increased the default set of Full text search languages Let me just show you that so for those that don't know postgres has a pretty extensive Full text search language capabilities You can do custom dictionaries in different languages and there's all kinds of advanced indexing that's in postgres That allows you to make that work And you know for most people like we say and and we see a lot of startups do this That are using postgres at the beginning like start with postgres like it has most of these kinds of capabilities You can build quite a bit on top of that Uh, you know, and then maybe at some point you may need to use an external thing But in the beginning like it's usually easiest to just kind of make it all work Uh and just keep using postgres and have that work. So um And in 12 like basically what they did is they said like we're going to put more languages Just in the system by default So here you can see it just a bunch of them that are in there. Uh, I always like to point out the irish has been added I'm not 100 familiar with the irish language. It is apparently different than gay like I guess But if you speak irish, then there you go Simple is actually what we normally use That's based on the american language because you know, we're simple, I guess So and if I show you this in in postgres 11, uh, there just weren't that many that were in there So I think we had 16 here. We've got 22 now in postgres 12 Uh, and there are actually more languages out there You can go, you know on the web like people have created dictionaries and that type of thing Uh that you can download and install in postgres. It's pretty pluggable in that way But this makes it a little bit easier if if it happened that one of the ones that you needed were one of these ones Um, you know, congratulations. Like you've now got it built in so it's a little bit easier to use Uh going forward. So, uh, let me jump into one that's a little bit more complicated I have an example and I think this will be the last of the 11s that we want to see Um, let's see if that gives me a common materialized expressions Uh, so depending on how much you use sql, you may be familiar with uh with queries. Um, they're also, uh Often called cte's which are common table expressions And the way the behavior of these queries works has been changed between postgres 11 and postgres 12 And this is another one that it's a little bit Different than how postgres usually handles these kinds of changes because the default behavior has actually changed And then they give you a way to go backwards and usually they try to do it the other way around where The default behavior, you know, it's backwards compatible to the old thing And then if you want to take advantage of something, you know, you use the new flag or keyword or whatever might be Um, but let me show you this one really quick and this is a pretty simple query You wouldn't really need to do this If you were going to do it, but I want to just for you know, again for sort of Explaining it. Um, so what I'm going to do is uh, and I'll do this first in postgres 12 And I'm going to do an explain So this is going to show you the query plan because that's really what's going on under the hood The way postgres tries to do this like the output is going to look the same um, but The way that postgres approaches getting that output has been changed and that has performance implications You know, and that's why it's kind of important to be aware of what's going on there um, so if I do this first Actually, maybe I'll do it. I'll do it the other way around. What the heck? Let's let's spice it up uh, so let me do Explain uh on width a as Let me grab all this All right, so what i'm doing here just to let you know and the way that this you can think of this conceptually is What postgres is going to do is it's going to do this width a as select all from actor first And that means it's going to go grab everything out of the actor table and it's kind of going to build A result set in memory, uh, and that's a pretty loose way of describing that but but close enough for for these purposes And then what it does is it says okay now that I have that piece of data there I can use that and then I can do a select all from a right, which is what I've labeled my particular width query as Where the first name equals robert? And so that will go grab me everybody who's named robert in there Right, and so if I run this explain plan You can see what it does is it first grabs everything on the actor table and there's like 200 rows Uh, and a sequential scan basically means it just went and grabbed every piece of data in there Uh, and then that builds ctea, which is what I called my my uh cte And then it runs the filter on that data, which is the name Well looking for anyone named robert and then would return a row back Right, and so that makes sense, uh, that that's how that would work And if I but if I took took that same thing I'm going to run this same query From 11 and I'm going to put it in 12 and you'll see how it actually does it a little bit differently Right, so what this thing does is In postgres 12 it says well I could do the query the way that you told me to and and kind of build that result set of the actor table in memory Um, but I actually don't need to do that and it would be faster if I did not do that Right and this query is simple enough one reason I use this query and is I want to be simple enough That we can reason about that that hey when you are grabbing all those rows from the actor table right when you're doing this sequential scan on actor Uh, you could just look at those rows as you're going through them And grab anyone their name robert and that would be faster than saying go get me all of you know every actor that's in there And then I'm going to look back through it a second time and find the one that's robert I always like to use like the You know sorting and dealing with laundry is like my real world example for databases Uh, where if you you know said like well, I've I've done the laundry I need a pair of clean socks and you said to someone like can you go get me a pair of clean socks? Well, if they want to grab you all of the clean laundry and the towels and shirts and all that and brought it to you And then said okay, here's all the things And then they picked out the pair of clean socks and said here's the clean socks You know you look at that and think like that's kind of inefficient Like while you were grabbing all this stuff to bring to me you probably could have just grabbed the socks there and brought Just that so that's basically what postgres is is saying like I I see what you're trying to do And I can make this faster by just automatically applying things and not worrying about You know breaking this into two parts the initial select where you told me to do it in the width query And then where you're actually trying to grab the data Uh now one of the things that's important here and what you see in this demo um, we have uh This keyword which is materialized So the problem that you can run into and a more complicated query You will see this is that the reason why one of the reasons why people will use these types of queries Is that sometimes it actually is faster to go get You know the initial piece of data and then try to do Some more advanced selects on that piece of data that you've got Imagine if instead of like sucked off from actor like that select had three or four joins in it or something like that Uh, and that's the kind of thing That you could look at and say, uh, you know, oh, maybe it's faster to go that way um, so Again postgres 12 it gives you a way to do that and so i'm going to run that version of the query And if you look this gives us the plan that we had back in 11 Right where it'll go grab everything to build them the materialized part first And then it will actually do the filtering. So if you need that old performance behavior You can still do that. There is a way to make that happen And like I said, it's a little bit odd because postgres usually tries to keep things You know the defaults stay the same and then you know, if you if you need a change You have the keyword. This is the opposite of that And so when this first like came into to postgres, so I saw the commit go into the tree I was a little bit concerned. I guess is one way to look at it Um, but I you know, uh, I work at a company. I'll credit you we do consulting and that kind of stuff. So we basically Uh went and stood up a bunch of postgres 12s all over the place And took all the cte queries, you know from a bunch of different customers and ran them to see What happens when you do, uh, you know when when they make this change does this actually hurt performance Because we knew that one of the reasons we were doing this was for performance benefits And what we found, you know, actually was was pretty good. I would say 90 plus percent Of queries in postgres 12 ended up being faster um Most of that was because it just turns out it's generally faster to You know decompose it and put it back together Than it is to separate it But there's also some other performance benefits in postgres 12 Like btree indexing has gotten faster, which is pretty critical to almost, you know 90 percent of queries you'd run So that's a thing that's faster now So what we found is by and large you probably don't actually have to worry about this most people's queries will be Fine and well performed, you know as good or better than they did in postgres 11 or earlier Obviously, of course, there is that one or two Um, that's probably going to be hanging out there. So you got to be careful with that Uh, but uh, you know, like I said, as long as you're careful with that, uh, it should work Okay, and and you'll be fine. So Um, so that's materialized expressions Uh, I think that's probably the the end of the ones Um, and I have both queries in there in case you case you want to look at them If you want to run this later at home So I'm gonna just close out this Goodbye postgres 11. I'm gonna close out the whole window. Hopefully we won't need it again. I don't think we do And that'll give us a little more space to look at some other things Uh in press press 12 So, all right And in fact, I'll just I'll just start at the next one like we can we can kind of do this in any order Um, if you're if you're looking on a line editor or something and you're like, hey I'd really like to see something specific feel free to shoot that into the chat Um, but otherwise, uh, I'm just gonna head down the list and and we'll show you Um, what we can do Since we've been talking about explains, uh, let me show you this one. So we're gonna do random page costs This is like an internal setting of postgres Where we do show random page costs So currently I set it four because this is all the defaults in the config Um, and this setting itself is not really what's important here. I'm gonna just change it to one I would as a general recommendation you probably want this at two or less One is not a horrible idea But that's neither here nor there What I want to show you is that explain has a new option where you can see if you pass in this settings on parameter into the explain It'll actually show you what their settings are And so you can see here it runs explain it gives me that back But then it tells me hey your settings you have random page costs equal to one And that's really nice for you know, when you're doing performance analysis and trying to do query tuning A lot of times you can be changing multiple parameters within a session And just remembering which ones you've changed and which ones you have and and if it's different or not You know, it's nice to be able to have an option to show you that so That's another little thing that that's nicer to see Forced plan cache mode. I will do this one Show plan cache mode. This is a little bit of an odd one. I don't think too many people run into this But it's you know, it's some to be aware of So plan cache mode in in postgres Sort of a I think a not terribly well known thing if you're using prepared statements The number of times that you actually run a prepared statement will change how postgres' behavior is And so i'm gonna Show you here. So we've got it in auto mode, which is is kind of the default I'm gonna make an index on a table And That creative that's fine. That's pretty quick And i'm gonna prepare a query and again the particular query is not super important But just so you know the syntax here. So prepare means basically it's not gonna run the query It's just gonna go and basically plan that query And then I have to give it a name and sort of a result like this is what it's gonna look like Uh, and then this is the query that it's gonna take right and what i'm passing in Uh, you know, if you've heard of like parameterized queries. I'm passing in a parameter for it And that's going to be a numeric That i'm going to pass in is the amount which is going to be string one So let me go ahead and just prepare that so when I go back and want to execute this Um Right, I just have to pass in sort of the name and and what the value is Right, and so it prepared you can see that there And then now let me show you this a few times here. We're gonna do Uh, I'm gonna pass this in so i'm gonna execute. So first you prepare the statement then you're gonna execute it so i'm executing Uh ex for example, and then just passing in the number 499 is my price Uh, so based on grabbing every payment where the amount, you know, it was equal to 499 Is what i'm doing that i'm using prepared statements as a way to do it. Um So and you can see here it uses an index scan with a condition Uh, hey, it's still showing me my random page cost is one Uh, hopefully that didn't break anything. I don't think it will The magic of of the live demo. Uh, let me do another one Uh here i'm passing in 1199 Uh, and what you can see is uh, right? So here we go. We pass in a different amount and it uses the index again And so now what i want to do is i'm gonna set enable bitmap scan off I'm just gonna change how the plan works Uh, or at least I believe it's supposed to We'll see what happens All right, so that is off And let me run these again And see if anything has changed Uh, let's see. We've got off here index scan using Passing that in index condition 499 And we'll do it again with 1199. I know you're like, where's he going with this? We'll see we'll see index scan again Now i'm gonna do i'm gonna change the plan cache mode to force generic plan And what that means is uh Just trying to keep it to a high level, but So when postgres is doing this planning, right? It's using the same plan for these two amounts the 499 and the 1199 But what is tricky is that depending on The amount that you passed in right you might have more or less Rows that would normally come back and so the way you would go back getting this data Uh, you know could be better or worse depending on the plan that's been generated Um, and so you have now ability upfront to sort of force a generic plan Which is to say if I were to Maybe a different example would be if I were to do something like a true and a false table where You know 90 percent of the rows were true and 10 percent were false In that case using an index to get the false rows at 10 percent would probably be a good plan Uh, but that might be a bad plan for getting the 90 percent of true rows Right, and if i'm just doing prepares and executes It's basing the plan that I should be using off of what i'm passing in rather than what the data is itself Uh, so let's see if we force that Let's see if we get something different here Index scan we got all kinds of settings going on This might be actually a little bit off But let's see what it tells us over here Well, it gave us the same thing Uh, let me set that back on and see if it has changed And I gotta explain with 11.99. So now I'm turning bitmap scan back on And did we get a different one? I think we got the same one Ah the demo gods they've come after me and they scan using index yet enable bitmap scan Looks like we got the same one. Although I do want you to notice Let's see here So up here we're seeing the amount of 11.99 And these original ones amount 4.99. So when it's doing its execution It's doing that based off of the 4.99 And the 11.99 and if you look at these ones down here where we've changed it You see the amount is dollar one, which is basically telling you Hey, I've used a generic plan on this not the data that you passed in in order to get The answer that I've gotten Right, and if I switch this back, I believe we should see it the other way demo gods are Giving me some side-eye though. I feel you know One of the fun things about doing this is that uh the order in which you do these demos can actually affect leader demos So it's always fun. Ah, but we did get it back I feel feel a little bit better. Um, so that's one it's worth playing around with if you use prepared queries You know and that kind of thing Or if you think that might be something useful Usually where people do that, you know, it's when you know, you have certain like very specific queries That you're going to be running a lot, uh, and you want to take the planning time out Especially on something that would be, you know, really really fast Uh, where you're trying to to really get into some some deep performance stuff. So, um Let me show you another one sort of a simple one That's actually my favorite features of sort of keeping one eye on the time here And I don't want to get too far without hitting this one And it's it's kind of sad maybe that this is one of my favorite features for 12 But but it is uh, so and you know, it is what it is So, um, one of the things with uh postgres if you run postgres for any amount of time You probably end up doing a reindex at some point And the reason in what reindexes is everyone generally with database you're familiar with indexes Um, you know, uh, you build an index on a particular column That makes it fast to look up the data in there But what happens is usually over time There's a lot of inserts and updates that are going on And so you can end up with what postgres called bloat in the index Which is basically like dead space in the index where rows have been removed And they haven't been able to reuse that space for some reason or another And then the way you fix that usually is you you can reindex that index Which basically just means hey go build the index again But the problem is that when you do that You actually need kind of a heavyweight lock on it to build the index And so it blocks inserts or updates to the table while it's doing the reindex And that is generally not good if people don't like that So the work around that most folks do at the moment is Instead of reindexing an index they just create an entirely new index because create index Has a concurrently option which allows you to do the index build While inserts and updates are happening concurrently But reindex did not have that option so postgres 12 finally gives us that option And so let me show you the syntax here real quick I just do a backslash h on reindex you can see here Command reindex it rebuilds indexes and then the super magic secret sauce here is this concurrently keyword You can pass that in and it will Do it while you're actually You know have inserts or updates going on And I'll show you how this looks like it's not really much to look at Because it's just you know, it's a reindex. So it's just going to say that I'm going to pass in the verbose flag so you can see what's going on It was reindexed it was reindexed. Yeah, there's not much to look at like and that's what I like It's not much to demo But if you're doing postgres dba work like this is you know, it's sad But this will be a reason that you will want you probably can't convince your boss to upgrade to 12 for this reason But as a dba, you would definitely want to upgrade to 12. I think just for for this command It just makes things easier even though it seems like a small thing It's a really nice one to me We go up this time. I want to show you create aggregate, which is another new change in command Let me show you the syntax for it first This is similar to reindex, but just not as common in usage I'll show you the We now have the option to do create or replace an aggregate And the reason this is nice people probably don't remember Because I think this is one that goes back like 10 years Used to be like when you created a function in postgres If you wanted to change the function, you'd have to drop it, you know and recreate the function Which that that could be annoying. Well aggregates kind of have worked that way up until now That if you need to change something about the definition of your aggregate You basically have to drop the aggregate and then You know sort of rebuild it and that can lead to issues, you know if you're Using that in a function or in a view or something like that Or like a table column or index You know try dropping an aggregate where you're doing some kind of indexing thing Or whatever right like there's just some examples where that leads to dependency trees and so it causes issues So now you don't have to do it that way. You can just do a replace and aggregates It's you know, there's a lot of different ways actually to build aggregates in postgres So this is like the help syntax is one of the more complicated ones. It comes up first There's actually other versions of it Like if you're using ccode to build your aggregate or whatnot So you can see there's different ones in there And so now but any of those will work You can just do create or replace and it'll take the existing syntax and just change what is necessary And then go ahead and make it One of the other things I'd like to point out is new in postgres 12 They've added these little url helpers And create aggregate is definitely one where that's helpful And what these are is basically when you do a help command within psql It it gives you the url to the online documentation where you can find out more about that particular command So that's that's kind of a nice again a nice little feature just to make it a little bit easier And I mentioned this with great aggregate because obviously like the command itself Here's the old syntax is kind of complicated. There's one version of syntax There's a third version of the syntax. So depending on what you're doing with aggregates You know, you can have some pretty complicated stuff in there I will note that those links are hard coded to the version of psql So if you're using a psql from the postgres 12 instance and you're trying to connect to You know postgres 11 or 10 or something, you know psgo is generally backwards compatible This is one case where it'll still link you to the version 12 Of that documentation So and there's links, you know, if you've seen the online docs, there's a link to just get to whatever version you need to get to So it's not a big inconvenience, but it's just a minor thing that like Don't forget when you click on that link. You may not actually be seeing the version that you're on So there could be differences there So all right, let's see what else we got here Oh, yeah, friendly or config size. That's kind of nice little one And this one actually could be practical this Demo is not but but it should get it across So this is a basic, you know running on my desktop Postgres instance the maintenance workman is set to 64. It's just the default which is size based on the amount of You know memory and whatnot that I have on this particular machine And postgres, I mean, it's pretty good. It scales up and down, you know hardware wise pretty well and whatnot, but So I have this set at 64 megs If I wanted to do something and I'm not sure why I would want this particular number, but We now have the ability to use things like decimals in there. Um, so I can do like 21.12 megabytes You know, let me just run that And so it'll actually accept this syntax whereas that would have caused an error In the past and where I think it's probably more useful is if you're trying to do You know, like let's say you need like four and a half gigs or something like that In the past it wouldn't have, you know, wouldn't accept 4.5 gigabytes like it would have actually complained And so you'd have to work that out in in megabits And let me just show you it actually is doing that behind the scenes for you So it isn't storing it the way that you've said It's actually storing it Right as kb even though it showed me originally as as 64 megs because there's a nice round number Now that I've put this weird decimal in there. I don't know how much 0.12 uh megabits is I don't know, but it works out to I guess it's probably about 600 ish Um, so it's actually storing it in the way it needs to be and it used to be you'd have to do that conversion yourself Uh, and so, you know, if you can prevent somebody from doing math Uh, I think most people end up being happy with that. So, um, let's see we did foreign key naming Uh, some new system views. These are nice. Oh man, uh, especially that second one Let me just show you if you if you're working on existing postgres now There's this one called pg stats progress vacuum Which is available and what this does is so so postgres Depending on how familiar you are with it. It has this vacuum process that runs in the background You can kind of think of it like garbage compaction That that you see in uh, you know programming languages Where there's inserts and updates that are going on to the database Those run and and do what they do And then, you know, every now and then postgres fires off a worker that goes and cleans up afterwards. So and that's called the vacuum process So, uh, one of the problems that you have with that is that it's really hard to see what the vacuum process is actually doing in older versions And I think pg stat progress vacuum. I think it came in in postgres 10 And what it basically would show you is like, well, here's the current state of what your vacuum is doing Right? Is it scanning the table or is it working on indexes or whatever? Uh, and so that was a really nice feature Uh gives you a lot more insight into what's going on if you're like, is this vacuum even doing anything? You know, like then then you could go look in here and you could collect this data over time And then model out like how long do vacuums take and all that kind of stuff. Um, the one I really liked So they they've added one now for cluster. So if you're clustering a table, uh, which is in in postgres lingo When you cluster a table, it's basically you rewrite the table in the order of a specific index, right that you choose So it's almost the same thing You, you know in the vacuum one you had your process id the database the relation that it was around So like the table it was on the oid type Because those are still oids Like what phase of vacuum it was in if you're clustering it's almost the same thing You still got there's a process id that's running on the system someplace, right? What database are you in? What's the table that you're in? What was the command that was around right? What phase of the cluster is it in that kind of stuff? So that's in there what what is more useful is probably clustering as it takes heavy weight locks to actually do it So people don't do it all that often Create index is definitely one that people do much more often When you're building an index, especially, you know, as you start to get those 100 gigabyte terabyte size tables Creating an index can take a while and so knowing what is going on is a nice thing. So Again, this one very similar What's the process id and you can match that to like PG stat activity and take a you know, see what's going on there What's the table that's going on? What is the index that you're building right? What was the command what phase of index building it is Right? Is when you're building an index like you're going to go grab all the data and you got to sort the data And then you actually rewrite it back out in the index So there's different parts to that index build that happened So they've added some more system views so you can see what's actually going on in there um and I think Trying to keep an eye on the time here. I think we got about five more minutes if i'm not mistaken So let's see, uh, we did create an idea. Let me look through the list and see what is interesting. Um Let's do maybe psql tricks. That's good Well, I copied with wear clause. Let's do copy with wear clause. Well, let's not do copy with wear clause Uh, let's do pluggable storage. Let me do this one just because I think a lot of people heard about this And they're kind of curious about it. Uh, and it's not Oh, well, let me get into it and then we'll do psql tricks and then maybe we'll do like jason path And that probably will take all the time Um, so this one I just want to touch on this real briefly because there's been talk about postgres doing pluggable storage engines Um, that is a thing that is coming but I would say in the current, uh, system You know in postgres 12 is the first version that has us and really what has been added It's not that they've actually added pluggable storage engines yet What they're starting to do is put the machinery in in order to do pluggable storage engines So there was actually a talk, uh, I don't know if it's yesterday or the day before I went to the folks at vm We're building a new storage engine called z store Which is going to plug into postgres and it it's more of like a column or storage type And so the first pieces of that are in postgres 12 If you're into that or that's the thing that you think you want to do You know, maybe it is worth going to look at 12 if you're just consuming this as a user and you're thinking like, oh, let me go You know, let me go use these new postgres plugin methods That's really not going to work at this point. Uh, so it's really at this point It's still kind of at the developer level that you'd see that But just so you see what they're talking about, uh, here's the help command within psql create a new access method And the type and what the handlers are and then there's you know links in the docs Um, if I showed you like a particular table Let me show you the actor table And oh, I think I have this hidden at the moment. So here's the actor table description. Um That's my tree. Yeah, let me set hide table a mom And run that again Let me set that off then See feel like I missed it. It should actually be in here and I don't see it. I feel confused It's demos for you. Uh, so there's ways to see this. Um, even though I can't prove it to you. So I'm probably just lying Uh, but I would swear it'll tell you so you can see, uh, there's a default with dinner, which is heap Which is what it uses now. So if you look for that, uh, you'll see heap. Let's do it backslash da and see what that gives us Um Here's access methods at the moment What's interesting is most of the ones at the moment are actually index access methods, right? Because postgres is built on this really pluggable engine So right now what most people have done is done pluggable index types So most people like when you just make an index by default you get a b tree index But there's all different kinds, you know brin and gin and just like if you're doing full text search Usually just their gin indexes are what you want. Uh, and so we have this one table method, which is called the heap Um, so any table that you look at in a postgres 12 now Uh, would just be a heap uh type unless you've done some magic patching or something Um, so heap is the name of the current one. Um, z store is the one that vmware is working on Uh, I think z heap is one that is being worked on by, uh, edb Um, and so, you know, there's ones that are out there that I think that are coming Uh, and just be aware of that that that's a thing But if you were getting ready to jump into that on postgres 12, like that's not really a thing yet Unless you want to do it from the developer level. So Um, I'll show you a couple other psql tricks. I don't know how many people use psql like it's it's in the postgres world Uh, it's by far, you know, one of the heaviest used tools is still the command line tool There's a lot of different guis out there. I don't think the community is really, you know congealed on a particular one Here's con info If you just want to see What your connection is you're connected to date with pagilla user postgres Using temp socket on that port The funny part is like there was no real way to see this from within psql. So it doesn't seem like a big thing I have sort of this customized, uh, you know Prompt setup so I can see some of that info because it's good to know what user you are like what database you're connected to um Let me show you this other one pset format csv Uh pset kind of controls how the output from ps psql works And so i'm going to do this example select all from actor limit five And what you can see is that what I've come up with is i'm basically getting csv data here, uh Right because I set the format to csv. So it just gave me my five rows back Like here's the column names, uh, and then here's the data Right and by default, uh the format that that it uses is called aligned So i'm going to set this back to the default And then, uh, I'll just run that same query And you can see what it would look like in a non csv format. So, um, that could be really nice Like you could use that with something like backslash o And which outputs to a file and then do a select Of csv and then you'd have the data as csv If you wanted to do something quick and dirty in psql Up that we might as well look at label partition tables. I have a partition table in here backslash d payment Some of the things you're showing is down here the number of partitions Uh, it has six partitions again It's like a little thing to see like how many partitions are on this table and then you can use d plus to list them out if you want to Here's another like a little one. This one actually is probably going to change the way some people manage their systems in postgres When you set up a standby system, right? So you have like a primary and then like secondary or or what have you Used to be that you had to have some method to to like touch a file on the file system in order to Actually promote the standby to become the new primary So you needed file level access to that and well what has happened is in postgres 12. They've added this function pg promote And basically what it does is you know, you can pass in how long to wait But it promotes the standby So you run it on the standby and it promotes it into a primary Now obviously that's not going to handle, you know, if you have to reroute your applications or whatever That that's outside the bounds of this But it means that you can do this from the database level. You don't actually have to have file system level access So um, you know for some people they're like, oh my god finally like I can't you know, I've been waiting for that for years Why did it take so long other people probably horrified of like wait? Anyone who connects to my database and it's like no, there's a little you know, there's some restrictions around it But on the other hand if you're the kind of person who lets all your apps connect as the postgres user Uh, I would obviously never connect as a postgres user I always make some other user and then do it that way. I'm sure you do as well. Um, so What do we got we got I think about Maybe two or three more minutes here. Um, I don't know if anyone had a thing they wanted to see let me look at some of the The options in there I think we gain in terms of geo database or there is actually um Let me show you Oh boy I have to remember the I think we're about done. There is a thing. Um, there's an example in here it is on Let's see country there is that's that's what I need to look for. Um, not that one though Yeah, this one here. Uh, so there's a thing I I think we're about out of time here So, um, I'm not gonna walk you through it, but just so you know, there is an example there if you want to look at So we have this thing create statistics for multiple columns You can do multi variant column statistics now to do things and what this example shows you Is basically looking at like cities and countries and in postgres when you're doing like and we see this a lot with geo oriented work Um, when you're doing that kind of thing postgres sometimes gets very confused about like, oh, these two columns have a relationship But I don't know that it actually has a relationship. Uh, and so this way You can actually tell postgres you create a statistic And here I just called it geo points, but you create a statistic telling it these two have a relationship And then it will grab data about that and then use that when it does query planning So that's one of the nice things for for post gis users that we see. So, um, I think I'm actually out of time Somebody yell at me if I am not But I will be over in the slack channel here momentarily And if you have questions or whatever and want to ask there, uh, we can definitely do do things there. So Other than that, thanks everyone for coming And uh, like I said, if you have any questions, happy to answer them Feel free to download this play around with it. Uh, learn, you know, as much as you can then uh, and keep going Yeah, let me jump back here. All right Uh, any last questions comments? I guess we're good. All right. Thanks everyone