Hi,

We’ve found a solution for this issue while working on PostgreSQL-level optimizations specifically for Odoo workloads, and we observed that foreign key validation was a major bottleneck during large delete operations.

In Odoo, many core models contain deep relational dependencies (`Many2one`, `One2many`, cascading deletes, computed relations, etc.). When deleting large datasets or uninstalling modules, PostgreSQL must repeatedly validate foreign key constraints and trigger cascade checks across multiple related tables.

From our benchmark testing across multiple PostgreSQL versions (15, 16, 17, and 18), we noticed that:

* Foreign key trigger validation time increases significantly with large relational datasets

* Missing or inefficient indexes on foreign key columns heavily impact deletion speed

* Cascade delete operations become slower as dependency depth and row count increase

* Module uninstallation in Odoo can become extremely slow when tables contain millions of related records

Based on these observations, we believe there are two important optimization areas:

1. Improving Foreign Key Validation Performance in PostgreSQL

We are currently exploring PostgreSQL source-level optimizations to reduce the overhead of FK trigger validation during delete and update operations.

Since Odoo relies heavily on relational integrity, optimizing FK checks could significantly improve:

* Bulk deletions

* Module uninstallations

* Cleanup operations

* Archiving workflows

* Large transactional updates

Our internal testing indicates that FK validation overhead becomes much more visible at multi-million row scale deployments.

2. Automatic Index Suggestion for Foreign Key Columns

Another major issue we observed is that many slow deletions are caused by missing indexes on foreign key columns.

Although PostgreSQL automatically indexes primary keys, foreign key columns themselves are not automatically indexed. In Odoo deployments with large relational datasets, this can cause expensive sequential scans during FK validation.

We are exploring an automatic index suggestion mechanism that could:

* Detect heavily-used FK columns

* Suggest or create optimized indexes for relational operations

* Improve delete/update validation speed

* Reduce cascade operation cost

* Improve overall ORM performance in Odoo

In many cases, proper FK indexing alone noticeably reduced deletion time during our tests.

We believe combining:

* FK validation optimization

* Better relational indexing strategies

* Odoo-specific workload tuning

could significantly improve PostgreSQL performance for large-scale Odoo environments.

It would be very interesting to know:

* At what table size users start seeing deletion slowdowns

* Which Odoo modules are most affected

* Whether the issue occurs mainly during module uninstallations, scheduled cleanups, or normal business operations

Regards

Yes, I've faced this exact issue. Slow deletions become a bottleneck past 50K records with heavy relational fields. Cascade deletes and FK constraint checks easily take 30+ seconds for just 10K records.

I see it regularly during:

• Module uninstalls (ir.model.data cascades)

• CRM campaign cleanups

• Stock move archiving

My current approach:

• Batch unlink: records[0:1000].unlink() via cron job (~80% faster)

• Raw SQL: DELETE FROM model WHERE id IN (...) + VACUUM ANALYZE

• Archive first: Set active=False, then purge later

This keeps things manageable up to 1M records with proper indexing.

Yes In So many Cases Like Delete Product (In Link With Sale Order,Invoice,Inventory,Picking etc) 

uninstalling a custom module after it has been used for months or years. it'getting long time to uninstall because of lot's of relation and business data.

Right now We are handling this way.

In general " DELETION " of data is not a recommended solution

Other alternatives :

CHeck the indexes  - implement  wherever you need

In case if you use any third party security apps

If it is .sh environment - you can consider  to " move the attachment to a shared location"  - So ODOO will have url o nly -when user click only document will load