Tuple
MVCC - Multiversion Concurrency Control
- Actually tuple is a row in the table.
- Row is what will be back in a SELECT query.
- Tuple is how it managed.
- 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';
No comments:
Post a Comment