Re: Databases vs Schemas

On Fri, Oct 9, 2009 at 1:46 PM, Scott Otis <scott(dot)otis(at)intand(dot)com> wrote:
> Over the next couple of months we will be creating an instance of our solution for each public school district in the US which is around 18,000.  That means currently we would be creating 18,000 databases (all on one server right now – which is running 8.4).  I am assuming this is probably not the best way of doing things.

Schema advantages:
*) maintenance advantages; all functions/trigger functions can be
shared. HUGE help if you use them
*) can query shared data between schemas without major headaches
*) a bit more efficiency especially if private data areas are small.
kinda analogous to processes vs threads
*) Can manage the complete system without changing database sessions.
This is the kicker IMO.

Database Advantages:
*) More discrete. Easier to distinctly create, dump, drop, or move to
separate server
*) Smaller system catalogs might give efficiency benefits

merlin