Contributors mailing list archives
Re: multi company: how many companies is too many?by
Open for Small Business, Graeme Gellatly
Record rules make little difference, neither does properties in my experience. The one that can get you if you have a lot of products or customers is cost / pricelist / payment term lookups that table can get slow but a set of partial index resolves those (take care of the NULL case in each one). But you see that issue with as few as 1 company, just obviously the problem multiplies.
What can make a difference is postgres performance due to query execution plans on weirdly shaped queries due to the distribution of company_ids in what are naturally much larger tables. You can get some truly awful query planning. However again it is really just a subset of the large data issues.
In general the standard mitigations, if you have 1 large company and lots of little ones you might do a partial on the large and a partial on everything but the large. You may have to build a lot of composites with company_id as well (I'm not sure postgres optimizes the NOT NULL attribute when ORM searches for NULL companies even when required - such a simple optimization they could do).
So I guess unlike Pedro, I see issues at 10 companies, but wildly different data distributions and the largest table at 50,000,000 lines. Although usually the worst issues are reserved for enterprise reports which actually access relatively small tables, say 5m records or less, like reconcile tables, but they are date and company dependent and in big joins.
On Tue, May 10, 2022 at 9:52 PM Pedro M. Baeza (Tecnativa) <email@example.com> wrote:
We have one with 89 without a performance penalty. Maybe the problem is some specific unoptimized rule/s?Regards.