7 ms·
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 c
by atombender 1y 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.
- menthe 1y agoFalse. As of PG16, HOT updates are tolerated against summarizing indexes, such as BRIN. https://www.postgresql.org/docs/16/storage-hot.html 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.
- atombender 1y agoNot 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.