title: Dynamic table partitioning by string identifier in Postgres
body: <p>I would like to partition a table in Postgres by previously unknown value. In my scenario that value would be device_id which is a string.</p>
  
  <p>This is current situation:</p>
  
  <p>Table 'device_data' - stores sensor data which is sent from devices, defined by DDL:
  </p>
  
  <pre><code>CREATE TABLE warehouse.device_data (
    id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('device_data_id_seq'::regclass),
    device_id TEXT NOT NULL,
    device_data BYTEA NOT NULL,
  --   contains additional fields which are omitted for brevity
    received_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
  );
  </code></pre>
  
  <p>Table currently holds millions of records and queries are taking huge amount of time. Most of queries contain <code>WHERE device_id='something'</code> clause.</p>
  
  <p>Solution I have in mind is to create table partitions for each <code>device_id</code>.</p>
  
  <p><strong>Is it possible in Postgres to create table partitions for each <code>device_id</code>?</strong></p>
  
  <p>I went through Postgres documentation and couple of examples I found but all of them use fixed boundaries to create partitions. My solution would require:</p>
  
  <ol>
  <li>create new table partition on the fly when new <code>device_id</code> is first
  encountered</li>
  <li>store to an existing partition if the <code>device_id</code> is
  already known and partition for that <code>device_id</code> already exist</li>
  </ol>
  
  <p>I would like this to be done using table partitions as it would allow querying across multiple <code>device_id</code>s.</p>