background

Integer Overflow, User-Friendly Permissions, Dump & Logical Replication, Worker Config | Scaling Postgres 255

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

In this episode of Scaling Postgres, we discuss how to detect & handle integer overflows, a wish for user-friendly permissions, using a dump to start logical replication and configuring background workers.

Content Discussed

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your friends, family, and coworkers continue to do well. Our first piece of content is "The Integer at the End of the Universe: Integer Overflow in Postgres". This is from crunchydata.com. And this is a great post with a ton of information about basically running out of integers. So as you're scaling your database, this becomes exceedingly important because you don't want to have, say, an integer that overruns. That's your primary key for an important table. Ideally, you're going to want to use bigints but if you happen to have integers there and you're at risk of overflowing them, this post goes through ways on how to detect that and alleviate that. So the first thing they provide here is a really comprehensive query that helps you find auto-incrementing columns and the sequence. It gives you a percentage of how close it is to becoming overrun.

So if you look down here, they did a test. They set a sequence at the 2 billion mark for a table. And then they ran that query against it. And here you can see it is 93% in the overflow state. So this is definitely a great query to have to actually detect these in the system. Now I'm assuming they don't detect other types of columns like just a standard integer column that is at risk of being overrun. They're specifically saying auto incrementing columns. Now in terms of mitigating this, one solution they have to mitigate it is to actually start using all of the negative numbers that are available within that integer.

So he says you could simply alter the sequence, start with a negative one, and increment by negative one. So to the database, that'll just keep on chugging without an issue. And you don't have to change the column structure and it's very fast. You just run this command and you're done. Now the negatives, this might not work with your application framework or even how you programmed your application. For example, if you count on doing an order by the primary key, that is an integer, suddenly your recent data is now going to be considered old. And there may be other issues depending on how you've written your application.

But definitely, this will be a super fast way to alleviate an integer overrun problem. The next solution is changing it to a bigint. Now you don't want to just change the type to a bigint because that's going to require a whole table rewrite. That's fine if it's a small table but the fact that your about to overrun the integer implies it's a large table. But if you do a lot of deletes of that data, maybe it is actually a table with a small amount of data. So changing in place could be an option but the solution that they propose here is creating a new bigint column, starting to use this as the unique identifier going forward and then backfilling the rest.

So here they create the new bigint column. They create a new unique index for it because this is going to be the new primary key. You create a new sequence for this new column and go ahead and define the next value and the column that should own it. So now moving forward, you have IDs that look like this. Then in one transaction, you flip over from one column to another. So you drop the constraints and the defaults, then you rename the columns, alter the old column so that it's not null, and then add a constraint on that new column, saying it's not null but it's not valid.

So what that means is, moving forward, it will enforce that not null constraint, but because it's not valid, it's not going to look at the old rows yet. Then you do a backfill of all the data from the old column to the new column, then you can validate that not null constraint, add a primary key constraint, and then drop the old constraint. So that's definitely one solution to use to switch to a bigint column without downtime. And I've seen other ways of doing this where you actually backfill first, and then you do it this way. But if you're in an emergency, this is probably the fastest way to do it to get switched over to a bigint column as soon as possible.

Now of course, if you're changing these over, you need to be aware of foreign keys, potentially drop the foreign key constraints, and address the foreign key columns as well so that they can handle the new larger integer. So that's definitely another consideration you need to take into account. Then, of course, in closing they say really, the best thing you should be doing is using bigints to start, at least for definitely the primary keys and foreign keys. And that if you're used to using SERIAL types, you don't want to be using the SERIAL type because that's the standard integer; you actually want to be using the BIGSERIAL type. But this was a great post with a lot of great information, I highly encourage you to check it out.

Next piece of content- "Can we make permissions management more user-friendly?". This is from hdombrovskyia.wordpress.com. And she has a very complex setup with a lot of users and a lot of databases that she has to manage. She's encountering a lot of complexity with the permission systems in Postgres to set it up in the best way possible. One difficulty is being able to view all access privileges for a specific user. You basically have to do some recursive queries to actually find out what exactly those permissions are. She talks about direct privileges, meaning that you can set privileges on a role that can log in or essentially the user that can log in.

I'll say it is unusual in a permission system to have users and groups being interchangeable. Generally, they are considered separate objects and that's something unique and different about Postgres is that users and roles are all just considered roles. Then she talks a little bit about default schema permissions and other inconsistencies that she encounters. So I thought this was an interesting post to check out. Also interesting is the different responses to the post below where some people are suggesting some solutions for some of the issues she encountered. As well as proposing Potential patches to be able to do certain things like being able to recursively find all the permissions of a user.

And as I think about the Postgres permission system, I think it seems to have very few constraints. From the fact that users can be roles and they are interchangeable and you can have groups that are actually under what you would classify as a logged-in user. I think that's probably what's causing some of this difficulty, maybe if there were more constraints, it might be easier to work with. Of course, the flip side of that is that it becomes less powerful. However, I found it an interesting discussion and viewpoint on the Postgres permission system. So definitely encourage you to check this post out.

Next piece of content- "Starting PostgreSQL logical replication for some tables based on a dump?". This is from dbi-services.com. He's talking about how he was working with a client that had a Postgres database on a cloud-managed service. They wanted to set up logical replication to another instance, but the problem was that trying to do the synchronization between the service Postgres and the other instance that they had would have taken weeks to complete the full initial synchronization of the data. Now some of the tables were pretty quick but there were specific larger tables, I assume, that were taking forever in the initial copy to get synchronized.

But they came up with an interesting solution here where they created a publication for all the tables that didn't have issues and then set up a subscription with them so that they started syncing over. But then to symbolize a problematic Table, they're using the pgbench_accounts table. And the first thing they did is they created a publication and then they created the replication slot on the client. During the creation, they get back the snapshot name. Now they use that snapshot name to do a pg_dump from the service provider's Postgres database using these commands that you see here. And then on the destination database, they load that pg_dump table.

They then create this subscription on the destination database. Using that specific slot name, they leave it disabled so enabled is false and they say copy data is false. So the initial data copy was done by the pg_dump and restore. So they don't need to do that when creating the subscription. And once that's in place, they can start this subscription by altering it and enabling it. Then it should pick up right at that snapshot point to start replicating the data accurately. So I found this a super interesting technique to accelerate getting logical replications setup. So if you encounter this particular problem, maybe you want to check out this blog post.

Next piece of content- "Workers of the World, Unite!". This is from thebuild.com. And he's talking about configuring worker processes for Postgres. And basically, when you think about the worker processes and Postgres or the background processes, you can think of them like a Venn diagram. You have the setting of max worker processes, that's the maximum total worker processes you can have, and then of that, you have a certain number of max parallel workers. And then of that, you have max parallel workers per gathering for processing things and then max parallel maintenance workers for doing things like auto vacuum or perhaps index creation. So he describes what each of these do in more detail and also goes into recommendations for each of these settings.

So he says generally, for max worker processes, you want to stick with four to eight to start with. He says if you routinely process larger results sets, increasing parallel workers per gather from two to four to six is reasonable. And in terms of parallel maintenance workers, four to six is a good value. Go with six if you have a lot of cores or four if you have more than eight, otherwise two. He does say usually, you want two to three times the number of cores for worker processes in general. But if you have a lot of cores, 32 to 64 maybe 1.5 times may be more appropriate. So if you want some guidance on how to configure your background workers for Postgres, definitely check out this blog post.

Next piece of content- "Postgres 16 highlight: More regexps in pg_hba.conf". This is from paquier.xyz. So this is a patch that will hopefully make it into Postgres 16 where you can now use regular expressions for defining databases and users that you can connect to. So in this example, there's this double-quoted regular expression that basically allows connections from a DB system with up to four integers at the end of it. It also supports defining multiple values with commas in between including regular expressions because of double quotes. So for really complex systems, this could be highly advantageous. And I encourage you to check this out if you want to learn more.

Next piece of content- "STORED PROCEDURES IN POSTGRESQL: GETTING STARTED". This is from cybertec-postgresql.com. And this is a basic post talking about how to start creating and using procedures in Postgres. They also discuss a little bit about the difference between procedures and functions and generally functions all take place within a transaction. So you can't in a function, for example, do a commit or a rollback. That's connected to the transaction. It's a part of so you can't do that within a function. But you can within a procedure because it does not exist within the scope of a transaction.

So within a procedure, you can have it commit for example or do a rollback. So I like to use procedures if I'm doing backfills of data, for example. Maybe you ran out of integers and you want to do a backfill process within Postgres that grabs a certain number of IDs at a time and commits them. Procedures are a great use case for that as opposed to a function. But this goes into a little bit more detail about functions, procedures, and transactions. So if you want to learn more, definitely check out this blog post.

Next piece of content- "PGSQL Phriday #006". This is from pgslqphriday.com. And it's a new month So it's time for a new PGSQL Phriday. So this is the announcement post. The actual introduction post is here- "ONE THING YOU WISH YOU KNEW WHILE LEARNING POSTGRESQL: PGSQL PHRIDAY #006". And this is from scarydba.com. And he's basically looking for posts on what people wish they knew when they started Postgres.

The next post that addresses this is at softwareandbooze.com. And he's talking about lateral joins, specifically cross join laterals. A lateral join basically allows you to quote "...execute an inner query for each row of the outer query and you can reference values at the parent". So I think of it as, for loop essentially, for each row that you're pulling back. A cross-join can actually help you create all sorts of interesting data, and he shows some examples here. So if you want to learn more about this, you can check out this blog post.

The next post on this topic is from gorthex.wordpress.com. And he's talking about the \e command in PSQL. Basically, it allows you to load a file name into your editor and work with it and then execute the statement. Or if you use it without a file name, it just brings up the last query you ran so you can make a quick edit to it and run it again. So that's his recommendation.

The next post in the series is from mydbanotebook.org and it's about Postgres. documentation. It basically gives some recommendations on how to make sure you're on the right version if you're looking at documentation. Particularly if you're Googling something, make sure you have the right version because, of course, features change across the versions. But also she gives recommendations for your DBA, maybe you want to go through these different sections of the documentation to get up to speed. As well as what if you're a software developer. So those are some great areas you can check out if you're interested.

I should also mention that she references another website she has called psql-tips.org. Now we did cover this on a previous episode of Scaling Postgres, but more tips seem to be added such that they're at 152, as of right now. So if you want to learn PSQL, definitely recommend checking out this website.

Next piece of content- "Geocoding with Web APIs in Postgres". This is from crunchydata.com. And I'm used to geocoding addresses and I just have a job that runs in the background and does it for new data being added. But I actually have my application framework making these web calls out to a service and pulling the data back. In this example, they're actually using a Python module within Postgres itself. So they're not even using an application server to do it. So the first thing they did was create the plpython3u extension in the database so they could run some Python code. Then they created this function here that basically calls out to the census.gov geocoding website to be able to geocode addresses. So that when they run a statement like this, they get a set of points back that they can use for longitude and latitude.

Now they set up a table to be able to store different addresses as well as the geometry data type for the longitude latitude coming back. Then they created a function to be able to store this data as it's being received and associated it with a trigger that runs before data is actually inserted on the address table. So here you can see you can now insert an address with the address value and then the database makes the call out to the service to get back the geometry and stores it all in one insert. Now I don't think I'll actually be following this process. I don't want my database inserts to be hampered at all because it suddenly can't contact a service. But it's definitely an interesting way of handling this. So you can check out this blog post if you want to learn more.

Next piece of content- "Waiting for PostgreSQL 16- Add pg_stat_io view, providing more detailed IO statistics". This is from depesz.com. Now we've covered this in previous episodes of Scaling Postgres. This is the new pg_stat_io view that shows you all the IO activity based upon the backend type and the IO context. So this is a lot of great information that wasn't available before but it is now. And he discusses ways he would find this useful as a DBA.

So you can check out this post as well as this is also the post from pganalyze.com- "How Postgres DBA's can use pg_stat_io". Where he covers this episode on their "Five minutes of Postgres". So check that out if you want to learn more.

Next piece of content- "European Route Planning". This is from tech.marksblog.com. And this is actually using a few different tools to do data routing, including pg_routing. Now I mentioned this because actually one of the viewers of Scaling Postgres wanted some information on pg_routing. Well, here's the blog post example where they're actually using this utility to do some routing. So check this blog post out if you want to learn more.

Next piece of content. There was another episode of Postgres FM this week. This one was on "TOAST" or The Oversized Attribute Storage Technique. They discuss what it is, how it works and some general things to be aware of. So you can listen to the episode or look at the YouTube video.

Next piece of content, the PostgreSQL person of the week is Thom Brown. Be sure to check out this blog post if you want to learn more about Thom and his contributions to Postgres.

And the last piece of content we did have another episode of The Rubber Duck Dev Show this past Thursday afternoon. This one was on "Failing In Public". Now really what we were discussing is how senior developers live streaming their coding can help other developers learn more about how they work through issues. So maybe it's not necessarily failing in public, but learning in public and presumably, when you're learning, you're making some mistakes along the way. But if you're interested in that content, we welcome you to check out our show.

episode_image