@knz that isn’t actually a CTE, just pg-specific storage options passed at table creation.
@mingbao pgbench
does an init step when you pass -i
where it creates tables before it runs the load generation step to measure performance. Unfortunately, the table creation step uses postgres-specific storage options. If you create the schema yourself, you can run off-the-shelf pgbench
without -i
and it should work. Indeed, we’ve done this ourselves, and have the edited schema here: https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/pgbench/setup.go#L25
Once you have the tables though, you need some rows in them for it to run against, which usually are also created by -i
so we made a little program to create the tables and fill them the same way pgbench -i
would but without the pg options. If you have a go toolchain, you can
go install github.com/cockroachdb/cockroach/pkg/sql/pgbench/cmd/pgbenchsetup
and then run pgbenchsetup postgres://<your db>
to set it up for you. If not though, you can just paste that schema above and then insert the rows by hand into the 4 tables.
Either way, once you have the tables setup, you’ll want to run pgbench
without the -i
option and with the -n
option, which disables sending vacuum commands, which are another postgres-speciifc storage optimization and aren’t recognized by cockroach.