3 ms·

Technically PostgreSQL already has this in terms of the WAL log. The problem is that "rolling back" to a given point of time requires recreating the database fr

by tensor 10y ago

Technically PostgreSQL already has this in terms of the WAL log. The problem is that "rolling back" to a given point of time requires recreating the database from the WAL log so it's not useful in the same way. There are other "remember everything" schemes you can play with clever triggers, but it always comes back to how you end up using the stored data and how easy it is to bring it back to a queryable state.

twic 10y ago
PostgreSQL also has it in the form of its MVCC table structure. If you never delete 'old' rows, you could query old states of the databases by ignoring rows with newer transaction IDs. However, nothing about pgSQL is designed for this approach, so i imagine the performance would be terrible.
- grzm 10y ago
  nothing about pgSQL is designed for this approach Interestingly enough, that was a part of the original design from the Berkeley days. http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf Our proposed approach is to treat the log as normal data managed by the DBMS which will simplify the recovery code and simultaneously provide support for access to the historical data. ... 3.3. Time Varying Data POSTQUEL allows users to save and query historical data and versions [KATZ85, WOOD83]. 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. One of the purposes of the much-maligned VACUUM command was to push the historical data to archival (optical) media. The archival store holds historical records, and the vacuum demon can ensure that ALL archival records are valid.
  - twic 10y ago
    TIL! > POSTQUEL allows users to save and query historical data and versions [KATZ85, WOOD83]. 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. Or, to put it another way [1]: "Since one can't change the past, this implies that the database accumulates facts, rather than updates places, and that while the past may be forgotten, it is immutable." &:) [1] https://www.infoq.com/articles/Datomic-Information-Model https://www.infoq.com/articles/Datomic-Information-Model
- atombender 10y ago
  One of Postgres' proudest features actually used to be this -- "time travel", which used MVCC + row time stamps to allow the client to query historical data. This was eventually removed, but the functionality can be emulated using a contrib extension called "timetravel" [1]. [1] https://www.postgresql.org/docs/current/static/contrib-spi.html https://www.postgresql.org/docs/current/static/contrib-spi.h...

by tensor 10y ago

twic 10y ago

PostgreSQL also has it in the form of its MVCC table structure. If you never delete 'old' rows, you could query old states of the databases by ignoring rows with newer transaction IDs. However, nothing about pgSQL is designed for this approach, so i imagine the performance would be terrible.

grzm 10y ago
nothing about pgSQL is designed for this approach Interestingly enough, that was a part of the original design from the Berkeley days. http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf Our proposed approach is to treat the log as normal data managed by the DBMS which will simplify the recovery code and simultaneously provide support for access to the historical data. ... 3.3. Time Varying Data POSTQUEL allows users to save and query historical data and versions [KATZ85, WOOD83]. 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. One of the purposes of the much-maligned VACUUM command was to push the historical data to archival (optical) media. The archival store holds historical records, and the vacuum demon can ensure that ALL archival records are valid.
- twic 10y ago
  TIL! > POSTQUEL allows users to save and query historical data and versions [KATZ85, WOOD83]. 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. Or, to put it another way [1]: "Since one can't change the past, this implies that the database accumulates facts, rather than updates places, and that while the past may be forgotten, it is immutable." &:) [1] https://www.infoq.com/articles/Datomic-Information-Model https://www.infoq.com/articles/Datomic-Information-Model
atombender 10y ago
One of Postgres' proudest features actually used to be this -- "time travel", which used MVCC + row time stamps to allow the client to query historical data. This was eventually removed, but the functionality can be emulated using a contrib extension called "timetravel" [1]. [1] https://www.postgresql.org/docs/current/static/contrib-spi.html https://www.postgresql.org/docs/current/static/contrib-spi.h...