Re: schema or database
От | Jim Nasby |
---|---|
Тема | Re: schema or database |
Дата | |
Msg-id | 552BD5DB.30207@BlueTreble.com обсуждение исходный текст |
Ответ на | Re: schema or database (Anil Menon <gakmenon@gmail.com>) |
Список | pgsql-general |
On 4/13/15 6:21 AM, Anil Menon wrote: > In addition to all these comments > > - If you use multiple databases, if you want to keep some "common" > tables (example counties_Table, My_company_details), its going to be a pain > - if you want to access tables across databases - you might need to > start using FDWs (which is going to be a administrative pain - syncing > passwords and stuff) > - you could set up security easier with multiple schemas - example userA > can only use schema A and no access to other schemas Please don't top-post. > On Mon, Apr 13, 2015 at 4:48 PM, Pavel Stehule <pavel.stehule@gmail.com > <mailto:pavel.stehule@gmail.com>> wrote: > > > > 2015-04-13 10:43 GMT+02:00 Albe Laurenz <laurenz.albe@wien.gv.at > <mailto:laurenz.albe@wien.gv.at>>: > > Michael Cheung wrote: > > I have many similar database to store data for every customer. > > Structure of database is almost the same. > > As I use same application to control all these data, so I can only use > > one database user to connect to these database. > > And I have no needs to query table for different customer together. > > > > I wonder which I should use, different shema or different database to store data? > > > > I 'd like to know the advantage and disadvantage for using schema or database. > > In addition to what others have said: > > If you use multiple schemas within one database, the danger is > greater that > data are written to or read from the wrong schema if your > application has a bug > ans does not make sure to always set search_path or qualify > every access with a > schema name. > > With multiple databases you are guaranteed not to access data > from a different > database. > > The main downside that I see to multiple databases is the > overhead: each of > the databases will have its own pg_catalog tables. > > > It can be advantage - if your schema is pretty complex - thousands > procedures, tables, then separate pg_catalog can be better - there > are issues with pg_dump, pg_restore. > > So it depends on catalog size and complexity. Two things no one has mentioned. First, you could also use row-level security. If you plan on each customer having a fairly small amount of data, this is by far your most efficient option. Anything else will result in either huge catalogs or a lot of wasted catalog space. Second, if you do per-database, that makes it trivial to scale across multiple servers. Regarding backups; you can easily do partial either way with pg_dump; there's really no difference. You can't do partial with PITR, but that's true for both schema and database. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Мартынов АлександрДата:
Сообщение: Re: SELinux context of PostgreSQL connection process