title: Storing git like commit history of changes
body: <p>I want to build a git like versioning system for one my database entities.</p>
  
  <p>The models would be:</p>
  
  <pre><code>Commit
    parent_commit_id
  
  Change
    entity_id
    commit_id
    modifier (added, deleted, modified)
  
  Entity
  </code></pre>
  
  <p>An Entity is never really deleted so an user can undo a commit and end up with a valid state.</p>
  
  <p>It sounds simple, but:</p>
  
  <ul>
  <li>A commit is generated on every click by the user (performance is very important)</li>
  <li>On every commit there will be about 100 changes (either modifications, additions or deletions)</li>
  </ul>
  
  <p>Because we are just storing changes, the current state must either be calculated or stored.</p>
  
  <p><strong>Option 1</strong>: Saving the complete state for every commit would mean that I would not only store the modifications, additions and deletions in a commit, but also every entity that has not changed. I think this is not an really an option because there will be more than 20 million entities when the system is running.</p>
  
  <p><strong>Option 2</strong>: Calculating the current state by using a recursive common table expression. I don't know if this is fast enough...</p>
  
  <p><strong>Option 3</strong>: Performing a clean up after a certain period of time (like git gc). This would mean that I would actually delete all entities that are no longer valid at the time of the clean up. Not really an option because I want to be able to see all changes and go back to those changes.</p>
  
  <p><strong>Option 4</strong>: Use a graph database (neo4j)? Could it handle such a specifaction with reasonable performance?</p>
  
  <p>Do you have any other ideas?</p>