background

max_locks_per_transaction, Logical Replication, Backup & Recovery, Pgbouncer | Scaling Postgres 95

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss max_locks_per_transaction, logical replication, managing backup & recovery and pgbouncer connection pooling.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres, we talk about max locks per transaction, logicalreplication, backup, end recovery, and PG bouncer. I'm creston, Jameson. And this is scalingpostgres 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'veactually gone through the past two weeks because there wasn't much content and collected allof it that should have been missed. So this should include everything that occurred over the pastalmost three weeks. Now, our first piece of content is PostgreSQL you might need to increasemax locks per transaction. And this is from Cyberducky postgresql.com. And basically he'sindicating here you may see an error that says out of shared memory. And it basically indicatesyou might need to increase max locks per transaction. And he shows you where you canactually see this if you generate 20,000 create table statements to create that many tables. Andhe used this generic generate series function to be able to use these statements and create allthese tables. And eventually it says error out of shared memory. Hint you might need to increasemax locks per transaction. So because this is all occurring within one transaction, it can't createthat many locks. And by default, if you do a show max locks transactions, it's 64. So you mayneed to increase that if you wanted to have more locks per transaction. Now, I've seen thismyself 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 pertransaction to be able to handle a PG dump with a large number of tables in the database. Andhe says something interesting of note here, the number of locks we can keep in shared memoryis max connections times max locks per transaction. Keep in mind that row level locks are notrelevant here. So, for example, doing a select all from a table with a lot of rows for update, theserow locks are stored on disk and not in Ram. So something to keep in mind. Now, he alsomentions here if you want to see what kind of locks are in action, you can use the PG lockstable. 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 tothis out of shared memory error. So he used this code to be to generate 1000 partitions and thensimply querying the parent table is going to hit all 1000 partitions. And you'll see in PG locks thatit generates over 1000 access share locks, trying to pull back all the data for all of this. So this issomething else that could hit that limit of Max locks transaction. So, great blog post talking aboutMax locks per transaction as well as the out of shared memory error that you may see in yourinstallation. So definitely a blog post to check out. The next post is recovery.com is gone inPostgreSQL twelve and this is also from Cybertechn postgresql.com and basically this followson with a number of other posts that we've seen where version twelve has gotten rid ofrecovery.com. You now do those configuration changes in the postgresql.com file. So nowinstead of the recovery.com file being present, you either have two signal files, a recovery signalfile that tells Postgres to enter normal archive recovery or a standby signal file that tells Postgresto enter a standby mode. And they go over some different considerations here and basicallyyour backups don't really need to change but restoration processes need to change as well ashow you set up Replicas now because these will be impacted by these changes. And he alsoadvocates use of the PostgreSQL auto.com file because that's something set by Alter systemcommands and generally is what's happening with PG based backup now. And he says if you douse third party software, here are the particular versions of PG Backrest, PG Pro Backup andBarman that you should be using that support PostgreSQL version twelve and its new recoveryscheme or recovery process. So, yet another post to review if you are making the transition toPostgreSQL twelve and then what changes you need to make with regard to recovery or yourReplicants.

[00:04:36] The next post is actually a YouTube channel where they've posted updatedpresentations for PG Day Paris. So this is the PG Day Paris --YouTube channel and there's about eight presentations here that they posted for whathappened 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 fromthe Enterprise DB YouTube channel and they're basically talking about what is logical replicationand how to set it up, how to use it and all of those sorts of things. Now, this is a webinar is about54 minutes in length and it's about the twelve minute mark where it really starts into the meat ofthe presentation. But definitely if you're interested in logical replication, this is a presentation tocheck out. And the third piece of video content is actually a webinar. You forgot to put the wherein delete which implies you've probably deleted a lot of data you didn't mean to. So this talksabout descriptions of database backups, the type of backups you can take from PostgreSQLserver, basically logical based backed up where you're backing up objects or physical basedbacked up where you're backing up the raw files and how to do that different use cases forbackups disaster scenarios and what are the best ways to recover from them. So you didn't putthe where in the delete statement or server hardware has crashed, what are some different waysto handle recovery scenarios and things to consider when taking backups? So again, this isabout 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 thewebinar.

[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 abouthow it works, how you can set up authentication. So you're connecting to a PG Bouncer as if itwere a postgres server. And then it basically pulls the connections and uses fewer connectionson the database side. And it goes through the different configuration settings and how you canadjust certain things to increase the pool size, certain things to increase the number of maxclient connections, the max DB connections and max user connections. So it has thisrepresentation here. Basically you keep more live connections open here using fewer databaseconnections. Now it'll use fewer as long as you're using certain pooling modes. So the mostpopular pooling mode is transaction mode, where each transaction will run on a separate PGBouncer connection. There's no guarantee they're all going to be running within the samesession. You can achieve that with session pooling mode. But again, that doesn't let you usefewer connections on the database server because it's a one to one ratio, one session on PGBouncer, one session on the database. But transaction pooling, you could have multipletransactions, actions that can actually happen across sessions. So that enables you to usefewer connections. It's essentially a many to one possibility. So it lets you be more efficient. Butthen you can't set sessions and do certain things in that way. And then you also have astatement pooling mode, which is also popular, but generally the configuration is done usingtransaction pooling. So they go into discussions about why you would choose PG Bouncer oversome other solutions. And then what can PG Bouncer not do? Basically high availability orfailover that you may be able to get through things like from things like a PG Pool. So if you'reinterested in learning more about PG Bouncer, this is a blog post to check you out. A next onerelated to PG Bouncer is can PG Bouncer handle failover to a new machine? And this is fromthe 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 beingable to have a virtual IP address and then flip that out, you can do failovers. So presumablysome of the things that PG Bouncer cannot do, they mentioned here. You could use a tool suchas the EDB Failover Manager to be able to do those sorts of things to have a bit of a seamlessfailover, and they discuss and show some of that here. So if you're interested in using PGBouncer, 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 fromthe Netflix tech bl --og on Medium. And this is a new CDC or a new change data capture tool called DB Log. So itbasically monitors the log files from different database systems. They talk about MySQLPostgreSQL, MariaDB. So it does support PostgreSQL right now, and it looks for changes to thedata and then streams those elsewhere for say, applying to a data mart, a data warehouse, oryou need to kick off some sort of processing. So it goes over in depth to this tool and how itworks and why they chose to design it, versus some other CDC tools that exist. So if you'reusing change data capture for certain use case, and you perhaps want to look at a new tool thatcould 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 tablefunctions, 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 waysthat they can be configured. Now they're just functions, but they return essentially a table ofdata. An example is Generate series so you can do a select from this function. So this is kind oflike a table function. Now, this is from the Ugabyte DB blog on Medium, but all the content Imentioned here is actually applicable to PostgreSQL as well. The second post in the series isimplementing PostgreSQL user defined table functions in Gigabyte DB. But again, you could dothis in PostgreSQL and it talks about the implementation and how you can set it up differentfunctions to do this.

[00:10:57] And the last post is four compelling use cases for PostgreSQL table functions. So thefirst case is you want a parameterized view. Second case is pretty printed ad hoc reports foradministrators. 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 talkingabout table functions. So if you're interested in that type of content, definitely Blog Postgres tocheck out.

[00:11:28] That does it. For this episode of Scaling Postgres, you can get links to all the contentmentioned in the show Notes. Be sure to head over to Scalingposgres.com where you can signup to receive weekly notifications of each episode code. Or you could subscribe by YouTube oritunes. Thanks. --

episode_image