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.
You cannot keep the connection and change users.
A change of database user always means a new connection and a new backend
process.
Yours,
Laurenz Albe