Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

Question

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing data(inserts) to the database. There will be less updates and deletes queries.

I have read this article on performace comparison of Postgresql vs MySQL.

Also I have gone through below links as well.

https://stackoverflow.com/questions/8181604/postgres-9-1-vs-mysql-5-6-innodb

https://stackoverflow.com/questions/110927/would-you-recommend-postgresql-over-mysql

https://stackoverflow.com/questions/724867/how-different-is-postgresql-to-mysql

MySQL vs PostgreSQL Wiki

MySQL vs PostgreSQL: Why MySQL Is Superior To PostgreSQL

My problem is some of the links on the stackoverflow is out dated. Some of the people say that Mysql better & vice-versa.

Since I'm more concerned with join queries & writing data to the database Which is better for me? Postgresql vs MySQL? What approachs should I take to design a database like this?

Given that please don't consider this as another Postgresql vs MySQL question. I have done my research and I'm only concerned with join queries & writing data to the database scenario. I also got to know that PostgreSQL is better for GIS data. .

Answer 1

The databases are different. In general, the answer depends very much on your specific queries. As far as a general use case, I would expect PostgreSQL to perform better for a few reasons, but there are cases where I would expect MySQL to perform better.

In PostgreSQL all tables are heap tables. In MySQL all innodb tables are btree indexes with the tuple in the payload. This means that primary key lookups are faster on MySQL but general queries are faster on PostgreSQL. It also means you typically need more indexes on MySQL which will slow down writes.

For example, the following query I would expect to perform better on MySQL than PostgreSQL:

SELECT u.username, p.* 
  FROM users u
  JOIN preferences p ON u.id = p.user_id
 WHERE u.id = 123;

Provided that both tables share the same primary key (u.id and p.user_id), there are thousands of rows in both tables, and so forth.

On the other hand, I would expect the following query to perform better on PostgreSQL than MySQL in a db too big to fit in memory, non-cached data, proper indexes, decent-sized tables, etc:

SELECT c.legal_name, a.*
  FROM company c
  JOIN address a on a.company_id = c.id
 WHERE a.zip_code like '95%' and country = 'us';

In this case you are having to utilize other indexes, which means a lot of extra random disk I/O on MySQL.

The second issue I would expect would be write performance. I would expect PostgreSQL to generally win here due to the fact that heap tables allow inserts wherever it is convenient, and fewer indexes maintained would be helpful too.