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?

The names of the processes are quite descriptive I guess - just start the
database and use 'ps ax' command. And then use the names to search the
docs, there's a plenty of details available there.

>> 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.

See this:
http://wiki.postgresql.org/wiki/User:Gsmith#How_is_the_shared_buffer_cache_organized

Shared buffers is a LRU cache, i.e. when it's full and a new buffer is
requested, some of the 'oldest' buffers are removed.

>
>
>> 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.

No. As I wrote the maintenance_work_mem is the maximum amount of memory
the vacuum may consume, so if it is not running the memory is not
allocated. BTW you could find this out simply by starting the server with
enabled / disabled autovacuum and comparing the amount of memory consumed.

>> 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.

What do you mean by 'freezed'? The cluster operates normally, the
pg_start_backup() just creates a backup label (and performs a checkpoint),
but that's not a problem. OK, there could be a performance decrease
because of full page writes, but the data will be processed as if there is
no backup running. PostgreSQL does not need the checkpoints to perform
backup.

Tomas