How To Be a Postgres DBA in a Pinch with Elizabeth Christensen

Hi, everybody. Welcome to How to Be a Postgres DBA in a pinch. I'm Elizabeth and this is a talk for DjangoCon US 2022. I'm a customer success manager at Crunchy Data. I talk to people a lot about Postgres. I write a lot of blogs and tutorials and docs. I have a background in open source project management. I'm not an engineer or a DBA, but I've worked with engineers and DBAs and technical teams for 20 years. I volunteer for the Postgres US organization and I lead the Kansas City Postgres User Group And like Django, I am from Lawrence, Kansas. I'm on Twitter at SQL

Liz. If you ever want to chat with me. And believe it or not, I actually was a DBA in a pinch about 15 years ago. I was managing a small support and DevOps team. And a couple people left the company and I was on my own with 150 SQL Server databases. I got a little help and I actually ended up migrating all the databases to new hardware and to a new version. Before I got some DBAs back on my team. So I'm kind of familiar with how to do this DVA job in a pinch. So yeah, however you ended up here today, welcome. I'm glad you're here. I'm gonna try to give you the vocabulary A few things you need to know about running Postgres, a couple of the settings, and some queries to keep you out of trouble with your database.

And before we get too far, let's just take a look at what a DBA does. And you may notice I have some pictures of gnomes in here. They don't mean anything. I just thought that We needed to spice up this presentation a little bit. I don't love really boring text slides, and I also love puns. So that being said, let's dig in. All right. A DVA is primarily going to work on the data availability side, right? Making sure that the data is there, that it's available, that it can be accessed to everyone who needs it. You need to know where your backups are and that if there's a disaster, how to get back up and running. And you need to make sure that things are performant.

So data is available to the people when they need it And you also have to stay on top of updates. All right, quick roadmap about where we're headed here today. We're going to talk about hosting, how you can get Postgres running Rules, security, backups, sizing your database, a few of the really key tuning options for Postgres. What to do and what to look for when you're scaling and growing your database. A little bit of talk about the monitoring. I'll give you some troubleshooting tips, and then I'll be available at the end for questions All right, so Postgres can run almost anywhere.

Most commonly you'll see it on Linux distributions, but you can run it locally and on other operating systems if you'd like. If you're looking to run super simple Postgres just to try it out or run a really basic app, you can run it locally. There's a nice Mac OS tool called Postgres. app that's kind of fun to use. There's a lot of Docker containers out there if you want to use those. Crunchy just recently released a little Postgres playground where you can run Postgres in a web browser just to kind of mess around and write some queries and stuff like that. If you're on a traditional deployment kind of infrastructure, you can totally run

major Postgres at scale using virtual machines, using cloud instances, and then lots and lots of people run it successfully in data centers and on-prem. There's also a lot of ways to run Postgres now in containers. There are quite a few options out now for running Postgres in Kubernetes using the operator pattern. Which kind of orchestrates Postgres and then some of the other side components that you would need to run Postgres in production. Cloud Postgres, like all things cloud, is sort of having a big moment, and there are quite a few ways to get this done. There is

now like a fully managed Postgres option that's available on a lot of different platforms, like Amazon RDS, Google Cloud Postgres. And several other companies have options for fully managed Postgres, Crunchy data included. We have a cloud option called Crunchy Bridge. So fully managed Postgres is going to be just a simple developer experience. Click a few buttons and get Postgres running. Grab a connection string, throw that in your application and you're kind of off and running Um, when you're choosing a host, you'll have kind of two major buckets. You'll have self-managed and fully managed.

So self-managed is going to be that Kind of you're in charge of compiling Postgres itself and any of the extensions and tools that you'll be using. You're configuring your own backups. You'll be self-initiating and doing all of your updates and you might be like migrating to different um you know hardware depending on what you need for those updates. Typically, self-managed Postgres is a lot lower cost, you have a lot more control, and you'll have full SSH access to the underlying machine. Fully managed Postgres kind of outsources a lot of the DBA work. So, you know, you'll have the backups built in, you'll be offloading a bunch of the monitoring to somebody else.

The updates are a lot easier and they can be sort of managed for you. You're typically going to have a little bit higher cost for that. You'll let go of some of the control and you won't have SSH access to the underlying machine. So I kind of like to tell people to choose something that helps you sleep at night. And for a lot of people , fully managed Postgres is the way to go. When you're shopping around for Postgres, you'll also see a lot of talk about high availability. So I just want to touch on that really quickly. So high availability when you're talking about it with Postgres is typically used for business critical applications. The database cannot go down. You might have a downstream SLA with your own customers or users.

When you have high availability Postgres, you have a standby machine that is already running. It already has the data loaded and the failover to that machine is automated. There's a couple open source tools if you want to self-manage this yourself or it's built into a lot of the fully managed options. Okay , let's talk a little bit now about securing Postgres. So from a resource perspective, these are kind of the big topics. First, the firewall and networking. You're gonna want to make sure just like any of your other infrastructure pieces, they're not available to just the outside internet and you've locked it down to

you know, your small user base. For fully managed Postgres, you can put your Postgres in like a virtual private network that the cloud has, and then you can allow different firewalls in. You're also going to want to make sure that any database connection uses SSL , and this is true for applications connecting to the database. and users or if you're using some kind of desktop application to connect to your database and Use that to manage some of your Postgres stuff. Make sure all of your things are encrypted. There is full disk encryption. You can self-manage that if you want, full disk encryption is part of most of the fully managed options

And then I probably don't have to say this since you guys are all open source software people, but staying on top of your versions is absolutely key to security. The Postgres This team releases a new major version every year and four bug releases a year. So staying on top of those is huge. In terms of the actual access to the database itself, if you're a DBA and a pinch, you're going to be in charge of this. And so when you get access to Postgres, you're going to have a Postgres user in the Postgres database And you're gonna want to from there build out all of your roles. Um, you'll wanna make a role for yourself

and anything that you're doing. Um The sort of that rule of thumb is to use the Postgres user very sparingly. Only if you want to install an extension. or do some major maintenance that requires that super user. Most of the work you're going to want to do is going to be in your own role. And then when you're thinking about making roles for other people or applications or you know, BI tools or kind of whatever you've got connecting. Think about, you know, who needs our read and our write access versus who needs just a read, and that can really save you some trouble down the road too. Okay, um so you've got access to Postgres, you've taken a look, you're in the database.

The next thing you need to definitely take a look at is backups. If you're just starting out with a database and you're a DBA and a pinch, checking backups is the first thing you should do once you have access to the database. Because a rainy day is going to come someday and it's going to be your phone that rings when someone needs help. Um, so The first thing to do is to find out where your backups are. If you don't see any backups, you can take a backup of just the data using the PG Dump utility. You can take that from a local machine if your database isn't that big. You can take it from a remote server if you have something larger.

You can use the PG restore tool to get data back online from the PG dump. And I always tell people to practice that so that you know kind of the commands that need to be run. What net needs to look like for your application to get you restored. And then as soon as you can look at a more sophisticated backup solution, Crunchy maintains a tool called PG Backrest, which is kind of like the de facto Postgres backup tool And it will do a lot more than just that dump. It'll let you do like a point-in-time recovery, restore, and a lot more sophisticated backup solutions. So get on PG Backrest as soon as you can

If you're fully managed, your fully managed backup stuff should all be handled. It's good to know and go through the steps and check the docs. So I talked about a couple different things in the last slide, and I just want to go over some high-level backup vocabulary. Postgres has some specific terms and knowing what these are, especially when you're Doing some reading in the docs is kind of helpful. You'll hear a lot about the Postgres wall, which is the write-a-head log And that is sort of a log that's used by Postgres itself to ensure the disk 's integrity. So each change is logged and in the case of some kind of disaster, the wall log is re played and the data is restored.

And then tools like PG Backrest and then some of the replication tools actually use that same wall log to play the replication or the backup. A dump, that's just literally that means a backup. Logical, you'll hear this referred to in backups and in replication terminology, and that's in Postgres just the data itself. A physical backup or physical replication is done at the full machine level, so an entire copy of the disk PG Backrust, we talked a little bit about that. And then point-in-time recovery is something that sophisticated backup solutions like PG Backrust will let you have. And that is because they can use that wall

log. They can replay the database to any point in time. And then you can take that database point in time, and that's called a fork. And so you can create a standalone database at any point in time in the backup, either as a restore or some kind of disaster recovery machine, or a lot of people use them for. development and staging environments. All right, so um Once you've moved on from backups, the next thing you should probably do as a DBA and a pinch is just double check your machine size. right making sure that Postgres has all the room it needs to do the jobs that it's doing

This is some quick notes that I have on machine sizing. Obviously, like these are simplified and your specific use case might be a little bit different. So your underlying storage is going to be the size of the actual data that you have. So if that's static, that doesn't change a lot or it you might be in a situation where you get lots of data in and out. So those are things you kind of need to know If you are planning for storage, you always need to lead a little bit of room on the storage for the wall, since the wall itself. Regardless of where you host Postgres, it will be stored on the machine. And then, you know, always like any disk, leave some room for yourself for growth.

Memory on Postgres, and we'll get into some deeper conversations about memory in a minute. A rough gauge could be like 10% of your storage size. We tell people not to go lower than 8 gigabytes for any production database, even if your data is really small. And Postgres is pretty memory intensive, so the more you have, the better off you're gonna be. I have a quick query here for you to just find out how much storage you're using. And here is a query using the extension PG ProcTab. And this is an extension that will help you pull a few of those like kind of operational metrics.

So if you're doing the DBA in a pinch job, um check out PG Proc tab. It's pretty helpful. And here's a query to find the free memory and then the total memory on your machine if you don't know that. All right. So we've got memorage, we've got storage, we're good, right? Okay. No. So um there's a couple key tuning pieces of Postgres that you need to look at. Beyond those basic settings. Especially if you're changing the amount of memory that you're using, there's some tuning parameters that you need to know. So

one thing that's kind of helpful is just to take a look at sort of the Postgres memory usage. And this is pretty simplified. But roughly half of the memory on your Postgres instance is going to be file system cache and the maintenance working memory. A quarter is going to be the working memory, and another quarter is going to be the shared buffers. So shared buffers is a memory segment that's shared between all the Postgres backends. So there are data blocks that have been read from the file system and they sort of coordinate the changing of data between all the processes. If you read data from a table or index, it will be read into the shared buffers first. Okay.

So working memory is private memory allocated to individual Postgres backends, so individual connections that are querying and storing data. So as a general rule, you'll have the working memory as a quarter of your memory, shared buffers as a quarter, and then the other half will be used for maintenance tasks. Okay, so when you're talking about shared buffers, um, there is a way that you can kind of check that shared buffers is being used correctly. So you can look at the cache hit ratio. So cat hit hit ratio measures how many content requests a cache is able to handle

compared to how many it misses. And so a cache hit is something that's handled by the shared buffer cache, and then a miss is one that's not and has to go um to the other system cache. And then So you can actually use this query to get your cash hit ratio You're typically looking for a cash hit ratio in the high 90s. You're going to want something that is most of your requests are able to be handled in the request. So you can shared buffers and the settings for that is a default in Postgres.

So the default is 128. Megabytes and then it should be about that quarter of the total RAM. So if you're running that standard eight gigabyte production machine, you know, that's two gigabytes for the shared buffers. So you would want to update that shared buffer setting if that's what you were using. So queries and connections to the database use the working memory, and working memory is measured in per connection. So the default is four megabytes per connection. You can get yourself kind of a an estimate based on how many max connections that you have.

We'll talk about connections a little bit later in the slide. So these two pieces kind of fit together. But you'll have your total RAM and then you'll have a quarter of that divided by each of the connections you expect to use memory. Okay. Um maintenance work memory is used on things like vacuuming, um, creating indexes. altering tables and since this is maintenance only this isn't memory that is going to be used all of the time and so you can allocate like half of your RAM to the maintenance work memory It's generally recommended to set this higher than the working memory since that'll really help vacuum out. So take a look at that.

And then statement timeout is another tuning setting that we recommend that everyone take a peek at A typical short-lived query in Postgres is only going to be a couple seconds, and you'll very rarely have things that take several minutes. And so setting a statement timeout is just going to stop you from having things that accidentally run or kind of a runaway process or a runaway query that ends up, you know, running for like eight hours. Um, you can just set a statement timeout. A lot of people set it to like 60 seconds or 120 seconds. Okay, so

um let's talk about a couple of the other things that happen once you start having a lot of things connecting to your database. So Things are growing. You've actually done a good job being a Postgres DPA in a pinch, and your application needs a lot of connections and things are kind of growing, and we need to do a little bit of scaling. So each connection in Postgres takes memory, right? Because we just talked about that working memory. So you're resource-bound in terms of how many connections that you can allow. One thing to keep in mind is that everything that connects to Postgres

requires a connection, even if that's you and you're connecting with PSQL or you're you know, Postgres GUI tool or if that's your reporting server, whatever you have, those all require connections. Postgres has a max connection setting um and we'll talk about that in just one second and then there's um a connection age setting in the Django ORM too and so a lot of people kind of tune that as well So the max connections setting is very application dependent, obviously, based on how you have your servers configured and kind of how many connections those are all allowing. The default number of connections in Postgres

is 100. So for example, if you had four servers and each of those had 50 connections. you want to add a little bit of room for yourself and other things to connect, and then you end up with like 220. Once you go beyond kind of some of the small amounts of connections, you're going to want to think a little bit about connection pooling too. You can do some application side pooling in your web server. And once you kind of start looking at connection pooling, you're gonna want to take a look at the PG bouncer tool. That is sort of the gold standard in Postgres as having

that connection pooling. If you're self-managing, you're going to be kind of working with PG Bouncer on your own. If you're fully managed, some of the offerings have have you manage it as a separate piece. And then like our offering at Crunchy Bridge, PG Bouncer is actually inside the Postgres instance, so it can be managed like kind of alongside the instance. And then the other thing to think about as you kind of have a lot of connections to manage is scaling out with read replicas. We kind of work with people that are sort of early on in their Postgres journey a lot and scaling with read replicas

is such a great way. to kind of get you a lot more going on, improve performance. So basically you're going to just be separating out and allowing some of your ORM to connect reads. to a read replica. This is super easy to set up in the database router configs in the Django ORM. So you can just kind of add a and add a replica machine in there. Okay, um so let's talk a little bit about monitoring and I've showed you a couple things that you can do yourself with queries You're probably going to want to get hooked up with some kind of monitoring system that comes with, you know, a more comprehensive look at your database

and then alerting too. If you're gonna set something up, you're gonna probably want to include, you know, uptime, memory, disk usage, locks. That cash hit ratio we talked about. One thing we haven't talked about now, but is good to point out here is the logs. So Postgres will let you set up log shipping via syslog. And so you can send logs to your monitoring provider and you can set up some parsing so that your logs are kind of easy to look at. And this can be really helpful, especially if you don't have X SSH access to that underlying machine. You'll still have access to the Postgres

logs. And you can also monitor stuff like bloat and slow queries is really popular in terms of monitoring and kind of working on some of those performance improvements. So monitoring tools that are available are kind of in two buckets. There's sort of software as a service monitoring that you can get. And then there's open source monitoring and those are kind of roll your own, host your own monitoring. The three most common Software as a service monitoring that I hear about are Datadog, PG Analyze, and New Relic. There's quite a few other ones out on the market and they're all good. Datadog and New Relic are going to monitor other pieces of your infrastructure as well.

So you can kind of have a bird's eye view if you're If you're a DBA in a pinch and your DevOps in a pinch, you can kind of keep an eye on other stuff. PG analyzes Postgres only and it does a little bit of a deeper dive into like some query analysis and stuff like that Open source tools, PG Monitor is good, that's a crunchy tool, and then there's PG Watch 2, and that's pretty easy to set up. Um, so one thing that we haven't talked about yet, but as you start monitoring and kind of keeping an eye on what's going on in your database, you're gonna come across so This is kind of a good time to bring it up. So that is understanding your query plans. Postgres has a tool called Explain.

And there's a couple different ways you can run it. So you can run it with just a query plan explain, explain analyze that actually runs the query and gives you the plan and the execution time. And then you can also run it with buffers if you want to include some of that. buffer memory information inside of it. So you can run explain by hand if you want. Like if you know the query that's That's being run, and you just kind of want to know how long that's taking. That can also really help you when you're planning things like indexes and you want to know if things have gotten better. Um Postgres also has something called auto-explain, which will log an explain plan for a query that's run.

And you can set the auto explain to like a size. So, you know, let's say that you're totally fine, you know, if the query takes two, three, four seconds to run That's probably a pretty good optimized query. But like if it takes 10 seconds, you want to know more about the query plan because you want to take a look at what's going on and if you need indexing So you can set up auto explain to do that for you. And then if you're you know using a monitoring system that has your logs, you can find those explain plans in those parsed logs that are kind of hosted by your monitoring vendor Um

and PG statements is another really popular Postgres tool to help you track query time. PGstat statements runs as an extension inside the database and just tracks statistics on the queries themselves. So this is on the screen a query that will just show you like the slowest 20 queries that you have. You can, there's a lot of different ways to look at PG stat statements. But this is a great place to go if somebody says something is slow and you want to just kind of see if you can make it faster. PGStat statements is a great place to look. All right.

Um, so let's talk a little bit about troubleshooting. So we've started monitoring things. Things aren't quite right. People say that things are slow. Let's go into some places to start for troubleshooting. So the first place to look, and I already talked a little bit about this, is indexing. So Indexing in the database is kind of the way to really increase performance without doing a ton of work. I have a little sample query of something I benchmarked where I just ran a query on a weather database and I had no indexes.

Um the query ran in 27 milliseconds, and then I indexed the event type column and the query ran in three. So you can see like simple column indexing, single or multi-column indexing can have a really big impact. You'll build the indexes um in the Django RM, kind of in the models. If you're gonna do that with Django. Hockey Benita has a really good talk that he did last year for DjangoCon 2021 on index usage. So, and I think that's on YouTube. which is a great place to go if you want to dig into a bunch of indexing. PGstat activity

is the activity view for what's going on inside Postgres. So this is kind of like if you want to see what is running, you want to see the queries and the cron jobs and all the system processes This is the place to do it. There's some other ways. There's some other ways you can run it, but a select all will give you everything. Um And if you're trying to figure out what's running and something has locked things up and you know something isn't running right and you need to find it, this is where you're gonna find that Um so here's a query at the top to go and find in PGSAT activity um the the ID of the actual activities and what's happening and then the bottom two queries are how to cancel and terminate those queries

Um just a touch a little bit on the topic of blue and vacuum. So as Postgres is updating and deleting data and entire rows of data, the space that was taken up with that data is not actually free to use until the database is vacuumed. So this is just kind of part of like Postgres 's internal storage management. So if Vacuum isn't run or runs out of memory when it is running, the database kind of fills up with a bunch of unused space. And that's called bloat. And there's a query that you can run that's on the Postgres wiki if you want to see like by table how much bloat you have.

Most smaller databases don't have an issue with Bloat. This becomes an issue as your database grows and as your transactions grow. It's particularly notable for really high transaction databases. But your Postgres should be auto vacuuming. You can tune auto vacuum, but generally the settings it comes with are pretty good. If you just want to take a peek and make sure that, yes, okay, when did the last vacuum run? I have given you a query for that. If you're seeing that your vacuum hasn't run, you should definitely figure out what's going on and kind of take a look at that. Um, and then logs. So obviously if you're troubleshooting, you want to go find the logs and see what's going on.

Show log destination is how Postgres will tell you where you're sending logs. This example, you've got a syslog. And you're sending standard errors and then show log directory will show you where those log files are at. All right, so if you get in too far and you need help, the Postgres Slack is excellent. There's quite a few seasoned Postgres engineers on there, the more detailed questions you can give them, the better the quality of the answers are gonna be. There's other community kind of Postgres people around. There's an IRC channel. There's some Facebook groups. There's Postgres

people out there that are willing to help. Postgres itself has really good docs on a on a wiki and always dig in there for answers to questions. I like to look at the Postgres docs to kind of understand the terminology and then go and try to find some blogs and videos that, you know, have an application of those. There's a lot of great companies and a lot of smart Postgres people out there and the blog and video content is excellent. And then if you get you know, just to the point that you can't help yourself. Don't be scared to hire a consultant. There's a lot of great consulting companies out there that do Postgres. Some of them don't require you to buy a ton of hours

and you can get five or ten hours of help and really make a big impact on what you're trying to do and then kind of be on your way. Okay, well we're kind of getting to the end. So just a quick summary of sort of some of the things I just want you to walk away with. Choose your host depending on your needs. If you're wanting to be self-managed or fully managed, update your Postgres as often as you can. Make sure that your backups are running and you know how to restore things. Make sure that you know some of these key tuning parameters, especially with memory and connections.

Those are really important. Find a monitoring tool that works for you. And you'll be totally fine. I know you can do it. Um, just a quick shout-out to a couple people that helped me with this talk: my husband, David Christensen. My boss Craig Kirsteens, who is a Postgres content master. If you don't know who Craig is yet, find him online or on Twitter. And Andrew Atkinson writes a lot about Postgres for developers and he helped me with the slides as well All right. Well, that's all I have for you guys today. Thanks so much for everything. Find me on SQL Liz. Find Crunchy Data

if you want to find our newsletter or Postgres content.