title: Database choice and design for Google Spreadsheets alternative
body: <p>We are building a Spreadsheet web app for our clients. They can upload any csv (20 MB+) and then perform operations (listed below) on the data. The data is <strong>highly unstructured</strong>.</p>
  
  <p>Over the last few months we have experimented with a couple of architectures:</p>
  
  <ol>
  <li><p>Initially, we stored the whole grid in 2d array format e.g [ {a: 'b', x:'y'}, {a: 'e'} ] inside of PostGreSQL's JSON data type. But then any cell updated required the whole CSV to be stored in the database. This made the app extremely slow.</p></li>
  <li><p>Next, we moved to MongoDB. This improved the performance but we are still running into performance and scalability issues. Below is our structure. </p></li>
  </ol>
  
  <p>Our current database design:<br>
  PostgreSql Structure:<br>
  Table - datasets<br>
  id, name, description, etc...</p>
  
  <p>Mongo Structure:<br>
  Row 1<br>
  _id, column1: value1, column2: value2, _data_set_id = datasets.id<br>
  Row 2<br>
  _id, column1: value1, column2: value2, _data_set_id = datasets.id<br>
  and so on...<br>
  Also, we have a mongo index on _data_set_id key to support faster queries of the following types.</p>
  
  <pre><code>( db.coll.find({_data_set_id: xyz}) )
  </code></pre>
  
  <p>We are also using hosted mongo from a third party vendor who takes care of sharding, backups, uptime etc. (we don't have devops)</p>
  
  <p>The operations on data are of 2 types:</p>
  
  <ol>
  <li>Row operations e.g adding or deleting a row</li>
  <li>Column Operations e.g adding or deleting a column<br>
  <strong>Most of the operations on the data are column level operations</strong> i.e update only the column in each of the rows.</li>
  </ol>
  
  <p>We have optimized to a point where in mongo works fairly fine with datasets having less than 10k rows. But, beyond that, we are not able to scale. We currently have ~25GB of data in Mongo and within next few weeks we will hit 50GB.
  Our current product is a prototype and now, we are reconsidering our database architecture in order to scale better.</p>
  
  <p>The most critical requirements for our database are:</p>
  
  <ol>
  <li>Fast Read-Writes.</li>
  <li>Column querying and updates.</li>
  <li>Updating single cell (i.e row x, column y) value.</li>
  </ol>
  
  <p>So,     </p>
  
  <ol>
  <li>Is Mongo the right database for this use case ?</li>
  <li>If yes, what else (other than indexing, sharding) can we do to scale Mongo ?</li>
  </ol>
  
  <p>P.S<br>
  We do realise we can achieve only 2 of <a href="http://en.wikipedia.org/wiki/CAP_theorem" rel="nofollow noreferrer">CAP</a> and also gone through <a href="http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis" rel="nofollow noreferrer">Cassandra vs Mongodb vs Couchdb vs Redis</a>
  We are also evaluating Couchdb (Master-master replication, MVCC etc but no qynamic querying), Cassandra (<a href="https://stackoverflow.com/questions/24806170/cassandra-and-unstructured-data">querying on unstructured data is not possible</a>) and HBase(ColumnStore) as alternatives.</p>