|
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. -- CS/IT Systems Engineer From:
pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Iulian Manea 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! |
