using external(cloud) key-value db as backing store

[Alex Ramos]

Hi All,

I'm investigating the possibility of using an external key-value store as the backing store for h2 db. Imagine something like Redis, memcache, or DynamoDB, as an alternative to the current two choices if in-memory vs filesystem storage. The end-goal is the ability to instantiate an "embedded" h2 that uses "dumb" (NoSQL) cloud storage to persist the data and index B-Trees, while retaining the full RDBMS SQL capability of the upper layers.

I've come down to a decision-point with three choices... 1) reimplement MVStore abstraction, 2) reimplement filesystem abstraction used by MVStore, or 3) implement pluggable tables, one layer up.

A question to those more familiar with the code-base... Which of the above three lines of research is most likely to yield fruitful results?

Thanks

[Andreas Reichel]

Greetings.

On Fri, 2021-04-23 at 21:43 -0700, Alex Ramos wrote:

The end-goal is the ability to instantiate an "embedded" h2 that uses "dumb" (NoSQL) cloud storage to persist the data and index B-Trees, while retaining the full RDBMS SQL capability of the upper layers.

Pardon my dumb question, I just want to learn something: Why would you want to do that? Why not:

a) mount the cloud storage per SSHFS and access the normal DB file 

b) or start the H2 server in the cloud and access it via TCP

What are the Pros of your ideas and what are the Cons of the "traditional" approach (despite having no buzzwords in the description).

Cheers

Andreas

[Alex Ramos]

Hi Andreas,

Thanks for asking. I realized I have some unstated goals. Here are my thoughts:

On Saturday, April 24, 2021 at 12:22:30 AM UTC-7 and...@manticore-projects.com wrote: 

a) mount the cloud storage per SSHFS and access the normal DB file 

This configuration would still retain an undesirable (to me) characteristic of a traditional RDBMS, mainly, that you have one node (running H2) that acts as a bottleneck for all data access.

Here I have an unstated requirement: I want the ability for some consumers to "backdoor" and read the data in the cloud backing store directly through native APIs without going through H2.

 

b) or start the H2 server in the cloud and access it via TCP

This approach suffers of the same limitation above and also one more: I don't want to run an additional server.  

 

What are the Pros of your ideas and what are the Cons of the "traditional" approach (despite having no buzzwords in the description).

Traditional approach: 

- I have some small legacy apps running on mysql. These apps already exist so it's not a matter of debate.

My ideas: 

- I don't want to rewrite the apps to a native cloud API. 

- I actually like SQL, and I love Java SQL APIs such as JDBC, JdbcTemplate, and Hibernate.. 

- But I don't like database servers.   

- So my idea is to move to embedded H2 with cloud backing store and get rid of the database servers, while keeping SQL, JDBC, JdbcTemplate, and Hibernate.

[Andreas Reichel]

Hi Alex,

thank you for the explanation.

On Sat, 2021-04-24 at 11:59 -0700, Alex Ramos wrote:

- But I don't like database servers.   

- So my idea is to move to embedded H2 with cloud backing store and get rid of the database servers, while keeping SQL, JDBC, JdbcTemplate, and Hibernate.

This is were you lose me (pardon me,  I am a Unix dinosaur): Would you not still need a single H2 "server" or service instance somewhere to dispatch messages between the client and the "cloud-storage"?

Beacause you stated:

This configuration would still retain an undesirable (to me) characteristic of a traditional RDBMS, mainly, that you have one node (running H2) that acts as a bottleneck for all data access.

Here I have an unstated requirement: I want the ability for some consumers to "backdoor" and read the data in the cloud backing store directly through native APIs without going through H2.

Pardon my ignorance, but to me this looks like contradicting requirements.

Either you will use "Embedded" mode not sharing, then you can use any file (wether local or mounted on a network does not matter).

Or you will use "Shared" mode and will rely on a kind of Service Dispatcher, which is usually the H2 SQL Server over a TCP connection.

Anyway, maybe I am just not understanding it well and  it is certainly not on me to evangelize you. 

Good luck and cheers

Andreas

[Alex Ramos]

On Saturday, April 24, 2021 at 11:56:07 PM UTC-7 and...@manticore-projects.com wrote:

Pardon my ignorance, but to me this looks like contradicting requirements.

Either you will use "Embedded" mode not sharing, then you can use any file (wether local or mounted on a network does not matter).

Or you will use "Shared" mode and will rely on a kind of Service Dispatcher, which is usually the H2 SQL Server over a TCP connection.

It's a hybrid mode:

- It's Embedded: The H2 engine is embedded (running in the same JVM as the client). So you get the benefit of not having an intermediate server. The H2 engine stores it's data (MVStore) in a cloud key-value store. This also is done without any server. You just pay a few cents for each GB of data stored.

- It's Shared: Another application can use the cloud key-value store APIs directly to retrieve data, without going through H2. This has "Shared" characteristics, but only the MVStore is shared, not the H2 engine. So it's sharing at a lower layer.

- Mounting a file on a network server is not the same, considering that the network server does not have an API that you can call directly to get access to the data. I don't think it's possible for two embedded H2's to share the same network filesystem. But that could solve the problem if it was possible.

 

[Noel Grandin]

On Sat, 24 Apr 2021 at 06:52, Alex Ramos <atr...@gmail.com> wrote:

I've come down to a decision-point with three choices... 1) reimplement MVStore abstraction, 2) reimplement filesystem abstraction used by MVStore, or 3) implement pluggable tables, one layer up.

MVStore is a "transactional key-value store", not merely key-value, so just bear that in mind.

Pluggable tables are probably the easiest.

Another option is CREATE LINKED TABLE if you have a JDBC driver for your cloud store.