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
