From June 14-15, EDB hosted our annual Postgres Vision event, bringing together luminaries from both within and outside the organization to discuss the challenges, opportunities and experiences of Postgres users over the last year.
In addition to a range of keynote speakers—including EDB’s CEO Ed Boyajian—discussing the future of Postgres, some of the most watched and enjoyed sessions dove into the technical questions that pop up for businesses just getting started with this transformative database. One of the most informative of these came courtesy of EDB Solutions Architect Boriss Mejias. In his presentation entitled “Idle in Transaction,” he discussed what it means to have multiple idle sessions in Postgres and, more specifically, what it means when a session is “idle in transaction.”
For those who could not attend the event (or those who just want to revisit some of their favorite moments), here is a recap of Mejias’ presentation. You can also rewatch all of the sessions in full, here!
Idle sessions slow your Postgres
Unique among the sessions at this year’s Postgres Visions conference, Boriss Mejias’ presentation takes the form of a story—one that many enterprises are probably familiar with. Faced with a slowing—and ultimately stalling—database connection, developers receive an error message alerting them that there are too many clients accessing the system at once.
When they go to investigate they find that of the 200 clients their Postgres database has been built to handle, a whopping 198 of them are idle, simply sitting there gumming up the works, despite not being in use. As anyone might do in this situation, the IT team decides to see which of those idle sessions have been running the longest and shut them down.
As Mejias explains, this can be a frequent issue for organizations because it’s easy to lose track of how many idle sessions you have running, until their impact becomes apparent. Luckily, he demonstrates how IT managers can create a script that automatically terminates a chosen percentage number of idle sessions once they reach a given percent of sessions overall.
But the story doesn’t end there!
Idle vs. idle in transaction
Next, Mejias recounts a team coming across a table that is continuing to impact database performance. Despite the DBA team using VACUUM, they can’t return the unused space to the operating system, much to their frustration. When they decide to analyze the state of all the sessions within the table they find a similar issue to the one they had previously, with one notable exception.
Clearing away the idle sessions is simple enough, but the problem here ultimately lies with that single session labeled as “idle in transaction.”
So, what’s the difference? Why is a single idle in transaction session impacting performance more than 166 idle sessions?
To use Mejias’ metaphor, an idle session is like a patron who has fallen asleep at the bar—they’re taking up space, but not really bothering anyone. Meanwhile, an idle in transaction session is like the patron who calls the bartender over and keeps him waiting while the patron scrutinizes the menu. It’s using up both space and computing power because, while it’s not actively doing anything, the database has to treat it like it is. Other processes are, therefore, waiting in line behind it.
The impact of idle in transaction
Idle in transaction sessions, even in very small numbers, can bring a once hyper-performant Postgres database to a crawl. As Mejias details, this makes it extremely important for DBAs and developers to carefully monitor how they start and close their queries. Even small errors here can lead to idle in transaction sessions which will ultimately impact every other team member and application dependent on your database.
The answer to idle in transaction
While we can’t detail all of the solutions that Mejias provides in his presentation, we encourage you to watch it in full to discover how to prevent the problem of idle in transaction sessions and keep your Postgres running at its full capacity. While you’re there, check out some of the other exciting discussions from Postgres Vision 2022, all of which are tailored to help you experience the true power of Postgres!