Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group