- read-heavy table
- updates to this table happen very rarely
- except for one column
last_active_at is updated every time user makes a request
If those requests happen rather frequently, then yes, breaking out the column into a separate table can make sense.
Write locks may be a concern with heavy concurrent write load on the same table, overlapping on same rows. But that should not be the case for what you describe. The MVCC model of Postgres makes sure that readers don't block writers, and writers don't block readers. The manual:
The main advantage of using the MVCC model of concurrency control
rather than locking is that in MVCC locks acquired for querying
(reading) data do not conflict with locks acquired for writing data,
and so reading never blocks writing and writing never blocks reading.
However, due to the same MVCC model, frequently updating a row incurs a substantial write cost - and table and index bloat. Especially if one or more of the following apply:
- There are more indexes.
- The row is wide (many bytes per row in the main relation, TOASTed values are of less concern).
- The column in question is often not involved in other queries.
- Your table (and its indexes) tend to bloat, and
autovacuum has a hard time to keep up.
Then it may pay to split off the
last_active_at into a separate table in a 1:1 relationship to the main table. Updates will only leave very small dead tuples and bloat only (typically) two indexes. Lock contention with other writes will (typically) decrease,
VACUUM cost will go down.
SELECT on the main table will have to read fewer data pages and be faster.
Whenever you have to join to the additional table, you introduce additional cost and complexity in your queries, though. In pristine condition, without bloat, storage size increases due to the added overhead per row, another ID column and the index on it. But if bloat is not an issue, then the split is probably not warranted to begin with.