pgbench: INSERT workload, FK indexes, filler fix

Attached is a combined diff for a set of related patches to the built-in
pgbench workloads. One commit adds an INSERT workload. One fixes the long
standing 0 length filler issue. A new --extra-indexes option adds the
indexes needed for lookups added by the --foreign-keys option.

The commits are independent but overlap in goals. I'm grouping them here
mainly to consolidate this message, covering the feedback leading to this
particular combination plus a first review from me. More graphs etc.
coming as my pgbench toolchain settles down again.

Code all by David Christensen based on vague specs from me, errors probably
mine, changes are also at
https://github.com/pgguru/postgres/commits/pgbench-improvements David ran
through the pgbench TAP regression tests and we're thinking about how to
add more for changes like this. Long term that collides with performance
testing for things like CREATE INDEX, which I've done some work on myself
recently in pgbench-tools.

After bouncing the possibilities around a little, David and I thought this
specific set of changes might be the right amount of change for one PG
version. Core development could bite on all these pgbench changes or even
more [foreshadowing] as part of a themed rework of pgbench's workload
that's known to adjust results a bit, so beware direct comparisons to old
versions. That's what I'd prefer to do, a break it all at once strategy
for these items and whatever else we can dig up this cycle. I'll do my
usual thing to help with that, starting with more benchmark graphs of this
patch and such once my pgbench toolchain settles again.

To me pgbench should continue to demonstrate good PostgreSQL client
behavior, and all this is just modernizing polish. Row size and indexing
matter of course, but none of these changes really alter the fundamentals
of pgbench results. With modern hardware acceleration, the performance
drag due to the increased size of the filler is so much further down in the
benchmark noise from where I started at with PG. The $750 USD AMD retail
chip in my basement lab pushes 1M TPS of prepared SELECT statements over
sockets. Plus or minus 84 bytes per row in a benchmark database doesn't
worry me so much anymore. Seems down there with JSON overhead as a lost
micro optimization fight nowadays.

# Background: pgbench vs. sysbench

This whole rework idea came from a performance review pass where I compared
pgbench and sysbench again, as both have evolved a good bit since my last
comparison. All of the software defined storage testing brewing right now
is shining a brighter light on both tools lately than I've seen in a while.

The goal I worked on a bit (with Joe Conway and RedHat, thank you to our
sponsors) was how to make both tools closer to equal when performing
similar tasks. pgbench can duplicate the basics of the sysbench OLTP
workload easily enough, running custom pgbench scripts against the
generated pgbench_accounts and/or the initially empty pgbench_history. Joe
and I did some work on sysbench to improve its error handling to where it
reconnected automatically as part of that. How to add a reconnection
feature to pgbench is a struggle because of where it fits between PG's
typical connection and connection pooler abstractions; different story than
this one. sysbench had the basics and just needed some error handling bug
fixes, which might even have made their way upstream. These three patches
are the changes I thought core PG could use in parallel, as a mix of
correctness, new features, and fair play in benchmarking.

# INSERT workload

The easiest way to measure the basic COMMIT overhead of network storage is
by doing an INSERT into an empty database and seeing the latency. I've
been doing that regularly since 9.1 added sync rep and that was the easiest
way to test client scaling. From my perspective as an old CRUD app writer,
creating a row is the main interesting operation that's not already
available in pgbench. (No one has a DELETE heavy workload for very long)

Some chunk of pgbench users are trying to do that job now using the
built-ins, and none of the options fit well. Anything that touches the
accounts table becomes heavily wrapped into the checkpoint cycle, and
extracting signal from checkpoint noise is so hard dudes charge for books
about it. In this context I trust INSERT results more than I do the output
from pg_test_fsync, which is too low level for me to recommend as a
general-purpose tool.

For better or worse pgbench is a primary tool in that role to PG customers,
and the INSERT scaling looks great all over. I've attached an early sample
comparing 5 models of SSD to show it; what looks like a PG14 regression
there is a testing artifact I'm working on.

The INSERT workload is useful with or without the history indexes, which
again as written here only are created if you ask for the FKs. When I do
these performance studies of INSERT scaling as a new history table builds,
for really no good reason other than my curiosity, the slowdowns from
whether the pgbench_history has keys on it seem like basic primary key
overhead to me.

# FK indexes

The new extra index set always appears if you turn on FKs after this
change. Then there's also the original path to turn on the indexes but not
the FKs.

As I don't consider the use case of FKs without indexes to exist in the
wild, I was surprised at the current state of things, that you could even
have FKs but not the associated indexes. I have not RTFA for it but I'd
wager it's been brought up before. In that case, +1 from me and David for
this patch's view of database correctness I guess.

On a fresh pgbench database, the history table is empty and only the
accounts table has serious size to it. Adding indexes to the other tables,
like this patch does, has light overhead during the creation cycle.

My take on INSERT/UPDATE workloads that once you're hitting disk and have
WAL changes, whether one or three index blocks are touched each time on the
small tables is so much more of a checkpoint problem than anything else.
The overhead these new indexes add should be in the noise of the standard
pgbench "TPC-B (sort of)" workload.

The index overhead only gets substantial once you've run pgbench long
enough that history has some size to it. The tiny slice of people using
pgbench for long-term simulation--which might only be me--are surely
sophisticated enough to deal with index overhead increasing from zero to
normal primary key index overhead.

I personally would prefer to see pgbench lead by example here, that tables
related this way should be indexed with FKs by default, as the Right Way to
do such things. There's a slow deprecation plan leading that way possible
from here. This patch set adds options to add those indexes, and slowly
those options could become the defaults. Or there's the break it all at
once and the FK+Index path is the new default path forward, and users would
have to turn it off if they want to reduce overhead.

# filler

Every few years a customer I deal with discovers pgbench's generated tables
don't really fill its filler column. I think on modern hardware it's time
to pay for that fully, as not as scary of a performance regression.
memcpy() is AVX accelerated for me on Linux now; it's not the old C
standard library doing the block work. When I field detailed questions
about the filler, why it's length is 0, how the problem was introduced, and
why it was never fixed before, it's not the best look.

From port 5432 you can identify if a patched pgbench client created the
database like this:

pgbench# SELECT length(filler) FROM pgbench_accounts LIMIT 1;
length | 84

That is 0 in HEAD. I'd really prefer not to have to pause and explain this
filler thing again. It looks a little too much like benchmark mischief for
my comfort, which the whole sysbench comparison really highlighted again.