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.
