Column or row oriented store

[tyt2y3]

There is recently more interest in column oriented store.

The benefits are:

1. less serialization overhead
2. add/drop a new column is cheaper
3. efficient handling of wide table
4. opportunities to parallelize operations on different columns

The drawbacks are:

1. require more indexation space
2. more overhead in materializing a query result
3. more overhead in insertion operation

But our execution engine cannot be agnostic to both, because the design of algorithms would depends heavily on the underlying storage model.

[joaoh82]

Column oriented storage is definitely becoming more popular, but I see it as more more of a data warehouse and databases targeting more analytical solutions, because they is grouped on disk or even in-memory. So I guess it depends on what we are going for here.

I have to be honest, that it wasn't what I had in mind.

Another drawback, that it counterpoints the add/drop a new column benefit is that inserting and removing a row in the middle of a sorted table it would be very expensive. There are solutions to lower the cost of that, but the point is that with a column oriented storage the cost of writes is higher.

Paragraph taken from the Designing Data-Intensive Applications:
These optimizations make sense in data warehouses, because most of the load consists of large read-only queries run by analysis. Column-oriented storage, compression, and sorting to make those read queries faster. However, they have the downside of making writes more difficult.

Like you said @tyt2y3 , our execution engine cannot be agnostic to both, so I vote for us sticking to row oriented storage.

Then again, we are here to break new ground.

What do you think?

[tyt2y3]

Can you talk about your intended use too?

[joaoh82]

To be honest my vision for this project is evolving.
I am trying to compile and write something about the current vision and post it in a specific thread.
That said, so far this is what I have in mind:

• Embedded DB that can run inside WASM, with in memory storage.
• Embedded DB locally or on a server, and backed by a disk data store like sled, and taking advantage of the concurrency features of Rust.
• Embedded DB to use inside unit tests compatible with multiple SQL Dialects and features.

All those in my mind fall into a more transactional model, which then fall under a row based storage. MySQL, PostgreSQL, SQLite all follow a row based storage. Even MySQL which is multi engine, the main engines InnoDB and MyISAM are both row based.

BigTable uses column families mostly as an extra index, but storage is still pretty much row based, since rows are stored together.

In here you (@tyt2y3 ) say:
From my experience I'd said OLTP is 90% read 10% write, so I care more about read performance.
But at the same time OLTP read queries tend to retrieve a lot more row the OLAP and in that case a row based storage is beneficial, because something like select * from tableA on a OLAP column base database can be very costly.

I also agree with what you said earlier, our execution engine cannot be agnostic to both. So for now my vision in tending towards a row based storage and try to come up with some optimization towards columns. Maybe something like what bigtable does that groups columns by column family but still keeps the rows together.

[tyt2y3]

Yes, I agree with you on these. I wonder what'd the performance characteristics look like when MySQL is paired with a Column oriented store.

Yes as we discussed before, our vision is a multi-modal SQL engine that can scale from nano, micro to mini.

I also want to coin one more use case: for micro services, using a SQLite store would be 'too small' but orchestrating an external DB would be hard to manage. Often the choice is to pack a MySQL or Postgres within a docker image, but then it is not quite 'micro'.

So I see an opportunity for SQLrite to become the critical component of a self-contained micro service stack, together with other crates in the Rust ecosystem.

And the fact that there is no need to serialize / deserialize data over a 'wire protocol' would imply much lower latency.

[joaoh82]

Can you imagine if instead of the MEAN or MERN stack, we have something like SARR stack. SQLrite, Actix, React and Rust. LOL.

[joaoh82]

Now going back to a more serious comment. SQLite handles well multiple reads, but limits only one writer at a time. So this use case we can definitely target by just allowing for concurrent writes.
I am with you on this.