7 ms·

Foundation DB Record Layer SQL API

136 points by fidotron 1y ago

bognition 1y ago
I remember learning about FoundationDB a decade ago and being deeply impressed with what they built. Then it was acquired by Apple and went silent. Since then we've seen an explosion in new database storage layers. I'm curious is FoundationDB still the new hotness or has it been replaced by newer better technologies?
- fidotron 1y ago
  Foundation is fundamental to iCloud at Apple, and is _something_ at Snowflake, among a few others. Recently DeepSeek used it for https://github.com/deepseek-ai/3FS https://github.com/deepseek-ai/3FS "The Fire-Flyer File System (3FS) is a high-performance distributed file system designed to address the challenges of AI training and inference workloads." I don't think that there's anything else quite the same, partly because it has some real oddities that manifest because of things like the transaction time limits. At Apple they worked around some of this with https://www.foundationdb.org/files/QuiCK.pdf https://www.foundationdb.org/files/QuiCK.pdf
  - senderista 1y ago
    Snowflake uses FoundationDB for their metadata store and...something else which isn't public.
    - redwood 1y ago
      Hmm? A secret weapon to slow down Databricks?
    - parkerhiggins 1y ago
      ya here's the link explaining the metadata store: https://www.snowflake.com/en/blog/how-foundationdb-powers-snowflake-metadata-forward/ https://www.snowflake.com/en/blog/how-foundationdb-powers-sn...
  - frakkingcylons 1y ago
    Tigris (an object storage provider, I have no affiliation) also uses FoundationDB for storing metadata: https://www.tigrisdata.com/docs/concepts/architecture/#foundationdb https://www.tigrisdata.com/docs/concepts/architecture/#found...
- Dave_Rosenthal 1y ago
  FoundationDB's original promise was to combine a distributed storage engine with stateless layers on top to expose a variety of useful data structures (including SQL). The company was acquired before it could release the layers part of that equation. Apple open-sourced the core storage engine a few years later so FDB has kind of had a second life since then. In that second life, the vast majority of the powerful databases around the industry built on FoundationDB have been built by companies making their own custom layers that are not public. This release is cool because it's a rare case that a company that has built a non-trivial layer on top of FDB is letting that source code be seen. The group to which the FoundationDB storage engine itself appeals is fairly narrow--you have to want to go deep enough to build your own database/datastore, but not so deep to want to start from scratch. But, for this group, there is still nothing like FoundationDB in the industry--a distributed ACID KV store of extreme performance and robustness. So, yes, it's still the hotness in that sense. (As others have mentioned, see e.g. Deepseek's recent reveal of their 3FS distributed filesystem which relies on FDB.)
  - jbverschoor 1y ago
    AFAIK, the SQL layer was available and released
    - Dave_Rosenthal 1y ago
      There have been some 3rd party toy projects in the past years, but pretty sure nothing was ever released by FoundationDB/Apple relating to SQL (until this additional SQL interface on the Apple "record layer").
      - jbverschoor 1y ago
        2014: Beta launch of the SQL Layer: https://news.ycombinator.com/item?id=7593388 https://news.ycombinator.com/item?id=7593388 2014: Download SQL Layer: https://web.archive.org/web/20141209200002/https://foundationdb.com/layers/sql https://web.archive.org/web/20141209200002/https://foundatio... 2013: They talk about the SQL Layer: https://web.archive.org/web/20131220201001/https://foundationdb.com/our-solution#layer-level https://web.archive.org/web/20131220201001/https://foundatio... 2013: And someone can't start the SQL Layer on windows: https://web.archive.org/web/20131222063330/http://community.foundationdb.com/ https://web.archive.org/web/20131222063330/http://community....
- jwr 1y ago
  FoundationDB is pretty much the best distributed database out there, but it's more of a toolkit for building databases than a complete batteries-included database. I found that once I took the time to learn about FoundationDB and think about how to best integrate with it, the toolkit concept makes a lot of sense. Most people instinctively expect a database interface with a certain level of abstraction, and while that is nice to work with, it does not provide the advantages of a deeper integration. To take an example: FoundationDB itself has no indexing. It's a key-value store, but you get plenty of great tools for maintaining indices. That sounded strange to me, until I understood that now I can write my indexing functions in my app's language (Clojure in my case), using my model data. That is so much better than using a "database language" with a limited set of data types. Incidentally, I think that using SQL with FoundationDB is a waste, and I would not write a new app this way. Why would I want to talk to my database through a thin straw that mixes data with in-band commands? Since FoundationDB is hard to understand, there is (and will be) strong resistance to adoption. That's just how things are: we do not enjoy thinking too hard.
  - umvi 1y ago
    > Since FoundationDB is hard to understand, there is (and will be) strong resistance to adoption. That's just how things are: we do not enjoy thinking too hard. More like: we all have limited time, and if it's hard to understand you are asking for a big upfront time investment for a thing that may not even be the best fit for your use case. Anything can be made easier to understand with the right abstractions. The theory of relativity was super hard to understand when it was first developed; you basically had to be an elite physicist. But now non-physicists can understand it at a high level thanks to YouTubers like veritasium and minute physics. Maybe FoundationDB just needs better marketing. Also: your description of FoundationDB reminds me of ZeroMQ, which basically just dumps MQ legos at your feet and tells you to build your own MQ system (as opposed to a batteries included solution like RabbitMQ)
  - MarkMarine 1y ago
    Can we see some of your indexing code?
    - 1y ago
- jen20 1y ago
  It’s now been approximately 7 years since Apple open sourced FoundationDB. Note that it was closed source before the acquisition, which is often not appreciated.
- olavgg 1y ago
  There is a company delivering a data platform for the Industry 4.0 named Cognite based in Oslo, Norway that migrated from Google BigQuery to their own database on on top of FoundationDB. The video about it is available here: https://2023.javazone.no/program/85eae038-49b5-4f32-83c6-0779d252d0e9 https://2023.javazone.no/program/85eae038-49b5-4f32-83c6-077... After watching, my thoughts were; why didn't you just use Clickhouse?
- jbverschoor 1y ago
  I Thin that was more around 1.5 decades by now :). Yeah I was super enthusiastic about it. Seemed perfect. Back then I considered Riak, MongoDB, and things like Tokyo cabinet. FoundationDB to me is like Duke Nukem Forever. I don’t need it anymore. At least not for now
anhldbk 1y ago
At last Foundationdb has SQL Layer. AFAIK the initial discussion was in 2018 [1] [1] SQL layer in FoundationDB, https://forums.foundationdb.org/t/sql-layer-in-foundationdb/94 https://forums.foundationdb.org/t/sql-layer-in-foundationdb/...
gitroom 1y ago
been watching foundationdb for ages and it's kinda crazy it's still holding up while new stuff keeps dropping. always makes me wonder what it takes to keep something useful for so long
- jwr 1y ago
  The best technologies aren't always the most fashionable technologies — FoundationDB is very good, but it isn't flashy or fancy and the concepts that it addresses are hard to understand, so it isn't that popular. It also doesn't help that FoundationDB is really a toolkit (foundation), rather than a complete "here's my objects, store them" database.
  - lll-o-lll 1y ago
    With the “Record Layer” you get that “here’s my objects, store them”. Unfortunately, that layer is Java, so it’s not out of the box if that’s not your language. It’d be nice if they could provide an gRPC api for the Record Layer.
- parkerhiggins 1y ago
  here's some insight into the team's thought processes around the development and testing of FoundationDB. A few went on to form Antithesis: https://antithesis.com/company/backstory/ https://antithesis.com/company/backstory/
- nasretdinov 1y ago
  It really says something when the Aphyr guy doesn't even want to test FoundationDB because their testing suite is vastly superior and comprehensive: https://news.ycombinator.com/item?id=39358448 https://news.ycombinator.com/item?id=39358448
amazingamazing 1y ago
At some point someone will reimplement the dynamodb api on top of foundation db. That’ll be nice because then you have an effectively cheap hosted version available then.
computerfan494 1y ago
FoundationDB is very cool, but I wish it didn't require linking in their C library to talk to it. The client story is not good.
- jwr 1y ago
  Theoretically you could write your own client library, but this is nontrivial — this is a distributed database. The client library talks to multiple servers. It's not a "connect to a socket and send commands" type of client library, like in case of SQL servers.
  - computerfan494 1y ago
    The hard part is that there is no client spec you can follow as a third-party. Everything is implementation-defined. If you're out-of-tree, your code can break at any time. If the FoundationDB project committed to a protocol, client authors could write libraries outside of the main project.
  - jakemoshenko 1y ago
    Can you though? The protocol is not very well documented and it seems to iterate rather rapidly with the server version that it aims to be compatible with.
    - Dave_Rosenthal 1y ago
      You might be able to, but are definitely not supposed to. The client is conceptually "part of the cluster".
    - jwr 1y ago
      Are you planning to? This seems to be a theoretical discussion: I don't think I'd ever want to implement the client part of FoundationDB myself, and I don't really see a good reason to.
      - computerfan494 1y ago
tehlike 1y ago
I really really want nodejs bindings for foundationdb record layer. I tried using node java bridge, and it could be made to work but it'd be quiet an effort to maintain I guess...
- ToJans 1y ago
  Shouldn't be too hard. I built an Erlang/BeamVM driver/wrapper for it [1] before it got acquired by Apple... Their API is nice and clean. [1] https://github.com/happypancake/fdb-erlang https://github.com/happypancake/fdb-erlang
  - tehlike 1y ago
    Plain foundation db and document layer has bindings. It's the record layer that's a bit more complex with indexes, queries, etc.
rapsey 1y ago
If you are storing rows as records in FDB I am very skeptical about the performance. It seems to me it would be quite poor, because of the latency. You are talking to the network to get every row. I guess it would be scalable. You could execute lots of concurrent queries, but the actual performance of every non trivial query would be poor compared to a regular SQL DB.
- davgoldin 1y ago
  FDB seems to outperform, and out-scale most, if not all? According to an old report, FDB can do around 75k transactions per core [0]. MySQL on same CPU (all cores) can do about 8k [1]. [0] https://apple.github.io/foundationdb/performance.html https://apple.github.io/foundationdb/performance.html [1] https://www.anandtech.com/show/8357/exploring-the-low-end-and-micro-server-platforms/13 https://www.anandtech.com/show/8357/exploring-the-low-end-an...
  - rapsey 1y ago
    Which is why I said it probably scales well. But you can not equate that with per query performance. Accessing rows over the network is a few orders of magnitude slower than local NVME disk with big RAM caches.
  - mike_hearn 1y ago
    These sorts of numbers need very careful understanding and analysis, otherwise they can be misleading. It isn't comparing apples to oranges to measure the performance of FoundationDB transactions vs an RDBMS. A FoundationDB transaction is a vastly less powerful thing than an RDBMS transaction. In particular, an operation that would be one transaction in an RDBMS must often be split into several transactions in FoundationDB due to its timeout and size limits. An RDBMS allows a client to take thinking time for as long as it needs whilst working on a transaction. FoundationDB stores its undo logs in RAM rather than on disk. This lets them advertise great latencies but is the cause of the five second timeout. They actually advertise this as an "anti feature": https://apple.github.io/foundationdb/anti-features.html https://apple.github.io/foundationdb/anti-features.html And KV stores suffer the N+1 query problem once you go over the network: traversing links between objects/joins requires round-trips through the client unless you have a very smart record layer and only do simple traversals, which can immediately kill your latency. But in FoundationDB higher latency = smaller numbers of possible operations before your transaction expires and has to be retried from scratch. Thus you can get into a situation where the cluster slowing down due to network congestion can cause death spirals in which transactions start dying because they ran out of time, get retried by the client and this places more load on the cluster which then slows down even more, etc. Whereas in an RDBMS the database slowing down causes backpressure to the clients that is ultimately propagated to the user, avoiding this kind of problem. So... database benchmarks. Complex thing. Be careful out there! See my other comments on this thread for disclosures and more discussion of RDBMS vs FoundationDB.
pstuart 1y ago
There's an intriguing project which puts SQLite on top of FoundationDB that is quite intriguing, unfortunately the dev seems to have moved on from that effort: https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite
fabianlindfors 1y ago
Really cool although quite an undertaking to build an entire SQL engine! I have been working on something pretty similar but using Postgres instead. Basically extending Postgres to run stateless on top of FoundationDB, which would achieve the same thing but with all the Postgres features one would expect (and without some quirks you might not want, like vacuuming). Working with FoundationDB is a real pleasure as many people have noted already, a very intuitive abstraction to build databases on top of.
- mike_hearn 1y ago
  How do you plan to solve the N+1 query issue and the five second timeout?
  - fidotron 1y ago
    This is the interview question you would use to spot people that have actually used FDB vs just talked about it.
  - fabianlindfors 1y ago
    The five second timeout remains and trickles through to apply to Postgres transactions instead. This project would very much be for OLTP, so not really fit for using Postgres for OLAP or hybrid workloads. The N+1 issue is a really interesting one which I have a plan for but haven't implemented yet. FoundationDB has what they call mapped ranges [0] to help with this, which works in some cases. More generally, one should make sure to issue read request as soon as possible and not on-demand, given that FoundationDB clients have a futures-based design. This is slightly tricky in Postgres because internally it has a pull-based model where one tuple is pulled from the execution plan at a time, so one needs to implement pre-fetching and not make a read against FDB each time a new tuple is pulled. [0] https://github.com/apple/foundationdb/wiki/Everything-about-GetMappedRange https://github.com/apple/foundationdb/wiki/Everything-about-...
    - fabianlindfors 1y ago
      Neon encounters a similar problem and performs prefetching of pages before Postgres wants to read them: https://neon.tech/docs/extensions/neon#prefetch-option https://neon.tech/docs/extensions/neon#prefetch-option
mastabadtomm 1y ago
There is one more project that aims to build a MongoDB-like query engine and uses Redis wire protocol. It's Kronotop: https://github.com/kronotop/kronotop https://github.com/kronotop/kronotop Kronotop uses FoundationDB as a metadata store for document indexes and the other stuff. It stores the document bodies on the local disk and supports primary-follower replication. It also works as a RESP3/RESP2 proxy for FoundationDB API.
curtisszmania 1y ago
[dead]
yencabulator 1y ago
So how will this round improve on the previous design that was quite slow? > FDB-SQL was less than half as fast as MySQL on a single machine. https://www.voltactivedata.com/blog/2015/04/foundationdbs-lesson-fast-key-value-store-not-enough/ https://www.voltactivedata.com/blog/2015/04/foundationdbs-le...
yencabulator 1y ago
Java source at https://github.com/FoundationDB/fdb-record-layer https://github.com/FoundationDB/fdb-record-layer
conradev 1y ago
My favorite FoundationDB layer is per-user SQLite databases: https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite It's hard to tell if it's running in production, but the author works at Deno!
- tough 1y ago
  there was some discussion early on another thread about the one sqlite-db-per-vendor infra architecture can't remember maybe on duckdb one?

136 points by fidotron 1y ago

I remember learning about FoundationDB a decade ago and being deeply impressed with what they built. Then it was acquired by Apple and went silent. Since then we've seen an explosion in new database storage layers. I'm curious is FoundationDB still the new hotness or has it been replaced by newer better technologies?

fidotron 1y ago
Foundation is fundamental to iCloud at Apple, and is _something_ at Snowflake, among a few others. Recently DeepSeek used it for https://github.com/deepseek-ai/3FS https://github.com/deepseek-ai/3FS "The Fire-Flyer File System (3FS) is a high-performance distributed file system designed to address the challenges of AI training and inference workloads." I don't think that there's anything else quite the same, partly because it has some real oddities that manifest because of things like the transaction time limits. At Apple they worked around some of this with https://www.foundationdb.org/files/QuiCK.pdf https://www.foundationdb.org/files/QuiCK.pdf
- senderista 1y ago
  Snowflake uses FoundationDB for their metadata store and...something else which isn't public.
  - redwood 1y ago
    Hmm? A secret weapon to slow down Databricks?
  - parkerhiggins 1y ago
    ya here's the link explaining the metadata store: https://www.snowflake.com/en/blog/how-foundationdb-powers-snowflake-metadata-forward/ https://www.snowflake.com/en/blog/how-foundationdb-powers-sn...
- frakkingcylons 1y ago
  Tigris (an object storage provider, I have no affiliation) also uses FoundationDB for storing metadata: https://www.tigrisdata.com/docs/concepts/architecture/#foundationdb https://www.tigrisdata.com/docs/concepts/architecture/#found...
Dave_Rosenthal 1y ago
FoundationDB's original promise was to combine a distributed storage engine with stateless layers on top to expose a variety of useful data structures (including SQL). The company was acquired before it could release the layers part of that equation. Apple open-sourced the core storage engine a few years later so FDB has kind of had a second life since then. In that second life, the vast majority of the powerful databases around the industry built on FoundationDB have been built by companies making their own custom layers that are not public. This release is cool because it's a rare case that a company that has built a non-trivial layer on top of FDB is letting that source code be seen. The group to which the FoundationDB storage engine itself appeals is fairly narrow--you have to want to go deep enough to build your own database/datastore, but not so deep to want to start from scratch. But, for this group, there is still nothing like FoundationDB in the industry--a distributed ACID KV store of extreme performance and robustness. So, yes, it's still the hotness in that sense. (As others have mentioned, see e.g. Deepseek's recent reveal of their 3FS distributed filesystem which relies on FDB.)
- jbverschoor 1y ago
  AFAIK, the SQL layer was available and released
  - Dave_Rosenthal 1y ago
    There have been some 3rd party toy projects in the past years, but pretty sure nothing was ever released by FoundationDB/Apple relating to SQL (until this additional SQL interface on the Apple "record layer").
    - jbverschoor 1y ago
      2014: Beta launch of the SQL Layer: https://news.ycombinator.com/item?id=7593388 https://news.ycombinator.com/item?id=7593388 2014: Download SQL Layer: https://web.archive.org/web/20141209200002/https://foundationdb.com/layers/sql https://web.archive.org/web/20141209200002/https://foundatio... 2013: They talk about the SQL Layer: https://web.archive.org/web/20131220201001/https://foundationdb.com/our-solution#layer-level https://web.archive.org/web/20131220201001/https://foundatio... 2013: And someone can't start the SQL Layer on windows: https://web.archive.org/web/20131222063330/http://community.foundationdb.com/ https://web.archive.org/web/20131222063330/http://community....
jwr 1y ago
FoundationDB is pretty much the best distributed database out there, but it's more of a toolkit for building databases than a complete batteries-included database. I found that once I took the time to learn about FoundationDB and think about how to best integrate with it, the toolkit concept makes a lot of sense. Most people instinctively expect a database interface with a certain level of abstraction, and while that is nice to work with, it does not provide the advantages of a deeper integration. To take an example: FoundationDB itself has no indexing. It's a key-value store, but you get plenty of great tools for maintaining indices. That sounded strange to me, until I understood that now I can write my indexing functions in my app's language (Clojure in my case), using my model data. That is so much better than using a "database language" with a limited set of data types. Incidentally, I think that using SQL with FoundationDB is a waste, and I would not write a new app this way. Why would I want to talk to my database through a thin straw that mixes data with in-band commands? Since FoundationDB is hard to understand, there is (and will be) strong resistance to adoption. That's just how things are: we do not enjoy thinking too hard.
- umvi 1y ago
  > Since FoundationDB is hard to understand, there is (and will be) strong resistance to adoption. That's just how things are: we do not enjoy thinking too hard. More like: we all have limited time, and if it's hard to understand you are asking for a big upfront time investment for a thing that may not even be the best fit for your use case. Anything can be made easier to understand with the right abstractions. The theory of relativity was super hard to understand when it was first developed; you basically had to be an elite physicist. But now non-physicists can understand it at a high level thanks to YouTubers like veritasium and minute physics. Maybe FoundationDB just needs better marketing. Also: your description of FoundationDB reminds me of ZeroMQ, which basically just dumps MQ legos at your feet and tells you to build your own MQ system (as opposed to a batteries included solution like RabbitMQ)
- MarkMarine 1y ago
  Can we see some of your indexing code?
  - 1y ago
jen20 1y ago
It’s now been approximately 7 years since Apple open sourced FoundationDB. Note that it was closed source before the acquisition, which is often not appreciated.
olavgg 1y ago
There is a company delivering a data platform for the Industry 4.0 named Cognite based in Oslo, Norway that migrated from Google BigQuery to their own database on on top of FoundationDB. The video about it is available here: https://2023.javazone.no/program/85eae038-49b5-4f32-83c6-0779d252d0e9 https://2023.javazone.no/program/85eae038-49b5-4f32-83c6-077... After watching, my thoughts were; why didn't you just use Clickhouse?
jbverschoor 1y ago
I Thin that was more around 1.5 decades by now :). Yeah I was super enthusiastic about it. Seemed perfect. Back then I considered Riak, MongoDB, and things like Tokyo cabinet. FoundationDB to me is like Duke Nukem Forever. I don’t need it anymore. At least not for now

anhldbk 1y ago

At last Foundationdb has SQL Layer. AFAIK the initial discussion was in 2018 [1] [1] SQL layer in FoundationDB, https://forums.foundationdb.org/t/sql-layer-in-foundationdb/94 https://forums.foundationdb.org/t/sql-layer-in-foundationdb/...

gitroom 1y ago

been watching foundationdb for ages and it's kinda crazy it's still holding up while new stuff keeps dropping. always makes me wonder what it takes to keep something useful for so long

jwr 1y ago
The best technologies aren't always the most fashionable technologies — FoundationDB is very good, but it isn't flashy or fancy and the concepts that it addresses are hard to understand, so it isn't that popular. It also doesn't help that FoundationDB is really a toolkit (foundation), rather than a complete "here's my objects, store them" database.
- lll-o-lll 1y ago
  With the “Record Layer” you get that “here’s my objects, store them”. Unfortunately, that layer is Java, so it’s not out of the box if that’s not your language. It’d be nice if they could provide an gRPC api for the Record Layer.
parkerhiggins 1y ago
here's some insight into the team's thought processes around the development and testing of FoundationDB. A few went on to form Antithesis: https://antithesis.com/company/backstory/ https://antithesis.com/company/backstory/
nasretdinov 1y ago
It really says something when the Aphyr guy doesn't even want to test FoundationDB because their testing suite is vastly superior and comprehensive: https://news.ycombinator.com/item?id=39358448 https://news.ycombinator.com/item?id=39358448

amazingamazing 1y ago

At some point someone will reimplement the dynamodb api on top of foundation db. That’ll be nice because then you have an effectively cheap hosted version available then.

computerfan494 1y ago

FoundationDB is very cool, but I wish it didn't require linking in their C library to talk to it. The client story is not good.

jwr 1y ago
Theoretically you could write your own client library, but this is nontrivial — this is a distributed database. The client library talks to multiple servers. It's not a "connect to a socket and send commands" type of client library, like in case of SQL servers.
- computerfan494 1y ago
  The hard part is that there is no client spec you can follow as a third-party. Everything is implementation-defined. If you're out-of-tree, your code can break at any time. If the FoundationDB project committed to a protocol, client authors could write libraries outside of the main project.
- jakemoshenko 1y ago
  Can you though? The protocol is not very well documented and it seems to iterate rather rapidly with the server version that it aims to be compatible with.
  - Dave_Rosenthal 1y ago
    You might be able to, but are definitely not supposed to. The client is conceptually "part of the cluster".
  - jwr 1y ago
    Are you planning to? This seems to be a theoretical discussion: I don't think I'd ever want to implement the client part of FoundationDB myself, and I don't really see a good reason to.
    - computerfan494 1y ago

tehlike 1y ago

I really really want nodejs bindings for foundationdb record layer. I tried using node java bridge, and it could be made to work but it'd be quiet an effort to maintain I guess...

ToJans 1y ago
Shouldn't be too hard. I built an Erlang/BeamVM driver/wrapper for it [1] before it got acquired by Apple... Their API is nice and clean. [1] https://github.com/happypancake/fdb-erlang https://github.com/happypancake/fdb-erlang
- tehlike 1y ago
  Plain foundation db and document layer has bindings. It's the record layer that's a bit more complex with indexes, queries, etc.

rapsey 1y ago

If you are storing rows as records in FDB I am very skeptical about the performance. It seems to me it would be quite poor, because of the latency. You are talking to the network to get every row. I guess it would be scalable. You could execute lots of concurrent queries, but the actual performance of every non trivial query would be poor compared to a regular SQL DB.

davgoldin 1y ago
FDB seems to outperform, and out-scale most, if not all? According to an old report, FDB can do around 75k transactions per core [0]. MySQL on same CPU (all cores) can do about 8k [1]. [0] https://apple.github.io/foundationdb/performance.html https://apple.github.io/foundationdb/performance.html [1] https://www.anandtech.com/show/8357/exploring-the-low-end-and-micro-server-platforms/13 https://www.anandtech.com/show/8357/exploring-the-low-end-an...
- rapsey 1y ago
  Which is why I said it probably scales well. But you can not equate that with per query performance. Accessing rows over the network is a few orders of magnitude slower than local NVME disk with big RAM caches.
- mike_hearn 1y ago
  These sorts of numbers need very careful understanding and analysis, otherwise they can be misleading. It isn't comparing apples to oranges to measure the performance of FoundationDB transactions vs an RDBMS. A FoundationDB transaction is a vastly less powerful thing than an RDBMS transaction. In particular, an operation that would be one transaction in an RDBMS must often be split into several transactions in FoundationDB due to its timeout and size limits. An RDBMS allows a client to take thinking time for as long as it needs whilst working on a transaction. FoundationDB stores its undo logs in RAM rather than on disk. This lets them advertise great latencies but is the cause of the five second timeout. They actually advertise this as an "anti feature": https://apple.github.io/foundationdb/anti-features.html https://apple.github.io/foundationdb/anti-features.html And KV stores suffer the N+1 query problem once you go over the network: traversing links between objects/joins requires round-trips through the client unless you have a very smart record layer and only do simple traversals, which can immediately kill your latency. But in FoundationDB higher latency = smaller numbers of possible operations before your transaction expires and has to be retried from scratch. Thus you can get into a situation where the cluster slowing down due to network congestion can cause death spirals in which transactions start dying because they ran out of time, get retried by the client and this places more load on the cluster which then slows down even more, etc. Whereas in an RDBMS the database slowing down causes backpressure to the clients that is ultimately propagated to the user, avoiding this kind of problem. So... database benchmarks. Complex thing. Be careful out there! See my other comments on this thread for disclosures and more discussion of RDBMS vs FoundationDB.

pstuart 1y ago

There's an intriguing project which puts SQLite on top of FoundationDB that is quite intriguing, unfortunately the dev seems to have moved on from that effort: https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite

fabianlindfors 1y ago

Really cool although quite an undertaking to build an entire SQL engine! I have been working on something pretty similar but using Postgres instead. Basically extending Postgres to run stateless on top of FoundationDB, which would achieve the same thing but with all the Postgres features one would expect (and without some quirks you might not want, like vacuuming). Working with FoundationDB is a real pleasure as many people have noted already, a very intuitive abstraction to build databases on top of.

mike_hearn 1y ago
How do you plan to solve the N+1 query issue and the five second timeout?
- fidotron 1y ago
  This is the interview question you would use to spot people that have actually used FDB vs just talked about it.
- fabianlindfors 1y ago
  The five second timeout remains and trickles through to apply to Postgres transactions instead. This project would very much be for OLTP, so not really fit for using Postgres for OLAP or hybrid workloads. The N+1 issue is a really interesting one which I have a plan for but haven't implemented yet. FoundationDB has what they call mapped ranges [0] to help with this, which works in some cases. More generally, one should make sure to issue read request as soon as possible and not on-demand, given that FoundationDB clients have a futures-based design. This is slightly tricky in Postgres because internally it has a pull-based model where one tuple is pulled from the execution plan at a time, so one needs to implement pre-fetching and not make a read against FDB each time a new tuple is pulled. [0] https://github.com/apple/foundationdb/wiki/Everything-about-GetMappedRange https://github.com/apple/foundationdb/wiki/Everything-about-...
  - fabianlindfors 1y ago
    Neon encounters a similar problem and performs prefetching of pages before Postgres wants to read them: https://neon.tech/docs/extensions/neon#prefetch-option https://neon.tech/docs/extensions/neon#prefetch-option

mastabadtomm 1y ago

There is one more project that aims to build a MongoDB-like query engine and uses Redis wire protocol. It's Kronotop: https://github.com/kronotop/kronotop https://github.com/kronotop/kronotop Kronotop uses FoundationDB as a metadata store for document indexes and the other stuff. It stores the document bodies on the local disk and supports primary-follower replication. It also works as a RESP3/RESP2 proxy for FoundationDB API.

curtisszmania 1y ago

[dead]

yencabulator 1y ago

So how will this round improve on the previous design that was quite slow? > FDB-SQL was less than half as fast as MySQL on a single machine. https://www.voltactivedata.com/blog/2015/04/foundationdbs-lesson-fast-key-value-store-not-enough/ https://www.voltactivedata.com/blog/2015/04/foundationdbs-le...

yencabulator 1y ago

Java source at https://github.com/FoundationDB/fdb-record-layer https://github.com/FoundationDB/fdb-record-layer

conradev 1y ago

My favorite FoundationDB layer is per-user SQLite databases: https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite It's hard to tell if it's running in production, but the author works at Deno!

tough 1y ago
there was some discussion early on another thread about the one sqlite-db-per-vendor infra architecture can't remember maybe on duckdb one?