7

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.

Postgres 9.1 vs Mysql 5.6 InnoDB?

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

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. .

Community
  • 1
  • 1
Techie
  • 44,706
  • 42
  • 157
  • 243
  • 5
    Don't know about performance, but I like SQL in PostgreSQL much more than one in MySQL. MySQL don't have window functions, PostgreSQL have `distinct on` and common table expressions, hstore, json, arrays + you can use plpgsql, plpython and other languages. + MySQL `group by` syntax is weird. – Roman Pekar Sep 04 '13 at 10:14
  • 9
    This is ***very*** much personal preference. My personal opinion is that Postgres has *so* many more (modern) SQL features that this decision is a a no-brainer to me (see my list of features in the first SO post you linked to) –  Sep 04 '13 at 10:14
  • @a_horse_with_no_name +1 to your answer, you can mention hstore, composite data types, arrays (if MySQL doesn't have it) – Roman Pekar Sep 04 '13 at 10:16
  • At least leave a comment why you are marking it as should be closed – Techie Sep 04 '13 at 10:18
  • I agree wholeheartedly with @a_horse_with_no_name. postgres is my goto choice for green field projects. I admit mysql has lots of funky features that are really handy, but postgres has evolved over the last few years and I put it ahead of mysql overall. That said, mysql is very popular and lots of good companies use it and it's fine too. – Bohemian Sep 04 '13 at 10:19
  • 4
    @Dasun: it will be closed because it is "mainly opinion based" - you won't get a definite answer. You will have to bite the bullet and compare the features and decide for yourself. If you are concerned about performance, you will need to do your own performance tests with a load that resembles your application load. Btw: we just migrated one application from MySQL to Postgres and were able to replace a quite lengthy stored procedure that took over an our in MySQL with a single SQL statement in Postgres (making heavy use of CTEs and window functions) that ran for 5 minutes. –  Sep 04 '13 at 10:23
  • 2
    Test it yourself. Why wait to hear opinions. Just do it! – Namphibian Sep 04 '13 at 10:24
  • Thanks for the concern @a_horse_with_no_name. Please post it as an answer therefore i can accept it. – Techie Sep 04 '13 at 10:26
  • 1
    WRT `many joins`: I have seen postgreSQL query plans with a few thousand lines. Hard to read (for a human being), but correct. – wildplasser Sep 04 '13 at 10:46
  • 1
    @a_horse_with_no_name if above question is on hold why is not this one ? http://stackoverflow.com/questions/8181604/postgres-9-1-vs-mysql-5-6-innodb – Techie Sep 04 '13 at 11:05

0 Answers0