PostgreSQL Tuples and MVCC

Tuple

• Actually tuple is a row in the table.
• Row is what will be back in a SELECT query.
• Tuple is how it managed.

MVCC - Multiversion Concurrency Control

• MVCC enables operations to occur concurrently by utilizing snapshots of the database.
• In MVCC, When you update or delete any row, Internally It creates the new row and mark old row as unused.
• The tradeoff is that it creates dead rows / dead tuples.
• MVCC is a little bit similar to READ_COMMITTED_SNAPSHOT in SQL Server.

Vacuum

• Vacuuming is needed to get rid of old (dead) tuples which are created when you change/delete rows.
• In PostgreSQL, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.
• It doesn’t reduce the size of the files.
• except for the case when several pages at the end of the file are completely free.
• Space is freed up inside the data pages, which can later be used to insert new tuples.
• Vacuuming runs:
• Manually with the VACUUM command
• Autovacuum background process
• VACUUM update statistics when the configuration parameter “track_counts” is set to “on”.
  
• Running it too often will create unnecessary load on the system. But running vacuum too rare, with a large volume of changes, the files may grow significantly in size.
• VACUUM update statistics when the configuration parameter “track_counts” is set to “on”.
• This parameter is under “Runtime Statistics” section in postgresql.conf configuration file.
• To check what is the current value run: SELECT name, setting FROM pg_settings WHERE name='track_counts';

https://edu.postgrespro.com/2dintro/08_admin_maintenance.pdf