It's common knowledge, it seems, that MySQL without transactions will be a lot faster than Postgres on Inserts. And on Updates too, that is, unless you have more than a few concurrent concurrent connections, at which point the MySQL full table lock will just kill everything. And you don't have transactions, of course, and if something goes wrong, bye bye data, or funky stuff happens, like half-commited transactions if a constraint is violated in an INSERT SELECT, or you get 0 January 0000 or 31 February, etc. I heard it said that MySQL with transactions (InnoDB) is slower than postgres. I'd believe it... and you still get 00-00-0000 as a date for free. But from your use case postgres doesn't sound like a problem, yours sounds like a few big batched COPY's which are really really fast.

And about SELECTs, this is really from an experience I had a few months ago, from a e-commerce site... well, to put it nicely, MySQL's planner don't know shit when it comes to doing anything a bit complicated. I had this query to show the "also purchased" products on a page, and also a few other queries, best buys in this category, related products, etc..., nothing very complicated really, at worst they were 4-table joins... and with 50K products MySQL planned it horrendously and it took half a second ! Seq scans every times... I had to split the query in two, one to get the product id's, another one to get the products. I took the sql, put it in postgres with the usual changes (typenames, etc...) but same indexes, same data... the query took half a millisecond. Well... what can I say ?

Also when you sit in front of the psql or mysql command line, it's an entirely different experience. One is a pleasure to work with... the other one is just a pain.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to