 We're live? Oh, are we live? Oh, hello, welcome everyone, welcome to everyone who's live streaming and welcome to the large audience that I have here today. We're here to talk about the seven. Well, I actually submitted this talk of seven deadly sins. And then as I wrote them, I figured I actually had 10 or 11. So I had to cross out the seven and put 10. So hopefully you don't mind that I added a couple other tips and tricks for y'all, but it is something to see. And one of the things that we noticed when it comes to most databases is people make the same mistakes over and over and over again. And a lot of these are not hard mistakes, right? They're easy things. And some of these are caused because, you know, right now databases are so ubiquitous. They, you know, are commodities for most people. So what they do is they just deploy them and then they forget about them. And that happens quite a bit. And so our support team, we handle, you know, tens of thousands of cases every year across, you know, multiple databases. So we've got tons and tons of data points across this. And we keep on seeing these things happen over and over again. So we're going to start with that. Then we're going to have Kishore come up and he's going to tell you a little bit about some of the Postgres 14 features. And a lot of those features are actually geared towards some of these mistakes. So that's actually kind of a cool thing. And it kind of connects the dots, some of those things that you could look at. So who are we? So I am Matt. Hello. I am the Haas or head of open source strategy here at Percona. My job is to, you know, educate people to get people excited about, you know, what we're doing in the database space and to help people understand why open source is so important to their infrastructure. And Percona, if you don't know us, we've been around for 14 years, and we are exclusively focused on open source databases and helping you get the most out of them. So we have services and software, everything that we provide from a software perspective is 100% free and open. So you can go download it. Don't have to pay us for any enterprise features. And Kishore, you want to introduce yourself? Yes. I'm Kishore Dostrom, new to Percona about six months. I'm a solutions consultant supporting the West Coast. All right. All right. And there we go. And so we're going to break this up. I'm going to take the first half. Kishore is going to take the second. But, you know, let's go ahead and let's dive into number 10. If this thing wants to work. Actually, no, first one, just to give you kind of a breakdown of where we see the most issues. This is actually a graph of, you know, for the last couple of years, I think this is 24 months where tickets are opened across our organization. So you can see that replication lag high availability is pretty high. Config and upgrades are also high. And then the Percona monitoring system, that's not just a PMM like our monitoring system. That's also monitoring other things. So how do I, you know, find out what's low? How do I find out what's happening? And then you see backups, crashes and security kind of there. So this is where it's kind of interesting because when most people think about trying to get help for your database, you think I only need to help when it doesn't work or it's crashed. But in fact, a lot of this is, you know, things around design. Things around, you know, getting the best performance out of the system. This leg is horrible on this thing. So number 10. So I'm going to count down the top 10 here. Tool selection. All right. So I don't know how many people here are running Postgres right now. Cool. Are you guys using PG Bouncer, HA Proxy or PG Pool? Yes, all of them? All of them? Yeah, okay. So here's the fun thing is they all do something very similar and you can overlap these. Understanding the differences between tools like this is incredibly important, right? Because, you know, you can deploy one and not get the benefit. So if you're trying to understand whether you need load balancing versus connection pooling versus, you know, HA, you might have different components you install. And a lot of times what people end up doing is they'll install one of these, they'll assume they get the other and then when it doesn't work later on, then they get all confused. But this extends beyond just, you know, the tool selection with those tools. You know, tool selection has a lot of different parts and you have to understand what the tools do, right? You know, PG Repack is a very popular tool to help with, you know, re-optimizing and preventing, you know, crazy vacuum issues, right? It's widely loved and used. I think, you know, pretty much everybody that I've talked to uses it. But you can have some weirdness if you don't follow some of the basic practices, some of the rules, right? So, you know, you can run into locking issues. You can, you know, cause, you know, issues with DDL that's running. So you have to understand the tool and a lot of times people step on their own feet, right? And, you know, a classic example of this is anybody tried to run vacuum during a busy, busy cycle, right? It really just kind of locks the database if you're doing it full vacuum on a big table, because that's what it's designed to do. So understanding this tool is there for a purpose is very important. And we have a blog on that if you're interested in going into how to use Repack specifically. So we see that a lot. Now, lack security practices. This is something that, you know, you can't turn around without seeing someone have a security issue. You saw on our chart that it was about 9%. We're asking security related questions. Every enterprise is interested in the most secure environment that they can get. And there are a lot of basic things that you would expect from a setup that just don't happen. And a lot of it's because a lot of people install by default and they just leave it as default. So not using SSL to encrypt traffic is one of prime examples. So you can sniff the packets, you can see what's going across the wire, you want to avoid that. You can overly, you know, provision the system, right? Causing issues there. You could store everything in the public schema. You can give, you know, horrible amounts of access without intending to. So all of these things happen way more frequently than you might like to admit. And part of the problem is as you automate, especially on the security side, when you take one small bad practice and you automate it times 10,000 servers, guess what, all 10,000 servers become insecure. And so that's something you need to watch out for. Also, auditing is so important, especially to the enterprise. A lot of people just skip that or they don't want to audit a ton of stuff because there is overhead with it and, you know, a lot of times they don't want big brother watching either. Now, of course, we're going to talk about the proper use of vacuum because has anybody been bitten by vacuum issues before or auto vacuum issues? Yeah, I mean, I think everybody has at some point. You know, so if you don't know what vacuum is, it compacts and optimizes that space in a nutshell. And, you know, it can dramatically speed up performance. But Postgres has two, you know, primary tuning settings for auto vacuum, which is threshold and scale factor. You can use those to determine how often it's going to run, how many changes have occurred or, you know, how much either by percentage or by the number of changes. And some people put both of them on. And, you know, that can cause issues because getting those right is something very difficult. And some people, you know, push off vacuum way too much and they run into all kinds of other issues. So you've got to make sure that you understand those vacuuming issues, understand how to tune them. And you have to look at tools like PG-REPAC, you know, and other opportunities to, you know, fix some of those issues. Number seven, poor connection management. Now, it's interesting. And, you know, the connection and threading model of Postgres is very different than, you know, let's say a MySQL or some of the other databases on the market. It's a bit heavy on, you know, the connection side. So the more connections you add, the more memory it gets consumed. And you can have a lot of times where you see, you know, maybe like, you know, one to two megabytes that were supposed to be allocated, but those sessions and those processes are actually taking up more like 50 megs each. And so if you've got, you know, 100, it might be okay. If you've got a thousand, it might completely destroy your box. And that's why connection pooling is so important for that. But also understanding, you know, hey, there is this impact here. That especially happens if you have a lot of change because there's transaction logs and there's old data that's stored in these connections as well to make sure that you have transactional consistency. And so you have to make sure that, you know, hey, we're going to set these up. We're going to make sure that we have connection pool to limit some of those issues. Now, number six, this one, everyone who's run Postgres for a while probably doesn't fall into this. But just to remind you, Postgres is great out of the box, but it is not tuned out of the box, right? It's for real general generic workloads. And so if, even if you're going to, let's say, an Amazon or an Azure and you're clicking the button to spin up instances, you're going to see that, you know, the default settings aren't always optimized for you. And you need to make sure that you're setting the right settings for your workload, right? So the big ones, I just listed the big ones here, you know, effective cache size, shared buffers, work memory, you know, and then obviously the vacuum settings. Those are things that you should look and think through. How much are you going to use? What sort of workload are you going to have in the box? The other ones that you really need to look out for are the wall settings. So how often is checkpointing happening? And so what size? Because that can just destroy performance if you've got to have a right workload. You also want to look at log and debugging issues or settings. So you might want to increase it, you might want to, you know, decrease it depending on what it is. There will be some impact on performance, but when you're having issues, understanding where those issues are is vital to solving those problems. Now, number five, over and under indexing. Either way, so I've run into this case where I talked to users, I've looked at systems, and you have some users who fall into the bucket where every column is indexed. Don't do that. You also fall into the, you know, the side where there's like no indexes. So every column doesn't need an index. Every column is unique. When you look at your where clause, you know, not every one of them is in the where clause needs an index, but it's probably a good place to start to look at those. And you can look at, you know, PGSTAT all indexes and STATIO all indexes to see what indexes are being used, because you might have a lot of indexes that are out there that aren't being used. And here's the thing, indexes add overhead. So when you do a lot of inserts, a lot of updates, a lot of deletes, those are going to add overhead to all of those operations. So the more indexes you have, the slower things can go. That's not necessarily a great thing, so you want to make sure that you rely on that. There's also a plethora of awesome index options in Postgres that you can use, whether it's a B tree or a hash, you know, a GIN index. Just, you know, so there are specific indexes you can use for specific workloads and trying to match the index type to what you're trying to do is important. Now, extension foo. So number four problem that we see quite a bit is people run extensions, which is great, and Postgres has this wonderful extension ecosystem. There are something like, you know, 30,000 extensions out there. Only like 50 of them are officially in the Contrib library or in the Contrib, you know, directory. And so you can get the ones that are kind of officially blessed, which are fairly, you know, good to use. They're very reliable. But then there are all these other ones that you're going to find at GitHub. You're going to Google for something. You're going to find something cool. And the first thing we always recommend when people have issues is, let's look at what extensions you have, right? If you have some weird issue, you know, hey, what kind of extension? I just had a weird issue a couple of weeks ago when I was running some JSON workload. I was playing with it and it turned out I was crashing because I had an extension installed and it wasn't an extension that had reached GA yet. And so just, you know, hey, and this was something that wouldn't, you wouldn't assume would crash, but it did. And so you have to, you know, make sure that you realize, hey, this is what I'm installing. This is why I'm installing it. Here's the important, you know, things for this. Now, number three, backups and HA. So everyone should be running backups, and you probably are. But the question that I have for most people is, how often are you testing those backups? And a lot of times what we've found is, people test their backups very irregularly, if at all. And so, you know what that means? You are, you know, basically relying on lady luck to guarantee that your systems are going to be recoverable in an issue. That's not a good thing. You want to make sure that you have proper backups that are set up and that they're tested. And you have to think about there are trade-offs that you're going to make within Postgres between, you know, response time, you know, your RPO and your RTO, right? Which take priority, right? Are you going to, you know, prioritize, you know, recovery or prioritize, you know, getting up faster? And, you know, those are things that you need to think about because the tuning settings that you have, you might trade off some lost data or some, you know, you know, recovery time for something else. Now, a few things specifically on that. Check your wall settings because that's going to be where you're going to have a huge impact on recoverability and how long it takes you to recover. You know, if you've got a massive amount of transactions and you set, you know, your wall settings really high, that could potentially really impact your recovery timeframe. And so, be mindful of that. From an HA perspective, how are you doing HA? Some people rely on just standard, you know, replication and they're going to fail over. Other people will use tools like Patroni and HA proxy. So, again, what are you trying to do? What are you trying to achieve? And a lot of people that we talk to don't know even the answers to those simple questions. And that's how you have to start to figure out, like, you know, what is my goal? What is the outcome here? And, you know, very important. When we talk about backups and policies, you know, you want to do things like have a retention limit. Are you storing data to just S3? Okay, so a lot of people now, because they don't want to store local backups, they'll stream to S3, they'll take from S3, copy to a local disk, then move it and delete it. But that means that your recovery time requires you to pull back from S3. And so, if that's slow with a big, giant database, that can take a lot of time. And again, that's an outage if you're recovering from a backup. So, our recommendation is retain backups for seven days minimum, you know, try for at least a full backup every week and have some sort of local copy or immediate kind of get back to, and then go remote from there. All right, so number two, and this one's important, you know, we talked about the configuration not being tuned. But there's some other things that, you know, you have to realize that, you know, the database often isn't tuned for the workload changes that you have. Now, I don't know about you, but most systems that I see, and maybe I'm just unusual and I've seen weird systems, do not have a uniform traffic pattern, right? So you have busy days, you have slow days, you have busy periods, you have slow periods. And, you know, a lot of times the databases themselves are designed to work in whatever someone tested them in. So they ran a workload that was representative of fill-in-the-blank. It was some sort of, you know, generic workload, and it works great there. And so they haven't gone back and physically tuned their system to match whatever those workload is. And so you have to look at that. What kind of tools do you have to understand what the workload's doing? We have our own tooling, you know, Prokona Monitoring Management for Insight, but there are other tools out there. What do we see is those changes, right? So we've, we have a, I built an arcade controller to control Postgres. And so it's actually down in the booth if you want to see it. But you click different buttons on it and it does different things and you'll see the changes in the workload patterns and you'll see bottlenecks that start to show up based on that. And that's the weird thing is people don't realize that sometimes the little cascading things can have a huge impact. So how do you change? How does the workload change? What sort of workload are you doing? And how do you optimize the system is very, very important. But not only do you have to optimize that, you have to tune the actual physical system, the hardware, the kernel, right? So when you're talking about, you know, the database side, there's all kinds of things you can do. We talked about config. We talked about indexes. We, you know, database design, things like that. But you can also tune that kernel to match things, whether that's the swappiness, the disk IO, you know, making sure that your memory is configured right. There's all kinds of things that you might need to do in order to fix that. But it's all about what is the goal of this workload? How are you, what are you trying to achieve out of it? What are you trying to get out of it? And then matching that up. Now, the most prevalent issue by far, okay? By far is bad design. I actually have a talk called the Lost Art of Database Design because right now, more than ever, developers are choosing the technologies, are choosing the databases, and, you know, from an SRE or an infrastructure perspective, a DBA perspective, you get whatever you get and you have to support it. And it's not a great place to be in a lot of cases. And you see that there is this prevalent kind of misunderstanding on how the system works or how you should structure it. A good example of this is bad data type selection. So an integer versus a numeric. It seems relatively small, but it actually can cause huge ramifications in terms of memory and consumption of disk. Because one is a four byte, one is an eight byte and you're thinking bytes, who's packing bytes? Well, now we've got petabyte systems. We've got billions of rows. Four bytes over billions of rows, it can add up. But not only does that add up there, think about this. You have the data that's in there, and then you've got an index, maybe two indexes. And so you have this cascading impact when you talk about some of that data selection. This even really, really impacts performance specifically on primary keys. A lot of applications like to use UUIDs, so 32 character fields for primary keys. If you do that, you are killing the performance in your system, you really are. We've been doing some benchmarks and we see when you switch from a numeric to a varchar, same workload, same everything, you're talking 20, 30X in a lot of cases performance difference. And so little things like that that just, I'm gonna use an ORM, it's gonna generate a UUID and everybody's gonna be happy with it. Woo-hoo, wonderful. It actually causes way more problems than you might think. And so that's a really critical thing to realize. So that schema design matters. And we gotta get out of the mentality of the database is a dumping ground, it's a file system, because it's not. And so that structure is really important. And this goes hand in hand with so many developers now are relying on ORMs because they don't wanna think about the database. And so ORMs are great, they can move fast, they can help developers develop quickly, they can get code out without having to think about it. But ORMs hide that complexity, that logic, but they make bad decisions in a lot of cases. Really bad decisions in a lot of cases. And so you have to understand and weigh those trade-offs. Now, as you design your database, a lot of people will actually go out there and they'll say, you know what, I think this is going to get big, so I'm going to buy the biggest, baddest instance I can. They're gonna go to Amazon, they're gonna go right to like the largest, you know, selection and they're gonna roll that out. I've seen this where companies have, you know, spent like five, six times what they should spend just because they think in the future they could need it. And so, you know, that's a poor decision that they made. It's not a design decision in the database, but that over-provision kills you when it comes to budget because you could use that for way more better things. Now, the other and last thing I wanted to mention here specifically was storing data forever. Okay, a lot of people don't want to get rid of data anymore. Archive processes, you know, ooh, archive, we want that, maybe if it's gonna go to, you know, some other data lake, right, you know, let's throw in some big data buzzwords here. You know, how do we take that data? Because someday that data might be valuable and someday we might need it. So we become digital pack rats and so the more data we store, the more difficult it is for, you know, our systems to react in a timely fashion. And right now, you know, if you can't respond within a second or two, your users are gonna go somewhere else. And so you've got this insatiable demand for faster systems, but at the same time storing 30 times more data. Now, the bonus round here, this is one of the things that, you know, I got late from one of our engineers and so I added it as number zero. That's why I said it could be 10 or 11. But he said, you know, the database design side is true, you know, that people aren't investing there. But we see the opposite as well, which is people come out and they over engineer the crap out of some of this, right? They're adding in three, four layers of complexity components. They can't figure out why they did it, you know, like a year later. They're like, wait a minute, why did we implement HAProxy here and, you know, PG Bouncer and why did this work with, I don't know, right? And so you lose that knowledge because you thought maybe you might have a workload change, maybe something was gonna happen that was gonna drive something and you thought this is cool. Humans are logical creatures, right? And so from an illogical perspective, we, you know, sometimes like to explore cool and interesting things. And that means we're going to go out there, we're going to, you know, find, you know, cool things to try and implement and try. It doesn't mean we should, probably not in production. I remember I built an extension for, it was actually for MySQL, and I had this guy come to a conference session, he goes, I ran your thing in production and I'm like, oh my god, I would never run that in production. He's like, oh, but it was so cool, you know, and it's like, why did you do that? He's like, because I like cool things and it was the cool new thing, right? And so you have that mentality with a lot of engineers. So, you know, those are the common things that we see over and over again with Postgres and, you know, some of those issues. Now with Postgres 14, you know, we've got a few other things that, you know, are being fixed there that are being addressed that are going to help with some of those things. And so Kishore, I wanted you to kind of come over and tell us a little bit about that. You want to stand there and do... Yeah, I'll stand here and if you don't mind if I use yours. That's quite okay, quite okay. So I'm going to cover the Postgres 14 upgrade. So there's about 200 updates to Postgres and unfortunately I can't cover every one of them. We'll be here all night long. And also I broke it down to different areas of what I think would be useful to the audience and which is basically performance, data types, management, backup return replication, security, and other add-ons. Matt mentioned a few things. Hopefully I can include some of that and some of them may not be, but most of it should be here. That will help you guys in terms of the upgrade. So the first thing we're going to cover is the performance, enhancements. So scaling, active and idle connections have significantly improved better. Hold on. Woohoo! Sorry, I decided to do that because, you know, connection... It helps improve for most demanding applications at this point. Parallel query functions. You can leverage your query, parallelism of incurring remote databases of using foreign data wrappers specifically. And then lastly is improve partition systems. Updating or deleting, data rows are no longer impactful on the performance of the partition. Oops, it's not. I'm moving forward. Oh, thank you. This is that stuck, sorry. Okay. Data types and functions. So there's a new added multi-range data type that's been added. We can define multiple ordered and non-overlapping ranges. An associated multi-range type is automatically created with that data type. And then a T-search data file is unlimited line length now. Previously it was of four kilobytes. And then lastly, for the function, added a new data date function. Input stands into groups intervals for uniform length. And by the way, I'm trying to cover as high level as possible, in-depth in a lot of this. This is already documented in the changes in the Postgres on the website, which is at the end included link. Vacuum enhancements, which was covered earlier. The commands have been, there's been many improvements. And one of them is, of course, including the index optimizations has been added to that. And then on the auto-vacuum improvement side, improve the logging per index for the output. And also some auto-analyze for the auto-vacuum has been modified. And then lastly, on the reporting side, there have been a couple of PG stat wall view has been added to track the wall activity. And lastly, the report replication slot statistics. They were added the PG stat replication slot for that specific statistics. And these are really kind of cool enhancements, especially when you consider how impactful vacuuming can be. And everybody makes fun of some of these talks where it's like, yeah, they're going to talk about those issues. Again, they're going to talk about vacuum, aren't they? It's kind of like the cliche, because everybody knows it causes issues. So some of these are interesting because you get more visibility. And it's really important to get that visibility. And with the reporting side from the wall, that was a missing component that getting down to what was happening when you had that checkpoint and occurring when you were running those functions, super critical, especially when you're looking at random stalls that happen and it's just very difficult to track down. Next, with the backup and restore replication, enhanced logical replication API, allowing for long in progress transactions. Also added for the, what do you call it, for your standby server. Added a new read-only server parameter in hot standby. A lot of times it will easily detect connected hot standby server. And lastly for the tables, sped up the truncation of small tables during recovery of clusters with large number of shared buffers. With the security section, provided a couple of PG, provided predefined roles for example, for the PG read all data and the PG write all data. They can be used to specify read-only and also just write jurisdiction, sorry. And then the other part is added by default to manage the password, the SCRAM SHA 256 algorithm. I think the config file is also an MD5 option. Yes. If I'm not mistaken. And then for the new option for PG HBAR COM file, they added the client name for the extinguished name, which is equal to DN. Allows for comparison with certificates and attributes beyond common names, which can be also combined with identity maps. Oops. I think there's nothing. Yeah, you missed my favorite one. Sorry. He skipped over my favorite one and I'm very upset. Sorry about that. Okay. With the last one, with the additional updates. And if you notice, I only include three updates in each section. This one added the pipeline more to the live queue, allows applications to send query without having to read the previously sent query. So let me, let me just kind of share why I like that. You know, when you're talking about, you know, throwing a lot of queries at a system, a lot of times what ends up happening is one query will bottleneck on the next. So let's say you're running in a sequential workload. So you've got, you know, query A and query A takes one second. Query B then runs, it takes one second. Query C runs, it takes one second. It will take three seconds to get your data from Query C, even if they're not dependent on one another. Right? So if you're running those in a sequential system, this enables you to run something where it's like, fire all three at once. Go ahead and use the resources if they're available and then get them back. That's actually a really cool feature. You know, because we do see a lot of code that gets bottlenecked on that sequential thinking. And, you know, it won't solve all of it because sometimes it is querying and then relying on that result set to do another query, but it can help in quite a number of workloads that I've seen. All right. And the next one is the automatic cancellation for long-running queries of an client to disconnect. And the lastly is that they added a piece equal command slash DX to look at the extended statistics objects, which, by the way, I tried running this on 13. It didn't work, so I guess 14 has it. I haven't installed it to find out. But anyway, that's the last slide with the links to where you can find the updates for all the Postgres. So with this, you know, here's, I guess, this is maybe potential problem number negative one. Now that we're talking about these, there's some great features. A lot of this is designed to enhance or fix some of those issues that have plagued users over and over again, right? So when we look at vacuum features, when we look at performance features, we look at, you know, extended statistics from wall, all of these are designed to, you know, focus heavily on those issues that we continually see. And that's intentional, right? So you see that, you know, a lot of, you know, the developers are listening to the community. They're able to, you know, focus on those areas. But, you know, maybe potential problem negative one, people don't run the latest release and oftentimes there are several releases behind. In fact, I talked to some people, they're running, you know, Postgres 9 and quite happy with it, right? Postgres 10, quite happy with it. And, you know, so they have no plans to migrate, so they're still dealing with problems that might have already gotten fixed. So it's just something to be mindful of, just because, you know, we have these new features doesn't mean that they're going to reach, you know, a high usage mark anytime soon. That's it, that's all I have. That's all you have. That was my last slide. And this is Kishore's first Dakota presentation. Yeah, so I kept it brief. Thank you. Thank you. I kept it very brief. Thank you. Any questions that we can potentially answer? Ah, yes. Ah, let's go back to the multi-range data type. I do not know. Kishore, you looked it up. Do you know offhand? No, I don't. Sorry. I have to look it up and get back to you. Yeah, yeah. Yes. So, I mean, so I do know that, like, there is a, there's been a big push in both MySQL and Postgres for multi-key indexes and multi-key, you know, values. And so, you know, enabling you to store objects and, you know, have basically a column that, or a data point that has multiple data points within it. It's kind of a chase for that big data type thing where you're looking at, you know, having those things. So it's very similar to some of the JSON work that they do. And, you know, and, you know, but that's my guess. I haven't dug that deep into that. Okay. Excellent. Thank you for coming. Thank you. I hope I entertained you. Would you like me now to go into the interpretive dance portion of this show? Yes. I'm going to walk up the stairs. He's going to dance. Postgres is awesome. See, we got somebody popping in their head just when I said interpretive dance. They were very excited about that. So, all right. Thank you, everybody.