From: | Brice André <brice(at)famille-andre(dot)be> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Partition database between users and implement disk quotas |
Date: | 2012-10-21 19:18:14 |
Message-ID: | CAOBG12mysVWSiEWUOCv2RHJ=doJyLfc++=D4W5odk5aDm9LDUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2012-10-22 11:29:34 | Re: Trigger triggered from a foreign key |
Previous Message | lmagnell | 2012-10-19 20:49:43 | Re: Insert strings that contain colons into a table |