Re: PostgreSQL is much faster than MySQL, only when...

Użytkownik terry(at)greatgulfhomes(dot)com napisał:

> You said:
> "When I have run my application on PostgreSQL it was disaster"
>
> I think you meant:
> "When I have run my application on MySQL it was disaster"

Well, my english isn't perfect or it was simple mistake... but what I
wanted to say is that my application wasn't working well with PostgreSQL
before the db structure has been changed (new indexes). After that it
was much better...

On Tue, 2003-11-25 at 08:07, Marek Lewczuk wrote:
> Użytkownik terry(at)greatgulfhomes(dot)com napisał:
>
> > You said:
> > "When I have run my application on PostgreSQL it was disaster"
> >
> > I think you meant:
> > "When I have run my application on MySQL it was disaster"
>
> Well, my english isn't perfect or it was simple mistake... but what I
> wanted to say is that my application wasn't working well with PostgreSQL
> before the db structure has been changed (new indexes). After that it
> was much better...
>

Yes. I think the gist of your post was "out of the box postgresql
performed like garbage compared to mysql, but then i spent some time
tweaking and tuning, taking advantage of indexes, and now it performs so
quickly that i am unable to make any changes within mysql to match
postgresql's performance"

I'm curious as to what type of application you run and what first
prompted you to switch to postgresql?

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> Yes. I think the gist of your post was "out of the box postgresql
> performed like garbage compared to mysql, but then i spent some time
> tweaking and tuning, taking advantage of indexes, and now it performs so
> quickly that i am unable to make any changes within mysql to match
> postgresql's performance"

I would put it this way. 'Applications designed for mysql does not perform good
when ported to postgresql when porting means "just make it work with postgresql"'.

Rather obvious isn't it?

Shridhar

Użytkownik Robert Treat napisał:
> On Tue, 2003-11-25 at 08:07, Marek Lewczuk wrote:
>
>
> Yes. I think the gist of your post was "out of the box postgresql
> performed like garbage compared to mysql, but then i spent some time
> tweaking and tuning, taking advantage of indexes, and now it performs so
> quickly that i am unable to make any changes within mysql to match
> postgresql's performance"
Exactly. But as I wrote before - tweaking and tuning wasn't so
important... You cannot use the same db structure in both databases -
even if you think that all indexes, keys are well for MySQL it could be
not sufficient for PostgreSQL. Many people use the same structure and
they see that PostgreSQL is slower... so I just wanted to tell them,
that it is necessary to re-check structure which will be used with PG.

>
> I'm curious as to what type of application you run and what first
> prompted you to switch to postgresql?
It's analysis system for automotive market. There are many complicated
querys, with many joins... PostgreSQL is much more flexible, more
powerful and more stable than MySQL. There are also many other features
which are just not available in MySQL...

>
> Robert Treat

How does the performance compare using models mimicked from Oracle? Is
there any information anywhere on that?

Marek Lewczuk wrote:
> Użytkownik Robert Treat napisał:
>
>> On Tue, 2003-11-25 at 08:07, Marek Lewczuk wrote:
>>
>>
>> Yes. I think the gist of your post was "out of the box postgresql
>> performed like garbage compared to mysql, but then i spent some time
>> tweaking and tuning, taking advantage of indexes, and now it performs so
>> quickly that i am unable to make any changes within mysql to match
>> postgresql's performance"
>
> Exactly. But as I wrote before - tweaking and tuning wasn't so
> important... You cannot use the same db structure in both databases -
> even if you think that all indexes, keys are well for MySQL it could be
> not sufficient for PostgreSQL. Many people use the same structure and
> they see that PostgreSQL is slower... so I just wanted to tell them,
> that it is necessary to re-check structure which will be used with PG.
>
>
>>
>> I'm curious as to what type of application you run and what first
>> prompted you to switch to postgresql?
>
> It's analysis system for automotive market. There are many complicated
> querys, with many joins... PostgreSQL is much more flexible, more
> powerful and more stable than MySQL. There are also many other features
> which are just not available in MySQL...
>
>>
>> Robert Treat
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Barbara E. Lindsey,
COG RDC
Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----

Barbara Lindsey wrote:
> How does the performance compare using models mimicked from Oracle? Is
> there any information anywhere on that?

From reports we have received, we are usually +/- 10% compared to Oracle
on most queries.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hi all;

Since we are on the topic of what prompted us to use PostgreSQL, I figured I
would share my experiences as well, and some additional thoughts that I had.

I chose PostgreSQL about 2 years ago when I realized that the application I
was building needed something more robust than MySQL. I found the learning
curve to be a bit steep at first, but eventually got the hang of it and have
developed a real appreciation for PostgreSQL. Unlike many members here I
won't say I would never go back to MySQL, because there are some
circumstances where I would (where I need an RDBMS-like interface but not an
RDBMS, f. ex. MySQL's heap tables could be very useful for real-time control
systems). However, I am actually seeing less and less value in MySQL as I
become more familiar with the extensibility features in PostgreSQL.

Anyway one additional thought I had was that it should be possible to write
parsers for text files in PL/PERLu and then plug those in as views. In this
way, files such as /etc/passwd or even /var/log/messages could be used as if
they were tables in the database. Information from these tables could then
be selected into other tables, presented directly to an application, etc.
While it mught be possible to do the same for MySQL, it would be much more
work. Imagine being able to run a query such as:

select * from logs.iptables
where proto = 'TCP' and td_entered > '2003-01-01' and port =
(select port from sysinfo.services where service='ssh');

in order to get every hit against the ssh service on the firewall in the
current year. Of course, I imagine, it would take some time to run ;-) But
for generating reports of system activirt, I would not assume this to be a
problem.

Next question-- any ideas how one could generate something like MySQL's heap
tables (maybe in shared memory?) within PostgreSQL?

Best WIshes,
Chris Travers

Chris Travers wrote:
> Anyway one additional thought I had was that it should be possible to write
> parsers for text files in PL/PERLu and then plug those in as views. In this
> way, files such as /etc/passwd or even /var/log/messages could be used as if
> they were tables in the database. Information from these tables could then
> be selected into other tables, presented directly to an application, etc.
> While it mught be possible to do the same for MySQL, it would be much more

Copy can import delimited data with user specified delimiter. May be you can
upload the files in batch to database periodically.

Of course if you need multiple delimiter parsing, then you need to do something
about it. Sed/AWK scripts probably..

> Next question-- any ideas how one could generate something like MySQL's heap
> tables (maybe in shared memory?) within PostgreSQL?

Pardon the dumb question(Used mysql seriously onced 2 years back.). What is heap
table? What do you need that postgresql does not offer? Just trying to understand..

Shridhar

> Since we are on the topic of what prompted us to use PostgreSQL, I
> figured I would share my experiences as well, and some additional
> thoughts that I had.
>
> I chose PostgreSQL about 2 years ago when I realized that the
> application I was building needed something more robust than MySQL. I
> found the learning curve to be a bit steep at first, but eventually got
> the hang of it and have developed a real appreciation for PostgreSQL.
> Unlike many members here I won't say I would never go back to MySQL,
> because there are some circumstances where I would (where I need an
> RDBMS-like interface but not an RDBMS, f. ex. MySQL's heap tables could
> be very useful for real-time control systems). However, I am actually
> seeing less and less value in MySQL as I become more familiar with the
> extensibility features in PostgreSQL.

I'm moving from Oracle 8i to PostgreSQL. I don't really do much fancy
stuff in SQL now, but I do use views a little bit and so MySQL just isn't
an option for me.

I just finished reading another book on PostgreSQL so I can imagine
why you like it so much. The only two things I'd really like to see in
PostgreSQL are:

1. Support for "CONNECT BY" (useful for family trees), which I
understand is being worked on now. The only twist is that I'd also like to
see a specialized index option for supporting this as this is something
that Oracle can't do -- my vision of such an index file is that it would be
quite different from others in that it would be designed to increase the
speed of traversing through the tree. I'm not convinced that Oracle's
solution to this is optimal in this regard.

2. Moving to table spaces (PostgreSQL version 8 maybe?) rather
than just storing a whole bunch of files in a single directory. Oracle's
implementation is nice because tables, indexes, etc., can span multiple
table spaces, and there are great performance optimization and scalability
advantages that otherwise just aren't possible without them. I read in
another thread (approx. 2 months old) earlier this evening that some folks
would like to see OIDs deprecated, and if this is the case then the sub-
directories under "base/" will obviously need a different naming mechanism,
so instead of re-thinking this perhaps it would be a good opportunity for
the PostgreSQL team to look at the possibility of implementing things
within table spaces.

> Anyway one additional thought I had was that it should be possible to
> write parsers for text files in PL/PERLu and then plug those in as
> views. In this way, files such as /etc/passwd or even /var/log/messages
> could be used as if they were tables in the database. Information from
> these tables could then be selected into other tables, presented
> directly to an application, etc. While it mught be possible to do the
> same for MySQL, it would be much more work. Imagine being able to run a
> query such as:
>
> select * from logs.iptables
> where proto = 'TCP' and td_entered > '2003-01-01' and port (select
> port from sysinfo.services where service='ssh');
>
> in order to get every hit against the ssh service on the firewall in the
> current year. Of course, I imagine, it would take some time to run ;-)
> But for generating reports of system activirt, I would not assume this
> to be a problem.

A very interesting idea, but my feeling is that pure PERL is best
suited for dealing with flat text files.

> Next question-- any ideas how one could generate something like MySQL's
> heap tables (maybe in shared memory?) within PostgreSQL?

I have no idea. Anyone else?

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

Hi;

I am trying to determine if there is anything I can do with MySQL that I
can't do with PostgreSQL. HEAP tables are the last one I can't figure out.

HEAP tables are MySQL tables which reside in memory and are visible across
sessions. For obvious reasons, this violates the D in ACID.

In many cases this can be very usefull-- for example if I am building a
system where I want the simplicity of a SQL-like interface with the low
latency of memory-only devices. WOuld I have to run the entire db in a
ramdisk, or can anyone think of a way to do something like this?

It has occurred to me that when table spaces are supported, then this would
be much easier (have a HEAP table space sitting on a ramdisk).

Best WIshes,
Chris Travers

Chris Travers wrote:
> HEAP tables are MySQL tables which reside in memory and are visible across
> sessions. For obvious reasons, this violates the D in ACID.

Postgresql depend upon two level of buffers. One is it's own buffers and others
are OS buffer cache. So when you say update the table, the relevant portion is
fetched in postgresql buffers and updated. OS does the physical fetch for
postgresql and keeps a copy in it's own buffer cache.

Even if postgresql does not keep them in it's own buffers after a certain time,
most probably OS buffer cache will. So effectively it is cached in RAM. However
other IO operations might need memory and it might get reused under load.

Postgresql does not have a special name for it. It goes for every operation it
makes. So if your table is small enough, it is already cached in the RAM by all
chances.

There should be no performance difference between small tables in postgresql and
heap tables in mysql.

> In many cases this can be very usefull-- for example if I am building a
> system where I want the simplicity of a SQL-like interface with the low
> latency of memory-only devices. WOuld I have to run the entire db in a
> ramdisk, or can anyone think of a way to do something like this?

No. Just build a normal table in postgresql. That's it. And the bright side of
it, it satisfy D.

Hope this helps. I would be interested in numbers that say postgresql is slower
than mysql heap tables. (You can force postgresql to load entire table by doin
select * from table. Of course the table is expected to be small enough.. Then
compare the results. It will always be slow first time..)

Shridhar

Hi All,

I've just been reading an article in PHP Architect magazine
(http://www.phparch.com) which is the cover story for October called
"Migrating from MySQL to PostgreSQL". I must say that this is a highly
compelling article, especially for me, and is aimed at programmers that
aren't necessarilly SQL experts or DBAs. For instance, like many PHP
Web developers who use MySQL instead of flat files to store stuff!
Instead of using a DB as a powerful tool. This article presents reasons
as to why a more standards compliant DB is good for programmers, and why
in some cases MySQL can be less of a friend to programmers than perhaps
PostgreSQL.

I honestly believe that if the advocates of PostgreSQL wrote an article
or case study along the lines of this article, it would go a long way to
attracting many more programmers. In my experience all of the articles
and tutorials are written from the perspective of why PG is a better DB
as a DB. Rather than emphasise aspects like "PG is great because you
can move complicated code like this <insert complicated PHP/Perl code
here> ..... normally dealt programatically to your DB which can be both
faster and applied to any other programmers (VB, Java) that you are
sharing the important enterprise data with. I've not seen anything in
articles aimed at PHP/MySQL users saying, "Hey, look at how these
triggers can make your life soooo much easier" or "Hey, look at how
cascading can save you oh so much coding" or "Hey look at all this
programmatical logic that can be put into queries just by writing your
own functions"

I have recently compared the PostgreSQL users to the Debian users (meant
as a complement) by the fact that they are in general highly
knowledgable of thier own subject and peripheral subjects too. They are
passionate and well versed, and happy to nudge people in the direction
of enlightenment without spoonfeeding them. But in the same way, the
advocacy (IMHO) falls into the same boat as Debian. There is a certain
self-assuredness that PostgreSQL is a far superior product and if
someone can't see how obvious that is then maybe PG isn't for them (a
little harsh I know but I'm trying to illustrate a point).

My point is that there are thousands, tens of thousands of programmers
out there, that need to know why and how PG is so great. My eyes have
now been fully opened by this article, and got rid of my nagging feeling
that there was something great about PG that I "Just wasn't grasping,
and couldn't put my finger on". Maybe the advocacy team should be
aiming for all those programmers that desperately need PG, but don't
know it yet, and probably don't have time to garner enough DB experience
to understand why they need it!

Sadly the PHP Architect article is not free, I bought the electronic
magazine for about $2, but believe it's worth every penny and more.

Just my 2 cents.

Apologies if the PG articles ARE out there and please notice that my
comments do not say that they don't exist, but that I have never seen them.

Cheers

Tony.

On 27/11/2003 08:08 Chris Travers wrote:
> Hi;
>
> I am trying to determine if there is anything I can do with MySQL that I
> can't do with PostgreSQL. HEAP tables are the last one I can't figure
> out.
>
> HEAP tables are MySQL tables which reside in memory and are visible
> across
> sessions. For obvious reasons, this violates the D in ACID.
>
> In many cases this can be very usefull-- for example if I am building a
> system where I want the simplicity of a SQL-like interface with the low
> latency of memory-only devices. WOuld I have to run the entire db in a
> ramdisk, or can anyone think of a way to do something like this?
>
> It has occurred to me that when table spaces are supported, then this
> would
> be much easier (have a HEAP table space sitting on a ramdisk).

If your table is that small, you could just cache it in your application.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

HEAP tables in MySQL can be basically used as a network-aware SQL interface
for shared memory segments. I was more looking at the possibility of using
these for coordinating various processes on different systems on a network.

If there is not much performance difference between reasonably small tables
and HEAP tables in MySQL, then I could simply create a simple database and
use that. I was just wondering if there was any other tricks that could be
used, aside from functions written in C ;-)
----- Original Message -----
From: "Paul Thomas" <paul(at)tmsl(dot)demon(dot)co(dot)uk>
To: "pgsql-general @ postgresql . org" <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, November 27, 2003 5:02 PM
Subject: Re: PostgreSQL, MySQL, etc., was Re: [GENERAL] PostgreSQL is much
faster than MySQL, only when...

>
> On 27/11/2003 08:08 Chris Travers wrote:
> > Hi;
> >
> > I am trying to determine if there is anything I can do with MySQL that I
> > can't do with PostgreSQL. HEAP tables are the last one I can't figure
> > out.
> >
> > HEAP tables are MySQL tables which reside in memory and are visible
> > across
> > sessions. For obvious reasons, this violates the D in ACID.
> >
> > In many cases this can be very usefull-- for example if I am building a
> > system where I want the simplicity of a SQL-like interface with the low
> > latency of memory-only devices. WOuld I have to run the entire db in a
> > ramdisk, or can anyone think of a way to do something like this?
> >
> > It has occurred to me that when table spaces are supported, then this
> > would
> > be much easier (have a HEAP table space sitting on a ramdisk).
>
>
> If your table is that small, you could just cache it in your application.
>
> --
> Paul Thomas
>
+------------------------------+--------------------------------------------
-+
> | Thomas Micro Systems Limited | Software Solutions for the Smaller
> Business |
> | Computer Consultants |
> http://www.thomas-micro-systems-ltd.co.uk |
>
+------------------------------+--------------------------------------------
-+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

This should be pretty easy. Look into PostgreSQL's rule system. You
should just need a lot of "do instead" functions for the table.

Jon

On Thu, 27 Nov 2003, Chris Travers wrote:

> Hi all;
>
> Since we are on the topic of what prompted us to use PostgreSQL, I figured I
> would share my experiences as well, and some additional thoughts that I had.
>
> I chose PostgreSQL about 2 years ago when I realized that the application I
> was building needed something more robust than MySQL. I found the learning
> curve to be a bit steep at first, but eventually got the hang of it and have
> developed a real appreciation for PostgreSQL. Unlike many members here I
> won't say I would never go back to MySQL, because there are some
> circumstances where I would (where I need an RDBMS-like interface but not an
> RDBMS, f. ex. MySQL's heap tables could be very useful for real-time control
> systems). However, I am actually seeing less and less value in MySQL as I
> become more familiar with the extensibility features in PostgreSQL.
>
> Anyway one additional thought I had was that it should be possible to write
> parsers for text files in PL/PERLu and then plug those in as views. In this
> way, files such as /etc/passwd or even /var/log/messages could be used as if
> they were tables in the database. Information from these tables could then
> be selected into other tables, presented directly to an application, etc.
> While it mught be possible to do the same for MySQL, it would be much more
> work. Imagine being able to run a query such as:
>
> select * from logs.iptables
> where proto = 'TCP' and td_entered > '2003-01-01' and port =
> (select port from sysinfo.services where service='ssh');
>
> in order to get every hit against the ssh service on the firewall in the
> current year. Of course, I imagine, it would take some time to run ;-) But
> for generating reports of system activirt, I would not assume this to be a
> problem.
>
> Next question-- any ideas how one could generate something like MySQL's heap
> tables (maybe in shared memory?) within PostgreSQL?
>
> Best WIshes,
> Chris Travers
>
>

On 27/11/2003 09:19 Tony wrote:
> Hi All,
>
> I've just been reading an article in PHP Architect magazine
> (http://www.phparch.com) which is the cover story for October called
> "Migrating from MySQL to PostgreSQL". I must say that this is a highly
> compelling article, especially for me, and is aimed at programmers that
> aren't necessarilly SQL experts or DBAs. For instance, like many PHP
> Web developers who use MySQL instead of flat files to store stuff!
> Instead of using a DB as a powerful tool. This article presents reasons
> as to why a more standards compliant DB is good for programmers, and why
> in some cases MySQL can be less of a friend to programmers than perhaps
> PostgreSQL.
>
> I honestly believe that if the advocates of PostgreSQL wrote an article
> or case study along the lines of this article, it would go a long way to
> attracting many more programmers. In my experience all of the articles
> and tutorials are written from the perspective of why PG is a better DB
> as a DB. Rather than emphasise aspects like "PG is great because you
> can move complicated code like this <insert complicated PHP/Perl code
> here> ..... normally dealt programatically to your DB which can be both
> faster and applied to any other programmers (VB, Java) that you are
> sharing the important enterprise data with. I've not seen anything in
> articles aimed at PHP/MySQL users saying, "Hey, look at how these
> triggers can make your life soooo much easier" or "Hey, look at how
> cascading can save you oh so much coding" or "Hey look at all this
> programmatical logic that can be put into queries just by writing your
> own functions"
>
> I have recently compared the PostgreSQL users to the Debian users (meant
> as a complement) by the fact that they are in general highly
> knowledgable of thier own subject and peripheral subjects too. They are
> passionate and well versed, and happy to nudge people in the direction
> of enlightenment without spoonfeeding them. But in the same way, the
> advocacy (IMHO) falls into the same boat as Debian. There is a certain
> self-assuredness that PostgreSQL is a far superior product and if
> someone can't see how obvious that is then maybe PG isn't for them (a
> little harsh I know but I'm trying to illustrate a point).
>
> My point is that there are thousands, tens of thousands of programmers
> out there, that need to know why and how PG is so great. My eyes have
> now been fully opened by this article, and got rid of my nagging feeling
> that there was something great about PG that I "Just wasn't grasping,
> and couldn't put my finger on". Maybe the advocacy team should be
> aiming for all those programmers that desperately need PG, but don't
> know it yet, and probably don't have time to garner enough DB experience
> to understand why they need it!

Maybe there's not such a need for the advanced features of PostgreSQL
amongst PHP programmers as you seem to believe. Most of the PHP stuff I've
seen is read-only content display stuff and that doesn't really require a
top-notch RDBMS; a more limited database should also be up to the job. For
complex transactional web applications, J2EE/Model II is a far superior
technology to scripts/Model I and that means a different target audience
for the apps where PostgreSQL can offer those essential extra features.
Whilst most J2EE developers will be using Oracle/DB2/MSSQL as their
back-end, the awareness of PostgreSQL seems quite high and, in the few
usenet groups I monitor, I don't recall anyone being flamed for
recommending PostgreSQL over MySQL. Maybe seasoned, professional
developers don't like being told that they're crap programmers just
because they ask for something as fundamental as referential integrity!

Coming to your point about advocacy, I certainly don't recognize what you
describe. Of course the members of the advocacy group believe in the
quality of PostgreSQL (a view shared by most of the subscribers to list).
What I think you need to bear in mind is that PostgreSQL is a genuinely
open-source product _not_ a commercial product in GPL clothing like MySQL.
The developers and advocates are not making $xx per box shifted or trying
to seduce users down a supposedly free path into their licensed software
lair. That has a big effect on advocacy. Instead of smarmy marketing types
who rely on spread FUD and misinformation about every product they
consider a competitor, we have a group of people acting with honesty and
integrity. Welcome to the real world of open source :-)

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

Cross-posted to "comp.databases.postgresql.advocacy" because I believe
this is something that should be considered for future additions to the
PostgreSQL advocacy web site.

"paul(at)tmsl(dot)demon(dot)co(dot)uk (Paul Thomas)" wrote in
comp.databases.postgresql.general:

> On 27/11/2003 09:19 Tony wrote:
>
>> Hi All,
>>
>> I've just been reading an article in PHP Architect magazine
>> (http://www.phparch.com) which is the cover story for October called
>> "Migrating from MySQL to PostgreSQL". I must say that this is a highly
>> compelling article, especially for me, and is aimed at programmers that
>> aren't necessarilly SQL experts or DBAs. For instance, like many PHP
>> Web developers who use MySQL instead of flat files to store stuff!
>> Instead of using a DB as a powerful tool. This article presents reasons
>> as to why a more standards compliant DB is good for programmers, and why
>> in some cases MySQL can be less of a friend to programmers than perhaps
>> PostgreSQL.
>>
>> I honestly believe that if the advocates of PostgreSQL wrote an article
>> or case study along the lines of this article, it would go a long way to
>> attracting many more programmers. In my experience all of the articles
>> and tutorials are written from the perspective of why PG is a better DB
>> as a DB. Rather than emphasise aspects like "PG is great because you
>> can move complicated code like this <insert complicated PHP/Perl code
>> here> ..... normally dealt programatically to your DB which can be both
>> faster and applied to any other programmers (VB, Java) that you are
>> sharing the important enterprise data with. I've not seen anything in
>> articles aimed at PHP/MySQL users saying, "Hey, look at how these
>> triggers can make your life soooo much easier" or "Hey, look at how
>> cascading can save you oh so much coding" or "Hey look at all this
>> programmatical logic that can be put into queries just by writing your
>> own functions"
>>
>> I have recently compared the PostgreSQL users to the Debian users (meant
>> as a complement) by the fact that they are in general highly
>> knowledgable of thier own subject and peripheral subjects too. They are
>> passionate and well versed, and happy to nudge people in the direction
>> of enlightenment without spoonfeeding them. But in the same way, the
>> advocacy (IMHO) falls into the same boat as Debian. There is a certain
>> self-assuredness that PostgreSQL is a far superior product and if
>> someone can't see how obvious that is then maybe PG isn't for them (a
>> little harsh I know but I'm trying to illustrate a point).
>>
>> My point is that there are thousands, tens of thousands of programmers
>> out there, that need to know why and how PG is so great. My eyes have
>> now been fully opened by this article, and got rid of my nagging feeling
>> that there was something great about PG that I "Just wasn't grasping,
>> and couldn't put my finger on". Maybe the advocacy team should be
>> aiming for all those programmers that desperately need PG, but don't
>> know it yet, and probably don't have time to garner enough DB experience
>> to understand why they need it!
>
> Maybe there's not such a need for the advanced features of PostgreSQL
> amongst PHP programmers as you seem to believe. Most of the PHP stuff
I've
> seen is read-only content display stuff and that doesn't really require a
> top-notch RDBMS; a more limited database should also be up to the job.
For
> complex transactional web applications, J2EE/Model II is a far superior
> technology to scripts/Model I and that means a different target audience
> for the apps where PostgreSQL can offer those essential extra features.
> Whilst most J2EE developers will be using Oracle/DB2/MSSQL as their
> back-end, the awareness of PostgreSQL seems quite high and, in the few
> usenet groups I monitor, I don't recall anyone being flamed for
> recommending PostgreSQL over MySQL. Maybe seasoned, professional
> developers don't like being told that they're crap programmers just
> because they ask for something as fundamental as referential integrity!
>
> Coming to your point about advocacy, I certainly don't recognize what you
> describe. Of course the members of the advocacy group believe in the
> quality of PostgreSQL (a view shared by most of the subscribers to list).
> What I think you need to bear in mind is that PostgreSQL is a genuinely
> open-source product _not_ a commercial product in GPL clothing like
MySQL.
> The developers and advocates are not making $xx per box shifted or trying
> to seduce users down a supposedly free path into their licensed software
> lair. That has a big effect on advocacy. Instead of smarmy marketing
types
> who rely on spread FUD and misinformation about every product they
> consider a competitor, we have a group of people acting with honesty and
> integrity. Welcome to the real world of open source :-)
>

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

Randolf Richardson Wrote:
> 2. Moving to table spaces (PostgreSQL version 8 maybe?) rather
> than just storing a whole bunch of files in a single directory. Oracle's
> implementation is nice because tables, indexes, etc., can span multiple
> table spaces, and there are great performance optimization and scalability
> advantages that otherwise just aren't possible without them. I read in
> another thread (approx. 2 months old) earlier this evening that some folks
> would like to see OIDs deprecated, and if this is the case then the sub-
> directories under "base/" will obviously need a different naming
mechanism,
> so instead of re-thinking this perhaps it would be a good opportunity for
> the PostgreSQL team to look at the possibility of implementing things
> within table spaces.

I believe this is being worked on also.

>
> > Anyway one additional thought I had was that it should be possible to
> > write parsers for text files in PL/PERLu and then plug those in as
> > views. In this way, files such as /etc/passwd or even /var/log/messages
> > could be used as if they were tables in the database.
<SNIP>
> A very interesting idea, but my feeling is that pure PERL is best
> suited for dealing with flat text files.

True,. and I use it for that (see my project at
http://sourceforge.net/projects/fwreport). However the ability to take a
similar parser and then use it to present the same information to a RDBMS
would then provide some additional flexibility, as you could use the RDBMS
for managing the query interface to the files. Not very useful if you only
want to see the same files the same way every time, but very useful if you
need to extract different information from them.

>
> > Next question-- any ideas how one could generate something like MySQL's
> > heap tables (maybe in shared memory?) within PostgreSQL?
>
> I have no idea. Anyone else?
>
> --
> Randolf Richardson - rr(at)8x(dot)ca
> Vancouver, British Columbia, Canada
>
> Please do not eMail me directly when responding
> to my postings in the newsgroups.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

> Randolf Richardson Wrote:
>> 2. Moving to table spaces (PostgreSQL version 8 maybe?) rather
>> than just storing a whole bunch of files in a single directory.
Oracle's
>> implementation is nice because tables, indexes, etc., can span multiple
>> table spaces, and there are great performance optimization and
scalability
>> advantages that otherwise just aren't possible without them. I read in
>> another thread (approx. 2 months old) earlier this evening that some
folks
>> would like to see OIDs deprecated, and if this is the case then the sub-
>> directories under "base/" will obviously need a different naming
> mechanism,
>> so instead of re-thinking this perhaps it would be a good opportunity
for
>> the PostgreSQL team to look at the possibility of implementing things
>> within table spaces.
>
> I believe this is being worked on also.

Yes!!!!!!! That's excellent! I look forward to helping with testing
it on the NetWare port (and possibly FreeBSD if I ever get it running).

[sNip]
>> A very interesting idea, but my feeling is that pure PERL is best
>> suited for dealing with flat text files.
>
> True,. and I use it for that (see my project at
> http://sourceforge.net/projects/fwreport). However the ability to take a
> similar parser and then use it to present the same information to a RDBMS
> would then provide some additional flexibility, as you could use the
RDBMS
> for managing the query interface to the files. Not very useful if you
only
> want to see the same files the same way every time, but very useful if
you
> need to extract different information from them.
[sNip]

Obviously there's no disagreement here from anyone about the
usefulness of this. I guess I should have clarified a bit more in my post
because I'm concerned about PostgreSQL getting too fragmented from a
project management perspective -- it's a database engine, with many
different interfaces available, and to make it into a "Swiss Army Knife"
(or a Chinese knock-off I saw once which had even more functionality) could
result in possibly slower productivity in the long run (not to mention
greater system resource requirements, &c).

Albeit that's only a concern -- those developers who do all the hard
work on PostgreSQL would obviously know best what the answer to this is.

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

On Thursday 27 November 2003 04:18, Shridhar Daithankar wrote:
> Chris Travers wrote:
<snip explination of postgresql cacheing tables in memory>
>
> Hope this helps. I would be interested in numbers that say postgresql is
> slower than mysql heap tables. (You can force postgresql to load entire
> table by doin select * from table. Of course the table is expected to be
> small enough.. Then compare the results. It will always be slow first
> time..)
>

the difference is that with mysql, nothing pushes the table out of memory; it
always stays in memory. in postgresql, a big query on another tables, or
perhaps a vacuum, or other highly active applications on the same server can
cause the small tables to be pushed out of memory. both approches have
positives and negatives, and in many cases you would probably notice no
differance

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Sat, Nov 29, 2003 at 12:24:22PM -0500, Robert Treat wrote:

> [MySQL's heap tables]

> the difference is that with mysql, nothing pushes the table out of memory; it
> always stays in memory. in postgresql, a big query on another tables, or
> perhaps a vacuum, or other highly active applications on the same server can
> cause the small tables to be pushed out of memory. both approches have
> positives and negatives, and in many cases you would probably notice no
> differance

If this is a small heavily used table, 7.5 with the new ARC buffer
management policy should do much better. Even better, the table does
not actually need to be small: the heavily used portion will stay in
memory where it can be very fast, and the rest will be just wait its
turn on disk.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"

[sNip]
>> the difference is that with mysql, nothing pushes the table out of
>> memory; it always stays in memory. in postgresql, a big query on
>> another tables, or perhaps a vacuum, or other highly active
>> applications on the same server can cause the small tables to be pushed
>> out of memory. both approches have positives and negatives, and in
>> many cases you would probably notice no differance
>
> If this is a small heavily used table, 7.5 with the new ARC buffer
> management policy should do much better. Even better, the table does
> not actually need to be small: the heavily used portion will stay in
> memory where it can be very fast, and the rest will be just wait its
> turn on disk.

Is this a configurable option by any chance? If not, then perhaps it
should be on a per-table, per-index (etc.) basis.

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

A long time ago, in a galaxy far, far away, Randolf Richardson <rr(at)8x(dot)ca> wrote:
> [sNip]
>>> the difference is that with mysql, nothing pushes the table out of
>>> memory; it always stays in memory. in postgresql, a big query on
>>> another tables, or perhaps a vacuum, or other highly active
>>> applications on the same server can cause the small tables to be
>>> pushed out of memory. both approches have positives and
>>> negatives, and in many cases you would probably notice no
>>> differance
>>
>> If this is a small heavily used table, 7.5 with the new ARC buffer
>> management policy should do much better. Even better, the table
>> does not actually need to be small: the heavily used portion will
>> stay in memory where it can be very fast, and the rest will be just
>> wait its turn on disk.
>
> Is this a configurable option by any chance? If not, then
> perhaps it should be on a per-table, per-index (etc.) basis.

It is a MUCH BETTER thing to have policies that don't require
configuration effort.

One of the characteristic problems with Oracle is that you have
immense numbers of "knobs" to tune. You can get it to work "just
right" if you throw a large enough horde of DBAs at it.

In the case of the ARC policy, what Jan is trying to do is to come up
with a strategy that is an improvement irrespective of the
characteristics of the table. If that works out as hoped for, there
will be no need to "configure" anything in order to take advantage of
it.

You'd find your applications running faster simply by installing a 7.5
server; no need to configure anything. It's like getting Pentium chip
with improved execution strategies; you don't have to recompile
anything (the way IA-64 mandates it); you just install the app on the
new box and watch it speed up.
--
"cbbrowne","@","acm.org"
http://www.ntlug.org/~cbbrowne/spreadsheets.html
Rules of the Evil Overlord #222. "I reserve the right to execute any
henchmen who appear to be a little too intelligent, powerful, or
devious. However if I do so, I will not at some subsequent point shout
"Why am I surrounded by these incompetent fools?!"
<http://www.eviloverlord.com/>

Thanks, Tony

It's good to hear that my article hasn't fallen on deaf ears (er...
rather... blind eyes). I wrote this article as a result of reading many
messages in the pgsql mail archives wishing for such a thing. I also
intended to write another article (actually, a series of articles) to be
distributed freely for online use, and not focusing only on PHP. I am
still working on this, but a recent job change and an urgent project
have slowed me down a little. I plan to have at least a couple of these
ready before the new year, when I will post links to them here
(actually, in the pgsql-advocacy), and will welcome any reviews,
criticism, etc... I will also be more than happy to participate in any
way (reviews, proofreading, etc...) for any others writing articles.
Count me in, as a (more than) happy PostgreSQL user.

And yes, I think you hit the nail on the head here: often those who are
very familiar with PostgreSQL don't realize just how little
understanding many developers have of its potential. Since I was, until
2-3 years ago, an 'outsider' to many of the serious concepts of the
relational model, I can empathize with them somewhat, and it has helped
me present the case.

I was recently made moderator of the Database section at Dev Shed
Forums, where I have hopefully had a hand in explaining PostgreSQL to
more than a few developers. Based on what I am seeing, I sincerely
believe PostgreSQL is going to get LOT more recognition over the next
couple of years. This is especially the case if a few of us start
presenting *positive explanatory material*, in addition to the
comparisons to other DBMSs. The sad fact is, most developers need to be
educated in the basics, just to be able to understand what the benefits are.

Regards,

Rick Morris

Tony wrote:

> Hi All,
>
> I've just been reading an article in PHP Architect magazine
> (http://www.phparch.com) which is the cover story for October called
> "Migrating from MySQL to PostgreSQL". I must say that this is a
> highly compelling article, especially for me, and is aimed at
> programmers that aren't necessarilly SQL experts or DBAs. For
> instance, like many PHP Web developers who use MySQL instead of flat
> files to store stuff! Instead of using a DB as a powerful tool. This
> article presents reasons as to why a more standards compliant DB is
> good for programmers, and why in some cases MySQL can be less of a
> friend to programmers than perhaps PostgreSQL.
>
> I honestly believe that if the advocates of PostgreSQL wrote an
> article or case study along the lines of this article, it would go a
> long way to attracting many more programmers. In my experience all of
> the articles and tutorials are written from the perspective of why PG
> is a better DB as a DB. Rather than emphasise aspects like "PG is
> great because you can move complicated code like this <insert
> complicated PHP/Perl code here> ..... normally dealt programatically
> to your DB which can be both faster and applied to any other
> programmers (VB, Java) that you are sharing the important enterprise
> data with. I've not seen anything in articles aimed at PHP/MySQL
> users saying, "Hey, look at how these triggers can make your life
> soooo much easier" or "Hey, look at how cascading can save you oh so
> much coding" or "Hey look at all this programmatical logic that can be
> put into queries just by writing your own functions"
>
> I have recently compared the PostgreSQL users to the Debian users
> (meant as a complement) by the fact that they are in general highly
> knowledgable of thier own subject and peripheral subjects too. They
> are passionate and well versed, and happy to nudge people in the
> direction of enlightenment without spoonfeeding them. But in the same
> way, the advocacy (IMHO) falls into the same boat as Debian. There is
> a certain self-assuredness that PostgreSQL is a far superior product
> and if someone can't see how obvious that is then maybe PG isn't for
> them (a little harsh I know but I'm trying to illustrate a point).
>
> My point is that there are thousands, tens of thousands of programmers
> out there, that need to know why and how PG is so great. My eyes have
> now been fully opened by this article, and got rid of my nagging
> feeling that there was something great about PG that I "Just wasn't
> grasping, and couldn't put my finger on". Maybe the advocacy team
> should be aiming for all those programmers that desperately need PG,
> but don't know it yet, and probably don't have time to garner enough
> DB experience to understand why they need it!
>
> Sadly the PHP Architect article is not free, I bought the electronic
> magazine for about $2, but believe it's worth every penny and more.
>
> Just my 2 cents.
>
> Apologies if the PG articles ARE out there and please notice that my
> comments do not say that they don't exist, but that I have never seen
> them.
>
> Cheers
>
> Tony.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>
>

Christopher Browne wrote:

> A long time ago, in a galaxy far, far away, Randolf Richardson <rr(at)8x(dot)ca> wrote:
>> [sNip]
>>>> the difference is that with mysql, nothing pushes the table out of
>>>> memory; it always stays in memory. in postgresql, a big query on
>>>> another tables, or perhaps a vacuum, or other highly active
>>>> applications on the same server can cause the small tables to be
>>>> pushed out of memory. both approches have positives and
>>>> negatives, and in many cases you would probably notice no
>>>> differance
>>>
>>> If this is a small heavily used table, 7.5 with the new ARC buffer
>>> management policy should do much better. Even better, the table
>>> does not actually need to be small: the heavily used portion will
>>> stay in memory where it can be very fast, and the rest will be just
>>> wait its turn on disk.
>>
>> Is this a configurable option by any chance? If not, then
>> perhaps it should be on a per-table, per-index (etc.) basis.
>
> It is a MUCH BETTER thing to have policies that don't require
> configuration effort.
>
> One of the characteristic problems with Oracle is that you have
> immense numbers of "knobs" to tune. You can get it to work "just
> right" if you throw a large enough horde of DBAs at it.
>
> In the case of the ARC policy, what Jan is trying to do is to come up
> with a strategy that is an improvement irrespective of the
> characteristics of the table. If that works out as hoped for, there
> will be no need to "configure" anything in order to take advantage of
> it.

The stuff is in CVS HEAD. Randolf, look at the README file in
src/backend/storage/buffer for some explanations.

Jan

>
> You'd find your applications running faster simply by installing a 7.5
> server; no need to configure anything. It's like getting Pentium chip
> with improved execution strategies; you don't have to recompile
> anything (the way IA-64 mandates it); you just install the app on the
> new box and watch it speed up.

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

[sNip]
> I was recently made moderator of the Database section at Dev Shed
> Forums, where I have hopefully had a hand in explaining PostgreSQL to
> more than a few developers. Based on what I am seeing, I sincerely
> believe PostgreSQL is going to get LOT more recognition over the next
> couple of years. This is especially the case if a few of us start
> presenting *positive explanatory material*, in addition to the
> comparisons to other DBMSs. The sad fact is, most developers need to be
> educated in the basics, just to be able to understand what the benefits
> are.
[sNip]

What will also be helpful is for people to see working systems in
action that use PostgreSQL in the backend. I've got a few projects that are
in the works, but nothing that's ready for public consumption yet (but once I
do, I will certainly let the Advocacy group know about it and will be
included a link from a section of the web site to both the PostgreSQL home
page and the Advocacy page).

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.