Hacker News Reader

Comment by ConceitedCode | Hacker News Reader

I recently switched to PostgreSQL from MySQL and I couldn't be happier about it.

It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?

> Why do not more people select Postgres? Is it comfort level with MySQL?

* Cheap (let alone free/ISP) hosting does not provide Postgres

* Most introductory tutorials, especially in the PHP world (but also Ruby I believe) use MySQL

* AMP packages/installers (is there any out there using Postgres as its db?) and a history of punitively hard installation

Following that, a combination of habit (MySQL works for me, why would I care?) and BLUB (what could it even bring to the table?), as well as drawbacks/issues whose fixes are recent (replication, master/slave, ...)

I have an unusual reason, but a strong one: I primarily use relational DBs for large-scale analysis of frozen snapshots of data, rather than transactional loads. PostgreSQL has MVCC features built into it at the most fundamental levels where they cannot be disabled, and as such it's not suitable for the types of queries that I frequently run against MySQL. In the most extreme (and trivial) case, you can't run a "SELECT COUNT(*)" on a table in PostgreSQL without a full sequential scan of your data, which can be a huge expense when your row counts are in the tens or hundreds of millions. MySQL, in contrast, can return a cached answer instantly, which isn't possible under PostgreSQL. Less trivially, MySQL is still drastically faster for aggregates on low-cardinality fields.

Yes, I know you could set something up with triggers, but the point is that it needs to be simple enough for frequent, ad-hoc usage. Usually the time I need to know how big a table is when I just made it, and I might well drop it five minutes later. I'm not going to set up an elaborate network of meta-data tables when MySQL will just do it for me for free.

Not trying to hate on Postgres, by the way. I fully get that it's superior in most regards, and I use it frequently just for the much stronger support of user-defined functions. But MySQL does still have a few tricks left in it.

If I remember correctly it is only with MyISAM you get instant count(*) while InnoDB, just like PostgreSQL, has to look at every row of the table (or every row of the index since InnoDB also supports index only scans). This is due to both PostgreSQL and InnoDB being MVCC (multi-version concurrency control) database engines.

And MyISAM is only fast in very specialized workloads with low write and read/write concurrency.

For something like developer testing against frozen snapshots of data, you don't have the CRUD aspects, so turn fsync off (fsync = off in postgresql.conf). You'll note a large speed increase when you're not waiting on the disk to confirm transactions.

Nearly all of the introductory PHP tutorials use MySQL, so many beginning web developers automatically go to MySQL because it is the only thing they have played around with.

It may be that tutorials for Tool X use MySQL in their examples.

Because I spent $60 on an awesome MySQL reference guide and tutorial.

http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/re...

Please don't pimp your referral links on HN

I didn't know linking to Amazon did anything other than link to a page. I added this to make a point in the discussion about training and investment.

Thanks for helping me and being so polite about it.

Reply on news.ycombinator.com