Re: PostgreSQL Advocacy, Thoughts and Comments

Comments within:

Chris Travers wrote:

>Tony <tony(at)unihost(dot)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.

>
>
>
>
>