Episode Transcript
[00:00:00] I have to admit the number of tables in the database for my application is growing a little bit faster than I would like.
[00:00:08] Why is that? Well, because I'm using partition tables for different action tracking, but unfortunately if you get too many tables you can run into a problem.
[00:00:20] And we'll talk about that this week, but I hope you, your friends, family and co workers continue to do well.
[00:00:27] Our first piece of content is Too many tables are bad for you. This is from cyberdeck PostgreSQL and he's talking about a situation that a customer was having that they were working with where they had two problems. One, they were hitting a Linux out of memory killer issue that was crashing Postgres. And second, they had long running queries that hog the CPU and delayed the application.
[00:00:52] So what they did to adjust the out of memory killer was disable memory over commit. And he also thought it might have been issues of other software running on the machine, but that really wasn't the case. And he says usually when you have a high memory usage in Postgres, it's because you set your shared buffers too high. Or maybe you have too many connections and you set your work memory too high, or maybe you're using too much memory for parallel workers, or you have large binary data or JSON values or even geometries that you're working with. But none of that was the cause. But they started looking at memory context dumps and they actually found this entry for cache memory context was really high, almost half a gigabyte. And this is permanent storage for the REL cache, CAT cache and related modules. And the CAT cache is metadata tables or the catalog entries. And the REL cache is caches of the descriptions of the relations.
[00:01:48] So it's basically in how many tables and other objects you have in the database. And this is per backend.
[00:01:55] So essentially each back end process was accruing almost half a gigabyte of RAM in the cache memory context. And how did they do that? Well, they had tens of thousands of tables. Now I said, my database is growing. I don't have anywhere near 10,000 tables, but still a little bit more than I would like. Anyway, with tens of thousands of tables and long lived database connections touching more and more of them, the cache grew larger and larger and that's what was actually bringing down the memory. And he shows an example here of simulating 20,000 tables with 20 columns each.
[00:02:33] He looked at the size and it was around four kilobytes. But when he started doing queries of each of them the cache memory context grew from 4 kilobytes to 221 megabytes. So if you have a problem of too many tables, he suggests, well, try to reduce your connection count and use the connection pooler instead if you need a lot of connections to the database. Or maybe try setting idle session timeouts so if nothing's going on with those back ends, they can be reaped, which basically frees up some of the memory. But so many tables was also causing long running statements and they were running a statement that looked at a metadata view of PostGIS data. So that's another issue you can potentially deal with. But if you want to learn more about this, I definitely encourage you to check out this blog post.
[00:03:23] Next piece of content Stop punishing your Postgres for a crash that won't happen this is from mydbanotebook.org so she's talking about checkpoint timeout and we just looked at a blog post last week on scaling Postgres where he emphasized keeping checkpoint timeout at five minutes because of the risks of extending crash recovery of Postgres, whereas she's advocating no, it's fine to set it longer than five minutes and you're actually going to hurt yourself if you try to set it lower. Because she says some people she knows have set it to one minute and then wonder why their postcards is struggling because they're asking it to checkpoint way too frequently. Now the reason why extending greater than five minutes is not an issue is because first, serious people have a replica. So if you do have some sort of a crash, you're going to tend to fail over to that replica as opposed to wait for the primary to come up if it happens to take an extended amount of time. And second, the math doesn't work the way you think. In other words, how these checkpoint timeouts are scheduled is over time. They look at the checkpoint completely completion target, which defaults to nine, so it takes its time to do the checkpoint because checkpoints are a load on the system, so trying to extend them all the way out through the time keeps the database running performantly. So when a database is restarting and it's doing recovery, typically it's much faster than the whole duration of the checkpoint timeout. It's a fraction of that time, which is exactly what she says here. It finishes in a fraction of that time, not five minutes, much less. It could be on the order of seconds, maybe a minute. It depends on how much wall that it has to replay. Third, a short timeout actually makes things Worse because you're checkpointing more frequently and every new write requires a full page write to the wall. So you're actually increasing the amount of wall that you have to replay at the same time with frequent checkpoints. But of course she definitely emphasizes, you know, turn on your log checkpoints and she advocates using the logging collector. Basically understand where checkpoints are happening and how frequently they are. Now she says you want checkpoints to be triggered by Maxwell size and not by checkpoint timeout. And I don't know if I necessarily agree with this. I think they both go hand in hand. Basically add a checkpoint warning as well so you know if checkpoints are happening too soon and if they are, increase your max wall size. But I think choosing a checkpoint timeout range and seeing how long checkpoints are happening can give you a good sense of the state of the system. And she says, quote, a value between 15 and 30 minutes is reasonable for most workloads, which I agree.
[00:06:13] But if you want to learn more, definitely check this out. Next piece of content Disaster Recovery is a Process, Not a Tool Part 2 this is from richyan.com and this is a follow up to a post that we discussed a couple of weeks ago on scaling Postgres and this one he talks about why runbooks can fail again, emphasizing that you need a process and how runbooks fail is they typically are drafted by experts and then at the time when there's an actual emergency those experts may not be available and the stressed staff at 3am have to complete the steps within there. Andy lists a few anti patterns for runbooks, but I'm actually going to read this as recommendations.
[00:06:57] So don't just have giant wiki pages in the runbook. Make sure your commands and your host names are up to date. Make sure you have rollback criteria for each statement that happens to run. Like if this doesn't work then what's the rollback process for that make sure you define clear ownership of who's running the process and make sure the instructions are not vague. He also mentions the importance of communication with customers when an issue like this is going on, as well as what he calls an incident commander. So the person in charge of bringing the system back up and working with the engineers on staff to do that. Basically the leader and he talks about some of experience working at EDB Support as well as Microsoft.
[00:07:44] And of course when you have a runbook, be sure to validate it. So can new people follow it? Is the runbook current? Can it work today? And he even advocates having game days so Basically, simulations of this event happened. Can we use the runbook? Can we use our process to do a drill to bring a system back online?
[00:08:06] And then he also discussed what to measure during that. So definitely another great piece of content. Go ahead and check it out if you're interested. Next piece of content. Same rows, different sum. This is from boringsql.com and he's talking about that.
[00:08:21] Everyone knows not to store money as a double precision, but unfortunately this type of data floats. Doubles can leak into data and if you end up summing that, you can introduce great variability to the end result.
[00:08:38] The reason is because with floating point arithmetic, the order by which you sum something up gives you a different answer. It's not like your usual mathematics, but this is the result of floating point arithmetic. So if you have a double precision here and you're summing it from a table, you're going to get a different value each time. Now, of course it's close. You can round up to a certain level of precision, but it's still going to give you a different value each time.
[00:09:06] Part of the reason, he says, is because floating point does not associate. So A, B, C is the same as A plus B and C with parens around it. That's fine for typical mathematics, but not for floating point addition.
[00:09:20] It is not associated. So change the order of how things are added, you get different results. And this gets surfaces when you're dealing with parallelism, because that adds things in different orders based on what processes are doing and which process goes first against the data.
[00:09:37] Now, as long as you can ensure that order will be preserved when adding, you can get identical results. Here they turned off the parallel workers and you get identical results. But if you suddenly introduce a different order by for this table now you start getting those different results again. So just something to keep in mind when working with floating point numbers in Postgres and check that out if you want to learn more. Next piece of content when an 18 terabyte backup just stopped. This is from command prompt.com so there was an 18 terabyte database that was being recovered to a point in time, but the wall replay just stopped. Quote, no error, nothing in the logs. Even at debug level 5, there was no IO, no CPU.
[00:10:25] The startup process was sitting there doing nothing at all.
[00:10:28] And they used proc wchan on the startup process and it showed a parked futex wait queue that they interpreted as the database had deadlocked against itself while replaying its own wall. And apparently there was a fix in Postgres that closed a race condition that can happen with a self deadlock when playing wallback. So this is another endorsement to basically keep your Postgres minor version as up to date as possible so you can try to avoid issues such as this. But check this out if you want to learn more. Next piece of content inside a PostgreSQL checkpointer bug a production postmortem in this instance a client was using a Postgres 16.8 production database and they started seeing error invalid memory alloc request size and they said usually you see this with memory exhaustion or corruption. Instead it was a Postgres bug that trapped the check pointer in an infinite retry loop and the only way to recover it is for a forced restart. So basically the process couldn't complete an async request. So we have all these wall files that continue to accumulate. The checkpoint process can't finish so nothing's being checkpointed and it's basically because the fsync request queue exceeded the sync size which is approximately 1 gigabyte. So the only way to resolve this is to do a restart and allow recovery to replay back those wall files to get the system back up and checkpointing occurring again. And also installing a more recent version of Postgres that resolves this issue by adjusting that limit. But if you want to learn more, definitely check out this blog post. Next piece of content introducing PG head health check PostgreSQL health diagnostics this is from pgedge.com and this is an open source utility that runs 180+ checks across 14 groups querying a live PostgreSQL system catalog views to give you insight into the health of your system.
[00:12:33] So they have the GitHub release here and shows you how you can build it and run a full check. It is basically just a utility written in Go that you run against your database and he mentions some of the areas that get covered. He says you can even connect up to an LLM and ask it specific questions if you want. But I'd be interested to see what the output looks like. Maybe that's on GitHub and I'm sure there's more information about it on the GitHub as well. So check that out if you're interested. Next piece of content Closing a Critical gap in PostgreSQL upgrade workflows with sequence synchronization this is from PostgreSQL and they're talking about the enhancement coming to 19 where you can do one time copies or synchronization of sequences and we've covered this in previous episodes of scaling Postgres, so I'm not going to go over it too much. It doesn't keep them always in sync. It's a one time sync you use typically when you're doing a logical replication upgrade. So this blog post works through the process of doing that. Typically they recommend having a physical replication set up with a standby.
[00:13:42] Then you convert it into a logical replication using PG Create Subscriber. Then you run PGUpgrade on this new node to bring it to the next version, say Postgres 19 and then during the transition point where you're moving Your app from 18 to 19, one of the last steps, you generally synchronize those sequences. So so it goes through the process of doing that. It also provides more detail explaining how the new feature works. So if you're interested in that, definitely check this out. Next piece of content PostgreSQL as a temporal database this is from Zada IO and we have talked about this new feature that's coming in 19. Basically update or delete for portion of as well as the temporal keys without overlaps in period that came in 18.
[00:14:30] This blog post shows we're using both of those together to have Postgres run as a temporal database. So definitely check this out if you're interested. And the last piece of content waiting for SQL 22y Stockholm BMA meeting report this is from Peter eisentraut.org and he's covering the meeting report where they're discussing the next version of the SQL standard. So he's talking about all the different capabilities or new commands they're planning to add to the SQL language.
[00:15:00] So if you want to learn more about that, definitely check out this blog post. I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find all the links to the content mentioned, as well as sign up to receive weekly notifications of each episode. There you can also find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
