Re: How many Mandatory Process are there in Postgres

> 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