> I have been testing it a bit performance-wise, and the numbers are
> worrying. In my test, MySQL (using InnoDB) had a 40% lead in
> performance, but I'm unsure whether this is indicative for PostgreSQL
> performance in general or perhaps a misconfiguration on my part.
In my experience the "numbers are always worrying" in a read-only environment.
I've used MySQL, but found it rather disturbing when it comes to integrity.
MySQL has just some things I can't live with (i.e. silently ignoring
overflowing charater types etc).
That aside, MySQL IS fast when it comes to read operations. That's probably
because it omits a lot of integrity checks postgres and other standard
compliant databases do.
I'm running a turbogears website with a couple million pages on postgresql and
I don't have any problems, so I guess postgres can be configured to service
Drupal just as well. Check your indexes and your work memory
(postgresql.conf). You want to have the indexes correct and in my experiene
the work memory setting is rather important. You want to have enough work
memory for sorted queries to fit the resultset into memory - as always disk
access is expensive, so I avoid that by having 2GB memory exclusively for
postgres - which allows me to do quite expensive sorts in memory, thus
cutting execution time down to a couple milliseconds.
Oh, and never forget: explain analyze your queries. That will show you whether
your indexes are correct and useful, as well as how things are handled. Once
you learn how to read the output of that, you'll be surprised what little
change to a query suddenly gives you a performance boost of 500% or more.
I had queries take 30 seconds cut down to 80 milliseconds just by setting
indexes straight.
Keep in mind: postgres will take good care of your data (the most important
asset in todays economy). I run all my customers on postgres and did so ever
since postgres became postgresql (the times way back then when postgres had
it's own query language instead of SQL). With a little care I've never seen
postgresql dump or corrupt my data - not a "pull the plug" scenario and not a
dumb user SQL injection scenario. I was always able to recover 100% of data
(but I always used decent hardware, which IMHO makes a big difference).
I've toyed with MySQL (not as deep as postgresql I must admit) and it
dumped/corruped my data on more than one occasion. Sure, it can be my
proficiency level with MySQL, but personally I doubt that. Postgresql is just
rock solid no matter what.
Uwe