Does it make sense to split an often updated column into a separate table?

Question

Let's say I have a wide user_account table.

It is read-heavy table, and updates to this table happen very rarely, except for one column: This table has column last_active_at, which is updated every time user makes a request.

last_active_at happens to be rarely read.

Does it make sense to move the last_active_at column to a separate table?

My thinking is that this would reduce the lock time for any queries that are user_account table related.

Answer 1

• 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.