[nathan_long]

This definitely sounds cool.

> your SQL database

They mention "a declarative query API", but as far as I can tell that's not actually SQL, right? So migrating from another relational db would require learning a new query language?

[rdsubhas]

One could technically write an SQL translation layer on top of it, as a client-side library? Or does it need support on the server-side / record layer?

[nschiefer]

(I'm from the iCloud team that works on the Record Layer.) Both building a relational database and implementing a proper SQL interface on top of it are huge projects. The SQL spec is large and complicated, so achieving true compatibility (as opposed to superficial compatibility) is challenging. Even worse, once you have a SQL interface users expect to be able to throw any SQL that they give to, say, Postgres, and have it work just as well, which requires a ton of detailed work on the query optimizer.

The client/server distinction isn't terribly strong in the FDB world. The FDB client is unusual in that it's a (stateless) part of the FDB cluster itself. You could therefore embed it in the client itself or build an RPC service around it. The Record Layer takes the same approach---it's just a Java library---so you could either embed it in the client application or build some kind of wire protocol for accessing it. One could have an embedded SQL layer like SQLite or H2 with no additional server beyond the cluster or a separate SQL layer network server that acted more like Postgres or MySQL.

The Record Layer was designed for use cases that don't need a SQL interface, so we focused on building the layer itself. That said, the Record Layer exposes a ton of extension points so there's a fluid boundary between what needs to live in its main codebase and what can be implemented on top. There are almost certainly enough extension points to implement a SQL interface as another layer on top of the Record Layer. For example, you could add totally new types of indexes outside of the Record Layer's codebase, if that were needed for SQL support. It's still a lot of work, especially on the query optimizer. Perhaps the community is up to that challenge. :-)

[grandinj]

Apache Ignite is also using H2 as their "SQL parser and query planner" layer

[ryanworl]

It would be tough to implement every SQL construct on top of what is there today. I could explain why (I have tried and presumably ran into the same issues they did described in various areas in the docs and the paper), but the docs give an authoritative answer to save me typing :)

"In the future it is possible that the Record Layer may develop a formal query language, but it is unlikely that such a language would closely resemble the SQL standard." [0]

[0] https://foundationdb.github.io/fdb-record-layer/FAQ.html

[dman]

Could you briefly describe the challenges you ran into?

[ryanworl]

* Schema evolution and schema management. Keeping schema state updated transactionally for a single, high-traffic table is complicated. An FDB core feature I requested could make this easier. Basically managing the lifecycle of adding, building, using, and dropping indexes is the hard part.

* Full table scans on large tables in a R/W transaction would fail due to the 5s transaction duration restriction. This is obviously a bad idea regardless of database, but if you wanted to support SQL you would have to allow it

* Sorting and joins require (theoretically) the same amount of memory as your data size, or the ability to spill to disk. This isn't FDB specific, but offering this kind of feature in a scale-out, high-concurrency model would be tough for QoS.

There are plenty more, but those are the big ones I ran across during design. The actual SQL interface part is not hard, but you would have to disallow many useful constructs people normally expect to work.