Thread: MySQL versus Postgres

Postgres has a very gentle learning curve.  By which I mean that it
takes an extremely long time, perhaps a lifetime, to fully appreciate
it.  On the other hand, it is definitely worth it.  Each new discovery
is worth the effort and the wait.

But most people, including myself, don't even want to know the
documentation exists (for anything).  We just want to plunge in and do
it.

So, perhaps what is needed in any sort of battle with MySQL is an
introductory documentation that gives specific examples of how to
achieve "oh wow!" worthwhile results quickly with Postgres.

I am sure that this is already in place.  Perhaps it needs to be
separated from the regular documentation and buffed to make it truly
short and truly rewarding to study.  And showcased.

This is the better version of my dumbing down idea.

John

P.S. I noticed in my brief journey into the MySQL docs that they used
color (ineffectively) and they attempted a kind of bulleted outline
format.

P. P. S. You want to get to grandma's house.  You want to drive a
car.  You want to learn to drive the car quickly.

On Fri, Aug 6, 2010 at 04:41, John Gage <jsmgage@numericable.fr> wrote:

[...]
> So, perhaps what is needed in any sort of battle with MySQL is an
> introductory documentation that gives specific examples of how to achieve
> "oh wow!" worthwhile results quickly with Postgres.

Bruce Momjian's book is an excellent primer:
http://www.postgresql.org/docs/books/awbook.html


--
- Rikard

2010/8/6 Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com>:
> On Fri, Aug 6, 2010 at 04:41, John Gage <jsmgage@numericable.fr> wrote:
>
> [...]
>> So, perhaps what is needed in any sort of battle with MySQL is an
>> introductory documentation that gives specific examples of how to achieve
>> "oh wow!" worthwhile results quickly with Postgres.
>
> Bruce Momjian's book is an excellent primer:
> http://www.postgresql.org/docs/books/awbook.html

It is *very* outdated (I remember using that to get started myself,
almost 10 years ago) and a lot has been improved since then.

Ian Barwick

John Gage schrieb:

> So, perhaps what is needed in any sort of battle with MySQL is an
> introductory documentation that gives specific examples of how to
> achieve "oh wow!" worthwhile results quickly with Postgres.

I receive my "oh wow" when i do the same things in Postgres like in
MySQL: Writting some procedures, triggers and use foreign key. The "oh
wow" was that it just *works*. After some years of using MySQL this is a
very uncommon feeling, even if you are experienced which MySQL. ;)

And after that "oh wow" you starting reading the manual.

Greetings from Germany,
Torsten

--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.

John Gage wrote on 06.08.2010 04:41:
> But most people, including myself, don't even want to know the
> documentation exists (for anything). We just want to plunge in and do it.

That just doesn't work and is an attitude that won't get you far.

In order to do things properly you need to learn and understand what you are dealing with. "Plunging" into something
mightlook easy at the start but will get you into problems later when you need to understand *why* and *how* things are
working.

This is not something unique to Postgres or databases in general. It's not even unique to software.
Learn what youare doing (or dealing with) is a "strategy" that applies to everything you do.

Do take the time to read the manuals - including the MySQL manual (because just "plunging" into MySQL simply doesn't
workeither) 
It'll make you a lot more proficient in the long run.

Regards
Thomas

On reflection, I think what is needed is a handbook that features cut
and paste code to do the things with Postgres that people do today
with MySQL.

Such a handbook, featured as the first section of the documentation,
would take readers through the steps necessary to set up an online
shopping site, for example, with Postgres.  Cut and paste code is
absolutely crucial to the success of such a document.

Make it easy.  Make it easy.  Our road in the yellow wood has to be
the one you can go down the easiest.

John


On Aug 6, 2010, at 1:39 PM, Phillip Smith wrote:

> On 6 August 2010 16:08, Torsten Zühlsdorff <foo@meisterderspiele.de>
> wrote:
>
> I receive my "oh wow" when i do the same things in Postgres like in
> MySQL: Writting some procedures, triggers and use foreign key. The
> "oh wow" was that it just *works*. After some years of using MySQL
> this is a very uncommon feeling, even if you are experienced which
> MySQL. ;)
>
>
> I'm going the other way -- I first started SQL/RDBMS with
> PostgreSQL, and now I'm starting to have to deal with MySQL for some
> things at work. I know exactly how to do what I want in PG, but
> doing the same thing in MySQL is ten times more complicated and has
> me pulling my hair out :(

John Gage schrieb:

> On reflection, I think what is needed is a handbook that features cut
> and paste code to do the things with Postgres that people do today with
> MySQL.

Everyone of my trainees want such thing - for databases, for other
programming-languages etc. It's the worst thing you can give them. The<
will copy, they will paste and they will understand nothing. Learning is
the way to understanding, not copying.

Greetings,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.

I only said this to criticize it.  And I agree completely with Thomas.

John


On Aug 6, 2010, at 2:09 PM, Thomas Kellerer wrote:

> John Gage wrote on 06.08.2010 04:41:
>> But most people, including myself, don't even want to know the
>> documentation exists (for anything). We just want to plunge in and
>> do it.
>
> That just doesn't work and is an attitude that won't get you far.
>
> In order to do things properly you need to learn and understand what
> you are dealing with. "Plunging" into something might look easy at
> the start but will get you into problems later when you need to
> understand *why* and *how* things are working.
>
> This is not something unique to Postgres or databases in general.
> It's not even unique to software.
> Learn what youare doing (or dealing with) is a "strategy" that
> applies to everything you do.
>
> Do take the time to read the manuals - including the MySQL manual
> (because just "plunging" into MySQL simply doesn't work either)
> It'll make you a lot more proficient in the long run.
>
> Regards
> Thomas
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

If I recall correctly William Shakespeare did a ton of cutting and
pasting.  And he was not alone.  My Fair Lady, one of the most
successful Broadway shows ever, contains whole sections from Shaw's
play.

We learn by imitation.  I am not suggesting that once you cut and
paste you call it quits, but it is the only place to begin.

John


On Aug 6, 2010, at 3:13 PM, Torsten Zühlsdorff wrote:

> John Gage schrieb:
>
>> On reflection, I think what is needed is a handbook that features
>> cut and paste code to do the things with Postgres that people do
>> today with MySQL.
>
> Everyone of my trainees want such thing - for databases, for other
> programming-languages etc. It's the worst thing you can give them.
> The< will copy, they will paste and they will understand nothing.
> Learning is the way to understanding, not copying.
>
> Greetings,
> Torsten
> --
> http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
> verschiedenen Datenbanksystemen abstrahiert,
> Queries von Applikationen trennt und automatisch die Query-
> Ergebnisse auswerten kann.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

I would also enquire whether one thinks that the examples should be
removed from the Postgres documentation for fear that they may be cut
and pasted into an application?

John


On Aug 6, 2010, at 3:13 PM, Torsten Zühlsdorff wrote:

> John Gage schrieb:
>
>> On reflection, I think what is needed is a handbook that features
>> cut and paste code to do the things with Postgres that people do
>> today with MySQL.
>
> Everyone of my trainees want such thing - for databases, for other
> programming-languages etc. It's the worst thing you can give them.
> The< will copy, they will paste and they will understand nothing.
> Learning is the way to understanding, not copying.
>
> Greetings,
> Torsten
> --
> http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
> verschiedenen Datenbanksystemen abstrahiert,
> Queries von Applikationen trennt und automatisch die Query-
> Ergebnisse auswerten kann.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:

> John Gage schrieb:
>
>> On reflection, I think what is needed is a handbook that features
>> cut and paste code to do the things with Postgres that people do
>> today with MySQL.
>
> Everyone of my trainees want such thing - for databases, for other
> programming-languages etc. It's the worst thing you can give them.
> The< will copy, they will paste and they will understand nothing.
> Learning is the way to understanding, not copying.

I couldn't disagree more.  Presenting working code (at least snippets)
should continue to be a fundamental part of any documentation project.

As a first-time db programmer and 'casual' user of PostgreSQL, I read
Bruce Momjian's book to get started.  I rely on the example code
presented in the current documentation to learn best practices,
compare against it to troubleshoot my code when it breaks, and provide
inspiration for elegant solutions to challenges I encounter.

I would further suggest that a QuickStart guide would be an ideal
addition to the current documentation efforts.  Scanning a basic soup-
to-nuts solution can often answer fundamental questions more
efficiently than full-blown documentation can, especially when the
user is not already familiar with specific terminology to search for
in the index.

Regards,
Scott


> Greetings,
> Torsten
> --
> http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
> verschiedenen Datenbanksystemen abstrahiert,
> Queries von Applikationen trennt und automatisch die Query-
> Ergebnisse auswerten kann.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Fri, Aug 6, 2010 at 11:02 AM, Scott Frankel <frankel@circlesfx.com> wrote:
>
> On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:
>
>> John Gage schrieb:
>>
>>> On reflection, I think what is needed is a handbook that features cut and
>>> paste code to do the things with Postgres that people do today with MySQL.
>>
>> Everyone of my trainees want such thing - for databases, for other
>> programming-languages etc. It's the worst thing you can give them. The< will
>> copy, they will paste and they will understand nothing. Learning is the way
>> to understanding, not copying.
>
> I couldn't disagree more.  Presenting working code (at least snippets)
> should continue to be a fundamental part of any documentation project.

I agree. It's especially useful if you're dealing with folks who
already have a clue, but may not be 100% familiar with how SQL or a
particular language.  I had a Perl cookbook back in the day that was
priceless when I was switching from C to Perl.  I didn't just copy and
paste, but I did certainly learn a lot looking at other people's code.

The idea being discussed here is a CookBook and it's extremely useful.
 The current manual has a lot of examples, and some of them are very
much cookbook style.  I'm sure we could always use more.

john, you're running up against a culture here, and trying to answer
the question: how to make a nerd cool? answer: it can't be done.

On Fri, Aug 6, 2010 at 1:38 PM, zach cruise <zachc1980@gmail.com> wrote:
> john, you're running up against a culture here, and trying to answer
> the question: how to make a nerd cool? answer: it can't be done.

I'm all in favor of a nice tutorial section in the docs.  But I
certainly don't want pgsql docs to mimick the mysql docs method of
using a tutorial method for most of the beginner information.  It's so
dang hard to find anything I want in the mysql docs because of it.
Once you're somewhat familiar with the db, having to slog through
pages of tutorial to find the bits you want is counterproductive.  The
easy-peasy entry level mentality of the mysql docs works against
experienced users.

pgsql docs, OTOH, are much easier to trawl for the info I need, using
the top down layout that's so easy to use to find what you want.

On Fri, Aug 06, 2010 at 02:09:43PM -0600, Scott Marlowe wrote:
> I'm all in favor of a nice tutorial section in the docs.  But I
> certainly don't want pgsql docs to mimick the mysql docs method of
> using a tutorial method for most of the beginner information.  It's so
> dang hard to find anything I want in the mysql docs because of it.
> Once you're somewhat familiar with the db, having to slog through
> pages of tutorial to find the bits you want is counterproductive.  The
> easy-peasy entry level mentality of the mysql docs works against
> experienced users.

+1

I spent half an hour today trying to find the various ways MySQL has
for controlling charsets.  The TOC is unusable and even using the index
it can sometimes be very frustrating to find something.  Postgres has
outstanding docs right now!  It would be a shame to change that.

Tutorials would be good, but in a separate section of the site.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

In my fondest moments, I consider myself a nerd, and when I do I think
I am completely cool.

On Aug 6, 2010, at 9:38 PM, zach cruise wrote:

> john, you're running up against a culture here, and trying to answer
> the question: how to make a nerd cool? answer: it can't be done.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Fri, Aug 6, 2010 at 2:15 PM, Peter Bex <Peter.Bex@xs4all.nl> wrote:
> On Fri, Aug 06, 2010 at 02:09:43PM -0600, Scott Marlowe wrote:
>> I'm all in favor of a nice tutorial section in the docs.  But I
>> certainly don't want pgsql docs to mimick the mysql docs method of
>> using a tutorial method for most of the beginner information.  It's so
>> dang hard to find anything I want in the mysql docs because of it.
>> Once you're somewhat familiar with the db, having to slog through
>> pages of tutorial to find the bits you want is counterproductive.  The
>> easy-peasy entry level mentality of the mysql docs works against
>> experienced users.
>
> +1
>
> I spent half an hour today trying to find the various ways MySQL has
> for controlling charsets.  The TOC is unusable and even using the index
> it can sometimes be very frustrating to find something.  Postgres has
> outstanding docs right now!  It would be a shame to change that.

For an extra added bonus, show a click-path from the front page
mysql.com to the documentation.  I gave up and just entered
mysql.com/documentation which kicked off the search engine and let me
click somewhere into the 5.1 docs.  Yes I know they're on
dev.mysql.com but still, it feels like a total brochure site from the
front end.

On Fri, Aug 06, 2010 at 02:25:27PM -0600, Scott Marlowe wrote:
> For an extra added bonus, show a click-path from the front page
> mysql.com to the documentation.  I gave up and just entered
> mysql.com/documentation which kicked off the search engine and let me
> click somewhere into the 5.1 docs.  Yes I know they're on
> dev.mysql.com but still, it feels like a total brochure site from the
> front end.

I know :)  They actually have TWO sites: mysql.com which is indeed
a commercial outlet where they try to sell you their commercial offering
and mysql.org which is geared toward developers.  The manual is "only"
three clicks away on that site.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

On Fri, Aug 6, 2010 at 2:30 PM, Peter Bex <Peter.Bex@xs4all.nl> wrote:
> On Fri, Aug 06, 2010 at 02:25:27PM -0600, Scott Marlowe wrote:
>> For an extra added bonus, show a click-path from the front page
>> mysql.com to the documentation.  I gave up and just entered
>> mysql.com/documentation which kicked off the search engine and let me
>> click somewhere into the 5.1 docs.  Yes I know they're on
>> dev.mysql.com but still, it feels like a total brochure site from the
>> front end.
>
> I know :)  They actually have TWO sites: mysql.com which is indeed
> a commercial outlet where they try to sell you their commercial offering
> and mysql.org which is geared toward developers.  The manual is "only"
> three clicks away on that site.

Yeah, the difference between the MySQL site(s) and the PostgreSQL
site(s) is kinda like Microsoft versus Apple (the ui, not their
websites).    Everything you need on pgsql is a click or two away, and
the direction to head is pretty obvious.  Not so much with mysql's
site.

On 06/08/2010 21:15, Peter Bex wrote:
> On Fri, Aug 06, 2010 at 02:09:43PM -0600, Scott Marlowe wrote:
>> I'm all in favor of a nice tutorial section in the docs.  But I
>> certainly don't want pgsql docs to mimick the mysql docs method of
>> using a tutorial method for most of the beginner information.  It's so
>> dang hard to find anything I want in the mysql docs because of it.
>> Once you're somewhat familiar with the db, having to slog through
>> pages of tutorial to find the bits you want is counterproductive.  The
>> easy-peasy entry level mentality of the mysql docs works against
>> experienced users.
>
> +1

+1 from me also.

> Tutorials would be good, but in a separate section of the site.

+1

Maybe the wiki?


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Friday 06 August 2010, Scott Marlowe elucidated thus:
> >> Everyone of my trainees want such thing - for databases, for other
> >> programming-languages etc. It's the worst thing you can give them.
> >> The< will copy, they will paste and they will understand nothing.
> >> Learning is the way to understanding, not copying.
> >
> > I couldn't disagree more.  Presenting working code (at least
> > snippets) should continue to be a fundamental part of any
> > documentation project.
>
> I agree. It's especially useful if you're dealing with folks who
> already have a clue, but may not be 100% familiar with how SQL or a
> particular language.  I had a Perl cookbook back in the day that was
> priceless when I was switching from C to Perl.  I didn't just copy
> and paste, but I did certainly learn a lot looking at other people's
> code.

I'll add in a "me too" only to say that I am someone that learns best by
example.  Reading about an API or function call is great, but I'll pick
it up faster, and understand it better, if you show me an example of it
in use (preferably multiple examples).

So, a "getting started" guide and/or cookbook would be great.  Another
cool idea: a MySQL -> PostgreSQL migration guide (I'm sure there's
already one out there) that would show "To do *this* MySQL function in
PostgreSQL, use this function/feature/etc."  A good example is
auto_increment -> serial. A comparison/contrast of permission would be
good too. I'm sure others can think of more examples.

So, that's my 2c.

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A

On Sat, Aug 7, 2010 at 12:29 AM, Joshua J. Kugler <joshua@eeinternet.com> wrote:

On 7 Aug 2010, at 5:19, Sandeep Srinivasa wrote:

> +1 on this.
> This is very interesting from the point-of-view of transitioning MySQL webapps to Postgres. The truth is that for a
lotof people, MySQL is their first DB (because of loads of pre-existing software. Refer to my thread "Which
CMS/Ecommerce/shoppingcart"). When we are ready to move to PG, we are already used to the MySQL way of doing things. 

Oh gosh, you make me remember my first MySQL experience! I had just started a small company that was starting their
mainproject on a MySQL/PHP environment on Windows. We ran into some trouble with Windows IIRC and the sysadmin was
happierrunning stuff on Linux too, so we switched our early code and database over to Linux. Turns out that in MySQL,
goingfrom a case-insensitive file-system to a case-sensitive one means that all your table names are now case-sensitive
aswell! 

That, and the struggle getting MySQL to actually use InnoDB and relational integrity on tables instead of just claiming
thatit did, made it a really easy case for me to convince my colleagues and boss to switch to Postgres. They haven't
lookedback since. 

That was back in the days of MySQL 4, but the scars it left are still there. That company has gone bankrupt in the
meantime(core developers moved away), but I run into some of my old colleagues every now and then and they're almost
allstill doing their stuff on Postgres - or at least not on MySQL. One exception is the guy who has to use one of their
real-timeengines for telecommunication, where data-integrity apparently isn't considered critical. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c5d30a7286211834955988!

2010/8/7 Alban Hertroys <dalroi@solfertje.student.utwente.nl>:
> On 7 Aug 2010, at 5:19, Sandeep Srinivasa wrote:
>
>> +1 on this.
>> This is very interesting from the point-of-view of transitioning MySQL webapps to Postgres. The truth is that for a
lotof people, MySQL is their first DB (because of loads of pre-existing software. Refer to my thread "Which
CMS/Ecommerce/shoppingcart"). When we are ready to move to PG, we are already used to the MySQL way of doing things. 
>

As it was happening in the last 10 years, PostgreSQL will slowly
gather more acceptance and MySQL will reduce in popularity down the
years. But, we should help the process along.

1. Almost all webhosting providers have MySQL support, but PostgreSQL
support is available from only a few who also have MySQL support.
Hence MySQL is universal and PostgreSQL is present as also available.

2. Books.
In a book store (where I live), technical sections have SQL, SQL
Server, PHP and MySQL, Oracle racks. There is no PostgreSQL rack.

3. Name
It is difficult to bring up the name in conversation.

To break these circles:

1. Study a typical web hosting set up and work on supporting
everything (Wordpress, Drupal, OS Commerce) with PostgreSQL.
PostgreSQL only servers should be made possible.

2. Bring out more books

a. Documentation is already available (PostgreSQL User Manual,
PostgreSQL Technical Documentation). Re-package them and publish as
books targeting different user levels.

b. Cook Books can be created from the discussions in this mailing list.

c. More PHP+PostgreSQL books should be created. <Professional PHP6>
from Wrox uses PostgreSQL as the default db.

3. The default configuration settings for PostgreSQL are not optimal
for performance. Can there be a recommended configuration file in the
installation (assuming certain amount of RAM and processor type) ?

4. A pet name
Is it possible to have a pet name which can be used in casual
conversation easily?

Ma Sivakumar
http://masivakumar.blogspot.com

On 8/7/2010 4:24 AM, சிவகுமார் மா wrote:
>
> 4. A pet name
> Is it possible to have a pet name which can be used in casual
> conversation easily?
>
PG

On 07/08/2010 11:24, சிவகுமார் மா wrote:
> 2010/8/7 Alban Hertroys<dalroi@solfertje.student.utwente.nl>:
>> On 7 Aug 2010, at 5:19, Sandeep Srinivasa wrote:
>>
>>> +1 on this.
>>> This is very interesting from the point-of-view of transitioning MySQL webapps to Postgres. The truth is that for a
lotof people, MySQL is their first DB (because of loads of pre-existing software. Refer to my thread "Which
CMS/Ecommerce/shoppingcart"). When we are ready to move to PG, we are already used to the MySQL way of doing things. 
>>
>
> As it was happening in the last 10 years, PostgreSQL will slowly
> gather more acceptance and MySQL will reduce in popularity down the
> years. But, we should help the process along.
>
> 1. Almost all webhosting providers have MySQL support, but PostgreSQL
> support is available from only a few who also have MySQL support.
> Hence MySQL is universal and PostgreSQL is present as also available.
>
> 2. Books.
> In a book store (where I live), technical sections have SQL, SQL
> Server, PHP and MySQL, Oracle racks. There is no PostgreSQL rack.
>
> 3. Name
> It is difficult to bring up the name in conversation.
>
> To break these circles:
>
> 1. Study a typical web hosting set up and work on supporting
> everything (Wordpress, Drupal, OS Commerce) with PostgreSQL.
> PostgreSQL only servers should be made possible.
>
> 2. Bring out more books
>
> a. Documentation is already available (PostgreSQL User Manual,
> PostgreSQL Technical Documentation). Re-package them and publish as
> books targeting different user levels.
>
> b. Cook Books can be created from the discussions in this mailing list.
>
> c. More PHP+PostgreSQL books should be created.<Professional PHP6>
> from Wrox uses PostgreSQL as the default db.

Yes, so does "Pro PHP" from Apress, though it doesn't mention PG on the
cover. There was a nice warm glow of "goes without saying..." about it,
as I remember. :-)

> 4. A pet name
> Is it possible to have a pet name which can be used in casual
> conversation easily?

"Postgres" or "PG" are the usual, AIUI.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Ray--

I would like to see in postgres:
1)Clustering
2)a more user-friendly interface to packages
3)eliminate the requirement to create a postgres user to execute the server binaries..I guess i never understood that requirement 
 
I would like to see in MySQL:
A progression back to OpenSource ..i dont see Oracle championing 2 database products simultaneously
although under the same roof it would seem the engineering resources currently devoted to Oracle features could easily be re-factored to MySQL..
a prime example of a much needed requirement for MySQL ..When will MySQL implement row-level locking instead of just table-level locking?

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

 
> Date: Sat, 7 Aug 2010 14:11:29 +0100
> From: rod@iol.ie
> To: masivakumar@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] MySQL versus Postgres
>
> On 07/08/2010 11:24, சிவகுமார் மா wrote:
> > 2010/8/7 Alban Hertroys<dalroi@solfertje.student.utwente.nl>:
> >> On 7 Aug 2010, at 5:19, Sandeep Srinivasa wrote:
> >>
> >>> +1 on this.
> >>> This is very interesting from the point-of-view of transitioning MySQL webapps to Postgres. The truth is that for a lot of people, MySQL is their first DB (because of loads of pre-existing software. Refer to my thread "Which CMS/Ecommerce/shopping cart"). When we are ready to move to PG, we are already used to the MySQL way of doing things.
> >>
> >
> > As it was happening in the last 10 years, PostgreSQL will slowly
> > gather more acceptance and MySQL will reduce in popularity down the
> > years. But, we should help the process along.
> >
> > 1. Almost all webhosting providers have MySQL support, but PostgreSQL
> > support is available from only a few who also have MySQL support.
> > Hence MySQL is universal and PostgreSQL is present as also available.
> >
> > 2. Books.
> > In a book store (where I live), technical sections have SQL, SQL
> > Server, PHP and MySQL, Oracle racks. There is no PostgreSQL rack.
> >
> > 3. Name
> > It is difficult to bring up the name in conversation.
> >
> > To break these circles:
> >
> > 1. Study a typical web hosting set up and work on supporting
> > everything (Wordpress, Drupal, OS Commerce) with PostgreSQL.
> > PostgreSQL only servers should be made possible.
> >
> > 2. Bring out more books
> >
> > a. Documentation is already available (PostgreSQL User Manual,
> > PostgreSQL Technical Documentation). Re-package them and publish as
> > books targeting different user levels.
> >
> > b. Cook Books can be created from the discussions in this mailing list.
> >
> > c. More PHP+PostgreSQL books should be created.<Professional PHP6>
> > from Wrox uses PostgreSQL as the default db.
>
> Yes, so does "Pro PHP" from Apress, though it doesn't mention PG on the
> cover. There was a nice warm glow of "goes without saying..." about it,
> as I remember. :-)
>
> > 4. A pet name
> > Is it possible to have a pet name which can be used in casual
> > conversation easily?
>
> "Postgres" or "PG" are the usual, AIUI.
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

pgtune [http://pgfoundry.org/projects/pgtune/]  is already available for this purpose.


Amitabh Kant

Hello

2010/8/7 Martin Gainty <mgainty@hotmail.com>:
> Ray--
>
> I would like to see in postgres:
> 1)Clustering
> 2)a more user-friendly interface to packages


> 3)eliminate the requirement to create a postgres user to execute the server
> binaries..I guess i never understood that requirement
>

it is simply - security - when pg run under root and somebody hacks
it, then he has a admin rights. When pg will run under postgres and
somebody hacks it the he have access only to database.

Regards

Pavel Stehule


> I would like to see in MySQL:
> A progression back to OpenSource ..i dont see Oracle championing 2 database
> products simultaneously
> although under the same roof it would seem the engineering resources
> currently devoted to Oracle features could easily be re-factored to MySQL..
> a prime example of a much needed requirement for MySQL ..When will MySQL
> implement row-level locking instead of just table-level locking?
>
> Martin Gainty
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
> dient lediglich dem Austausch von Informationen und entfaltet keine
> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
> destinataire prévu, nous te demandons avec bonté que pour satisfaire
> informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
> de ceci est interdite. Ce message sert à l'information seulement et n'aura
> pas n'importe quel effet légalement obligatoire. Étant donné que les email
> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
> aucune responsabilité pour le contenu fourni.
>
>
>
>
>> Date: Sat, 7 Aug 2010 14:11:29 +0100
>> From: rod@iol.ie
>> To: masivakumar@gmail.com
>> CC: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] MySQL versus Postgres
>>
>> On 07/08/2010 11:24, சிவகுமார் மா wrote:
>> > 2010/8/7 Alban Hertroys<dalroi@solfertje.student.utwente.nl>:
>> >> On 7 Aug 2010, at 5:19, Sandeep Srinivasa wrote:
>> >>
>> >>> +1 on this.
>> >>> This is very interesting from the point-of-view of transitioning MySQL
>> >>> webapps to Postgres. The truth is that for a lot of people, MySQL is their
>> >>> first DB (because of loads of pre-existing software. Refer to my thread
>> >>> "Which CMS/Ecommerce/shopping cart"). When we are ready to move to PG, we
>> >>> are already used to the MySQL way of doing things.
>> >>
>> >
>> > As it was happening in the last 10 years, PostgreSQL will slowly
>> > gather more acceptance and MySQL will reduce in popularity down the
>> > years. But, we should help the process along.
>> >
>> > 1. Almost all webhosting providers have MySQL support, but PostgreSQL
>> > support is available from only a few who also have MySQL support.
>> > Hence MySQL is universal and PostgreSQL is present as also available.
>> >
>> > 2. Books.
>> > In a book store (where I live), technical sections have SQL, SQL
>> > Server, PHP and MySQL, Oracle racks. There is no PostgreSQL rack.
>> >
>> > 3. Name
>> > It is difficult to bring up the name in conversation.
>> >
>> > To break these circles:
>> >
>> > 1. Study a typical web hosting set up and work on supporting
>> > everything (Wordpress, Drupal, OS Commerce) with PostgreSQL.
>> > PostgreSQL only servers should be made possible.
>> >
>> > 2. Bring out more books
>> >
>> > a. Documentation is already available (PostgreSQL User Manual,
>> > PostgreSQL Technical Documentation). Re-package them and publish as
>> > books targeting different user levels.
>> >
>> > b. Cook Books can be created from the discussions in this mailing list.
>> >
>> > c. More PHP+PostgreSQL books should be created.<Professional PHP6>
>> > from Wrox uses PostgreSQL as the default db.
>>
>> Yes, so does "Pro PHP" from Apress, though it doesn't mention PG on the
>> cover. There was a nice warm glow of "goes without saying..." about it,
>> as I remember. :-)
>>
>> > 4. A pet name
>> > Is it possible to have a pet name which can be used in casual
>> > conversation easily?
>>
>> "Postgres" or "PG" are the usual, AIUI.
>>
>> Ray.
>>
>> --
>> Raymond O'Donnell :: Galway :: Ireland
>> rod@iol.ie
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>

Scott Frankel schrieb:
>
> On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:
>
>> John Gage schrieb:
>>
>>> On reflection, I think what is needed is a handbook that features cut
>>> and paste code to do the things with Postgres that people do today
>>> with MySQL.
>>
>> Everyone of my trainees want such thing - for databases, for other
>> programming-languages etc. It's the worst thing you can give them.
>> The< will copy, they will paste and they will understand nothing.
>> Learning is the way to understanding, not copying.
>
> I couldn't disagree more.  Presenting working code (at least snippets)
> should continue to be a fundamental part of any documentation project.

You missunderstand me. Working code is a fundamental part of any
documentation. But we talk about a handbook with code that works in
PostgreSQL and does the same thinks in MySQL.
This way the trainees won't learn how PostgreSQL works, the just learn
the different examples. Giving them training-problems and the PostgreSQL
handbook is out of my experience the best way. It tooks longer for them
to solve the problems, but in this way they are able to solve problems,
which are not related to the presented examples.

Greetings from Germany,
Torsten

On Aug 8, 2010, at 2:45 AM, Torsten Zühlsdorff wrote:

> Scott Frankel schrieb:
>> On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:
>>> John Gage schrieb:
>>>
>>>> On reflection, I think what is needed is a handbook that features
>>>> cut and paste code to do the things with Postgres that people do
>>>> today with MySQL.
>>>
>>> Everyone of my trainees want such thing - for databases, for other
>>> programming-languages etc. It's the worst thing you can give them.
>>> The< will copy, they will paste and they will understand nothing.
>>> Learning is the way to understanding, not copying.
>> I couldn't disagree more.  Presenting working code (at least
>> snippets) should continue to be a fundamental part of any
>> documentation project.
>
> You missunderstand me. Working code is a fundamental part of any
> documentation. But we talk about a handbook with code that works in
> PostgreSQL and does the same thinks in MySQL.
> This way the trainees won't learn how PostgreSQL works, the just
> learn the different examples. Giving them training-problems and the
> PostgreSQL handbook is out of my experience the best way. It tooks
> longer for them to solve the problems, but in this way they are able
> to solve problems, which are not related to the presented examples.

I understand and appreciate your position.  Thanks for the
clarification.

While I believe that this thread has, for all intents and purposes,
run its course (and I look forward to reading the documentation it
informs), I'm going to go out on a limb and present an additional use-
case that may be unpopular, or at least controversial.

There are times when a documentation's audience is not interested in
taking the subject matter to expert level.  (eg:  informed supervisory
or vendor-client relationships, proof of concept development, hobbies,
&c.).  For those cases, "a working understanding" is all that's
strictly necessary.  Annotated, cookbook-style code reference is
especially well suited for that mode of learning.

Regards,
Scott




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

On Mon, Aug 9, 2010 at 1:51 AM, Scott Frankel <frankel@circlesfx.com> wrote:
>
> On Aug 8, 2010, at 2:45 AM, Torsten Zühlsdorff wrote:
>
>> Scott Frankel schrieb:
>>>
>>> On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:
>>>>
>>>> John Gage schrieb:
>>>>
>>>>> On reflection, I think what is needed is a handbook that features cut
>>>>> and paste code to do the things with Postgres that people do today with
>>>>> MySQL.
>>>>
>>>> Everyone of my trainees want such thing - for databases, for other
>>>> programming-languages etc. It's the worst thing you can give them. The< will
>>>> copy, they will paste and they will understand nothing. Learning is the way
>>>> to understanding, not copying.
>>>
>>> I couldn't disagree more.  Presenting working code (at least snippets)
>>> should continue to be a fundamental part of any documentation project.
>>
>> You missunderstand me. Working code is a fundamental part of any
>> documentation. But we talk about a handbook with code that works in
>> PostgreSQL and does the same thinks in MySQL.
>> This way the trainees won't learn how PostgreSQL works, the just learn the
>> different examples. Giving them training-problems and the PostgreSQL
>> handbook is out of my experience the best way. It tooks longer for them to
>> solve the problems, but in this way they are able to solve problems, which
>> are not related to the presented examples.
>
> I understand and appreciate your position.  Thanks for the clarification.
>
> While I believe that this thread has, for all intents and purposes, run its
> course (and I look forward to reading the documentation it informs), I'm
> going to go out on a limb and present an additional use-case that may be
> unpopular, or at least controversial.
>
> There are times when a documentation's audience is not interested in taking
> the subject matter to expert level.  (eg:  informed supervisory or
> vendor-client relationships, proof of concept development, hobbies, &c.).
>  For those cases, "a working understanding" is all that's strictly
> necessary.  Annotated, cookbook-style code reference is especially well
> suited for that mode of learning.
>
> Regards,
> Scott
>
>

I think it is important to have some examples in the documentation.
Some kind of balance between few examples and too many examples.
From what I have seen many users in this mailing list usually do
include some example code of the problem they have as an aid to
explaining their current situation. This seems to me quite useful. The
other day I asked for help in implementing  "DISTINCT ON" like
behaviour and was pointed correctly to the docs. The examples in
"DISTINCT ON" documentation quickly led me to understand the working
of this command. Sometime back I was learning about Window functions
(rank() and so on) where I found the examples very useful and I feel
it would have been a challenge for me to understand these concepts
without the use of examples.

There may be worry of "copy and paste" without proper understanding of
the code and concepts but this may be mitigated IMHO by fact that it
seems unlikely that when presented with a case to solve, simple "copy
and paste" of several commands (in the right sequence) will happen and
correctly solve the problem at hand without appreciation of what these
commands and statements do. Also more often or not the "copy and
paste" will only work on specific schema definitions and data used in
the example, therefore reconstruction (hence understanding) of these
commands is neccessary.



Allan.

On Mon, Aug 9, 2010 at 12:15 PM, Allan Kamau <kamauallan@gmail.com> wrote:

Sandeep Srinivasa wrote on 09.08.2010 08:54:
> The way I see it - for those who want to truly learn, there is the
> documentation. For those who dont, there are ORMs.

Another of those ORM myths ;)

ORMs are not an alternative to learning SQL or understand how a DBMS works.
You need to be good at SQL and you need a good understanding of relational databases in order to use an ORM
efficiently.

One of the first sentences in the Hibernate manual is: "If you have a limited knowledge of JAVA or SQL, it is advised
thatyou start with a good introduction to that technology prior to attempting to learn Hibernate" 

Regards
Thomas

On 09/08/10 14:54, Sandeep Srinivasa wrote:

>
> The way I see it - for those who want to truly learn, there is the
> documentation. For those who dont, there are ORMs.

Ha, I wish!

Despite being rather comfortable with SQL I've been using the Hibernate
ORM system in a project to try to reduce some of the repetitive coding,
while falling back to JDBC and hand-coded SQL where Hibernate doesn't do
a great job. This is my first venture into ORM-land, and may well be my
last.

The amount of learning required in getting the ORM to behave even
vaguely sanely in anything but trivial situations is vastly greater than
what's required to use plain SQL. I'm not at all sure it's worth it for
anything but the hugest projects, as I've wasted way more time battling
Hibernate than I would've done writing all the repetitive template
classes and SQL mappings myself. All the lazy-loading stuff is useless
in practice, because you're always working with detached entities by the
time you need it, so it doesn't help with the problem of figuring out
what data your app is going to need well before it asks for it.

Additionally, ORM system authors seem to consider the database to be
getting uppity and above its place if it's used for anything much more
than a dumb row store. Basic database features like referential
integrity constraints (especially things like ON DELETE CASCADE),
in-database triggers, column privileges, etc tend to confuse it
mightily, because it assumes it'll be the only thing making changes to
the database. Hibernate is better than most in this regard, and way
better than things like ActiveRecord (from Ruby on Rails) in that it
understands most basic database features and can be told not to cache
between sessions, but it still gets frustrating as soon as you try to do
things like use "weird" data types like the native "xml" type in Pg,
(for which I had to write a custom UserType mapping).

You certainly do need to have a decent understanding of basic SQL to use
an ORM reasonably efficiently, including the trade-offs of joins vs
subqueries, how queries can be rewritten/replanned by the database, the
cost of vast numbers of repeated small queries vs large-and-expensive
one-off multi-way joins that return repetitive information, the effect
of latency and planning time, the difference between prepared and
one-off statements, parameter placement, etc. That said, there are large
sections of the SQL language that most ORMs appear to never go anywhere
near. You won't be using window functions and custom aggregates in any ORM.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

>
> On Aug 8, 2010, at 2:45 AM, Torsten Zühlsdorff wrote:
>
*snip*
>
> I understand and appreciate your position.  Thanks for the
> clarification.
>
> While I believe that this thread has, for all intents and purposes,
> run its course (and I look forward to reading the documentation it
> informs), I'm going to go out on a limb and present an additional use-
> case that may be unpopular, or at least controversial.
>
> There are times when a documentation's audience is not interested in
> taking the subject matter to expert level.  (eg:  informed supervisory
> or vendor-client relationships, proof of concept development, hobbies,
> &c.).  For those cases, "a working understanding" is all that's
> strictly necessary.  Annotated, cookbook-style code reference is
> especially well suited for that mode of learning.

As a recent convert from MySQL (I needed PostGIS) who has also seen the
benefit of Postgresql over MySQL in numerous other areas, that's exactly
what I am doing for myself.

I have 4x6 cards that I write the postgresql way of doing what I use to do
with MySQL so that I can easily reference them when I need to.

Should I sit down and read a book and go through the exercises?
Yes. But I need to get stuff done now, and the cheat sheets I make for
myself let me do just that.

I am not a DBA - I am not even a web developer.
I do both because I can't afford to hire them, and when I have used stuff
created by them, very frequently their code is clearly crap and insecure
and even I can see that, so unless I really want to pay the big bucks,
it's better for me to do it myself and cheat sheets really help.

-----
Michael A. Peters

http://www.shastaherps.org/

It's not a requirement, just a reasonable default.

On Aug 7, 2010, at 11:09 AM, Martin Gainty wrote:

> 3)eliminate the requirement to create a postgres user to execute the server binaries..I guess i never understood that
requirement 


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

>>>>> "Joshua" == Joshua J Kugler <joshua@eeinternet.com> writes:

Joshua> I'll add in a "me too" only to say that I am someone that learns
Joshua> best by example.

Keep in mind though that there are three primary learning modes:
- example
- concept
- structure

Do not overemphasize the example mode at the cost of presenting concepts
or structure.  You need all three.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion

On Mon, 2010-08-09 at 10:09 -0600, Scott Ribe wrote:
> It's not a requirement, just a reasonable default.

The actual requirement is:

Thou shall not use a privelaged user, e.g; Administrator or UID = 0.

Not only is that a reasonable default, MySQL is broken because of
theirs.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Monday 09 August 2010, Randal L. Schwartz elucidated thus:
> >>>>> "Joshua" == Joshua J Kugler <joshua@eeinternet.com> writes:
>
> Joshua> I'll add in a "me too" only to say that I am someone that
> learns Joshua> best by example.
>
> Keep in mind though that there are three primary learning modes:
> - example
> - concept
> - structure
>
> Do not overemphasize the example mode at the cost of presenting
> concepts or structure.  You need all three.

Oh, definitely.  I like reading the concepts and structure of how, say,
an API works, and use it for reference, but what really cements *how*
to use it is those examples.

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A

On Monday 09 August 2010, Joshua D. Drake elucidated thus:
> On Mon, 2010-08-09 at 10:09 -0600, Scott Ribe wrote:
> > It's not a requirement, just a reasonable default.
>
> The actual requirement is:
>
> Thou shall not use a privelaged user, e.g; Administrator or UID = 0.
>
> Not only is that a reasonable default, MySQL is broken because of
> theirs.
>
> Joshua D. Drake

Hmm...I've always seen MySQL run under the user mysql.  Of course,
mysqld_safe (the script that restarts mysql if it crashes) starts as
root, but the actually binary runs as mysql.

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A

"Joshua J. Kugler" <joshua@eeinternet.com> writes:
> On Monday 09 August 2010, Joshua D. Drake elucidated thus:
>> The actual requirement is:
>>
>> Thou shall not use a privelaged user, e.g; Administrator or UID = 0.
>>
>> Not only is that a reasonable default, MySQL is broken because of
>> theirs.

> Hmm...I've always seen MySQL run under the user mysql.  Of course,
> mysqld_safe (the script that restarts mysql if it crashes) starts as
> root, but the actually binary runs as mysql.

That's how it's done if the user/packager knows what they're doing.
The problem is that not only doesn't mysql enforce that, it isn't
the default --- mysqld_safe is perfectly happy to launch the server
as root if you don't tell it not to.  If you dig hard enough in their
manuals, you can find a recommendation to not run the server as root;
but they don't exactly push you to avoid that.

            regards, tom lane

On Mon, 2010-08-09 at 10:09 -0600, Scott Ribe wrote:
> It's not a requirement, just a reasonable default.

The actual requirement is:

Thou shall not use a privelaged user, e.g; Administrator or UID = 0.

Not only is that a reasonable default, MySQL is broken because of
theirs.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Randal L. Schwartz wrote:
> Do not overemphasize the example mode at the cost of presenting concepts
> or structure.  You need all three.

Hmm I recently had to find out the various options to get values from a tuple
via SPI - referred by docs to the contrib examples :-(
Docs did not help and the examples only cover very basic cases.
I found the quickest way to find the list of macro's available was to read the source code :-)

I suppose the source is the final layer of documentation!

Jacqui

சிவகுமார் மா wrote:
> 1. Almost all webhosting providers have MySQL support, but PostgreSQL
> support is available from only a few who also have MySQL support.
> Hence MySQL is universal and PostgreSQL is present as also available.
>

http://www.postgresql.org/support/professional_hosting lists far more
than "a few".  I think the only real area to complain about here is that
there are definitely multiple sources for free small-scale MySQL
hosting, but none I'm aware of for PostgreSQL.  I'm not sure what anyone
here can do about that though.

> a. Documentation is already available (PostgreSQL User Manual,
> PostgreSQL Technical Documentation). Re-package them and publish as
> books targeting different user levels.
>

Fultus already sells printed copies of the documentation.  They're not
very popular.  Given that the trend everywhere is toward e-books, and
you can get a free PostgreSQL manual in PDF form already, I'm not sure
who exactly would be served by repackaging them in any way.  What's
needed here is completely new content.

> b. Cook Books can be created from the discussions in this mailing list.
>

We've already been doing that on the -performance list successfully; a
good portion of things people used to get individual responses to now
can be directed toward an article from
http://wiki.postgresql.org/wiki/Performance_Optimization instead.

The problem is that few discussions happen on these lists for things
like "how do I get used to PostgreSQL after growing up on MySQL?"  And
that material will never be appropriate for the PostgreSQL
documentation.  I would highly encourage people to migrate their own
personal notes on such things to the very under-maintained section at
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

> 3. The default configuration settings for PostgreSQL are not optimal
> for performance. Can there be a recommended configuration file in the
> installation (assuming certain amount of RAM and processor type) ?
>

This doesn't work because there are many different types of database
applications, and what's optimal even as a starting configuration for
each type is very different.  Also, hardware changes pretty fast; you'd
be hard pressed to write down useful generic recommendations (or insert
them into the core database code) that are still relevant at all after a
release has been out a few years.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

    Greg Smith wrote:

> The problem is that few discussions happen on these lists for things
> like "how do I get used to PostgreSQL after growing up on MySQL?"  And
> that material will never be appropriate for the PostgreSQL
> documentation.  I would highly encourage people to migrate their own
> personal notes on such things to the very under-maintained section at
> http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

On the other hand, some of the notes already mentioned on the wiki, such as
for example:
http://www.xach.com/aolserver/mysql-to-postgresql.html
...are so outdated and/or bad that they're probably counter-productive.

Also, a page such as
http://wiki.postgresql.org/wiki/How_to_make_a_proper_migration_from_MySQL_to_
PostgreSQL
reads as something that (unintentionally I imagine) discourages people from
trying to migrate rather than providing any actual help. You don't help
people by telling them a migration will be very long and hard and that they
should probably redesign a lot, without knowing anything about their
application/database. It could be just as well very smooth with immediate
benefits.

IMHO such contents should simply be scraped/unlinked.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On 11/08/2010 04:01, Greg Smith wrote:
>> 3. The default configuration settings for PostgreSQL are not optimal
>> for performance. Can there be a recommended configuration file in the
>> installation (assuming certain amount of RAM and processor type) ?
>
> This doesn't work because there are many different types of database
> applications, and what's optimal even as a starting configuration for
> each type is very different. Also, hardware changes pretty fast; you'd
> be hard pressed to write down useful generic recommendations (or insert
> them into the core database code) that are still relevant at all after a
> release has been out a few years.

Well, many defaults are hardcoded into a file now. I'd like to see
'auto' among possible values of parameters, e.g.:

max_connections = auto
shared_buffers = auto
work_mem = auto

with PG wild guessing reasonable values based on system specs. It may be
a awful piece of code (getting system info is very platform specific),
and sometimes the guess may be wrong. Anyway nothing prevents PG to have
a postgresql_failsafe.conf.

Not that I'm advocating it. Complex systems need well-thought configuration.

.TM.

Hi,

I've started using ARRAY data type recently, and I fell into the
following problem:

When I have a relatively large ARRAY (like [1:500]) takeing an aggregate
function on its elements is not so easy. One has to iterate all the
elements, like this:

SELECT min(A[1]) as a1, min(A[2]) as a2, ...

This is because aggregate functions are not defined on ARRAY types. Or
may be there is an easier and more readable way to do that?

-R

On Wed, Aug 11, 2010 at 8:42 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> Hi,
>
> I've started using ARRAY data type recently, and I fell into the
> following problem:
>
> When I have a relatively large ARRAY (like [1:500]) takeing an aggregate
> function on its elements is not so easy. One has to iterate all the
> elements, like this:
>
> SELECT min(A[1]) as a1, min(A[2]) as a2, ...
>
> This is because aggregate functions are not defined on ARRAY types. Or
> may be there is an easier and more readable way to do that?

If you have a fixed number of elements across the entire table, you
can accomplish what I think you are trying to do by expanding all the
arrays in the table and regrouping based on generate_series(), but
this is a horribly inefficient way to go.  Are you sure you aren't
looking at table design issue?

merlin

Daniel Verite wrote:
> On the other hand, some of the notes already mentioned on the wiki, such as
> for example:
> http://www.xach.com/aolserver/mysql-to-postgresql.html
> ...are so outdated and/or bad that they're probably counter-productive.
> ...
> IMHO such contents should simply be scraped/unlinked.
>

I would happily start removing the obviously outdated content from the
wiki if something better came along.  It's hard to blow away stuff just
because it could be better right now, when that would leave that section
near empty at the end.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Marco Colombo wrote:
> Well, many defaults are hardcoded into a file now. I'd like to see
> 'auto' among possible values of parameters, e.g.:
> with PG wild guessing reasonable values based on system specs. It may
> be a awful piece of code (getting system info is very platform
> specific), and sometimes the guess may be wrong.

The problem here is that the amount of shared memory a system can
allocate is hard to discover any other way than starting the server and
seeing if it works.  So doing what you advise will leave the database
unable to start on any system that hasn't gotten the right OS kernel
tweaks done first.  If there was an obvious 'auto' setting, I guarantee
you newcomers would use it without consulting the documentation first,
then tell everyone it doesn't work because their server broke when it
was enabled.  There is a technique used when you run initdb to try and
detect the shared memory allocation range possible, but it really
doesn't scale very well into the sizes needed to be useful for this.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

On 11/08/2010 17:34, Greg Smith wrote:
> The problem here is that the amount of shared memory a system can
> allocate is hard to discover any other way than starting the server and
> seeing if it works. So doing what you advise will leave the database
> unable to start on any system that hasn't gotten the right OS kernel
> tweaks done first.

Well, is that true under Windows, too? I think we need to cover Windows,
here.

Under unix, having postgresql start correctly is a concern of the
distribution vendor. Even if the guessing isn't bullet-proof, the vendor
either knows how to configure the kernel to have the 'auto' thing work,
or is able to provide its own postgresql.conf.

Sure, there are people who download and compile, but I don't think they
are afraid of editing postgresql.conf should the server fail to start.

Also, I'd say this is a case where it's much better to fail with a
message "listen buddy, your server has 64GB of RAM installed but your
kernel is configured for 20MB of shared memory only, you should really
increase it", rather than start successfully but with very poor
performance. It's a matter of correctness: I see PG as a high
performance database system. Allowing to start it in awfully suboptimal
conditions it's no different from allowing '0000-00-00' as a date: it
may give you the idea you did the right thing, but most of the time you
didn't.

.TM.

Marco Colombo <pgsql@esiway.net> writes:
> It's a matter of correctness: I see PG as a high
> performance database system. Allowing to start it in awfully suboptimal
> conditions it's no different from allowing '0000-00-00' as a date: it
> may give you the idea you did the right thing, but most of the time you
> didn't.

Unfortunately, there are quite a few of us for whom "correctness"
doesn't mean "automatically try to eat all the resources available".
Your view of what is useful behavior is far too narrow-minded ...

            regards, tom lane

2010/8/12 Tom Lane <tgl@sss.pgh.pa.us>:
>
> Unfortunately, there are quite a few of us for whom "correctness"
> doesn't mean "automatically try to eat all the resources available".
> Your view of what is useful behavior is far too narrow-minded ...

The point is, some one installing PostgreSQL for the first time is not
aware that configuration parameters should be changed to much higher
values than defaults in postgresql.conf

There can be a stronger and more prominent hint / recommendation in
postgresql.conf file,  in install README, Resource Consumption section
of manual (http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html)
and other appropriate places  :

1. README has after Post Installation Setup, Getting Started under What Now?

"------------ The default configuration is, however, not designed for
optimum performance. To achieve optimum performance, several server
parameters must be adjusted, the two most common being shared_buffers
and work_mem.  ---------------"

===== This comment can be included immediately after Post Installation Setup.

3. Then in the manual,  shared_buffers section says

"The default is typically 32 megabytes (32MB), but might be less if
your kernel settings will not support it (as determined during
initdb). This setting must be at least 128 kilobytes. (Non-default
values of BLCKSZ change the minimum.) However, settings significantly
higher than the minimum are usually needed for good performance."

"If you have a dedicated database server with 1GB or more of RAM, a
reasonable starting value for shared_buffers is 25% of the memory in
your system."

and then there are comments like
"it is unlikely that an allocation of more than 40% of RAM to
shared_buffers will work better than a smaller amount."

"Increasing this parameter might cause PostgreSQL to request more
System V shared memory than your operating system's default
configuration allows. See Section 17.4.1 for information on how to
adjust those parameters, if necessary"

====== Can a final

"We recommend that you to set shared_buffers to 25% of system memory
you can allocate for PostgreSQL"

be included.

4. work_mem section says

"Note that for a complex query, several sort or hash operations might
be running in parallel; each one will be allowed to use as much memory
as this value specifies before it starts to put data into temporary
files. Also, several running sessions could be doing such operations
concurrently. So the total memory used could be many times the value
of work_mem; it is necessary to keep this fact in mind when choosing
the value. Sort operations are used for ORDER BY, DISTINCT, and merge
joins. Hash tables are used in hash joins, hash-based aggregation, and
hash-based processing of IN subqueries. "

This makes Increasing work_mem is a complex calculation for a new user
trying out PostgreSQL.

==== Can something like this included?

"It is recommended that about X% of your system memory to be set as
work_mem. You may have to monitor system performance and adjust this
setting as required"

Best regards,

Ma Sivakumar
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com

Ma Sivakumar wrote:
> There can be a stronger and more prominent hint / recommendation in
> postgresql.conf file,  in install README, Resource Consumption section
> of manual (http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html)
> and other appropriate places
>

What you might not be aware of is that most of the text you quoted was
just added to the PostgreSQL documentation in the last couple of
months.  That already represents a good step forward from earlier
versions; until quite recently
http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html
was how that looked even for 8.3 and 8.4.  I'm happy that we've already
made a big step forward in helping new users here in that update.  At
least now DBAs used to other systems who go looking for the "how do I
set the size of the cache?" knob will stumble on reasonable advice in
the manual, which was not the case before.  So I already expect this
situation to be much better than it has ever been.

> Can a final
> "We recommend that you to set shared_buffers to 25% of system memory
> you can allocate for PostgreSQL"
> be included.
>

If only it were that easy.  25%, but only on a dedicated server, don't
go above 8GB, limit to much less than that on Windows, and be extremely
careful if you're writing heavily lest large checkpoints squash you.
Giving simple advice that people might follow without thinking about
actually has some worst-case downsides that are worse than not tuning
the server at all.

> This makes Increasing work_mem is a complex calculation for a new user
> trying out PostgreSQL.
>

Increasing work_mem is a complex calculation for everyone, not just new
users.  If it were easy for anyone, we'd just bottle whatever experts do
into the software directly.  I tried doing a round of that with pgtune,
and it's still not quite right yet even after surveying everyone who had
an opinion on the subject.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Sandeep Srinivasa wrote:
>  Maybe a tabular form would be nice - "work_mem" under...

The problem with work_mem in particular is that the useful range depends
quite a bit on how complicated you expect the average query running to
be.  pgtune tries to model this using an input of what type of
application you're running; presuming that a data warehouse application
is far more likely to sort/hash a lot of data in one query than a web
application for example.  But even that relatively simple idea has
proven to be confusing to a lot of people, based on the feedback I've
gotten.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

On Thu, Aug 12, 2010 at 10:53 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> I'm happy that we've already made
> a big step forward in helping new users here in that update.  At least now
> DBAs used to other systems who go looking for the "how do I set the size of
> the cache?" knob will stumble on reasonable advice in the manual, which was
> not the case before.  So I already expect this situation to be much better
> than it has ever been.

Thanks for that.

While writing the message, I remembered that the manual does not
stress much on the point, but on checking the latest version I found
it to be better. I should have checked the earlier versions also :-)

>> Can a final
>> "We recommend that you to set shared_buffers to 25% of system memory
>> you can allocate for PostgreSQL"
>> be included.
>>
>
> If only it were that easy.  25%, but only on a dedicated server, don't go
> above 8GB, limit to much less than that on Windows, and be extremely careful
> if you're writing heavily lest large checkpoints squash you.  Giving simple
> advice that people might follow without thinking about actually has some
> worst-case downsides that are worse than not tuning the server at all.
>

What does a migrating PHP/MySQL user do? If MySQL performs fast just
out of box (I have not used MySQL), what is different there? Do MySQL
defaults give better performance? How do they arrive at those
defaults?

Or is it a completely different system, which can not be adapted in PostgreSQL?

>> This makes Increasing work_mem is a complex calculation for a new user
>> trying out PostgreSQL.
>>
>
> Increasing work_mem is a complex calculation for everyone, not just new
> users.  If it were easy for anyone, we'd just bottle whatever experts do
> into the software directly.  I tried doing a round of that with pgtune, and
> it's still not quite right yet even after surveying everyone who had an
> opinion on the subject.

Again, what happens in MySQL? Do they have an equivalent parameter to
be set? and how it is set?

Thanks and regards,

Ma Sivakumar
http://masivakumar.blogspot.com

On Wed, Aug 11, 2010 at 11:41 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Sandeep Srinivasa wrote:
>>
>>  Maybe a tabular form would be nice - "work_mem" under...
>
> The problem with work_mem in particular is that the useful range depends
> quite a bit on how complicated you expect the average query running to be.

And it's very dependent on max connections.  A machine with 512GB that
runs batch processes for one or two import processes and then has
another two or three used to query it can run much higher work_mem
than a machine with 32G set to handle hundreds of concurrent accesses.
 Don't forget that when you set work_mem to high it has a very sharp
dropoff in performance as swapping starts to occur.  If work_mem is a
little low, queries run 2 or 3 times slower.  If it's too high the
machine can grind to a halt.

On Thursday 12. August 2010 08.29.13 Ma Sivakumar wrote:
> What does a migrating PHP/MySQL user do? If MySQL performs fast just
> out of box (I have not used MySQL), what is different there? Do MySQL
> defaults give better performance? How do they arrive at those
> defaults?
>
> Or is it a completely different system, which can not be adapted in
PostgreSQL?

For the vast majority of use cases, ie. with db size < 100 MB, PostgreSQL
performs well enough out of the box. I guess that the same goes for MySQL.

For the relatively few high-profile, high-performance installations some manual
tuning obviously is needed, and I guess that applies whatever the name of
system is. That is of course one of the major reasons why such installations
need qualified DBAs. But for the long-tail segment where most of us belong,
this isn't a problem.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

On Wed, 2010-08-11 at 09:53 -0400, Merlin Moncure wrote:
> On Wed, Aug 11, 2010 at 8:42 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
[....]
> >
> > SELECT min(A[1]) as a1, min(A[2]) as a2, ...
> >
> > This is because aggregate functions are not defined on ARRAY types. Or
> > may be there is an easier and more readable way to do that?
>
> If you have a fixed number of elements across the entire table, you

I don't. Although I may try to constriant the problem to that if I
assume maximum size of the array.

> can accomplish what I think you are trying to do by expanding all the
> arrays in the table and regrouping based on generate_series(), but
> this is a horribly inefficient way to go.  Are you sure you aren't
> looking at table design issue?

I actually did a sort of this, by having an intermediate table which
decomposes the array into separate table and have the aggregate
computted then. But his is hirrible, unreadable and not usefull only in
case of single dimention array.

I'm gathering measurement data, which most suitable go into an array.
The data is time related and array is used to bin it up.... into a
variable number of bins - array of variable size.

Any hints on how can I arrange that sort of data (preferably into an
array) and subsequently be able to compute statistics functions on all
those bins at the same time?

-R

On Thu, 2010-08-12 at 11:59 +0530, Ma Sivakumar wrote:

> What does a migrating PHP/MySQL user do? If MySQL performs fast just
> out of box (I have not used MySQL), what is different there? Do MySQL
> defaults give better performance? How do they arrive at those
> defaults?

I have been watching this thread off and on and I think this is the
question that many are kind of ignoring. With deepest respect to Greg
Smith who frankly knows more about intricacies PostgreSQL performance
than I would ever care to, I think he is looking at this wrong.

"Can we just say in the docs say 25% of memory to shared_buffers"

Yes, in fact we can. With the caveat of Windows, the reality is this
isn't going to hurt nearly as much as a untuned version of PostgreSQL
will.

Now work_mem is an entirely different issue. Frankly it doesn't need to
be changed, even from the default. *IF* you spill over it will be on
specific larger queries that you can then tune.

We should and can put in the docs a table that says:

GOOD PERFORMANCE IS ALWAYS RELIANT ON PROPER HARDWARE, DATABASE DESIGN
AND APPLICATION ARCHITECTURE. THIS TABLE IS A HINT ONLY. YOU WILL LIKELY
HAVE TO TUNE BEYOND THIS.

shared_buffers = 25% of available memory
work_mem = 2-4MB (test using explain analyze)
effective_cache_size = 50-60% of available memory INCLUDING
shared_buffers


etc....

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Thu, Aug 12, 2010 at 8:42 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> GOOD PERFORMANCE IS ALWAYS RELIANT ON PROPER HARDWARE, DATABASE DESIGN
> AND APPLICATION ARCHITECTURE. THIS TABLE IS A HINT ONLY. YOU WILL LIKELY
> HAVE TO TUNE BEYOND THIS.
>
> shared_buffers = 25% of available memory
> work_mem = 2-4MB (test using explain analyze)
> effective_cache_size = 50-60% of available memory INCLUDING
> shared_buffers

There was also a point about making the installer create a recommended
postgresql.conf file.

After installation when the server is started for the first time, try
to start with postgresql.conf.suggested

If it starts successfully, let the user know that the server is
started with a configuration file tuned for the specific system
resources. Also warn about the pitfalls.

If it fails to start, then fall back to default postgresql.conf and
tell the user to configure manually (using pgtune) for optimum
performance.

Is this doable?

Ma Sivakumar
http://masivakumar.blogspot.com

About the shared buffers size configuration discussion:

Like a few others here, I've spent a sizable proportion of my career
dealing with this issue (not with PG, with other products I've developed
that had a similar in-memory page pool).

There are roughly six stages in understanding this problem:

Stage 1: "Just make it figure out how much memory the system has, and go
use all of it".
Stage 2: "Oh, oops, often that's too much. The process won't start or
the system becomes unstable. Darn."
Stage 3: "Ok, how about we make it use a big pile of the system's
memory, but not so much that it won't start and the system won't become
unstable".
Stage 4: "Oh, there's no practical way to achieve #3 (e.g. the
filesystem cache completes with your buffer space in unpredictable and
unstable ways). Oops".
Stage 5: "Ah...you know, using all the available memory isn't even
necessarily the goal of the system's owner -- they may be running other
applications that should be allowed to use most of the available memory".
Stage 6: "Rats, without some kind of resource policy allocation
mechanism built into the OS, this problem is intractable, let's try to
document the issue as best we can and define a moderately sized default
so nobody shoots their feet off. The filesystem cache does a reasonable
job for most deployments anyway.".

On Thu, 2010-08-12 at 11:59 +0530, Ma Sivakumar wrote:

> What does a migrating PHP/MySQL user do? If MySQL performs fast just
> out of box (I have not used MySQL), what is different there? Do MySQL
> defaults give better performance? How do they arrive at those
> defaults?

I have been watching this thread off and on and I think this is the
question that many are kind of ignoring. With deepest respect to Greg
Smith who frankly knows more about intricacies PostgreSQL performance
than I would ever care to, I think he is looking at this wrong.

"Can we just say in the docs say 25% of memory to shared_buffers"

Yes, in fact we can. With the caveat of Windows, the reality is this
isn't going to hurt nearly as much as a untuned version of PostgreSQL
will.

Now work_mem is an entirely different issue. Frankly it doesn't need to
be changed, even from the default. *IF* you spill over it will be on
specific larger queries that you can then tune.

We should and can put in the docs a table that says:

GOOD PERFORMANCE IS ALWAYS RELIANT ON PROPER HARDWARE, DATABASE DESIGN
AND APPLICATION ARCHITECTURE. THIS TABLE IS A HINT ONLY. YOU WILL LIKELY
HAVE TO TUNE BEYOND THIS.

shared_buffers = 25% of available memory
work_mem = 2-4MB (test using explain analyze)
effective_cache_size = 50-60% of available memory INCLUDING
shared_buffers


etc....

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Joshua D. Drake wrote:
> "Can we just say in the docs say 25% of memory to shared_buffers"
> Yes, in fact we can. With the caveat of Windows, the reality is this
> isn't going to hurt nearly as much as a untuned version of PostgreSQL
> will.
>

With modern servers often shipping with 72GB of RAM now, that would make
shared_buffers set to 18GB.  This is an absolutely disastrous setting
for PostgreSQL in its current state; I'm seeing servers with that much
RAM that suffer enormous problems with a far lower shared_buffers than
that in production.  I'm working on a doc patch to address this better
before 9.0 goes out but I assure you this simple rule of thumb is
already nearing its end of life as a good one for big systems.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

> With modern servers often shipping with 72GB of RAM now, that would make
> shared_buffers set to 18GB.  This is an absolutely disastrous setting
> for PostgreSQL in its current state; I'm seeing servers with that much
> RAM that suffer enormous problems with a far lower shared_buffers than
> that in production.  I'm working on a doc patch to address this better
> before 9.0 goes out but I assure you this simple rule of thumb is
> already nearing its end of life as a good one for big systems.

What's the problem with 18GB shared_buffers exactly?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

Tatsuo Ishii wrote:
> What's the problem with 18GB shared_buffers exactly

It's slower than smaller numbers, and if you actually dirty a
significant portion of it you can have a checkpoint that takes hours to
sync, completely trashing system responsiveness for a good portion of it.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Ma Sivakumar wrote:
> There was also a point about making the installer create a recommended
> postgresql.conf file.
>
> After installation when the server is started for the first time, try
> to start with postgresql.conf.suggested
>
> If it starts successfully, let the user know that the server is
> started with a configuration file tuned for the specific system
> resources. Also warn about the pitfalls.
>
> If it fails to start, then fall back to default postgresql.conf and
> tell the user to configure manually (using pgtune) for optimum
> performance.
>

You're assuming most users usually start the server by hand where they
will see such errors.  They don't.  If you've got a typical system where
the startup/shutdown of the server is being handled by the OS, people
will never see the messages you're suggesting to present to them unless
they go look at the logs.

I'm going to bow out of this thread now.  We have a quite reasonable
plan for sorting this whole situation out that has been moving forward a
little bit with each successive release.  I know everyone is just trying
to help, but all of the suggestions here have been beaten to death at
least a half dozen times on other lists.  If someone wants to work on
patches to improve the specific hurdles here, I'd be happy to suggest
some.  There's already a vast excess of ideas for what to do, what's
missing is manpower to execute on them.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

On Thu, 2010-08-12 at 20:01 -0400, Greg Smith wrote:
> Joshua D. Drake wrote:
> > "Can we just say in the docs say 25% of memory to shared_buffers"
> > Yes, in fact we can. With the caveat of Windows, the reality is this
> > isn't going to hurt nearly as much as a untuned version of PostgreSQL
> > will.
> >
>
> With modern servers often shipping with 72GB of RAM now,

No they aren't. Those are servers you and I will deal with. Not 98% of
the users, using PostgreSQL.

Heck you could even say, "On systems with less than 16GB", for systems
larger contact your PostgreSQL support professional.

The point is, this is solvable for the majority base. There are always
corner cases and 72GB of ram is an extreme corner case.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

> It's slower than smaller numbers, and if you actually dirty a
> significant portion of it you can have a checkpoint that takes hours to
> sync, completely trashing system responsiveness for a good portion of it.

So how much is the reasonal upper limit of shared_buffers at this
point? If it's obvious, should we disable or warn to use more than
that number?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On 13/08/10 08:38, Tatsuo Ishii wrote:
>> It's slower than smaller numbers, and if you actually dirty a
>> significant portion of it you can have a checkpoint that takes hours to
>> sync, completely trashing system responsiveness for a good portion of it.
>
> So how much is the reasonal upper limit of shared_buffers at this
> point? If it's obvious, should we disable or warn to use more than
> that number?

Trouble is, there won't be a "reasonable upper limit" ... because it
depends so much on the ratio of memory to I/O throughput, the system's
writeback aggressiveness, etc etc etc.

Personally I've had two Pg machines where one seems to suffer with
shared_buffers > 250MB out of 4GB and the other, which has 8GB of RAM,
wants shared_buffers to be around 4GB! The main difference: disk subsystems.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

On Fri, 13 Aug 2010 14:17:17 +0800
Craig Ringer <craig@postnewspapers.com.au> wrote:

> On 13/08/10 08:38, Tatsuo Ishii wrote:
> >> It's slower than smaller numbers, and if you actually dirty a
> >> significant portion of it you can have a checkpoint that takes
> >> hours to sync, completely trashing system responsiveness for a
> >> good portion of it.
> >
> > So how much is the reasonal upper limit of shared_buffers at this
> > point? If it's obvious, should we disable or warn to use more
> > than that number?
>
> Trouble is, there won't be a "reasonable upper limit" ... because
> it depends so much on the ratio of memory to I/O throughput, the
> system's writeback aggressiveness, etc etc etc.
>
> Personally I've had two Pg machines where one seems to suffer with
> shared_buffers > 250MB out of 4GB and the other, which has 8GB of
> RAM, wants shared_buffers to be around 4GB! The main difference:
> disk subsystems.

What about the ratio of R/W? If it is a mostly read system is the
memory/IO throughput still a limiting factor for increasing
shared_buffers?

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

On Thu, 2010-08-12 at 20:01 -0400, Greg Smith wrote:
> Joshua D. Drake wrote:
> > "Can we just say in the docs say 25% of memory to shared_buffers"
> > Yes, in fact we can. With the caveat of Windows, the reality is this
> > isn't going to hurt nearly as much as a untuned version of PostgreSQL
> > will.
> >
>
> With modern servers often shipping with 72GB of RAM now,

No they aren't. Those are servers you and I will deal with. Not 98% of
the users, using PostgreSQL.

Heck you could even say, "On systems with less than 16GB", for systems
larger contact your PostgreSQL support professional.

The point is, this is solvable for the majority base. There are always
corner cases and 72GB of ram is an extreme corner case.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Thu, Aug 5, 2010 at 10:41 PM, John Gage <jsmgage@numericable.fr> wrote:
> P. P. S. You want to get to grandma's house.  You want to drive a car.  You
> want to learn to drive the car quickly.

You're driving to grandma's house because she needs cataract surgery.
You don't want to pay the surgeon, you just want to do it yourself.
But you don't want to read a book. You just want to take the knife and
dive in.

If you just want to play with toys, then play with toys.  If you want
to get professional results, be a professional or hire one.

Well in that sense, Oracle does cling to some old designs that suck for
most people's use-cases these days; most notably arbitrary-length indexable
text fields. In most Oracle-related applications you are stuck with
either an indexable nvarchar(4096) or an unindexable CLOB field (which
also requires a byte-stream cursor-based access implementation; one
cannot just "SELECT clob_field FROM table" to get a field dump).

The main problem with mysql is that it has historically prioritized speed
and ease of use over data integrity, which a professional DBA
would/should/ought to refuse to compromise over. The DBA and her DBMS is
supposed to be your organization's last line of defense when it comes to
data integrity.

There are far too many cases involving mysql where you end up with
data in, garbage out, and any DBA worth their salt should be cognizant
of that reality.

On 2010-08-18 12:00:15PM -0400, Vick Khera wrote:
> On Thu, Aug 5, 2010 at 10:41 PM, John Gage <jsmgage@numericable.fr> wrote:
> > P. P. S. You want to get to grandma's house.  You want to drive a car.  You
> > want to learn to drive the car quickly.
>
> You're driving to grandma's house because she needs cataract surgery.
> You don't want to pay the surgeon, you just want to do it yourself.
> But you don't want to read a book. You just want to take the knife and
> dive in.
>
> If you just want to play with toys, then play with toys.  If you want
> to get professional results, be a professional or hire one.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
===========================================================
Peter C. Lai                 | Bard College at Simon's Rock
Systems Administrator        | 84 Alford Rd.
Information Technology Svcs. | Gt. Barrington, MA 01230 USA
peter AT simons-rock.edu     | (413) 528-7428
===========================================================

On 12/08/2010 03:43, Tom Lane wrote:
> Marco Colombo<pgsql@esiway.net>  writes:
>> It's a matter of correctness: I see PG as a high
>> performance database system. Allowing to start it in awfully suboptimal
>> conditions it's no different from allowing '0000-00-00' as a date: it
>> may give you the idea you did the right thing, but most of the time you
>> didn't.
>
> Unfortunately, there are quite a few of us for whom "correctness"
> doesn't mean "automatically try to eat all the resources available".
> Your view of what is useful behavior is far too narrow-minded ...
>
>             regards, tom lane

Well, my idea was more along the line of "automatically try to acquire a
reasonable amount of the available resources".

You know, you don't jump directly from 0,1% to 100%. There's a lot in
between. "In medio stat virtus".

.TM.

On Mon, Aug 30, 2010 at 10:08 AM, Marco Colombo <pgsql@esiway.net> wrote:
> Well, my idea was more along the line of "automatically try to acquire a
> reasonable amount of the available resources".
>

I challenge you to defined "reasonable" in a manner that will satisfy
more than 50% of the users in all possible use cases.

On Mon, 2010-08-30 at 15:42 -0400, Vick Khera wrote:
> On Mon, Aug 30, 2010 at 10:08 AM, Marco Colombo <pgsql@esiway.net> wrote:
> > Well, my idea was more along the line of "automatically try to acquire a
> > reasonable amount of the available resources".
> >
>
> I challenge you to defined "reasonable" in a manner that will satisfy
> more than 50% of the users in all possible use cases.

I could do it for 90.

What people seem to forget about this thread is that we don't have to be
correct, just better than what we are now. Generally speaking 90% of the
users out there, by changing a dozen parameters by a basis of percentage
will see a productive result.

JD

>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Mon, 2010-08-30 at 15:42 -0400, Vick Khera wrote:
> On Mon, Aug 30, 2010 at 10:08 AM, Marco Colombo <pgsql@esiway.net> wrote:
> > Well, my idea was more along the line of "automatically try to acquire a
> > reasonable amount of the available resources".
> >
>
> I challenge you to defined "reasonable" in a manner that will satisfy
> more than 50% of the users in all possible use cases.

I could do it for 90.

What people seem to forget about this thread is that we don't have to be
correct, just better than what we are now. Generally speaking 90% of the
users out there, by changing a dozen parameters by a basis of percentage
will see a productive result.

JD

>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt