Re: Thousands of tables versus on table?

On Wed, 6 Jun 2007, Scott Marlowe wrote:

>> > pretty doggone cheap relative to the table searches, which means it
>> > almost certainly will lose badly if you carry the subdivision out to
>> > the extent that the individual tables become small. (This last could
>> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> what is considered 'small'? a few thousand records, a few million records?
>
> I would say small is when the individual tables are in the 10 to 20 Megabyte
> range. How many records that is depends on record width, of course.
> Basically, once the tables get small enough that you don't really need
> indexes much, since you tend to grab 25% or more of each one that you're
> going to hit in a query.

thanks, that makes a lot of sense

>> what multiplication factor would there need to be on the partitioning to
>> make it worth while? 100 tables, 1000 tables, 10000 tables?
> Really depends on the size of the master table I think. If the master table
> is about 500 Megs in size, and you partition it down to about 1 meg per child
> table, you're probably ok. Walking through 500 entries for constraint
> exclusion seems pretty speedy from the tests I've run on a 12M row table that
> was about 250 Megs, split into 200 to 400 or so equisized child tables. The
> time to retrieve 85,000 rows that were all neighbors went from 2 to 6
> seconds, to about 0.2 seconds, and we got rid of indexes entirely since they
> weren't really needed anymore.

remember, I'm talking about a case wher eyou don't have to go through
contraint checking. you know to start with what customerID you are dealing
with so you just check the tables for that customer

>> the company that I'm at started out with a seperate database per customer
>> (not useing postgres), there are basicly zero cross-customer queries, with
>> a large volume of updates and lookups.
>>
>> overall things have now grown to millions of updates/day (some multiple of
>> this in lookups), and ~2000 customers, with tens of millions of rows
>> between them.
>>
>> having each one as a seperate database has really helped us over the years
>> as it's made it easy to scale (run 500 databases on each server instead of
>> 1000, performance just doubled)
> I think that for what you're doing, partitioning at the database level is
> probably a pretty good compromise solution. Like you say, it's easy to put
> busy databases on a new server to balance out the load. Hardware is cheap.
>
>> various people (not database experts) are pushing to install Oracle
>> cluster so that they can move all of these to one table with a customerID
>> column.
> Have these people identified a particular problem they're trying to solve, or
> is this a religious issue for them? From your description it sounds like a
> matter of dogma, not problem solving.

in part it is, in part it's becouse the commercial database companies have
told management that doing database replication is impossible with so many
databases (we first heard this back when we had 300 or so databases),
we've gone the expensive EMC disk-layer replication route, but they think
that mergeing everything will simplify things somehow so the database can
do it's job better.

I see it as just a limitation on the replication solution offered by the
bigname vendors.

>> the database folks won't comment much on this either way, but they don't
>> seem enthusiastic to combine all the data togeather.
> I think they can see the fecal matter heading towards the rotational cooling
> device on this one. I can't imagine this being a win from the perspective of
> saving the company money.

neither do I.

David Lang