Re: Architecting a database

On 6/25/10 3:28 PM, Kevin Grittner wrote:
> <tony(at)exquisiteimages(dot)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