Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about indexonly scans, locking tables, hot standby feedback, and very large backups. My name is Kristen Jameson, and this is scaling postgres episode 54 one.
[00:00:22] Alright, I hope everyone's doing well this week. Our first article is 2019 database trends SQL versus NoSQL, top databases, single versus multiple database use. And this is from ScaleGrid IO blog. And this is a survey from asking hundreds of developers at Developer Week to discuss these different trends. So they are talking about SQL versus SQL or SQL versus NoSQL, and they look at the ratio to what people are using, they look at the most popular databases, so it looks like MySQL is coming out on top, followed by Mongo and then PostgreSQL. And then they reference the DB engine rankings with regard to who's increasing in popularity. And then they talk a little bit about single database versus multi database use and how those are broken out by SQL and NoSQL, as well as time spent between different database management tasks. So if you're interested in looking at some of this developer survey data, definitely a blog post to check out.
[00:01:29] The next post is index only scans in postgres. And this is from the PG Mustard blog and it's talking about index only scans. These are index scans that can only look at the index to be able to answer the question that's being queried. Normally with an index scan, depending on how much data you need to pull back, it scans through the index and then goes and pulls the data from the heap to be able to return the query results. However, if you're only asking for data that is already indexed, it can just do an index only scan and return that data to you, so it can result in much faster queries. And they describe two conditions that must exist for this to work. First is a compatible index. So btree indexes work. However, something like a gen index that you would normally use on a JSON field or a full text field you want to do full text searching on. Actually you can't use an index only scan with that, and if you're using other types of indexes, you should consult whether you can use those or not. But the standard B tree indexes do work. The second condition is that the data columns for the information you're returning need to be part of the index. So for example, they're showing an index here that is just indexing the chef column. If you want to be able to return more data than just the chef column, you need to add more columns to that index. Now a benefit of PostgreSQL version eleven is it allows covering indexes. So you can still only index the column that you're interested in, but you can actually bring back what I like to call a payload of what that index should include. So you can actually increase the proportion of index only scans you can get. Now then it has this important caveat here. When is an index only scan not an index only scan? Well, because PostgreSQL is a multi version concurrency controlled database, it has the concept of visibility. In other words, are you a user that can what's your state of usability of the data? You can see now that usability data is predominantly stored in the heap. So sometimes when you want to do an index only scan, it actually needs to consult the heap to see what's visible. Now there is a visibility map when you're trying to do an index only scan to see if it needs to go to the heap. Hopefully you do not need to, but if the data page has changed then you will need to consult the heap and when you have a query plan output, it actually tells you how many heap fetches were necessary. So this was a good blog post that explained index only scans and it is definitely a technique you can use to make your queries faster.
[00:04:07] The next post is Locked table can harm your database's Health. And this is from Cybertechn Postgresql.com blog and it starts off just explaining table locks in general. So normally in your operation of the database when you do a select on the table, it will do a Access Share lock automatically. You don't have to automatically lock at the select part does that or other types of locking depending upon what data operation you're needing to do. Like for example an Access Exclusive lock when you're going to truncate a table. But of course it mentions you can explicitly lock a table with the lock table statement. Now they mention here most people who are using this lock table stable don't know that the default lock mode is Access Exclusive, which blocks all concurrent access to the table, even Read Access. So that's probably not what you want to do to your database when you're trying to scale and that there's less restrictive ways to be able to do locking. And this part was actually a great reference. So they talk about you don't want concurrent transactions to modify a row between the time you read it and the time you update it. Use. Select for update. If you want to perform several selects on the table and want to be sure that nobody modifies the table between your statements, then use a transaction with the repeatable Read isolation level. You want to get a row from a table, process it later and then remove it. Use delete with returning. You want to implement a queue where workers should grab different items and process them. Use select with a limit of one for Update skip Locked and you want to synchronize concurrent processes with database techniques, then use advisory locks. Now, how locking tables can really cause you problems is regard to auto vacuum and that vacuum's share Update Exclusive Lock. A lot of times conflicts with lock levels people are using, such as Shared and Access Exclusive. And here's an important point they made. Quote now, Auto Vacuum is designed to be non intrusive. If any transaction that wants to lock a table is blocked by Auto Vacuum, the deadlock detector will cancel the Auto Vacuum process after a second of waiting and it actually posts an error in the log and then the Auto Vacuum will start up again. But the problem is, if you regularly lock tables as a part of your application's operations, you could be canceling Auto Vacuums frequently enough that it will never be able to finish. Then you will get table Bloat and your transaction IDs may start to be wrapping around shutting down the database, requiring a restart in single user mode with a manual vacuum. So you definitely don't want to get into that situation. And they phrase it as the ugly end. They say how can I avoid this problem? And if you already have the problem, just launch a manual vacuum, full freeze on the table. Wait until it's done to avoid the problem. Don't use lock on a routine basis. Turn auto vacuum to run more aggressively. And also using PostgreSQL 9.6 or later also assists. So if you find yourself as a developer locking tables as part of your application's operations, maybe try to figure out a way to minimize that. Or use some of the techniques mentioned in this blog post to make your application more performant and protect it from potential harm.
[00:07:30] The next post is I Fought the Wall and the Wall Won why hot Standby Feedback can Be Misleading so, this blog post is talking about hot standby feedback. So, if you have a primary database set up that is Replicating to one or more Replicas, those Replicas are typically set up in a hot standby state, meaning they can be promoted to be the new primary database. Therefore they are known as Hot standbys. So you're doing streaming replication from the primary to the Replicas. Now, a lot of times people do read only queries to these Replicas, but the problem is that sometimes queries read only queries to these Replicas get canceled and you'll see something to the effect of error canceling statement due to conflict with recovery. That's because, as they mentioned in this blog post, the job of the Replica is to follow the primary database. And if you have long running queries where a table needs to be dropped or a number of deletes, whatever changes are happening to the Master eventually need to be reflected in the Replicas. And typically there is allowed about 30 seconds for the readonly query to finish before the Replica forces itself to keep up with the state of the current primary. Now, one way you can solve this is to use hot standby feedback which basically allows the primary database to keep track of the back end X Men to keep track of where its replicas are at in their stage of replication and whether they can get rid of wall files. Because all the queries have completed on the Replicas. Now, of course the trade off they mention here is that setting hot stem by feedback to on can incur some table Bloat on the primary but often it's not significant because basically you're asking the primary to delay rotating those wall files until the replicas have finished the queries they're running. Quote however, there are some cases of query cancellation that hot stem by feedback cannot prevent. So if you have exclusive locks on the relation of the primary flaky or wall receiver connections or frequent writes on small numbers of tables and it goes into explain more of the details of each of these areas to consider if you have hot standby feedback on but basically the hot standby feedback is not a panacea. It will help increase the probability that a replica will allow readonly queries to finish but there are some cases where they still may be canceled. So you need to prepare for that in your application to be able to handle those canceled queries. So it helps, but it doesn't help all the time. So if you're interested in using this feature, definitely a blog post to check out.
[00:10:11] The next post is PostgreSQL Tools for the Visually Inclined. Now, I read this title and I was expecting to see a breakdown of the different visual tools for PostgreSQL but basically it's talking about how there aren't really many and you should actually embrace the text only tools. So it's more of a endorsement of PostgreSQL's direction of primarily using and you know the quote, here your best friend psql, and he talks about all the different capabilities you can do and he contrasts it with using some of the visual tools of like Microsoft SQL Server and he goes through all the different ways you can do different DBA tasks. And primarily he comes down to is that it's a matter of speed. A text based tool should always be faster than a Gui tool basically. So if you want to find new and better ways to use the text based tools like psql and PostgreSQL, definitely a blog post to check out the next post. Managing PostgreSQL Backups and Replication for Very Large Databases part Two. And this is from the Lebanon Coin Engineering blog. Now, we covered this first part in a previous episode of Scaling Postgres. So this is a second part and here he goes over the importance of restore tests and how no matter the way your backup is set up, you definitely need to set up restore tests. And they say they do it on a weekly basis and they also go into a little bit about how their backups are set up. So they do logical backups. So PG dumps to be able to have long term data retention. And they talk about information with regard to GDPR. But of course, they also do physical backups that are used for restoration purposes, for restoring an entire database, and how they use Barman for this purpose, because they do have actually multiple data centers. They said which barman has the capabilities to do the backups for? And then they talk about their physical backup retention. Estimating. Time to backup. Estimating the Time To restore. So it really has a good overview of how to examine your environment and what kind of backup restore protocols work best for you. So if you're interested in learning more, definitely a blog post to check out.
[00:12:34] The next post is how shift left uses PostgreSQL extension timescale. DB. So TimescaleDB is a PostgreSQL extension optimized for storing time oriented data sets. And it basically uses this by using concept of partitioning, but partitioning many, many tables. And it's a structure I believe they call Hyper tables. So this organization uses TimescaleDB, which is running on Postgres, and they talk about their rationale for using it and how they use it. So if you have a need for tracking time series oriented data and want to use PostgreSQL to do it, definitely a blog post to check out.
[00:13:15] The next post is settling the myth of Transparent. Huge pages for databases. And this is from the Procona.com blog, and he's talking about a testing process where he goes and enables transparent huge pages to see if he can get a boost in performance or what's the performance like. Because most recommendations that he's observed have said not to use transparent huge pages, but he goes through some benchmarks and basically he comes to the conclusion, yes, you probably shouldn't use it. But what's even more interesting this blog post is the discussion with regard to the comments. So, for example, the first post here, are you planning to test non transparent huge pages soon? And they said they did that and it gave really good performance, although there's some back and forth with regard to how much it improved. Like the reference to this post is this one back from in December benchmark PostgreSQL Linux Huge Pages. And it talks about their process of enabling huge pages in the results. But I remember it actually may be this individual, the author here. There was a presentation by Procona at I think it was Fostem, and they were saying, well, some of those results with the Linux Huge pages weren't conclusive either. So it's still very much up in the air for me. How much at least Linux Huge Pages can help improve performance and it may be dependent upon your workload and what you're doing. Is it more of an OLTP or is it more OLAP? And it's probably the kind of configuration that you need to benchmark how your database operates currently. Try changing or implementing huge pages in a test environment to see if you get better performance using that. And if you're interested in doing that, these are definitely two blog posts to check out that does it. For this episode of Scaling Postgres, you can get links to all the content mentioned in the show notes. Be sure to head over to Scalingpostgres.com, where you can sign up to receive weekly notifications of each episode. Or you could subscribe via YouTube or itunes. Thanks.
