Re: inheritance vs performance
От | Pascal Polleunus |
---|---|
Тема | Re: inheritance vs performance |
Дата | |
Msg-id | 402F3F90.9080909@beeznest.net обсуждение исходный текст |
Ответ на | inheritance vs performance (Pascal Polleunus <ppo@beeznest.net>) |
Список | pgsql-general |
Hi, Here's a deeper explanation about what I'm trying to achieve... > I'm wondering if there could be problems related to inheritance in the > following scenario (with PostgreSQL 7.4.1)... In fact, the main concern is not really about inheritance but more about how to handle large amounts of data. > 1 A-table, abstract. A-table contains the common columns for each type of customer. > Max 10 B-tables that inherit from A, with sometimes some more columns > than A. These are also abstracts. A B-table is created for each type of customer, some of them need more columns (currently only 3 types, but maybe some more that's why I said max 10. These 3 types have a different structure). > "n" C-tables that inherit from 1 B-table, without more columns. > Each C-table could contain quite a lot of rows (500K, 1M, ...). A C-table is created for each customer, that inherits from the B-table of their "customer type". I hope their will be some hundreds of customers/C-tables. My concern is that each C-table would contain around 500K records per year... and I hope more ;-) (1M or more was probably targeting too high) > Could there be problems, or performance issues, related to inheritance > if there is "too much" C-tables (in combination with the number of > rows)? And what would be that "too much"? Let's take for example 100 customers with 500K records per year. Grouping them together will lead to have, at the end of the year, 50M records in a single table. Isn't that too much? A solution could be to broken out the data per month and to keep only the current & previous months... 50M / 6 = 8.3M of records at the end of each month. Or to do the same weekly, so 50M / 26 = 1.9M records at the end of each week, for the last 2 weeks. With these data, I need to be able to: - generate daily, monthly and yearly reports per customer. - providing a list of records per customer for a given period (for a given day could be enough). Inheritance is maybe not really what is needed here. To handle table creation, I could store the structures somewhere instead of using inheritance. And to handle hypothetical structure changes, I could create bulk procedures. > Remarks: > A-table could be removed as it's not that important/relevant. > The purpose of this structure is not to be able to easily select through > the parent in all children tables, though it would be appreciated. I don't really need to do cross-customer queries, though that would be appreciable for generating global reports. Without inheritance, how to handle that will depend on the reports that need to be generated. > The purpose of this is just to be able to easily create C-tables, and > maybe also to easily handle structure changes of A or B-tables. Sorry, that was stupid :-/ What I wanted to say is that the purpose was mainly to distribute the amount of data between several tables. And, secondly, to easily handle structure changes. Thanks again for your advice, Pascal
В списке pgsql-general по дате отправления: