19 Dec, 2015

PostgreSQL vs MariaDB(MySQL) 2015

Postgres vs Mysql

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


  • 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


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.