In PostgreSQL every update of tuple creates new tuple version. So for some period of time there can be lot of versions of same tuple and different transactions can see different version of tuple (using visibility rules)
Index is updated before transaction complete. How this works with SI?
So when one transaction updated tuple then index entry updated to point to new version of tuple?
Since PostgreSQL implements MVCC by keeping multiple versions of one row in the table at the same time, it also keeps multiple index entries for different versions of a single row (sometimes this can be avoided with heap-only tuples if the indexed entries are not modified during an update and the updated row is in the same table block as the original version).
The visibility information is not stored in the index, so to find the correct row version during an index scan, the table entries for all these index entries have to be checked (somethimes this can be avoided if an index block is known to contain only entries that are visible to everybody; this is an index-only scan).
Old index entries are removed along with old table entries during autovacuum.
