База данных и таблица

=> CREATE DATABASE admin_monitoring;
CREATE DATABASE
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
=> CREATE TABLE t(n numeric);
CREATE TABLE
=> INSERT INTO t SELECT 1 FROM generate_series(1,1000);
INSERT 0 1000
=> DELETE FROM t;
DELETE 1000

Статистика обращений

=> \c
You are now connected to database "admin_monitoring" as user "postgres".
=> \x
Expanded display is on.
=> SELECT * FROM pg_stat_all_tables WHERE relid='t'::regclass;
-[ RECORD 1 ]-------+-------
relid               | 16436
schemaname          | public
relname             | t
seq_scan            | 1
seq_tup_read        | 1000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 1000
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 1000
n_mod_since_analyze | 2000
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Мы вставили 1000 строк (n_tup_ins = 1000), удалили 1000 строк (n_tup_del = 1000).

После этого не осталось активных версий строк (n_live_tup = 0), все 1000 строк не актуальны на текущий момент (n_dead_tup = 1000).

Очистка

=> VACUUM;
VACUUM
=> SELECT * FROM pg_stat_all_tables WHERE relid='t'::regclass;
-[ RECORD 1 ]-------+------------------------------
relid               | 16436
schemaname          | public
relname             | t
seq_scan            | 1
seq_tup_read        | 1000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 1000
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 2000
last_vacuum         | 2019-03-31 15:24:20.714225+03
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Неактуальные версии строк убраны при очистке (n_dead_tup = 0), очистка обрабатывала таблицу один раз (vacuum_count = 1).

Взаимоблокировка

=> INSERT INTO t VALUES (1),(2);
INSERT 0 2

Одна транзакция блокирует первую строку таблицы...

postgres$ psql 
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
=> BEGIN;
BEGIN
=> UPDATE t SET n=10 WHERE n=1;
UPDATE 1

Затем другая транзакция блокирует вторую строку...

postgres$ psql 
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
=> BEGIN;
BEGIN
=> UPDATE t SET n=200 WHERE n=2;
UPDATE 1

Теперь первая транзакция пытается изменить вторую строку и ждет ее освобождения...

=> UPDATE t SET n=20 WHERE n=2;

А вторая транзакция пытается изменить первую строку...

=> UPDATE t SET n=100 WHERE n=1;

...и происходит взаимоблокировка.

UPDATE 1
ERROR:  deadlock detected
DETAIL:  Process 12389 waits for ShareLock on transaction 598; blocked by process 12457.
Process 12457 waits for ShareLock on transaction 597; blocked by process 12389.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "t"

Информация в журнале

postgres$ tail -n 8 /home/postgres/logfile
2019-03-31 15:24:25.248 MSK [12389] ERROR:  deadlock detected
2019-03-31 15:24:25.248 MSK [12389] DETAIL:  Process 12389 waits for ShareLock on transaction 598; blocked by process 12457.
	Process 12457 waits for ShareLock on transaction 597; blocked by process 12389.
	Process 12389: UPDATE t SET n=20 WHERE n=2;
	Process 12457: UPDATE t SET n=100 WHERE n=1;
2019-03-31 15:24:25.248 MSK [12389] HINT:  See server log for query details.
2019-03-31 15:24:25.248 MSK [12389] CONTEXT:  while updating tuple (0,2) in relation "t"
2019-03-31 15:24:25.248 MSK [12389] STATEMENT:  UPDATE t SET n=20 WHERE n=2;