How to interpret "transactions per second"?

Question

Short version: When I execute one transaction in IntelliJ, the "transactions per second" graph increases by 10 tx/sec - I only expect it to increase by 1.
So, it seems that my understanding of the "transactions per second" graph in PgAdmin is wrong or that PgAdmin is doing something unexpected.

Here is a detailed description of my test and my expectations:

I start PgAdmin, then wait a while to see how many "transactions per second" we have when only PgAdmin is connected (up to max. 3), and then execute a simple test-query 4 times.

This is the test-query which I run from IntelliJ. Note, that the transaction mode is set to "Auto", so I expect 4 transactions (since I execute this query 4 times)

This is what I can see in PgAdmin:

Details:

1. (in the Server Sessions chart) is when I start to execute the test-query. We can see that a new server session is created. This is expected, since IntelliJ will connect to the database.
2. (in the "Transactions per second" chart) is the time when I execute my test-query (4 times)

• before I start, we can see that about 3 transactions per second happen sometimes (since only PgAdmin is connected, I guess these are the queries to show the chart-data)
• but when I execute my simple test-query the transactions per second spike up to 14/15 transactions per second: Why is this so high? I'd only expect 1 transaction more than before: so max. 4 in total

Another thing that seems strange, is that the tx-per seconds spikes to 10 when I disconnect IntelliJ from the database:

Any idea why this is happening?

• Does IntelliJ maybe keep some transactions open while it is connected?
• I can also see this happen when other apps disconnect. Some even have extreme spikes up to 1000 tx/sec.
• maybe it is related to the database? I use postgres-13 with timescale-db 2.7

Answer 1

IntelliJ and related tools do make a number of queries when you run one query in the console.

For example, here is my MySQL query log when I run SELECT * FROM USERS; in the PyCharm (same framework as IntelliJ) console:

SELECT @@session.transaction_isolation
/* ApplicationName=PyCharm 2022.2.4 */ set session transaction read write
SELECT @@session.transaction_read_only
SHOW WARNINGS
/* ApplicationName=PyCharm 2022.2.4 */ SET SQL_SELECT_LIMIT=DEFAULT
/* ApplicationName=PyCharm 2022.2.4 */ select database()
SHOW WARNINGS
SHOW WARNINGS
/* ApplicationName=PyCharm 2022.2.4 */ SET net_write_timeout=600
/* ApplicationName=PyCharm 2022.2.4 */ SET SQL_SELECT_LIMIT=501
/* ApplicationName=PyCharm 2022.2.4 */ SELECT * FROM users
SHOW WARNINGS
SELECT @@session.transaction_isolation

That's consistently 13 queries for a single console query. Mostly these are sanity checks and configuration – making sure that PyCharm/IntelliJ has write access to the database, that the selected database is correct, that the number of rows returned is what the IDE expects, and apparently a lot of requests to show warnings.

I don't have data for a PostgreSQL instance, but I expect you are facing a similar set of queries as the same checks would be needed for Postgre. It makes sense that other apps would do the same.

However, I do not see any queries on disconnect.

Answer 2

Adding to the other answer that there will be housekeeping transactions.

To me, transactions per second is not the same as number of transactions. Is not like saying that the speed is the same as distance travelled.