4 ms·
MySQL vs PostgreSQL
- rbanffy 18y agoFlamewar in 3... 2... 1... Oh... This is not Slashdot. Sorry.
- olefoo 18y agoIf you're in Portland this weekend and want to learn something about Postgres then you should attend http://pgcon.us/ http://pgcon.us/ Postgres West.
- dazzawazza 18y agoThis 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).
- rbanffy 18y agoI 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.
- tdavis 18y agoORMs 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 18y agoI 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 18y agoI 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 18y agoWell, 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 18y agoThe flexibility is great, but only if grouped with consistency. No foreign keys on MyISAM but yes on InnoDB? Thanks but no, thanks.
- thwarted 18y agoNo disk backed storage with heap tables but yes on InnoDB? Thanks but no, thanks. Uh, the definition of flexibility is lack of consistency between the options. Otherwise, it's a false choice.
- earthboundkid 18y agoCar 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.
- st3fan 18y agoWe 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.
- jamongkad 18y agoOTOH I was thinking about moving my app from MySQL to SQLite. Did anyone else has gone through the same situation?
- ashleyw 18y agoThat 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 18y agoYou are right. But "hundreds of queries a second" are fine if there're mostly _read_ queries. http://www.sqlite.org/faq.html#q5 http://www.sqlite.org/faq.html#q5
- ashleyw 18y agoYeh I should have mentioned the times I've used SQLite was early in development, or one time when I created a script which would pump tons of data into the database in threads, which resulted in errors and missing data. I'm sure I could have added better error exceptions and keep retrying, but if you translate that to a multi-user app, thats just not going to cut it. A quick change to MySQL fixed it. :) I do like SQLite, just not for anything where you cant control the writes per second, or need it to be high.
- jamongkad 18y agoThank 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?
- jm4 18y agoYou couldn't get anywhere near the size of Facebook on SQLite. Even a FogBugz or BaseCamp type of application on SQLite could be problematic if you are doing lots of writes. The driver you use to connect to the database is also very important. If I'm not mistaken the PHP driver was pretty terrible a while back. It may be better now. It didn't handle the locks well which sometimes resulted in deadlocked queries with only a handful of users. Personally, I wouldn't run any multiuser application doing a significant number of writes on SQLite except during early development. I think where it really shines is when you need a lightweight embedded SQL database without the hassle of having to install something extra. Think Amarok or something similar. I ran Trac on SQLite for a while. Setup was a breeze and performance was reasonable with 3 or 4 users, but a switch to PostgreSQL was a night and day improvement. If you're set on using an embedded database you might want to check out Firebird or H2. I haven't used Firebird myself, but I have had excellent results with H2. You have the flexibility to use it a few different ways - embedded vs. server mode and in-memory vs. disk storage - and it is very compatible with MySQL syntax so it's useful as a drop-in replacement.
- Maascamp 18y agoMy 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 18y agoI'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 18y agoGreat 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.
- tdavis 18y agoI 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...