How to Keep your Database Happy

Talks

Karen Jex

@karenjex

#postgresql

#databases

#database-performance

#high-availability

#data-integrity

How to Keep your Database Happy

by Karen Jex

In her presentation titled "How to Keep your Database Happy" at the Helvetic Ruby 2023 event, Karen Jex, a Senior Solutions Architect at Crunchy Data, shares insightful strategies for ensuring optimal performance and management of databases, particularly focusing on PostgreSQL. Jex emphasizes that developers, who often prefer to focus on coding rather than database maintenance, can implement several straightforward tips to maintain a smooth-running database environment.

She outlines her top five tips as follows:

Check Key Configuration Parameters: It's crucial to review and adjust a few essential configuration settings in PostgreSQL rather than relying on default values. This includes parameters like shared buffers, work_mem, and the write-ahead log (WAL) settings, which can greatly impact the database's performance.
Regular Backups and Testing Recovery: Jex stresses the importance of taking regular backups and testing the recovery process to ensure the integrity of the data stored in the database. Insights on backup tools like pgbasebackup, pgBackRest, and pgdump are discussed, alongside the necessity of point-in-time recovery setups using WAL archiving.
High Availability Architecture: Implementing a high availability setup is vital in production environments to minimize downtime. Jex talks about using streaming replication to maintain replica databases and tools like Patron for monitoring cluster status and managing failovers, ensuring users have immediate access to data.
Manage User Connections: Proper management of who and what can connect to your database is imperative. Jex advises creating application-specific users with restricted privileges and fine-tuning connection settings like maxconnections and pghba.conf configurations.
Monitoring Database Activity: Keeping an eye on database activity is essential for prompt troubleshooting. Basic Postgres logging and the pgstatstatements extension can provide insights into database performance and query statistics.

Jex concludes with a positive note on PostgreSQL, emphasizing that with proper implementation of these tips, maintaining a robust database becomes much easier. The aim is to allow developers to concentrate on application development, knowing their database runs efficiently in the background.

00:00:05.240 Hi! Oh yep, that is working. Hi, it's really lovely to be here. So, I'm Karen Jex, a Senior Solutions Architect at Crunchy Data.

00:00:10.519 This is my first ever Ruby event, so I'm really, really thrilled to be here. Thank you!

00:00:13.960 I'm looking forward to sharing my tips with you on how to keep your database happy. First, I have to get the confession out of the way: I'm not a developer. I'm sorry! But as you can see from my career illustration so far, I know a lot about databases. I have worked with databases exclusively for the past 25 years.

00:00:28.480 This is my first ever job title that doesn't include 'database' in the name, but I still work exclusively with databases. I've learned a lot from the developers I've worked with, and I've tried really hard to share with them what I know about databases. That's what I'm here for today.

00:01:03.440 I'm here to talk about databases, particularly Postgres. One of the things that developers appreciate about Postgres is that it just works. This is great because, as a developer, you don't want to spend a lot of time managing your database; you have better things to do.

00:01:17.280 Just out of curiosity, how many people here actually use Postgres or have applications that run on Postgres? Fantastic! I feel it’s worth me being here. So, I promised you my top five tips—things that you can implement without too much effort to ensure that you've got a robust, scalable, performant database environment.

00:01:42.840 The focus is very much on Postgres, but most of the tips are also relevant to other databases. Given that Postgres is relevant to the audience, let’s dive into the tips!

00:02:02.640 Tip number one: check a few key configuration parameters and ensure they are set correctly for your environment and workload. Number two: take regular backups of your database and test your recovery process.

00:02:36.200 Number three: put a high availability architecture in place. Number four: make sure the right users and applications can connect to and interact with your database. Finally, number five: ensure you know what’s going on in your database so you can react quickly if something goes wrong.

00:02:57.400 So, there you have it—my top five tips. Thank you very much! Now, I think we have roughly three minutes for each tip, so let’s go into more detail.

00:03:03.440 Number one: checking the settings for a few key configuration parameters. Out of the box, a Postgres database has a very small footprint, allowing you to install it virtually anywhere without worrying too much about resources.

00:03:39.280 However, the default configuration may not be suitable for your production environment. In fact, there are 361 parameters in Postgres 16, but you’ll be pleased to know that you don’t need to look at all of them. Just adjusting a few can make a big difference.

00:03:57.640 For example, you should allocate the right amount of memory to your database and manage checkpoints appropriately—not too many, not too few, but just right. Let’s examine a few of those parameters. Shared buffers dictate the amount of memory allocated at startup for caching data. The default value is 128 MB but should be much higher if you have enough memory.

00:04:54.160 If your system has more than 1 GB of RAM, start somewhere between 25% and 40% of your total memory.

00:05:01.400 Work_mem refers to the maximum amount of memory that can be used by a query operation before it spills to disk and creates a temporary file. The default is 4 MB. A larger value is useful for queries that perform complex sort operations, but be cautious; a complex query could run multiple operations.

00:05:52.760 It’s often better to set this for specific sessions that require more memory rather than across the board. The log_temp_files parameter can help you see when temp files are created. If you notice a lot of temporary files, it may suggest that you need to increase work_mem.

00:06:31.600 Maintenance work_mem is similar but determines the memory used by maintenance operations such as vacuum and database index creation. The default is 64 MB, but a higher value can significantly improve performance.

00:06:50.760 Now, tuning your write-ahead log (WAL) and checkpoints can also impact performance. The WAL logs all changes made to your data before being written permanently to disk. Checkpoints flush dirty data pages to disk; they're expensive operations, so frequency should be managed.

00:07:26.000 The wal_buffers parameter determines how much memory is allocated for WAL before syncing to disk. The default is -1, calculated automatically to about 3% of shared buffers. If you have many concurrent connections, consider increasing it.

00:07:49.760 Checkpoint timeout triggers a new checkpoint if one hasn’t occurred within a set duration, typically defaulting to 5 minutes, which many find too low.

00:08:02.760 Adjust it between 10 and 30 minutes for better control. Max_wal_size will also trigger checkpoints if the specified amount of WAL is generated. The default is 1 GB, equivalent to a certain number of WAL files. You want to ensure your setup has suitable space for WAL files.

00:09:09.560 Tip number two: make sure you take regular backups of your database and test your recovery process. I’m going to assume that the data in your database is important, and if something goes wrong, you need to recover it.

00:09:48.200 So, you’ll need somewhere to store those backups, preferably in a separate location or on a different server. You need a backup tool; pg_basebackup is integrated with Postgres. Consider using pgBackRest or Barman for comprehensive options. Don’t forget logical backup methods, such as pg_dump, which allows you to recreate objects in your database.

00:10:59.840 Also, schedule regular backups. An optimal setup means most people cannot afford downtime for their database. You want point-in-time recovery as well.

00:11:21.880 For that, configure WAL archiving by saving copies of your WAL files. Use tools to facilitate your backups and test recovery regularly to prove its effectiveness.

00:11:52.320 Tip number three: implement high availability. While you can recreate your database from a backup, your users prefer immediate access. Most production environments need high availability. This setup isn’t as quick or easy as the other tips, but isn't as complex as you might think.

00:12:17.920 If you lack the time or infrastructure for setup, consider managed services. Using Postgres streaming replication to create replica databases that stay current is a common architecture.

00:12:56.520 Patron is a monitoring framework and uses a DCS like etcd to maintain the cluster's current status. If issues arise, it can promote a standby to primary, quickly minimizing downtime without waiting for user intervention.

00:14:04.720 You may use tools like HAProxy for automatic virtual IP management, helping eliminate the need for application changes. Although detailed setup for these frameworks would take longer than three minutes, links to documentation are provided for further reading.

00:15:11.440 Tip number four: ensure the right users and applications can connect to and interact with your database. You'll want to manage connections carefully to control resource use.

00:15:40.280 Create application users with restricted privileges—it's crucial that your applications don’t connect using the database superuser account. Control connection settings such as listen addresses, specifying which hostnames or IP addresses Postgres will accept.

00:16:17.400 The default is local connections only, so configure for wider access if needed. Also, manage max_connections—default is typically 100—but be careful of setting this too high, as Postgres performs optimally with fewer concurrent connections.

00:16:50.320 Control idle transactions and client authentication through the pg_hba.conf file. It specifies which users are allowed to connect, their allowed IP ranges, and authentication methods. Make necessary custom entries for your environment.

00:17:40.440 Finally, tip number five: remain aware of what's happening in your database to quickly react if issues arise. You may want an advanced monitoring system, but even basic Postgres logs can provide valuable insights.

00:18:36.520 Ensure logging_collector is active to capture log messages. Be aware of your log directory's location and contents. Consider configuring log parameters, such as log_line_prefix, to ensure logs are informative.

00:19:20.200 Utilize the pg_stat_statements extension for insights into query performance. Make sure to configure and enable it for information on frequently executed queries.

00:20:13.880 In conclusion, Postgres truly does just work. You shouldn't have to exert much effort to maintain a reliable database. Focus on key configuration parameters, ensure regular backups with recovery testing, set up high availability or consider managed services, manage connections appropriately, and maintain awareness of database activity.

00:21:45.080 Once you have all this in place, your database should be self-sufficient, allowing you to focus on developing your application. Thank you very much!

00:22:02.799 the

Helvetic Ruby 2023