Comment by atombender
1 day ago
If you have a "done" column that you filter on using a partial index, then it would never use HOT updates anyway, since HOT requires that none of the modified columns have an index.
1 day ago
If you have a "done" column that you filter on using a partial index, then it would never use HOT updates anyway, since HOT requires that none of the modified columns have an index.
False.
As of PG16, HOT updates are tolerated against summarizing indexes, such as BRIN.
https://www.postgresql.org/docs/16/storage-hot.html
Besides, you probably don't want "done" jobs in the same table as pending or retriable jobs - as you scale up, you likely want to archive them as it provides various operational advantages, at no cost.
Not false. Nobody would ever use BRIN for this. I'm talking about regular indexes, which do prevent HOT.
If you read my earlier comment properly, you'll notice a "done" column is to avoid deleting columns on the hot path and avoid dead tuples messing up the planner. I agree that a table should not contain done jobs, but then you risk running into the dead tuple problem. Both approaches are a compromise.