[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] Partition database between users and implement disk quotas
From:       Brice_André <brice () famille-andre ! be>
Date:       2012-10-21 19:18:14
Message-ID: CAOBG12mysVWSiEWUOCv2RHJ=doJyLfc++=D4W5odk5aDm9LDUw () mail ! gmail ! com
[Download RAW message or body]

Hello,

I wrote an aplication where I store data from different users whithin the
same database. There is no link between data of different users.

In my current implementation, I added a column "user_id" in each table,
and, with views and rules mechanisms, I am able to hide data from other
users. Everything works fine and I am happy with this solution. But I now
have another need : I would want to measure the disk usage of each user
such that I can restrict the access to the database when a user exceeds its
limit.

I envisaged three solutions to solve this problem, but I do not know which
one would be the best :

Solution1 : keep the same database layout, and count the size of each
record of a user. As some columns have variable sizes, this is not a really
simple solution. And I have no idea on how to take into account index and
stuff like that. I am not sure this solution is feasible and, if yes, I
fear it will not be efficient to compute disk usage on this way.

Solution 2 : use a separate database for each client. This solution is
probably the simplest one. But, one of the advantage of my previous
solution was that I was able to use connection pool to connect to my
database as all users are using the same database with same db user (access
is provided via a web service). With this solution, this will no more be
possible: I will need a dedicated connection for each user. A second
problem I am fearing is that I have a lot of clients (several hundreds) and
I am not sure this is a good idea to create so much database on the same
server.

Solution 3 : use the table partitionning mechanism. I never used this
feature, but from what I read in the doc, it seems that I could use this
mechanism to put data from different clients in different partitions. As
each partition is a dedicated sub-table, I suppose I could use dedicated
postgresql mechanism to compute its size (including associated index and so
on). I really think this solution is well fitted to my need. The only thing
that I fear is that I don't know if it's a good idea to partition a table
in hundreds of different partitions ?

So, does someone has good advices to solve my problem ? Maybe there is
another solution that I am not aware of ?

Thanks in advance for your help,
Brice

[Attachment #3 (text/html)]

Hello,<br><br>I wrote an aplication where I store data from different users=
 whithin the same database. There is no link between data of different user=
s.<br><br>In my current implementation, I added a column &quot;user_id&quot=
; in each table, and, with views and rules mechanisms, I am able to hide da=
ta from other users. Everything works fine and I am happy with this solutio=
n. But I now have another need : I would want to measure the disk usage of =
each user such that I can restrict the access to the database when a user e=
xceeds its limit.<br>
<br>I envisaged three solutions to solve this problem, but I do not know wh=
ich one would be the best : <br><br>Solution1 : keep the same database layo=
ut, and count the size of each record of a user. As some columns have varia=
ble sizes, this is not a really simple solution. And I have no idea on how =
to take into account index and stuff like that. I am not sure this solution=
 is feasible and, if yes, I fear it will not be efficient to compute disk u=
sage on this way.<br>
<br>Solution 2 : use a separate database for each client. This solution is =
probably the simplest one. But, one of the advantage of my previous solutio=
n was that I was able to use connection pool to connect to my database as a=
ll users are using the same database with same db user (access is provided =
via a web service). With this solution, this will no more be possible: I wi=
ll need a dedicated connection for each user. A second problem I am fearing=
 is that I have a lot of clients (several hundreds) and I am not sure this =
is a good idea to create so much database on the same server.<br>
<br>Solution 3 : use the table partitionning mechanism. I never used this f=
eature, but from what I read in the doc, it seems that I could use this mec=
hanism to put data from different clients in different partitions. As each =
partition is a dedicated sub-table, I suppose I could use dedicated postgre=
sql mechanism to compute its size (including associated index and so on). I=
 really think this solution is well fitted to my need. The only thing that =
I fear is that I don&#39;t know if it&#39;s a good idea to partition a tabl=
e in hundreds of different partitions ?<br>
<br>So, does someone has good advices to solve my problem ? Maybe there is =
another solution that I am not aware of ?<br><br>Thanks in advance for your=
 help,<br>Brice<br>


[prev in list] [next in list] [prev in thread] [next in thread]