Databases
StackOverflow2013
Postsdbo
POPostgres: one table with many columns or several tables with fewer columns?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgres: one table with many columns or several tables with fewer columns?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USMayur Patel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPostgres: one table with many columns or several tables with fewer columns?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPostgres: one table with many columns or several tables with fewer columns?
      1. USXavier_Ex
      1. VTFavorite
    3. VO
      singulars
      1. POPostgres: one table with many columns or several tables with fewer columns?
      1. USRichard
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload