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

Re: Postgresql Table partitioning in Odoo

Avatar

Re: Postgresql Table partitioning in Odoo

by
ForgeFlow, S.L., Jordi Ballester Alomar
- 27/02/2021 07:17:48
Nhomar,

In this case the account_move_line table is larger than 10 millions records. The company is using inventory accounting, and does a lot of inventory transactions. That does not help, as it creates a big number of inventory related AML's. I'd like to get rid of inventory accounting, of course! :)

We refactored the OCA financial reports completely, for example, and certainly this has added a lot of benefits.

What makes sense for me with partitioning a table, especially in accounting, is that you don't often make use of previous years move lines, except when you are computing an initial balance.

I tried to manually transform the account_move_line to a partitioned table with success. See https://github.com/odoo/odoo/pull/66964. I would need to test with a large database now to check if this really brings the expected benefits.

As Daniel Reis suggestested, using partial indexes seems interesting. However the Postgres recommends not to use them when partitioning really makes more sense. https://www.postgresql.org/docs/current/indexes-partial.html

"If your table is large enough that a single index really is a bad idea, you should look into using partitioning instead (see Section 5.11). With that mechanism, the system does understand that the tables and indexes are non-overlapping, so far better performance is possible."

Partitioning in odoo must be limited to using always the "id" column in the partitions, as the primary key must be contained in all partitions.



On Fri, Feb 26, 2021 at 8:57 PM Nhomar Hernández <nhomar@vauxoo.com> wrote:
How many records is for you **Absurd**.?

We try to fix odoo performance in the views or tools that are slow with records over between 1 and 10 millions.

But not always touch postgres will help.. Did you make the measurement?

El vie, 26 de feb. de 2021 a la(s) 13:17, Jordi Ballester Alomar (jordi.ballester@forgeflow.com) escribió:
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



--

Nhomar G Hernández

Vauxoo | CEO

¡Construyamos algo genial!
Cel: +52 (477) 393.3942 | Telegram: nhomar | Twitter: @nhomar

México · Venezuela · Costa Rica · Perú

phone nhomar@vauxoo.com phone vauxoo.com/contactus  

_______________________________________________
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