Dead Tuples in PostgreSQL.

Today we are going to learn about Dead Tuples in PostgreSQL. As we know PostgreSQL is based on MVCC Architecture.

MVCC is a Multi Version Concurrency Control basically "A single rows has multiple versions."
E.g, If a user insert a record of a Person whose AGE is 21. Now, he update the record with AGE 22, so what PostgreSQL does here, PostgreSQL stored those two records with the different versions.
Old version: Age = 21
New version: Age = 22
By default user can only select the new version of the record, and old version marked as an unused record.
That types of record called Dead Tuple or Dead Row.

How to find Live Tuples or Dead Tuples using pg_stat_user_tables.

select n_live_tup, n_dead_tup, relname from pg_stat_user_tables;

Find tuples from a specific table.

SELECT n_live_tup, n_dead_tup, relname FROM
pg_stat_user_tables where relname = 'users';

using Alias

SELECT relname AS Table_Name, n_live_tup AS Live_Tuples,
n_dead_tup AS Dead_Tuples FROM
pg_stat_user_tables where relname = 'users';

SQL Result:

How to avoid to create Dead Tuple while Inserting the duplicate data.

If a table have some unique constraint and a user trying to Insert the same data again, PostgreSQL will return an error >
Uniqueness violation. duplicate key value violates unique constraint. We can avoid this by using ON CONFLICT DO NOTHING clause.

INSERT INTO users VALUES ('name', 'email@gmail.com')
ON CONFLICT DO NOTHING;

It'll return INSERT 0 0 indicates that nothing was inserted in the table, the query didn't error out. In the case of ON CONFLICT DO NOTHING no dead tuples are generated because of the pre-check.

How to delete Dead Tuples using VACUUM -

VACUUM users;

It'll free up the space within the users table and only users table can use this space. If you want to use this free space by system then this command should be run.

VACUUM FULL users;

Read More about VACUUM.

Thank you <👨‍💻/>