In-Memory Caching Layer (H2) for Cross-Database Joins

[rrobetti]

Context

OJP already acts as a JDBC proxy between applications and one or more backends. I'd like to explore adding an in-memory H2 layer so that:

Frequently accessed data is cached in H2 (in-memory mode)
Cross-database joins (e.g., H2 cache ↔ PostgreSQL) can be expressed in a single SQL query
The app continues to see a single JDBC connection through OJP — no client-side changes

Proposed Approaches

#	Approach	Complexity	Scale
1	H2 Linked Tables — register PG as a linked table inside H2; joins run in H2	Low	Small/medium
2	Apache Calcite embedded in OJP — cost-based optimizer with pushdown to each source	High	Large
3	Pre-loaded H2 — sync PG → H2 ahead of time; all queries hit H2 only	Medium	Read-heavy, tolerable staleness
4	Hybrid — OJP intercepts queries, serves from H2 if cached, falls back to real DB; cross-DB joins via approach 1 or 2	Medium

Open Questions

Data size — what's the expected size of cached tables? (H2 in-memory handles a few GB comfortably; beyond that we'd need spill-to-disk or a different store.)
Placement — should the H2 instance live in the OJP server (shared, single-writer constraint) or be per-connection?
Freshness — is a few seconds/minutes of staleness acceptable, or do we need near-real-time?
Join patterns — are cross-DB joins a known/fixed set of table pairs, or ad-hoc?
Read-only vs. read-write — does the app only SELECT from the cache, or also INSERT/UPDATE?

[rrobetti]

It might be interesting exploring integrating with DuckDB as well, it might give better performance because its columnar indexing approach. Maybe we should abstract the engine and allow for more than one.