Re: Experience with many schemas vs many databases

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.