# Implementing materialized views in Tryton
**URL:** https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562
**Category:** Feature
**Created:** [August 21, 2024, 5:36pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562 "2024-08-21T17:36:42Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [August 21, 2024, 5:36pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/1 "2024-08-21T17:36:42Z")
Hello everyone,
One of our customer is having performance issues when generating an extract from the accounting relying on the `account.account.party` Model which is using a `table_query` over hundred of millions of records.
I’ll cut the long story short but in the ends one of the origin of the slowness is the fact that we are repeating the query for each batch of 1000 records. So I started to think again about using the [materialized views](https://en.wikipedia.org/wiki/Materialized_view) in Tryton (which is a subject that we have already though about but without having a good enough solution).
But this time I think that we’ve finally reach a point where the idea and the implementation looks good enough.
### The idea
It’s really three different things coming together:
- A Mixin that people can add to an existing Model that will override the ` __register__ ` method in order to create the view from the `table_query`. The Mixin will also add a method to refresh the view.
- A wizard on each materialized view that will allow the user to trigger a refresh of the materialized view.
- A cron job that would loop over each of the materialized views and call the method to refresh it when the time has come. The time would be specified in a section of the configuration file of Tryton.
### The issues
One of the issue we had we the materialized views is that they would require to be kind of invalidated when the underlying data generating them is changed.
Our position on this has changed a little bit: first, we (at least I but @ced seems to go along 😃) consider that it might not be a big deal as long as the user knows that the data can be outdated. I mean when your company is dealing with million of invoices each year, you don’t really care if the statistics is off by 10 or 20 invoices because the invoices of today are not taken into account yet. Moreover there’s a way for the users to refresh the data (and thus hit hard your database with constant requests to compute a huge view 😉 ).
Moreover with the method to refresh the view, it will be possible for developers to trigger themselves the refresh of the data if really necessary by plugging it in the CUD calls (although I wonder why using the materialized view in this case but you might have a good reason).
One pending issue with this design is with the cron job, once the view is not populated with any record we can not know if it’s time to refresh the view or not. For now I’ve decided to skip updating the view.
Another pending issue is about updating the view when the `table_query` is modified (not only because a new model extends it but also because a new version might modify it). We could `DROP` and recreate the view on each ` __register__ ` call but it’s not satisfactory from my point of view.
### The code
So the code for this is available in this merge request:
> **[Allow to define materialized views (!1705) · Merge requests · Tryton / Tryton...](https://foss.heptapod.net/tryton/tryton/-/merge_requests/1705)**
>
> Business Software Modularity, scalability & security for your business Documentation Contribute
I would appreciate if anybody could share its idea about this (especially @albert as I know that you’re quite keen on everything postgres and I guess that it’s an issue that you’ve encountered quite some time with babi).
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 21, 2024, 6:01pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/2 "2024-08-21T18:01:28Z")
> [@nicoe](#):
>
> One pending issue with this design is with the cron job, once the view is not populated with any record we can not know if it’s time to refresh the view or not. For now I’ve decided to skip updating the view.
For me it must be updated otherwise it will never be bootstrapped as new materialized views are always empty.
> [@nicoe](#):
>
> Another pending issue is about updating the view when the `table_query` is modified (not only because a new model extends it but also because a new version might modify it). We could `DROP` and recreate the view on each ` __register__ ` call but it’s not satisfactory from my point of view.
One solution would be to compare the queries between the model and the view.
If it is not possible to get the query from the view, I think we could use the hash of the SQL query as the view name (a little bit like for the indexes) and so if the SQL query change, the name changes also.
---
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [August 22, 2024, 8:20am UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/3 "2024-08-22T08:20:37Z")
> [@ced](#):
>
> For me it must be updated otherwise it will never be bootstrapped as new materialized views are always empty.
Yes it’s the only correct thing to do but I was wondering if there was not another way.
> [@ced](#):
>
> One solution would be to compare the queries between the model and the view.
We can retrieve the query but it will be the verbatim of what was executed by the DB thus we would have to make the interpolation of the parameters ourselves and I don’t think psycopg can do that.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 22, 2024, 8:34am UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/4 "2024-08-22T08:34:11Z")
> [@nicoe](#):
>
> Yes it’s the only correct thing to do but I was wondering if there was not another way.
If the view is empty, this means that there are no data so the refresh will be fast. I think we can afford to refresh such view very frequently.
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [August 22, 2024, 11:24am UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/5 "2024-08-22T11:24:15Z")
> [@nicoe](#):
>
> I would appreciate if anybody could share its idea about this (especially @albert as I know that you’re quite keen on everything postgres and I guess that it’s an issue that you’ve encountered quite some time with babi).
The proposal looks great to me!
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [August 22, 2024, 11:47am UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/6 "2024-08-22T11:47:39Z")
One thing that I think the proposal lacks is a way for the user to know when was computed the data he’s exploring.
Otherwise there’s a lot of insecurity in many scenarios.
We had to add it in babi reports as part of the title of the tab when it is opened:
Given that your proposal makes it part of core, maybe that information could be obtained dynamically in some way.
It seems a minor detail, but it is not.
Also note, that if the refresh is relatively frequent it is also much more probable that the search() returns a set of records that it no longer exists when the client does the read() operation and things like that. Handling that gracefully would be a nice addition too.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 22, 2024, 11:53am UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/7 "2024-08-22T11:53:30Z")
> [@albert](#):
>
> One thing that I think the proposal lacks is a way for the user to know when was computed the data he’s exploring.
It is the create date of the record.
> [@albert](#):
>
> Also note, that if the refresh is relatively frequent it is also much more probable that the search() returns a set of records that it no longer exists when the client does the read() operation and things like that. Handling that gracefully would be a nice addition too.
The `id`s must be stable and this was already the case with dynamic table query so there is nothing new here.
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [August 22, 2024, 12:41pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/8 "2024-08-22T12:41:27Z")
> [@ced](#):
>
> It is the create date of the record.
Ok, but as long as the query fills the create\_date with NOW(). Anyway it would be nice to make it more visible.
> [@ced](#):
>
> The `id`s must be stable and this was already the case with dynamic table query so there is nothing new here.
You’re right.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 22, 2024, 12:53pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/9 "2024-08-22T12:53:04Z")
> [@albert](#):
>
> Ok, but as long as the query fills the create\_date with NOW().
We always use `CurrentTimestamp` but we may add a generic test for that.
> [@albert](#):
>
> Anyway it would be nice to make it more visible.
I do not think we should have any different behavior than the one for “standard” records.
For the user they are indistinguable.
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [August 22, 2024, 1:52pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/10 "2024-08-22T13:52:28Z")
> [@ced](#):
>
> I do not think we should have any different behavior than the one for “standard” records.
> For the user they are indistinguable.
I disagree.
This is a special case because we’re not showing the user the latest information. Everywhere in Tryton when the user opens a list of records that information is the latest there is. After 5 seconds they can reload and they’ll see what’s exactly there at that moment.
That is no longer true as soon as there’s a materialized view so we must be clear about that to the user.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 22, 2024, 1:57pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/11 "2024-08-22T13:57:33Z")
> [@albert](#):
>
> This is a special case because we’re not showing the user the latest information. Everywhere in Tryton when the user opens a list of records that information is the latest there is. After 5 seconds they can reload and they’ll see what’s exactly there at that moment.
You are thinking about existing users who already knows the system and have already assumptions about the behavior.
This does not apply to new users.
Old users must learn the new system after an upgrade.
For example Discourse as some dashboards and I do not expect them to show me the very updated information and they do not need to show me any kind of information to me to expect that. They just have a “Refresh” button on the detailed version.
---
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [August 22, 2024, 2:27pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/12 "2024-08-22T14:27:07Z")
> [@nicoe](#):
>
> I don’t think psycopg can do that.
In fact psycopg2 does the merge between the query and the parameters client-side (and I haven’t found out yet how to access this) but psycopg3 uses [server side binding](https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding). So sooner or later it won’t be possible anymore.
I tend to think that we should use the hash solution (with an additional switch on `trytond-admin` to drop the outdated views).
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 22, 2024, 2:30pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/13 "2024-08-22T14:30:56Z")
> [@nicoe](#):
>
> I tend to think that we should use the hash solution (with an additional switch on `trytond-admin` to drop the outdated views).
Indeed I do not think we should drop old views because most of them will be intermediary views that will be created again.
So I think we should always create the materialized view with no data so it does not consume any resources.
For the name of the view, I think the hash should be just appended to the standard name to make it _unique_.
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [August 22, 2024, 2:32pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/14 "2024-08-22T14:32:27Z")
> [@ced](#):
>
> For example Discourse as some dashboards and I do not expect them to show me the very updated information and they do not need to show me any kind of information to me to expect that. They just have a “Refresh” button on the detailed version.
How obvious is that Refresh button?
I think there must be a way for the user to know if that information needs to be refereshed or not.
---
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [August 22, 2024, 2:38pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/15 "2024-08-22T14:38:19Z")
> [@ced](#):
>
> Indeed I do not think we should drop old views because most of them will be intermediary views that will be created again.
This reasoning holds for the views that are extended by another module, but in the case where we add a new field to the same model in the same module it doesn’t hold anymore.
> [@ced](#):
>
> So I think we should always create the materialized view with no data so it does not consume any resources.
But then we need to refresh them once without the `CONCURRENTLY` keyword (it’s a requirement of postgres).
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 22, 2024, 2:47pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/16 "2024-08-22T14:47:42Z")
> [@nicoe](#):
>
> This reasoning holds for the views that are extended by another module, but in the case where we add a new field to the same model in the same module it doesn’t hold anymore.
Then the simplest way is to drop all the materialized views.
I guess it could be an extra option of the database update.
> [@nicoe](#):
>
> But then we need to refresh them once without the `CONCURRENTLY` keyword (it’s a requirement of postgres).
What about refresh all the _active_ materialized view created at the end of the database update without CONCURRENTLY?
---
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [August 22, 2024, 2:58pm UTC](https://discuss.tryton.org/t/implementing-materialized-views-in-tryton/7562/17 "2024-08-22T14:58:08Z")
> [@ced](#):
>
> What about refresh all the _active_ materialized view created at the end of the database update without CONCURRENTLY?
Yes this was my idea.