Sysbench-tpcc Supports PostgreSQL (No, Really This Time)

Sysbench-tpcc Supports PostgreSQL (No, Really This Time)

PREVIOUS POST
NEXT POST

Sysbench-tpcc Supports PostgreSQLThis time, we really mean it when we say sysbench-tpcc supports PostgreSQL.

When I initially announced sysbench-tpcc, I mentioned it potentially could run against PostgreSQL, but it was more like wishful thinking than reality. The reality was that even though both databases speak SQL, the difference in dialects was too big and the queries written for MySQL could not run without modification on PostgreSQL.

Well, we introduced needed changes, and now you can use sysbench-tpcc with PostgreSQL. Just try the latest commit to https://github.com/Percona-Lab/sysbench-tpcc.

If you’re interested, here is a quick overview of what changes we had to make:

  1. It appears that PostgreSQL does not support the  tinyint and datetime data types. We had to use smallint and timestamp fields, even if using smallint makes the database size bigger.
  2. PostgreSQL does not have a simple equivalent for MySQL’s SHOW TABLES. The best replacement we found is select * from pg_catalog.pg_tables where schemaname != 'information_schema' and schemaname != 'pg_catalog'.
  3. PostgreSQL does not have a way to disable Foreign Key checks like MySQL: SET FOREIGN_KEY_CHECKS=0. With PostgreSQL, we needed to create and load tables in a very specific order to avoid Foreign Keys violations.
  4. PostgreSQL requires you to have a unique index name per the whole database, white MySQL requires it only per table. So instead of using:

    We need to use:
  5. PostgreSQL does not have a  STRAIGHT_JOIN hint, so we had to remove this from queries. But it is worth mentioning we use STRAIGHT_JOIN mostly as a hack to force MySQL to use a correct execution plan for one of the queries.
  6. PostgreSQL is very strict on GROUP BY queries. All fields that are not in the GROUP BY clause must use an aggregation function. So PostgreSQL complained on queries like SELECT d_w_id,sum(d_ytd)-w_ytd diff FROM district,warehouse WHERE d_w_id=w_id AND w_id=1 GROUP BY d_w_id even when we know that only single value for w_ytd is possible. We had to rewrite this query as SELECT d_w_id,SUM(d_ytd)-MAX(w_ytd) diff FROM district,warehouse WHERE d_w_id=w_id AND w_id=1 GROUP BY d_w_id.

So you can see there was some work involved when we try to migrate even a simple application from MySQL to PostgreSQL.

Hopefully, now sysbench-tpcc supports PostgreSQL, it is a useful tool to evaluate a PostgreSQL performance. If you find that we did not optimally execute some transaction, please let us know!

PREVIOUS POST
NEXT POST

Share this post

Leave a Reply