Thread: 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.

Thanks ,
Rahul.
--
View this message in context: http://www.nabble.com/postgre-vs-MySQL-tp15976517p15976517.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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.

>
>
>  --
>  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-general
>

"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

"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

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.

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.
 

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

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

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

Reece Hart 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


For a database of InnoDB tables, people tend to replicate the database,
and then backup the slave (unless the db is trivially small, in which
case, mysqldump).  For MyISAM, you can back it up hot, or do the same
replication thing as with InnoDB tables.

For larger and active MySQL installations, it's not uncommon to see a
MySQL database replicate to 2 or more slaves, and:

- use a slave to initialize any future additional slaves
- use a slave for backups
- promote a slave to master in case of master failure

There's the hot backup tool you can buy for InnoDB, but I've yet to meet
anyone who's actually used it.

Paul

On Wed, 12 Mar 2008 09:13:14 -0700
paul rivers <rivers.paul@gmail.com> wrote:

> For a database of InnoDB tables, people tend to replicate the
> database, and then backup the slave (unless the db is trivially

That recalled me the *unsupported* feeling I have that it is easier
to setup a HA replication solution on MySQL.

Pardon my ignorance of serious DBA jargon...

I'm thinking to something suited for load balancing the read as
highest priority in terms of performance *and* duplicate the write
across different boxes without the application layer has to know
about it as second priority in terms of performance...

I just would like to be contradicted and pointed to some viable
(easy?) setup for pgsql, so that I and other people will get rid of
this preconception if any.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Take a look at pgpool . http://pgpool.projects.postgresql.org/
2008/3/12, Ivan Sergio Borgonovo <mail@webthatworks.it>:
> On Wed, 12 Mar 2008 09:13:14 -0700
>  paul rivers <rivers.paul@gmail.com> wrote:
>
>  > For a database of InnoDB tables, people tend to replicate the
>  > database, and then backup the slave (unless the db is trivially
>
>
> That recalled me the *unsupported* feeling I have that it is easier
>  to setup a HA replication solution on MySQL.
>
>  Pardon my ignorance of serious DBA jargon...
>
>  I'm thinking to something suited for load balancing the read as
>  highest priority in terms of performance *and* duplicate the write
>  across different boxes without the application layer has to know
>  about it as second priority in terms of performance...
>
>  I just would like to be contradicted and pointed to some viable
>  (easy?) setup for pgsql, so that I and other people will get rid of
>  this preconception if any.
>
>
>  --
>  Ivan Sergio Borgonovo
>  http://www.webthatworks.it
>
>
>
>  --
>  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-general
>

Ivan Sergio Borgonovo wrote:
> On Wed, 12 Mar 2008 09:13:14 -0700
> paul rivers <rivers.paul@gmail.com> wrote:
>
> > For a database of InnoDB tables, people tend to replicate the
> > database, and then backup the slave (unless the db is trivially
>
> That recalled me the *unsupported* feeling I have that it is easier
> to setup a HA replication solution on MySQL.

Well, if you have a crappy system that cannot sustain concurrent load or
even be backed up concurrently with regular operation, one solution is
to write a kick-ass replication system.

The other solution is to enhance the ability of the system to deal with
concurrent operation.

We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr,
etc; and they all run on farms and farms of MySQL servers, "because
MySQL replication is so good".  I wonder if replication is an actual
_need_ or it's there just because the other aspects of the system are so
crappy.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Mar 12, 2008 at 10:15 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Ivan Sergio Borgonovo wrote:
>  > On Wed, 12 Mar 2008 09:13:14 -0700
>  > paul rivers <rivers.paul@gmail.com> wrote:
>  >
>  > > For a database of InnoDB tables, people tend to replicate the
>  > > database, and then backup the slave (unless the db is trivially
>  >
>  > That recalled me the *unsupported* feeling I have that it is easier
>  > to setup a HA replication solution on MySQL.
>
>  Well, if you have a crappy system that cannot sustain concurrent load or
>  even be backed up concurrently with regular operation, one solution is
>  to write a kick-ass replication system.
>
>  The other solution is to enhance the ability of the system to deal with
>  concurrent operation.
>
>  We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr,
>  etc; and they all run on farms and farms of MySQL servers, "because
>  MySQL replication is so good".  I wonder if replication is an actual
>  _need_ or it's there just because the other aspects of the system are so
>  crappy.

Reminds me of the saying that for each problem, there is a simple,
elegant solution that is completely wrong.  It amazes me that slony,
being basically a "bolt on" replication solution has given me much
fewer problems than MySQL replication which is known for silent
failures.  Slony is by no means perfect, but it is quite impressive as
both a replication tool and an upgrade tool.

An awful lot of the sites running on MySQL are running on it primarily
because it's what they started with, and now it's hard to switch
because their code is chock full of mysqlisms like "select field1,
field2 from table group by field1" and so on that no other database is
going to swallow without throwing an error.

On Wed, 12 Mar 2008 17:47:35 +0100
"jose javier parra sanchez" <jojapasa@gmail.com> wrote:

> Take a look at pgpool . http://pgpool.projects.postgresql.org/

I knew about it.

Giving a look at
http://pgpool.projects.postgresql.org/#restriction
it doesn't seem something that can be completely hidden to the
application layer.

I know that most of the problems (not all) arise from the fact that
pg has features that MySQL can just dream of... but it doesn't look
as something that is really transparent to the application layer.

Not that this should imply I consider easy to achieve such result...
I know it is far from being easy, just that it doesn't look as what I
was trying to describe.

So let me rephrase, in order of importance:
- something completely transparent at the application layer
- something that won't die if one of your boxes die
- something that will improve performances of reads
- something that won't suffer too much for replicating writes

At a first sight it looks as if pgpool can't boost stuff in pl*
functions.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Wed, 12 Mar 2008 10:26:21 -0700
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:

> On Wed, Mar 12, 2008 at 10:15 AM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > Ivan Sergio Borgonovo wrote:
> >  > On Wed, 12 Mar 2008 09:13:14 -0700
> >  > paul rivers <rivers.paul@gmail.com> wrote:
> >  >
> >  > > For a database of InnoDB tables, people tend to replicate the
> >  > > database, and then backup the slave (unless the db is
> >  > > trivially
> >  >
> >  > That recalled me the *unsupported* feeling I have that it is
> >  > easier to setup a HA replication solution on MySQL.
> >
> >  Well, if you have a crappy system that cannot sustain concurrent
> > load or even be backed up concurrently with regular operation,
> > one solution is to write a kick-ass replication system.
> >
> >  The other solution is to enhance the ability of the system to
> > deal with concurrent operation.
> >
> >  We keep hearing how great all those Web 2.0 sites are; Slashdot,
> > Flickr, etc; and they all run on farms and farms of MySQL
> > servers, "because MySQL replication is so good".  I wonder if
> > replication is an actual _need_ or it's there just because the
> > other aspects of the system are so crappy.

> Reminds me of the saying that for each problem, there is a simple,
> elegant solution that is completely wrong.  It amazes me that slony,
> being basically a "bolt on" replication solution has given me much
> fewer problems than MySQL replication which is known for silent
> failures.  Slony is by no means perfect, but it is quite impressive
> as both a replication tool and an upgrade tool.

I heard about Slony as well (dho!)...

I'm not complaining about anything... I do like PostgreSQL from a
programmer point of view and it makes my life easier.

I never pushed it to the limit I need replication, pooling etc...
Just as there is a myth out there that mysql outperform pgsql... I'm
here to testify there is another myth (?) that says that pg doesn't
have an easy (erm kick-ass) replication system.

I've no deep knowledge of Slashdot, Flickr or Google to say they
don't "high level" of data integrity/coherence as the one pg
offers and it is famous for... so I doubt they would hit the
limitations of systems like Slony or pgpool etc... etc... and maybe
they have no interest in the more advanced features and data
integrity pg has to offer...

> An awful lot of the sites running on MySQL are running on it
> primarily because it's what they started with, and now it's hard to
> switch because their code is chock full of mysqlisms like "select
> field1, field2 from table group by field1" and so on that no other
> database is going to swallow without throwing an error.

For what is worth I mostly share your opinion... just Google makes me
wonder...

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

--- Greg Smith <gsmith@gregsmith.com> wrote:

> 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).

This is something I noticed too when looking at MySQL and postgres.
The frequency of bug fixes and features, some coming over pretty
quickly from the community release of MySQL scared me.


      ___________________________________________________________
Rise to the challenge for Sport Relief with Yahoo! For Good

http://uk.promotions.yahoo.com/forgood/

Glyn Astill wrote:
>
> --- Greg Smith <gsmith@gregsmith.com> wrote:
>
> > 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).
>
> This is something I noticed too when looking at MySQL and postgres.
> The frequency of bug fixes and features, some coming over pretty
> quickly from the community release of MySQL scared me.

MySQL has incentives to _not_ make their community release
production-quality.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 12 Mar 2008 14:35:19 -0400 (EDT)
Bruce Momjian <bruce@momjian.us> wrote:


> > This is something I noticed too when looking at MySQL and postgres.
> > The frequency of bug fixes and features, some coming over pretty
> > quickly from the community release of MySQL scared me.
> 
> MySQL has incentives to _not_ make their community release
> production-quality.
> 

This thread is making my talk at MySQLCon very interesting.

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH2CP+ATb/zqfZUUQRAg78AKCNPJsFvLY7I/zxIXVZ9ndFtf9aMACdF7J+
QJQWDqDMLoj4lTiUIyGx1Ps=
=8Xe7
-----END PGP SIGNATURE-----

--- Bruce Momjian <bruce@momjian.us> wrote:

> Glyn Astill wrote:
> >
> > --- Greg Smith <gsmith@gregsmith.com> wrote:
> >
> > > 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).
> >
> > This is something I noticed too when looking at MySQL and
> postgres.
> > The frequency of bug fixes and features, some coming over pretty
> > quickly from the community release of MySQL scared me.
>
> MySQL has incentives to _not_ make their community release
> production-quality.
>

I mean features being pulled into the enterprise release that haven't
had much time to be tested even in the community release.


      ___________________________________________________________
Rise to the challenge for Sport Relief with Yahoo! For Good

http://uk.promotions.yahoo.com/forgood/

At 09:47 PM 3/11/2008, rrahul 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
>
>Any major clients of the two.
>You can add you own points too.

The "advantage" of MySQL is it looks good on paper with all the ticks
on the extensive feature list so it's easy to convince bosses to use it.

But the problem with MySQL is you often can't use all the "advertised
features" at the same time, some of them are mutually exclusive.

For example, with MySQL if you want fast single user selects or
insert speeds you use MyISAM tables, but if you start to need
transactions or high concurrency writes you have to use InnoDB which
is significantly slower.

Want to have a consistent backup of your MySQL database? For MyISAM
tables you have to lock all tables till the backup is done, and that
can affect performance a lot. OK so you use InnoDB. But when the time
comes to _restore_a multiGB innodb table, you might find innodb a bit
too slow. Worse, apparently "fun" things happen if someone halts the
restore process halfway ;).

Guess what happens if you have a mix of table types.

A "solution" of course is to have multiple database servers with a
master server replicating to a slave server that's used for backups,
and resort to backing up stuff in on-disk format - shutdown slave and
copy the files. This makes restoring faster. But after all this,
MySQL stops looking so easy right?

In fact such a multi database set up just to do proper backups and
restores resembles a bad implementation of Postgresql's MVCC :).

Basically with Postgresql, there's a lot less of this "If you want to
use Cool Feature A, you can't have Wonderful Feature B" thing.

I did a simple mass insert test (followed by backup with pg_dump or
mysqldump and restore ) and postgresql 8.1 is faster or as fast as
MySQL 5.0.26 with MyISAM tables, and faster than MySQL with innodb
tables- default packages from suse 10.2 with tuning done for MySQL
(increase of buffers etc) but postgresql is as per suse 10.2 defaults.

I use MySQL daily at my workplace, and it's not something I recommend
you use if you had a choice. In the old days (before version 6.x)
postgresql wasn't good, but postgresql is way ahead now.

Lastly, the other problem with MySQL is its Innodb and BDB stuff are
now owned by Oracle. While Oracle is not squeezing MySQL yet, who
knows what will happen a few years later.

Regards,
Link.

Glyn Astill <glynastill@yahoo.co.uk> writes:
> --- Bruce Momjian <bruce@momjian.us> wrote:
>> MySQL has incentives to _not_ make their community release
>> production-quality.

> I mean features being pulled into the enterprise release that haven't
> had much time to be tested even in the community release.

For the last year or so it's actually the other way around: they
put things into the for-pay version that have *not* hit the community
version yet.  So you pay to be a beta tester ;-)

            regards, tom lane

Alvaro Herrera wrote:
> Ivan Sergio Borgonovo wrote:
>
>> On Wed, 12 Mar 2008 09:13:14 -0700
>> paul rivers <rivers.paul@gmail.com> wrote:
>>
>>
>>> For a database of InnoDB tables, people tend to replicate the
>>> database, and then backup the slave (unless the db is trivially
>>>
>> That recalled me the *unsupported* feeling I have that it is easier
>> to setup a HA replication solution on MySQL.
>>
>
> Well, if you have a crappy system that cannot sustain concurrent load or
> even be backed up concurrently with regular operation, one solution is
> to write a kick-ass replication system.
>
> The other solution is to enhance the ability of the system to deal with
> concurrent operation.
>
> We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr,
> etc; and they all run on farms and farms of MySQL servers, "because
> MySQL replication is so good".  I wonder if replication is an actual
> _need_ or it's there just because the other aspects of the system are so
> crappy

"Kick-ass" imho really means "really simple to setup" and included as
part of the standard db.

There are all kinds of corner cases that can bite you with MySQL
replication. Offhand, I wager most of these (at least in InnoDB) result
from the replication "commit" status of a transaction is in the binlogs,
which is not the same as the InnoDB database commit status in the .ibd
files. Writing out binlog entries happens at a higher level than the
storage engine, and so it's not hard to imagine what can go wrong there.
There are a few my.cnf settings that let you really roll the dice with
data integrity based on this dichotomy, if you so choose.

In those high volume shops, imho replication is a requirement, but in
part to overcome technical limitations of MySQL. Or to phrase it from a
MySQL point of view, to do it the MySQL way. If you have 50-ish minutes,
this video by the YouTube people talks about their evolution with MySQL
(among many other things) :

http://video.google.com/videoplay?docid=-6304964351441328559

The summary from the video is:

- Start with a MySQL instance using InnoDB
- Go to 1-M replication, and use the replicants as read-only version.
- Eventually the cost of replication outweighs the gains, so go to
database sharding
- Keep 1-M replication within a shard group to allow easy backups of a
slave, some read-only use of the slaves, and a new master in case of
master failure (i.e. high availability)


Almost everyone finds MyISAM unworkable in large scale environments
because of the repairs necessary post-crash.


Big complaints about MySQL high-volume shops often, imho, come back to :

- You can only have so many active threads in the InnoDB storage engine
module at a time. See e.g.:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_thread_concurrency

- Auto_increment columns as pkeys in InnoDB tables are practically
required, yet severely limited scalability due to how a transaction
would lock the structure to get the next auto-increment (significantly
improved in 5.1)

- Shutting down a MySQL engine can take forever, due partly dirty page
writes, partly due to insert buffer merging. See:

http://dev.mysql.com/doc/refman/5.1/en/innodb-insert-buffering.html


There are other complaints you'd expect people to have, but don't seem
to get talked about much, because people are so used to (from my point
of view) working around them. For example, statistics on an InnoDB table
are calculated when the table is first accessed, but not stored
anywhere, so there are extra costs on database startup. The backup issue
with InnoDB has already been covered. Tablespace management in InnoDB
seems exceptionally primitive, and is helped somewhat by the
tablespace-per-table option. There are many more, again imho.

Paul

> Well, if you have a crappy system that cannot sustain concurrent load or
> even be backed up concurrently with regular operation, one solution is
> to write a kick-ass replication system.
>
Still, it would be nice to have a kick-ass replication system for PG,
too.  We've been toying with WAL archiving and backup db recovery, which
works pretty well it seems as it appears to support all of our tables
(not just those with an explicit primary key) and does the DDL stuff for
creating/altering/dropping tables, columns, etc.

The downside is that the backup is not operational in order to run even
run a SELECT against, and because it's asynchronous in nature, there's
always a window of data loss for transactions written to the WAL that
haven't been archived yet.

David

On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <rivers.paul@gmail.com> wrote:

>  - Auto_increment columns as pkeys in InnoDB tables are practically
>  required, yet severely limited scalability due to how a transaction
>  would lock the structure to get the next auto-increment (significantly
>  improved in 5.1)

Pretty sure they implemented the fix for that in an early 5.0 release.
 I remember chatting with Heikki Turri about it.

>  There are other complaints you'd expect people to have, but don't seem
>  to get talked about much, because people are so used to (from my point
>  of view) working around them. For example, statistics on an InnoDB table
>  are calculated when the table is first accessed, but not stored
>  anywhere, so there are extra costs on database startup. The backup issue
>  with InnoDB has already been covered. Tablespace management in InnoDB
>  seems exceptionally primitive, and is helped somewhat by the
>  tablespace-per-table option. There are many more, again imho.

I remember seeing something about some problems that using the
tablespace per table option on some mysql site... goes to look...
paraphrased from the Mysql Performance Blod...  Using the
innodb_file_per_table=1 setting really tends to work against you, as
you tend to get lots of bloated tables over time.  If all your innodb
tables are in the same file, then when one frees space, another can
use it.  with files per table, you can't recover space this way.


My real complaint with InnoDB is it's a red headed step child.  If
mysql supported only innodb, it would be a very different database,
and probably a bit simpler as well.  no need to worry about how you
state fk-pk relationships (currently column level references are
silently dropped for innodb OR myisam).  If there was a run time
switch that said "use only innodb and use syntax that's sane" I'd
probably be willing to test that out.

Scott Marlowe wrote:
> On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <rivers.paul@gmail.com> wrote:
>
>
>>  - Auto_increment columns as pkeys in InnoDB tables are practically
>>  required, yet severely limited scalability due to how a transaction
>>  would lock the structure to get the next auto-increment (significantly
>>  improved in 5.1)
>>
>
> Pretty sure they implemented the fix for that in an early 5.0 release.
>  I remember chatting with Heikki Turri about it.
>

Definitely not fixed until 5.1, in fact not until very recently (5.1.22) :

http://bugs.mysql.com/bug.php?id=16979

Anyway, enough of that for me.  It's a Postgres list, and my list of
MySQL complaints is far longer than my enthusiasm for documenting them.


Paul

On Donnerstag, 13. März 2008, Scott Marlowe wrote:
| My real complaint with InnoDB is it's a red headed step child.  If
| mysql supported only innodb, it would be a very different database,
| and probably a bit simpler as well.  no need to worry about how you
| state fk-pk relationships (currently column level references are
| silently dropped for innodb OR myisam).  If there was a run time
| switch that said "use only innodb and use syntax that's sane" I'd
| probably be willing to test that out.

To be fair, MySQL-5 has such a switch (kind of):

  http://dev.mysql.com/doc/refman/5.0/en/faqs-sql-modes.html

You can set the @@sql_mode variable to a value, that MySQL almost behaves
like standard SQL (I've not tested this by myself, though).

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

On Thu, Mar 13, 2008 at 1:25 AM, Thomas Pundt <mlists@rp-online.de> wrote:
> On Donnerstag, 13. März 2008, Scott Marlowe wrote:
>  | My real complaint with InnoDB is it's a red headed step child.  If
>  | mysql supported only innodb, it would be a very different database,
>  | and probably a bit simpler as well.  no need to worry about how you
>  | state fk-pk relationships (currently column level references are
>  | silently dropped for innodb OR myisam).  If there was a run time
>  | switch that said "use only innodb and use syntax that's sane" I'd
>  | probably be willing to test that out.
>
>  To be fair, MySQL-5 has such a switch (kind of):
>
>   http://dev.mysql.com/doc/refman/5.0/en/faqs-sql-modes.html
>
>  You can set the @@sql_mode variable to a value, that MySQL almost behaves
>  like standard SQL (I've not tested this by myself, though).

Sadly running in ansi mode only fixes about half the problems.  The
FK-PK syntax on a column level is still ignored in ansi sql mode, and
a lot of other issues are still there.  Admittedly, it does do things
like check ints for sane input values, and refuses to truncate
varchars silently if they're too large.  But that's the kind of thing
you really shouldn't have to be turning on in the first place.

At 11:37 AM 3/13/2008, Scott Marlowe wrote:

>I remember seeing something about some problems that using the
>tablespace per table option on some mysql site... goes to look...
>paraphrased from the Mysql Performance Blod...  Using the
>innodb_file_per_table=1 setting really tends to work against you, as
>you tend to get lots of bloated tables over time.  If all your innodb
>tables are in the same file, then when one frees space, another can
>use it.  with files per table, you can't recover space this way.

With separate files per table, the space returns to the filesystem if
you run "optimize table" (something like "vacuum full").

In contrast when you have a single 70GB file with all the tables, it
NEVER shrinks unless you drop the entire database and reload it. That
could take more time than you get from the Boss/Customer standing
behind you (and asking every 5 minutes - "Is it back up yet?").

Also if you do that huge file thing:
1) From my experience, deleting stuff from innodb tables doesn't free
space up to be used by other tables, you still need to run optimize table.
2) I suspect even if you do "optimize table", often fragmentation or
something happens so not all space can be reclaimed - so that huge
file will tend to grow faster than your data does.
3) Depending on how much free _usable_ space there actually is left
in that huge file, optimize table could cause the single huge file to
get bigger because it makes a copy of the entire table.
4) The times when most admins want to do "optimize table" are often
the very times where 2)+3) could cause major unhappiness ;).

If you use innodb_file_per_table=1, you have a better idea of how
much space each table takes up, so you can figure out which tables
you should start with first and schedule shorter periods of time to
run "optimize table" on each table.

That said, many times it's just postponing the inevitable - you
regularly get some pain (optimize locks the table), the pain
gradually increases as your tables get bigger :). Hopefully by the
time the pain is a lot, people would have come up with better alternatives.

Currently postgresql's "vacuum full" also locks the affected tables.
Does 8.3 vacuum full effectively make a copy of the entire table? How
much extra space will the various vacuums use while vacuuming?

Regards,

Link.

Thanks to all you wonderful people out their. I don't know if its your love
for Postgres or nepothism that makes it look far superior than mysql.
But why does the client list dosen't tell that?
I see Mysql bosting for Google,Yahoo, Alcatel......
What about Postgres the list is not that impressive.

Are their any major implementations that moved from Mysql to Postgres?
Howmany out their have done this or will advice to do that?

cheers,
Rahul.
--
View this message in context: http://www.nabble.com/postgre-vs-MySQL-tp15976517p16024988.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

> Currently postgresql's "vacuum full" also locks the affected tables.
> Does 8.3 vacuum full effectively make a copy of the entire table? How
> much extra space will the various vacuums use while vacuuming?
>
As to 8.3 and how it handles vacuum-full internally, I can't say for
certain without reading the notes. I suspect it is an in-place operation
similar to prior versions.

But if you are running PG 7.0 or later (maybe even earlier, I didn't
look) and have enough disk space to replicate the table, you can use
CLUSTER to do a copy-to-new file and delete old file operation. It does
still lock the table and it does require that the table have an index
based on which it will physically reorder the table but it is usually
_way_ faster and you get fresh indexes as a bonus.

IOW, PG offers you the choice to pick whichever is best for you.

Cheers,
Steve

Steve Crawford escribió:

>> Currently postgresql's "vacuum full" also locks the affected tables.
>> Does 8.3 vacuum full effectively make a copy of the entire table? How
>> much extra space will the various vacuums use while vacuuming?
>>
> As to 8.3 and how it handles vacuum-full internally, I can't say for
> certain without reading the notes. I suspect it is an in-place operation
> similar to prior versions.

VACUUM FULL is in 8.3 pretty much the same that's on previous versions.
You are advised to stay away from it, though, as much as possible.

> But if you are running PG 7.0 or later (maybe even earlier, I didn't
> look) and have enough disk space to replicate the table, you can use
> CLUSTER to do a copy-to-new file and delete old file operation. It does
> still lock the table and it does require that the table have an index
> based on which it will physically reorder the table but it is usually
> _way_ faster and you get fresh indexes as a bonus.

You can use CLUSTER reliably only from 7.2 upwards.  (Or was it 7.3?  I
forget).  In earlier versions it would lose information about other
indexes (i.e. those not being clustered on), foreign keys, inheritance,
etc; in other words pretty much a disaster except for the simplest of
tables.  Also, it is MVCC-safe only from 8.3 upwards; on older versions
it (incorrectly) deletes dead tuples that are still visible to old
transactions.

Of course, the main problem with CLUSTER is that it needs about 2x the
disk space of table + indexes.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thursday 13 March 2008 5:36 am, rrahul wrote:
> Thanks to all you wonderful people out their. I don't know if its your love
> for Postgres or nepothism that makes it look far superior than mysql.
> But why does the client list dosen't tell that?
> I see Mysql bosting for Google,Yahoo, Alcatel......
> What about Postgres the list is not that impressive.

There is an old saying "If 'everybody' else is jumping off a cliff should you
too?"  Years ago I played around with MySQL because that was what "everybody"
was using. The problem was it did not do what I wanted and Postgres did. Be
less concerned with marketing lists and more concerned with what the software
can help you do. Draw up a list of things you need in a database and then use
the previous answers to decide which database better serves your needs.

>
> Are their any major implementations that moved from Mysql to Postgres?
> Howmany out their have done this or will advice to do that?
>
> cheers,
> Rahul.
> --
> View this message in context:
> http://www.nabble.com/postgre-vs-MySQL-tp15976517p16024988.html Sent from
> the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
aklaver@comcast.net

On 14/03/2008, rrahul <rahul.rathi@cognizant.com> wrote:
>  Thanks to all you wonderful people out their. I don't know if its your love
>  for Postgres or nepothism that makes it look far superior than mysql.
>  But why does the client list dosen't tell that?
>  I see Mysql bosting for Google,Yahoo, Alcatel......
>  What about Postgres the list is not that impressive.
What can I say?  96% of personal computers run some form of
windows.  Does that mean it's a superior product to a PC running
Linux, or a Mac w/  MacOS?
I'd say "no" (actually more like "NOOO!"), because windows doesn't let
me do 80% of the things that I do (need to do) with my PC.

From the fact hat the user-base is so massive, can I deduce that
windows is superior in terms of security or easy maintenance?
My personal experience says "No, no way".

What then?  Could it be marketing or the sad results of a avalanche
effect? Geee, there's a thought.


>  cheers,
>  Rahul.
Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

"Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:
> On 14/03/2008, rrahul <rahul.rathi@cognizant.com> wrote:
>> I see Mysql bosting for Google,Yahoo, Alcatel......
>> What about Postgres the list is not that impressive.

> What then?  Could it be marketing or the sad results of a avalanche
> effect? Geee, there's a thought.

Marketing.  Remember that MySQL AB have a strong financial incentive to
make organized efforts to locate and publicize impressive-sounding users
of MySQL. (I've heard rumors they even give licensing discounts to
companies that will let their names be used like that.)

There is no comparable effort happening on the Postgres side.  There are
plenty of impressive users of PG too, but they don't have to talk about
it.

            regards, tom lane

> What then?  Could it be marketing or the sad results of a avalanche
> effect? Geee, there's a thought.
>
What a wide variety of topics.  One big difference for me is that MySQL
used to be open source, but it no longer is.  It's an odd hybrid OSS
that barely makes sense to me since they claim to be open source under
the GPL, and while you can contribute code to them (I did so in their
JDBC driver many years ago before switching to Postgresql), they then
own the code (fine!), but if you want to use it in any system that's not
itself open source, you have to pay to get a license.  Pay for GPL software?

But they proudly state they are part of LAMP, yet only the "M" charges
to use their software.  The real leaders in these open source camps are
Linux and Apache, neither of which have such absurd pseudo-open
licensing terms.

David

Andrej Ricnik-Bay wrote:
> On 14/03/2008, rrahul <rahul.rathi@cognizant.com> wrote:
>
>>  Thanks to all you wonderful people out their. I don't know if its your love
>>  for Postgres or nepothism that makes it look far superior than mysql.
>>  But why does the client list dosen't tell that?
>>  I see Mysql bosting for Google,Yahoo, Alcatel......
>>  What about Postgres the list is not that impressive.
>>
> What can I say?  96% of personal computers run some form of
> windows.  Does that mean it's a superior product to a PC running
> Linux, or a Mac w/  MacOS?
> I'd say "no" (actually more like "NOOO!"), because windows doesn't let
> me do 80% of the things that I do (need to do) with my PC.
>
> >From the fact hat the user-base is so massive, can I deduce that
> windows is superior in terms of security or easy maintenance?
> My personal experience says "No, no way".
Whoa! Sure glad I read this thread. I was about to buy a Bentley but now
that I know that sales figures are the sole measure of quality I can see
that the Kia is clearly a superior vehicle.

But seriously, some of the most disappointing products and services I've
had the misfortune to connect with were also the most popular choices.

It's interesting to look at the past several years' Linux Journal
Readers' Choice Awards (basically a popularity contest) where MySql
consistently takes top place vs. the Editors' Choice Awards where the
writers have to lay-out their evaluation/reasoning. For the editors,
PostgreSQL is #1 year after year.

Also, a simple "used by" is pretty meaningless as it can range from
"core component of large-scale mission-critical 24x7 infrastructure" to
"used by secretary to manage holiday card list". If you are a marketroid
attempting to assemble an impressive-looking client list, guess where
the threshold will be.

Cheers,
Steve

On Thu, 13 Mar 2008, rrahul wrote:

> I see Mysql bosting for Google,Yahoo, Alcatel......

Sure they are.  Do some reading on the Google installation.  The blog list
at http://www.mysql.com/customers/customer.php?id=75 works as well as any.

The reality here is that Google was just about fed up with MySQL not
working well enough for them circa 2000, and what they ended doing is a
combination of customizing MySQL to add the features they needed along
with doing a large-scale replication job.  They don't need any instance to
be reliable, they solve that problem with redundancy instead.

Look at http://code.google.com/p/google-mysql-tools/wiki/Mysql4Patches
They had to add multiple replication features and fix some tiny little
bugs, you know things like "Changed InnoDB to recover when InnoDB and
MySQL data dictionaries are inconsistent".*

Now, ask yourself this:  do you have that level of resources?  Are you
going to write your own recovery tool when MySQL bungles a commit and the
data dictionary is screwed up?  If not, I wonder how much that Google has
managed to hack MySQL into a usable state for them should matter to you.

* Why does this data dictionary corruption happen in MySQL?  Because the
data dictionaries (which they just call metadata), the most important
tables in the database, are still using a design that frankly is garbage.
See http://forge.mysql.com/w/images/0/0a/Mdl.pdf for details, it starts
with the cheery "Designed in the pre-transactional era of MySQL,
[metadata] has not had an overhaul or a clean up ever since then".

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

On Thu, 13 Mar 2008 20:08:27 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:
> > On 14/03/2008, rrahul <rahul.rathi@cognizant.com> wrote:
> >> I see Mysql bosting for Google,Yahoo, Alcatel......
> >> What about Postgres the list is not that impressive.
>
> > What then?  Could it be marketing or the sad results of a
> > avalanche effect? Geee, there's a thought.
>
> Marketing.  Remember that MySQL AB have a strong financial
> incentive to make organized efforts to locate and publicize
> impressive-sounding users of MySQL. (I've heard rumors they even
> give licensing discounts to companies that will let their names be
> used like that.)
>
> There is no comparable effort happening on the Postgres side.
> There are plenty of impressive users of PG too, but they don't have
> to talk about it.

I still find impressing that Google uses MySQL... I can guess why,
I'd enjoy to hear a more informed opinion.
I'd say: 1) legacy 2) no particular interest in data
integrity/coherence

Something more here
http://www.postgresql.org/about/casestudies/
and a bit updated would help too.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 13 Mar 2008 20:08:27 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
 
> Marketing.  Remember that MySQL AB have a strong financial incentive
> to make organized efforts to locate and publicize impressive-sounding
> users of MySQL. (I've heard rumors they even give licensing discounts
> to companies that will let their names be used like that.)

That's fairly standard practice. We have actually had customers try and
negotiate that with us. "If you give us a discount you can use our name
in xyz...". Of course, we don't :) but then again that's why you don't
see our name attached to large rollouts (even though you can see large
rollouts attached to us.)

Sincerely,

Joshua D. Drake

- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH2dVdATb/zqfZUUQRAjFWAJ9MJibdGfK+mV7ZcOnsi2BK5j5k2wCcCh7t
zRi1usSR6m/zgLV2BEcUG7w=
=7h9t
-----END PGP SIGNATURE-----

d.wall@computer.org (David Wall) writes:
>> What then?  Could it be marketing or the sad results of a avalanche
>> effect? Geee, there's a thought.
>>
> What a wide variety of topics.  One big difference for me is that
> MySQL used to be open source, but it no longer is.  It's an odd hybrid
> OSS that barely makes sense to me since they claim to be open source
> under the GPL, and while you can contribute code to them (I did so in
> their JDBC driver many years ago before switching to Postgresql), they
> then own the code (fine!), but if you want to use it in any system
> that's not itself open source, you have to pay to get a license.  Pay
> for GPL software?
>
> But they proudly state they are part of LAMP, yet only the "M" charges
> to use their software.  The real leaders in these open source camps
> are Linux and Apache, neither of which have such absurd pseudo-open
> licensing terms.

Indeed.  If Linux had had the same sorts of obligations as MySQL(tm),
it would have wound up a mere curiosity, because there were plenty of
other OS projects around of fairly comparable functionality
(particularly if we step back to 1993!), and no one would have put up
with such in that context.
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/multiplexor.html
:FATAL ERROR -- VECTOR OUT OF HILBERT SPACE

On 14/03/2008, Steve Crawford <scrawford@pinpointresearch.com> wrote:
>  > What can I say?  96% of personal computers run some form of
>  > windows.  Does that mean it's a superior product to a PC running
>  > Linux, or a Mac w/  MacOS?
>  > I'd say "no" (actually more like "NOOO!"), because windows doesn't let
>  > me do 80% of the things that I do (need to do) with my PC.
>  >
>  > >From the fact hat the user-base is so massive, can I deduce that
>  > windows is superior in terms of security or easy maintenance?
>  > My personal experience says "No, no way".

> Whoa! Sure glad I read this thread. I was about to buy a Bentley but now
>  that I know that sales figures are the sole measure of quality I can see
>  that the Kia is clearly a superior vehicle.

Heh. :}

A silly question in this context:  If we know of a company that does use
PostgreSQL but doesn't list it anywhere ... can we
tahttp://www.securecomputing.com/techpubsRC.cfm?pid=85ke the liberty
to
publicise this somewhere anyway?

E.g. the  control center (
http://www.securecomputing.com/techpubsRC.cfm?pid=85 )
uses postgres, the only official attribution (I've seen the binaries in the
file-system) is that their product uses port 5432 in the manual.



Cheers,
Andrej

P.S.: This is all really starting to belong to advocacy :}



--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

On Fri, 14 Mar 2008, Andrej Ricnik-Bay wrote:

> A silly question in this context:  If we know of a company that does use
> PostgreSQL but doesn't list it anywhere ... can we take the liberty to
> publicise this somewhere anyway?

Bad idea.  There are companies who consider being listed as a user of a
product a sort of recommendation of that technology, and accordingly they
will get really annoyed if you do that when they didn't give you
permission.  This has actually happened, where someone has asked to be
removed from the list of those using PostgreSQL.

While it's fun to run "strings" on random software to see who is using
PostgreSQL inside, it's best not to publish the results unless you like to
collect cease & desist letters.

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 14 Mar 2008 00:05:10 -0400 (EDT)
Greg Smith <gsmith@gregsmith.com> wrote:

> On Fri, 14 Mar 2008, Andrej Ricnik-Bay wrote:
> 
> > A silly question in this context:  If we know of a company that
> > does use PostgreSQL but doesn't list it anywhere ... can we take
> > the liberty to publicise this somewhere anyway?
> 
> Bad idea.

Very bad idea.

>  There are companies who consider being listed as a user of
> a product a sort of recommendation of that technology, and
> accordingly they will get really annoyed if you do that when they
> didn't give you permission.  This has actually happened, where
> someone has asked to be removed from the list of those using
> PostgreSQL.

Yes it has.

Sincerely,

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH2fpEATb/zqfZUUQRAjktAJ9ApY2Mgtylm5Ud13wAIjoDhudfOACeNgQv
cuEmfQ3IT3MtPoPcdazXwKw=
=7NTF
-----END PGP SIGNATURE-----

Hi,

This has been a very interesting thread indeed.

I think the popularity of any Big Name $oftware with a 'nice' price tag has
also something to do with the fear of taking responsibility for your own
actions and decisions.

With a Big Name you can always blame them if something goes wrong instead of
having to admit that it was you who actually did something stupid.

But as said, popular does not necessarily equal to superior.

I have used PG since RedHat 6.2 (can't remember the PG version of that time)
and now use it on FC, CentOS and Ubuntu. My dbs are not large, the biggest
one has sixty odd tables and the biggest table is holding now around 100.000
rows. But I have seen it reach that from zero, with version upgrades and
without any real db-related problems.

Best regards,
--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---

>  itself open source, you have to pay to get a license.  Pay for GPL software?

You cannot be serious, GPL has no relation with monetary value. The
GPL is a 'Usage License'.  If i write GPL software to my clients,
should i give it free of charge ?. That's absurd.

On Thu, Mar 13, 2008 at 08:08:27PM -0400, Tom Lane wrote:
> "Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:
> > On 14/03/2008, rrahul <rahul.rathi@cognizant.com> wrote:
> >> I see Mysql bosting for Google,Yahoo, Alcatel......
> >> What about Postgres the list is not that impressive.
>
> > What then?  Could it be marketing or the sad results of a avalanche
> > effect? Geee, there's a thought.
>
> Marketing.  Remember that MySQL AB have a strong financial incentive to
> make organized efforts to locate and publicize impressive-sounding users
> of MySQL. (I've heard rumors they even give licensing discounts to
> companies that will let their names be used like that.)

Of course they do. All commercial companies do. It's called marketing. (I
know JD is going to say he doesn't. So ok, change that to almost all
commercial companies do)

Ever heard of Microsoft? Best way to get a 95% discount? That's about it..

//Magnus

On Fri, Mar 14, 2008 at 02:29:07AM +0100, Ivan Sergio Borgonovo wrote:
> On Thu, 13 Mar 2008 20:08:27 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > "Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:
> > > On 14/03/2008, rrahul <rahul.rathi@cognizant.com> wrote:
> > >> I see Mysql bosting for Google,Yahoo, Alcatel......
> > >> What about Postgres the list is not that impressive.
> >
> > > What then?  Could it be marketing or the sad results of a
> > > avalanche effect? Geee, there's a thought.
> >
> > Marketing.  Remember that MySQL AB have a strong financial
> > incentive to make organized efforts to locate and publicize
> > impressive-sounding users of MySQL. (I've heard rumors they even
> > give licensing discounts to companies that will let their names be
> > used like that.)
> >
> > There is no comparable effort happening on the Postgres side.
> > There are plenty of impressive users of PG too, but they don't have
> > to talk about it.
>
> I still find impressing that Google uses MySQL... I can guess why,

What makes you so sure Google don't use PostgreSQL *as well*?

(hint: we don't force them to tell you about it...)

//Magnus

On Friday 14. March 2008, Adrian Klaver wrote:

> Years ago I played around with MySQL because that
> was what "everybody" was using. The problem was it did not do what I
> wanted and Postgres did.

That pretty much sums up my experiences too. Back in 2002 when I started
fooling around with databases, there wasn't much of a competition, and
I used MySQL as 'everybody else' did. But when I reached the point
where issues like data integrity started to matter, I was advised to
try PostgreSQL. I did, and haven't looked back. That was in 2005, and
PostgreSQL was at version 7.4 something.

There are several reasons why MySQL have a lot more users than
PostgreSQL, and in more than one way the parallel to Microsoft vs. *nix
is striking. In the software world, getting a mediocre product on the
market early may often be the key to success, and is commonly referred
to as the "Worse Is Better" principle.

<http://en.wikipedia.org/wiki/Worse_is_better>

Besides, a lot of database users don't really care for the database
itself. Notably the object/relational mapping (ORM) camp, like Ruby On
Rails, Django, and Catalyst, will consider the DBMS as a dumb storage
engine. With that attitude, combined with the fact that most ORM
frameworks are written mainly for MySQL, it's no wonder that PostgreSQL
doesn't make many inroads here.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On Fri, 14 Mar 2008 10:28:37 +0100
Magnus Hagander <magnus@hagander.net> wrote:

> > I still find impressing that Google uses MySQL... I can guess why,

> What makes you so sure Google don't use PostgreSQL *as well*?

I'm not sure... in fact I never excluded they could use pg for other
stuff... They may use it for collecting payment, counting # of
ads/click trough etc...

> (hint: we don't force them to tell you about it...)

So... MySQL forced Google to tell everyone?

I think the field of application is pretty different.
I'm speculating...
They should have a "narrow" task, they shouldn't be interested in
coherency/integrity... just on HA etc...

Still I'd be curious to know if people can scale pg to several
hundreds(?) machines without loosing the features that differentiate
it from other DB...


--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Gurjeet Singh escribió:

> I wouldn't comment on that, but having read so much about MySQL in Postgres'
> lists, I sure have a disliking for MySQL, so much so that I haven't bothered
> even downloading and installing it even once!!!

I have downloaded the source at different periods of time.  The first
time it was horrid -- they had done a port to Novell recently and had
stuffed it with weird macros that made it really unreadable.  Oh, and
the naming convention for source files was awful.

The second time, the macros weren't there (but then, perhaps I looked at
a different part of the source).  But it was pretty much undocumented --
and the naming convention doesn't help.

My guess is that it's written in such a way that you need some sort of
roadmap or external file (missing from the GPL source) that makes it
hard to understand it.  Or perhaps they preprocess a different
repository in order to create an obfuscated GPL source tree.


(I have a memory of comments in the source that looked like it referred
to some internal knowledge base or bug tracking system.  But frankly it
is a dim memory and could very well be related to the PHP source or
MaxDB rather than MySQL.)

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
Management by consensus: I have decided; you concede.
(Leonard Liu)

On Mar 14, 2008, at 7:22 AM, Ivan Sergio Borgonovo wrote:

> Still I'd be curious to know if people can scale pg to several
> hundreds(?) machines without loosing the features that differentiate
> it from other DB...

Jan Weick wrote Slony which was released by Affilias who runs the top-
level registries for the .info and .org domains (ref:
http://archives.postgresql.org/pgsql-general/2007-12/msg01421.php) 
.  I'd imagine those are some pretty serious set-ups.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Mar 14, 2008, at 3:26 AM, jose javier parra sanchez wrote:

>> itself open source, you have to pay to get a license.  Pay for GPL
>> software?
>
> You cannot be serious, GPL has no relation with monetary value. The
> GPL is a 'Usage License'.  If i write GPL software to my clients,
> should i give it free of charge ?. That's absurd.

They've gotten around that by making MySQL "dual-licensed".  If you're
going to be using MySQL in a commercial application then you can not
use the GPL'd version, you have to use their paid, commercial license.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Gurjeet Singh escribi�:
>> I wouldn't comment on that, but having read so much about MySQL in Postgres'
>> lists, I sure have a disliking for MySQL, so much so that I haven't bothered
>> even downloading and installing it even once!!!

> I have downloaded the source at different periods of time.

In connection with my Red Hat duties I've had to look at it occasionally
:-(.  They definitely have a lower standard for commenting than we do.
I sure hope that there is unpublished documentation somewhere ... though
their spectacularly bad track record on the 5.0 and 5.1 release series
makes one wonder.  Maybe the problem is exactly that they have too many
engineers who don't understand their own code very well.

> Or perhaps they preprocess a different
> repository in order to create an obfuscated GPL source tree.

No, the parts of their devel process that are visible (such as the bk
commit archives) don't suggest any such thing.  It's just ugly code.

I'm not sure we should be throwing too many stones, though.  It takes
a long time to wrap your brain around the PG source code, too.

            regards, tom lane

Erik Jones wrote:
>
> They've gotten around that by making MySQL "dual-licensed".  If you're
> going to be using MySQL in a commercial application then you can not
> use the GPL'd version, you have to use their paid, commercial license.
>
My understanding is that's not quite true. The client libraries are GPL,
so you can't use them directly, but I don't see what would stop you
using their ODBC/JDBC drivers with your non-GPL application (especially
if you support other ODBC databases as well). The server can't be
bundled in your application, but you can still get the user to install
it and use it with your application.

Why go to all that fuss, though, when you can just use a database with a
more friendly license? For embedding there's SQLite, Firebird, etc, and
for standalone work there's PostgreSQL. You'd contribute any changes or
patches back to the database developers anyway, after all - if not by
way of thanks and the desire to contribute then at least because it's
not worth the hassle of maintaining them out-of-tree - so I don't think
the DB teams would gain much from the GPL anyway.

Speaking of which, thanks to everybody who's worked on PostgreSQL- it's
magic, and it's made my latest project at work a lot easier. I keep on
finding myself thinking "I wish I could do <something>" only to find out
that it's been implemented in the next version. For example, now EXECUTE
... INTO has been implemented for 8.3 . It's turning into a seriously
impressive database.

--
Craig Ringer

Tom Lane wrote:
> In connection with my Red Hat duties I've had to look at it occasionally
> :-(.  They definitely have a lower standard for commenting than we do.
> I sure hope that there is unpublished documentation somewhere ...

And cut into the very lucrative "figuring out the MySQL source code"
book market??  No way.  There have been at least 3 books out in the last
year or so on just that topic.

Paul

On Fri, Mar 14, 2008 at 03:17:27PM +0530, Gurjeet Singh wrote:
> On Thu, Mar 13, 2008 at 6:06 PM, rrahul <rahul.rathi@cognizant.com> wrote:
> > Thanks to all you wonderful people out their. I don't know if its
> > your love for Postgres or nepothism that makes it look far superior
> > than mysql.
>
> I wouldn't comment on that, but having read so much about MySQL in
> Postgres' lists, I sure have a disliking for MySQL, so much so that I
> haven't bothered even downloading and installing it even once!!!
>
> Does anyone know of threads in MySQL lists/forums where they run a
> propaganda against Postgres or if they downplay us? That would be an
> interesting read!

Indeed that would.  This thread is adding a lot, of what I think is
known as "confirmation bias", to my personal preference of PG over
MySQL.  It gives me a warm fuzzy feeling that I can't be too wrong with
my choice, but it also increases my dogmatism.

I'm wondering how interesting it would be to put a smallish survey
together to see if we can tease things apart.  The wording of questions
would be fun.  Questions like "how important is security to you" seem
somewhat pointless as they're open to *a lot* of interpretation[1] and
almost everyone will pick "very".  A couple of multiple-choice questions
I've been able to think of are:

  1.a. most people interact with this database directly (i.e. they
       write SQL)
    b. the developers interact with the database directly, but the
       users don't
    c. neither developers or users interact with the database directly

  2.a. the database should stop invalid data from being entered as
       quickly as possible, aborting transactions immediately
    b. the database should do it's best to interpret my data in a
       valid way, only aborting transactions when necessary

The second seems very biased, but I've not been able to think of
a better way of describing it.  I think it could be quite a good
description of what most users actually want though!

I think the hypothesis would be something along the lines of comparing
developer and project requirements with the choice of database
implementation.  Comments?  I was thinking of sending it to other FOSS
database mailing lists as I'm not sure how to contact commercial DBs'
users.  I'm sure that lots of people use both, but the sample will be
biased.


  Sam

 [1] http://www.eros-os.org/pipermail/cap-talk/2007-December/009460.html

Alvaro Herrera wrote:
>
>
> You can use CLUSTER reliably only from 7.2 upwards.  (Or was it 7.3?  I
> forget).  In earlier versions it would lose information about other
> indexes (i.e. those not being clustered on), foreign keys, inheritance,
> etc; in other words pretty much a disaster except for the simplest of
> tables.
Interesting historical note, but fortunately largely irrelevant these days.

>   Also, it is MVCC-safe only from 8.3 upwards; on older versions
> it (incorrectly) deletes dead tuples that are still visible to old
> transactions.
>
>
More interesting. I may have a broken mental-model. I *thought* that
CLUSTER acquired exclusive locks and that acquisition of the exclusive
lock would imply that there couldn't be any transactions accessing that
table. Where is my misunderstanding?

> Of course, the main problem with CLUSTER is that it needs about 2x the
> disk space of table + indexes.
>
Again checking my mental model. My understanding is that CLUSTER
basically recreates the tables and indexes and then swaps the new ones
in place of the originals. So ~2x is true for typical tables. But for
tables badly bloated by multiple bulk updates or bad vacuum practices
CLUSTER should require far less than 2x.

Cheers,
Steve

Steve Crawford escribió:
> Alvaro Herrera wrote:

>>   Also, it is MVCC-safe only from 8.3 upwards; on older versions
>> it (incorrectly) deletes dead tuples that are still visible to old
>> transactions.
>
> More interesting. I may have a broken mental-model. I *thought* that
> CLUSTER acquired exclusive locks and that acquisition of the exclusive
> lock would imply that there couldn't be any transactions accessing that
> table. Where is my misunderstanding?

In that a transaction may have started _before_ the CLUSTER, yet not
grabbed any locks on the table yet.  CLUSTER completes, releases the
lock, and your old transaction visits the table only to find that the
tuples it needs are no longer there.  (In fact IIRC what happens is that
the transaction finds the table empty).

>> Of course, the main problem with CLUSTER is that it needs about 2x the
>> disk space of table + indexes.
>>
> Again checking my mental model. My understanding is that CLUSTER
> basically recreates the tables and indexes and then swaps the new ones
> in place of the originals. So ~2x is true for typical tables. But for
> tables badly bloated by multiple bulk updates or bad vacuum practices
> CLUSTER should require far less than 2x.

Yeah, that would seem to be correct -- you only need space enough for
live tuples.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Leif B. Kristensen wrote:
> On Friday 14. March 2008, Adrian Klaver wrote:
>
>> Years ago I played around with MySQL because that
>> was what "everybody" was using. The problem was it did not do what I
>> wanted and Postgres did.
>
> That pretty much sums up my experiences too. Back in 2002 when I started
> fooling around with databases, there wasn't much of a competition, and
> I used MySQL as 'everybody else' did. But when I reached the point
> where issues like data integrity started to matter, I was advised to
> try PostgreSQL. I did, and haven't looked back. That was in 2005, and
> PostgreSQL was at version 7.4 something.

Our issue back then (early part of this decade) was that PostgreSQL
didn't run easily on top of MS Windows and I didn't yet have any Linux
boxes that I could run it on.  It wasn't until 8.0 that we finally
started playing with pgsql in earnest.  We also now have Linux servers
installed, which makes things easier on a lot of fronts.

(We'll be migrating our small MySQL install over to postgresql this
year.  The public site is still based on MS SQL and hosts a few dozen GB
worth of data, but we have plans to migrate that to pgsql as well once
we have more experience with it.)

On Fri, 2008-03-14 at 08:43 -0700, Steve Crawford wrote:
> >   Also, it is MVCC-safe only from 8.3 upwards; on older versions
> > it (incorrectly) deletes dead tuples that are still visible to old
> > transactions.
> >
> >
> More interesting. I may have a broken mental-model. I *thought* that
> CLUSTER acquired exclusive locks and that acquisition of the exclusive
> lock would imply that there couldn't be any transactions accessing that
> table. Where is my misunderstanding?

Here's a scenario:

 - transaction A starts to read table A;
 - transaction B starts, deletes some records from table B, end ends;
 - transaction C starts and clusters table B;
 - transaction A finished reading table A, and now tries to read the
records just deleted by transaction B;

Question: under MVCC rules should transaction A see the deleted records
or not ?

Unfortunately I don't know for sure the answer, but if it is yes, then
bad luck for transaction A, because cluster just ate them. And the
locking will not help this...

Cheers,
Csaba.

My understanding is that's not quite true. The client libraries are GPL, so you can't use them directly, but I don't see what would stop you using their ODBC/JDBC drivers with your non-GPL application (especially if you support other ODBC databases as well). The server can't be bundled in your application, but you can still get the user to install it and use it with your application.

When your application is not licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product.

Typical examples of MySQL distribution include:

• Selling software that includes MySQL to customers who install the software on their own machines.

• Selling software that requires customers to install MySQL themselves on their own machines.

• Building a hardware system that includes MySQL and selling that hardware system to customers for installation at their own locations.

On Fri, Mar 14, 2008 at 4:34 PM, David Wall <d.wall@computer.org> wrote:
>
>
>
> My understanding is that's not quite true. The client libraries are GPL, so
> you can't use them directly, but I don't see what would stop you using their
> ODBC/JDBC drivers with your non-GPL application (especially if you support
> other ODBC databases as well). The server can't be bundled in your
> application, but you can still get the user to install it and use it with
> your application.
>  According to the MySQL license info (
> http://www.mysql.com/about/legal/licensing/commercial-license.html ):
>
>
> When your application is not licensed under either the GPL-compatible Free
> Software License as defined by the Free Software Foundation or approved by
> OSI, and you intend to or you may distribute MySQL software, you must first
> obtain a commercial license to the MySQL product.
>
> Typical examples of MySQL distribution include:
>
>
>
> Selling software that includes MySQL to customers who install the software
> on their own machines.
>
>
> Selling software that requires customers to install MySQL themselves on
> their own machines.
>
>
> Building a hardware system that includes MySQL and selling that hardware
> system to customers for installation at their own locations.
>
>  It sure sounds like if your application uses MySQL and you sell your
> software (I presume this would include online services that charge for use
> of the site and that site runs MySQL under the hood), you have to buy a
> commercial license, and you can't get around it just by not directly
> distributing MySQL and having your customer install it separately.

I imagine you can get round the second one by building your software
so it supports PostgreSQL as well - that way you don't 'require
customes to install MySQL'.

As for the hardware one, well, that just confirms everything I
previously thought about MySQL that is unrepeatable where minors may
be reading.


--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

> I imagine you can get round the second one by building your software
> so it supports PostgreSQL as well - that way you don't 'require
> customes to install MySQL'.
>
Well, I'm not sure how they'd even know you were doing this, but as a
commercial company, I'd suggest you not follow that advice since the
code would not work without install MySQL.  Yes, they could install PG
instead, and if they did, MySQL would have no problem.  But if you use
MySQL, then clearly it's required and a commercial license would be
required (though perhaps at least you'd put the legal obligation on the
end customer).  Of course, all of this is based on reading their high
level stuff, not the actual legal document that may be tighter or looser.

That fact that there's so much confusion and so many instances in which
commercial licenses would be required that I say they are only open
source in self-branding, not reality.

David

On 14 Mar, 09:26, jojap...@gmail.com ("jose javier parra sanchez")
wrote:
> >  itself open source, you have to pay to get a license.  Pay for GPL software?
>
> You cannot be serious, GPL has no relation with monetary value. The
> GPL is a 'Usage License'.  If i write GPL software to my clients,
> should i give it free of charge ?. That's absurd.

Yes, it's nice to see the standard licensing rumours spread around
completely unconstrained by inconvenient things like the facts. Of
course you can charge people for GPL-licensed software, but you have
to promise to let them have the source code at no additional cost. And
the mere existence of your GPL-licensed software doesn't mean that you
are obliged to give random inquirers the source code: it's only if
you've already distributed the software to people that they have the
right to the source.

http://www.fsf.org/licensing/licenses/gpl-faq.html#DoesTheGPLAllowMoney

As for things like contributor agreements, that has nothing to do with
the licence and whether a product is Free Software or not: it's a
copyright thing; various permissively licensed projects have
contributor agreements, too. Naturally, the MySQL corporate entity
want people to assign copyright to them so that they can then offer
the code under a proprietary licence, but there would be nothing to
stop you from just forking MySQL and offering it as a purely GPL-
licensed product.

And with respect to the MySQL corporate policy on using their product
in proprietary software, I believe that the reason why the client
libraries are GPL-licensed is precisely because nobody bought their
case for insisting that merely using the database system from a
program creates a GPL-licensed derived work consisting of MySQL and
the program. By linking to the client libraries, however, you are
creating a GPL-licensed derived work in a situation that the FSF would
actually go along with. The recent tendency of differentiation between
the "commercial" and "open source" editions would also indicate that
people aren't really believing the MySQL corporate spin, either.
Here's an example of the smoke and mirrors:

http://forums.mysql.com/read.php?4,31,888#msg-888

In some businesses with a dual-licensing model, I think it can be the
case that some people in sales/marketing/licensing like to make claims
that wouldn't stand up to thorough scrutiny, but where customers
probably aren't going to risk making a fuss if the licensing costs are
relatively low. Really, the MySQL people would have more credibility
if they just charged for support and bug-fixing and/or used something
like the Affero GPLv3 instead of the vanilla GPL, rather than trying
to ride two quite different horses.

Paul

P.S. It's not that I use MySQL, being happy with PostgreSQL, but
people should at least try and understand the licensing issues
involved.

Steve Crawford <scrawford@pinpointresearch.com> writes:
> Alvaro Herrera wrote:
>> Of course, the main problem with CLUSTER is that it needs about 2x the
>> disk space of table + indexes.
>>
> Again checking my mental model. My understanding is that CLUSTER
> basically recreates the tables and indexes and then swaps the new ones
> in place of the originals. So ~2x is true for typical tables. But for
> tables badly bloated by multiple bulk updates or bad vacuum practices
> CLUSTER should require far less than 2x.

Another point to keep in mind is that creation of a new btree index
(and, soon, a new hash index) involves a temporary sort file that's
roughly the size of the index.  So the peak transient space demand is
size of compacted table + size of compacted indexes + size of largest
index, more or less.  (I suppose it'd depend on the order in which
the indexes get rebuilt.)

            regards, tom lane

On Fri, Mar 14, 2008 at 10:34 AM, David Wall <d.wall@computer.org> wrote:

>  It sure sounds like if your application uses MySQL and you sell your
> software (I presume this would include online services that charge for use
> of the site and that site runs MySQL under the hood), you have to buy a
> commercial license, and you can't get around it just by not directly
> distributing MySQL and having your customer install it separately.

No, selling services that rely on mysql is not the same as selling
software that relies on mysql.  It's only when you sell or distrubute
software that you have to worry about the GPL.

craig@postnewspapers.com.au (Craig Ringer) writes:
> Erik Jones wrote:
>> They've gotten around that by making MySQL "dual-licensed".  If
>> you're going to be using MySQL in a commercial application then you
>> can not use the GPL'd version, you have to use their paid,
>> commercial license.
>>
> My understanding is that's not quite true. The client libraries are
> GPL, so you can't use them directly, but I don't see what would stop
> you using their ODBC/JDBC drivers with your non-GPL application
> (especially if you support other ODBC databases as well). The server
> can't be bundled in your application, but you can still get the user
> to install it and use it with your application.

Well, there's a certain amount of distance between "expectations" and
"legal requirements," and lots of room for weasel wording...

<http://forums.mysql.com/read.php?4,31,888#msg-888>
<http://www.mysql.com/about/legal/licensing/>

According to the above things that MySQL AB has said/continues to say,
it is quite clear that the owners of the code *intend* that
"commercial users" should pay them a licensing fee,
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://linuxdatabases.info/info/x.html
"Blessed are those who backup daily; for they shall be restored."
-- Seen in .signature of Veritas rep

On Fri, Mar 14, 2008 at 11:57 AM, Chris Browne <cbbrowne@acm.org> wrote:
> craig@postnewspapers.com.au (Craig Ringer) writes:
>  > Erik Jones wrote:
>  >> They've gotten around that by making MySQL "dual-licensed".  If
>  >> you're going to be using MySQL in a commercial application then you
>  >> can not use the GPL'd version, you have to use their paid,
>  >> commercial license.
>  >>
>  > My understanding is that's not quite true. The client libraries are
>  > GPL, so you can't use them directly, but I don't see what would stop
>  > you using their ODBC/JDBC drivers with your non-GPL application
>  > (especially if you support other ODBC databases as well). The server
>  > can't be bundled in your application, but you can still get the user
>  > to install it and use it with your application.
>
>  Well, there's a certain amount of distance between "expectations" and
>  "legal requirements," and lots of room for weasel wording...
>
>
>  <http://forums.mysql.com/read.php?4,31,888#msg-888>
>
> <http://www.mysql.com/about/legal/licensing/>
>
>  According to the above things that MySQL AB has said/continues to say,
>  it is quite clear that the owners of the code *intend* that
>  "commercial users" should pay them a licensing fee,

Read this earlier response where Zak makes it quite clear that web
services that are run in house do not violate the GPL terms for the
distribution of MySQL.

http://forums.mysql.com/read.php?4,31,63#msg-63

On Fri, Mar 14, 2008 at 5:07 PM, David Wall <d.wall@computer.org> wrote:
>
>  > I imagine you can get round the second one by building your software
>  > so it supports PostgreSQL as well - that way you don't 'require
>  > customes to install MySQL'.
>  >
>  Well, I'm not sure how they'd even know you were doing this, but as a
>  commercial company, I'd suggest you not follow that advice since the
>  code would not work without install MySQL.  Yes, they could install PG
>  instead, and if they did, MySQL would have no problem.  But if you use
>  MySQL, then clearly it's required and a commercial license would be
>  required (though perhaps at least you'd put the legal obligation on the
>  end customer).

Huh? I'm suggesting that you write your code to be
database-independent such that it is the user's choice what DBMS he
uses. That way you aren't 'requiring them to install MySQL'. MySQL
cannot hold you liable if a customer chooses to use your closed source
Java/JDBC app with their DBMS if you didn't require it.

--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

Dave Page wrote:
> On Fri, Mar 14, 2008 at 5:07 PM, David Wall <d.wall@computer.org> wrote:
>
>>  > I imagine you can get round the second one by building your software
>>  > so it supports PostgreSQL as well - that way you don't 'require
>>  > customes to install MySQL'.
>>  >
>>  Well, I'm not sure how they'd even know you were doing this, but as a
>>  commercial company, I'd suggest you not follow that advice since the
>>  code would not work without install MySQL.  Yes, they could install PG
>>  instead, and if they did, MySQL would have no problem.  But if you use
>>  MySQL, then clearly it's required and a commercial license would be
>>  required (though perhaps at least you'd put the legal obligation on the
>>  end customer).
>>
>
> Huh? I'm suggesting that you write your code to be
> database-independent such that it is the user's choice what DBMS he
> uses. That way you aren't 'requiring them to install MySQL'. MySQL
> cannot hold you liable if a customer chooses to use your closed source
> Java/JDBC app with their DBMS if you didn't require it.
>
>
Yes, that is MySQL's licensing angle.  I have spoken numerous times to
MySQL staff about it.  So what ended up happening for my software
development was it became a waste of time to support MySQL and
PostgreSQL, I moved to PostgreSQL solely which didn't have any of those
restrictions associated with it.  Which is how I got into PostgreSQL in
the first place.  And now I use MySQL when I have to because PostgreSQL
does the job for me and I'm used to writing SQL, plpgsql and the like
for it.

Russell Smith

Greg Smith wrote:
> On Fri, 14 Mar 2008, Andrej Ricnik-Bay wrote:
>> A silly question in this context:  If we know of a company that does
>> use PostgreSQL but doesn't list it anywhere ... can we take the
>> liberty to publicise this somewhere anyway?

I notice Oracle (and sleepycat before them) had a lot of fun
pointing out when Microsoft uses BDB.

http://www.oracle.com/technology/oramag/oracle/07-jan/o17opensource.html
    You'll find Oracle Berkeley DB "under the hood" in everything
    from Motorola cell phones, Microsoft/Groove's collaboration suite

and it seems unlikely Microsoft gave them their blessings.

> Bad idea.  There are companies who consider being listed as a user of a
> product a sort of recommendation of that technology, and accordingly

Other reasons a company might get offended by this:

  * They might consider it a trade secret and a competitive advantage
    over competitors; and internally enjoy giggling when they see
    their competitors sign deals with expensive databases.

  * They might have a close business partnership with Microsoft
    or Oracle that could be strained if they support other databases.

I suspect my employer would not like it announced for both reasons.

> they will get really annoyed...asked to be removed from the list of
> those using PostgreSQL.  ... PostgreSQL inside, it's best not to
> publish the results unless you like to collect cease & desist letters.

While I agree companies are likely to get annoyed - just like fast
food companies do when you say how much trans-fats their products
contain; I'm rather curious what such a cease&desist letter would say.

On Mar 15, 2008, at 8:58 AM, Ron Mayer wrote:

> Greg Smith wrote:
>> On Fri, 14 Mar 2008, Andrej Ricnik-Bay wrote:
>>> A silly question in this context:  If we know of a company that
>>> does use PostgreSQL but doesn't list it anywhere ... can we take
>>> the liberty to publicise this somewhere anyway?
>
> I notice Oracle (and sleepycat before them) had a lot of fun
> pointing out when Microsoft uses BDB.
>
> http://www.oracle.com/technology/oramag/oracle/07-jan/o17opensource.html
>   You'll find Oracle Berkeley DB "under the hood" in everything
>   from Motorola cell phones, Microsoft/Groove's collaboration suite
>
> and it seems unlikely Microsoft gave them their blessings.
>
>> Bad idea.  There are companies who consider being listed as a user
>> of a product a sort of recommendation of that technology, and
>> accordingly
>
> Other reasons a company might get offended by this:
>
> * They might consider it a trade secret and a competitive advantage
>   over competitors; and internally enjoy giggling when they see
>   their competitors sign deals with expensive databases.
>
> * They might have a close business partnership with Microsoft
>   or Oracle that could be strained if they support other databases.
>
> I suspect my employer would not like it announced for both reasons.
>
>> they will get really annoyed...asked to be removed from the list of
>> those using PostgreSQL.  ... PostgreSQL inside, it's best not to
>> publish the results unless you like to collect cease & desist
>> letters.
>
> While I agree companies are likely to get annoyed - just like fast
> food companies do when you say how much trans-fats their products
> contain; I'm rather curious what such a cease&desist letter would say.

Probably just a firm,  but polite, request to quit it.  I'd say that
with a completely open piece of software like Postgres, i.e. where no
commercial licensing is involved, the question is more ethical than
legal.  In fact, I can't think of a situation where "mind your own
business" could be take more literally :)

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 17 Mar 2008 09:26:35 -0500
Erik Jones <erik@myemma.com> wrote:

> > While I agree companies are likely to get annoyed - just like fast
> > food companies do when you say how much trans-fats their products
> > contain; I'm rather curious what such a cease&desist letter would
> > say.
> 
> Probably just a firm,  but polite, request to quit it.  I'd say that  
> with a completely open piece of software like Postgres, i.e. where
> no commercial licensing is involved, the question is more ethical
> than legal.  In fact, I can't think of a situation where "mind your
> own business" could be take more literally :)

Sometimes they may also claim trademark or trade secret issues.

Sincerely,

Joshua D. Drkae


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH3pQoATb/zqfZUUQRAt8iAJ9yLSuV4LQXeUl238VOk6k9VLwdYACgqdkW
bGvcvjIUVMj0VZetffDhYhY=
=91uz
-----END PGP SIGNATURE-----