[crooked-v]

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}')::int) AS average_rating
    FROM reviews
    WHERE review_jsonb@>'{"product": {"category": "Sheet Music & Scores"}}' GROUP BY title
    ORDER BY average_rating DESC
    ;

[etirk]

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.

[CoffeeOnWrite]

How do you protect against user input that produces a psychopathic volume of indexes?

[etirk]

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.

[rmbyrro]

I guess that would be on the developer side to sanitize/limit/clean in some way, like limiting the number of key-value pairs, object depth, etc?

[time0ut]

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…

[etirk]

"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