Обсуждение: New FAQ item
Here is a new FAQ item that tries to answer the question of how
PostgreSQL compares to other DBMS alternatives. Comments?
---------------------------------------------------------------------------
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, 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 it 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 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.
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.
--
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
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: > Here is a new FAQ item that tries to answer the question of how > PostgreSQL compares to other DBMS alternatives. Comments? > > --------------------------------------------------------------------------- > > 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, 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 it 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 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. > > 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. > > -- > 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
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
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."
> 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.
> 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."