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

=> 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         | 2018-12-24 17:28:30.758598+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 9381 waits for ShareLock on transaction 598; blocked by process 9449.
Process 9449 waits for ShareLock on transaction 597; blocked by process 9381.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "t"

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

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