Re: Multi tenancy : schema vs databases
От | |
---|---|
Тема | Re: Multi tenancy : schema vs databases |
Дата | |
Msg-id | 20160930121903.5A28983C@m0087795.ppops.net обсуждение исходный текст |
Ответ на | Multi tenancy : schema vs databases (Rakesh Kumar <rakeshkumar464@outlook.com>) |
Ответы |
Re: Multi tenancy : schema vs databases
(Rakesh Kumar <rakeshkumar464@outlook.com>)
|
Список | pgsql-general |
>> I've been reading this discussion with great interest, to see what >> other Postgres experts think. :-) > >I am bit disappointed that most of the replies are questioning why we are doing what we are doing. Once again, we (db designers)have no choice in that. What I would like to know that which one is better :- multiple db vs multiple schema. Read few interesting arguments and noted that connection pooling works better with multiple schemas than dbs. Anythingelse? I've been curious to see what the others would tell you too. :) There's a lot about the admin side I can't advise you on,but I'll take a shot anyway from an overall-app view... Your situation sounds somewhat similar to my previous job. There, we hosted multiple customers on the same physical server.We also used Mysql, so your question wouldn't have applied there. But translating that situation to if they had usedPostgres, I think I'd have told them to do 1 DB and many schemas because of the resource sharing. That would have workedfor them because the DBs were completely internal; i.e. the customer could not get to the DB directly -- the customercould only see the data thru our app. Given that, then each DB server would have hosted between 1-50 customers (dependingon their size). The difficult spot with Postgres (AFAICT) is that if your customer has direct access to the DB, then 1 DB to many schemawould break your security requirements. We had a situation at my present job recently where one of customers wantedaccess to our log tables. The first idea was to grant them select-only privs to the logging schema thinking that wouldbe safe enough as they couldn't get to the main data schema. However, in testing, we found that wasn't good enough asit allowed them to at least look at table designs even if they couldn't get to the data. That was bad so in a sense Postgresfailed us (to the experts if there is a way to do this, I'd love to know how to do that). We considered creatinga separate DB for the logging data, but decided that would make things too difficult and we didn't want to "waste"server resources in that way. We ended up writing a small app that the customer could query and it read the log filesfor them, ensuring security was maintained. This is why others are asking you about your [security] requirements. BTW, if you go the 1 DB and many schema way, be sure you fully understand "search_path". I don't know if that's helpful to you or not, but hopefully it was at least a little. Kevin
В списке pgsql-general по дате отправления: