Essential storage tradeoff: Simple Reads vs. Simple Writes

Hey Stephan,

very neat and clean presentation of the trade-off those two principles.
Unfortunately there is more involved in such a decision, but hopefully you’ll get into that later ^^

Chears
Stefan

In applications I have worked on, data consistency has been the highest concern. The performance loss to joins has been minimal in almost every case, and the ability to change\add data without having to remember to do it in 10 different tables is essential.

I will likely stick to Normalized Data for the majority of my applications, but I will have to keep an open mind towards DeNormalized data as well, I suppose.

+1 this is a great high-level summary of the architectures. The characterisation of a simple trade-off is a good starting point.

A good example of where this played out was Twitter, back when it kept falling on its face because it was using an sql database for storing everything.

I think it’s worth mentioning that some SQL databases (Oracle for example) provide out-of-the-box solutions for this problem as well and arguably in a more seamless manner. For example, creating a materialized view based on a complex query gives you the benefits of a denormalized schema. This, in conjunction with query re-writing could mean your application would continue to operate normally without any knowledge of the denormalization going on behind the scenes. Meanwhile, at the application level inserts and updates don’t need to be concerned about manually updating the data across multiple tables since that will be handled automatically via the materialized view mechanism.

My experience with non-normalized data is that it will become inconsistent, guaranteed. You have to have a normalized backbone for important data. Complex views of normalized data are very useful and I’ve been using a materialized view strategy for 25 years, long before I ever heard of any formal descriptions. I have never been able to get any other system to work.

In terms of chunking, I find extremely complex “materialized views” are very useful. I haven’t used the Oracle system, but I’ve written some pretty complicated update routines that I would be surprised to see in standardized packages. So I think your goals can be best realized (in most environments) with sophisticated systems comparable to materialized views – documents that update automatically based on an underlying verifiable data system.

Again a beautiful and nice posting, beside the content, which is very good!

For ad-hoc reporting we BI experts are currently facing the arising of coulumn based and column indexed storages, mainly in combination with in-memory technology.

As developer one does data modelling in his / her best way, in preparation for access things are split into columns and links between. Far away from 3NF or documents.

I supose it’s worth having a look at during your further investigations. ‘am looking forward.

Regarsd
Rayk

I’ve been studying this issue closely for a while now. I have built and maintain a system with hundreds of millions of records that’s growing at about 25% per quarter consistently, so I’m obsessed with data storage. My application relies on hundreds of queries that need to run in real-time against all of that transactional data – no offline cubes or Hadoop clusters. I’m considering a jump to NoSql, but the lack of ad-hoc queries against live data is just a killer. I write probably a dozen ad-hoc queries a week to resolve support issues, and they normally need to run “right now!” I might be analyzing tens of millions of records in several different tables or fixing some field that got corrupted by a bug in the software. How do you do that with a NoSql system?

My solution for the moment is sharded databases, with a healthy dose of de-normalized aggregates sprinkled throughout the model, which are kept in synch with triggers or controlled data access via stored procedures. There’s no chance of data being different in two places.

And there’s no edict on how to write queries – if you join 20 tables and it runs fast, fine. If you need to write parallelized queries to return raw data from one table at a time and then join them on an application server, then that’s fine too.

I think a lot of people new to this game don’t get how good a SQL database can be. On a single shard (a cheap 2U box), I can run 5 million customers, which is something like 150Gb of data, and everything runs fast enough to keep people happy. In a NoSql system, how many nodes would you need to support both transactional and reporting applications for that much data?

If people haven’t, now would be a great time to go and review Brewer’s CAP Theorem!

http://www.julianbrowne.com/article/viewer/brewers-cap-theorem

If the denormalized views are constructed from a normalized database, the normalized data isn’t dropped. It’s still there, and can be used to verify or reconstruct documents. My understanding of the NoSQL movement is that it abandons the goal of a normalized data structure entirely.

[...] Code Monkeyism: Essential storage tradeoff: Simple Reads vs. Simple Writes [...]

[...] Code Monkeyism: Essential storage tradeoff: Simple Reads vs. Simple Writes [...]

[...] The second is a rejection of the strong typing of relational schemas, which make changes to data models, which are inevitable,  disastrously difficult to achieve.  It also makes writing to the data store a complex process. [...]

[...] an old post about the read-/write-cost tradeoff. For most applications reads occur way more often than writes. Therefore this application should [...]

This discussion seems to miss the logical/physical distinction.

Whatever you think of the relational model as a logic user model, remember that it is intended as only a logical model.

Of course pointers for foreign keys, co-locating related entities, and relaxing constraints, can help performance. Those things are a part of the physical model.

The weakness is that to a greater or lesser degree, the SQL products do not support some
of the physical models you want. That seems to me to be purely an implementation issue.

Even in the document-based version, updating items on it can be very easy and quick depending on what solution you use. For example, if you are using MongoDB, you can just tell it to update the inner content of an element inside a document, like:

shoppinglist.friends[0].name = ‘Johnny’

And you can also store references to other columns, so if your list of friends can be a list of references to other documents describing users (kinda like a relational database would do), so all you would need is, too, to update the user document.

You can also have server-side functions written in javascript to do these kind of updates, so that you need not transfer info around server client, just call the server-side function and it will do the update locally. You can also send plain javascript as a query, like an UPDATE, to do what is needed.

This illustrates the emerging issue of relational data modeling vs object data modeling… the evidence is that a paradigm shift is imminent, unfortunately I think the writing is on the wall for use developers and sys admins alike, we will come to miss the simplicity of the relational model.

think’s, it’s sobering !

but the problem is frameworks and ORM are also developped for relationnel datas

for example in PHP (yeah I know ! but now with frameworks and architecture it’s powerful for “web2.0″ or social, see drupal or facebook!) there is a very good ORM like Doctrine for data components.
but if you want to use these new model, I suppose you have to throw all ORM you liked so muche and make yourself all data work. paintful !

don’t know if it’s supported in Rails ?

for me the problem is not if we can use these database, but if they are integrated in application frameworks.

[...] Bases de données dénormalisées : Article très instructif sur la nouvelle génération de bases de données dites [...]