I totally agree with your high level point that there isn't a great SQL (OLTP, or otherwise) layer for FoundationDB. Building something like this would be very hard--but I don't think the FoundationDB storage engine itself would end up inflicting the limitations you mention if it was well executed. And FoundationDB was specifically designed for real-time workloads with mixed reads/writes (i.e. the OLTP case).
Whether or not concurrency is optimistic (or done with locks, or whatever) doesn't really have a bearing on things. Any database is going to suffer if it has a bunch of updates to a specific hot keys that needs to be isolated (in the ACID sense). As long as your reads and writes are sufficiently spread out you'll avoid lock contention/optimistic transaction retries.
You speak to the real main limitation of FoundationDB when you talk about stuff like schema changes. There is a five-second transaction limit which in practice means that you cannot, for example, do a single giant transaction to change every row in a table. This was definitely a deliberate deliberate design choice, but not one without tradeoffs. The bad side is that if you want to be able to do something like this (lockout clients while you migrate a table) you need a different design that uses another strategy, like indirection. The good side is that screwed-up transactions that lock big chunks of your DB for a long time don't take down your system.
I find that the people who are relatively new to databases tend to wish that the five second limit was gone because it makes things simpler to code. People that are running them in production tend to like it more because it avoids a slew of production issues.
That said, I think for many situations a timeout like 30 or 60 seconds (with a warning at 10) would be a better operating point rather than the default 5 second cliff.
I think that the SQL-on-top, and optimistic model, are definitely things that can have a workflow-dependent performance impact and are relevant.
All databases do suffer under some red line of write contention; but optimistic databases will suffer more, and will start degrading at a lower level of contention. “Avoiding contention” is database optimization table stakes, and you should be structuring every schema you can to do so; but hot keys are almost inevitable when a certain class of real-time product scales, and they will show up in ways you do not expect. When it happens, you’d like your DBMS to give as much runway as possible before you have to make the tough changes to break through.
SQL-on-top becomes an issue for geographic distribution; without “pushing down” predicates, read-modify-write workloads, table joins, etc. on the client can incur significant round-trip time issuing queries. I think the lack of this is always going to present a persistent disadvantage vs selecting a competitor.
And again, given FDBs multiple-full-secondary model, it’s only a problem when working in real time, slower queries can work off a local secondary. But latest-data-latency is relevant for many applications.
FWIW, I believe read transactions are unlimited in duration now that the Redwood engine has been available. But I haven't tested Redwood myself. Write transactions are still definitely limited to 5 seconds, though.
Whether or not concurrency is optimistic (or done with locks, or whatever) doesn't really have a bearing on things. Any database is going to suffer if it has a bunch of updates to a specific hot keys that needs to be isolated (in the ACID sense). As long as your reads and writes are sufficiently spread out you'll avoid lock contention/optimistic transaction retries.
You speak to the real main limitation of FoundationDB when you talk about stuff like schema changes. There is a five-second transaction limit which in practice means that you cannot, for example, do a single giant transaction to change every row in a table. This was definitely a deliberate deliberate design choice, but not one without tradeoffs. The bad side is that if you want to be able to do something like this (lockout clients while you migrate a table) you need a different design that uses another strategy, like indirection. The good side is that screwed-up transactions that lock big chunks of your DB for a long time don't take down your system.
I find that the people who are relatively new to databases tend to wish that the five second limit was gone because it makes things simpler to code. People that are running them in production tend to like it more because it avoids a slew of production issues.
That said, I think for many situations a timeout like 30 or 60 seconds (with a warning at 10) would be a better operating point rather than the default 5 second cliff.