From: | Johan Nel <johan(dot)nel(at)xsinet(dot)co(dot)za> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Experience with many schemas vs many databases |
Date: | 2009-11-15 11:42:09 |
Message-ID: | hdopf3$hph$1@news.eternal-september.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
undisclosed user wrote:
> I have hit a wall on completing a solution I am working on. Originally,
> the app used a db per user (on MyIsam)....the solution did not fair so
> well in reliability and performance. I have been increasingly interested
> in Postgres lately.
>
> Currently, I have about 30-35k users/databases. The general table layout
> is the same....only the data is different. I don't need to share data
> across databases. Very similar to a multi-tenant design.
>
> Here are a few questions I have:
>
> 1. Could postgres support this many DBs? Are there any weird things that
> happen when the postgres is used this way?
As John indicated, not any traditional environment that will handle that
well..
> 2. Is the schema method better? Performance, maintainability, backups,
> vacuum? Weird issues?
I would rather use schemas to logically group tables together. Insert a
user_id column in the tables and ensure each user can only see the rows he
has access to via query design to limit user access. Something in the
line of:
CREATE OR REPLACE VIEW SomeTableQuery AS
SELECT * FROM SomeTable WHERE user_id = current_user;
Where SomeTable has a column user_id that defaults to current_user.
Johan Nel
Pretoria, South Africa.
From | Date | Subject | |
---|---|---|---|
Next Message | Jorge Godoy | 2009-11-15 11:50:18 | Re: Experience with many schemas vs many databases |
Previous Message | John R Pierce | 2009-11-15 09:08:10 | Re: Experience with many schemas vs many databases |