DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • PostgreSQL vs MySQL Performance
  • Top 8 PostgreSQL GUI Software in 2021
  • 5 Key Postgres Advantages Over MySQL
  • Using Envoy Proxy’s PostgreSQL and TCP Filters to Collect Yugabyte SQL Statistics

Trending

  • Event-Driven Architectures: Designing Scalable and Resilient Cloud Solutions
  • Non-Project Backlog Management for Software Engineering Teams
  • Role of Cloud Architecture in Conversational AI
  • Building AI-Driven Intelligent Applications: A Hands-On Development Guide for Integrating GenAI Into Your Applications
  1. DZone
  2. Data Engineering
  3. Databases
  4. Sysbench-tpcc Supports PostgreSQL (No, Really This Time)

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

When I initially announced sysbench-tpcc, I mentioned that it potentially could run against PostgreSQL, but it was more like wishful thinking than reality.

By 
Vadim Tkachenko user avatar
Vadim Tkachenko
·
Apr. 25, 18 · Analysis
Likes (2)
Comment
Save
Tweet
Share
4.6K Views

Join the DZone community and get the full member experience.

Join For Free

This time, we really mean it when we say sysbench-tpcc supports PostgreSQL.

When I initially announced sysbench-tpcc, I mentioned that 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:

It appears that PostgreSQL does not support the tinyint and datatime data types. We had to use smallint and timestamp fields, even if using makes the database size bigger.

PostgreSQL does not have a way to disable Foreign Key checks like MySQL: SHOW TABLES. The best replacement we found is:

select * from pg_catalog.pg_tables where schemaname != 'information_schema' and schemaname != 'pg_catalog'

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. PostgreSQL requires you to have a unique index name per the whole database, white MySQL requires it only per table. So instead of using:

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:

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)

PostgreSQL does not have a STRAIGHT_JOIN hint, so we had to remove this from queries. But it is worth mentioning that we use STRAIGHT_JOIN mostly as a hack to force MySQL to use a correct execution plan for one of the queries.

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 FROMdistrict,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 FROMdistrict,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 tried 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!

PostgreSQL Database MySQL

Published at DZone with permission of Vadim Tkachenko, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • PostgreSQL vs MySQL Performance
  • Top 8 PostgreSQL GUI Software in 2021
  • 5 Key Postgres Advantages Over MySQL
  • Using Envoy Proxy’s PostgreSQL and TCP Filters to Collect Yugabyte SQL Statistics

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: