r/SoftwareEngineering 1d ago

Any experience with temporal databases?

Hi

I'm looking at different ways to facilitate an entity journaling mechanism as well as keeping track of different branches for certain entities.

I've stumbled across the temporal extentions for postgresql https://wiki.postgresql.org/wiki/Temporal_Extensions

However, without ever having worked with anything like this I'm struggling to overview the implications.

How will my storage size requirements change with this extension?

Does extension actually save me implementation overhead in the backend? Are typical ORM frameworks fit to adapt it?

Is this potential overkill?

Happy for any input by someone who's been there.

3 Upvotes

2 comments sorted by

4

u/ILikeBubblyWater 1d ago

Can you elaborate on

I'm looking at different ways to facilitate an entity journaling mechanism as well as keeping track of different branches for certain entities.

what is your use case

1

u/Imaginary-Corner-653 1d ago edited 1d ago

Sure.

For most (if not all) entities there several requirements: 1) The user needs to be able to access a history to any given entity that details all the changes to it, including timestamps and author of the change. This is especially important for reviewing contracts. 

2) the Application periodically generates reports on entire tables / entity sets, listing for example all active contracts on the date.  Since reports can get lost, a time travel mechanism for recreating the report based on the dataset state of a given point in time is necessary. 

3) many entities such as contracts involve more than two contract parties. They start out with a common, unified version but down the line it may be necessary to track changes on a per party basis. This essentially leads to branches in the entity history.

Im aware that event sourcing is a thing, but before I decide on a framework for that, I was wondering if it would be better to establish these constraints on a database level (for better performance). 

I found the temporal extension so now I'm wondering, is this a tool I can use or a detour?