Re: Need concrete "Why Postgres not MySQL" bullet list

On Wednesday 20 August 2003 17:39, Josh Berkus wrote:
> Folks,
>
> I need someone to prepare a standard response for me to send out to
> inquiries on this topic. I get them a lot.
>
> What I'd like is a factual, non-perjorative list of the things which
> PostgreSQL and the PostgreSQL project have that MySQL does not,

Oh dear, this excludes my usual one-item "things to think about
when considering what database to use":

* PROPER USAGE OF NULL

mysql> select * from ai_test where id is null;
+----+-------+
| id | txt |
+----+-------+
| 1 | hello |
+----+-------+
1 row in set (0.00 sec)

;-). I digress. Off the top of my head, in no particular order:

* VIEWS
VIEWs are an important element of efficient professional database design,
pushing repetitive, often duplicated queries into the backend,
increasing the flexibility of an application and reducing the risk of errors.
They also enable the encapsulation of table structure, which may change
with application development, behind a consistent interface.

- MySQL has no support for views, but promises them in a future version,
for which no target production date exists:
http://www.mysql.com/doc/en/ANSI_diff_Views.html

* TRIGGERS
Triggers enable predefined actions to be carried out before
or after inserts, deletes and updates are performed on tables.
This enables automation of many tasks in the database, for
example logging specific events such as changes to a table
holding accounting data, or checking and modifying a column
prior to insertion or update.

- MySQL does not support triggers. Implementation is planned:
http://www.mysql.com/doc/en/ANSI_diff_Triggers.html

* SEQUENCES
Often it is useful or necessary to generate a unique number
for a row of data, for example for providing a primary key
ID. Many databases including PostgreSQL provide sequences -
functions for automatically generating unique numbers
in predefined increments.

- MySQL provides only a very primitive sequence generator, AUTO_INCREMENT,
whose behaviour is difficult to modify. It is not possible to explicitly
set the current value of a specific AUTO_INCREMENT sequence or an
incrementation other than 1.
- AUTO_INCREMENT is implemented as SELECT MAX(col) FROM table
(see: http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html )
which makes concurrent transactions prone to deadlocks
- The use of the NULL value to provoke insertion of the next sequence value
means it is impossible to have a sequence on a NULLable column in MySQL.
- there can be only one AUTO_INCREMENT column per table

* RULES, TYPES, DOMAINS

* PROCEDURES
in a variety of languages...

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.
- Some parts of a table definition, although syntactically corrent,
maybe ignored without warning. This applies particularly to syntax
such as
CREATE TABLE my_table (
id INT NOT NULL PRIMARY KEY,
fkey_id INT NOT NULL REFERENCES other_table(id)
) TYPE=InnoDB

See:
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html

- MySQL may, at its discretion, silently alter column specifications. See:
http://www.mysql.com/doc/en/Silent_column_changes.html
- MySQL only permits constants as default values not functions or expressions.
See: http://www.mysql.com/doc/en/CREATE_TABLE.html
This makes it impossible to provide default values like this:
CREATE TABLE deftest (
id INT,
date_at_creation TEXT DEFAULT 'The date is '|| to_char('dd-mm-yyyy',
now())
);

* GENUINE REFERENTIAL INTEGRITY
An essential part of the relational model is the ability to
create foreign keys, which define relationships between tables.
Naturally only values which exist in the referenced table can
be used in a foreign key column.
PostgreSQL has provided integrated foreign key support
since (find out when).

- In MySQL foreign keys are an "optional extra" and are only
available when the InnoDB table type is specified.
- Not all MySQL server installations are configured to provide
InnoDB support, e.g. ISP-run servers.
- despite the implementation of foreign keys with InnoDB tables,
MySQL does not provide robust referential integrity.
Under certain circumstances MySQL will insert into or permit the
presence of values in a foreign key which are not present in the table
referred to. For example, if a foreign key column can contain null
values, and the column's definition is changed to NOT NULL,
MySQL will insert 0 (zero) into the previously NULL columns,
even if the column referred to in a different table does not contain
0.
- No ON UPDATE ... support

* RIGOROUS FAILURE TESTING
When developing a database, and in day-to-day operation, it
is essential that erroneous statements fail with a specific
warning to prevent violations of data integrity.

- in many cases where a statement would be expected to
fail, MySQL will silently insert default values.
For example, when (mistakenly) inserting an integer
1 into an ENUM field, MySQL will insert a string
containing '0' rather than raise an error.

* DICTIONARY BASED FULL-TEXT INDEXING
Although SQL provides several methods of searching for patterns
in text fields (LIKE, regexes), these do not provide sufficient functionality
for more complex searches, e.g. as used by search engines.

- MySQL provides a builtin index type FULLTEXT, which allows keyword
searches. In contrast to PostgreSQL's tsearch2 functionality, this
does not provide for advanced natural language based searches using
features such as dictionary lookups and stemming.

* LICENSING
- MySQL is available as "open source", but depending on useage
not always with an open source license. MySQL's licencing
conditions are subject to change.
- PostgreSQL is available under a BSD-style licence to all
users whether commercial or private.

I can take the above and any further additions and flesh it out
into a more readable list (if noone else wants to). I have a
project at the moment which will probably be marketed as
a MySQL-compatible application ("it's what the users have"),
though I am prototyping it in PostgreSQL, and I'm collecting
a lot of interesting insights...

Ian Barwick
barwick(at)gmx(dot)net