Using Transaction Chaining to Reduce Server Round-Trips

by Christoph Schiessl on PostgreSQL

Implementing business applications with PostgreSQL, or really any relational database, often amounts to executing sequences of transactions. It doesn't matter if a given transaction ends with a COMMIT or ROLLBACK because, in both cases, the next transaction starts as soon as the previous one has finished. Furthermore, for most applications, the previous and the next transactions share similar characteristics, such as their ISOLATION LEVEL. Essentially, what you end up with is a pattern that looks as follows:

START TRANSACTION;
-- workload of 1st transaction
COMMIT;
START TRANSACTION;
-- workload of 2nd transaction
ROLLBACK;
START TRANSACTION;
-- workload of 3rd transaction
COMMIT;

In the SQL script above, you see three subsequent transactions and a total of six statements (two per transaction), but you can imagine much longer transaction sequences. Here is the thing: Each statement requires a separate server round-trip to execute. There is no way around this, and even the fact that three example transactions are empty — don't contain any statements — doesn't make a difference.

Monitoring Connection States

Rather than take my word for it, we can quite easily prove that the server round-trips are actually happening. To make this experiment, we need two separate connections to the same PostgreSQL server (e.g., two psql sessions). The first connection uses the postgres database, and the second uses the demo database.

Alright, now we can use pg_stat_activity, which is a built-in VIEW that belongs to the pg_catalog schema, on the first connection to ask PostgreSQL about the state of the second connection that is using the demo database:

postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
 state
-------
 idle
(1 row)

As you can see, the connection is currently idle because it's not doing anything. However, if we start a transaction on the second connection, we can see that the connection's state immediately changes to idle in transaction.

demo=# START TRANSACTION;
START TRANSACTION
postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
        state
---------------------
 idle in transaction
(1 row)

This proves that the START TRANSACTION statement caused a round-trip with the server because if it had not, the first connection would not have been able to see a state change of the second connection. The story is the same if we finish the transaction with a COMMIT or ROLLBACK, in which case the connection immediately reverts to the idle state:

demo=*# COMMIT;
COMMIT
postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
 state
-------
 idle
(1 row)

Overhead from Round-Trips

So, long story short, the starting and finishing of transactions can pose a significant overhead. Specifically, if any of the following conditions are met:

  1. The round-trip time to the server is slow. Usually, this is true if the distance in terms of networking between client and server is great.
  2. There are many transactions with short average runtimes. That is because the overhead as a percentage of the total runtime is higher for shorter transactions.

So, what can we do to reduce the overhead and improve performance?

Transaction Chaining

The SQL standard has a built-in solution that is implemented by PostgreSQL: the AND CHAIN parameter. This parameter is available for the COMMIT and the ROLLBACK statements and has the following effect ...

If the AND CHAIN parameter is provided, then the current transaction is committed (or rolled back), and additionally, a follow-up transaction with the same characteristics (e.g., ISOLATION LEVEL) is started immediately.

Therefore, if we apply this to the original example, we can reduce the number of server round-trips by basically 50% (from n to n/2+1).

START TRANSACTION;
-- workload of 1st transaction
COMMIT AND CHAIN;
-- workload of 2nd transaction
ROLLBACK AND CHAIN;
-- workload of 3rd transaction
COMMIT;

We can run the same experiment to prove that this works as expected. I'm again using two connections, one using the postgres database and the other using the demo database. Initially, the demo connection was idle, but as soon as we started a new transaction, its state changed to idle in transaction.

demo=# START TRANSACTION;
START TRANSACTION
postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
        state
---------------------
 idle in transaction
(1 row)

So, what happens if the demo connection executes a COMMIT AND CHAIN statement? As I said, PostgreSQL immediately starts a follow-up transaction, so we don't see a state change.

demo=# COMMIT AND CHAIN;
COMMIT AND CHAIN
postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
        state
---------------------
 idle in transaction
(1 row)

When we do ROLLBACK AND CHAIN, we get precisely the same behavior — no apparent state change.

demo=# ROLLBACK AND CHAIN;
ROLLBACK AND CHAIN
postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
        state
---------------------
 idle in transaction
(1 row)

Finally, when we issue a normal COMMIT or ROLLBACK, the state changes back to the initial idle state.

demo=# COMMIT;
COMMIT
postgres=# SELECT state FROM pg_stat_activity WHERE datname='demo';
 state
-------
 idle
(1 row)

I hope you found this article interesting and learned something new. Thank you very much for reading, and see you soon!

Christoph Schiessl

Hi, I'm Christoph Schiessl.

I help you build robust and fast Web Applications.


I'm available for hire as a freelance web developer, so you can take advantage of my more than a decade of experience working on many projects across several industries. Most of my clients are building web-based SaaS applications in a B2B context and depend on my expertise in various capacities.

More often than not, my involvement includes hands-on development work using technologies like Python, JavaScript, and PostgreSQL. Furthermore, if you already have an established team, I can support you as a technical product manager with a passion for simplifying complex processes. Lastly, I'm an avid writer and educator who takes pride in breaking technical concepts down into the simplest possible terms.

Continue Reading?

Here are a few more Articles for you ...


Why JavaScript’s undefined Isn’t What You Think It Is

In this informative article, you learn that undefined is not a keyword in JavaScript, and it's up to you to ensure it refers to the value its name suggests.

By Christoph Schiessl on JavaScript

Exploring Orphaned Branches to Understand Git's Internals

Learn about Git's internal data structure and how orphaned branches can be used to create separate histories with their own root commits.

By Christoph Schiessl on DevOps and Git

Caching Expensive Queries with MATERIALIZED VIEWs

Learn how to use PostgreSQL's MATERIALIZED VIEWs to improve performance of complex queries. Persist query results and refresh them manually or automatically.

By Christoph Schiessl on PostgreSQL

Web App Reverse Checklist

Ready to Build Your Next Web App?

Get my Web App Reverse Checklist first ...


Software Engineering is often driven by fashion, but swimming with the current is rarely the best choice. In addition to knowing what to do, it's equally important to know what not to do. And this is precisely what my free Web App Reverse Checklist will help you with.

Subscribe below to get your free copy of my Reverse Checklist delivered to your inbox. Afterward, you can expect one weekly email on building resilient Web Applications using Python, JavaScript, and PostgreSQL.

By the way, it goes without saying that I'm not sharing your email address with anyone, and you're free to unsubscribe at any time. No spam. No commitments. No questions asked.