Database Reference
In-Depth Information
Option 1 is covered in the recipe
Using
multiple
schemas
.
Option 2 is covered in the recipe
Giving
users
their
own
private
database
.
Option 3 is covered in the recipe
Running
multiple
servers
on
one
system
.
Which is best? Well, it's certainly a topic many people ask, and something on which many
views exist. The answer lies in looking at the specific requirements, which are as follows:
F
If our goal is the separation of physical resources, then options 3 or 4 work best.
Separate database servers can be easily assigned different disks, individual memory
allocations can be assigned, and we may take the servers up or down without
impacting the other.
F
If our goal is security, then Option 2 may be sufficient.
F
If our goal is merely the separation of tables for administrative clarity, then options 1
or 2 may also be useful.
Option 2 allows complete separation for security purposes. That does also prevent someone
with privileges on both groups of tables from performing a join between those tables. So, if
there is a possibility of future cross-analytics, it might be worth considering option 1, though it
might also be argued that such analytics should be carried out on a separate data warehouse,
not by co-locating production systems.
Option 3 has one simple difficulty in many of the PostgreSQL distributions. The default
installation uses a single location for the database, making it a little harder to configure
that option. Ubuntu/Debian handles that aspect particularly well, making it more attractive
in that environment.
Option 4 can be arranged using virtualization technology, though outside of the scope
of this topic.
I've seen people who use PostgreSQL with thousands of databases, though it would be my
opinion that the majority of people use just one database, such as postgres (or at least only
a few). I've also seen people with a great many schemas.
One thing you will find is that almost all of the admin GUI tools become significantly less useful
with 100s or 1000s of items to display. In most cases, admin tools use a tree-view, which
doesn't cope gracefully with large numbers of items.
Using multiple schemas
We can separate groups of tables into their own "namespaces", referred to as "schemas" by
PostgreSQL. In many ways they can be thought of as being similar to directories, though that
is not a precise description.
