Advantages of PostgreSQL over MySQL 5.0

On Mar 22, 2006, at 6:06 AM, Jimbo1 wrote:

lab (who uses MySQL to manage their terabyte data warehouse) said, "We
chose MySQL over PostgreSQL primarily because it scales better and has
embedded replication.".".

The "one size fits all" style replication. What if it doesn't suit
your needs? And as for scalability, I have to doubt the knowledge of
the person making that claim....

If any PostgreSQL devotees on this group can comment on the above and
its accuracy/inaccuracy, I'd really appreciate it.

Basically you need to decide what you need your DB to do, then decide
what kind of licensing you want, then decide which product fits your
bill. If all you're doing is simple store/fetch then mysql will do
(heck, even SQLite will do). If you need complex things like
triggers, foreign keys, etc. then think again about using mysql...
even if they check off those as features, you need to evaluate their
stability and speed.

Jimbo1 wrote:

"With MySQL, customers across all industries are finding they can
easily handle nearly every type of database workload, with performance
and scalability outpacing every other open source rival. As Los Alamos
lab (who uses MySQL to manage their terabyte data warehouse) said, "We
chose MySQL over PostgreSQL primarily because it scales better and has
embedded replication.".".

If any PostgreSQL devotees on this group can comment on the above and
its accuracy/inaccuracy, I'd really appreciate it.

That's exactly what it is "propoganda", I can think of two really high
profile Postgresql installs that have recently been discussed:

1. The Wisconsin Court System, search the archives for a recent post
about this.
2. The entire .org and .info domains are stored in a Postgresql database.

I am sure there are many more.

Postgresql is also much more flexible than Mysql, for example with Mysql
you are restricted to using one proc langauge, while with PG you can
choose from many that might fit your needs or solve a specific problem.
Using PLperl for example you can easily write functions that send
emails, connect to socket servers etc etc.

Mysql also does not seem to have system tables that are accessible to
the developer, you have to use their information schema or the odd SHOW
syntax which is not flexible at all.

Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql

Tony Caduto wrote:

Jimbo1 wrote:

"With MySQL, customers across all industries are finding ...

If any PostgreSQL devotees on this group can comment on the above and
its accuracy/inaccuracy, I'd really appreciate it.

That's exactly what it is "propoganda", I can think of two really high
profile Postgresql installs that have recently been discussed:

1. The Wisconsin Court System, search the archives for a recent post
about this.
2. The entire .org and .info domains are stored in a Postgresql database.

I am sure there are many more.

Whenever this kind of question comes up, I always enjoy re-reading
(Caution: Flame War Impending!) the "MySQL Gotchas" at

http://sql-info.de/mysql/gotchas.html

even though that is getting dated.

-- BMT

Jimbo1 wrote:

Hello there,

I'm a freelance Oracle Developer by trade (can almost hear the boos now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.

It is out of the question for me to use Oracle, although I am a
(biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
route, and to that end I'm looking at either MySQL or PostgreSQL.

If Oracle is out of the question, so is MySQL. The technology that makes
MySQL even reasonably close to production OLTP quality is owned by
Oracle ;) (Innodb and BDB).

Sincerely,

Joshua D. Drake

Jimbo1 wrote:

Hello there,

I'm a freelance Oracle Developer by trade (can almost hear the boos now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.

It is out of the question for me to use Oracle, although I am a
(biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
route, and to that end I'm looking at either MySQL or PostgreSQL.

Regarding MySQL, I've been put off by Oracle's recent purchase of
InnoDB and realise this could badly impact the latest version of the
MySQL database. I can almost hear Larry Ellison's laughter from here
(allegedly)! I've also been put off by the heavy marketing propaganda
on the MySQL website.

I use Oracle at work and PostgreSQL for personal projects, and I think you'll find that PostgreSQL is the more feature-complete (or "Oracle-like") database. There are definitely situations Oracle comes out ahead, but for a website I doubt you'll find them. Also, the syntax between the two is more closer to the standard (PostgreSQL is actually better in this) than MySQL. Postgres has Pl/PgSQL, which is close enough PlSQL to not cause any problems.

Jeff

On Wednesday 22 March 2006 03:06, Jimbo1 wrote:

Hello there,

I'm a freelance Oracle Developer by trade (can almost hear the boos now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.

I've built many sites based on PostgreSQL. Originally,like most, I started
with MySQL, but after I discovered PG in about 2000, I've switched all
development to it, and have never looked back. I have "enterprise" systems
developed with PostgreSQL with 500 users, 50 online at a time, > 100 database
tables. Although the data sample is still not that impressive, (71 MB sql
file with pg_dump) the database itself is quite complex, with multiple
foreign keys in a single table being the norm.

It's just been a dream. It's solid, reliable, and virtually always behaves as
expected.

My only caveat is that occasionally, you really have to watch the use of
indexes. I had one query (nasty, with 7-8 tables involved in a combined
inner->outer->inner join) that was taking some 20 seconds to execute. Just
changing the order of some of the tables in the query, without logically
changing the result at all, dropped that time down to < 50 ms!

Regarding MySQL, I've been put off by Oracle's recent purchase of
InnoDB and realise this could badly impact the latest version of the
MySQL database. I can almost hear Larry Ellison's laughter from here
(allegedly)! I've also been put off by the heavy marketing propaganda
on the MySQL website.

Perhaps the single thing I most like about PostgreSQL is the feeling that "it
can't be taken away from me". The license is sufficiently open, and the
product is sufficiently stable, that I don't ever wonder if I'm "compliant"
or "paid up", nor do I wonder if my growth will be particularly limited
anywhere in the forseeable future.

"With MySQL, customers across all industries are finding they can
easily handle nearly every type of database workload, with performance
and scalability outpacing every other open source rival. As Los Alamos
lab (who uses MySQL to manage their terabyte data warehouse) said, "We
chose MySQL over PostgreSQL primarily because it scales better and has
embedded replication.".".

PostgreSQL has replication, as well. From what I've read, it's probably about
on par with MySQL in terms of manageability and reliability.

But, truthfully, having dealt with database replication, it's a PAIN IN THE
ARSE and very unlikely worth it. In fact, systems that I've worked on that
included replication are generally less reliable than those that simply do a
dump/copy every hour or two, due to the increased management headaches and
niggling problems that invariably seem to occur.

Consider replication if the cost of a full-time DB Admin is justified by
saving perhaps a few hours of uptime per year. If so, go for it. Be honest
about it - most people grossly overestimate the actual cost of few hours of
downtime every other year.

If any PostgreSQL devotees on this group can comment on the above and
its accuracy/inaccuracy, I'd really appreciate it.

PG does constraints wonderfully. It's performance is midline with simple
schemas. It handles very complex schemas wonderfully, and, with a little
tuning, can make very effective use of memory to speed performance.

My $0.02. Cheers!

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

...I can think of two really high
profile Postgresql installs that have recently been discussed:

1. The Wisconsin Court System, search the archives for a recent post
about this.
2. The entire .org and .info domains are stored in a Postgresql database.

I am sure there are many more.

Yup, Sony Online Entertainment springs to mind. They just selected (and
invested in) EnterpriseDB. EnterpriseDB is, at it's core, PostgreSQL but
it has been altered in a number of ways - primarily to enhance Oracle
compatibility so that PG can be more of a drop-in replacement for
programs that require Oracle.

From their press release:
"EnterpriseDB Advanced Server is the clear open source database choice
because of its PostgreSQL foundation, compatibility with Oracle and the
support of the EnterpriseDB team,“ said Christopher Yates, vice
president of technology at Sony Online Entertainment."

Their whole press-release is at:
http://www.enterprisedb.com/news_events/press_releases/03_20_06b.do

Cheers,
Steve

jd@commandprompt.com ("Joshua D. Drake") writes:

Jimbo1 wrote:

Hello there,
I'm a freelance Oracle Developer by trade (can almost hear the boos
now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.
It is out of the question for me to use Oracle, although I am a
(biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
route, and to that end I'm looking at either MySQL or PostgreSQL.

If Oracle is out of the question, so is MySQL. The technology that
makes MySQL even reasonably close to production OLTP quality is
owned by Oracle ;) (Innodb and BDB).

Combine that with the consideration that there is a real paucity of
performance results involving the "production OLTP quality"
subsystems.

The traditional claims concerning MySQL being "way faster" relate to
its use with the "definitely not production OLTP quality" MyISAM
engine.

And it's pretty needful to be even a bit more specific than that...

MySQL will be way faster than anything else if you have applications
designed to specifically harness its strengths, namely...

- Single user doing a stream of MyISAM updates

(If there are multiple connections, they quickly "butt heads"
on MyISAM table locks, so that >1 user suffers *badly*)

- Performing heavy loads involving often creating fresh connections
for each query, and submitting small, fairly trivial, select-one-row
query requests

If any of the assumptions there change, such as:
- Having multiple concurrent updating processes, or
- Submitting complex queries,
the "advantage" can pretty quickly evaporate.

PostgreSQL is likely to be way slower if you submit streams of little
queries, each an independent transaction...

- If multiple queries group into a single transaction, some "slowness"
will go away;

- The more complex the queries, the likelier that the sophisticated
query planner and optimizer in PostgreSQL will win out;

- The larger the number of concurrent update processes, the greater
the likelihood that MVCC allows PostgreSQL to chug along at speed
whilst the MyISAM table locks drag things to a halt...
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/lisp.html
Rules of the Evil Overlord #69. "All midwives will be banned from the
realm. All babies will be delivered at state-approved
hospitals. Orphans will be placed in foster-homes, not abandoned in
the woods to be raised by creatures of the wild."
<http://www.eviloverlord.com/&gt;

Jimbo1 wrote:

Hello there,

Regarding MySQL

Hi Jimbo,

As I'm sure you've asked the same question of the MySQL folks, can you tell
us what they've said about "us"? I guess it's not just idle curiosity (90%
though), but it might give us some pointers about how to improve either our
marketing, implementation or both.

Cheers

Michael

PS For my tuppeny ha'penny's worth, and having used both MySQL and PGSQL for
a web-app I'm developing, I've been very impressed with PGSQL since making
the switch from MySQL (4.0.15).

On Wed, 2006-22-03 at 11:34 -0800, Benjamin Smith wrote:

On Wednesday 22 March 2006 03:06, Jimbo1 wrote:

Hello there,

I'm a freelance Oracle Developer by trade (can almost hear the boos now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.

I've built many sites based on PostgreSQL. Originally,like most, I started
with MySQL, but after I discovered PG in about 2000, I've switched all
development to it, and have never looked back. I have "enterprise" systems
developed with PostgreSQL with 500 users, 50 online at a time, > 100 database
tables. Although the data sample is still not that impressive, (71 MB sql
file with pg_dump) the database itself is quite complex, with multiple
foreign keys in a single table being the norm.

It's just been a dream. It's solid, reliable, and virtually always behaves as
expected.

My only caveat is that occasionally, you really have to watch the use of
indexes. I had one query (nasty, with 7-8 tables involved in a combined
inner->outer->inner join) that was taking some 20 seconds to execute. Just
changing the order of some of the tables in the query, without logically
changing the result at all, dropped that time down to < 50 ms!

Regarding MySQL, I've been put off by Oracle's recent purchase of
InnoDB and realise this could badly impact the latest version of the
MySQL database. I can almost hear Larry Ellison's laughter from here
(allegedly)! I've also been put off by the heavy marketing propaganda
on the MySQL website.

Perhaps the single thing I most like about PostgreSQL is the feeling that "it
can't be taken away from me". The license is sufficiently open, and the
product is sufficiently stable, that I don't ever wonder if I'm "compliant"
or "paid up", nor do I wonder if my growth will be particularly limited
anywhere in the forseeable future.

"With MySQL, customers across all industries are finding they can
easily handle nearly every type of database workload, with performance
and scalability outpacing every other open source rival. As Los Alamos
lab (who uses MySQL to manage their terabyte data warehouse) said, "We
chose MySQL over PostgreSQL primarily because it scales better and has
embedded replication.".".

PostgreSQL has replication, as well. From what I've read, it's probably about
on par with MySQL in terms of manageability and reliability.

But, truthfully, having dealt with database replication, it's a PAIN IN THE
ARSE and very unlikely worth it. In fact, systems that I've worked on that
included replication are generally less reliable than those that simply do a
dump/copy every hour or two, due to the increased management headaches and
niggling problems that invariably seem to occur.

Consider replication if the cost of a full-time DB Admin is justified by
saving perhaps a few hours of uptime per year. If so, go for it. Be honest
about it - most people grossly overestimate the actual cost of few hours of
downtime every other year.

You can dump a running DB. Unless you have a hardware failure you should
not require any down time. I have been running a PG database that is
backed up every day, and has been been running for 5 years with one
5 min interruption when the server was moved to a new rack.

I am in the process of building a replacement machine, because the
hardware is bound to fail sometime, and it will be nice to upgrade
the OS. Unfortunately the base program I heavily customized to
put the collected data directly into PostgreSQL is no longer
maintained, so I need to build a whole new management and client
interface system around the new program, that has native support
for PostgreSQL but uses a significantly different table system.

If any PostgreSQL devotees on this group can comment on the above and
its accuracy/inaccuracy, I'd really appreciate it.

PG does constraints wonderfully. It's performance is midline with simple
schemas. It handles very complex schemas wonderfully, and, with a little
tuning, can make very effective use of memory to speed performance.

MySQL also does not properly Support NULL, has glaring errors in the
scope of some data types and does not have robust support for many of
the data types I use on a regular basis.

If you are only interested in varchar and blobs MySQL may have a small
advantage. If you need proper support for NULL and/or robust data
types with proper scope handling, MySQL would not serve you well. The
performance and features of MySQL also depend on the type of table you
use and if your needs change you need to dump/drop/create/restore with
the type of table you require later on. PostgreSQL has one table type
and has well developed locking mechanisms that allow a table to be
dumped even when in use, where MySQL locks the whole table while it
is being dumped.

I have used both DB's depending on the requirements of the project, but
by far prefer PostgreSQL for anything that is not overtly simple. I
find that with MySQL the programmer has to take more precautions to
ensure proper variable scope and NULL handling work as expected, or
weird bugs crop up once in a while.

On Wed, 2006-03-22 at 05:06, Jimbo1 wrote:

Hello there,

I'm a freelance Oracle Developer by trade (can almost hear the boos now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.

It is out of the question for me to use Oracle, although I am a
(biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
route, and to that end I'm looking at either MySQL or PostgreSQL.

We use both where I work, for different things. We also use Oracle for
our transactional engine.

I submit bug reports for both PostgreSQL and MySQL. I've asked for help
for both databases. The PostgreSQL users and developers are
unbelievably knowledgeable and helpful. When I first started using
PostgreSQL I found something that seemed odd, in that functional indexes
couldn't accept constant arguments, only column names (this was with 6.5
or 7.0 or something like that) and within a few hours Peter Eisenstraut
(I'm sure I spelled his name wrong there) had posted a work around for
me. I've found a few bugs here and there. They've all resulted in
changes being made anywhere from very fast for serious bugs, to being
pipelined into the process for the next major release.

I've never had that kind of very fast response from MySQL. If feels
like those guys really have their hands full meeting all the
requirements for the next version to pay much attention to the smaller
bugs. For instance, this DDL in MySQL:

mysql> create table a (x int primary key) engine innodb;
Query OK, 0 rows affected (0.13 sec)

mysql> create table b (y int references a) engine innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into b values (10);
Query OK, 1 row affected (0.07 sec)

Executes without a warning or an error.

Now, I shouldn't be able to insert anything in b that's not referencing
an entry in a. and I used innodb tables. and I used ansi SQL, and I
got no errors. So how come my data's incoherent three seconds after
creating the tables the way the spec says should work? Simple. MySQL
only implements foreign keys if you do them this way:

create table b (y int, foreign key (y) references a(x)) engine innodb;

I.e. you have to declare fk constraints outside the column defs. Why?
Because innodb tables and transactions in general are kind of like
red-headed step-children, I guess. It's not gonna change. I files a
bug report on it and the answer was basically "yep, that's how it's
supposed to work". This flies in the face of MySQL's claims that they
are striving towards SQL specification compliance.

In closing, I found that with PostgreSQL I had to invest a fair bit of
time learning how databases were supposed to work, and the more I used
it, the happier and more rewarded I became.

With MySQL, it's really easy to get started. It's over time you start
thinking "maybe this thing isn't quite right in the head" with some of
the odd behaviour like the one I listed above. And it's just one of
many.

that said, for content management, MySQL is a GREAT database. It has
collation per column, something PostgreSQL only gets about halfway to
right now with collation classes. OTOH, when that gets added to
PostgreSQL, it will likely be done very well.

As for the load handling, anyone who benchmarks a database with a single
thread (which is what a LOT of people do, sadly) should never be allowed
to declare any database fast. A database that can do exactly one thing
at once fast is not really all that interesting. A database that can
stay on its feet with 1000+ users hammering away updating and inserting
and selecting and deleting is impressive as hell. And PostgreSQL is
quickly moving in that direction.

The whole "built in replication" argument smacks of intellectual
laziness to me. Read the bug reports on mysql.com about it. There are
scads of reports of it just stopping for no good reason. And how do you
bring it back online? Simple. shut down your entire application,
disconnect all users, and FILE COPY everything to the slave server.
Restart replication, and then reconnect all your users. This is
enterprise quality? I hope they come up with a better way than that.

With Slony, I can add and remove replicated sets on the fly, on a living
database system, and the user never even notices.

I can use Point in time Recovery in Postgresql, a feature MySQL
currently lacks, and one I consider more useful than real time
replication for distaster recovery anyway. After all, replication can't
save you from "delete from tablename" with no where clause. Again, I
can set up PITR replication on the fly, without shutting down the master
database for even a second.

Lastly, try creating a test app of some kind that runs a lot of queries
(read and write both) on MySQL and PostgreSQL. Turn it on, let it
settle and place a heavy load on both machines. Pull the plug.
Assuming PostgreSQL is running on proper hardware (SCSI drives, RAID
controller with BBU, etc) it will come right back up.

MySQL, if it's running on myisam files, will be trashed. You may spend
quite alot of time getting it back up, and it will be missing data, and
if you have related tables, you will most certainly have orphans now.

Innodb tables should survive. But will they still be in MySQL in a
year? who knows?

I suggest someone put rubber on the road there and test both, including
possibly destructive tests like the power plug being pulled several
times. See how they both handle a simulation of what load you think
you'll have. See how much faster each gets when you move it from a
single CPU machine with 512 Meg ram and a single hard drive to an 8 way
Opteron with 16 gigs ram and a 12 drive RAID 1+0 drive set.

Now is the time to do it, before you've "married" your company to one or
the other, and found it wanting. Waving your hands around magically
declaring one better than the other gets you no where.

Hi James :

I am mysql user for 5 years. An a this time i am trying to go out from
mysql.

What i am living mysql?
Becouse my php bases systems require some features that mysql is
inplementing in the vercion 5.0, like store procedures and functions,
trrigers, transaction, views, and some others features.
Dou you now how to lock a counter table to users have no access to this
table mysql ?

Table two field, identerprise and counter

Mysql, to lock write, you have to lock entire table for write.

LOCK TABLES co_nro_notas WRITE.

SELECT counter
FROM co_nro_notas
WHERE identerprise = 1

Update co_nro_notas
SET counter = counter + 1

UNLOCK TABLES

This is a problem when do you have two or more enterprise runing in the same
database, an each with more than 10 users.

In postgresql each enterprise have your own record, and eachone can lock his
record.

You use a SELECT with a FOR UPDATE.

Try postgresql, is better than mysql.

I am dice to migrate all my systems to postgresql.

PD:

I try postgresql in 2002, but a this time postgresql does not have windows
vercion, this is the reason for use mysql,
have not other reason.

Alejandro M.S.
Porto Alegre
Brasil

-->-----Mensagem original-----
-->De: pgsql-general-owner@postgresql.org
-->[mailto:pgsql-general-owner@postgresql.org] Em nome de Jimbo1
-->Enviada em: quarta-feira, 22 de março de 2006 08:06
-->Para: pgsql-general@postgresql.org
-->Assunto: [GENERAL] Advantages of PostgreSQL over MySQL 5.0
-->
-->
-->Hello there,
-->
-->I'm a freelance Oracle Developer by trade (can almost hear
-->the boos now ;o)), and am looking into developing my own
-->Snowboarding-related website over the next few years.
-->Anyway, I'm making some decisions now about the site
-->architecture, and the database I'm going to need is
-->obviously included. If my site works out, I'm expecting
-->reasonably heavy traffic, so want a database that I'm
-->confident can cope with it.
-->
-->It is out of the question for me to use Oracle, although I am a
-->(biased) 'fan' of that RDBMS. I definitely need to go for a
-->cheaper route, and to that end I'm looking at either MySQL
-->or PostgreSQL.
-->
-->Regarding MySQL, I've been put off by Oracle's recent
-->purchase of InnoDB and realise this could badly impact the
-->latest version of the MySQL database. I can almost hear
-->Larry Ellison's laughter from here (allegedly)! I've also
-->been put off by the heavy marketing propaganda on the MySQL website.
-->
-->Recently, I've been taking a look at PostgreSQL, and am very
-->impressed by what I've read, although I've not yet
-->investigated the database first-hand. To cut to the chase, I
-->would be interested in anybody's feedback on the advantages
-->that PostgreSQL has over MySQL.
-->
-->Also, I've recently read the "Inside MySQL 5.0" (marketing
-->propaganda) document, and it makes the following claim:
-->
-->"With MySQL, customers across all industries are finding
-->they can easily handle nearly every type of database
-->workload, with performance and scalability outpacing every
-->other open source rival. As Los Alamos lab (who uses MySQL
-->to manage their terabyte data warehouse) said, "We chose
-->MySQL over PostgreSQL primarily because it scales better and
-->has embedded replication.".".
-->
-->If any PostgreSQL devotees on this group can comment on the
-->above and its accuracy/inaccuracy, I'd really appreciate it.
-->
-->Thanks in advance.
-->
-->James
-->
-->
-->---------------------------(end of
-->broadcast)---------------------------
-->TIP 6: explain analyze is your friend
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.385 / Virus Database: 268.2.6/287 - Release
-->Date: 21/3/2006
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.385 / Virus Database: 268.2.6/287 - Release
-->Date: 21/3/2006
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/287 - Release Date: 21/3/2006

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/287 - Release Date: 21/3/2006

I see a titanic advantage of PostgreSQL over MySQL: the license.

http://www.postgresql.org/about/licence
http://www.mysql.com/company/legal/licensing/commercial-license.html

Would you like to use the database for commercial purposes?

To my way of thinking, the Berkeley style license is the best of all
worlds.

Other than that, the Oracle/Sleepycat stuff is a little worrying.

But both products have advantages and disadvantages. If you build a
standards based interface to the database, then you can always switch
from one to the other {or some other DB altogether} with relative ease.

You'll find rabid fans in both camps, of course. I'm a PostgreSQL fan,
so anything I say should also be taken with a grain of salt. Simply
put, I like PostgreSQL better.

Chis Browne wrote:

PostgreSQL is likely to be way slower if you submit streams of little
queries, each an independent transaction...

When I get around to it I plan on debunking this ;). I recently did
extensive internal benchmarking of mysql 5.0 vs. postgresql 8.1 and
it's victories across the board with only a couple of exceptions, and
I have benchmarks to prove it. Im summary:

1. mysql is faster when it's query cache hit ratio is high
2. mysql opens connections much faster than pg, which is why we use pgpool
3. mysql sometimes wins where mvcc delete + insert can be kind of a
pain (*much* rarer than commonly thought)

While 'out of the box' postgresql is slower at select a,b,c from t
where k type queries wrt mysql, the performance advantage is
completely negated if you run those queries via prepared statements.

In postgresql, queries executed over the parameterized/prepared C api
are particularly fast...as much as a 70% speed reduction over vanilla
PQexec. Now, the lower level API and prepared statements are not
available for all applications, but when used they provide extremely
low-latency access.

Also,
1. pg can read off large result sets (50k records +) from the cache
much faster than mysql
2. pg has a generally better query optimizer, altough here and there
mysql scores a win
3. many other advantages you are already quite familiar with, mvcc, etc.

Basically, I am saying that the proverbial bread and butter queries
are not necessarily faster on mysql, just easier to get running fast,
if that makes sense. Now, I'm not trying to bash mysql (I was in
fact, quite impressed with 5.0) generally, but I really think the
claim that it is faster for a broad array of tasks is highly dubious.
pg just requires a little bit more specialized knowledge to get
running up to its level in some cases.

Merlin

On Wed, 2006-03-22 at 16:36 -0500, Merlin Moncure wrote:

Chis Browne wrote:

PostgreSQL is likely to be way slower if you submit streams of little
queries, each an independent transaction...

When I get around to it I plan on debunking this ;). I recently did
extensive internal benchmarking of mysql 5.0 vs. postgresql 8.1 and
it's victories across the board with only a couple of exceptions, and
I have benchmarks to prove it. Im summary:

1. mysql is faster when it's query cache hit ratio is high
2. mysql opens connections much faster than pg, which is why we use pgpool
3. mysql sometimes wins where mvcc delete + insert can be kind of a
pain (*much* rarer than commonly thought)

While 'out of the box' postgresql is slower at select a,b,c from t
where k type queries wrt mysql, the performance advantage is
completely negated if you run those queries via prepared statements.

In postgresql, queries executed over the parameterized/prepared C api
are particularly fast...as much as a 70% speed reduction over vanilla
PQexec. Now, the lower level API and prepared statements are not
available for all applications, but when used they provide extremely
low-latency access.

Also,
1. pg can read off large result sets (50k records +) from the cache
much faster than mysql
2. pg has a generally better query optimizer, altough here and there
mysql scores a win
3. many other advantages you are already quite familiar with, mvcc, etc.

Basically, I am saying that the proverbial bread and butter queries
are not necessarily faster on mysql, just easier to get running fast,
if that makes sense. Now, I'm not trying to bash mysql (I was in
fact, quite impressed with 5.0) generally, but I really think the
claim that it is faster for a broad array of tasks is highly dubious.
pg just requires a little bit more specialized knowledge to get
running up to its level in some cases.

Out of interest, what MySQL table type did you compare against? I
personally would be much more interested in such a comparison with
InnoDB tables than MyISAM.

--

Russ

As I'm sure you've asked the same question of the MySQL folks, can you tell
us what they've said about "us"? I guess it's not just idle curiosity (90%
though), but it might give us some pointers about how to improve either our
marketing, implementation or both.

Not yet asked them, but will pop the question over the next week. ;o)

Hello there,
I'm a freelance Oracle Developer by trade (can almost hear the boos now
;o)), and am looking into developing my own Snowboarding-related
website over the next few years. Anyway, I'm making some decisions now
about the site architecture, and the database I'm going to need is
obviously included. If my site works out, I'm expecting reasonably
heavy traffic, so want a database that I'm confident can cope with it.
It is out of the question for me to use Oracle, although I am a
(biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
route, and to that end I'm looking at either MySQL or PostgreSQL.

One aspect you might consider is that Postgres has an OGC SFS compliant
extension, PostGIS, wich is comparable to (or better than :-) Oracle
Spatial.

MySQL is in the process of developing such a capability, but the current
implementation is incomplete and will frequently return wrong answers. Not
really buggy (the reasons are clearly documented), but the stage of
development is such that I don't think it should have been released as
other than alpha software.

The ease of integrating web map server applications using data from
Postgres/PostGIS tables may be useful for such a site, where maps, road
information, photos accessed by clicking on a map, etc might be useful.

Applications such as mapserver can treat PostGIS enabled Postgres tables
as map layers, and allow spatial queries etc to be carried out as well as
normal non-spatial queries.

Cheers,

Brent Wood

"Jimbo1" <jamestheboarder@googlemail.com> writes:

Not yet asked them, but will pop the question over the next week. ;o)

Careful about "popping the question"... While good marriages have
come from that, so also have been some bad ones :-).
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/lsf.html
One good turn gets most of the blankets.

On Mar 22, 2006, at 10:08 PM, Scott Marlowe wrote:

Now, I shouldn't be able to insert anything in b that's not
referencing
an entry in a. and I used innodb tables. and I used ansi SQL, and I
got no errors. So how come my data's incoherent three seconds after
creating the tables the way the spec says should work? Simple. MySQL
only implements foreign keys if you do them this way:

Good lord, is that still true in 5.0??
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461