President / Founder at credativ, America, the U.S. branch of credativ international. We specialize in:- Database Performance and Architecture Consulting- Distributed Operations / SRE / DBA / DBRE teams for 24x7 Platform Management- Integrated Web Application Development and MaintenanceWe bring a devops style approach to solving mission-critical problems at scale. It is our belief that successful companies need technology partners that not only bring technical expertise but an organizational culture that applies honesty, safety, and inclusion towards their business needs.View the profile
About the talk
You know Postgres 12 is available, but you just haven't had time to look into it. Sure you want to learn more, but no one wants to just sit through a bunch of slides. What you really want is to see the features in Postgres 12 so you can figure out what will help make your users (and you!) happier with as little work as possible.
I’m Robert Treat, and I’ve put together a whirlwind tour of new features available in Postgres 12. I think I know what will help you get the most bang for the buck, but if I am wrong, we can chose the features that you want to see most. That's right, there are no slides in this presentation, not even an about-me slide; we're going straight into the database to help you learn what options will help you make the best case with management to get you upgraded.
Everybody Welcome to PG, con 2020 stay-at-home Edition. Welcome to the Robert Treat from doing a talk on postgres, 12 list of all demos Edition that you didn't walk in and around where we need to head off to you. That's cool. So a little bit about this talk. We're going to do today is going to be. The issue is very interactive. And the way that I do is I got a bunch of examples of things are going out there, but why I want to do something so you can see what exactly you
can play with it a little bit. We're going to look at, and you can play. If you want to URL is actually easier to get too. If you just go to eczema. Net postgresql demos. Looks like I'm at school all day, if you need to come find me. That's where I'll be. That's okay. So you don't need to post Criswell requirements. You know if I stressed you out and I've got one here a little bit. I'm going to start with a postgres database in SQL, 12.1 and the version of my database is also 12th at 1. So we're pretty good to go there like special
is wrong, on my laptop is not a production server. Maybe you're thinking master I would do this time I hope that's not what I'm doing. The demo on my laptop you should be doing this for. I'm sure has recently recently released and what not have URL is in there on the script. You wanted to see if there's other articles and DVD rental units like Netflix for snail mail. It's a case you're not familiar with that, but so it has to be like that. And the point of this is
to help a showcase features with in postgres, so that's like the best practices database. It something that we want to be able to put together and manipulate in order to show me feature, so it was exactly the kind of thing, it was to sign for it. And that's why reason in here. Now the way this works is like I said, I've got all this time, I was in here and I'm going to just kind of walk you through. I'm not normally when I do this live like if I do it at a meet up or I'll do it at a conference. So if you remember what conference is where we did this earlier this year and scale was the
last conference I went to out of Los Angeles and then I don't know what that was Blu-Ray. So I went to scale when I do it in this place has there's really more material here that we can actually cover, you know, in, and even like an hour's time. I'm going to shoot for this to be about 40, 45 minutes worth of stuff. And so we're not going to cover everything and that's fine. Like I said, you can get the script and run the stuff or try to figure out a way I can show it to you.
Look at it. I'm so what I did was I went and I wrote down a list of a mix of, like things that I think are interesting things, I think you should be aware of things to clarify a little bit about what's going on with postgresql released. And so those, so I've got kind of that list. And if I can get through all of those, I don't know. That'll put a fair number of them and look at them and see what else is there. But you can always get the script and do it all yourself. Like I said, we got to press, I'm sitting here at the prompt.
Now, I don't actually have a, a Davis, I just want to show you like this and totally like going to make the database. database connect to it and then Coming to see if I can remember. But it doesn't look right. Let's go, and there's a database not doing anything fancy. I'm pretty sure this will work. Hopefully, it will work on a fresh database around Tibet, you can see a couple interesting things that we got at like at the actor table, City Country, kind of standard. South customer is little
partition table in here. Notice payment is a partition table and with all of that, I think we're good to go. Sort of like maybe it's developer-friendly and I still thought they were important. And so you know you're going to Southwest is how we do it straight forward with removed as my first feature. I want to take a look at and we'll jump down here. So first to create a new table with a right and a single column with an integer and I'm good with it. Awesome gloss the next year, which
in this case because we've actually really like a legacy option. I think it was dated at least five years ago. I was so stressed about the prison cells, were still using it and that's probably one. So if we just do a description of the PG class table, you know, that first and if you had a 11 and you didn't No ID calls, I'm going to be there, but what's magic show up in the temple description? And the reason for that is entirely a sueldo ID was treated as like a special columns wouldn't show up.
But if you look up a little bit where you are using, if you did a create table that I was actually creating these magic, is if they had tables at didn't have primary Keys, you can make this kind of magical IDs in there and it would be more or less. I need to have as a unique object depends on your needs, and so. So we're taking care of that. Now, you'll see that, show up if you hadn't seen that before, that's going to show up, it's possible that could break certain scripture. You might have if you do like, maintenance stuff for that. Kind of thing. You're not accounting for that. Column in in
production on your user tables, that will probably the answer will be much like the the system tables converted into visible columns and just kind of treat them like normal. Just want to show that. Don't work. The next thing I want to look at is friendlier friendly or config sizing. And right now I'll see what my system is set up to right now. I'm set to 64 megabytes for being this work Meme and what the sprinkler can fix sizing allows you to do, is she can do things like fractional or decimal base thing. So if I
try to do this and press press jigs or something like that, that might be useful. This is the number that I needed. You know, what I would have to still get to this value, but you got to go do the math and convert it and the math is not, you know, math is hard. I guess this is what we tend to say. Certainly like, I don't want to come up with this. You can see what it actually gets up to internally. It just takes what you are passing in and converting it
over to what it needs to be. And so now we see, we get basically 21 How do you know where we get 21 6627 peachers? Like some of these other ones are easier? We now have a thing for Creative, replace a little bit of a sample here. So you can see the semtex has been change create or replace aggregate most cases. That's probably not too big of a problem but it could start to lead to issues going on at sounds. You have something there. Any cases where you might have a dependency on the aggregate. So it's kind of a pain and it's just a little bit nicer
way. It's right. You can see Aggregates. This is like a normal way and then there's a part of it. Here is the fact that I used to have to just started working notices, one of things to do in psql is it's actually showing us the URL for the command if we wanted to go look this up, right? So we can take these your volume into the browser actually have internet. Or maybe we found a bug, you never know. And here we are. We can see all the same information and then get all the description if we need it. So that's your
L has been added for all the brands of gas grill, is that that URL is hard-coded to the version of t. F u, l your ends of your rank SEO 12:12. If you use your PS3 while 12 to go back to like pretend, it's still going to show you the link for the 12. That's probably fine. Right. Cuz if you go to the 12 and you have concerned you can get you know just click on the other person that would probably not that big of a deal. Alright. So that's greater play Sakura
look like it's a pretty simple. Another one, we're going to do the ad and Teresa enum, when I really like a little more, if you're not familiar with this. So, this is the MPAA rating system, which is the ratings for movies, right? Answer. This one is different in different countries where you're at, but we wanted to add something to drop the old and then create a new one and I can just get worse cuz you're more likely to have, but you might have these issues with that. And so, you know, we want to
get want to make this a little bit easier to deal with if we could. I'm just going to do, we get the altar type back and then we'll just take a look at it again. And you can see we've added that in, I mean, that's a little bit of an issue. I don't recommend nicer and easier to deal with that because there's different ways. You might want to be able to do this but just make sure that it'll work the way that you wanted to know if you were to do that. So that's adding entries. Call Azteca, what are generated Collins has gotten a lot of stress about things.
You hear at all, why don't you to add columns to tables? It's based on generating values from other things with the implementation. There's some stuff that I think will allow us to do more interesting things in the future. Looks like she's pretty good right now. Like it, when you make that new column, it's got to be based on computed values between other stuff within the table, or just, I can't reference that generator. Some pretty interesting stuff with us. So I'm going to grab the first one before I run this. Let me just take a look at the film table, so you can
take a look at it. We've got a title description, right? So, we're just basically describing things about a film. I want to get a tad is new column called Revenue potential. Are you going to grab that and I'll walk you through this impacts a little bit more? Okay, so here were doing got altar table film, that's pretty straightforward, Adcom Revenue, potential new mirror and a generator command cake Sam. It's a really generated. Always as rental duration times the rental rate and review that store and I'll talk about both of those things in a second.
So we have here in the sandwiches, look, we've got a rental rate in a rental duration. So what we're going to claim here for reasons of the rate of the rental times, the amount of time that somebody could rent it and then that should be the revenue potential and that's based off of these two other column stored in. Their means, there were actually store this information as soon as it gets generator. And I'll be able to fill me up for good. I was going to grab a couple of hours. I was able here. First
five and see what that looks like. So we have a couple of movies, got me dinosaur and you can see, somebody is math again, easier than others. But whoever, duration of 6 and 99 Cents, that we would expect that to be about six different versions of this. So you can actually, you don't actually have to use the war in the only show you an example here, if I were to insert a new row, All right, so I'm answering the phone. I need all the required to put in the
language English on. The rate is 12 strikes as they say. In what happens when I try to do this because we can actually answer Is what it is. Bellevue. Grandmother film in here. I'm actually make a dinosaur good film. And set the rental duration. You, and if we're doing this right, the way we think we should, that should change. Let's see what happens with that says. And we go back and select the back out and will be held Revenue potential now is 495. So that's what is. So I can go up in a mile.
That should work. So he's good to change a few things in your demo on the fly. So you know who's real and I don't actually know what the idea is. So let's do We R. where, Lytle Texas things. That should work if I actually put them in a 12 x on PS4 while you can, I'm just wondering about that. You can see all J Collins in here. This is the the PG attribute table. So it's the system table that stores all the attribute information. Basically, column table, in the ones, he might be related to X-Men X-Men, if you had bad days will pass, crushing you heard of these
columns. Which is what we ask for store now and you can also do like generated and it'll generate on-the-fly rather than some Scaffolding in here to make that happen that that isn't in the Prescott 12 release. But I do think it will come eventually given enough to me and that'll be in there. Cuz we got the stuff that I'm also want to show you this. If you ever want to know about columns and what are there. So we have any information, you can get some information about this as well, call and call him usage is a field. Where is a table in
here and Rental decoration and rental rates? Now, have a dependent Did you want to go drop something like rental Ray? Call him at a table Revenue potential table. Film. Drop calling with might have issues. Hopefully, it doesn't rain too much of a view in there anyway so we couldn't do it cuz we are you in there. Alright. So that's generally calls. We got like I said, I think there's a lot of interesting stuff to be done with that. If we can move it down, that path
is actually maybe my favorite teacher impressed, rest. Well, and I don't know about you folks, but yes, I do a lot of kind of thing and And if you're running test, I said, volume, you know, I scale. Please show you how this works real quick, but he rebuilds index index. Currently of the magic And I'm going to do relax, we're both currently has not really any different than what you might expect to see, right? Like basically hey we just read everything looks like normal. But what's really nice about it? And the reason why is one of my favorite features as it is
not as if I haven't had to do this in production, you know, within the last month on some older system, they don't have to be that but if you had a bloated index for some reason and you need an operator's against the table who are trying to insert into that, and that's the same problem that the re-index command itself. If you try to read a table or reindexing, it would pass Blocking it would go on which could lead to trouble. The system would go ahead and switch over to start using that and then you could do. But of course like that, you know, requires a little bit of locking itself. You know
what you really want is you just want to read an excerpt but without blocking rights and so that's what re-index currently is giving us our lives easier. But like that's already a huge fan of, you know, you kind of want some of this. I'm really happy about that. Performances and Shrink their store inside. And so you may want to upgrade or something along those lines where you're not changing the underlying files on a rear end axle, probably actually help. If you've
obviously be careful you want to swap your system with too many things like that but but it's pretty nice generally speaking. Call Sur La Table, vacuum options. These are also helpful. If you do a little out of reindexing, you may actually want to do something like this when I show you first altar table, rental that is pretty straightforward. Rental idea. We had a date, right? And then just a bunch of for Angie's off to some other stuff. So what I'm going to do is I'm going to actually set honest. When I modified the table and table rental, I don't to set these two
new options, which is vacuum index, clean up, clean up off and vacuum truncate off and get them and then I'll explain what they do. We will and we go down towards the bottom here. We've got options. Vacuuming next clean up. What's going on here? What this is going to do for you is let's say you have a table where you normally when you run vacuum on that table, it's Kansas versus Kansas. What do I have to clean up a room in that? Well, if you're doing reindexing
in that kind of a thing, you maybe don't need to actually go back, you know, where you just the way that you're out there patterns work isn't doing a lot of activity and it may be faster to deck you in the table and not worry about this. Or you know that the table is really really bad. You maybe want to skip the end. Set the table level, which is really nice. When you can run a normal people think of it. So, lazy vacuum is just a vacuum Without, Really any option that we sometimes referred to as lazy vacuum versus a vacuum. Which is going to rewrite the table,
right? And takes a heavy lock on the table kind of blocks access to it and then it rewrites on the table and rewrite a normal. But if it's not a vacuum lazy, vacuum normal, vacuum will not run. If there is space at the end of the page, right? Or there's a number of empty amount of space at the end of the table, and that's the tricky part, is usually text message. Writing course, you don't always see this but if there is space will actually work on a regular vacuum. But that's always a useful, right? Because
in this case, that might be, yes, if the latest Niger or something like that, but if you knew the table was just going to grow back again. Right now, I need to get rid of. This is just spaced that happened to get free. It might make sense. If you don't have to do you know you don't have to increase the page space on the file system. Again when the faucet in Winter we get deep into the internals of this. Every time you have to increase the size of the file or maybe whatever
system of locks that have to happen in order to make that happen and so maybe you don't want to have to go through that again. Shopping with that. And so we know you don't really want to see that until you can turn that off. Turn off. Do quick demo. How many vacuum reverse on with rental? And what you see is right from there. I could do something like that rental. I don't know. Let's go with like staff. I need to go to yours Friday, and I have to sign and send me versions all that,
but I want you to see the difference turn that off. I have it turned off at the table of them. Normally when it runs its going to run with the vacuum in This Clean us off and not do the indexes but I can actually pass in case. I feel like I just did like a big operation actually care about cleaning up. The index is on this run. I can pass that in as an option to vacuum on the rental and let's run that. And now you see what? I just got a hold of Val pedir right now, you can see that.
Haven't done the table and you want to do a quick vacuum. Really nice feature, really good thing, it gets it go in there. So all right, so that is options, a couple of quick, a new system use this kind of all in the in the same. You might be familiar with. There's a vacuum BJ staff progress vacuum. Play me things hard, maybe you looked at the save you having to use the store to see if you're doing a really long vacuum on a really large stable, you can work in this table and see where
the state of that is, what phase of vacuum is whatever, added a couple of new ones, 148 progress cluster cluster in your tables. Then, this will show you where you are in that process. And then again, we got the process rocker panel. What is a flocking whatever's going on? Common materialize Expressions. This is a really nice one. All right, so materialize express your thoughts. Are for those who are familiar with, what I'm talking about here. There's a way to use common table expression
and in previous version the way the planet would look at that is When you put something in the start of your Common Table Expressions, it would actually run. And these are so, tables fresh. Again, if you're not familiar with it, at least you're basically with queries, right? Also, sometimes called, and the way that you would do that, Without the explain. Let's just do this family. Awesome actors are named Robert, there's none. Did I break something? Or is that just the truth of the matter?
It's try. Bob Fosse it. So if you want to do with singing it, with a is materialize and then psycho from actor, what person is here behind the scenes, that's what it was and it would materialize. And then in the second half, a call from a wear, whatever, whatever. I ate, like your weather condition. So that's that's cool. And people could actually use this and people like performance fencing. So, sometimes it would help to make something into, if you wanted it, but I should be run first and do the work
I daresay. It was almost like an Optimizer hands, but I don't want anyone to get you. No job said. If I said that, do you want the query to perform? Are some issues around. That one was actually sit. There were certain times where you might want to write the query. That way you need to write it that way, we didn't actually want the performance of stations, you really just like a single-level really need to do that and then yeah. Really by the fault, that shouldn't work
that way. So it was a little bit or some other things going on and so well actually, you helped us back when it first happened, we change the default Behavior with the rest of the stuff you know that would be in the select portions or whatever. Is up here in your with bits is going to get pushed down into here and then I was going to just figure out loud and it scared, you know, because whatever reasons we usually aquarius's cuz we need to make sure this was going to work without actually harming us and make sure that You know, we
could run this and it in again like we won't have her performance penalty. I want to show you, they explain here and it was odd as this is what if you run this on the customer's 11 this is what your explain plan would look like without this materialize, right? You do a CT scan first a and then I'm sorry you build a ZTE first an actor first and then you do a CT scan on a filter of the knee. Alright, so that's how I would have worked in the past. And now if we run this explain and I said this a little bit weird because the default behavior is actually
changed when I was so nice to get the old Behavior, we don't really need to do this. You this is the thing, we know because this simple enough, we can easily sort of reason about this it in that with that I was building or if you just applied the filter as you're doing the original Stand, like that would be faster than scanning everything and then going back and going over it again in a plane, right? So we can see that this is actually faster, but you may need the old Behavior, so if you want the old Behavior, you use the materialize keyword which gives you what would have happened in a
previous post. Now if you were worried about this from her horse, like it's legitimate to be a little bit concerned about this, but we did a pretty fair amount of her customers want. And what we found is Usually I would say 90% of all queries got faster with this change and I would say that they're like with this changing other performance improvements that happened in postgresql. So even though this seems like it would be very scary. Like, we were actually pretty happy with the end result. Most all of our stuff out
faster, or maybe stay the same even though the plans were all changed after, we did have the Escape Clause to use the materialize if we needed to do to go back. So that works pretty well and we're pretty happy with that. So I wouldn't beat you scared about this. Obviously, if you're using this a lot, you know, if you can look like, that's never a bad thing to be a test, I would recommend that if you're worried so I don't look at a few other ones here in time. Let's do explain settings on cuz I think this was really helpful, especially
if you're Plainfield exhaust. That is a pretty new cluster. I just said to 4, which is the default value. Now waiting is actually, I guess the hardest part and then after the waiting comes, keeping track of what you doing, so, did my session, but I'm also putting these in my session trying to figure out, where do I need to go with us to make things work when we have a new option to the explain to me, and which is settings on, and you'll see exactly how this works. Right. So I got a plan like you'd expect like offer actors. Going to give me a sequential scan and actor,
that's really sure. But what's really nice is I get to settings out outlier here. If you do extra information, if you forgotten that you changed to be careful with that stuff and I think I want to show you, at least, at least one more. Cuz this one let me show you what, man, I had more to do, try to get to him fast and like I said, there's so much stuff in hundreds of people that are working on a release at least a hundred, the features, a lot of them. You just don't
have time to deal with, you know, they're very specific. Let me show you this last one with the where Clause this is kind of a nice one if you've ever had to deal with this before, so I'm going to be a little bit of a, right? So we got customer data, and here, we need a list of all your customers that are in the United States or something, right? Because it's 2020 and that's how we actually do data exchange on the worst. I'm so, okay, so how do I quickly generate that
you do the copy command? Who do the copy command down here? Copy select all and write it out to a CSV file, right? Which is going to be demo pg12, customer list. All right, that's simple enough. Man, we got copy done. So okay, we got to see. Now what I want to show you if you know! You to escape out of postgres and run the shell to me. So I'm going to run the shell command head which shows me basically the first, you know, the number of lines off of this violin I prayed it on my file system, right? It's this is
this is getting me to the local for psql. So where I'm at. So I just copied that out. Now I want to just look and what kind of data do I have? And you know, CSP walking Dana, it looks like that. So now I'm going to do is Call the US customers and what I want to do is I'm going to do select star from customer list. Lemon zero, I don't want to deal with. This is a lot faster is a way to do this. Once a day to over, it just reads it with the same structure that table with everything like that. We can take a look at you. If you want to see, it looks very much like the
other one, but it's very plain Jane. Like there's nothing there. Okay. So now comes the magic. Only you can see this right? A little place, like it doesn't matter where you put it on the spring. Like that's all happens. Without fail, this copy us. Customers on the copy list from The customer list CSV file that I created earlier, right? That has all of the customer data in there. A CSV to tell. If it's a whiskey, is where the country equals United States are in there. Can we see you guys here? This one right here,
Jennifer Davis. In the United States. Let's go Henry. See what happened? Where can I get a copy of customers? We copy. 36 lines, and then We can go ahead and see what that looks like a call from us customers and write. Like so now you created that table that's got just your us ones in there so I can see what's the time you stopped this one? I hope you like what you seen so far as yet, I can get the script if you want to, if you have questions, and also impressed, if you need help to ask me
to feel free to shoot me an affair, and I know Dan is going to collect the questions and thanks for doing all this thing. I hope you all have a good time with your PG, con 2020 and hope to see you at some point in the future. I'm sure we'll all get back together. That's the way it's going to be. Alright, thanks everybody.
Buy this talk
Buy this video
With ConferenceCast.tv, you get access to our library of the world's best conference talks.