Cary Huang explains how row visibility works in Postgres:
Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions.
For example,
- A user changes a data record from ‘A’ to ‘B’ via UPDATE
- PostgreSQL handles this by INSERTing a new record call ‘B’ while marking record ‘A’ as ‘invisible’
- Both records exist in the system, but ‘B’ is visible, while ‘A’ is not.
- A deleted or invisible record is also referred as a ‘dead’ tuple
- One of the responsibilities of VACUUM process is to clear these ‘dead’ tuples to free up space.
Read on to learn more about various visibility indicators and how they ultimately tell us whether a row should be visible or not.