one of the reasons PostgreSQL is less popular with shared hosting
services is that there is insufficient isolation between database
users. For instance, one user leaves a <Idle in TransactioN> pending
due to sloppy programming, and the entire cluster eventually can't be
vacuumed. There's numerous other places where the isolation between
postgres users is insufficient (visibility of information in pg_catalog,
for instance).
Also, that other 'big name' commercial database has a concept of a
listener, where multiple database 'clusters' can be accessed via the
same port.
I have an idea that combines both of these.
We add a new type of "super tablespace" to postgres, we'll call these
'instances' (thats what the Big O calls them, anyways). An instance has
its own WAL logging, WAL writer, and its own autovacuum, and is
associated with one or more databases. A given user can have a default
'instance' such that any database they create will be in that instance.
An instance can optionally contain multiple tablespaces. An instance
can contain multiple databases, these databases still have schemas in them.
All users are common to and managed in the root instance. The
instances could be associated with either the user, or with the
databases, I'm not sure which is more appropriate.
An issue to be resolved: Should each of these 'instances' have its own
pg_catalog, or should all instances continue to use the master pg_catalog ?