Skip to main content

PG.L7.UPDATED_AT_INDEX_BLOCKS_HOT — An index on updated_at that costs its table every HOT update

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: experimental — this rule does not run unless a project opts in
  • Suites: audit
  • Applies to: PostgreSQL 18

What it reports​

A B-tree index, or any other index that does not summarize, that reads updated_at in a key, an expression, an INCLUDE column or its predicate, on a table whose updates were counted and were mostly not HOT. The columns are read from pg_index, never from the index's name.

A HOT update writes the new row version on the same page and adds no index entry, but only while no column that such an index reads has changed. Laravel's timestamps() writes updated_at on every save() that changes anything, so with an index on it no such update can be HOT, however much room the page has. Every update then writes an entry into every index of the table and leaves a dead one in each, and fillfactor does not help.

Flagged​

CREATE INDEX orders_updated_at_index ON orders (updated_at);

What it was measured on​

PostgreSQL 18.0, three tables with fillfactor = 70 and 10,000 rows each, then one UPDATE … SET v = v + 1, updated_at = now() over every row:

TableIndex on updated_atn_tup_updn_tup_hot_upd
hot_plainnone100004416
hot_btreeB-tree100000
hot_brinBRIN100004416

The BRIN row is the control on the same column: a summarizing index does not take the HOT path away, a B-tree takes it entirely.

CREATE TABLE hot_btree (id bigint PRIMARY KEY, v int, updated_at timestamptz) WITH (fillfactor = 70);
CREATE INDEX hot_btree_updated_at_index ON hot_btree (updated_at);
INSERT INTO hot_btree SELECT g, 0, now() - interval '1 day' FROM generate_series(1, 10000) g;
UPDATE hot_btree SET v = v + 1, updated_at = now();
SELECT pg_stat_force_next_flush();
-- in a second session:
SELECT relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'hot_btree';

How it weighs a table​

From n_tup_upd and n_tup_hot_upd of the index's table, over the window since its statistics were last reset. A table that took fewer than 1,000 updates in that window pays almost nothing, and one whose updates are at least 80 percent HOT evidently does not write the column on them. Neither is reported.

Where the counters cannot be read, or the server hands new connections track_counts = off, the answer is undetermined with the reason, never a pass: a role that cannot see pg_stat_all_tables and a table whose updates are all HOT produce the same empty answer.

Why it is experimental​

Its weight comes from counters that move with every write and can be reset, so the same schema can get two answers on two days. That is the reason PG.L7.INDEX_UNUSED gives, and this rule gets the same answer: a project that wants the question asks for it with sqlens.stability.

What to do​

Drop the index if nothing reads it, which PG.L7.INDEX_UNUSED answers, or keep it on purpose with a baseline entry and the reason. A BRIN index on the column does not cost HOT, but it serves a range filter, not an ORDER BY:

DROP INDEX CONCURRENTLY orders_updated_at_index;