Pingu
Computer MySQL PostgreSQL Books Publications
Spielereien Kanu Business TopoDB POI Klettersteigen History TransPool Thermal Baden Brokenstuben Goldwaschen
Blog Contact
Shinguz
Google
/ch/open

PostgreSQL VACUUM

VACUUM

Dead tuples (dead rows) caused by

  • DELETE
  • PK / UK violation

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)