title: Storing a huge amount of points(x,y,z) in a relational database
body: <p>I need to store a very simple data structure on a disk - the <code>Point</code>. It's fields are just:</p>
  
  <ul>
  <li><p><code>Moment</code> - 64-bit integer, representing a time with high precision</p></li>
  <li><p><code>EventType</code> - 32-bit integer, reference to another object</p></li>
  <li><p><code>Value</code> - 64-bit floating point number</p></li>
  </ul>
  
  <p><strong>Requirements</strong>:</p>
  
  <p>1) The pair of (<code>Moment</code> + <code>EventType</code>) is unique identifier of the <code>Point</code>, so I suspect it to be a composite primary key for a table</p>
  
  <p>2) There's a huge number of <code>Points</code>. Up to 5 billions (1-2 TB of disk space). So the format must be as small as possible. </p>
  
  <p>3) Typical and almost single usage of the table is a retrieval (or creating a view) of millions of <code>Points</code> by exact <code>EventType</code> and a range of <code>Moments</code>.</p>
  
  <p><strong>Questions:</strong></p>
  
  <p>Which RDBMS to choose and why?</p>
  
  <p>What is the optimal sql definition for a table of <code>Points</code>?</p>
  
  <p>And comments about my thoughts below are also appreciated</p>
  
  <p><strong>My research:</strong></p>
  
  <p>I'm a complete newbie in the field of RDBMS, but I've heard a lot about SQLite. I don't need a huge professional system with all tools, features and extensions like PostgreSQL or MSSQL. Also I don't feel I need a server instead of simple "embedded" database file, so the choise of SQLite looks optimal. Another great RDBMS with a feature of embedded database is Firebird, but I was seduced by SQLite's dynamic typing paradigm. It looks like it can save me space on disk, because integer fields can be stored in "smaller" form (1, 2, 3, 4, 6 bytes).</p>
  
  <p>But shortly problems appeared.</p>
  
  <p>First of all, SQLite creates special <code>ROWID</code> column (64-bits length) when primary key is composite:</p>
  
  <pre><code>CREATE TABLE points (
      moment integer not null,
      event_id integer not null,
      value numeric not null,
      PRIMARY KEY (moment, event_id)
  );
  </code></pre>
  
  <p>It means table wastes nearly 40% much space for nothing.</p>
  
  <p>I found nice article about <a href="http://www.sqlite.org/draft/withoutrowid.html" rel="nofollow">"The WITHOUT ROWID Optimization"</a>. But it will be avaible only in 3.8.2 version of SQLite (December 2013). Waiting for ADO.NET provider which I need is inappropriate.</p>
  
  <p>Another problem is that SQLite uses <a href="http://en.wikipedia.org/wiki/B-tree" rel="nofollow">B-tree</a> for tables. I'm not sure, but it looks like it is inefficient for selecting data ranges. My main task is to select a big block of <code>Points</code> based on primary key's range, so it looks like SQLite will be bad choise.</p>
  
  <p>Future research seems too hard for me (at least for today). Looking forward for a help of experienced guys.</p>