Re: select count() out of memory

Gregory Stark wrote:
> Tom's point is that if you have 55k tables then just *finding* the newest
> child table is fairly expensive. You're accessing a not insignificant-sized
> index and table of tables. And the situation is worse when you consider the
> number of columns all those tables have, all the indexes those tables have,
> all the column keys those indexes the tables have have, etc.

Yes, I got that. But I name the child tables so that I when my server
receives read requests, I retreive details from the request to be able
to figure out the exact child table name, without the system needing to
do any internal searches to find the newest table.

> Nonetheless you've more or less convinced me that you're not completely nuts.

thank you for only regarding me as somewhat nuts :)

> I would suggest not bothering with inheritance though. Inheritance imposes
> additional costs to track the inheritance relationships. For your purposes you
> may as well just create separate tables and not bother trying to use
> inheritance.

As addressed in a previous reply, I find inheritance better for a couple
of practical reasons.

>> If its practical to use partitions, granularity does not come into the
>> equation.
>
> Uhm, yes it does. This is engineering, it's all about trade-offs. Having 55k
> tables will have costs and benefits. I think it's a bit early to dismiss the
> costs. Keep in mind that profiling them may be a bit tricky since they occur
> during planning and DDL that you haven't finished experimenting with yet. The
> problem you just ran into is just an example of the kind of costs it imposes.

See answer on why granularity is not relevant for my case.

> You should also consider some form of compromise with separate tables but at a
> lower level of granularity. Perhaps one partition per day instead of one per
> 30s. you could drop a partition when all the keys in it are marked as dead.

The structure of the data is divided in a descrete timeline, so every
predefined x seconds a whole new bunch of data arrives, and all that
belongs in a single partition.


regards

thomas