How to Understand Why Partition Fails in Odoo Database

Partitioning is one of the most effective techniques for managing large postgres tables, improving maintenance operations, and keeping query performance consistent as data grows. Because Odoo databases often contain tables with millions of records, partitioning appears to be a practical solution for reducing the size of individual tables and simplifying long-term data management.

However, converting an Odoo model into a partitioned table is not as straightforward as it is in a standalone postgres database. Although the partitioned table can be created successfully and Odoo continues to read and write data without issues, unexpected problems appear during module upgrades.

These failures are caused by differences between postgresql's partitioning requirements and the assumptions made by Odoo's ORM and metadata validation.

So now let’s try to check whether the partition will work in Odoo.

Now we are taking the sale_order model for partition.

So before partition, understand the structure of the sale_order model.

odoo_17=# \d+ sale_order

Result :

                                                                                Table "public.sale_order"
          Column           |            Type             | Collation | Nullable |                Default                 | Storage  | Compression | Stats target |      Description       
---------------------------+-----------------------------+-----------+----------+----------------------------------------+----------+-------------+--------------+------------------------
 id                        | integer                     |           | not null | nextval('sale_order_id_seq'::regclass) | plain    |             |              | 
 campaign_id               | integer                     |           |          |                                        | plain    |             |              | Campaign
 source_id                 | integer                     |           |          |                                        | plain    |             |              | Source
 medium_id                 | integer                     |           |          |                                        | plain    |             |              | Medium
 company_id                | integer                     |           | not null |                                        | plain    |             |              | Company
 partner_id                | integer                     |           | not null |                                        | plain    |             |              | Customer
 journal_id                | integer                     |           |          |                                        | plain    |             |              | Invoicing Journal
 partner_invoice_id        | integer                     |           | not null |                                        | plain    |             |              | Invoice Address
 partner_shipping_id       | integer                     |           | not null |                                        | plain    |             |              | Delivery Address
 fiscal_position_id        | integer                     |           |          |                                        | plain    |             |              | Fiscal Position
 payment_term_id           | integer                     |           |          |                                        | plain    |             |              | Payment Terms
 pricelist_id              | integer                     |           |          |                                        | plain    |             |              | Pricelist
 currency_id               | integer                     |           |          |                                        | plain    |             |              | Currency
 user_id                   | integer                     |           |          |                                        | plain    |             |              | Salesperson
 team_id                   | integer                     |           |          |                                        | plain    |             |              | Sales Team
 analytic_account_id       | integer                     |           |          |                                        | plain    |             |              | Analytic Account
 create_uid                | integer                     |           |          |                                        | plain    |             |              | Created by
 write_uid                 | integer                     |           |          |                                        | plain    |             |              | Last Updated by
 access_token              | character varying           |           |          |                                        | extended |             |              | Security Token
 name                      | character varying           |           | not null |                                        | extended |             |              | Order Reference
 state                     | character varying           |           |          |                                        | extended |             |              | Status
 client_order_ref          | character varying           |           |          |                                        | extended |             |              | Customer Reference
 origin                    | character varying           |           |          |                                        | extended |             |              | Source Document
 reference                 | character varying           |           |          |                                        | extended |             |              | Payment Ref.
 signed_by                 | character varying           |           |          |                                        | extended |             |              | Signed By
 invoice_status            | character varying           |           |          |                                        | extended |             |              | Invoice Status
 validity_date             | date                        |           |          |                                        | plain    |             |              | Expiration
 note                      | text                        |           |          |                                        | extended |             |              | Terms and conditions
 currency_rate             | numeric                     |           |          |                                        | main     |             |              | Currency Rate
 amount_untaxed            | numeric                     |           |          |                                        | main     |             |              | Untaxed Amount
 amount_tax                | numeric                     |           |          |                                        | main     |             |              | Taxes
 amount_total              | numeric                     |           |          |                                        | main     |             |              | Total
 amount_to_invoice         | numeric                     |           |          |                                        | main     |             |              | Amount to invoice
 locked                    | boolean                     |           |          |                                        | plain    |             |              | Locked
 require_signature         | boolean                     |           |          |                                        | plain    |             |              | Online signature
 require_payment           | boolean                     |           |          |                                        | plain    |             |              | Online payment
 create_date               | timestamp without time zone |           |          |                                        | plain    |             |              | Creation Date
 commitment_date           | timestamp without time zone |           |          |                                        | plain    |             |              | Delivery Date
 date_order                | timestamp without time zone |           | not null |                                        | plain    |             |              | Order Date
 signed_on                 | timestamp without time zone |           |          |                                        | plain    |             |              | Signed On
 write_date                | timestamp without time zone |           |          |                                        | plain    |             |              | Last Updated on
 prepayment_percent        | double precision            |           |          |                                        | plain    |             |              | Prepayment percentage
 pending_email_template_id | integer                     |           |          |                                        | plain    |             |              | Pending Email Template
 sale_order_template_id    | integer                     |           |          |                                        | plain    |             |              |

Summary of the columns

These are the indexes of this sale_order table.

Indexes:

    "sale_order_pkey" PRIMARY KEY, btree (id)
    "sale_order__campaign_id_index" btree (campaign_id) WHERE campaign_id IS NOT NULL
    "sale_order__company_id_index" btree (company_id)
    "sale_order__create_date_index" btree (create_date)
    "sale_order__medium_id_index" btree (medium_id) WHERE medium_id IS NOT NULL
    "sale_order__name_index" gin (name gin_trgm_ops)
    "sale_order__partner_id_index" btree (partner_id)
    "sale_order__partner_invoice_id_index" btree (partner_invoice_id) WHERE partner_invoice_id IS NOT NULL
    "sale_order__partner_shipping_id_index" btree (partner_shipping_id) WHERE partner_shipping_id IS NOT NULL
    "sale_order__source_id_index" btree (source_id) WHERE source_id IS NOT NULL
    "sale_order__state_index" btree (state)
    "sale_order__team_id_index" btree (team_id)
    "sale_order__user_id_index" btree (user_id)
    "sale_order_date_order_id_idx" btree (date_order DESC, id DESC)
Check constraints:
    "sale_order_date_order_conditional_required" CHECK (state::text = 'sale'::text AND date_order IS NOT NULL OR state::text <> 'sale'::text)

The following is the list of outgoing foreign key column definitions related to this sale_order table.

Foreign-key constraints:

    "sale_order_analytic_account_id_fkey" FOREIGN KEY (analytic_account_id) REFERENCES account_analytic_account(id) ON DELETE SET NULL
    "sale_order_campaign_id_fkey" FOREIGN KEY (campaign_id) REFERENCES utm_campaign(id) ON DELETE SET NULL
    "sale_order_company_id_fkey" FOREIGN KEY (company_id) REFERENCES res_company(id) ON DELETE RESTRICT
    "sale_order_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
    "sale_order_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES res_currency(id) ON DELETE RESTRICT
    "sale_order_fiscal_position_id_fkey" FOREIGN KEY (fiscal_position_id) REFERENCES account_fiscal_position(id) ON DELETE SET NULL
    "sale_order_journal_id_fkey" FOREIGN KEY (journal_id) REFERENCES account_journal(id) ON DELETE SET NULL
    "sale_order_medium_id_fkey" FOREIGN KEY (medium_id) REFERENCES utm_medium(id) ON DELETE SET NULL
    "sale_order_partner_id_fkey" FOREIGN KEY (partner_id) REFERENCES res_partner(id) ON DELETE RESTRICT
    "sale_order_partner_invoice_id_fkey" FOREIGN KEY (partner_invoice_id) REFERENCES res_partner(id) ON DELETE RESTRICT
    "sale_order_partner_shipping_id_fkey" FOREIGN KEY (partner_shipping_id) REFERENCES res_partner(id) ON DELETE RESTRICT
    "sale_order_payment_term_id_fkey" FOREIGN KEY (payment_term_id) REFERENCES account_payment_term(id) ON DELETE SET NULL
    "sale_order_pending_email_template_id_fkey" FOREIGN KEY (pending_email_template_id) REFERENCES mail_template(id) ON DELETE SET NULL
    "sale_order_pricelist_id_fkey" FOREIGN KEY (pricelist_id) REFERENCES product_pricelist(id) ON DELETE SET NULL
    "sale_order_sale_order_template_id_fkey" FOREIGN KEY (sale_order_template_id) REFERENCES sale_order_template(id) ON DELETE SET NULL
    "sale_order_source_id_fkey" FOREIGN KEY (source_id) REFERENCES utm_source(id) ON DELETE SET NULL
    "sale_order_team_id_fkey" FOREIGN KEY (team_id) REFERENCES crm_team(id) ON DELETE SET NULL
    "sale_order_user_id_fkey" FOREIGN KEY (user_id) REFERENCES res_users(id) ON DELETE SET NULL
    "sale_order_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL

The below is the list of incoming foreign key column definitions related to this sale_order table.

Referenced by:

    TABLE "sale_advance_payment_inv_sale_order_rel" CONSTRAINT "sale_advance_payment_inv_sale_order_rel_sale_order_id_fkey" FOREIGN KEY (sale_order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_cancel" CONSTRAINT "sale_order_cancel_order_id_fkey" FOREIGN KEY (order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_discount" CONSTRAINT "sale_order_discount_sale_order_id_fkey" FOREIGN KEY (sale_order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_line" CONSTRAINT "sale_order_line_order_id_fkey" FOREIGN KEY (order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_mass_cancel_wizard_rel" CONSTRAINT "sale_order_mass_cancel_wizard_rel_sale_order_id_fkey" FOREIGN KEY (sale_order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_option" CONSTRAINT "sale_order_option_order_id_fkey" FOREIGN KEY (order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_tag_rel" CONSTRAINT "sale_order_tag_rel_order_id_fkey" FOREIGN KEY (order_id) REFERENCES sale_order(id) ON DELETE CASCADE
    TABLE "sale_order_transaction_rel" CONSTRAINT "sale_order_transaction_rel_sale_order_id_fkey" FOREIGN KEY (sale_order_id) REFERENCES sale_order(id) ON DELETE CASCADE

Now these are the metadata related to the not null constraints based on the sale_order table.

Not-null constraints:

    "sale_order_id_not_null" NOT NULL "id"
    "sale_order_company_id_not_null" NOT NULL "company_id"
    "sale_order_partner_id_not_null" NOT NULL "partner_id"
    "sale_order_partner_invoice_id_not_null" NOT NULL "partner_invoice_id"
    "sale_order_partner_shipping_id_not_null" NOT NULL "partner_shipping_id"
    "sale_order_name_not_null" NOT NULL "name"
    "sale_order_date_order_not_null" NOT NULL "date_order"

This is the default table access method of the postgres named heap.

Access method: heap

Now we are inserting some fake data into the sale_order table where the records will be created with create_date from the years starting from 2020 to 2025.

INSERT INTO sale_order (
    campaign_id,
    source_id,
    medium_id,
    company_id,
    partner_id,
    pending_email_template_id,
    journal_id,
    partner_invoice_id,
    partner_shipping_id,
    fiscal_position_id,
    payment_term_id,
    pricelist_id,
    currency_id,
    user_id,
    team_id,
    analytic_account_id,
    create_uid,
    write_uid,
    access_token,
    name,
    state,
    client_order_ref,
    origin,
    reference,
    signed_by,
    invoice_status,
    validity_date,
    note,
    currency_rate,
    amount_untaxed,
    amount_tax,
    amount_total,
    amount_to_invoice,
    locked,
    require_signature,
    require_payment,
    create_date,
    commitment_date,
    date_order,
    signed_on,
    write_date,
    prepayment_percent,
    sale_order_template_id
)
SELECT
    campaign_id,
    source_id,
    medium_id,
    company_id,
    partner_id,
    pending_email_template_id,
    journal_id,
    partner_invoice_id,
    partner_shipping_id,
    fiscal_position_id,
    payment_term_id,
    pricelist_id,
    currency_id,
    user_id,
    team_id,
    analytic_account_id,
    create_uid,
    write_uid,
    md5(random()::text),
    'SO' || to_char(nextval('sale_order_id_seq'), 'FM000000'),
    state,
    client_order_ref,
    origin,
    reference,
    signed_by,
    invoice_status,
    validity_date,
    note,
    currency_rate,
    amount_untaxed + random()*5000,
    amount_tax + random()*500,
    amount_total + random()*5500,
    amount_to_invoice + random()*5000,
    locked,
    require_signature,
    require_payment,
    d,
    d + interval '2 days',
    d,
    d + interval '1 day',
    d,
    prepayment_percent,
    sale_order_template_id
FROM sale_order s
CROSS JOIN generate_series(1,10000) g
CROSS JOIN LATERAL (
    SELECT timestamp '2020-01-01'
           + random() * (timestamp '2025-12-31' - timestamp '2020-01-01') AS d
) x
WHERE s.id = 1;

Now check the records count from the table named sale_order.

select count(*) from sale_order;

Result :

 count 
-------
 10002
(1 row)

Now we are doing the partition steps manually without using any partition tools like pg_partman.

Partition steps

1. Rename the existing table and free up its index names

ALTER TABLE sale_order RENAME TO sale_order_old;

Now rename the sale_order table’s constraint also.

ALTER TABLE sale_order_old RENAME CONSTRAINT sale_order_pkey TO sale_order_old_pkey;

Also rename the indexes related to the sale_order table like this.

ALTER INDEX sale_order__campaign_id_index          RENAME TO sale_order_old__campaign_id_index;
ALTER INDEX sale_order__company_id_index           RENAME TO sale_order_old__company_id_index;
ALTER INDEX sale_order__create_date_index          RENAME TO sale_order_old__create_date_index;
ALTER INDEX sale_order__medium_id_index            RENAME TO sale_order_old__medium_id_index;
ALTER INDEX sale_order__name_index                 RENAME TO sale_order_old__name_index;
ALTER INDEX sale_order__partner_id_index           RENAME TO sale_order_old__partner_id_index;
ALTER INDEX sale_order__partner_invoice_id_index   RENAME TO sale_order_old__partner_invoice_id_index;
ALTER INDEX sale_order__partner_shipping_id_index  RENAME TO sale_order_old__partner_shipping_id_index;
ALTER INDEX sale_order__source_id_index            RENAME TO sale_order_old__source_id_index;
ALTER INDEX sale_order__state_index                RENAME TO sale_order_old__state_index;
ALTER INDEX sale_order__team_id_index              RENAME TO sale_order_old__team_id_index;
ALTER INDEX sale_order__user_id_index              RENAME TO sale_order_old__user_id_index;
ALTER INDEX sale_order_date_order_id_idx           RENAME TO sale_order_old_date_order_id_idx;

2. Drop the incoming FKs. After the rename they point at sale_order_old, and they cannot be re-created against a partitioned sale_order (stock PG needs UNIQUE(id) alone; partitioned unique keys must include create_date). So we will replace it with triggers in the below step 8.

ALTER TABLE sale_advance_payment_inv_sale_order_rel DROP CONSTRAINT sale_advance_payment_inv_sale_order_rel_sale_order_id_fkey;
ALTER TABLE sale_order_cancel                       DROP CONSTRAINT sale_order_cancel_order_id_fkey;
ALTER TABLE sale_order_discount                     DROP CONSTRAINT sale_order_discount_sale_order_id_fkey;
ALTER TABLE sale_order_line                         DROP CONSTRAINT sale_order_line_order_id_fkey;
ALTER TABLE sale_order_mass_cancel_wizard_rel       DROP CONSTRAINT sale_order_mass_cancel_wizard_rel_sale_order_id_fkey;
ALTER TABLE sale_order_option                       DROP CONSTRAINT sale_order_option_order_id_fkey;
ALTER TABLE sale_order_tag_rel                      DROP CONSTRAINT sale_order_tag_rel_order_id_fkey;
ALTER TABLE sale_order_transaction_rel              DROP CONSTRAINT sale_order_transaction_rel_sale_order_id_fkey;

3. New partitioned parent with the same columns, defaults, NOT NULLs,CHECK constraint etc. Now Indexes are excluded here, and we will create them in step 6.

CREATE TABLE sale_order (LIKE sale_order_old INCLUDING ALL EXCLUDING INDEXES)
    PARTITION BY RANGE (create_date);

In postgres the partition key must be NOT NULL (it is part of the primary key).

So the odoo always writes create_date in UTC; the default only covers raw SQL inserts.

ALTER TABLE sale_order ALTER COLUMN create_date SET DEFAULT (now() AT TIME ZONE 'UTC');
ALTER TABLE sale_order ALTER COLUMN create_date SET NOT NULL;

The id sequence is owned by the old table; move it, or DROP TABLE sale_order_old would drop the sequence too.

ALTER SEQUENCE sale_order_id_seq OWNED BY sale_order.id;

4. Child partitions (one per year of create_date) + default.

CREATE TABLE sale_order_y2020 PARTITION OF sale_order FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE sale_order_y2021 PARTITION OF sale_order FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE sale_order_y2022 PARTITION OF sale_order FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE sale_order_y2023 PARTITION OF sale_order FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sale_order_y2024 PARTITION OF sale_order FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE sale_order_y2025 PARTITION OF sale_order FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sale_order_y2026 PARTITION OF sale_order FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE sale_order_y2027 PARTITION OF sale_order FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
CREATE TABLE sale_order_default PARTITION OF sale_order DEFAULT;

5. Copy the data from the old sale_order to new sale_order partitioned table like this.

INSERT INTO sale_order SELECT * FROM sale_order_old;

6. Primary key creation on parent partitioned table.

ALTER TABLE sale_order ADD CONSTRAINT sale_order_pkey PRIMARY KEY (id, create_date);

In Postgres there is a rule. If we partitioned a table and then create a unique constraint, then it will include the partitioned column also.

So that’s why during the primary key creation on the sale_order partitioned table includes the partitioned columns also.

Now create index on the sale_order parent partition will auto create the local indexes for all the child partitions.

CREATE INDEX sale_order__campaign_id_index         ON sale_order (campaign_id)         WHERE campaign_id IS NOT NULL;
CREATE INDEX sale_order__company_id_index          ON sale_order (company_id);
CREATE INDEX sale_order__create_date_index         ON sale_order (create_date);
CREATE INDEX sale_order__medium_id_index           ON sale_order (medium_id)           WHERE medium_id IS NOT NULL;
CREATE INDEX sale_order__name_index                ON sale_order USING gin (name gin_trgm_ops);
CREATE INDEX sale_order__partner_id_index          ON sale_order (partner_id);
CREATE INDEX sale_order__partner_invoice_id_index  ON sale_order (partner_invoice_id)  WHERE partner_invoice_id IS NOT NULL;
CREATE INDEX sale_order__partner_shipping_id_index ON sale_order (partner_shipping_id) WHERE partner_shipping_id IS NOT NULL;
CREATE INDEX sale_order__source_id_index           ON sale_order (source_id)           WHERE source_id IS NOT NULL;
CREATE INDEX sale_order__state_index               ON sale_order (state);
CREATE INDEX sale_order__team_id_index             ON sale_order (team_id);
CREATE INDEX sale_order__user_id_index             ON sale_order (user_id);
CREATE INDEX sale_order_date_order_id_idx          ON sale_order (date_order DESC, id DESC);

7. Outgoing foreign keys (same names, same ON DELETE actions).

ALTER TABLE sale_order
    ADD CONSTRAINT sale_order_analytic_account_id_fkey       FOREIGN KEY (analytic_account_id)       REFERENCES account_analytic_account(id) ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_campaign_id_fkey               FOREIGN KEY (campaign_id)               REFERENCES utm_campaign(id)             ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_company_id_fkey                FOREIGN KEY (company_id)                REFERENCES res_company(id)              ON DELETE RESTRICT,
    ADD CONSTRAINT sale_order_create_uid_fkey                FOREIGN KEY (create_uid)                REFERENCES res_users(id)                ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_currency_id_fkey               FOREIGN KEY (currency_id)               REFERENCES res_currency(id)             ON DELETE RESTRICT,
    ADD CONSTRAINT sale_order_fiscal_position_id_fkey        FOREIGN KEY (fiscal_position_id)        REFERENCES account_fiscal_position(id)  ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_journal_id_fkey                FOREIGN KEY (journal_id)                REFERENCES account_journal(id)          ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_medium_id_fkey                 FOREIGN KEY (medium_id)                 REFERENCES utm_medium(id)               ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_partner_id_fkey                FOREIGN KEY (partner_id)                REFERENCES res_partner(id)              ON DELETE RESTRICT,
    ADD CONSTRAINT sale_order_partner_invoice_id_fkey        FOREIGN KEY (partner_invoice_id)        REFERENCES res_partner(id)              ON DELETE RESTRICT,
    ADD CONSTRAINT sale_order_partner_shipping_id_fkey       FOREIGN KEY (partner_shipping_id)       REFERENCES res_partner(id)              ON DELETE RESTRICT,
    ADD CONSTRAINT sale_order_payment_term_id_fkey           FOREIGN KEY (payment_term_id)           REFERENCES account_payment_term(id)     ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_pending_email_template_id_fkey FOREIGN KEY (pending_email_template_id) REFERENCES mail_template(id)            ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_pricelist_id_fkey              FOREIGN KEY (pricelist_id)              REFERENCES product_pricelist(id)        ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_sale_order_template_id_fkey    FOREIGN KEY (sale_order_template_id)    REFERENCES sale_order_template(id)      ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_source_id_fkey                 FOREIGN KEY (source_id)                 REFERENCES utm_source(id)               ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_team_id_fkey                   FOREIGN KEY (team_id)                   REFERENCES crm_team(id)                 ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_user_id_fkey                   FOREIGN KEY (user_id)                   REFERENCES res_users(id)                ON DELETE SET NULL,
    ADD CONSTRAINT sale_order_write_uid_fkey                 FOREIGN KEY (write_uid)                 REFERENCES res_users(id)                ON DELETE SET NULL;

8. Trigger replacement for the 8 incoming FKs dropped in step 2.

A. Child side: the referenced sale_order id must exist.

TG_ARGV[0] = name of the column that points at sale_order.id

CREATE FUNCTION sale_order_ref_check() RETURNS trigger
LANGUAGE plpgsql AS
DECLARE
  vidinteger:=(tojsonb(NEW)->>TGARGV[0])::integer;
BEGIN
  IFvidISNULLTHEN
    RETURNNULL;
  ENDIF;
  IFTGOP='UPDATE'AND(tojsonb(OLD)->>TGARGV[0])::integer=vidTHEN
    RETURNNULL;
  ENDIF;
  --KEYSHAREblocksaconcurrentDELETEofthatorderuntilwecommit
  PERFORM1FROMsaleorderWHEREid=vidFORKEYSHARE;
  IFNOTFOUNDTHEN
    RAISEEXCEPTION'insertorupdateontable"%"violatesforeignkeyconstraint"%"',
            TGTABLENAME,TGNAME
       USINGERRCODE='foreignkeyviolation',
          DETAIL =format('Key(%s)=(%s)isnotpresentintable"saleorder".',
                   TGARGV[0],vid);
  ENDIF;
  RETURNNULL;
END;
;

This function checks that a row in a related table always points to a sales order that really exists. Before partitioning, PostgreSQL did this automatically through foreign keys. Once sale_order is partitioned, those foreign keys can't be created any more, so this function does the job instead. Whenever someone adds or changes a row in sale_order_line or one of the other 7 tables that point at sale_order, the function looks up the order. If the order exists, the save goes through. If it doesn't, the save is stopped with the same error message a foreign key would give. It also locks the order for a moment so that nobody can delete it while the row is being saved.

Now create this trigger function for the 8 tables where it has the incoming foreign keys to the sale_order table.

CREATE TRIGGER sale_advance_payment_inv_sale_order_rel_sale_order_id_fkey
    AFTER INSERT OR UPDATE OF sale_order_id ON sale_advance_payment_inv_sale_order_rel
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('sale_order_id');
CREATE TRIGGER sale_order_cancel_order_id_fkey
    AFTER INSERT OR UPDATE OF order_id ON sale_order_cancel
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('order_id');
CREATE TRIGGER sale_order_discount_sale_order_id_fkey
    AFTER INSERT OR UPDATE OF sale_order_id ON sale_order_discount
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('sale_order_id');
CREATE TRIGGER sale_order_line_order_id_fkey
    AFTER INSERT OR UPDATE OF order_id ON sale_order_line
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('order_id');
CREATE TRIGGER sale_order_mass_cancel_wizard_rel_sale_order_id_fkey
    AFTER INSERT OR UPDATE OF sale_order_id ON sale_order_mass_cancel_wizard_rel
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('sale_order_id');
CREATE TRIGGER sale_order_option_order_id_fkey
    AFTER INSERT OR UPDATE OF order_id ON sale_order_option
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('order_id');
CREATE TRIGGER sale_order_tag_rel_order_id_fkey
    AFTER INSERT OR UPDATE OF order_id ON sale_order_tag_rel
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('order_id');
CREATE TRIGGER sale_order_transaction_rel_sale_order_id_fkey
    AFTER INSERT OR UPDATE OF sale_order_id ON sale_order_transaction_rel
    FOR EACH ROW EXECUTE FUNCTION sale_order_ref_check('sale_order_id');

B. Parent side: ON DELETE CASCADE.

The function (sale_order_cascade_delete)

When sales orders are deleted, this function removes everything that belongs to them in the 8 related tables: order lines, optional products, tags, cancel and discount records, payment links, and wizard links. Before partitioning, postgres did this automatically through the ON DELETE CASCADE setting on the foreign keys. Those foreign keys can't exist on a partitioned sale_order, so the function does the clean-up instead. Without it, deleting an order would leave its lines and other records behind, pointing at an order that no longer exists.

CREATE FUNCTION sale_order_cascade_delete() RETURNS trigger
LANGUAGE plpgsql AS
BEGIN
  DELETEFROMsaleadvancepaymentinvsaleorderreltUSINGdeletedordersdWHEREt.saleorderid=d.id;
  DELETEFROMsaleordercancel            tUSINGdeletedordersdWHEREt.orderid   =d.id;
  DELETEFROMsaleorderdiscount           tUSINGdeletedordersdWHEREt.saleorderid=d.id;
  DELETEFROMsaleorderline             tUSINGdeletedordersdWHEREt.orderid   =d.id;
  DELETEFROMsaleordermasscancelwizardrel    tUSINGdeletedordersdWHEREt.saleorderid=d.id;
  DELETEFROMsaleorderoption            tUSINGdeletedordersdWHEREt.orderid   =d.id;
  DELETEFROMsaleordertagrel           tUSINGdeletedordersdWHEREt.orderid   =d.id;
  DELETEFROMsaleordertransactionrel       tUSINGdeletedordersdWHEREt.saleorderid=d.id;
  RETURNNULL;
END;
;

The trigger (sale_order_cascade_delete)

The trigger makes the function run automatically after every DELETE on sale_order. It collects all the orders deleted by that statement into a temporary list called deleted_orders, then runs the function once for the whole list. Deleting 1 order or 100 orders in one statement still runs the clean-up only once.

CREATE TRIGGER sale_order_cascade_delete
    AFTER DELETE ON sale_order
    REFERENCING OLD TABLE AS deleted_orders
    FOR EACH STATEMENT EXECUTE FUNCTION sale_order_cascade_delete();

Now the partitions steps are completed. After partition, you can run Odoo.

You can also create new records, and it will automatically redirect to the child partitions.

Now try to upgrade the sales module like this.

Now you get an error message in the ui.

This is because, after the partition, the primary key now includes two columns.

Indexes:

    "sale_order_pkey" PRIMARY KEY, btree (id, create_date)

This is the main issue. In Odoo source code, create_date is a magical field, and it is unique. During the Odoo module upgrade, the issue is that it will check the consistency of fields in the code and the database.

So now it is mismatched, because the ID is automatically created, and it is in code. But in database, the ID includes the partition column also.

This is why the issue arises.

def existing_tables(cr, tablenames):
    """ Return the names of existing tables among ``tablenames``. """
    cr.execute(SQL("""
        SELECT c.relname
          FROM pg_class c
          JOIN pg_namespace n ON (n.oid = c.relnamespace)
         WHERE c.relname IN %s
           AND c.relkind IN ('r', 'v', 'm')
           AND n.nspname = current_schema
    """, tuple(tablenames)))
    return [row[0] for row in cr.fetchall()]

This is from sql.py file of the Odoo source code.

This is a small code part where the consistency checking of all models is done during the module upgrade.

Here, the values of the relkind are r,v and m only. In postgres every partitioned table has the value of relkind is p.So odoo didnt included that value here.

Note : In Odoo 20, you cannot get this issue, because they didn't block the module upgrade what ever brokes internally based on the database.

Now check the structure of this sale_order partitioned table, you can see the child partitions like this.

Partitions:

   sale_order_y2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')
    sale_order_y2021 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2022-01-01 00:00:00')
    sale_order_y2022 FOR VALUES FROM ('2022-01-01 00:00:00') TO ('2023-01-01 00:00:00')
    sale_order_y2023 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00')
    sale_order_y2024 FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2025-01-01 00:00:00')
    sale_order_y2025 FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2026-01-01 00:00:00')
    sale_order_y2026 FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2027-01-01 00:00:00')
    sale_order_y2027 FOR VALUES FROM ('2027-01-01 00:00:00') TO ('2028-01-01 00:00:00')
    sale_order_default DEFAULT

Now check the record counts of each child partitioned table by using the query below.

SELECT
    inhrelid::regclass AS partition_name,
    pg_class.reltuples::bigint AS estimated_rows
FROM pg_inherits
JOIN pg_class
    ON pg_class.oid = inhrelid
WHERE inhparent = 'sale_order'::regclass
ORDER BY partition_name;

Result :

   partition_name   | estimated_rows 
--------------------+----------------
 sale_order_y2020   |           1000
 sale_order_y2021   |           2000
 sale_order_y2022   |           2000
 sale_order_y2023   |              0
 sale_order_y2024   |           1000
 sale_order_y2025   |           4000
 sale_order_y2026   |             20
 sale_order_y2027   |              0
 sale_order_default |              0
(9 rows)

Now the partition executes correctly. But it breaks Odoo data consistency during module upgrades.

This research shows that postgresql partitioning itself is not the source of the problem. The partitioned sale_order table operates correctly, data is distributed across child partitions, indexes are created, and normal database operations continue to work as expected.

The real challenge appears during Odoo module upgrades. The postgres requires a partitioned table's primary key to include the partition key, which changes the table definition from what Odoo expects. During its metadata validation process, Odoo compares the database structure with the model definition and detects this difference, causing the upgrade to fail.

This demonstrates that implementing partitioning for Odoo models requires more than database-level changes. It also requires compatibility between postgresql's partitioning rules and Odoo's schema validation logic. Understanding these internal differences is essential before introducing partitioning into production environments or designing long-term solutions for large Odoo databases.