Skip to Content
  2. Mailing Lists
  3. Contributors
  4. Postgresql Table partitioning in Odoo

Archives

Contributors

Prevent accidental emails to external addresses

Async import external employee data from db view to Odoo.

Postgresql Table partitioning in Odoo

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
by Jordi Ballester Alomar - 08:16 - 26 Feb 2021

Follow-Ups

  • Re: Postgresql Table partitioning in Odoo
    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
    by Jordi Ballester Alomar - 07:26 - 27 Feb 2021
  • Re: Postgresql Table partitioning in Odoo
    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
    by Jordi Ballester Alomar - 07:21 - 27 Feb 2021
  • Re: Postgresql Table partitioning in Odoo
    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

    by Graeme Gellatly - 03:11 - 27 Feb 2021
  • Re: Postgresql Table partitioning in Odoo
    Hello Jordi,

    In some cases Partial Indexes can do the trick.

    For example, you could have a partial index only for active records. Since most of the time there is filter condition on the active field, the smaller partial index can be used instead of a full index.

    This can give you the same benefits as a partitioned table, with a simpler solution (no need to push data around).

    More info:


    --dr 

    No dia 26/02/2021, às 19:17, Jordi Ballester Alomar <jordi.ballester@forgeflow.com> escreveu:

    
    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

    by Daniel Reis - 09:36 - 26 Feb 2021
  • Re: Postgresql Table partitioning in Odoo
    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  

    by Nhomar Hernández - 08:56 - 26 Feb 2021