[00:00:00] In this episode of Scaling Postgres, we talk about observer effect, partition management, tuple freezing, and hung transactions. I'm Kristen Jameson, and this is scaling postgres episode 116.
[00:00:22] All right, I hope you, your family and coworkers continue to do well in these unusual times. Our first piece of content is Explain Analyze may be lying to you. And this is from Angres.com. And they're talking about something that in physics is called the Observer Effect, meaning that the process of observing something may actually alter the actual outcome you're trying to observe. And specifically, they're talking about explain, analyze. And they go over an example here where they use Generate series to populate two different tables. And they're explain analyzing the sum from these two tables and using psql timing or PG stats statements to look at the queries and comparing them to what Explain Analyze is reporting. After 20 runs using Explain Analyze, they notice significantly slower runtimes compared to just running the query without Explain Analyze. And as a reminder, Explain Analyze runs the query and shows you the execution plan for it. So for this particular query, that's a 50% overhead for running it through Explain Analyze. And then just as a further example, he looked at a virtual instance running on a non nitro EC two instance. And that's important for a reason. I'll come to in a second and you see a huge 700% overhead for this query on that type of instance. And a lot of this has to do with the clocks and how they are being used. And he's talking about the Zen virtualized clocks on VMs compared to KVM. So it depends on clocks because when running Explain Analyze, it checks the time. And sometimes it has to do this with every row that is processing. And it's because that he mentions here postgres, like other OLTP databases, follows a query execution model named the Volcano model, or basically a one row at a time model. And a row goes through each node of the query tree. So any delays in getting the clock time are going to amplify the impact of Explain Analyze. And he says of course the problem with this is that maybe you think that you've optimized the query, but really you haven't because maybe you were focusing on one particular section, but that particular section is significantly impacted by this overhead we're seeing here. And he makes this comment right here. The overhead Explain Analyze introduces is not proportional to the real duration of the query plan, but rather proportional to the number of rows processed by the node. So he says here there's no great way to get around this, but he does have some proposals on something that may make it better for the future. And he says really you want to think of Explain Analyze as a query execution profiler, and like any profiler, you're going to impact the performance of what you're profiling. But towards the future, he would be interested in seeing perhaps some correction mechanism developed where Izzy says here, quote if the clock time can be measured precisely and the number of times the clock is called is known, postgres could certainly keep track of it. Its contribution could be subtracted from the total measure time and while this wouldn't be exact, it would get a little bit closer. So this is just something to keep in mind when you're using Explain Analyze to profile your queries and analyze what's slow because as he says, Explain Analyze may be lying to you.
[00:03:49] The next post is partition management. Do you really need a tool for that? And this is from Cybertechyphen postgresql.com. And the post generally talks about usually when someone wants to partition, something looks for a tool that helps them do the partitioning, whereas really it's pretty easy to set up partitions. So there are tools that can do it, but just to do it in PostgreSQL they just have a basic table and created some indexes. An interesting thing I hadn't seen anyone do here, he actually created a separate schema for these subpartitions so it doesn't pollute your primary schema. So that's a pretty interesting proposal. And in terms of managing the partitions and when he says managing partitions, basically say every month or every year, you need to create a new partition for the new data coming in, or maybe you want to purge old ones. That's a bit of management that needs to be done on the tables. And this is an example of a CTE that could do this process for you. And here's one for dropping old partitions. Now personally, how I set it, I don't use a tool, but I use my application frameworks language like Ruby to be able to do SQL calls to do the table management, and I have it running in a cron job and it works just fine. I don't need a separate tool for it. So I definitely agree with him that you don't need separate tools to do it and use whatever language you're comfortable with. Here he's using SQL to do it. You could do PL SQL to do it and put them behind functions, but I agree you definitely don't need a tool to do partitioning, although it may make some cases easier to manage. So if you're interested in learning more about doing this without using the tool, check out this blog post.
[00:05:30] The next post, also from CyberTech postgresql.com is Sqltricery Configuring Windowing Functions. And this is a pretty basic post, but it goes into some different techniques of using window functions to understand what each of the parts mean. So I always read through any windowing function posts because I always like to keep up on windowing functions because I actually don't use them that frequently. So this is a brief post about windowing functions. So if you want to learn more about that, definitely check out this post.
[00:06:01] The next piece of content is actually a webinar and it's called Webinar Tuple Freezing and Transaction Wraparound through Pictures follow up. And this is from secondquadrant.com. So this is a webinar. You just click the link here, put in your contact information register and you can look at the webinar. It's about an hour in length and it goes over specifically Tuple Freezing in association with Vacuum and handling or avoiding transaction wraparound. And they do have a number of videos that were produced as well as pictures that describe how this works in postgres. So if you want to get more education about that particular part of postgres, definitely check out this webinar.
[00:06:40] The next piece of content is Don't Leave Me Hanging, another type of transaction to monitor. And this is from Richard
[email protected] and he's talking about transactions that can get hung up are of course the idle in transaction states and there's actually an idle in transaction session timeout you can set to hopefully avoid too many of these. However, something you also need to be aware of is prepared transactions. So this is essentially a two phase commit. Now, I'll say first that most application developers should not be using this feature. It's a very, very specific feature when you're having multiple databases and you want to do a cross server commit of a piece of data. So this should be a pretty rare use case. But if you are using them, you do need to be aware of having orphan transactions that could be hanging around, that could cause problems with your database, prevent vacuum from running, causing your transaction IDs to run out, basically a lot of issues. So if you use this feature or are considering it, definitely check out this blog post.
[00:07:44] The next post is a multi node elastic petabyte scale time series database on postgres for free and more ways we are investing in our community. This is from the Timescale.com blog and basically their multi node timescale DB is being released for free. So you get all of those features being able to run timescale DB, a timescale extension for postgres running across multiple nodes and it will be available for free. Of course, you can also get it on a cloud platform, of course, but it looks like the license ward. It would be an open source variant of sorts. Now, this post is more of a high level marketing perspective of communicating it and the benefits, clients that are using it, et cetera. But if you're interested in timescale DB, maybe you want to check out this particular post on them to see what they're working on.
[00:08:38] The next post wall LSN and file names. This is from Luca Ferrari at fluca, 1978. GitHub IO he's talking about? In the wall files there are log sequence numbers that identify what's being recorded in the wall and that if you know the LSN or the log sequence number, you can actually identify the file that it refers to. Now you can use the command BG current wall LSN to get what the current sequence number is, as well as the file it refers to. But he goes over the process of explaining how you can break down this LSN to be able to identify the file. Like the first part is the timeline, the middle part refers to this first character, and then the last part refers to these two characters. And then this is the Identifier within the file. So if you want to learn more about LSNS and how they work with wall files, check out this blog post from Luca Ferrari.
[00:09:36] The next post, also from Luca Ferrari, is inspecting command tags and events in event triggers. So apparently event triggers are something that you can set up that will enable you to know if certain objects are created or deleted in your database. So he made a trigger that basically logs output. So for example, when you do a create a table, it prints out hey, we created the table here, or if you alter a table and add a column to it, it fires the trigger as well, or if you're creating an index or if you're renaming a table. So this could be potentially useful for auditing. So if you're interested in checking out the Pgevent trigger DDL commands, definitely check out this blog post.
[00:10:18] The next post is upgrading postgres and lessons learned. And this is from the mode engineering blog on Medium. And they're talking about how they wanted to upgrade from postgres 9.4 to ten or higher, ultimately 11.5, and the process they went to. Now originally they wanted to use Bucardo to do it because they wanted to try to avoid downtime, basically do a logical upgrade of sorts. They couldn't use logical replication because that's not available in 9.4, but they wanted to do it in the same kind of fashion. And how Picardo does it is through triggers on different tables to pass over the data. But they had problems with locking scenarios with their application and ultimately they had to abort from using that. What they ended up doing is actually taking several hours of downtime to do the upgrade that's offered by AWS RDS because they have their database in RDS. Now what's interesting, and they mentioned they had talked to some postgres consultants that they didn't mention using PG upgrade because usually with PG upgrade you have on the order minutes of downtime as opposed to several hours. But usually using PG upgrade with Hardlinks is the route I like to go when doing a postgres upgrade to minimize the amount of downtime. But if you want to learn more about some of their experience and the issues they encountered, definitely check out this blog post.
[00:11:48] The next piece of content is partitioning with PostgreSQL version eleven. This is a series of two blog posts that talks about partitioning, what it is, how it works, the different types that are available in eleven, and the second one talks about doing partitions of partitions. So a multi tier partitioning scheme as well as attaching and detaching partitions. So if you want to learn more about partitioning, check out these two posts on Medium.
[00:12:16] The next piece of content is the PostgreSQL person of the Week is Paul Ramsey. So if you want to learn more about Paul and his contributions and work with postgres, definitely check out this blog post.
[00:12:28] The next post is spatial Constraints with Postgres part One this is from Crunchydata.com. They're talking about using constraints. These are, say, check constraints or not null constraints or unique constraints as it applies to PostGIS and the data that it stores. So if you want to learn more about that, check out this post.
[00:12:49] The next article is MongoDB Logical Decoding Plugin first community release on GitHub. So if you're interested in using logical decoding with MongoDB, check out this post from and the last post is actually an announcement of a series of releases for a new connection pooler called PG. A-G-R-O-A-L. I'm not sure how to exactly pronounce that, but it looks to be supported by Red Hat because this is coming from Red Hat Incorporated and its focus is on high performance. So they're trying to make it a better performing connection pooler than others you've heard of, like PG Pool, PG Bouncer, et cetera. So if you're looking to get more performance, maybe you want to check out this new connection pooler for Postgres.
[00:13:38] 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, or you could subscribe via YouTube or itunes. Thanks.
