Search Postgresql Archives

Re: schema or database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx
<mailto:pavel.stehule@xxxxxxxxx>> wrote:



    2015-04-13 10:43 GMT+02:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx
    <mailto:laurenz.albe@xxxxxxxxxx>>:

        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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux