2

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

But I am not able to figure out how a 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 can be: 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 what happens when the row gets updated to RUNNING status? (task_uuid, task_status) is still unique, but will it be removed from the partial index? The record does not fulfill the condition any more.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Prometheus
  • 549
  • 1
  • 7
  • 18
  • 1
    Every index gets updated every time you run a DML statement on the table –  Aug 10 '20 at 12:18

1 Answers1

4

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34