3 ms·

Expanding on the the above, > The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions al

by refset 3y ago

Expanding on the the above, > The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th...] Postgres deprecated support for time-travel in ~1997 and the more general notion of "system time" wasn't standardised until SQL:2011. This blog post is a good overview on the SQL:2011 spec + adoption in databases of (bi-)temporal versioning: https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ https://illuminatedcomputing.com/posts/2019/08/sql2011-surve... Temporal versioning is less sophisticated than git-like versioning (no branching etc.) but is usually more aligned with common end-user requirements. Kent Beck suggests this framing of "eventual business consistency": https://tidyfirst.substack.com/p/eventual-business-consistency https://tidyfirst.substack.com/p/eventual-business-consisten...

traceroute66 3y ago
> (bi-)temporal versioning I once spent some time trying to find a way to do bi-temporal versioning in Postgres. The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at. I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.
- refset 3y ago
  A shame indeed, they were only a few decades too early! The support for "Time Varying Data" gets discussed briefly in this 1995 paper by Stonebraker "The Design of Postgres" > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. > [...] Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version. https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf
  - galaxyLogic 3y ago
    Those are cool features, so why were they dis-continued?

by refset 3y ago

traceroute66 3y ago

> (bi-)temporal versioning I once spent some time trying to find a way to do bi-temporal versioning in Postgres. The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at. I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.

refset 3y ago
A shame indeed, they were only a few decades too early! The support for "Time Varying Data" gets discussed briefly in this 1995 paper by Stonebraker "The Design of Postgres" > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. > [...] Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version. https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf
- galaxyLogic 3y ago
  Those are cool features, so why were they dis-continued?