Persisting pgh_diff for scalability

[maksjood]

In our usecase, where the number of events are in the order of millions, and we actually use some keys in the pgh_diff to filter querysets, we couldn't use the generated pgh_diff column in the CTE. We added triggers to persist it in database as a jsonfield and overrode the Events model to use the persisted column instead.

If you're interested, I can share what we did and we can discuss how can it be included in the project here.

[wesleykendall]

I recently found myself wishing I had the diff directly in a JSON in the history model.

We have some fields in the auto-generated history models that are opt-in. I'd certainly be open to supporting this as part of pghistory.

are you using NEW/OLD to compute the diff in a trigger? Any issues you ran into?

From the perspective of the library, I assume it would only store the diff of the fields you're tracking. I could see the alternative use case though

[93578237]

@maksjood Is there a particular reason to create a separate insert trigger on event table instead of refactoring insert event on target table?

[maksjood]

@q0w It's something I put on top of built-in functionalities of pghistory library. What we are desucssing here, is to integrate it into the library. We are open to suggestions.

[valentijnscholten]

@maksjood Can you create a PR or provide the gist again, it seems to be gone.

[9128305]

It's not gone: https://gist.github.com/maksjood/90c6f414d43dd2b2358635d2266e092b

[valentijnscholten]

ah, the first link from oct 9 has the wrong url "https://github.com/AmbitionEng/django-pghistory/discussions/url". Thanks.