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

This 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:
   
   Shell

   CREATE INDEX idx_customer ON customer1 (c_w_id,c_d_id,c_last,c_first) CREATE INDEX idx_customer ON customer2 (c_w_id,c_d_id,c_last,c_first)

   1 2	CREATE INDEX idx_customer ON customer1 (c_w_id,c_d_id,c_last,c_first) CREATE INDEX idx_customer ON customer2 (c_w_id,c_d_id,c_last,c_first)

   
   We need to use:
   
   Shell

   CREATE INDEX idx_customer1 ON customer1 (c_w_id,c_d_id,c_last,c_first) CREATE INDEX idx_customer2 ON customer2 (c_w_id,c_d_id,c_last,c_first)

   1 2	CREATE INDEX idx_customer1 ON customer1 (c_w_id,c_d_id,c_last,c_first) CREATE INDEX idx_customer2 ON customer2 (c_w_id,c_d_id,c_last,c_first)

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!