title: Can I build a blob in Postgres?
body: <p>I have a scenario where a db has a few hundred million rows, keeping a history of a few weeks only.</p>
  <p>In this database are different products (tradable instruments) and each has about 72k rows of data per hour and there is roughly 30 products.</p>
  <p>The data is always requested by blocks of 1h, aligned on 1h. For example &quot;I want data for X from 2pm to 3pm.</p>
  <p>This data is processed by several tools and the requests are very demanding for the database.</p>
  <p>Each tool does its own disk caching, building a binary blob for each hour.</p>
  <p>But I was wondering if it would be possible to build these directly in Postgres?
  Data is indexed by timestamp and is written in a linear fashion as the writes represent live data.
  So it would be possible to detect with a trigger that we just crossed an hour.</p>
  <p>Would it be possible when we detect this to get all this data, build a binary blob out of it and save it in it own table? the data is simply all the columns one after another in binary forms. They're all numbers, no strings, etc so the alignment / format is very simple and rigid.</p>
  <p>In practice the rows are like this:</p>
  <pre><code>instrument     VARCHAR                     NOT NULL,
  ts             TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  quantity       FLOAT8                      NOT NULL,
  price          FLOAT8                      NOT NULL,
  direction      INTEGER                     NOT NULL
  </code></pre>
  <p>and I would like, at the end of each hour to build a byte array that's like this:</p>
  <pre><code>    0  4       12    20         24
      |ts|quantity|price|direction|ts|quantity|price|direction...
  </code></pre>
  <p>with every row of the hour. Build one blob per instrument and write it in a table like this:</p>
  <pre><code>instrument    VARCHAR
  ts            TIMESTAMP
  blob          BYTEA
  </code></pre>
  <p>My questions are:</p>
  <ol>
  <li>is this possible? or would be it be very inefficient to get 30 (products) * 72k rows each to aggregate and save every hour in an efficient way?</li>
  <li>is there anywhere I could find an example leading me toward this?</li>
  </ol>