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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
