sapdb wote:
> We have an application where our customers each have their
> own database,
> and their own username/password that grants them access to
> that database.
> This is on postgresql.
>
>
> We are considering one of two approaches with sapdb. One is
> to create a
> separate set of tables for each user (about 20), and log in
> the users from
> our application using their database username/password.
>
> The other approach we could take is to have one set of tables under a
> master user, add another column to each table that contains
> the customer
> id, and do the login authentication in our application using our own
> authentication database.
>
> Approach number two does make it easier to update schemas
> when we need to
> make a change, and makes management easier. All the data is
> transaction
> based. These are financial transactions so separation is
> also an issue.
> One thing I do not like about approach two is that it leaves open the
> option for bugs in the application which could let users see
> other users
> data.
>
> My main question for the list though is performance related.
> Does a large
> number of users in the system degrade performance
> significantly for the login process?
Let me explain:
Meta-data, i.e. data about users, tables, views is mainly stored in one
file.
If you have 5000 users, 20 tables only once, then you will have around
5000 + 20 + some
rows in this file. This file is not too large, maybe it will have root +
indexlevel + leaf level
in its B*-tree, 3 I/Os in maximum to reach each meta-data. Root will
usually stay in
cache, therefore there should be no significant influence, especially no
degration
of performance.
If you have 5000 users, each with 20 tables --> 100 000 table-entries.
If you want to have 1 index on one of those tables, you will have 5000
(1 per user)
more entries.
Therefore you will have 100 000 table-entries + 5000 user-entries + 5000
* x index-entries.
Mhm, sounds a little bit bigger, but no real degration of performance, I
assume.
> And how much extra overhead is it when you
> have say 5000
> users each with their own set of tables versus just one set of tables
> under a single user? Will query performance degrade
> significantly with
> the large number of users who each have their own tables?
As I wrote above, finding meta-data will (perhaps) need a little bit
longer, but you
will not be able to measure.
What is interesting is the fact, that for each table (100 000 we are
thinking of) +
5000 * existing_indexes, i.e. for each single file in the database an
entry in the
filedirectory is done and for each file at least one root-page is used.
I have no idea, why you think of so many customers and how many data we
can expect
for each customer in each of those 20 tables/indexes. If only few entries
are in each
table, much more space will be needed to store them compared to one big
table because
at least the root page of each file will not be filled as much as it was
in the one big file
of each kind.
If more pages are needed for work, then the data cache may need more I/O
to have the needed info at hand or the datacache should be increased.
The DDL and the filedirectory would prefer the 'one table for all users'
approach.
But on the other hand, if each user has a lot of data in all tables, may
be for performance
reasons it may be better to have several tables. This depends on the
primary-key
of the big tables, where the user-identification is there, if this is
always specified,...
If the user_identification is the first primary key column and this is
always specified,
the performance should be ok.
For a user-identification, you can use the username (char(32)) or the
special register uid
(as known in oracle) which is not in the docu by now and is a fixed(10).
To avoid problems with applications you can use views, for example
create table basetab (uid_col fixed(10) default uid, <all the other
columns>)
create view viewtab as select <all the other columns> where uid_col = uid
with check_option
grant viewtab to <all users>
or something like this.
Maybe these comments help to decide what to do.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
