Observer Effect, Partition Management, Tuple Freezing, Hung Transactions | Scaling Postgres 116

In this episode of Scaling Postgres, we discuss the observer effect with explain analyze, partition management, tuple freezing and sources of hung transactions.

Content Discussed

• Explain Analyze May Be Lying To You
• Partition management – do you really need a tool for that?
• SQL trickery: Configuring windowing functions
• Webinar: Tuple Freezing & Transaction Wrap around Through Pictures [Follow Up]
• Don't Leave Me Hanging: Another Type of Transaction to Monitor
• A multi-node, elastic, petabyte scale, time-series database on Postgres for free
• WAL, LSN and File Names
• Inspecting Command Tags and Events in event triggers
• Upgrading Postgres & Lessons Learned
• Partitioning with PostgreSQL v11 (1/2)
• Partitioning with Postgres v11 (2/2)
• Paul Ramsey
• Spatial Constraints with PostGIS - Part 1
• MongoDB Logical Decoding Plugin – First Community Release on GitHub
• PGAGROAL

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres, we talk about observer effect, partitionmanagement, tuple freezing, and hung transactions. I'm Kristen Jameson, and this is scalingpostgres episode 116.

[00:00:22] All right, I hope you, your family and coworkers continue to do well in these unusualtimes. Our first piece of content is Explain Analyze may be lying to you. And this is fromAngres.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'retrying to observe. And specifically, they're talking about explain, analyze. And they go over anexample here where they use Generate series to populate two different tables. And they'reexplain analyzing the sum from these two tables and using psql timing or PG stats statements tolook at the queries and comparing them to what Explain Analyze is reporting. After 20 runs usingExplain Analyze, they notice significantly slower runtimes compared to just running the querywithout Explain Analyze. And as a reminder, Explain Analyze runs the query and shows you theexecution plan for it. So for this particular query, that's a 50% overhead for running it throughExplain Analyze. And then just as a further example, he looked at a virtual instance running on anon nitro EC two instance. And that's important for a reason. I'll come to in a second and yousee a huge 700% overhead for this query on that type of instance. And a lot of this has to dowith the clocks and how they are being used. And he's talking about the Zen virtualized clockson VMs compared to KVM. So it depends on clocks because when running Explain Analyze, itchecks the time. And sometimes it has to do this with every row that is processing. And it'sbecause that he mentions here postgres, like other OLTP databases, follows a query executionmodel named the Volcano model, or basically a one row at a time model. And a row goesthrough each node of the query tree. So any delays in getting the clock time are going to amplifythe impact of Explain Analyze. And he says of course the problem with this is that maybe youthink that you've optimized the query, but really you haven't because maybe you were focusingon one particular section, but that particular section is significantly impacted by this overheadwe're seeing here. And he makes this comment right here. The overhead Explain Analyzeintroduces is not proportional to the real duration of the query plan, but rather proportional to thenumber 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 hesays really you want to think of Explain Analyze as a query execution profiler, and like anyprofiler, 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 Izzysays here, quote if the clock time can be measured precisely and the number of times the clockis called is known, postgres could certainly keep track of it. Its contribution could be subtractedfrom the total measure time and while this wouldn't be exact, it would get a little bit closer. Sothis is just something to keep in mind when you're using Explain Analyze to profile your queriesand 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 isfrom Cybertechyphen postgresql.com. And the post generally talks about usually when someonewants to partition, something looks for a tool that helps them do the partitioning, whereas reallyit's pretty easy to set up partitions. So there are tools that can do it, but just to do it inPostgreSQL they just have a basic table and created some indexes. An interesting thing I hadn'tseen anyone do here, he actually created a separate schema for these subpartitions so itdoesn't pollute your primary schema. So that's a pretty interesting proposal. And in terms ofmanaging the partitions and when he says managing partitions, basically say every month orevery year, you need to create a new partition for the new data coming in, or maybe you want topurge old ones. That's a bit of management that needs to be done on the tables. And this is anexample 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 usemy application frameworks language like Ruby to be able to do SQL calls to do the tablemanagement, and I have it running in a cron job and it works just fine. I don't need a separatetool for it. So I definitely agree with him that you don't need separate tools to do it and usewhatever language you're comfortable with. Here he's using SQL to do it. You could do PL SQLto do it and put them behind functions, but I agree you definitely don't need a tool to dopartitioning, although it may make some cases easier to manage. So if you're interested inlearning 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 ConfiguringWindowing Functions. And this is a pretty basic post, but it goes into some different techniquesof using window functions to understand what each of the parts mean. So I always read throughany windowing function posts because I always like to keep up on windowing functions becauseI actually don't use them that frequently. So this is a brief post about windowing functions. So ifyou 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 Freezingand 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 youcan look at the webinar. It's about an hour in length and it goes over specifically Tuple Freezingin association with Vacuum and handling or avoiding transaction wraparound. And they do havea number of videos that were produced as well as pictures that describe how this works inpostgres. So if you want to get more education about that particular part of postgres, definitelycheck out this webinar.

[00:06:40] The next piece of content is Don't Leave Me Hanging, another type of transaction tomonitor. And this is from Richard Yen@richyen.com and he's talking about transactions that canget hung up are of course the idle in transaction states and there's actually an idle in transactionsession timeout you can set to hopefully avoid too many of these. However, something you alsoneed to be aware of is prepared transactions. So this is essentially a two phase commit. Now, I'llsay first that most application developers should not be using this feature. It's a very, veryspecific feature when you're having multiple databases and you want to do a cross servercommit 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 couldcause problems with your database, prevent vacuum from running, causing your transaction IDsto run out, basically a lot of issues. So if you use this feature or are considering it, definitelycheck out this blog post.

[00:07:44] The next post is a multi node elastic petabyte scale time series database on postgresfor free and more ways we are investing in our community. This is from the Timescale.com blogand basically their multi node timescale DB is being released for free. So you get all of thosefeatures being able to run timescale DB, a timescale extension for postgres running acrossmultiple 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 tocheck 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 identifywhat's being recorded in the wall and that if you know the LSN or the log sequence number, youcan actually identify the file that it refers to. Now you can use the command BG current wall LSNto get what the current sequence number is, as well as the file it refers to. But he goes over theprocess of explaining how you can break down this LSN to be able to identify the file. Like thefirst part is the timeline, the mi --ddle part refers to this first character, and then the last part refers to these two characters. Andthen this is the Identifier within the file. So if you want to learn more about LSNS and how theywork 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 inevent triggers. So apparently event triggers are something that you can set up that will enableyou to know if certain objects are created or deleted in your database. So he made a trigger thatbasically logs output. So for example, when you do a create a table, it prints out hey, we createdthe table here, or if you alter a table and add a column to it, it fires the trigger as well, or if you'recreating an index or if you're renaming a table. So this could be potentially useful for auditing. Soif you're interested in checking out the Pgevent trigger DDL commands, definitely check out thisblog post.

[00:10:18] The next post is upgrading postgres and lessons learned. And this is from the modeengineering blog on Medium. And they're talking about how they wanted to upgrade frompostgres 9.4 to ten or higher, ultimately 11.5, and the process they went to. Now originally theywanted to use Bucardo to do it because they wanted to try to avoid downtime, basically do alogical 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 triggerson different tables to pass over the data. But they had problems with locking scenarios with theirapplication and ultimately they had to abort from using that. What they ended up doing isactually taking several hours of downtime to do the upgrade that's offered by AWS RDSbecause they have their database in RDS. Now what's interesting, and they mentioned they hadtalked to some postgres consultants that they didn't mention using PG upgrade because usuallywith PG upgrade you have on the order minutes of downtime as opposed to several hours. Butusually using PG upgrade with Hardlinks is the route I like to go when doing a postgres upgradeto minimize the amount of downtime. But if you want to learn more about some of theirexperience 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 aseries of two blog posts that talks about partitioning, what it is, how it works, the different typesthat are available in eleven, and the second one talks about doing partitions of partitions. So amulti tier partitioning scheme as well as attaching and detaching partitions. So if you want tolearn 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. Soif you want to learn more about Paul and his contributions and work with postgres, definitelycheck out this blog post.

[00:12:28] The next post is spatial Constraints with Postgres part One this is fromCrunchydata.com. They're talking about using constraints. These are, say, check constraints ornot null constraints or unique constraints as it applies to PostGIS and the data that it stores. So ifyou want to learn more about that, check out this post.

[00:12:49] The next article is MongoDB Logical Decoding Plugin first community release onGitHub. So if you're interested in using logical decoding with MongoDB, check out this post fromand the last post is actually an announcement of a series of releases for a new connectionpooler called PG. A-G-R-O-A-L. I'm not sure how to exactly pronounce that, but it looks to besupported by Red Hat because this is coming from Red Hat Incorporated and its focus is on highperformance. So they're trying to make it a better performing connection pooler than othersyou've heard of, like PG Pool, PG Bouncer, et cetera. So if you're looking to get moreperformance, 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 contentmentioned in the show notes. Be sure to head over to Scalingposgres.com, where you can signup to receive weekly notifications of each episode, or you could subscribe via YouTube or itunes.Thanks. --