title: Database design for time series
body: <p>Approximately every 10 min I insert ~50 records with the same timestamp.<br>
  It means ~600 records per hour or 7.200 records per day or  2.592.000 records per year.<br>
  User wants to retrieve all records for the timestamp closest to the asked time.</p>
  
  <p><strong>Design #1</strong> - one table with index on timestamp column: </p>
  
  <pre><code>    CREATE TABLE A (t timestamp, value int);
      CREATE a_idx ON A (t);
  </code></pre>
  
  <p>Single insert statement creates ~50 records with the same timestamp:</p>
  
  <pre><code>    INSERT INTO A VALUES (
        (‘2019-01-02 10:00’, 5),
        (‘2019-01-02 10:00’, 12),
        (‘2019-01-02 10:00’, 7),
         ….
      )
  </code></pre>
  
  <p>Get all records which are closest to the asked time<br>
  (I use the function greatest() available in PostgreSQL):</p>
  
  <pre><code>    SELECT * FROM A WHERE t =
  (SELECT t FROM A ORDER BY greatest(t - asked_time, asked_time - t) LIMIT 1)
  </code></pre>
  
  <p>I think this query is not efficient because it requires the full table scan.<br>
  I plan to partition the A table by timestamp to have 1 partition per year, but the approximate match above still will be slow.</p>
  
  <p><strong>Design #2</strong> - create 2 tables:<br>
  1st table: to keep the unique timestamps and auto-incremented PK,<br>
  2nd table: to keep  data and the foreign key on 1st table PK</p>
  
  <pre><code>    CREATE TABLE UNIQ_TIMESTAMP (id SERIAL PRIMARY KEY, t timestamp);
      CREATE TABLE DATA (id INTEGER REFERENCES UNIQ_TIMESTAMP (id), value int);
      CREATE INDEX data_time_idx ON DATA (id);
  </code></pre>
  
  <p>Get all records which are closest to the asked time: </p>
  
  <pre><code>SELECT * FROM DATA WHERE id =
  (SELECT id FROM UNIQ_TIMESTAMP ORDER BY greatest(t - asked_time, asked_time - t) LIMIT 1)
  </code></pre>
  
  <p>It should run faster compared to Design #1 because the nested select scans the smaller table.<br>
  Disadvantage of this approach:<br>
   - I have to insert into 2 tables instead just one<br>
   - I lost the ability to partition the DATA table by timestamp</p>
  
  <p>What you could recommend?</p>