Hi,
I've used both PostgreSQL and MySQL on a Linux server and found both
setting up (using RPM) and maintaining them very easy (MySQL was slightly
easier to set up but I set it up after I was already proficient on
PostgreSQL, so perhaps comparing the ease of setup is unfair). Both are
well documented and each have very active mailing lists where list
subscribers are helpful and quick to respond. I have no idea how the
commercial support services compare in quality and price.
I have not used the more 'advanced' features, that you mentioned, of either
(such as: 'Triggers, Stored Procedures, User-Defined Functions') so can't
comment on them.
Backups on both are straightforward using pg_dump and mysqldump. Both of
these allow you to dump databases as files containing the relevant sql
commands to recreate the entire database quickly and easily.
Another responder mentioned a possible problem dumping MySQL databases
containing binary data, I have no idea whether this is an issue with
pg_dump as well, as I have no tables at all with binary data, but suspect
dumping these kind of backup files is generally inconsistent with binary
data (unless the dumping utilities do something clever, which I don't know
about, like uuencoding binary data). Anyway both servers can be backed up
by copying the actual database table files (on the local linux filesystem).
To achieve this the PostgreSQL server must be shut down (making it an
inferior backup technique to dumping which does not require a shutdown).
MySQL, however, has something called 'mysqlhotcopy' which will lock and
flush tables and copy the files using 'cp' and does not require a server
shutdown, it allows queries by different threads to continue, blind to the
backup in progress. --I must admit to being a bit wary of this as I don't
know enough about the underlying file systems and so personally have no
intention of using mysqlhotcopy. --Perhaps someone that knows more can
explain whether this is always safe to use and why??
Both PostgreSQL and MySQL have free GUI frontends (pgAdmin and Control
Center, respectively) that I have used on Windows 2000; both have been
adequate for my basic needs, EG. Quick database and table creation and
deletion, sql commands, and basic administration such as vacuuming
(PostgreSQL) and optimizing (MySQL). Of these PostgeSQL's is better (far
more features), while MySQL's is still at 0.9.3 beta (stable but feature poor).
My use of both has been low scale and is client program orientated using
.net and C# from Win2000. The ODBC drivers for Windows both function well.
The only annoying thing I can think of, from a programming perspective, is
MySQL's lack of a Boolean type - the manual says use TINYINT(1) which works
fine but is slightly annoying because of the extra type conversion needed
every time you use it. Apparently MySQL will be implementing the Boolean
type soon in accordance with whatever SQL standard requires it. Quite why
it still has not been implemented, even though MySQL is into version 4, I
have no idea - as a programmer I find this a staggering omission but
presumably they have their reasons and perhaps most people are happy with
TINYINT(1), but for clarity of code TINYINT(1) is inferior to a Boolean type.
I hope this helps,
..matthew
Jerry Apfelbaum wrote:
> Hello.
>
> I have been tasked with evaluating open source databases for a large
> upcoming project: e-commerce, B2B, high availability.
>
> The O/S is most likely to be Linux, although FreeBSD could possibly be used
> (lower probability).
>
> So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are
> possible candidates.
>
> Does anyone know why we should or should not use any of these? Does anyone
> know of other possibilities?
>
> I’d very much appreciate hearing your comments and recommendations.
>
> I have only recently started these evaluations. BTW, my own background is
> from the Oracle DBA world.
>
> MySQL is certainly popular and seems to have very good performance, but I am
> concerned that the lack of Triggers, Stored Procedures, User-Defined
> Functions, and Views (to a lesser degree ) will be a disadvantage.
>
> MaxDB "appears" to be more feature-rich and possibly more
> industrial-strength. How does its performance and stability compare to the
> others?
>
> Many Thanks.
> Jerry Apfelbaum
> Toronto
>
>
>
