|
SQL
STANDARD COMPLIANCE
|
- Mysql: MySQL
understands a
subset of SQL92 and some subset of the SQL99 syntax (union statement),
along with extra extensions such as the REPLACE statement and the LIMIT
clause for SELECT and DELETE.
- PostgreSQL: Postgres
understands a large subset of SQL2003 standard than MySQL.
- MaxDB: SQL 92 entry level
with
several extensions, support outer joins and subselects.
- Ingress:
entry level SQL92, several parts of Intermediate Level and some SQL99
The standards:
|
| Speed |
- MySQL is very fast on both
simple and
complex SELECTs, but might require changing the database type from
MySQL/MyISAM to MySQL/InnoDB for UPDATE intense applications. MySQL
handles connections very fast, thus making it suitable to use MySQL for
Web - if you have hundreds of CGIs connecting/disconnecting all the
time you'd like to avoid long startup procedures. As you can read from
MySQL Homepage: "MySQL is generally much faster than PostgreSQL"
- PostgreSQL: PostgreSQL
is
somewhat slow but has a lot of options for improving check Tunning
PostgreSQL for performance.
PostgreSQL forks on every incoming connection and the forking process
and backend setup is a bit slow, but one can speed up PostgreSQL by
coding things as stored procedures.
|
| ACID |
- ACID (an acronymn for Atomicity
Consistency
Isolation Durability) is a 'keyword' that business professionals
generally look for when evaluating databases. Frankly, non-ACID
databases aren't taken very seriously.
Here is a quick description of what it means to be ACID
compliant:
1. Atomicity is an all-or-none proposition. Suppose you define a
transaction that contains an UPDATE, an INSERT, and a DELETE statement.
With atomicity, these statements are treated as a single unit, and
thanks to consistency (the C in ACID) there are only two possible
outcomes: either they all change the database or none of them do. This
is important in situations like bank transactions where transferring
money between accounts could result in disaster if the server were to
go down after a DELETE statement but before the corresponding INSERT
statement.
2. Consistency guarantees that a transaction never leaves your
database in a half-finished state. If one part of the transaction
fails, all of the pending changes are rolled back, leaving the database
as it was before you initiated the transaction. For instance, when you
delete a customer record, you should also delete all of that customer's
records from associated tables (such as invoices and line items). A
properly configured database wouldn't let you delete the customer
record, if that meant leaving its invoices, and other associated
records stranded.
3. Isolation keeps transactions separated from each other until
they're finished. Transaction isolation is generally configurable in a
variety of modes. For example, in one mode, a transaction blocks until
the other transaction finishes. In a different mode, a transaction sees
obsolete data (from the state the database was in before the previous
transaction started). Suppose a user deletes a customer, and before the
customer's invoices are deleted, a second user updates one of those
invoices. In a blocking transaction scenario, the second user would
have to wait for the first user's deletions to complete before issuing
the update. The second user would then find out that the customer had
been deleted, which is much better than losing changes without knowing
about it.
4. Durability guarantees that the database will keep track of
pending changes in such a way that the server can recover from an
abnormal termination. Hence, even if the database server is unplugged
in the middle of a transaction, it will return to a consistent state
when it's restarted. The database handles this by storing uncommitted
transactions in a transaction log. By virtue of consistency (explained
above), a partially completed transaction won't be written to the
database in the event of an abnormal termination. However, when the
database is restarted after such a termination, it examines the
transaction log for completed transactions that had not been committed,
and applies them.
|
|
Stability
|
- MysSQL
has a much larger
user base
than PostgreSQL, therefore the code is more tested and has historically
been more stable than PostgreSQL. MySQL is the much more used in
production environments - MySQL has a lot of tweaking options. Random
disconnects and core dumps are exceptionally rare.
- PostgreSQL:
PostgreSQL 6.x
series
and earlier were dodgy. 7.0 series was a big improvement, 7.1 series is
very good. All incoming patches/improvements/suggestions are reviewed
by the core coders then accepted/rejected (mostly with recommendations
of changes/improvements)
|
| Data integrity |
- MySQL:
InnoDB/NDB storage engines have
transactions/rollbacks with savepoints.
InnoDB has foreign key constrains.
- PostgreSQL:
Postgres has
transactions/rollbacks and foreign key constrains
|
| Security |
- Mysql:
- Access Control: MySQL can limit logins based on different criteria; username, table name and client hostname. MySQL offers
access control for user/host connectivity, and from server-side down to
column level. With views in 5.0, row-level security is also possible.
- PostgreSQL:
- Access Control:
PostgreSQL can limit logins based on
different criteria -
network segment, ident string. Since version 7.3 PosgreSQL has added
schema, function and other permissions and settings to increase the
database administrator's granular control over security
|
| LOCKING and
CONCURRENCY SUPPORT |
- Mysql:
-
MySQL/MyISAM has a concurrent insert mechanism so many inserts donīt
locks reads.
-
InnoDB does locking on the row level
and also provides an Oracle-style consistent non-locking read in SELECT
statements
- PostgreSQL:
has a mechanism called
MVCC
(MultiVersion Concurrency Control), comparable or superior to best
commercial databases. It can do row-level locking, can lock rows for
writing in one session but give these rows unaffected in another
session.
- MaxDB:
row level support
|
|
Stored Procedures
|
- MySQL:
you can read in mysql
homepage: "Stored procedures. This is currently not regarded to be very
important as stored procedures are not very standardized yet. Another
problem is that true stored procedures make it much harder for the
optimizer and in many cases the result is slower than before We will,
on the other hand, add a simple (atomic) update language that can be
used to write loops and such in the MySQL server. " Check: http://www.mysql.com/doc/M/i/Missing_Triggers.html
Anyway there is an external development implemented in perl: tangent.org
|
| Transactions
|
- MySQL:
-BerkeleyDB tables and InnoDB both
support transactions (version >= 3.23.* )
- MySQL Cluster (NDB) works with a 2-phase commit (XA is in progress)
- InnoDB supports savepoints
- PostgreSQL:
since version 8.0. PostgreSQL also support Savepoints. Savepoints
allow specific parts of a transaction to be aborted without
affecting the remainder of the transaction. Prior releases had no such
capability; there was no way to recover from a statement failure within
a transaction except by aborting the whole transaction. This feature is
valuable for application writers who require error recovery within a
complex transaction.
2-phase commit are planned for version 8.1
- Firebird:
including
XA-compliant 2-phase
commit protocol
|
| Foreign keys
constrains |
- MySQL:
InnoDB tables support foreign keys constrains with cascading update and
delete
- Firebird: support foreign keys constrains with cascading update and
delete
|
| Replication
|
- MySQL:
"One way replication can be
used is
to increase both robustness and speed. For robustness two (or more)
systems can used to switch to a backup server if you have problems with
the master. The extra speed is achieved by sending part of the
non-updating queries to the replica server. Replication can also
benefit database backup operations. Live backups of the system can be
done on a slave instead of a master, eliminating potential problems and
possible downtime".Since version 4.1 replication can be done over SSL.
MySQL 4.1 has MySQL Cluster which offers full shared-nothing redundancy.
- PostgreSQL:
there is no built-in replication support in PostgreSQL, but it supports
replication
features via:
- Slonny
"master
to multiple slaves" replication system with cascading and failover
- eRServer the Enterprise Replication Server project. Is an
asynchronous replication server, which is probably less than ideal.
- MaxDB:
Only full table dump
and load,
but no online replication via log shipping or similar techniques
|
Visual
administration tools
|
- MySQL: MySQL
administrator
- PostgreSQL:: PgAdmin III, see
also
http://techdocs.postgresql.org/guides/GUITools
- MaxDB: DBMGUI (only
available for windows). Web interface also available
- Firebird: FlameRobin, see also
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_admin_tools
- Ingress: Visual DBA [VDBA]
not
Open Source and only available for windows, included in binary
distribution package (registration required)
|
| Platforms
|
- PostgreSQL:
Since version 8.0 (January 2005) there is a Native binary
distribution for Windows
|
|
Support 24/7
|
- MySQL:
Yes. Any version. No
special maintenance required.
- PostgreSQL:
VACUUM: Recent
versions of postgresql don't take your database offline during vacuum.
However, the vacuum process is an I/O intense process and can still,
even 7.4, slow the server significantly while it's running. Work is has
alredy been done in the 7.5 development tree to address the I/O storm
created by vacuum .
|
Hot Backups
|
- MySQL: - Using replication
is a very good method.
- Filesystem snapshots (such as LVM) are also posible
- MySQL Cluster ca take online backups
- if all tables are InnoDB, simple selects on all desired tables -
wrapped together in a sigle transaction- will also provide a
fully consistent and
non-locking (i.e. online) backup.
- PostgreSQL: since version 8.0 (January 2005) PostgreSQL
support a way to recover from disk faliure through Point-in-Time
recovery (PiTR) that continuous backup of the server. You
can recover either to the point of failure or to some transaction in
the past.
- Firebird: there
is a new
_incremental_ backup utility under development NBackup,
planned for version 2.0
|
| Schemas |
- PostgreSQL: schemas
allow users to create objects in separate
namespaces, so two people or applications can have tables with the same
name. There is also a public schema for shared tables. Table/index
creation can be restricted by removing permissions on the public
schema.
|
| Table
type |
- Firebird: a derived
table (not to be confused with "temporary tables")
is one that is created on-the-fly using the SELECT statement, and
referenced
just like a regular table or view. Derived tables exist in memory and
can only be referenced by the outer SELECT in which they are created.
* SQL Standard: A derived table is a table derived
directly or indirectly from one or more other tables by the evaluation
of a whose result has an element type that is a
row type. The values of a
derived table are derived from the values of the underlying tables when
the is evaluated.
|
Tablespaces
|
1) By using tablespaces, an administrator can control the disk
layout of an installation. This is
useful in at least two ways. First, if the partition or volume on which
the cluster was initialized runs out of space and cannot be extended, a
tablespace can be created on a different partition and used until the
system can be reconfigured.
2) Second, tablespaces allow an administrator to use knowledge
of the usage pattern of database objects to optimize performance. For
example, an index which is very heavily used can be placed on a very
fast, highly available disk, such as an expensive solid state device.
At the same time a table storing archived data which is rarely used or
not performance critical could be stored on a less expensive, slower
disk system.
- MySQL starting from
version 4.1.1, you can store each InnoDB table and its
indexes into its own file. This feature is called ``multiple
tablespaces''
because in effect each table has its own tablespace.
- MaxDB: MaxDB do no
use dedicated tablespaces. But if a volume is added, existing tables
and indices will use that additional space. This means that 1) is
applicable to MaxDB. 2) is no applicable as all volumes are considered
of equal performance and the administrator cannot decide on which
volume the pages for a specific table will be stored.
- PostgreSQL since version
8.0 (January 2005) support Tablespaces
|
License
|
- Firebird:
New code modules added to Firebird are licensed under the Initial
Developer's Public License (IDPL).
The original modules
released by Inprise are licensed under the InterBase
Public License v.1.0. Both licences are modified versions of the Mozilla
Public License v.1.1
|
| Other interesting Database comparisons |
- Linux-mag: Comparing Databases
-> http://www.linux-mag.com/2002-06/databbase_01.html
- http://www.mysql.com/doc/M/y/MySQL-PostgreeSQL_features.html
- http://www.mmlabx.ua.es/mysql-postgres.htmml (Spanish)
- http://openacs.org/philosophy/why-not-mysqql.html (2001)
- http://phd.pp.ru/Software/SQL/PostgreSQL-vvs-MySQL.html (2001)
- Linuxplanet
- Apachetoday
- http://www.mysql.com/information/crash-me.php
Benchmarkings Tools
- The Open
Source Database Benchmark
- http://www.mysql.com/information/benchmarkks.html
- benchw (sourceforge)
|
