0

I understand that when a transaction performs a change, Postgres mvcc scheme stores a new row with the updated data, and an associated version.

Other transactions know which versions they should see, and they pick the appropriate row.

I also understand that that the primary index of a table stores all the versions of the row. I can see how the primary index can be updated (you lock it for writing, append the row, and unlock. I'm not sure if this is how it works, but that is a potential way).

But how does Postgres atomically updates the secondary indexes? I imagine that when a transaction commits, all the indices need to be updated with the new data in the row.

Is Postgres acquiring a lock on all the secondary indices to atomically update them? If so, is the granularity of the lock at the index level or at the value level (lock only the part of the index for a specific value)?

Is the approach the same in other DB engine, like InnoDB?

I'm building a toy DB, and I'm having a hard time understanding how to efficiently update the secondary indexes. Any paper on the topic is also welcomed!

Thanks a lot!

Makers_F
  • 3,033
  • 4
  • 36
  • 51

1 Answers1

1

PostgreSQL does not support index-organized-tables. So all indexes are secondary indexes.

The indexes are not updated atomically. A process which sees an interesting entry in an index will chase it down to the table. In the table it sees that that tuple is not yet committed, so ignores it. The system arranges to present the user with an experience of atomicity, but at the level where indexes operate there is not actual atomicity.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • 1
    What about index-only scans? Wouldn't the index need to store the transaction id as well for those? – Bergi Nov 29 '20 at 09:52
  • 1
    For IOS it consults a visibility map having one bit per page, saying whether all tuples on the corresponding page are visible. (Recent versions have s second bit per page that serves another purpose) That is why vacuuming is so important for effective IOS. – jjanes Nov 29 '20 at 15:09
  • Thanks a lot! So, if I understand correctly, once the row data is updated, an entry is added to all the indexes. Indices at this point will still ignore the row, since it's not marked as committed. Once the row is marked as committed, all the queries going through the indices are going to start showing the row. Is that right? How would an older transaction which is not supposed to see the new tuple access the index? Does it go to the tuple to check the version? Also, is vacuuming important because it removes older rows and compacts valid rows, so it's more likely that the page bit is true? – Makers_F Nov 29 '20 at 23:13
  • It isn't the index which ignores the row, but the process consulting the index. If it finds the row to be committed but too recently to be visible to its snapshot, it ignores the row then as well. I think that vacuuming is the only process which sets the visibility bit to true. (In theory some other processes could sometimes do it, but I don't think they ever do) – jjanes Dec 04 '20 at 22:20