Thread: PG process architecture

Hi listers,
I am experienced Oracle DBA und now I was given a task to evaluate
Postgresql.
May first goal is to compare the architecture of Oracle and Postgres.
After reading the fine manuals and several mailing lists, I have found
that the following parameters are analogous in 
PG vs Oracle
------------
shared_buffers <-> db_cache_size
wal_buffers <-> log_buffer

shared_buffers and wal_buffers  are residing in shared memory segments.
My questions is:
Where PG is storing data dictionary information (coming form system pg_*
tables)
while parsing the queries ? 
I suppose each each background process is parsing (and eventually caching)
the parsed SQL
statements in his own memory (within each backend process), aka there is no
SHARED_POOL as in Oracle.
That would mean that backand processes don't have a common place to check
whether sa same 
SQL query (with the same planner environment) is already parsed (and ready
for execution).
That would mean that each backend process could reuse only his "own" parsed
statements (provided 
that bind variables are used)

Is there any parameter (apart from "geqo_pool_size", I suppose) that limits
the size
of this "private pool" memory in each backend process?


Consider the following scenario.
If I have a system with 50 or 100 connection (and the corresponding 100
backend processes),
and one session  creates an index on a given table, how do the other 99
processes 
notice that they can use (or at least estimate the appropriatness of the
usage of) the new index ?
How PG ist doing this ?

I would be very grateful if someone can sched some light /links, previous
postings, comments/  
on this topic.

Regards, Milen 

-- 
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

"Milen Kulev" <makulev@gmx.net> writes:
> My questions is:
> Where PG is storing data dictionary information (coming form system pg_*
> tables) while parsing the queries ? 

There's a limited-size "catalog cache" in each backend process, which
might be the closest analogy to this.  Offhand I think it's fixed at
5000 rows --- there's no user-visible knob to control it, anyway.

> I suppose each each background process is parsing (and eventually caching)
> the parsed SQL
> statements in his own memory (within each backend process),

There is no such cache.  If you tell us to PREPARE something, we save
the plan, otherwise not.

> If I have a system with 50 or 100 connection (and the corresponding 100
> backend processes),
> and one session  creates an index on a given table, how do the other 99
> processes 
> notice that they can use (or at least estimate the appropriatness of the
> usage of) the new index ?

There's a cache-invalidation signaling mechanism for the catcache, which
is what drives this sort of thing.
        regards, tom lane

Hi Harris,
from oracle DBA point of view Enterprise DB is VERY cool. My boss will be
very happy to hear that there a way to get (paid) support for  a PG DB.

But at the end I want to undestand how PG (and its clone Enterprise DB )
is working ;) . Hopefully I don't need to read the whole source of PG
(several times) to understand a little bit deeper the internal mechanics of
PG.

Regards. Milen.

> --- Ursprüngliche Nachricht ---
> Von: "Jonah H. Harris" <jonah.harris@gmail.com>
> An: Milen Kulev <makulev@gmx.net>
> Betreff: Re: [HACKERS] PG process architecture
> Datum: Wed, 11 Jan 2006 09:44:31 -0500
> 
> As an Oracle DBA, you may want to take a look at EnterpriseDB (
> http://www.enterprisedb.com/)
> 
> 
> On 1/11/06, Milen Kulev <makulev@gmx.net> wrote:
> >
> > Hi listers,
> > I am experienced Oracle DBA und now I was given a task to evaluate
> > Postgresql.
> > May first goal is to compare the architecture of Oracle and Postgres.
> > After reading the fine manuals and several mailing lists, I have found
> > that the following parameters are analogous in
> > PG vs Oracle
> > ------------
> > shared_buffers <-> db_cache_size
> > wal_buffers <-> log_buffer
> >
> > shared_buffers and wal_buffers  are residing in shared memory segments.
> > My questions is:
> > Where PG is storing data dictionary information (coming form system pg_*
> > tables)
> > while parsing the queries ?
> > I suppose each each background process is parsing (and eventually
> caching)
> > the parsed SQL
> > statements in his own memory (within each backend process), aka there is
> > no
> > SHARED_POOL as in Oracle.
> > That would mean that backand processes don't have a common place to
> check
> > whether sa same
> > SQL query (with the same planner environment) is already parsed (and
> ready
> > for execution).
> > That would mean that each backend process could reuse only his "own"
> > parsed
> > statements (provided
> > that bind variables are used)
> >
> > Is there any parameter (apart from "geqo_pool_size", I suppose) that
> > limits
> > the size
> > of this "private pool" memory in each backend process?
> >
> >
> > Consider the following scenario.
> > If I have a system with 50 or 100 connection (and the corresponding 100
> > backend processes),
> > and one session  creates an index on a given table, how do the other 99
> > processes
> > notice that they can use (or at least estimate the appropriatness of the
> > usage of) the new index ?
> > How PG ist doing this ?
> >
> > I would be very grateful if someone can sched some light /links,
> previous
> > postings, comments/
> > on this topic.
> >
> > Regards, Milen
> >
> > --
> > Telefonieren Sie schon oder sparen Sie noch?
> > NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
> 

-- 
10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
+++ GMX - die erste Adresse für Mail, Message, More +++

Milen Kulev wrote:

>Hi Harris,
>from oracle DBA point of view Enterprise DB is VERY cool. My boss will be
>very happy to hear that there a way to get (paid) support for  a PG DB.
>  
>
There several highly qualified support vendors for PostgreSQL:

SRA America
Pervasive
and ourselves, the only dedicated PostgreSQL support vendor; Command Prompt.


>But at the end I want to undestand how PG (and its clone Enterprise DB )
>is working ;) .
>
EnterpriseDB is not a clone, it is a fork. That said, it is very cool 
that they
have created an Oracle look-a-like for much less but if you want PostgreSQL
that isn't EnterpriseDB.


Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/