Re: How many tables is too many tables?

<cydatamatt(at)gmail(dot)com> wrote
>
> One machine is simply not going to be able to scale with the quantities
> of links we hope to store information about and we want to move to some
> kind of cluster. Because of the quantities of data, it seems to make
> sense to go for a cluster setup such that in a 4 machine cluster, each
> machine has a quarter of the data (is this "Share nothing," or, "Share
> everything"?). To that end, we figured a good first step was to
> partition the data on one machine into multiple tables defining the
> logic which would find the appropriate table given a piece of data.
> Then, we assumed, adding the logic to find the appropriate machine and
> database in our cluster would only be an incremental upgrade.
>

So you set up 4 separate copies of PG in 4 machines? This is neither SN or
SE.

The partition is good for performance if you distribute IOs and CPUs. In
your design, I believe IO is distributed (to 4 machines), but since you
sliced data into too small pieces, you will get penality from other places.
For example, each table has to maintain separate indices (index becomes an
useless burden when table is too small), so there will be so many Btree root
... System tables (pg_class/pg_attribute, etc) has to contains many rows to
record your tables ... though we cached system table rows, but the memory
space is limited ...

In short, too many tables. To design your new partition method, jsut keep in
mind that database access data in a page-wise IO.

Regards,
Qingqing