Thread: Help whit schemas

On Monday 12 February 2007 05:58, Gustavo wrote:
> Dear Postgre users,
>
>
> Im from Argentina and I have a question.
>
> im development a client-server application and i have a performance dude. I
> have a Db whit 10 tables. The server could attend a lot of clients at the
> same time and every client use this tables to insert rows. Every client is
> independent of the rest (dont share information). I want to know if is
> convenient create one schema for every user (10 tables from schema) or
> every users sharing the same schema (only 10 tables) Every user could
> insert a lot of rows per table.

Without more information, it is impossible to comment fully.  However, I would
generally use a single schema unless the data for each user is completely
private to that user.

Sean

Hi Gustavo,

> im development a client-server application and i have a performance
> dude. I have a Db whit 10 tables.
> The server could attend a lot of clients at the same time and every
> client use this tables to insert rows.
> Every client is independent of the rest (dont share information). I
> want to know if is convenient create one schema for every user (10
> tables from schema) or every users sharing the same schema (only 10
> tables)
> Every user could insert a lot of rows per table.

There are at least 2 parameters to watch.
How many would be "a lot of clients at a time" and are there even more
users of the DB?
For example an web email-system where you had 100.000 users but only
10.000 clients at a time.

How much are "a lot of rows per table"?


1) You design with a schema per user.
Thats nice and clean but I guess there will be a performance issues
because the DBMS had to manage a lot of distinct datasets concurrently.
But everytime your frontend changes, you had to update "a lot" of tables
in "a lot" of schemas. Probaply this doesn't happen very often.

2)   You go with one set of tables.
Then you have to mark every row for each separate user to mimic data
separation like schemas would and you have to make sure that the
separation actually works.
You need at least one collumn "user_id" where you can separate the
clients' data.
If not all tables are connected by foreign keys you need such a user_id
in eyery set of tables.

The limitation would be if there is enough room in one table to insert
"a lot of" records of "a lot of "users.
On the other hand I suppose Postgres handles huge datasets easier than
permantly swiching between schema contextes for all the concurrent users.