On 6/25/10 3:28 PM, Kevin Grittner wrote:
<t...@exquisiteimages.com> wrote:
With the PostgreSQL type tables I am not so certain how the data
is arranged within the one file. Does having the data all in one
database allow PostgreSQL to better utilize indexes and caches or
does having a number of smaller databases provide performance
increases? In case it is important, there are 2000 clients
involved, so that would be 2000 databases if I followed my current
FoxPro related structure.
The implications of putting multiple clients in a table, with a
client's rows identified by a client_id column, are probably fairly
obvious. If many of those 2,000 clients have tables with millions of
rows, performance could suffer without very careful indexing,
managing tables with billions of rows can become challenging, and
there could be concerns about how to ensure that data from one
client isn't accidentally shown to another.
You should also ask whether there are social (that is, nontechncal) reasons to
avoid multiple clients per table.
When a customer asks about security and you tell them, "You get your own database, nobody else
can log in," they tend to like that. If you tell them that their data is mixed with everyone
else's, but "we've done a really good job with our app software and we're pretty sure there
are no bugs that would let anyone see your data," that may not fly.
People will trust Postgres security (assuming you actually do it right) because
it's an open source, trusted product used by some really big companies. But
your own app? Do you even trust it?
Even if your application IS secure, it may not matter. It's what the customer
believes or worries about that can sell your product.
We've also found another really good reason for separate databases. It lets
you experiment without any impact on anything else. We have scripts that can
create a database in just a few minutes, load it up, and have it ready to demo
in just a few minutes. If we don't end up using it, we just blow it off and
its gone. No other database is impacted at all.
Craig
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
