Re: Implications of having large number of users

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc: Mike Ivanov *EXTERN* <mikei(at)activestate(dot)com>, "<pgsql-performance(at)postgresql(dot)org>" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Implications of having large number of users
Date: 2009-06-24 11:30:42
Message-ID: EECE8B8B-C0C5-4CC5-8094-8D27443289BA@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Jun 24, 2009, at 4:32 AM, "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dave North 2009-06-24 12:43:23 Nested Loop "Killer" on 8.1
Previous Message Albe Laurenz 2009-06-24 08:32:19 Re: Implications of having large number of users