[support] Drupal: MySQL vs. PostgreSQL

[Ivan Sergio Borgonovo]

On Tue, 20 Jul 2010 16:20:13 -0700
Kevin Davison <kevin at quevin.com> wrote:

I'm using Postgresql nearly exclusively.

I think the real burden of using Postgresql is seeing your
suggestion get into core. That's mainly because Drupal world is
still MySQL centric at many levels.

Fixing modules is generally quite trivial and once you chose "good
modules" you generally find good maintainers that are willing to
accept patches promptly.

> Here's what I read about PostgreSQL on Drupal.org:

> (From http://drupal.org/node/33171)

I've found this a bit misleading and make the whole process look
harder than it generally is.
What I generally do is:
- give a look to hook_install to see there is any evident
  compatibility problem just to avoid to have to clean up if the
  module didn't install cleanly.
- install it
- play a bit with the module
- grep db_query

fixes are generally trivial, once you pass them upstream you're done.

> > First off you have to ask yourself is it really worth it? In
> > most cases no absolutely not, MySQL is just fine for sites that
> > are small and want to remain that way, this includes personal
> > sites and blogs etc.
> > 
> > PostgreSQL's real strength at least in my opinion comes from its
> > ability to seamlessly cluster, thereby distributing the load
> > among many DB servers rather than bogging down a single server.
> > If you have a big website with lots of traffic and have the
> > resources for multiple servers, then PostgreSQL is definitely
> > the way to go.

Postgresql is strong on writes and coherency (at the DB level and
dev level).

If you're used to MySQL... you may not appreciate too much as a
developer the higher level of coherency in PostgreSQL. If you're
already used to PostgreSQL you won't be willing to feel the pain of
dealing with MySQL quirks.

Then there is coherency at the DB level (ACID, pk, fk, concurrency
etc...) that's another area where PostgreSQL is strong.

In Drupal world many features of RDBMS can't still be exploited
fully (on delete cascade, transactions...)...
Dealing with coherency and concurrency is hard, if you have strong
requirement about coherency and you're coding your own stuff,
PostgreSQL may be better suited for your needs... but then... you'll
end up writing stuff that won't be easy to be ported to MySQL, if
you plan to make them public to exploit open source development the
community of interested developers *may* be smaller.

Load balancing solutions in MySQL and PostgreSQL world are quite
different.
MySQL simplest balancing solution is easier to set up than
PostgreSQL simplest balancing solution... but then if you go beyond
choosing the simpler... you'll have to chose on detailed requirement.

PostgreSQL 9.0 should make clustering simpler.

PostgreSQL is known to run VERY large DB.

If you don't have strong coherency and write concurrency requirement
MySQL may be your best choice.

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