[mdellavo]

Are you unrolling the nested JSON data structures and storing as traditional K/Vs in an EAV pattern? Possibly using one table for each datatype or using a sparse table?

I'd be curious how this performs for complex queries - does this rely heavily on index intersection?

[etirk]

Yes, an EAV pattern is being used. The end result in query speed with the particular table designs and indexes is essentially just like what index intersection gives you but without the setup. And of course EAV is not a pattern that you can easily roll out by hand when you need it.

Please try this demo which is a sample of some queries against Clinical Trials data. https://schemafreesql.com/demo.html#clinicalTrial Although this demo data set is a limited size, the same queries when run against the entire clinicalTrial dataset performed very respectably.

[mdellavo]

EAV is a pattern you can easily roll out - https://docs.sqlalchemy.org/en/14/orm/examples.html#module-e...

Another commenter illustrated some of the issues you will run into with this pattern at scale.

It would probably help to benchmark some complex queries on a sizable data set. And compare against mongo, postgres jsonb, vanilla eav, clickhouse, etc. Without much information to go on, it's hard to know what this is.

[dfragnito]

A more detailed response will be provided shortly but I do encourage you to launch a demo, https://schemafreesql.com/demo.html. You will be provided with an endpoint and access key to your own dedicated SFSQL service. No login required. You can start trying it out from within your own environment.