I have this table:
CREATE TABLE fragment ( fragment_id integer, start_date timestamp without time zone, end_date timestamp without time zone, duration integer, -- <10+ more columns> revision_1 integer, revision_2 integer )
It is pretty big: 44 million rows, 27 GB of disk space. Daily insert rate is about 70k rows.
The data in this table is almost never updated except for the last two columns named
revision_2. They are updated via triggers set on other related tables. Updates come very frequently, especially for new rows in
fragment table. Each row can be updated up to 50-100 times. Old rows (let's say 1 week old), however, stop being updated, as they are considered 'processed'.
As far as I know,
UPDATE operation in Postgres is implemented as something like
INSERT. So, when a value in a single column is updated, the whole row is marked as deleted and a new row is created. That's why, I think, my
fragment table is autovacuumed every day which takes several hours.
The question is, is it generally a good idea to extract 'hot columns' into a separate table? I mean something like this:
CREATE TABLE fragment_revision ( fragment_id integer, revision_1 integer, revision_2 integer )