Thread: an enhancement idea

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 ?

John R Pierce <pierce@hogranch.com> writes:
> 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).

If you want that level of isolation, you have to give each user his own
cluster.

            regards, tom lane

On 12/05/10 12:27 PM, Tom Lane wrote:
> John R Pierce<pierce@hogranch.com>  writes:
>> 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).
> If you want that level of isolation, you have to give each user his own
> cluster.

as postgresql is currently structured, yes, and further, each cluster
needs its own listener port which is, IMHO, rather ugly.

My idea of adding an 'instance' layer allows clusters to share ports.
in fact, the first generation of this idea was to fire up a cluster for
each user, but put them all under the common postmaster, which would
look up the database being connected to, and point the connection's
forked postgres server process at the proper cluster, much the way the
Oracle listener forks Oracle instances.

John R Pierce <pierce@hogranch.com> writes:
> On 12/05/10 12:27 PM, Tom Lane wrote:
>> If you want that level of isolation, you have to give each user his own
>> cluster.

> as postgresql is currently structured, yes, and further, each cluster
> needs its own listener port which is, IMHO, rather ugly.

The amount of work needed to get rid of that small bit of ugliness seems
far out of proportion to the value.

            regards, tom lane

>>> If you want that level of isolation, you have to give each user his own
>>> cluster.
>> as postgresql is currently structured, yes, and further, each cluster
>> needs its own listener port which is, IMHO, rather ugly.
> The amount of work needed to get rid of that small bit of ugliness seems
> far out of proportion to the value.

With the current architecture, you would need to develop service
management that could potentially handle 100s of clusters, in a shared
web host sort of environment.   I sure wouldn't want to have to manage
100s of sysV /etc/rc3.d kinda services to launch all these postmasters,
ugh.   As I'm envisioning it, the user management would be in the master
cluster, child cluster/instances would not have their own users.

yeah, I suppose this is pointless.  ah well, after a couple beers last
night, it seemed like a fantastic idea :D

On 12/06/2010 04:41 AM, John R Pierce wrote:

>> If you want that level of isolation, you have to give each user his own
>> cluster.
>
> as postgresql is currently structured, yes, and further, each cluster
> needs its own listener port which is, IMHO, rather ugly.
>
> My idea of adding an 'instance' layer allows clusters to share ports.

How do you plan to handle the use of system V shared memory? Each
cluster needs its own reserved, pinned shm segment. You'll be wasting
memory on idle clusters while starving busy clusters for memory.

For shared hosting / multi-tenant DB needs, wouldn't it be better to
improve Pg's core to handle the job better? Per-user storage quotas,
database-scoped user IDs, age-limited transactions (though that can
already be done pretty easily with a simple script), access-filtered
views in pg_catalog, etc.

--
Craig Ringer