On Jun 24, 2009, at 4:32 AM, "Albe Laurenz" <laurenz.albe@wien.gv.at>
wrote:
> Mike Ivanov wrote:
>> Please help me to make a decision on how to manage users.
>>
>> For some reason it is easier in the project I'm working on to split
>> data
>> by schemes and assign them to Postgres' users (I mean those created
>> with
>> CREATE USER) rather than support 'owner' fields referring to a global
>> users table.
>
> You know that (unlike in Oracle) user and schema is not coupled in
> PostgreSQL, right? So you can have one user owning tables in various
> schemata
> and many users owning tables in one schema.
>
>> The question is what could be the consequences of having a large
>> number
>> of them (tens of thousands)?
>
> It shouldn't be a problem.
> The only critical number is the number of concurrent connections
> at a given time.
>
>> Context:
>>
>> - it is a web app
>> - thousands of concurrent requests from different users
>> - amount of user's data in the db is relatively small
>>
>> Concerns:
>>
>> - how big is the performance/memory penalty on switching users in the
>> same connection (connections are reused of course)?
>> - will it hurt the cache?
>> - are prepared statements kept per user or per connection?
>> - is the query planner global or somehow tied to users?
>>
>> I'd be glad to hear any opinions/suggestions.
A bunch of small tables might possibly take up more space than a
smaller number of larger tables, increasing memory requirements...
> You cannot keep the connection and change users.
> A change of database user always means a new connection and a new
> backend
> process.
I don't think this is true. You can use SET SESSION AUTHORIZATION,
right?
...Robert