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!
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.