Re: Advantages of postgresql

I've recently done the same thing.

Basically, it boils down to philosophy. MySQL's primary goal is speed.
Speed over features, and even speed over data integrity. PostgreSQL's
(and most RDBMS system's) primary goal is to present the complete
relational model and maintain ACID compliance.

If you're using MySQL 4.x or earlier, you've got a terrible DBMS. Prior
to MySQL 5, non-integer math was always inaccurate. There was no
precise datatype. Additionally, MySQL 4 lacked a number of features
like views, triggers, and stored procedures. MySQL 5 adds these
features. Even then, however, the default engine for MySQL, MyISAM, is
*not* a transactional engine so updates are not atomic. MyISAM also
doesn't support foreign key constraints, which, if your schema is even
remotely complex, is nightmarish. You must use the InnoDB engine in
MySQL to get the benefits of transactions.

Essentially, it boils down to this:

1. If you have a very simple database of 1 or two unrelated tables for a
shopping cart or a guest book, MySQL is fine. (But so is SQLite.)

2. If all you care about is speed and aren't terribly concerned if some
of your records break or don't commit, MySQL is also fine. This is why
some sites (Slashdot, Digg) use MySQL databases. It's no big deal if
one of the forums loses some random guy's anti-MS rant.

3. If you plan to do all your data checking in your control code and not
enforce referential integrity, MySQL is fine. This method is generally
considered poor design, however.

Part of the problem many DBAs have with MySQL is that the primary
developer is a bit... strange. Early versions of the MySQL
documentation called foreign keys tools for weak developers, and said
that ACID compliance could be emulated in your application code so it
wasn't necessary in the database. It should be pointed out that no
credible DBA (and, I should hope, no credible app devs) would agree with
these statements. Essentially, instead of properly citing limitations
of the database, early MySQL docs simply said not only that every other
DBMS in the world had it wrong, but that the relational model itself was
essentially not useful. To DBAs, MySQL came to be seen as one step
above the MBA who makes his department use a central Excel spreadsheet
as a "database". This reputation continues to stick with MySQL in spite
of the strides it has made with MySQL 5. Another huge problem with
MySQL is that it silently truncates data. If you have a DECIMAL(5)
field and try to INSERT 100000 or 1000000 or what have you, instead of
throwing an error MySQL instead inserts 99999 (the maximum value).
That's just... bad. An RDBMS should do exactly everything you tell it
and complain *loudly* when it can't.

If you're used to MySQL, the problems with PostgreSQL are basically that
it's not quite as friendly as MySQL. The command line for Postgre,
psql, is less user-friendly. The Windows GUI app, pgAdmin III, is also
less user-friendly. Additionally, the default install for PostgreSQL on
nearly every Linux system I've seen is configured to run at minimal
requirements. So you'll have to edit the configuration file in order to
get the database to perform correctly. Also, since PostgreSQL has so
many more features than MySQL, it can be a bit daunting to get started.
It's like you've worked with Notepad for years and years, and now you're
starting to use Word or EMACS.

--

Brandon Aiken

CS/IT Systems Engineer

________________________________

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Iulian Manea
Sent: Monday, October 02, 2006 3:38 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Advantages of postgresql

Hello everybody,

So far I have only been working with MySQL. Today I was talking to a
friend and he was suggesting I migrated to postgreSQL, as it is way
better ...

My question is ... why?

I mean could someone pls tell me some advantages and disadvantages of
working with postgresql?

Thanks in advance,

Iulian!