It was confirmed, that the issue was because of the cleared hint bits. Newest benchmarks look totally fine. I will close here.

Currently, “ads” is running some long-running benchmarks with different DB sizes and clients. Waiting for the result.

So, after further investigation of this pgbench performance drop issue, we believe, that we've found the issue. The issue is caused by ALTER TABLE <tablename> set access method, as this drops the hint bits. As you can read in the upstream documentation, without these queries can cause heavy writes to a database table even though you're just reading from it. To determine the visibility of a tuple without these bits set, you need to consult pg_clog and possibly pg_subtrans, so it is an expensive check.

Further information can be found here with the explanation:

To overcome this after an alter, you can do the following: "A plain SELECT, count(*), or VACUUM on the entire table will check every tuple for visibility and set its hint bits."

So what I did now was testing a manual vacuum on all pgbench tables after the ALTER and before the benchmark run and also a count(*). Obviously the count(*) is way quicker, for testing purposes. In both tests, I was not able to reproduce the issue anymore as the hint bits were set afterward again.

Some results below:

Vacuum on all tables before the pgbench run

tps = 13700.238776 (without initial connection time)
tps = 15705.631591 (without initial connection time)
tps = 15401.540154 (without initial connection time)
tps = 12119.952905 (without initial connection time)
tps = 13572.204126 (without initial connection time)
tps = 15592.978704 (without initial connection time)
tps = 11616.329240 (without initial connection time)
tps = 13548.561917 (without initial connection time)
tps = 13811.609644 (without initial connection time)
tps = 14721.345952 (without initial connection time)

count(*) on all tables before the pgbench run

tps = 13553.283767 (without initial connection time)
tps = 13542.271232 (without initial connection time)
tps = 13759.484216 (without initial connection time)
tps = 15279.172305 (without initial connection time)
tps = 13750.294649 (without initial connection time)
tps = 16313.974084 (without initial connection time)
tps = 11417.014614 (without initial connection time)
tps = 14031.992944 (without initial connection time)
tps = 11565.277732 (without initial connection time)
tps = 12746.276266 (without initial connection time)

My first thought was isn’t this just part of the amazing world of hint bits, but will take a look.

What's the issue?

• Whenever you do an "ALTER TABLE" from one access method to another, and you do run pgbench against this table, the performance might be super slow (like 5-10% of the normal speed)

• Adjusting memory, doing an ANALYZE of the tables or even restarting PostgreSQL service makes no difference, the issue is still persistent.

• If you do run a super short pgbench run against the just newly altered table, the issue is no longer there. A simple “-c 1-t 1” is enough even.

• This issue is reproducible with the latest upstream codebase.

• The easiest way to reproduce is:

Initialize pgbench without any changes to PostgreSQL

After that, create a new access method called heap2 based on heap

ALTER all pgbench tables to use the new access method heap2

Run pgbench

You may need to run pgbench multiple times, as the issue doesn't occur with every run, but this is the most consistent way to trigger it.