Re: PostgreSQL Advocacy, Thoughts and Comments

Comments within:

Chris Travers wrote:

Tony <tony@unihost.net> Wrote: 

 Now many
consultant/developer/sys-admins like myself are going to client site on
a contract (this is especially true in the UK, I can't speak for
anywhere else) and finding complex stocktrading systems, inventory
systems, CRM systems, and others, all written in PHP backed by MySQL.   

I started the CRM system I am developing on MySQL before realizing it was
the wrong choice.  Part of it is simple because people have heard of the
software and don't have the time/stamina/patience to do proper research into
the benefits of alternatives.  There is also a learning curve when going
from MySQL to a more standards-compliant RDBMS like PostgreSQL.  Heck, I
found that going from PostgreSQL to Firebird give me headaches :-P  And
these RDBMS's have most of the same features!
 

It's the learning curve part that I'm finding difficult, not because it's a too complicated, but because I can't find a good source of information to learn from.  I'm sure I'll get flamed for this, but I seem to be unable to find information on proper design principle, including where and when to use triggers, stored procs, etc, etc, that isn't 20 years old already.  In the liquid world of IT, I find it worrying (perhaps incorrectly) learning from a book written 14 years ago. 

Whether this is right or wrong, good choice or bad choice is not what
I'm interested in debating.   The point is that when these systems where
architected, the developers used MySQL not because they were dumb, but
because many of them develop awesome code and can get around most
problems in the code, with a little ingenuity.  Many simply do not have
the insight into the potential benefits of *proper* RDBMS can offer.   

I would actually venture to say that many of them are using the RDBMS as a
sort of object-persistance store, and not really trying to use the
*relational* features of the software.  They might as well be using Berkeley
DB 4.  I know that is how I started with MySQL.
 

Agreed...

What most of these programmers do not understand is that an RDBMS is not
simply a search-engine for stored persistant objects, but is actually a
fully-featured information storage management system.  With the right
features, this information can be stored, queried, presented in another
form, etc. all while ensuring that the stored information is EXACTLY what
was intended. The tasks that the RDBMS handles include data storage,
integrity enforcement, and data presentation.  Most MySQL programmers only
use it for data storage.  Sadly, this is about all MySQL is good for, and
hence the barrier to learning how to USE a REAL RDBMS are a bit higher
because of the prevalence of the likes of MySQL and MS Access. 

Indeed, and I believe that the lack of education with regards to this (or even available information presented at the right level) perpetuates the issue, along with the mis-information put forward by MySQL that these aspects aren't really all that important anyway.

The second scenario, is with admin systems, written by people like
myself for companies, whether they be simple or complex systems, that
are intended as a temporary work around to an immediate problem.
In a very short space of time the stop-gap application you had written
to sort out the immediate problem quickly becomes a core business
application (I recently returned to a site after not being there for two
years and the temporary address book/ email system that I knocked up in
an afternoon was not only still being used, but now relied upon heavily).   

But again, if you start with the right tools, it is easier to modify later
to adapt to changing needs.  I think that this is one of the messages we
should be presenting.  With updateable views, different applications can
even have access to different presentations of the data.
 

So on to my point, MySQL guys will happily say "Hey, we're not saying
that the features MySQL is missing aren't important, and we're working
towards them, but in the meantime these issues can be worked around like
this....."  and happily play the whole thing down.  Many LAMP developers
aren't aware of the benefits of stored procedures, of triggers and other
good stuff. Like myself, if they were aware how much easier life could
be if these things were accessible to them, they'd probably be converts   

too.

Agreed completely.  Now we just have to sell the PostgreSQL solution.  Here
is what the MySQL people will say (and we need good evidence to counter):
1:  MySQL is faster.
2:  MySQL has more community support.
3:  MySQL has replication as part of its core distribution.  MySQL's
replication is better tested... 

1.  Let's do apples to apples, NOT apples to Oranges as has been done many times in the past.   It would be far more useful IMHO to put forward a "real world" scenario (rather than purely transactions per second) which shows 2 examples of the same application, the first MySQL written with much of the logic in the code, PHP, Perl, whatever. the second would be written in the same language, with the appropriate use of the true RDBMS features in full effect.  Then let's comapare results.  I'm sure that the more we can usefully abstract to the DB the more performace you'll see from PG, especially as load increases.  The big issue I've found is not raw speed, if half of the logic is in regexps and algorithms in the scripting, this will be many times slower to run than the same en DB.  Although correct me if I'm way off the mark here.  Also the point needs to be made that, it is far more trivial to write other interfaces to the program/application.  Who wouldn't enjoy the ability to work on their CRM with a Python or Java frontend, without the need to write 75% of the logic in a different language.

There is not enough emphasis put on the basic importance of these
functions in PG.  Someone needs to standup and say "Hey, look how this
can simplify your programming lives"  until I started using
Druid/Postgres, I had no idea why I needed triggers or what a cascade
effect did, or why I might want one.   

The basic issue is that many programmers are not taught to value information
management systems, such as RDBMS's.  These programmers are interested only
in the data storage issues of the database, and not on how to use it to
manage the information stored therein. Changing this may take a lot of
effort.  Also, using an RDBMS to its full extent rubs some OO programmers
the wrong way because it strikes them as violating rules of OO design.  Of
course, then why not use an OO database? ;-) 

In some respects adding much of the logic to the DB doesn't fly in the face of OO, since you are providing an encapsulated data interface, only needing to change the programatic code in one place
(the DB) for potentially high returns in many places.  Hence taking OO one step further than the language and abstracting one level further.

The Linux  community has grown  at least in part because it has
educated  potential users and journo's to its benefits.  I believe if
the PG advocacy team did the same, then it would attract many more
serious LAMP developers.   

I agree.  But it will take some time to sell, and will require some
extremely basic work to get going.
 

I'd gladly help out with such a paper, but find myself in the sad
position of my prose being open to attack due to my newbieness in the DB
world and not able to speak authoratatively on the subject.   

I would be happy to help out as well.  How about a paper entitled:
Why PostgreSQL?
It would cover why PostgreSQL is ideal for serious application development. 

What about "PostgreSQL - Ideal for any application development"

Note, I have only about 2 years experience with PostgreSQL, and so I would
feel more comfortable with lengthy peer review of whatever I write, but I am
reasonably familiar with both worlds, and can make a strong case for
PostgreSQL, I think. 

I have only 2 months and have oodles of enthusiasm, but short on PG knowledge, and even shorter on real RDBMS basics and experience.  But I'll be happy to proof read as a novice.