etirk

Did not try that kind of index on postgres. Thx. Will keep that on the list for benchmarking.

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.

"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

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.

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.

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.

Exactly what you are getting! But without having to code, optimize and maintain your own EAV structure and API.

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.

That WOULD be horrible. But we arent doing that. In essence we found that JOINS using optimized indexes are less costly than full table scans (on unindexed attributes).

And just to be clear. This is NOT a mutually exclusive solution. Just as people use a combination of different databases for different projects and even within the same project, SFSQL can be used alongside other solutions. It is especially convenient to use as an additional datastore when you are already storing your data in a SQL DB because there is no additional devops beyond what was already in place for that SQL DB.

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.

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 vs straight SQL tables and indexes. If you needed to store a number of columns in a SQL table and you knew exactly what columns would be queried on (as in filtered on, not just selected), then you would not be able to beat the speed of a composite index on those exact SQL columns. However, as more columns are added to that table, and the demand for more of those columns be queryable at index speeds increases, then the possible combinations of covering indexes grows. In general, more queryable columns end up resulting in more indexes, and if you can't accomodate every possible combination of columns across those indexes, then someone will hit a query which ends up table scanning, and of course more indexes result in slower inserts as well. With SFSQL there is a static number of indexes. It's insert speed and query speed might not be the fastest compared to custom composite indexes. But query speed degrades predictibly as the number of columns queried grows. You might be surprised at the performance given that you can add and query new attributes on the fly without any tables or indexes to create.

- Do you generate indexes on data as it comes in?

No, we don't have to do that. Everything is indexed.

- How are nested keys handled?

Every primitive attribute (non-object) is indexed by it's value and the relation between objects uses oid/poid indexes. Joins are constructed on the fly that make use of the indexes on the primitives and between the objects.

- Are indexes eventually consistent?

The indexes are immediately consistent.

Several document-based database systems claim to be schamaless, yet when your data demands cross-document relations (esp. many-to-many), something ugly rears its head which usually ends with you performing multiple queries and relating (or JOINing) data by hand. This essentially forces the work away from the locality and fast speed of the database level, and up towards the remote and slow speed of the client-to-database connection. The difference in speed is akin to the difference between registers and RAM, or between RAM and Disk.

The resultant design pattern often ends up a mirror of what you would have done in any standard SQL database, but without the low level optimizations that a SQL database brings to such a pattern.

Additionally, with a non-native design pattern now in place to deal with your data relationships, concurrency issues may begin to surface.

In SchemafreeDB, your data relationship possibilities are essentially unbounded; yet consistent in both design patterns, access patterns and performance.

Hello, I'm Eric, developer and co-founder of Xornet, Inc. the company behind our new service, SchemafreeDB.

SchemafreeDB was designed from the ground-up to enable Rapid Database Development.

SchemafreeDB's unique combination of features include:

* Free-form data structures created at insert-time.

* No worries about issuing UPDATE TABLE on large data sets. In fact, we did away with the entire concept of UPDATE TABLE.

* Support for complex, nested data structures. Objects can contain simple attributes (String, Int, etc) or other Objects.

* Support for querying across disparate object structures.

* Familiar SQL query language.

* Simple join-free, dot-notated syntax (e.g. WHERE $s:person.address.city='Rochester' AND $i:person.income>50000). This is familiar to the ease of working in an ORM, but without the impedance mismatch between in-memory objects and database objects.

* Free indexing. Delivers fast index-like queries without index configuration AND at the same time delivers inserts at fast no-index speeds.

* Lightweight, consistent, platform-neutral connection library - JSON over HTTP. Access your database the same way from from any network-enabled platform.

Please check us out at http://schemafreedb.com

We're looking for feedback!