Re: Management of Concurrent Clients

Hi Tino,

Thanks for your response! I did not provide more specific technical
details because I just came in to the project. So, I will try to provide
as much information as I can.

Former versions of postgresql used in this project are postgresql
(starting at 6). I will try to find out the exact revision number.

It has been reported to me that the main problem encountered with former
postgresql versions is that when a process performs an update (of a
record in any table), the whole database was locked which prevents the
other processes from accessing the database (to retrieve data for
instance). In other words, the process performing the update operation
had an exclusive access to the database. This had an important impact on
the system performance and justified the fact that tables were spread
into different databases.

Usually, granularity on locks is at the row or table level but maybe
there was a specific configuration that was used which made it so that
the entire database was locked. Before starting any major database
re-structuring, I want to make sure that are not specific cases where
this situation occurs.

Thanks!
Hanan

-----Original Message-----
From: Tino Wildenhain [mailto:tino(at)wildenhain(dot)de]
Sent: Sunday, February 26, 2006 12:54 PM
To: Hanan Bentaleb
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Management of Concurrent Clients

Hanan Bentaleb schrieb:
> Hi all,
>
>
>
> I am working on an application that involves multiple processes
> accessing and updating different databases: tables are split into 3
> different databases that are accessed by 3 different processes. This
> architectural decision was made in the past because it has been
noticed
> that former revisions of postgres used to lock the whole database when
a
> process performs an update (on a record) which prevents the other
> processes from accessing the database.

Which former revision should that have been? Was it postgres95
or was it postgresql yet?

> After some readings on postgres concurrent client management, I am
> planning on changing the database structure to make all application
> processes access a shared database (i.e. put all application tables in
a
> single database that would be shared among the 3 processes). I did
not
> find in the documentation (at least in the current versions of
postgres
> starting from revision 7) any specific problem related to doing this,
> comments on this problem will be appreciated.

Its very unclear what kind of problems you expect. Accessing postgresql
with lots of clients is a common practice. You can also use schemas
to isolate your applications in the same database if you need that.

If you want better advice, give more details on what your application
does and what exact problems you expect.

Regards
Tino