F1: A Distributed SQL Database That Scales • Hacker News Board

Re: F1: A Distributed SQL Database That Scales

#21

Post by fintler » Wed, Aug 28, 2013, 4:18 PM UTC

mad44 wrote: post #8

fintler wrote: post #7

I posted this question on StackOverflow regarding TrueTime (used by Spanner) a few days ago and haven't received any responses: " rel="nofollow">http://stackoverflow.com/questions/18384883/why-is-googles-t... However, I think this HN thread seems like it might be a good place to get comments on why my line of reasoning may be incorrect. Does anyone have any thoughts on why building something like Spanner on top of ba…

Check this out. http://muratbuffalo.blogspot.com/2013/08/beyond-truetime-usi...

Thanks for the link -- I'm working through it now.

Re: F1: A Distributed SQL Database That Scales

#22

Post by twic » Wed, Aug 28, 2013, 5:29 PM UTC

jacques_chester wrote: post #12

vosper wrote: post #9

Earlier quoted context omitted.

Your point about change history and its poor support in SQL is spot-on; at my employer we've gone through (1) and (2) and are trying to figure out (3) at the moment. From what I understand of Datomic temporal support is baked in, but it's about the only database I can think of that does that, and I haven't heard stories about it being used in production for large volumes of data.

Snodgrass wrote an entire book about dealing with the temporal blindness of SQL which might help you: http://www.cs.arizona.edu/people/rts/tdbbook.pdf More generally, you can usually build a model that handles (2), the changing state, by generalising your original model. I've done in both of classical ways, having either validity period fields (thus pushing complexity into every query), or by having audit tables (thu…

Databases which use MVCC handle (2) - and if they have transactional DDL, then they cover (3). But they don't keep the history permanently. Who wants all that old data?

The one exception to this that i know of is Oracle, which although not fashionable round here, does expose the MVCC log through "flashback queries", where one can simply write "SELECT * FROM USER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)":

http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_...

I don't know what the logistics of keeping an infinite history are. I do know that an e-commerce system i worked with before kept a few days, and that that saved our bacon on a couple of occasions.

Re: F1: A Distributed SQL Database That Scales

#23

Post by dragonwriter » Wed, Aug 28, 2013, 5:55 PM UTC

twic wrote: post #22

jacques_chester wrote: post #12

Earlier quoted context omitted.

Snodgrass wrote an entire book about dealing with the temporal blindness of SQL which might help you: http://www.cs.arizona.edu/people/rts/tdbbook.pdf More generally, you can usually build a model that handles (2), the changing state, by generalising your original model. I've done in both of classical ways, having either validity period fields (thus pushing complexity into every query), or by having audit tables (thu…

Databases which use MVCC handle (2) - and if they have transactional DDL, then they cover (3). But they don't keep the history permanently. Who wants all that old data? The one exception to this that i know of is Oracle, which although not fashionable round here, does expose the MVCC log through "flashback queries", where one can simply write "SELECT * FROM USER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)":…

PostgreSQL used to allow this via "Time Travel", but it has long since been removed (I think because it was rarely used and supporting it interfered with other optimizations.)

Re: F1: A Distributed SQL Database That Scales

#24

Post by jacques_chester » Wed, Aug 28, 2013, 11:39 PM UTC

twic wrote: post #22

jacques_chester wrote: post #12

Earlier quoted context omitted.

Snodgrass wrote an entire book about dealing with the temporal blindness of SQL which might help you: http://www.cs.arizona.edu/people/rts/tdbbook.pdf More generally, you can usually build a model that handles (2), the changing state, by generalising your original model. I've done in both of classical ways, having either validity period fields (thus pushing complexity into every query), or by having audit tables (thu…

Databases which use MVCC handle (2) - and if they have transactional DDL, then they cover (3). But they don't keep the history permanently. Who wants all that old data? The one exception to this that i know of is Oracle, which although not fashionable round here, does expose the MVCC log through "flashback queries", where one can simply write "SELECT * FROM USER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)":…

They have the technological underpinnings, but it's not properly exposed via SQL. In practice you wind up having to do it all by hand.

What the F1 guys seem to have done is said "well that's just silly, let's take advantage of it".

Oracle is my day job, I've been saved by a flashback query once. A DBA misunderstood a request I was making and applied a dev change to production.

> I don't know what the logistics of keeping an infinite history are.

I'd say they're not much different than asking a database developer to be able to generate reports for any given span of time. Or keeping a complete audit trail of changes. Or, more broadly, complete website logs, complete source revision history etc etc.

That is: in theory it creates an infinite downside. In practice it is more valuable than not, especially with the ever-plunging cost of storage.