Re: How many Mandatory Process are there in Postgres

Hi Tomas,

Thank you for the reply.

> Well, there is a bunch of processes started at the beginning, and then
> there is one backend process for each connection (see the postgresql.conf
> how many connections are allowed in your case).
>
I do agree with you, that there would be bunch of process. Can you describe
any?

> Temp_buffers on the other hand are used to access temporary tables - so
> they serve s a completely different purpose and are session-specific. Each
> session may consume the given amount of memory.

So, what happen when the shared_buffer is filled completely, where it does
its operation.

> Yes, it's used for vacuuming (and many other things related to
> maintenance). AFAIK the amount of memory is 'upper limit' and does not
> mean the autovacuum will consume that.
>
> Anyway disabling the autovacuum is a bad idea, just as using not enough
> memory.

My question is does maintenance_work_mem is occupied or allocated, even the
autovacuum is off.

> Oracle uses a completely different implementation of MVCC architecture. It
> overwrites the data and then uses rollback segments to provide 'previous
> versions' to running transactions etc.
>
> PostgreSQL does not overwrite the data - it just creates a copy of the row
> and then decides which version should each session see (depending on the
> transaction IDs etc.). So it does not need to do rollbacks the way Oracle
> does, but it has to remove stale copies of the rows (such that no running
> transaction can see) - that's why there is VACUUM.

Here, if you have issued a command pg_start_backup() at that time the
cluster is freezed, and if any transaction takes place before the
pg_stop_backup() issued at that time where the transaction data will be kept
if the undo's are not there.

Regards
Raghav

2010/3/25 <tv(at)fuzzy(dot)cz>

> > Hi All,
> >
> > When we start the postgres server, the writer process, wal process,
> > postmaster, autovacuum ( if autovacuum is on), stats collector will come
> > into picture as mandotory process. My question is, is there any processes
> > apart from these process, what are the mandotory process come along with
> > the
> > postgres server and how many sleeping processes are there.
>
> Well, there is a bunch of processes started at the beginning, and then
> there is one backend process for each connection (see the postgresql.conf
> how many connections are allowed in your case).
>
> > Few important question about Postgres Architecture
> > ======================================
> > 1. When does temp_buffer comes into existence in a database session (
> like
> > when shared_buffer completely filled or any wal_buffer filled) ?
>
> AFAIK Those are completely different buffers I.
>
> Shared buffers are used as a "shared cache" for all the backends /
> sessions. Wal_buffers are used when writing data to the write-ahead-log
> (transaction log) and are shared by all backends just like the shared
> buffers.
>
> Temp_buffers on the other hand are used to access temporary tables - so
> they serve s a completely different purpose and are session-specific. Each
> session may consume the given amount of memory.
>
> > 2. What is process array in shared memory?
>
> No, idea (not a PostgreSQL hacker) but I'd guess it has something to do
> with the backends (list of backends).
>
> > 3. maintenance_work_mem is used for vacuuming(does this memory allocated
> > if
> > autovacuum is off)?
>
> Yes, it's used for vacuuming (and many other things related to
> maintenance). AFAIK the amount of memory is 'upper limit' and does not
> mean the autovacuum will consume that.
>
> Anyway disabling the autovacuum is a bad idea, just as using not enough
> memory.
>
> > 4. As oracle, Postgres doesnt have any undo_tablespace, for rollback
> > transaction. But it is handled in BEGIN /END block. So question is where
> > the
> > transaction data is stored.
>
> Oracle uses a completely different implementation of MVCC architecture. It
> overwrites the data and then uses rollback segments to provide 'previous
> versions' to running transactions etc.
>
> PostgreSQL does not overwrite the data - it just creates a copy of the row
> and then decides which version should each session see (depending on the
> transaction IDs etc.). So it does not need to do rollbacks the way Oracle
> does, but it has to remove stale copies of the rows (such that no running
> transaction can see) - that's why there is VACUUM.
>
> Regards
> Tomas
>
>