Am 19.01.23 um 21:14 schrieb Jason Johns:
> the AGE function takes in two timestamps and returns an interval. You
> can do this in python by subtracting two date/datetime objects and
> getting a timedelta. what would the difference be to kick this out to
> the db?
I'd say thats mainly about performance - the DB can almost almost always
calculate things much more efficiently, eg. for AGE with table data:
- less datapoints to be moved to client side (should run ~2x faster
here, if the mangling of datetime and range type is equally expensive)
- Ideally the db has a fast impl for the function, thus the calculation
loop would also run faster than in python / on django side.
Since I have not benchmarked AGE this is all speculative (TM), but there
is a good chance to get at least a 2x faster processing.
Furthermore this is also about SQL realms vs. client roundtrips &
deduplication - a value calculated on python side cannot be used in SQL
directly anymore, you either need another python roundtrip to calc the
values and put it back to DB side or some data duplication on a table
(eww, now you've violated the normalization rules).
Still all that is not yet a good indicator, whether AGE should be
supported natively, as there is more to it from ORM perspective:
- Is AGE a vivid use case, does it help django users to get things done?
- Whats the testing/maintenance burden of AGE?
- How about other DB vendors?
Now to the downsides of AGE in postgres itself - it creates a "symbolic"
range string in years|months|days, rather than in postgres' standard
interval repr. This might create frictions at the interval to timedelta
translation in psycopg*, at least needs extensive testing for edge
cases, that might occur in that weird "symbolic" notation. That
uncertainty is a very bad startpoint for an ORM to get reliable
functionality and def. raises the test/maintenance needs just for that
AGE function.
An in-depth analysis of all these points might reveal, that its more
reliable to just use datetime substraction at SQL level. Which is
already easy doable and prolly also works with many other DB vendors.
Which again somewhat questions the idea for native postgres AGE support.
In summary - I am not against an ORM age thingy, still think that a more
general impl covering other vendors as well might be more helpful.
Cheers,
Jerch
[*] Again speculative (TM) - I know that Daniele Varrazzo does a really
great job in maintaining psycopg and it prolly will just work as
expected. It still creates a dependency on a less often used/tested code
path, so needs more caution on ORM side.