1. Mailing Lists
  2. Contributors
  3. Re: Postgresql Table partitioning in Odoo

Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Re: Postgresql Table partitioning in Odoo

Avatar

Re: Postgresql Table partitioning in Odoo

by
ForgeFlow, S.L., Jordi Ballester Alomar
- 27/02/2021 07:21:11
Graeme,

I have considered for example to create materialized views for aggregated data in connection with the financial reports, MIS Builder, etc. The drawback is that it essentially requires you to introduce a lot of changes to the application logic in order to make use of those materialized views. At this stage partitioning seems to me like a better idea, because the change is (almost) limited to the database, and you can continue to use the application as it has been designed.

I'm not concerned (yet) by the 32 bits limitation of id's.

On Sat, Feb 27, 2021 at 3:12 AM Graeme Gellatly <gdgellatly@gmail.com> wrote:
Hi,

It's not really practical unfortunately. You can't just decide to use partitions. Well I suppose maybe you could rename the existing table, create a new one partitioned and then attach the renamed table as a partition if you were doing partition by date or id. I think a lot of the problem with aml in particular is the rows have gotten very wide as well.

Materialized views, partial indexes, clustering tables, even just good GIN indexing are all quite a lot simpler and can offer far better performance depending on need. Indeed I remember 8 or 9 years ago there was a popular module for account move lines which implemented what could best be described as homegrown matviews to store aggregate AML data. However, all these solutions are still constrained by Odoo 32 bit id's. So if by getting large you mean approaching the billions, then that's a new set of problems.

There is one set of solutions which I'm quite interested in for performance. That is using logical BDR to partition the write server of multi master databases. It seems this is doable without too much change but Odoo's id model doesn't meet the requirements, needing either GUID or BIGSERIAL. My idea is essentially to partition tables say geographically on some field, say company id, or create_uid and then run local Odoo instances in those geographies with their own pg cluster but achieving global consistency.

On Sat, Feb 27, 2021 at 8:17 AM Jordi Ballester Alomar <jordi.ballester@forgeflow.com> wrote:
Dear contributors,

We have a case of a database where some tables, like account.move.line are growing to absurd numbers of records. At this point we are considering using Postgresql Table partitioning to speed up performance. Has anyone of you had experience with table partitioning?

Looking forward to hearing your thoughts. 

Regards,
--
Jordi Ballester Alomar
CEO & Founder | ForgeFlow

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe



--
Jordi Ballester Alomar
CEO & Founder | ForgeFlow

Reference