title: PostgreSQL database with row versioning
body: <p>Just wanted to start out by saying much love to the StackOverflow community :)</p>
  
  <p>So I have a Postgres database table, but let's reduce to the significant columns:</p>
  
  <pre><code>id (string/int), when (timestamp/time/date), canonical (boolean)
  </code></pre>
  
  <p><em>Canonical</em> in this case means "accepted part of the version history".</p>
  
  <p>Let's say our table looks like this:</p>
  
  <pre><code>2, 2003-01-08, true
  2, 2002-01-08, false
  1, 2001-01-08, false
  3, 2000-01-08, false
  2, 2000-01-08, true
  1, 2000-01-08, true
  1, 1999-01-08, true
  </code></pre>
  
  <p>I want a query that will find the <strong>latest canonical version for each ID</strong>. The result should look like:</p>
  
  <pre><code>2, 2003-01-08, true
  1, 2000-01-08, true
  </code></pre>
  
  <p>So two questions I guess:</p>
  
  <ol>
  <li>How would you write a query for this?</li>
  <li>Can it be indexed?</li>
  </ol>
  
  <p>A larger, more meta-question, is if there is a better way to handle storing this sort of information.</p>