Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about max locks per transaction, logical replication, backup, end recovery, and PG bouncer. I'm creston, Jameson. And this is scaling postgres episode 95.
[00:00:21] All right, I hope everyone had a great holiday season and happy New Year to you. We've had a break for about two weeks and it's time to get started for the new year. And I've actually gone through the past two weeks because there wasn't much content and collected all of it that should have been missed. So this should include everything that occurred over the past almost three weeks. Now, our first piece of content is PostgreSQL you might need to increase max locks per transaction. And this is from Cyberducky postgresql.com. And basically he's indicating here you may see an error that says out of shared memory. And it basically indicates you might need to increase max locks per transaction. And he shows you where you can actually see this if you generate 20,000 create table statements to create that many tables. And he used this generic generate series function to be able to use these statements and create all these tables. And eventually it says error out of shared memory. Hint you might need to increase max locks per transaction. So because this is all occurring within one transaction, it can't create that many locks. And by default, if you do a show max locks transactions, it's 64. So you may need to increase that if you wanted to have more locks per transaction. Now, I've seen this myself sometimes when doing a PG dump when I've had an incredibly high number of tables. This may have only impact earlier versions, but I actually had to increase max locks per transaction to be able to handle a PG dump with a large number of tables in the database. And he says something interesting of note here, the number of locks we can keep in shared memory is max connections times max locks per transaction. Keep in mind that row level locks are not relevant here. So, for example, doing a select all from a table with a lot of rows for update, these row locks are stored on disk and not in Ram. So something to keep in mind. Now, he also mentions here if you want to see what kind of locks are in action, you can use the PG locks table. So he discusses that how you can query it to find out what locks are present. Now, another issue that you may see this for is if you are doing partitioning and how it can relate to this out of shared memory error. So he used this code to be to generate 1000 partitions and then simply querying the parent table is going to hit all 1000 partitions. And you'll see in PG locks that it generates over 1000 access share locks, trying to pull back all the data for all of this. So this is something else that could hit that limit of Max locks transaction. So, great blog post talking about Max locks per transaction as well as the out of shared memory error that you may see in your installation. So definitely a blog post to check out. The next post is recovery.com is gone in PostgreSQL twelve and this is also from Cybertechn postgresql.com and basically this follows on with a number of other posts that we've seen where version twelve has gotten rid of recovery.com. You now do those configuration changes in the postgresql.com file. So now instead of the recovery.com file being present, you either have two signal files, a recovery signal file that tells Postgres to enter normal archive recovery or a standby signal file that tells Postgres to enter a standby mode. And they go over some different considerations here and basically your backups don't really need to change but restoration processes need to change as well as how you set up Replicas now because these will be impacted by these changes. And he also advocates use of the PostgreSQL auto.com file because that's something set by Alter system commands and generally is what's happening with PG based backup now. And he says if you do use third party software, here are the particular versions of PG Backrest, PG Pro Backup and Barman that you should be using that support PostgreSQL version twelve and its new recovery scheme or recovery process. So, yet another post to review if you are making the transition to PostgreSQL twelve and then what changes you need to make with regard to recovery or your Replicants.
[00:04:36] The next post is actually a YouTube channel where they've posted updated presentations for PG Day Paris. So this is the PG Day Paris YouTube channel and there's about eight presentations here that they posted for what happened in 2019. So if you're interested in some video content, definitely a link to check out.
[00:04:56] Another piece of video content is logical replication in PostgreSQL. And this is from the Enterprise DB YouTube channel and they're basically talking about what is logical replication and how to set it up, how to use it and all of those sorts of things. Now, this is a webinar is about 54 minutes in length and it's about the twelve minute mark where it really starts into the meat of the presentation. But definitely if you're interested in logical replication, this is a presentation to check out. And the third piece of video content is actually a webinar. You forgot to put the where in delete which implies you've probably deleted a lot of data you didn't mean to. So this talks about descriptions of database backups, the type of backups you can take from PostgreSQL server, basically logical based backed up where you're backing up objects or physical based backed up where you're backing up the raw files and how to do that different use cases for backups disaster scenarios and what are the best ways to recover from them. So you didn't put the where in the delete statement or server hardware has crashed, what are some different ways to handle recovery scenarios and things to consider when taking backups? So again, this is about an hour in length webinar. So if you're interested in this content, definitely check it out. And you can click the link here to go ahead and register and you'll get immediate access to the webinar.
[00:06:20] The next piece of content is PostgreSQL connection pooling. Part Two PG bouncer. So this is a second post about PG Bouncer and its use as a connection pooler. It talks about how it works, how you can set up authentication. So you're connecting to a PG Bouncer as if it were a postgres server. And then it basically pulls the connections and uses fewer connections on the database side. And it goes through the different configuration settings and how you can adjust certain things to increase the pool size, certain things to increase the number of max client connections, the max DB connections and max user connections. So it has this representation here. Basically you keep more live connections open here using fewer database connections. Now it'll use fewer as long as you're using certain pooling modes. So the most popular pooling mode is transaction mode, where each transaction will run on a separate PG Bouncer connection. There's no guarantee they're all going to be running within the same session. You can achieve that with session pooling mode. But again, that doesn't let you use fewer connections on the database server because it's a one to one ratio, one session on PG Bouncer, one session on the database. But transaction pooling, you could have multiple transactions, actions that can actually happen across sessions. So that enables you to use fewer connections. It's essentially a many to one possibility. So it lets you be more efficient. But then you can't set sessions and do certain things in that way. And then you also have a statement pooling mode, which is also popular, but generally the configuration is done using transaction pooling. So they go into discussions about why you would choose PG Bouncer over some other solutions. And then what can PG Bouncer not do? Basically high availability or failover that you may be able to get through things like from things like a PG Pool. So if you're interested in learning more about PG Bouncer, this is a blog post to check you out. A next one related to PG Bouncer is can PG Bouncer handle failover to a new machine? And this is from the Enterprisedb.com Blog, and they talk about PG Bouncer and how they have set up things. But EDB has a tool called the Failover Manager that uses a virtual IP capability. So with being able to have a virtual IP address and then flip that out, you can do failovers. So presumably some of the things that PG Bouncer cannot do, they mentioned here. You could use a tool such as the EDB Failover Manager to be able to do those sorts of things to have a bit of a seamless failover, and they discuss and show some of that here. So if you're interested in using PG Bouncer, these have our two posts that you may want to check out.
[00:09:02] The next post is DB Log, a generic change data capture framework. And this is from the Netflix tech blog on Medium. And this is a new CDC or a new change data capture tool called DB Log. So it basically monitors the log files from different database systems. They talk about MySQL PostgreSQL, MariaDB. So it does support PostgreSQL right now, and it looks for changes to the data and then streams those elsewhere for say, applying to a data mart, a data warehouse, or you need to kick off some sort of processing. So it goes over in depth to this tool and how it works and why they chose to design it, versus some other CDC tools that exist. So if you're using change data capture for certain use case, and you perhaps want to look at a new tool that could offer some different features, definitely check out this blog post.
[00:10:00] The last set of posts are all related to PostgreSQL table functions. Now, by table functions, they're referring to functions in PostgreSQL that would actually return a table of data. So this goes over an introduction about what they are, how you can use them, the different ways that they can be configured. Now they're just functions, but they return essentially a table of data. An example is Generate series so you can do a select from this function. So this is kind of like a table function. Now, this is from the Ugabyte DB blog on Medium, but all the content I mentioned here is actually applicable to PostgreSQL as well. The second post in the series is implementing PostgreSQL user defined table functions in Gigabyte DB. But again, you could do this in PostgreSQL and it talks about the implementation and how you can set it up different functions to do this.
[00:10:57] And the last post is four compelling use cases for PostgreSQL table functions. So the first case is you want a parameterized view. Second case is pretty printed ad hoc reports for administrators. Third case is a dynamic end list.
[00:11:15] And the last case is compact syntax for bulk inserts. So, three sets of posts talking about table functions. So if you're interested in that type of content, definitely Blog Postgres to check out.
[00:11:28] 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 Scalingposgres.com where you can sign up to receive weekly notifications of each episode code. Or you could subscribe by YouTube or itunes. Thanks.
