MySQL vs PostgreSQL

http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

by chaostheory • 18 years ago

62 26 18 years ago

wikivs.com

rbanffy2• 18 years ago

Flamewar in 3... 2... 1...

Oh... This is not Slashdot. Sorry.

olefoo• 18 years ago

If you're in Portland this weekend and want to learn something about Postgres then you should attend http://pgcon.us/ Postgres West.

dazzawazza• 18 years ago

This is very useful (although I am not qualified to say if it's definitive or not). I chose PostgreSQL based on one single fact that I ONCE crashed a MySQL server with a query (about 4 years ago) and I've never crashed PostgreSQL.

It's a flimsy reason tbh but it's all I had to go on. Having said all that I do really love PostgreSQL now after 4 years of using it (via SQLAlchemy).

rbanffy2• 18 years ago

I tend to chose PostgreSQL because of the query language. It is more comfortable to program in.

As ORMs become more prevalent, things like query language start to become less relevant, but so do relational databases. I really don't care how my data is stored, as long as it's safe and performs well.

tdavis2• 18 years ago

ORMs are a case of "good enough for most," however, I have seen ridiculously dramatic increases in performance when dealing with millions of DB rows by using SQL and stored procedures. By design, it's pretty much impossible for an ORM to ever reach such levels of performance since they need to be generic enough to work across various RDMS systems and so forth.

joestrickler• 18 years ago

I don't understand why MySQL has 9 different storage engines.

Would someone mind quickly posting the benefits of that architecture as opposed to focusing on one 'storage engine' and making it really good?

neilc• 18 years ago

I think the argument is that different domains impose different requirements on the storage engine. The type of data organization I would like to use for a read-mostly web application vs. an OLTP-type transaction processing workload vs. an OLAP-type analysis workload might all differ fairly substantially.

cbrinker• 18 years ago

Well, I pose to you this question:

Would you rather have 1 type of car that the government makes that works pretty reliably and is a middle-ground car, or would you rather have a selection of cars to chose from; sports, truck, sedan, minivan, etc. ?

Everyone has different needs. I use MySQL extensively for it's memory-based storage engine, MyISAM for quick & dirty non-escential data i/o, and InnoDB for when data needs to be managed securely for processes.

I would use PG for transaction-based data processing in a high-volume situation with multi-processing clusters. Otherwise MySQL works fantastic for all my needs.

samuel• 18 years ago

The flexibility is great, but only if grouped with consistency. No foreign keys on MyISAM but yes on InnoDB? Thanks but no, thanks.

earthboundkid• 18 years ago

Car analogies?

Well, I pose you this question:

Would you rather have a car that runs into a wall and explodes and kills everyone inside or no car at all? The car is like using car analogies on Slashdot and not having a car is like not using car analogies on YC.

In conclusion and in summary, go back to Slashdot. KTHXBAI.

davidw• 18 years ago

I've always preferred the bicycle analogy: MyISAM is like a bicycle with no brakes. It goes real fast down hills, unless something goes wrong.

mdasen• 18 years ago

It isn't about better vs worse. It's about the right tool for a job.

For example, if you needed to store key => value pairs, how would you do that? If you use an array (array[0] = ('key', value')), you would have to iterate over the entire array to find the key you're looking for (Order n), but if you use a hash table, you can do key look-ups in constant time (Order 1).

MySQL's different storage engines provide similar opportunities to use different ways of storing data for different ends. MyISAM has amazingly fast read speeds, but is really poor under write conditions. Sounds like a crappy storage engine, right? Well, what if you have a table of zip codes and their geo-coordinates? How often are you going to be writing new data to that table? Every couple months in a batch update? So, if you put your postal codes in a MyISAM table you can take advantage of high read rates and ignore the fact that MyISAM is terrible for writes since you don't really write to the table.

st3fan• 18 years ago

We switched from MySQL to PG when mysql suddenly returned different results in a unit test when we upgraded to a newer minor version. Never looked back.

jamongkad2• 18 years ago

OTOH I was thinking about moving my app from MySQL to SQLite. Did anyone else has gone through the same situation?

ashleyw2• 18 years ago

That is a bad idea I think — SQLite locks the database while inserting data, obviously this is bad for a big application. I don't think it was designed for web apps with possibly hundreds of queries a second.

ash• 18 years ago

You are right. But "hundreds of queries a second" are fine if there're mostly _read_ queries.

http://www.sqlite.org/faq.html#q5

jamongkad2• 18 years ago

Thank you, but what is the definition of a big application? FaceBook? what if you were building something along FogBugz and the like? or maybe even something like BaseCamp? would SQLite fit those types of applications?

zby• 18 years ago

SQLite is a lot faster than than the 'real' RDBMSes on single queries - so this lock on writing is not that painful as it might seem. I think SQLite is really eating into that quick-and-dirty db segment occupied by MySQL - while PostgreSQL is still ahead in the standard-compliant-robust-and-lot's-of-features department.

Maascamp• 18 years ago

My preferred DBMS is Postgres, but I have to use MySQL at work. I can honestly say that moving to MySQL after Postgres is a serious downer. What happens when you want referential integrity alongside fulltext indexes? An ugly database.

jhancock• 18 years ago

I've read many MySQL vs Postgres articles/papers over the years. This one is the best. Not highly opinionated. "Just the facts" mostly. And in a good format. Thanks for publishing!!!

oh yeah, go postgres!!! ;)

iamelgringo• 18 years ago

Great comparison of the most recent features and versions. It's nice to see PostGres get some kudos in the speed department. And, it's nice to hear about the upcoming developments of the Falcon and Maria engines.

tdavis2• 18 years ago

I agree on both counts. I love postgre, but it took me a long time to learn the real differences from MySQL, what situations it's better in, etc. Wish this was around a long time ago...

MySQL vs PostgreSQL

Related Stories