postgre vs MySQL

On Tue, Mar 11, 2008 at 6:47 AM, rrahul <rahul.rathi@cognizant.com> wrote:

Hi,

I am a database professional but have never used Postgre. My client was
exploring the posiblity of using Postgre instead of Mysql and wnated to know
the comments from the community.
I waned you people you post your views on the following comparision points
1] Performance
2] Scalablity
3] community support
4] Speed
5] ease of use
6] robustness

I've used both pgsql and mysql (we prefer postgres, pgsql, or
PostgreSQL, but not postgre...) for quite some time. My experience
has been that under real production type load that postgresql is the
better database.

http://tweakers.net/reviews/657/6

the guys at tweakers appear to agree.

I like postgresql's community support, specifically the mailing lists.
I've gotten answers directly from the guy who wrote the code when
I've had problems in the past, and they're very willing to listen to
users who have reasonable requests for improvements, and some not so
reasonable but useful ones as well.

I would say postgresql is significantly more robust than MySQL. The
only time I've ever lost data in a pgsql database has been when the OS
stomped on it (think cheap crappy RAID controllers). PostgreSQL is
designed for 24/7 operation and quite good at it.

For some issues in both mysql and pgsql, take a look at these two links:

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

Look at which gotchas have been addressed in later versions, and you
can see a pattern. PostgreSQL gotchas that can affect data
correctness have mostly been stamped out, but there are plenty of
mysql ones that have been there since 3.23 and haven't gone away.

rrahul wrote:

Hi,

I am a database professional but have never used Postgre.

PostgreSQL, or Postgres rather than Postgre.

My client was
exploring the posiblity of using Postgre instead of Mysql and wnated to know
the comments from the community.
I waned you people you post your views on the following comparision points
1] Performance
2] Scalablity
3] community support
4] Speed
5] ease of use
6] robustness

People here are bound to prefer PostgreSQL to MySQL, otherwise you'd
find us on a MySQL list. What sort of database were you looking at? On
what operating system? With what hardware?

Any major clients of the two.
You can add you own points too.

You might want to look at the website http://www.postgresql.org/
particularly the "featured users", "more quotes" links on the front
page. If you click the "About" link on the front page almost all the
links down the left-hand side would be relevant.

Did none of this information meet your needs, or were you having trouble
finding it?

--
Richard Huxton
Archonet Ltd

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of rrahul
Sent: Tuesday, March 11, 2008 6:48 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] postgre vs MySQL

Hi,

I am a database professional but have never used Postgre. My client

was

exploring the posiblity of using Postgre instead of Mysql and wnated

to

know
the comments from the community.
I waned you people you post your views on the following comparision

points

1] Performance
2] Scalablity
3] community support
4] Speed
5] ease of use
6] robustness

Any major clients of the two.
You can add you own points too.

Consider the license differences for commercial use:

http://www.postgresql.org/docs/8.3/interactive/LEGALNOTICE.html

http://www.mysql.com/about/legal/licensing/

On Tue, 11 Mar 2008, rrahul wrote:

I waned you people you post your views on the following comparision points
1] Performance 2] Scalablity 4] Speed 6] robustness

These are all covered in more detail that you probably want at
http://www.postgresql.org/docs/techdocs.83

The quick summary is that MySQL is very fast when using its MyISAM engine,
which is prone to many data integrity issues. If you switch to the more
reliable InnoDB engine most of the performance advantage MySQL might have
goes away. There are also a couple of areas where PostgreSQL is almost
always faster: complex joins and scalability under a heavy transaction
load are two examples.

3] community support

It's not unheard of for someone who is really having a problem that looks
like a database bug to get one of the core PostgreSQL contributors poking
at their box to figure out what's going on. Meanwhile, MySQL can't even
get enough resources together to get their new version out the door (V5.1
has been lingering around since November of 2005), so there's little
developer capacity to spare to help users like the support you find on the
mailing lists here. I think if you poke around a bit you'll discover the
MySQL community has been rather unhappy with the number of bugs in MySQL
5.0 and 5.1. A good intro is
http://www.mysqlperformanceblog.com/2007/10/04/mysql-quality-of-old-and-new-features/

5] ease of use

MySQL has more applications aimed at making it easier to use the database
floating around. If all you want to build is a simple system, it's
probably got an edge there. Whether that's still true if you're building
something complicated enough that you can take advantage of some of the
more powerful PostgreSQL features MySQL doesn't have is certainly
debatable.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, Mar 11, 2008 at 11:55 AM, Richard Huxton <dev@archonet.com> wrote:

People here are bound to prefer PostgreSQL to MySQL, otherwise you'd
find us on a MySQL list. What sort of database were you looking at? On
what operating system? With what hardware?

I semi regularly post on the MySQL General Discussion list, and not
here (until now). The knowledge base is much deeper and there is much
more traffic here. MySQL has a very low barrier to entry, and allows
people to form very bad habits. Postgres has a superset of the MySQL
features.

I wish I worked in a Postgres environment.

--
Rob Wultsch

On Tue, Mar 11, 2008 at 12:32 PM, Greg Smith <gsmith@gregsmith.com> wrote:

3] community support

It's not unheard of for someone who is really having a problem that looks
like a database bug to get one of the core PostgreSQL contributors poking
at their box to figure out what's going on. Meanwhile, MySQL can't even
get enough resources together to get their new version out the door (V5.1
has been lingering around since November of 2005), so there's little
developer capacity to spare to help users like the support you find on the
mailing lists here. I think if you poke around a bit you'll discover the
MySQL community has been rather unhappy with the number of bugs in MySQL
5.0 and 5.1. A good intro is
http://www.mysqlperformanceblog.com/2007/10/04/mysql-quality-of-old-and-new-features/

An interesting point here is that because of the long delay in 5.1
some things have been attempted in 5.0 as performance enhancements
that broke things. See specifically the bug whereby with innodb an
order by DESC was actually turned into an order by ASC silently. It
was in the "production" version of 5.0 for several months if I recall
correctly.

That kind of change does NOT get into production versions of
postgresql. With a yearly release schedule, postgresql doesn't have
to put dodgy performance updates in a production release. When
something like that does happen, i.e. a bug or security fix goes
wrong, the immediate response I've seen from the pgsql hackers has
been amazingly fast.

On Tue, 11 Mar 2008, Scott Marlowe wrote:

That kind of change does NOT get into production versions of
postgresql. With a yearly release schedule, postgresql doesn't have
to put dodgy performance updates in a production release.

This is worth expanding on: PostgreSQL doesn't put *any* feature changes
in a production release. Once it's a stable release, only bug fixes are
applied. Any other way is madness.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, Mar 11, 2008 at 4:22 PM, Greg Smith <gsmith@gregsmith.com> wrote:

On Tue, 11 Mar 2008, Scott Marlowe wrote:

That kind of change does NOT get into production versions of
postgresql. With a yearly release schedule, postgresql doesn't have
to put dodgy performance updates in a production release.

This is worth expanding on: PostgreSQL doesn't put *any* feature changes
in a production release. Once it's a stable release, only bug fixes are
applied. Any other way is madness.

I'm really hoping Sun will put a stop to such behavior, but wonder if
they'll do anything at all.

Sadly, the worst problem with the behavior re mysql releases is that
it trains DBAs to NOT install updates. In fairness, I know quite a
few Oracle DBAs who won't install patches right away either.

Then they come to postgresql and run a release missing a year or more
of updates.

On Tue, Mar 11, 2008 at 3:31 PM, Dann Corbit <DCorbit@connx.com> wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of rrahul
Sent: Tuesday, March 11, 2008 6:48 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] postgre vs MySQL

Hi,

I am a database professional but have never used Postgre. My client

was

exploring the posiblity of using Postgre instead of Mysql and wnated

to

know
the comments from the community.
I waned you people you post your views on the following comparision

points

1] Performance

If you use InnoDB tables, and you should, then MySQL is actually
slower than PostgreSQL in most cases, and it gets worse the more load
you have and the more complex your DB is.

2] Scalablity

I've run databases with millions of rows with no problem on a multiway
system with 24 disks, and the performance was stellar.

3] community support

I have never found a mailing so willing and able to help than the
PostgreSQL mailing list. The folks here are the experts, and they
never turn a problem away that I have seen. They are insightful and
highly helpful. I only wish other products especially commercial ones
could rival this level of committment by the developers, give this
sort of support.

4] Speed

Like I said, as long as you don't try something stupid, PostgreSQL has
been very quick on all the projects I've used. Unless you make an
index that wont fit in RAM, and any database will dog on that. (you
might get a shock with count(*) but that's because postgresql cares
about getting the answer right, not guessing).

5] ease of use

They have excellent drivers for Python, Perl, Java, C, stored
procedures can be written in just about any popular language, and the
help screens in the command line app are very good.

6] robustness

In ten years of using PostgreSQL I've never seen it crash, Our entire
enterprise ran off PostgreSQL at our last company, and it was rock
solid, never skipping a beat.

Any major clients of the two.
You can add you own points too.

Consider the license differences for commercial use:

http://www.postgresql.org/docs/8.3/interactive/LEGALNOTICE.html

http://www.mysql.com/about/legal/licensing/

Like he said, PostgreSQL is really the only good choice for an open
source database that free. I don't understand why people use MySQL at
all to be honest.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

I'm really hoping Sun will put a stop to such behavior, but wonder if
they'll do anything at all.

Sadly, the worst problem with the behavior re mysql releases is that
it trains DBAs to NOT install updates. In fairness, I know quite a
few Oracle DBAs who won't install patches right away either.

It's worse than that: it also trains repackagers to not be too quick on
the draw to adopt a mysql update release. I can tell you that new mysql
updates don't get into Fedora, let alone RHEL, till they've been around
at least a month or two. That's not laziness on my part; that's the
burnt child shunning the fire.

regards, tom lane

On Tue, 11 Mar 2008, Tom Lane wrote:

I can tell you that new mysql updates don't get into Fedora, let alone
RHEL, till they've been around at least a month or two. That's not
laziness on my part; that's the burnt child shunning the fire.

That would make a great marketing quote: "Update to the latest MySQL and
relive the exciting experience of when you first touched a hot stove".

Wait, Dave asked me not to make comments like that anymore; sorry.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Tom Lane wrote:

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

I'm really hoping Sun will put a stop to such behavior, but wonder if
they'll do anything at all.

Sadly, the worst problem with the behavior re mysql releases is that
it trains DBAs to NOT install updates. In fairness, I know quite a
few Oracle DBAs who won't install patches right away either.

It's worse than that: it also trains repackagers to not be too quick on
the draw to adopt a mysql update release. I can tell you that new mysql
updates don't get into Fedora, let alone RHEL, till they've been around
at least a month or two. That's not laziness on my part; that's the
burnt child shunning the fire.

regards, tom lane

I view updates/patches of any kind like this, if ain't broke don't fix
it. I normally only update computers with security patches only after i
prove it don't destroy installs.

The email server we use, uses MySQL as the backend for calendars, tasks,
and contacts, i've never been happy with its performance or the
reliability. Seen mysql trash the database 3 times on dirty writes.
Talk about annoyed users when their task and calendar entries disappear.

Reading through the specs and what little i have played/experience with
MySQL I avoid it because data is not guaranteed to be consistent even
running InnoDB. I don't ever want to tell the data entry girls that
yesterdays work needs to be duplicated because the database took a dump.

performance is nice to have but no where near as important as making
sure the data is safe and clean.

On Tue, Mar 11, 2008 at 7:33 PM, Justin <justin@emproshunts.com> wrote:

I view updates/patches of any kind like this, if ain't broke don't fix it.
I normally only update computers with security patches only after i prove it
don't destroy installs.

But that's juast it. When a postgresql update comes out, it is
precisely because the database IS broken. A bug that might eat your
data or allow an attacker to get into your database are the kinds of
fixes, and the only kind really, that go into production pgsql
releases. I too wait a day or two to test it on a staging server, but
I've never had a pgsql update blow back in my face, and I've done an
awful lot of them.

i've had to many sleepless nights rolling back patches on other software
to just roll out patches.

I'm a wait and see guy on most things. If its security update and the
server is exposed to the internet i dig into that right away.

Now if patch fixes a problem about data integrity i also dig into the
detail to see if it affects my install if not i'll skip it.

this is just my personal experience with patches for other software.

Scott Marlowe wrote:

On Tue, 2008-03-11 at 06:47 -0700, rrahul wrote:

Any major clients of the two.
You can add you own points too.

Perhaps someone can comment on current MySQL backups procedures. I
believe that MySQL used to (still does?) require shutdown to be backed
up. I don't know whether this was true for all engines or whether it
might have been fixed. Having to shutdown a database to make a backup is
a non-starter for anything that other than a toy (or read-only)
databases.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Scott Marlowe wrote:

On Tue, Mar 11, 2008 at 7:33 PM, Justin <justin@emproshunts.com> wrote:

I view updates/patches of any kind like this, if ain't broke don't fix it.
I normally only update computers with security patches only after i prove it
don't destroy installs.

But that's juast it. When a postgresql update comes out, it is
precisely because the database IS broken. A bug that might eat your
data or allow an attacker to get into your database are the kinds of
fixes, and the only kind really, that go into production pgsql
releases. I too wait a day or two to test it on a staging server, but
I've never had a pgsql update blow back in my face, and I've done an
awful lot of them.

So you missed 8.1.7 then or weren't using those features at the very least?
You also didn't have the stats collector issue with 8.2.3, 8.2.4 took
quite some time to come out.
And remember the policy violation when 8.0 came out, we replaced the
buffer expiry algorithm with a patch release.

PostgreSQL is not perfect, but as you can see by the problems with 8.1.7
the next update was released very very quickly. Sometimes I fear we
pump up our status a little too far with the reliability and only
perfectly patched releases. The real key is what's the response when
things go wrong, because things will go wrong at some point. I think we
need to be careful because it's a much bigger fall the higher the
pedestal we put ourselves on.

Regards

Russell

On Wed, Mar 12, 2008 at 12:02 AM, Russell Smith <mr-russ@pws.com.au> wrote:

Scott Marlowe wrote:

On Tue, Mar 11, 2008 at 7:33 PM, Justin <justin@emproshunts.com> wrote:

I view updates/patches of any kind like this, if ain't broke don't fix it.
I normally only update computers with security patches only after i prove it
don't destroy installs.

But that's juast it. When a postgresql update comes out, it is
precisely because the database IS broken. A bug that might eat your
data or allow an attacker to get into your database are the kinds of
fixes, and the only kind really, that go into production pgsql
releases. I too wait a day or two to test it on a staging server, but
I've never had a pgsql update blow back in my face, and I've done an
awful lot of them.

So you missed 8.1.7 then or weren't using those features at the very least?
You also didn't have the stats collector issue with 8.2.3, 8.2.4 took
quite some time to come out.
And remember the policy violation when 8.0 came out, we replaced the
buffer expiry algorithm with a patch release.

Yeah, we went from 8.0.x (whatever was current at the time) to 8.2.4.
And I do test any update for a couple days before applying it. So
when something goes wrong with a release like 8.1.7 was, I suppose, I
get the next one and I'm good. I don't just throw updates at
production. But I've never been bitten by an update that was more
than a couple days old either.

And I remember the change in 8.0 in the cache control, and it
definitely caused me to be slow on updating at that time, to make sure
it worked. It was very well advertised though, so I don't feel like a
surprise was sprung upon me.

PostgreSQL is not perfect, but as you can see by the problems with 8.1.7
the next update was released very very quickly. Sometimes I fear we
pump up our status a little too far with the reliability and only
perfectly patched releases. The real key is what's the response when
things go wrong, because things will go wrong at some point. I think we
need to be careful because it's a much bigger fall the higher the
pedestal we put ourselves on.

Agreed. I do think though that the pg developers have gotten much
much better about such things as time has gone by. I don't get the
feeling MySQL has. The difference is very much in how one handles
one's mistakes, and in that arena, I feel like pgsql has fewer in
production releases and they fix them much quicker, which is a
combination I can live with.

On Wed, 12 Mar 2008, Russell Smith wrote:

Scott Marlowe wrote:

I too wait a day or two to test it on a staging server, but
I've never had a pgsql update blow back in my face, and I've done an
awful lot of them.

So you missed 8.1.7 then or weren't using those features at the very
least? You also didn't have the stats collector issue with 8.2.3, 8.2.4
took quite some time to come out...PostgreSQL is not perfect, but as you
can see by the problems with 8.1.7 the next update was released very
very quickly.

The stats collector one made my life miserable for quite some time. But
that was all part of a major upgrade that happened to contain a
performance regression. The problem had been there since 8.2.0, and major
version releases always come with new bugs in the new features. I know I
caught it in release validation and held off upgrades until it was dealt
with.

I think what Scott was suggesting is that it's generally safe to apply
minor revision updates and expect that you'll have less bugs afterwards
than you'd have if you didn't apply the update. 8.1.7 was out for only
two days before the 8.1.8 fix came out; only the most aggressive upgrade
plan would have been bit by that.

If you look at the link I passed along before, you'll see the difference
with MySQL is that they've been abusing their customers with minor point
releases that try to add new features. Instead some of these introduce
functional regressions, which often hang around for a whole long longer
than two days after being noticed (this isn't even considering the delays
before those fixes make their way back into the open source product, some
only even go to paying customers). Sure, the PG stats bug was around for
five months before correction, but it was just a performance issue that
only showed up under limited circumstances and once it was reported it got
squashed fairly quickly.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

You can make a backup in MySQL in several ways:

1) Using mysqldump;
2) Lock tables and copy their files one-by-one (MyISAM-only);
3) Shutdown server and copy all files (can be a slave in a replicated
setup);
4) Using InnoDB hot backup (commercial tool);

On 3/12/08, Reece Hart <reece@harts.net> wrote:

On Tue, 2008-03-11 at 06:47 -0700, rrahul wrote:

Any major clients of the two.
You can add you own points too.

Perhaps someone can comment on current MySQL backups procedures. I
believe that MySQL used to (still does?) require shutdown to be backed up. I
don't know whether this was true for all engines or whether it might have
been fixed. Having to shutdown a database to make a backup is a non-starter
for anything that other than a toy (or read-only) databases.

-Reece

-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/