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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
