Odoo API performance: the power of PostgreSQL
Why talk about Odoo API performance? Along these past years, we have witnessed an increasing number of requirements from customers to open their Odoo environments to dedicated and third-party systems by using the Odoo API system and XML-RPC Odoo APi interface.
When contemplating the Odoo performance of the API, one quickly realizes that it cannot handle large flows of data or intensive activity. For example, even on the latest version of Odoo 12 Enterprise, our tests reveal that creating more two Sales orders per Second in a Standard Sales order with no customization is already difficult. This is mostly due to the fact that the POST transactions pushed to Odoo must still be validated at the Odoo level by the ORM mechanisms. Same issues happen with large data data imports.
I saw many projects ending up bad because the Odoo developers never actually assessed the needs in terms of volume and speed necessary from the beginning. Yet, the clients know from the beginning how many sales orders, picking lists, warehouse moves and invoices they will generate.
This year, our teams have been delivering one dedicated High Availability architecture system for a big player in the “transactional world” of the Indonesian market. Initial performance and load requirements were the following:
- Up to 1500 inserts (POST requests) per second.
- Up to 10 000 downloads (GET requests) per second.
Now, with this sort of load, there is absolutely NO WAY the standard Odoo modules using standard XML-RPC Odoo API could cope. Rather, we aimed at the following technologies:
- Daemonized API service (external to Odoo) running in multi-threading mode
- Compliance of the API engine with load-balancing mechanisms (which offered us an almost infinite growth potential in terms of capacity)
- SQL embedded authentication and authorizations.
- SQL data types and ORM-like discrimination.
- Dedicated C+ PostgreSQL libraries.
- Haskell shell and binary executable.
Thanks to the above stack, we improved performance at the following levels:
- Authentication / Authorization take place at the low level instead of pulling access rights from the ORM.
- Data validation is real-time without going to ORM
- Security improvement since the XML-RPC connector of the ERP back-end is not open to public access anymore.
Execution time of 1500 Sales orders goes to approximately 3 seconds on a simple architecture and melts down to 200 ms when split over multiple cores and load-balanced instances.
Suddenly, new perspectives open for large transactional data!