A project I was working on wanted to do this, the basic idea was use Oracle (not my choice) as a source of truth and for OLTP and immediately blow any changes off to SOLR replicas for fulltext searching (for scalability/etc). Big-picture the idea was (imo, never set out directly) that they wanted clients to optimistically search on (and perhaps work on) the data from SOLR but "they also wanted the DB as an authoritative source".

We had it set up so that SOLR would basically run the queries, and return only the UUIDs of the database objects you wanted to return (plus of course any faceting data/etc). Then we would turn around and load the actual results from the query from the RDBMS - it's the source-of-truth and the data is freshest. Conveniently, the same way you generate the JSON for SOLR also works fantastic for generating the JSON for returning the data - this can be the same function so there will never be a mismatch between what's generated via DB-JSON and what's generated via ORM-JSON. It also means basically the entire backend layer can be pretty much generic - at the most basic level, you are just hammering off a query to SOLR and getting some UUIDs back, and then doing a batch-load from the DB, and getting some (uuid, record_version, json_body) tuples back, there is nothing "object-specific" in the backend object handler per-se, at least in the search/fetch code. Inserts/updates still need to work directly on the relational model, but all your search/retrieval code is completely generic (beyond like, table/view names). Furthermore - there is no "ORM collection spam" as it fires off individual queries to get collections for each row in the batch - since the object is already just a JSON string when it comes back, loading 250 rows will have (theoretically) exactly 250 results, there is no combinatorial blowout from joins, or hammering the DB with 250 additional queries (which perhaps fire off their own queries). One string per object, done.

I had an intelligent batch-loader built out with temporary tables/etc so it wasn't even "load 200 rows one at a time"... you inserted the rows you want to load, inner join your temporary table (which is still completely generic and nothing object-specific) and you get 200 results in a single query, fast as hell, without destroying your query planner with IN(:1, :2...) since every query has identical literal sqltext.

Instead of directly poking specific records into SOLR, the backend microservice would tell SOLR "there are new records to pull" and SOLR would do what they call a "delta-import" and actually go out to the database itself over JDBC and do an incremental import of all rows changed since the update. Since SOLR itself could ensure that only one import was running at the time, it would implicitly "batch" large sets of updates into a single query rather than hammering out a bunch of individual ones.

There's always tradeoffs though - the biggest one that concerned me is that since the DB is what's generating the JSON, you have essentially offloaded the entire string-building onto your DB engine. Yeah C/C++ string-building is a lot faster than Java, but you can't scale-out your database like you can a backend service or SOLR replica. Best case you can use microservices and split your database into a couple pieces, but that has problems too of course. It's possible that maybe Postgres has some read-replica type stuff that might be able to scale that out, but, that wasn't really going to be the issue probably. And if you move the JSON-ifying to an external source, you lose the nice performance benefits in terms of one-row-per-result, now you have the collection/combinatorial problems again.

(I wrote an intelligent object-caching mechanism as well, so it wouldn't rebuild every row every time... it'd only pull the rows that it didn't have in cache and would mix the cache+DB results to build the result list while minimizing DB load. So it's not all as bad as it sounds... ORM blowout overloads the database too and you fix that by caching too.)

One inherent problem to this design is desyncs - this isn't specific to my approach but just a problem of having a two-phase operation with things happening in the meantime. There are no exclusive locks on a SOLR while you're doing an update in the DB. I did add code to watch for this as much as possible and could accept it/log it, retry a couple times (will probably fail again on a hot table), try a full rebuild of the SOLR collection, etc, according to the specific situation. But while you can detect missing rows (in solr but not in DB) and stale rows (DB/solr have different versions) you could not ever detect the last kind... it's in DB but SOLR doesn't know about it. If SOLR can't search it for whatever reason (missing/stale/etc) and you never ask DB for it, then it doesn't exist.

As you note, it also means that instead of building Java models/entities you are now (also) building JSON models inside views, and our DBAs were green and already heavily overloaded, so this was not a good thing in terms of team scaling. It definitely pushed the workload into some weird places in that sense. In the long term what I'd have wanted to do is go further into the "ORM building exercise" here and automatically build matching view schemas from a document. A lot of these really map down to very similar Jackson-object-mapper or "hbm2ddl.auto" style code-generation tasks... complex to write the ORM and tedious to write your bindings but not actually all that difficult. A list<T> at the java level means you need a (whatever) JSON_LIST_AGG(TABLE_T, ...) element with that field name in the view, etc. Collections do suck in RDBMS, JSON lists of uuids or lists of populated (or partially populated) child elements make JDBC behave a lot better.

Another oddity is that now you basically have two (or more) "facades" onto the same database objects... and this messes with "default" caching behavior. After all, writes go into the database on the ORM relational entity, but they come out on the JSON-entity, and you need to tell the cache about that. It's not hard, but you have to write a custom adapter. Also, if you do a batch-insert (we of course used uuids generated in the application layer) or batch-update, you then have to do a batch-select immediately afterwards to retrieve the JSON-ified record, otherwise there's nothing to put into the cache or return to the client, since the application layer itself never has a JSON-serialization mapping (that all lives in the DB).

(In the long term the direction I was trying to point us was that rather than having JSON views just be a straightforward JSONization of a given table/object we could maintain different JSON-builder-views for different SOLR views servicing different types of services if needed... I kinda wonder how far you'll get on a single representation.)

That project was a dumpster fire and after I left I think they've pretty much thrown all this out. Easier to just have everything in DB (not even 21c - no native document support lol) be text and have the application layer do all the JSONifying.

But I'm still proud of turning some pretty nonsensical design requirements into a reasonably cohesive rube-goldberg machine. It's not what I'd have designed, but we had some, uh, overpromising and mismanagement involved, and it was a reasonable attempt at implementing the idea that was sold to the client imo. None of us had any experience in this stack and we were all trying to feel our way through it.

Ironically a bunch of this "full/incremental resync" stuff also ended up being a nice mechanism to bootstrap the SOLR cluster at startup (a full update instead of a delta) and for catching config errors. SOLR and the DB must stay in sync, so, SOLR importer is pointed at the wrong DB (according to the SOLR instance's JDBC URL)? That's a nope, application won't deploy, 503 Fix Your Shit.

(in hindsight I wonder if we'd gone postgres (I did argue for it, but that one was actually a legit understandable business decision...) if we could have done a FDW and gone with a "push" model by having Postgres publish its changes right to SOLR...)