From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Anil Menon <gakmenon(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Cc: | Michael Cheung *EXTERN* <vividy(at)justware(dot)co(dot)jp> |
Subject: | Re: schema or database |
Date: | 2015-04-13 14:42:35 |
Message-ID: | 552BD5DB.30207@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>> wrote:
>
>
>
> 2015-04-13 10:43 GMT+02:00 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at
> <mailto:laurenz(dot)albe(at)wien(dot)gv(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-04-13 15:17:56 | Re: Pgagent |
Previous Message | Мартынов Александр | 2015-04-13 14:31:56 | Re: SELinux context of PostgreSQL connection process |