Important Notice (13 Feb 2016)

When using pgadmin, the execution time at status bar is including data transfer time, unfortunately, I am using pgadmin status bar execution time to compare with MySQL server execution time which is unfair. I will update the correct benchmark soon.

Please Refer to updated benchmark on postgres vs mysql vs mariadb

Choosing RDBMS

So recently I want to find benchmark for RDBMS between MySQL/MariaDB and PostgreSQL. To my surprise, most benchmark result is outdated, therefore I have decided to benchmark them, using MariaDB(MySQL) [10.0.22(5.6.26)] vs PostgreSQL [9.2.14]

How to Benchmark

1. Using similar table structure: located at {db}.sql
2. Use python script to generate sql insert data
3. Open sqlyog to benchmark MariaDB(MySQL)
4. Open pgAdmin to benchmark PostgreSQL
5. All setting are using default: setting related file can be found at {benchmark_folder}/{db}_config.txt

You can find the detail at my Repo

Result

• mariadb(mysql) write (10000 rows): from insert.sql, run 10x time accumulate to 100000 rows

  1. 0.281s
  2. 0.131s
  3. 0.165s
  4. 0.146s
  5. 0.248s
  6. 0.141s
  7. 0.136s
  8. 0.208s
  9. 0.157s
  10. 0.155s

• postgresql write (10000 rows):

  1. 0.227s
  2. 0.256s
  3. 0.215s
  4. 0.227s
  5. 0.194s
  6. 0.195s
  7. 0.203s
  8. 0.205s
  9. 0.174
  10. 0.182s

simple query: taking average of multiple time query

1. SELECT * FROM testing LIMIT 1000

   • mariadb(mysql):0.007s
   • postgresql: 0.045s

2. SELECT * FROM testing WHERE int_col > 5000 LIMIT 1000

   • mariadb(mysql):0.007s
   • postgresql: 0.045s

3. SELECT * FROM testing WHERE int_col + int_col2 > 12345

   • mariadb(mysql): 0.008s
   • postgresql: 0.045s

4. SELECT COUNT(*) FROM testing WHERE int_col + int_col2 > 12345

   • mariadb(mysql): 0.040s
   • postgresql: 0.021s

5. SELECT * FROM testing WHERE int_col > 5000 ORDER BY word_col ASC LIMIT 1000

   • mariadb(mysql): 0.106s
   • postgresql: 0.093s

6. SELECT * FROM testing WHERE word_col LIKE '%lim%' ORDER BY word_col DESC LIMIT 1000

   • mariadb(mysql): 0.093s
   • postgresql: 0.058s

Conclusion

MariaDB(MySQL) is good at dead simple query, however PostgreSQL is good at more complicated query.

For real world scenario, it will more likely has a lot of complicated query, therefore its better to choose PostgreSQL for it - provided that PostgreSQL comes with lots of useful feature that MySQL lack of.