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 <t...@fuzzy.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 > >
