Thread: 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!

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.

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!