When does a partial index gets updated in PostgreSQL

user_1782535 0
Viewed 298

I am doing a performance tuning on one of the largest table in our project. While reading about indexes I came across the partial index in PostgreSQL. This sounds a very nice idea to put indexes only on the rows which are getting accessed frequently.

Though, I am not able to figure-out how the partial index gets updated. For example, I have a table with following Columns:

task_uuid, job_id, enqueued_at, updated_at, task_status

task_status= ENQUEUED, RUNNING, ASSIGNED, FAILED

We search for the records which are in ENQUEUED state very frequently. If we add a partial index on (task_uuid, task_status) it will build a unique key and improve the performance. But, I want to know, what happens when the record gets updated, when we update the record RUNNING status. (task_uuid, task_status) is still unique, but will it be removed from the partial index? as the record does not fulfills the condition.

partial-index indexing postgresql database
1 Answers

If we add a partial index on (task_uuid, task_status) it will build a unique key and improve the performance.

It will only build it as unique if you specify that in the definition of the index. Otherwise it won't be a unique index, even of those columns do happen to be unique.

When a record gets updated so that it no longer matches the WHERE predicate of the index, nothing happens to the index. It still has a pointer to the row, it just points to something no longer valid. If you did specify the index as UNIQUE, then upon inserting a conflicting index tuple, it will follow the pointer for the old tuple to the table, realize it is invalid, and allow the insertion to continue.

The next time the table is vacuumed, those obsolete pointers will be cleaned up. Queue tables with partial indexes should usually be vacuumed frequently (more frequently than the default) because the index bloats easily. The Autovac settings depend on the fraction of the table rows obsoleted, not on the fraction of the index rows obsoleted. For partial indexes, these fractions are not the same. (On the other hand, you don't seem to have a status for "COMPLETED". If completed tasks are immediately deleted, perhaps the queue table will stay small enough that this does not matter.)

Also, when an index scan follows the pointer from the index into the table and finds the row is no longer visible to anyone, it will mark the index entry as dead. Then future index scans won't have to pointlessly jump to the table. But this "microvacuum" only happens for regular index scans, not bitmap scans, and it only happens for queries done on the master, not for any done just on a hot standby.

user_17212390
Related