On 8/31/07, Brennan, Sean (IMS) <BrennanS@xxxxxxxxxx> wrote: > Hi, > I was looking for opinions on performance for a design involving schemas. We have a 3-tier system with a lot of hand-written SQL in our Java-based server, but we want to start limiting the data that different users can access based on certain user properties. Rather than update hundreds of queries throughout our server code based on these user properties we were thinking that instead we would do the following: > 1. Build a schema for each user. > 2. Reset the users search path for each database connection so it accesses their schema first, then the public schema > 3. Inside that users schema create about 5 views to "replace" tables in the public schema with the same name. Each of these views would provide only a subset of the data for each corresponding table in the public schema based on the users properties. > 4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views. > > Does anyone have any thoughts on how this may perform over the long-haul? Database cleanup or maintenance problems? This will work relatively ok if the main tables in the public schema do not change very much...otherwise you have to drop all the views, change tables, and re-make. Even still, that's a lot of rules flying around, and excessive use of rules is asking for trouble. You may want to explore trying to do it using a single view for each underlying table, and drop the schemas approach (which I would be looking at for separate physical tables, not views). A very simple way to do this that might work for you is: create view foo_view select * from foo where owner_col = current_user; plus update, delete rules, etc. you can then rename the tables in place for seamless app integration. You could replace the current_user item with an expression but the performance issues could be large...owner_col could be an array though as long as it's relatively small (you may want to look at array indexing techniques if you go the array route). merlin ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
