Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: A Schemaless Data Store Within Your SQL Database

schemafreesql.com

Post reply on HN
Print view View original

11–20 of 47 posts

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#11

Post by time0ut »

crooked-v wrote: post #4

This 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…

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#12

Post by [deleted] »

[deleted] wrote: post #6
crooked-v wrote: post #4

This 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}'):…

[deleted]

[deleted]

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#13

Post by dfragnito »

mdellavo wrote: post #7

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?

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.

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#16

Post by singron »

etirk wrote: post #8
brad0 wrote: post #3

Interesting project! I'm curious about how this compares to other tech: - How is data stored internally? - What are the tradeoffs with this implementation? - Do you generate indexes on data as it comes in? - How are nested keys handled? - Are indexes eventually consistent?

Thanks! - How is data stored internally? During our beta we will be hosting the databases and the underlying table structures are not viewable. However, on release, you will have full access to inspect the tables and indexes as they will be created within your own database. - What are the tradeoffs with this implementation? Depends on what you compare it to, but let's assume we are looking at the tradeoffs of SFSQL v…

It sounds like you have single-column indexes on every column and then would rely on an intersecting query plan (e.g. bitmap heap scan) if you query multiple columns.

This isn't just slightly slower, it depends a lot on your data how slow this is. It's potentially linear in the length of single-column matches. E.g. a query plan for (region='us' AND product='1234' AND year='2021') could scan the region index for all 'us' tuples, the product index for all '1234' tuples, and the year index for all '2021' tuples. The result might only be 0.0001% of rows, but this might have to scan more index entries than you have rows in the database. In some cases it's probably better to use one index and apply the remaining column filters as you fetch rows from the heap.

If you don't care about performance or only have a few MB of data then maybe you will get away with it, but I've never worked on a project where database performance wasn't an issue.

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#18

Post by etirk »

crooked-v wrote: post #4

This 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}'):…

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 still have to be aware of those new columns before you can index them. An app might let users create their own attributes on the fly. In SFSQL all new attributes are indexed.

- if that JSON document contains some deeper structures (e.g. 'Brand' which contains 'Address', 'Support Phone', etc) that you want to pull out and into their own tables (now or eventually). SFSQL stores all objects (aka nodes in the document) as objects.

- SFSQL updates individual attributes of that document independently from the other attributes, meaning you could even nest a 'counter' within the original document and constantly update it efficiently.

- Simplicity. You can still store anything (just like you would in a JSON type column) yet you gain the ability to reference objects as objects and query (even new attributes) without first indexing.

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#19

Post by dfragnito »

pvg wrote: post #14

There's something wrong with your account creation process, it rejects just about any auto-generated (by Chrome) password even though they easily meet the listed criteria.

Looks like chrome pw gen does not generate a special character we have removed that restriction. Please try again.

Re: Show HN: A Schemaless Data Store Within Your SQL Database

#20

Post by pvg »

dfragnito wrote: post #19
pvg wrote: post #14

There's something wrong with your account creation process, it rejects just about any auto-generated (by Chrome) password even though they easily meet the listed criteria.

Looks like chrome pw gen does not generate a special character we have removed that restriction. Please try again.

I think it does but not always. It works now, thanks!

I don't know if you've seen

https://news.ycombinator.com/showhn.html

one of the things it talks about is minimizing 'try it' friction and this one felt pretty high, even without the password thing. People bounce at not just account creation but also stuff like 'beta access' especially if it's not clear from the 'beta access' verbiage if it means 'access right now' or just ending up on some list.

Post reply on HN