Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

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

http://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. .

share|improve this question

closed as primarily opinion-based by dezso, Michael - sqlbot, Paul White, Max Vernon, Kin Sep 4 '13 at 18:56

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
3  
You can add that MySQL has only Nested Loop join algorithm, which for some types of joins is less efficient than hash joins (that Postgres has) –  ypercube Sep 4 '13 at 11:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.