PostgreSQL Patch of Interest (to me): Transaction Timeout

This is part of an occasional series of blogs about PostgreSQL commitfest patches. Look at the series intro which also lists some ground rules and expectations for what to expect in my thoughts below. 🐘

Patch title and thread:Transaction Timeout
Commitfest at time of review:2023-03
Status at time of review:Ready for Committer

Nikolay Samokhvalov (Postgres.ai, Postgres.tv, Postgres.fm) seems to enjoy pushing for small wins/iterative development on the core product. He and Michael Christofides (pgMustard) have been having a lot of great conversations on the Postgres.fm podcast about features of Postgres, changes they’d love to see, and ways that the overall Postgres development community can grow.

In the last few weeks I’ve seen a few posts from Nikolay about live-coding sessions he’s been hosting with Andrey Borodin (@x4mmmmmm) to prepare PoC patches for PostgreSQL.

Probably because I follow Nikolay and have seen some of these sessions, both of these names caught my attention when scanning the (long) list of patches currently included in the March 2023 Commitfest.

The patch has been reviewed and tested by a few folks and currently sitting in a “Ready for Committer” state, but no committer has yet to assign themselves to the patch.

Why Add Another Timeout?

After Andrey submitted the patch in December 2022, Nikolay was the first to respond as to why he suggested adding another timeout option to the mix. Currently PostgreSQL has several timeout settings available for configuration. In particular, statement_timeout often gets a lot of airtime in the community as a good failsafe setting to prevent runaway queries from running forever.

What Nikolay pointed out is that some workloads could have many very short running statements that run within one longer transaction. If your statement timeout is 30 seconds for instance, it’s possible that a transaction is started and many small statements are run and completed without any meaningful value being returned to the client. In web applications, this can mean that users have long abandoned the web page, but because the statements are completed under the statement_timeout setting, PostgreSQL continues to do the work.

Getting the combination of timeouts correct for the expected outcome might be a little tricky, but I like that the discussion and opportunity to submit a patch like this points, yet again, to the open nature of Postgres.

How Much Refactoring is Required?

So far, this patch has only existed since December 2022. Assuming a committer picks it up, there’s a chance this could be pretty short lived from concept to completion. Still, in those two months there has already been a number of comments in the thread from other contributors around what an implementation like this adds to the complexity of processing a single statement.

If you begin to follow the Commitfest threads more, this is a common discussion point. Obviously, it makes sense in any development endeavor to simplify function abstractions and be as DRY as possible. I’m sure many of us have done countless times in design meetings and code reviews.

What often catches my eye, however, within the PostgreSQL context is the deep understanding of how and why a particular function is coded the way it is. I’m not even surprised anymore when someone points back to mailing list or commit messages from a decade or more ago as further context. Having this kind of information about the code, potential improvements, previous attempts at simplifying the code, and more, is an amazing thing to watch.

Yes, it can slow things down because folks often have to rehash the previous discussion and see if anything has changed through the normal course of development, but the end result is typically a better path for long-term maintenance.

Follow Along with Live Coding Examples

If these kind of small patches interesting you, I’d encourage you to follow Nikolay and Postgres.tv. In the ~8 weeks since this patch was submitted, Andrey has been coding other small patches to improve things like UUID formats, buffer pool managements, detailed buffer statistics, and more.

1 thought on “PostgreSQL Patch of Interest (to me): Transaction Timeout”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.