New FAQ item

Hi,

What about available platforms, and some notes about porting pgsl to Windows .

(I'm watching this list, with this interest in mind).

Geraldo Lopes de Souza

Bruce Momjian wrote:

Bruce Momjian wrote:

Here is a new FAQ item that tries to answer the question of how
PostgreSQL compares to other DBMS alternatives. Comments?

Fine. Just a spelling nit-pick:

PostgreSQL runs it two modes.

Should surely be "runs *in* two modes".

:-)

--
Rachel

actually, via refint, you do have foreign keys.

True, but people just want Foreign Key to work from within their SQL
commands.

[SNIP]
In comparison to MySQL or leaner database systems, we are
slower because we have transaction overhead. We are built for
flexibility and features, not speed, though we continue to
improve performance through profiling and source code analysis.

id rephrase this to include 'inserts/updates' -- 6.5 is comparable to
mysql for selects, given the proper indexes.

Done.

id also stress that postgres supports (fully?) SQL92, triggers,
transactions, subselects, views, etc; these features are currently
unimplemented in mysql and msql ( does anyone still _use_ msql? 3hours
for a 2 table join was just a big nono ).

Done.

or maybe instead of comparing to mysql/msql, compare pgsql to oracle,
sybase, informix, et al. much cleaner comparison there, seeing
that mysql/msql dont support triggers, transactions, etc.

Yes, I agree we compare much more easily to the big guys, but that
information may not be getting out as much as it should, so we have to
include the obvious MySQL comparison.

New text attached. Copy on the web site too.

---------------------------------------------------------------------------

1.14) How does PostgreSQL compare to other DBMS's?

There are several ways of measuring software: features, performance,
reliability, support, and price.

Features
PostgreSQL has most features present in large commercial
DBMS's, like transactions, subselects, triggers, views, and
sophisticated locking. We have some features they don't have,
like user-defined types, inheritance, rules, and multi-version
concurrency control to reduce lock contention. We don't have
foreign key referential integrity or outer joins, but are
working on them for our next release.

Performance
PostgreSQL runs in two modes. Normal fsync mode flushes every
completed transaction to disk, guaranteeing that if the OS
crashes or looses power in the next few seconds, all your data
is safely stored on disk. In this mode, we are slower than most
commercial databases, partly because few of them do such
conservative flushing to disk in their default modes. In
no-fsync mode, we are usually faster than commercial databases,
though in this mode, an OS crash could cause data corruption.
We are working to provide an intermediate mode that suffers
from less performance overhead than full fsync mode, and will
allow data integrity within 30 seconds of an OS crash. The mode
is select-able by the database administrator.

In comparison to MySQL or leaner database systems, we are
slower on inserts/updates because we have transaction overhead.
Of course, MySQL doesn't have any of the features mentioned in
the Features section above. We are built for flexibility and
features, not speed, though we continue to improve performance
through profiling and source code analysis.

Reliability
We realize that a DBMS must be reliable, or it is worthless. We
strive to release well-tested, stable code that has a minimum
of bugs. Each release has at least one month of beta testing,
and our release history shows that we can provide stable, solid
releases that are ready for production use. We believe we
compare favorably to other database software in this area.

Support
Our mailing list provides a large group of developers and users
to help resolve any problems encountered. While we can not
guarantee a fix, commercial DBMS's don't always supply a fix
either. Direct access to developers, the user community,
manuals, and the source code often make PostgreSQL support
superior to other DBMS's. There is commercial per-incident
support available for those who need it. (See support FAQ
item.)

Price
We are free for all use, both commercial and non-commercial.
You can add our code to your product with no limitations,
except those outlined in our BSD-style license stated above.

On Fri, 9 Jul 1999, Bruce Momjian wrote:

[SNIP]
Features
PostgreSQL has most features present in large commercial
DBMS's, like transactions, subselects, and sophisticated
locking. We have some features they don't have, like
user-defined types, inheritance, rules, and multi-version
concurrency control to reduce lock contention. We don't have
foreign key referential integrity or outer joins, but are
working on them for our next release.

actually, via refint, you do have foreign keys.

[SNIP]
In comparison to MySQL or leaner database systems, we are
slower because we have transaction overhead. We are built for
flexibility and features, not speed, though we continue to
improve performance through profiling and source code analysis.

id rephrase this to include 'inserts/updates' -- 6.5 is comparable to
mysql for selects, given the proper indexes.

id also stress that postgres supports (fully?) SQL92, triggers,
transactions, subselects, views, etc; these features are currently
unimplemented in mysql and msql ( does anyone still _use_ msql? 3hours
for a 2 table join was just a big nono ).

or maybe instead of comparing to mysql/msql, compare pgsql to oracle,
sybase, informix, et al. much cleaner comparison there, seeing
that mysql/msql dont support triggers, transactions, etc.

[SNIP]

---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."

id rephrase this to include 'inserts/updates' -- 6.5 is comparable to
mysql for selects, given the proper indexes.

Is this tested, or do you just hope so?

id also stress that postgres supports (fully?) SQL92, triggers,

I believe there's still some way to go before SQL92 is fully supported.

Isn't outer joins, views with unions and more part of SQL92?

Now I am at trying to be annoying, how good is the ODBC / JDBC in
PostgreSQL? Can it measure up with MySQL? With Oracle?

On Sat, Jul 10, 1999 at 10:58:20PM -0400, Bruce Momjian wrote:

Yes, I agree we compare much more easily to the big guys, but that
information may not be getting out as much as it should, so we have to
include the obvious MySQL comparison.

The general topic of comparing against MySQL and other dbs came up on the
Zope mailing list a couple of days ago, in the context of Zope becoming
multithreaded, and what the db Adaptors needed to support that. I think
it'd be fruitful to share this post from there with this list. This is
from a developer who's familiar with the internals of MySQL, not just
an end user. One interesting nugget in the middle is a comparision of
query speed vs. Informix. He mentions that MySQL is faster, but only
if the selects are simple. As the queries become complex, the advantage
gets smaller. Perhaps postgres would be similar, with our (much maligned,
but IMHO, now pretty damn good) optimizer.

Ross

Date: Fri, 9 Jul 1999 12:51:31 -0400 (EDT)
From: Andy Dustman <XXXXXXXX@XXXXXXXXXXX>
Subject: RE: [Zope] Zope, performance and multithreading (beginner questio
ns)

On Fri, 9 Jul 1999, Rob Page wrote:

Anybody know how well MySQL performs with multiple threads? Last I
heard, it serializes database calls, which isn't exactly promising.

MySQL doesn't support transactions. Chris Petrilli here has the details
but I'm led to believe this is a fatal blow to MySQL's ability to _ever_
(at least until it does support txns) reliably support threaded usage.

The MySQL solution to doing transactions is to instead do table locking.
At least, this is what the docs say (see section 5.4). By not supporting
transactions, this makes the database 2-3x faster (claimed). An exerpt:

""" The current problem is actually ROLLBACK. Without ROLLBACK, you can do
any kind of COMMIT action with LOCK TABLES. To support ROLLBACK, MySQL
would have to be changed to store all old records that were updated and
revert everything back to the starting point if ROLLBACK was issued. For
simple cases, this isn't that hard to do (the current isamlog could be
used for this purpose), but it would be much more difficult to implement
ROLLBACK for ALTER/DROP/CREATE TABLE.

To avoid using ROLLBACK, you can use the following strategy:

1.Use LOCK TABLES ... to lock all the tables you want to access.
2.Test conditions.
3.Update if everything is okay.
4.Use UNLOCK TABLES to release your locks.

This is usually a much faster method than using transactions with possible
ROLLBACKs, although not always. The only situation this solution doesn't
handle is when someone kills the threads in the middle of an update. In
this case, all locks will be released but some of the updates may not have
been executed."""

But obviously, if you need transactions, or need to write a
cross-database-platform app, don't use MySQL. I have heard from someone
who has benchmarked MySQL against Informix (on Linux) that MySQL is about
2x faster, if the queries are simple. The more complicated the queries
are, the smaller this difference becomes.

Anyway, back to the original question: In general, MySQL does not
serialize database calls. With the non-standard LOW_PRIORITY keyword on
INSERT/REPLACE/UPDATE statements (REPLACE is like INSERT, except
pre-existing rows matching the primary key are replaced and there is no
error), the write is delayed until no other client is reading from the
table. With the non-standard DELAYED keyword on INSERT/REPLACE statements,
the query returns immediately but the data is not written out until later;
it goes into a delayed queue (with it's own thread). The delayed queue
handler tries to write delayed rows out en masse. This is intended for
things like logging where a lot of records are written on a regular basis,
but you don't want to delay the client.

Internally, the MySQL server uses table locks between server threads. The
client libraries are thread-safe, but there are some subtle caveats about
the connect call.

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

On Sat, Jul 10, 1999 at 10:58:20PM -0400, Bruce Momjian wrote:

Yes, I agree we compare much more easily to the big guys, but that
information may not be getting out as much as it should, so we have to
include the obvious MySQL comparison.

The general topic of comparing against MySQL and other dbs came up on the
Zope mailing list a couple of days ago, in the context of Zope becoming
multithreaded, and what the db Adaptors needed to support that. I think
it'd be fruitful to share this post from there with this list. This is
from a developer who's familiar with the internals of MySQL, not just
an end user. One interesting nugget in the middle is a comparision of
query speed vs. Informix. He mentions that MySQL is faster, but only
if the selects are simple. As the queries become complex, the advantage
gets smaller. Perhaps postgres would be similar, with our (much maligned,
but IMHO, now pretty damn good) optimizer.

I think you will find that the 6.5 optimizer is a watershed between a
fair optimizer and a now very good optimizer. Actually, it was always
very good, but there were bugs that make it only fair. With the bugs
gone, the code runs as it was intended, which is quite good.

I looked at the MySQL code once, and one things that struck me is that
the optimizer is in a single file! I didn't have to look any farther
than that too see it was limited. For example, our optimizer has
features like column optimization statistics that Informix did not have
until Informix version 5. We even have a genetic query optimizer(GEQO)
for really large joins, so we were always world-class in terms of
features.

However, in working with the optimizer with Tom Lane for 6.5, we found
MAJOR bugs that had been there from the Berkeley days that were killing
performance and optimizer choices.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Sun, 11 Jul 1999, Kaare Rasmussen wrote:

id rephrase this to include 'inserts/updates' -- 6.5 is comparable to
mysql for selects, given the proper indexes.

Is this tested, or do you just hope so?

tested against a 500,000 row table. pgsql seems to excell when that table
gains rows left and right, most likely due to the locking: when doing a
massive number of inserts from different processes along with a rather
large select, mysql had the tendency to crash ( client app lost connection
). postgres will slow down a little, but all the data eventually gets
into the table.

id also stress that postgres supports (fully?) SQL92, triggers,

I believe there's still some way to go before SQL92 is fully supported.

Isn't outer joins, views with unions and more part of SQL92?

hence the '(fully?)' bit. id have to look into the sql92 spec to see
what's not implemented (yet) in postgres... im fairly positive somebody
can answer this off the top of their head, however.

Now I am at trying to be annoying, how good is the ODBC / JDBC in
PostgreSQL? Can it measure up with MySQL? With Oracle?

try it and find out :) id like to know, but dont have any MS-Windows
machines here.

---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."