On 6/25/10 3:28 PM, Kevin Grittner wrote:
<tony@xxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
