title: Postgres: one table with many columns or several tables with fewer columns?
body: <p>My question relates to the innards of how Postgres works:</p>
  
  <p>I have a table:</p>
  
  <pre><code>
  CREATE TABLE A (
     id SERIAL,  
     name VARCHAR(32),
     type VARCHAR(32) NOT NULL, 
     priority SMALLINT NOT NULL,
     x SMALLINT NOT NULL,
     y SMALLINT NOT NULL,
     start timestamp with time zone,
     end timestamp with time zone,
     state Astate NOT NULL,
     other_table_id1 bigint REFERENCES W,
     other_table_id2 bigint NOT NULL REFERENCES S,
     PRIMARY KEY(id)
  ); </code></pre>
  
  <p>with additional indexes on other_table_id1, state and other_table_id2.</p>
  
  <p>The table is quite large and sees very many updates on columns: other_table_id1, state.  A few updates for start and end columns, but the rest are immutable. (Astate is an enumerated type for column state.)</p>
  
  <p>I'm wondering whether it makes sense to split out the two most frequently updated columns to a separate table.  What I'm hoping to gain is performance, for when I'm just looking up that info, or to reduce the weight of updates because (maybe?) reading and writing the shorter row is less costly.  But I need to weigh that against the cost of joins when they are (occasionally) needed to have all the data for a particular item all at once.</p>
  
  <p>At one point, i was under the impression that each column is stored separately.  But later, I modified my thinking when I read somewhere that lessening the width of a column on one side of the table does positively affect the performance when looking up data using another column (because the row is stored together, so the overall row length would be shorter).  So I'm now under the impression that all the data for a row are physically stored together on disk; so the proposed splitting of the table sounds like it would be helpful.  When I currently write 4 bytes to update the state, am I to believe I'm rewriting the 64 bytes of text (name, type) that actually never change?</p>
  
  <p>I'm not very experienced with table "normalization" and not familiar with the internals of Postgres, so I'm looking for advice and esp best practices for estimating the tradeoff without having to do the work first, then determine whether that work was worthwhile.  The change would require a fair bit of effort in rewriting queries that have already been highly optimized, so I would rather go in with a good understanding of what result I can expect.  Thanks, M.</p>