Dead tuples (dead rows) caused by
DELETE
Example:
test=# insert into accounts values (1, 'Oli', 'secret', 'oli@fromdual.com', current_timestamp, current_timestamp);
INSERT 0 1
test=# insert into accounts values (1, 'Oli', 'secret', 'oli@fromdual.com', current_timestamp, current_timestamp);
ERROR: duplicate key value violates unique constraint "accounts_pkey"
DETAIL: Key (user_id)=(1) already exists.
test=# insert into accounts values (2, 'Oli', 'secret', 'oli@fromdual.com', current_timestamp, current_timestamp);
ERROR: duplicate key value violates unique constraint "accounts_username_key"
DETAIL: Key (username)=(Oli) already exists.
test=# insert into accounts values (2, 'Oli2', 'secret', 'oli@fromdual.com', current_timestamp, current_timestamp);
ERROR: duplicate key value violates unique constraint "accounts_email_key"
DETAIL: Key (email)=(oli@fromdual.com) already exists.
test=# insert into accounts values (2, 'Oli2', 'secret', 'oli2@fromdual.com', current_timestamp, current_timestamp);
INSERT 0 1
test=# insert into accounts values (3, 'Oli3', 'secret', 'oli3@fromdual.com', current_timestamp, current_timestamp);
INSERT 0 1
How to find them:
test=# select * from accounts;
user_id | username | password | email | created_on | last_login
---------+----------+----------+-------------------+----------------------------+----------------------------
1 | Oli | secret | oli@fromdual.com | 2024-06-19 09:20:51.981428 | 2024-06-19 09:20:51.981428
2 | Oli2 | secret | oli2@fromdual.com | 2024-06-19 09:21:13.17121 | 2024-06-19 09:21:13.17121
3 | Oli3 | secret | oli3@fromdual.com | 2024-06-19 09:21:21.720801 | 2024-06-19 09:21:21.720801
(3 rows)
test=# SELECT schemaname, relname, n_live_tup, n_dead_tup
test-# FROM pg_stat_all_tables
test-# WHERE relname = 'accounts'
test-# ;
schemaname | relname | n_live_tup | n_dead_tup
------------+----------+------------+------------
public | accounts | 3 | 3
(1 row)
How to fix them:
test=# vacuum (verbose, analyze) accounts;
INFO: vacuuming "test.public.accounts"
INFO: finished vacuuming "test.public.accounts": index scans: 1
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 3 removed, 3 remain, 0 are dead but not yet removable
removable cutoff: 708094, which was 0 XIDs old when operation ended
new relfrozenxid: 708088, which is 707351 XIDs ahead of previous value
index scan needed: 1 pages from table (100.00% of total) had 3 dead item identifiers removed
index "accounts_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "accounts_username_key": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "accounts_email_key": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 78.715 MB/s, avg write rate: 98.394 MB/s
buffer usage: 15 hits, 4 misses, 5 dirtied
WAL usage: 9 records, 2 full page images, 15897 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: analyzing "public.accounts"
INFO: "accounts": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
VACUUM
Check for success:
test=# SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_all_tables
WHERE relname = 'accounts'
;
schemaname | relname | n_live_tup | n_dead_tup
------------+----------+------------+------------
public | accounts | 3 | 0
(1 row)