Sounds interesting and useful.
How likely is it for this product to be alive and well maintained for the next 5 years at least?
Show HN: A Schemaless Data Store Within Your SQL Database
31–40 of 47 posts
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#32Want schema-less SQL? Use an EAV (entity-attribute-value) schema.
Pretty good approach provided you don't want to query your data.
So right. It is VERY difficult to query a hand-rolled EAV structure. Kinda like editing a binary image with a text editor, lol.
Please check out some of the demo queries. Hope you think it's as slick as we do.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#33Are 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?
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 Al…
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.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#34Sounds interesting and useful. How likely is it for this product to be alive and well maintained for the next 5 years at least?
Great question.
We have shut down our consulting business and are all in on SFSQL. We will not be offering a FOSS solution. Our plans are to provide SFSQL via a subscription based self hosted model. Delivered initially as a docker container.
SAAS offerings would be provided through infrastructure provider partners. We would prefer not to be the SAAS provider of SFSQL. We have other business models that we plan to deploy but we don't want to get ahead of ourselves.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#35Sounds interesting and useful. How likely is it for this product to be alive and well maintained for the next 5 years at least?
Great question. We have shut down our consulting business and are all in on SFSQL. We will not be offering a FOSS solution. Our plans are to provide SFSQL via a subscription based self hosted model. Delivered initially as a docker container. SAAS offerings would be provided through infrastructure provider partners. We would prefer not to be the SAAS provider of SFSQL. We have other business models that we plan to dep…
I don't mean to be negative, but just two cents of feedback:
I believe (could be wrong) many developers would be worried about using this.
Seems to me you're a small shop (?). AWS can get away with a proprietary DB API like DynamoDB's. They are big and can provide strong assurances.
Since SFSQL is just starting out, by a small shop, it would be a high risk to rely on it being a closed source, proprietary API.
Might be worth thinking about how you're going to address such concerns. Must have something really strong to support.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#36Earlier quoted context omitted.
Great question. We have shut down our consulting business and are all in on SFSQL. We will not be offering a FOSS solution. Our plans are to provide SFSQL via a subscription based self hosted model. Delivered initially as a docker container. SAAS offerings would be provided through infrastructure provider partners. We would prefer not to be the SAAS provider of SFSQL. We have other business models that we plan to dep…
I don't mean to be negative, but just two cents of feedback: I believe (could be wrong) many developers would be worried about using this. Seems to me you're a small shop (?). AWS can get away with a proprietary DB API like DynamoDB's. They are big and can provide strong assurances. Since SFSQL is just starting out, by a small shop, it would be a high risk to rely on it being a closed source, proprietary API. Might b…
In a self hosted model developers would host SFSQL on their own infrastructure, which could be AWS, Google, Azure, DO, etc.. There is no reliance on us. Yes we are maintaining the API and underlying implementation. Software is a Service thus the subscription model.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#37Earlier quoted context omitted.
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 Al…
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…
I agree with getting some benchmarks up there. Until that's done, I can share what some preliminary tests revealed. Just to give you more of feel for where this tool might fit.
We imported the complete Clinical Trials dataset (380k docs ~ 200 attributes each) into SFSQL, Mongo and a Postgres JSON column.
Import speed with raw documents: Mongo and Postgres win hands down. Why? Very little processing to be done on their parts, while SFSQL unravels the structures and stores everything with indexes already in place. Excluded use cases for SFSQL? high-volume logging, data-sinks, etc.
Query speed against raw document elements: Mongo and Postgres very fast, SFSQL respectable. Why? Mongo is optimized for querying raw documents, Postgres obviously did their work as well. Excluded use cases for SFSQL? storage of raw, unprocessed json documents.
Then we extracted all unique instances of a particular attribute from that data and put them into their own collection in mongo and it's own table in Postgres. The number of distinct objects extracted to external collection/table was just 11. Then we modified the queries so that they JOINed to the external collection/table. Result: SFSQL still respectable (nothing changed internally or speed wise). Postgres and mongo displayed a huge slowdown (and this was just a single join). Included use cases for SFSQL? complex relational/referential data.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#38Earlier quoted context omitted.
Glad you asked about the JSON datatype. The Postgres JSON type is a great addition and certainly works well. Especially for situations where you have some common traits shared across something like 'products' (e.g. price), store those common traits in columns and then use the JSON type to store the uncommon columns (e.g. 'flavor'). Where SFSQL really helps is: - When new attributes are created that need indexing, you…
How do you protect against user input that produces a psychopathic volume of indexes?
That's a problem that standard SQL tables encounter - chasing new columns with new/modified indexes. (more info at our FAQ).
SFSQL never runs into that problem since the indexes are static in structure. No new indexes are made, even if every attribute name was unique.
The result is a very consistent query performance.
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#39This sounds like a lot of reinvention of the JSON/JSONB support that Postgres has already had since 9.2/9.4. They have a slide deck with a variety of in-depth examples. https://wiki.postgresql.org/images/7/7c/JSON-by-example_FOSD... CREATE INDEX review_review_jsonb ON reviews USING GIN (review_jsonb); -- Select data with JSON SELECT review_jsonb#>> '{product,title}' AS title , avg((review_jsonb#>> '{review,rating}'):…
MySQL and Oracle have had similar features for quite a while as well. I believe even sqlite added something like this recently, but I haven’t gotten around to trying it. Definitely curious what the advantages of this product are over the native capabilities. The page does describe the benefit of this solution indexing everything, but that sounds a little terrifying to me…
"terrifying" is understood.
However, the system is NOT indexing every single permutation of attributes possible. That would be impossible of course.
Please see the FAQ on indexing for more info: https://schemafreesql.com/faq.html#optimization-free
Re: Show HN: A Schemaless Data Store Within Your SQL Database
#40Is it similar to this: https://eng.uber.com/logging ?
Not at all. However, clickhouse is one of the underlying DBs on our list of test ports. We have been VERY curious how it would perform on
clickhouse. In fact, there are some particular features of SFSQL such as 2 stage deletes and the general structure of the tables that could
lend themselves to work well within clickhouse's mutations mechanism. We don't know yet, but if performance proves good on clickhouse then a whole set of
use-cases opens up.