Hi,
I got a application actualy running on mysql, but, due a contract with a client, the application can get some complex queries and lots of users (like 10x more users).
Its a academic application, and the company we are negociating have 7k+ students.
Im worry about the database, its better stick with de mysql? migrate to postgresql?
Which one can scale better with quality?
Thanks!
Diego Bernardes wrote:
Hi,
I got a application actualy running on mysql, but, due a contract with a client, the application can get some complex queries and lots of users (like 10x more users).
Its a academic application, and the company we are negociating have 7k+ students.
Im worry about the database, its better stick with de mysql? migrate to postgresql?
Which one can scale better with quality?
PostgreSQL is my default choice for all projects. It's a better database overall than mySQL, and does not force you to choose between data integrity and speed the way mySQL does. I'm told it doesn't handle clustering quite as well as mySQL, but that's its only real disadvantage.
However, it is likely that either database will scale suitably. The more immediate problems are likely to come from inefficient queries.
Thanks!
Best,
Scaling read queries with mysql is drop dead simple. Just add replicas. But you only have one server to do inputs on.
Real simple. But a Quad core machine with a little memory, 16 GB, you can serve hundred thousand people easily. We do hundreds of thousands of heavy queries every minute on one box. Just create indexes, ‘explain’ is your friend.
Trausti
I usually think about the specs and requirements, then make a choice.
+1 to PostgreSQL. It is my default too. I don't know about clustering, though. I've had many bad experiences with MySQL in the past, specially with large amounts of data, tables just got broken.
Greg Donald wrote:
PostgreSQL is my default choice for all projects.
I usually think about the specs and requirements, then make a choice.
So do I. But the type of projects I do are generally ones that are well
suited to PostgreSQL. Anyway, mySQL and SQLite are problematic, and MS
SQL and Oracle are too expensive to be justifiable for the sort of stuff
I work on. So that doesn't leave me much choice. 
-- Greg Donald http://destiney.com/
Best,
I've used both much success over the years. What sort of problems are you having?
I too choose PostgreSQL by default.
Here are some of my reasons.
1) It adheres more reliably to SQL standard than MySQL. 2) PostgreSQL has a better query planner than MySQL. 3) The number value zero in MySQL is treated NULL. 4) The default settings for MySQL make it case IN-sensitive. 5) Until version 5.0, MySQL did not support referential integrity, or advanced features like views, transactions, or triggers.
Just my thoughts.
Andrew
Here are some of my reasons.
1) It adheres more reliably to SQL standard than MySQL. 2) PostgreSQL has a better query planner than MySQL. 3) The number value zero in MySQL is treated NULL. 4) The default settings for MySQL make it case IN-sensitive. 5) Until version 5.0, MySQL did not support referential integrity, or advanced features like views, transactions, or triggers.
Actually, the only true reason to choose PostgreSQL over MySQL is this: you know PostgresSQL better. As for transaction support in MySQL it is not even funny anymore. But just in case: MySQL supports transactions since version 3.23.34a
Regards, Rimantas
I too choose PostgreSQL by default.
Here are some of my reasons.
1) It adheres more reliably to SQL standard than MySQL.
No it doesn't, they are both standards compliant.
2) PostgreSQL has a better query planner than MySQL.
MySQL's explain works great for me.
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
Looks nearly identical to the PostgreSQL offering:
3) The number value zero in MySQL is treated NULL.
That's a lie.
MySQL had replication before PostgreSQL too, I know I worked for a startup that helped fund it.
MySQL supports 7 different table types so you choose the right type for the data at hand. PostgreSQL support one table type.
PostgreSQL doesn't even store user/host permissions in a database, talk about not eating your own dog food.
* I use PostGIS a lot. There is nothing comparable for mySQL.
This is a lie.
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
* MySQL makes you choose between speed and data integrity. PostgreSQL does not.
PostgreSQL has one table type. MySQL gives you 7 different table types to choose from.
Sounds like you're either not smart enough to choose the right one or you didn't know you could make a choice.
I've never has any speed problems with MySQL, even using it on mega-sites like ezsweeps.com back in the late 90's.
2) PostgreSQL has a better query planner than MySQL.
MySQL's explain works great for me.
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
Looks nearly identical to the PostgreSQL offering:
I think what the GP was trying to say is that PostgreSQL picks a better execution plan for the query than MySQL does. Not all the time, but certainly some of the time. Several months ago I had both servers setup with the defaults, same schema, same indexes, same dataset, same query. The query was a three table join. Two smaller tables, one with 30,000 rows. PostgreSQL finished almost immediately. MySQL took 3 seconds. It kind of shocked me. I ran explain on both and MySQL simply would not use some of the indexes provided. This was with a 5.0-something release of MySQL. I'd heard that before, but it's still an issue in certain situations.
3) The number value zero in MySQL is treated NULL.
That's a lie.
Glad to know that's fixed! It was true awhile back. So where the issues of inserting data that is too long and having it be silently truncated -- yes, I'm ignoring the warnings MySQL provides. It should raise an error...
I notice that as of 5.0.51a-24 it still turns invalid dates into 0000-00-00 (with the warning, but no error).
5) Until version 5.0, MySQL did not support referential integrity, or advanced features like views, transactions, or triggers.
This argument is useless. There's a ton of things PostgreSQL used to not do when it was < 5.0. Replication, binary field storage, etc.
That's not a very fair comparison. Version numbers are meaningless.. Compare the versions at the same time... I will happily agree that prior to 7.x postgres had some issues with these things.
Replication is still not as simple as MySQL's, but there are solutions out there.
I'd suggest we let the original poster do some research on his own and figure out what works best. MySQL vs PostgreSQL is like VI vs Emacs vs TextMate
I don't know how current these pages are, but they are interesting...
http://sql-info.de/mysql/gotchas.html http://sql-info.de/postgresql/postgres-gotchas.html
-philip
So where the issues of inserting data that is too long and having it be silently truncated -- yes, I'm ignoring the warnings MySQL provides. It should raise an error...
It does raise an error if you're knowledgeable enough to know about the various strict modes you can set:
create table test4 (name char(3));
Query OK, 0 rows affected (0.03 sec)
insert into test4 values ('four');
Query OK, 1 row affected, 1 warning (0.00 sec)
Surely you can tell me you think I'm wrong without resorting to calling me a liar. Let's keep this debate as civil as possible.
You lied. Stop telling lies and your problem will solve itself.
Srsly? You're claiming that these are comparable to PostGIS?
They have served me well. What problems are you having?
You can easily replicate a table to another table of a different type.
What's the PostgreSQL analogue for taking away table features you don't need? Oh, right, there is none. You're stuck with that one "everything but the kitchen sink" table type.
I did not lie. I stand by my original statement and see no reason to retract or revise it.
You did lie. I have the email right in front of me. You said "I use PostGIS a lot. There is nothing comparable for mySQL.".
Then I showed you the url for the MySQL spacial extension. Here it is again in case you missed it:
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
It was created to solve the same problem as PostGIS, therefore they are indeed "comparable".
I'm not "telling lies" as you claim -- I'm stating what I believe is true.
Believe what you want, it doesn't make your lie true.
Quite wrong. I know mySQL, if anything, a bit better than PostgreSQL.
No you do not. You did not even know SQL_MODE. <…>
Regards, Rimantas
Greg Donald wrote:
Rimantas Liubertas wrote:
Quite wrong. I know mySQL, if anything, a bit better than PostgreSQL.
No you do not. You did not even know SQL_MODE. <…>
Yes, you found an obscure setting that I was unaware of. I'm sure there are more. I don't know 100% of either system's config parameters, and I don't claim to. But I do know both systems well enough to have used both for advanced development.
Regards, Rimantas -- http://rimantas.com/
Best,