Re: PostgreSQL users on webhosting

Benefits of multiple instances:
(1) Let's say you're using the one-instance method and one of your web
users is a less-than-talented developer, and makes an infinite loop that
fills the database with garbage. Not only will that hurt performance,
but if it fills the disk than no other users can even commit a
transaction! If you seperate the instances, you can run each as its own
uid and control each with quotas, etc.
(2) You can do a certain amount of favoritism, i.e. you can allocate a
lot of resources to your best customers, and less to the low-paying
customers.

Costs:
(1) The databases can't use eachother's shared memory. That will mean
that the databases with high activity can't cache data in the shared
memeory of a database with low activity.
(2) The RAM from the extra processes for each user. If a database has 0
connections, it's still using memory for the postmaster.
(3) Each instance will require about 30MB of disk for the $PGDATA
directory. In contrast, using the one-instance method it only requires
5MB for an additional DB (approximate).

Overall, I'd say it would be difficult to run seperate instances if
you're trying to have hundreds of people on the same server. If you have
a more managable number you could do it quite effectively I think. I
would recommend lowering the per-instance shared memory so that the OS
could buffer more (mitigating cost #1).

If you can't run multiple instances, just consider the risks and
understand that you should try to limit the users somehow.

Regards,
Jeff Davis

On Wed, 2005-01-05 at 09:49 -0500, Alan Garrison wrote:
> Jeff Davis wrote:
>
> >However, for truly good seperation, I recommend that you run a seperate
> >instance of postgresql (with a seperate $PGDATA directory) for each
> >user, and run it under the UID of that user. It requires a little more
> >disk space per account, but in a dollar amount it's virtually zero with
> >today's disk prices. You will be able to tie the user into filesystem
> >quotas, etc., much more easily, and also you could tune the DBs to the
> >individual users if needed.
> >
> >
> Out of curiosity, what kind of performance hit (whether CPU, memory,
> disk activity) is incurred with having a lot of postmasters running in
> this kind of a setup versus one postmaster with lots of databases? We
> typically run one postmaster for a lot of separate web applications, but
> I like the notion of a instance-per-user (for both security and
> maintenance). In the case of having several "big" databases on one
> server, would tuning stragegies need to keep in mind the settings of
> other instances, or would you just tune each one as if it were the only
> one on the box and let the OS deal with memory+disk load of multiple
> instances?
>
> /hope this question makes sense, waiting for coffee to kick in
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org