4 ms·
I've long wished for an RDBMS (or perhaps ORM, but I think it would be extra cool at the database level, see below) that does things that don't hyperscale. So
by jrumbut 4y ago
I've long wished for an RDBMS (or perhaps ORM, but I think it would be extra cool at the database level, see below) that does things that don't hyperscale.
So many LOB applications (the part of the iceberg under the water) have modestly sized databases with a relatively consistent number of users. The data is small, but often complex, awkwardly structured, highly relational, etc. The challenges these applications face are different, but real.
An example of something that would be incredibly useful here but completely untenable for a Facebook scale system would a rich rewind mechanism. You can go back in time 15 minutes or execute a query like:
RESTORE my_db TO LAST POINT WHERE EXISTS (SELECT * FROM ACCOUNTS WHERE ID=1234)
How much trouble would that kind of query have saved you in your career? At some point this would be cost prohibitive but I bet 50% or more of all apps never reach that point.
- salzig 4y agoCockroachDB has "AS OF SYSTEM TIME", which even allows you to backup the database for that moment. https://www.cockroachlabs.com/docs/stable/as-of-system-time.html https://www.cockroachlabs.com/docs/stable/as-of-system-time....
- remus 4y agoMS SQL Server has a fun feature called temporal tables that does some of this https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/relational-databases/ta...
- jrumbut 4y agoThis is very interesting! I haven't used this feature, is it possible to twist this to find a time associated with a table state so that you can then query the table at that time (allowing you to answer a question like what was the value of X the last time Y was true)?
- namibj 4y agoYou could replay the change feed from CRDB into Materialize and catch the timestamp (s) at which some query results pop out, using an incremental query in what is basically a linear search.
- mirages 4y agoIn Oracle you have the flashback tech SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE last_name = 'Chung';
- sammoorhouse 4y agoI think what you're looking for is called bitemporality (https://en.wikipedia.org/wiki/Bitemporal_modeling https://en.wikipedia.org/wiki/Bitemporal_modeling). It allows you to query a system (some databases support this) in a temporal context. This is useful in financial services where you have market data baked into a price you've offered to a client, where the underlying market data might have been later corrected by a vendor. So you can query how the world looked last tuesday 4pm; but also - given what we now know, how ought the world have looked last tuesday 4pm. Interesting stuff.
- fatnoah 4y ago> This is useful in financial services where you have market data baked into a price you've offered to a client, where the underlying market data might have been later corrected by a vendor. A long time ago, I worked on a such a system that provided service-based billing for an industry where rates were very fluid. Having this "at any point in time" view of key data was essential for many things.
- guiriduro 4y agoFor PostgreSQL with WAL logs, or in a service like RDS, can you not do a point in time recovery? Seems like an easy way to go back in time, recover the data you want, dig out any FK referents that might also have been cascade deleted etc. If you also maintained an audit log you should be able to isolate the point where the given data was deleted (if its id is in the audit log), and restore from just before it, to capture the deleted data, query it and reinsert in master.