Re: Drupal and PostgreSQL - performance issues?

On Sun, 12 Oct 2008 22:14:53 -0700
"Uwe C. Schroeder" <uwe(at)oss4u(dot)com> wrote:

> > 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 replying here but I could be replying to Scott and others...

I use nearly exclusively Postgresql. I do it mainly because it
makes me feel more comfortable as a programmer. I'm not the kind of
guy that is satisfied if things work now. I prefer to have something
that gives me higher chances they will work even when I turn my
shoulders and Postgresql give me the feeling it is easier to achieve
that result.

Anyway I don't find myself comfortable with replies in these 2 lines
of reasoning:
1) default configuration of PostgreSQL generally doesn't perform well
2) PostgreSQL may be slower but mySQL may trash your data.

I think these answers don't make a good service to PostgreSQL.

1) still leave the problem there and doesn't give any good reason
why Postgresql comes with a doggy default configuration on most
hardware. It still doesn't explain why I've to work more tuning
PostgreSQL to achieve similar performances of other DB when other DB
don't require tuning.
I know that a Skoda Fabia requires much less tuning than a Ferrari
F1... but well a Ferrari F1 will run faster than a Skoda with or
without tuning.
Making performance comparable without expert tuning will a) stop
most too easy critics about PostgreSQL performances b) give
developers much more feedback on PostgreSQL performance in "nearer
to optimal" setup.
1000 developers try PostgreSQL, 500 find it slow compared to other
DBs, 50 comes back to the list asking, 30 were looking for a magic
receipt that solved their problem, didn't find it and gave up, 10 at
least could hear they had to tune the DB but couldn't get convinced
to actually do so because it looked too expensive to them to learn.

If it is easy to write a tool that will help you to tune PostgreSQL,
it seems it would be something that will really help PostgreSQL
diffusion and improvements. If it is *complicated* to tune
PostgreSQL so that it's performance can be *comparable* (I didn't
write optimal) with other DB we have a problem.

Developer time is valuable... if it is complicated to tune
PostgreSQL to at least have comparable performances to other DB
PostgreSQL look less as a good investment.

Then other people added in the equation connection pooling as a MUST
to compare MySQL and PostgreSQL performances.
This makes the investment to have PostgreSQL in place of mySQL even
higher for many, or at least it is going to puzzle most.

Or maybe... it is false that PostgreSQL doesn't have comparable
performance to other DB with default configuration and repeating
over and over the same answer that you've to tune PostgreSQL to get
comparable performance doesn't play a good service to PostgreSQL.

2) I never saw a "trashing data benchmark" comparing reliability of
PostgreSQL to MySQL. If what I need is a fast DB I'd chose mySQL...
I think this could still not be the best decision to take based on
*real situation*.
Do we really have to trade integrity for speed? Is it a matter of
developers time or technical constraints? Is MyISAM really much
faster in read only operations?
Is Drupal a "read only" applications? Does it scale better with
PostgreSQL or MySQL?
These are answers that are hard to answer even because it is hard to
have valuable feedback.
What I get with that kind of answer is:
an admission: - PostgreSQL is slow
and a hard to prove claim: - MySQL will trash your data.
Unless you circumstantiate I'd say both things are false.

From my point of view the decision was easy. I needed transactions.
Functions would have made dealing with transactions much easier.
PostgreSQL had a much more mature transaction and function engine.
I like to sleep at night.

But is PostgreSQL competitive as a DB engine for apps like Drupal
for the "average user"?
Judging on real experience with Drupal on PostgreSQL I'd say maybe.
Judging on the replies I often read I'd say NO.
Unfortunately replies aren't turning that maybe into a NO for
any reasonable reasons.
If there are reasonable reasons to turn that maybe into a NO...
there may be some work to be done on the PostgreSQL code.
If there aren't reasonable reasons to turn that maybe into a NO...
please stop to give that kind of answers.
or both...

--
Ivan Sergio Borgonovo
http://www.webthatworks.it