Как и зачем MVCC мешает нам пользоваться базой данных и почему это не плохо

Несмотря на распространение различных NoSQL решений для хранения данных в последние годы, реляционные базы данных остаются популярным выбором среди разработчиков из–за своей универсальности и предсказуемости. Большинство из них следуют принципам ACID, который дает нам определенные гарантии. Однако, сделать так, чтобы БД им соответствовала не так то просто, и мы рассмотрим один из подходов к этому — MVCC. Иногда его работа приводит к неожиданным сайд–эффектам, и в докладе мы рассмотрим несколько примеров.

В качестве примеров я буду использовать PostgreSQL, но MVCC работает одинаково практически везде, так что адаптировать новые знания к своей любимой БД будет довольно просто. Доклад будет полезен как начинающим, так и продвинутым бэкэндерам.

Transcript

1. DmitryTsepelev
   Fall Saint P 2021 meetup
   Задача: нужно сбросить всем
   пользователям флаг подтверждения
   почты
   1
   

   View Slide

2. DmitryTsepelev
   Fall Saint P 2021 meetup 2
   UPDATE users SET email_conf
   i
   rmed = FALSE;
   

   View Slide

3. DmitryTsepelev
   Fall Saint P 2021 meetup 3
   А что если данных много? 🤔
   SELECT pg_size_pretty(pg_total_relation_size('users'));
   
   
   - -
   200 Gb
   

   View Slide

4. DmitryTsepelev
   Fall Saint P 2021 meetup
   Обновляем в цикле
   4
   User.in_batches do |batch|
   
   
   batch.update_all(email_conf
   i
   rmed: false)
   
   
   end
   
   
   SELECT users.id FROM users WHERE users.id > 0 ORDER BY users.id ASC LIMIT 1000;
   
   
   UPDATE users SET email_conf
   i
   rmed = FALSE WHERE users.id IN (
   . . .
   );
   
   
   SELECT users.id FROM users WHERE users.id > 1000 ORDER BY users.id ASC LIMIT 1000;
   
   
   UPDATE users SET email_conf
   i
   rmed = FALSE WHERE users.id IN (
   . . .
   );
   

   View Slide

5. DmitryTsepelev
   Fall Saint P 2021 meetup 5
   Ой, места на диске не осталось
   SELECT pg_size_pretty(pg_total_relation_size('users'));
   
   
   - -
   400 Gb
   

   View Slide

6. DmitryTsepelev
   Fall Saint P 2021 meetup 6
   @dmitrytsepelev
   

   View Slide

7. Как и зачем MVCC мешает нам
   пользоваться базой данных
   и почему это не плохо
   

   View Slide

8. DmitryTsepelev
   Fall Saint P 2021 meetup 8
   

   View Slide

9. DmitryTsepelev
   Fall Saint P 2021 meetup
   БД и разработчики
   • есть таблички с данными;
   
   
   • данные можно изменять и запрашивать;
   
   
   • таблички можно объединять (JOINs) в запросах;
   
   
   • таблички связаны с помощью foreign keys;
   
   
   • есть constraints для валидации данных.
   9
   

   View Slide

10. DmitryTsepelev
    Fall Saint P 2021 meetup
    Делаем биллинг
    def handle_payment(user, payment)
    
    
    payment.update(processed: true)
    
    
    subscription_active_until =
    
    
    [user.subscription_active_until, Date.current].max + payment.days_covered
    
    
    user.update(subscription_active_until: subscription_active_until)
    
    
    end
    
    
    UPDATE payments SET processed = true WHERE id = ?;
    
    
    UPDATE users SET subscription_active_until = '2021-10-23' WHERE id = ?;
    
    
    10
    

    View Slide

11. ACID
    

    View Slide

12. DmitryTsepelev
    Fall Saint P 2021 meetup
    ACID
    • Atomicity
    
    
    • Consistency
    
    
    • Isolation
    
    
    • Durability
    12
    

    View Slide

13. DmitryTsepelev
    Fall Saint P 2021 meetup
    Делаем биллинг
    def handle_payment(user, payment)
    
    
    payment.update(processed: true)
    
    
    subscription_active_until =
    
    
    [user.subscription_active_until, Date.current].max + payment.days_covered
    
    
    user.update(subscription_active_until: subscription_active_until)
    
    
    end
    13
    А вдруг user.update выбросит исключение? 🤔
    

    View Slide

14. DmitryTsepelev
    Fall Saint P 2021 meetup
    Atomicity (доделываем биллинг)
    def handle_payment(user, payment)
    
    
    ActiveRecord
    : :
    Base.transaction do
    
    
    payment.update(processed: true)
    
    
    subscription_active_until =
    
    
    [user.subscription_active_until, Date.current].max + payment.days_covered
    
    
    user.update(subscription_active_until: subscription_active_until)
    
    
    end
    
    
    end
    
    
    BEGIN;
    
    
    UPDATE payments SET processed = true WHERE id = ?;
    
    
    UPDATE users SET subscription_active_until = '2021-10-23' WHERE id = ?;
    
    
    COMMIT;
    14
    

    View Slide

15. DmitryTsepelev
    Fall Saint P 2021 meetup
    Транзакция
    • переводит БД из одного состояния в другое;
    
    
    • может содержать несколько операций;
    
    
    • фиксируется только если все операции прошли успешно;
    
    
    • фиксация изменений происходит одновременно;
    
    
    • работает независимо от других транзакций.
    15
    

    View Slide

16. DmitryTsepelev
    Fall Saint P 2021 meetup
    Спин–офф: локи
    def handle_payment(user, payment)
    
    
    user.with_lock do
    
    
    return if payment.reload.processed? # на случай если платеж уже запроцессили
    
    
    payment.update(processed: true)
    
    
    subscription_active_until =
    
    
    [user.subscription_active_until, Date.current].max + payment.days_covered
    
    
    user.update(subscription_active_until: subscription_active_until)
    
    
    end
    
    
    end
    
    
    BEGIN;
    
    
    SELECT * FROM payments WHERE id = ? FOR UPDATE;
    
    
    UPDATE payments SET processed = true WHERE id = ?;
    
    
    UPDATE users SET subscription_active_until = '2021-10-23' WHERE id = ?;
    
    
    COMMIT;
    16
    

    View Slide

17. DmitryTsepelev
    Fall Saint P 2021 meetup
    ACID
    • Atomicity
    
    
    • Consistency
    
    
    • Isolation
    
    
    • Durability
    17
    

    View Slide

18. DmitryTsepelev
    Fall Saint P 2021 meetup
    Целостность и консистентность
    • целостные данные соответствуют ограничениям БД (UNIQUE, NOT
    NULL, …);
    
    
    • корректные данные соответствуют всем ограничениям
    приложения;
    
    
    • БД не всегда может гарантировать корректность.
    18
    

    View Slide

19. DmitryTsepelev
    Fall Saint P 2021 meetup
    ACID
    • Atomicity
    
    
    • Consistency
    
    
    • Isolation
    
    
    • Durability
    19
    

    View Slide

20. DmitryTsepelev
    Fall Saint P 2021 meetup
    Isolation
    20
    BEGIN;
    
    
    UPDATE payments SET processed = true WHERE id = 1;
    
    
    SELECT processed FROM payments WHERE id = 1;
    
    
    - -
    true
    
    
    COMMIT;
    BEGIN;
    
    
    SELECT processed FROM payments WHERE id = 1;
    
    
    - -
    false
    
    
    COMMIT;
    
    
    BEGIN;
    
    
    SELECT processed FROM payments WHERE id = 1;
    
    
    - -
    true
    
    
    

    View Slide

21. DmitryTsepelev
    Fall Saint P 2021 meetup
    ACID
    • Atomicity
    
    
    • Consistency
    
    
    • Isolation
    
    
    • Durability
    21
    

    View Slide

22. Как БД хранит данные
    

    View Slide

23. DmitryTsepelev
    Fall Saint P 2021 meetup
    Как хранятся данные на диске
    23
    users
    id name
    1 John
    2 Jane
    42 (< 1GB)
    users forks
    Database
    orders
    id user_id
    1 2
    123 (< 1GB)
    orders forks
    Disk
    

    View Slide

24. DmitryTsepelev
    Fall Saint P 2021 meetup
    Как хранятся данные на диске
    24
    users
    id name
    1 John
    2 Jane
    Disk
    42 (< 1GB)
    users forks
    Database
    orders
    id user_id
    1 2
    123 (1GB)
    orders forks
    123.1 (< 1GB)
    Fork
    fi
    le
    page 1 (8 kB) page 2 (8 kB)
    page 3 (8 kB)
    

    View Slide

25. DmitryTsepelev
    Fall Saint P 2021 meetup
    Tuple
    • tuple = header + user data;
    
    
    • tuple почти никогда не обновляется (БД может, мы нет);
    
    
    • DELETE = UPDATE xmax;
    
    
    • UPDATE = INSERT + DELETE.
    25
    xmin xmax ctid infomask id name
    123 125 (3, 15) 111 1 Jon
    123 0 (4, 1) 111 2 Jane
    125 0 (3, 15) 111 1 Jonh
    

    View Slide

26. DmitryTsepelev
    Fall Saint P 2021 meetup
    Tuple header
    26
    BEGIN;
    
    
    SELECT txid_current();
    
    
    ┌──────────────┐
    
    
    │ txid_current │
    
    
    ├──────────────┤
    
    
    │ 8249614 │
    
    
    └──────────────┘
    
    
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    SELECT *, ctid, xmin, xmax FROM users;
    
    
    ┌────┬──────────────────┬───────┬─────────┬──────┐
    
    
    │ id │ email │ ctid │ xmin │ xmax │
    
    
    ├────┼──────────────────┼───────┼─────────┼──────┤
    
    
    │ 1 │ john@example.com │ (0,1) │ 8249614 │ 0 │
    
    
    └────┴──────────────────┴───────┴─────────┴──────┘
    
    
    

    View Slide

27. DmitryTsepelev
    Fall Saint P 2021 meetup
    Tuple header
    27
    CREATE EXTENSION pageinspect;
    
    
    SELECT (t_infomask & 256) > 0 AS xmin_commited, *
    
    
    FROM heap_page_items(get_raw_page('users', 0));
    
    
    ┌───────────────┬─────────┬────────┬──────────┬────────┬─────────────┬────────────┬────────┬────────┬───────┬────────┐
    
    
    │ xmin_commited │ t_xmin │ t_xmax │ t_f
    i
    eld3 │ t_ctid │ t_infomask2 │ t_infomask │ t_hoff │ t_bits │ t_oid │ t_data │
    
    
    ├───────────────┼─────────┼────────┼──────────┼────────┼─────────────┼────────────┼────────┼────────┼───────┼────────┤
    
    
    │ f │ 8249614 │ 0 │ 11 │ (0,1) │ 2 │ 2050 │ 24 │ ¤ │ ¤ │
    . . .
    │
    
    
    └───────────────┴─────────┴────────┴──────────┴────────┴─────────────┴────────────┴────────┴────────┴───────┴────────┘
    
    
    COMMIT;
    
    
    ┌───────────────┬─────────┬────────┬──────────┬────────┬─────────────┬────────────┬────────┬────────┬───────┬────────┐
    
    
    │ xmin_commited │ t_xmin │ t_xmax │ t_f
    i
    eld3 │ t_ctid │ t_infomask2 │ t_infomask │ t_hoff │ t_bits │ t_oid │ t_data │
    
    
    ├───────────────┼─────────┼────────┼──────────┼────────┼─────────────┼────────────┼────────┼────────┼───────┼────────┤
    
    
    │ t │ 8249614 │ 0 │ 11 │ (0,1) │ 2 │ 2050 │ 24 │ ¤ │ ¤ │
    . . .
    │
    
    
    └───────────────┴─────────┴────────┴──────────┴────────┴─────────────┴────────────┴────────┴────────┴───────┴────────┘
    

    View Slide

28. DmitryTsepelev
    Fall Saint P 2021 meetup
    Update tuple
    28
    BEGIN;
    
    
    SELECT txid_current();
    
    
    ┌──────────────┐
    
    
    │ txid_current │
    
    
    ├──────────────┤
    
    
    │ 8249618 │
    
    
    └──────────────┘
    
    
    UPDATE users SET email = 'john.doe@example.com' WHERE id = 1;
    
    
    SELECT *, ctid, xmin, xmax FROM users;
    
    
    ┌────┬──────────────────────┬───────┬─────────┬──────┐
    
    
    │ id │ email │ ctid │ xmin │ xmax │
    
    
    ├────┼──────────────────────┼───────┼─────────┼──────┤
    
    
    │ 1 │ john.doe@example.com │ (0,2) │ 8249618 │ 0 │
    
    
    └────┴──────────────────────┴───────┴─────────┴──────┘
    
    
    

    View Slide

29. DmitryTsepelev
    Fall Saint P 2021 meetup
    Update tuple
    29
    SELECT t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('users', 0));
    
    
    ┌─────────┬─────────┬────────┐
    
    
    │ t_xmin │ t_xmax │ t_ctid │
    
    
    ├─────────┼─────────┼────────┤
    
    
    │ 8249616 │ 8249618 │ (0,2) │
    
    
    │ 8249619 │ 0 │ (0,2) │
    
    
    └─────────┴─────────┴────────┘
    

    View Slide

30. DmitryTsepelev
    Fall Saint P 2021 meetup
    Delete tuple
    30
    BEGIN;
    
    
    SELECT txid_current();
    
    
    ┌──────────────┐
    
    
    │ txid_current │
    
    
    ├──────────────┤
    
    
    │ 8249620 │
    
    
    └──────────────┘
    
    
    DELETE FROM users WHERE id = 1;
    
    
    SELECT t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('users', 0));
    
    
    ┌─────────┬─────────┬────────┐
    
    
    │ t_xmin │ t_xmax │ t_ctid │
    
    
    ├─────────┼─────────┼────────┤
    
    
    │ 8249616 │ 8249619 │ (0,2) │
    
    
    │ 8249619 │ 8249621 │ (0,2) │
    
    
    └─────────┴─────────┴────────┘
    
    
    

    View Slide

31. Multi Version Concurrency
    Control
    

    View Slide

32. DmitryTsepelev
    Fall Saint P 2021 meetup
    MVCC
    • чтение не блокирует запись;
    
    
    • запись не блокирует чтение;
    
    
    • каждая транзакция видит свой «слепок» данных.
    32
    

    View Slide

33. DmitryTsepelev
    Fall Saint P 2021 meetup
    Snapshot
    • физическая копия данных не делается;
    
    
    • «видимые» данные определяются исходя из условий.
    33
    SELECT *
    
    
    FROM users
    
    
    WHERE xmin
    < =
    txid_current()
    
    
    AND (xmax = 0 OR txid_current() < xmax);
    
    
    - - -
    ^^ ^^
    
    
    - - -
    запись еще не удалена запись удалена позже
    
    
    

    View Slide

34. DmitryTsepelev
    Fall Saint P 2021 meetup
    Snapshot
    T1
    T2
    T3
    T4
    S1 S2 S3
    • S1: 🤷
    
    
    • S2: T1
    
    
    • S3: T1, T2
    34
    

    View Slide

35. DmitryTsepelev
    Fall Saint P 2021 meetup
    Snapshot
    35
    BEGIN;
    
    
    SELECT txid_current();
    
    
    ┌──────────────┐
    
    
    │ txid_current │
    
    
    ├──────────────┤
    
    
    │ 8257515 │
    
    
    └──────────────┘
    
    
    SELECT txid_current_snapshot();
    
    
    ┌──────────────────────────────┐
    
    
    │ txid_current_snapshot │
    
    
    ├──────────────────────────────┤
    
    
    │ 8257515
    :
    8257515
    :
    8257515 │
    
    
    └──────────────────────────────┘
    
    
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    SELECT *, ctid, xmin, xmax FROM users;
    
    
    ┌────┬──────────────────┬───────┬─────────┬──────┐
    
    
    │ id │ email │ ctid │ xmin │ xmax │
    
    
    ├────┼──────────────────┼───────┼─────────┼──────┤
    
    
    │ 2 │ john@example.com │ (0,3) │ 8257515 │ 0 │
    
    
    └────┴──────────────────┴───────┴─────────┴──────┘
    xmin
    xmax
    список активных транзакций
    

    View Slide

36. DmitryTsepelev
    Fall Saint P 2021 meetup
    Savepoints
    36
    BEGIN;
    
    
    SELECT txid_current();
    
    
    ┌──────────────┐
    
    
    │ txid_current │
    
    
    ├──────────────┤
    
    
    │ 8257515 │
    
    
    └──────────────┘
    
    
    SELECT txid_current_snapshot();
    
    
    ┌──────────────────────────────┐
    
    
    │ txid_current_snapshot │
    
    
    ├──────────────────────────────┤
    
    
    │ 8257515
    :
    8257515
    :
    8257515 │
    
    
    └──────────────────────────────┘
    
    
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    SELECT *, ctid, xmin, xmax FROM users;
    
    
    ┌────┬──────────────────┬───────┬─────────┬──────┐
    
    
    │ id │ email │ ctid │ xmin │ xmax │
    
    
    ├────┼──────────────────┼───────┼─────────┼──────┤
    
    
    │ 2 │ john@example.com │ (0,3) │ 8257516 │ 0 │
    
    
    └────┴──────────────────┴───────┴─────────┴──────┘
    

    View Slide

37. DmitryTsepelev
    Fall Saint P 2021 meetup
    Savepoints
    • механизм для "вложенных" транзакций;
    
    
    • позволяет откатить часть изменений, не откатывая всю
    транзакцию;
    
    
    • txid_current возвращает идентификатор главной транзакции!
    37
    BEGIN;
    
    
    SAVEPOINT s1;
    
    
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    ROLLBACK TO s1;
    

    View Slide

38. DmitryTsepelev
    Fall Saint P 2021 meetup
    Где кроме PSQL используется MVCC?
    • MySQL (InnoDB/Falcon/Archive storage)
    
    
    • MariaDB (XtraDB/PBXT)
    
    
    • MongoDB (WiredTiger storage)
    
    
    • Oracle (> 4)
    
    
    • CouchDB
    
    
    • Couchbase
    
    
    • …
    38
    

    View Slide

39. Спецэффекты
    

    View Slide

40. DmitryTsepelev
    Fall Saint P 2021 meetup
    Аномалии изоляции
    • Dirty Read
    
    
    • Nonrepeatable Read
    
    
    • Phantom Read
    
    
    • Serialization Anomaly
    40
    

    View Slide

41. DmitryTsepelev
    Fall Saint P 2021 meetup
    Уровни изоляции транзакций
    41
    Isolation Level Dirty Read
    Nonrepeatable
    Reads
    Phantom Reads
    Serialization
    Anomaly
    Read
    Uncommited
    (not PG) (not PG) x x
    Read Commited x x x
    Repeatable Read (not PG) x
    Serializable
    

    View Slide

42. DmitryTsepelev
    Fall Saint P 2021 meetup
    Как работают уровни изоляции?
    42
    • в Read Committed снепшот создается перед каждым запросом;
    
    
    • в Repeatable Read и Serializable снепшот создается только один раз.
    

    View Slide

43. DmitryTsepelev
    Fall Saint P 2021 meetup
    Как работает Serializable
    43
    • гарантируется, что результат работы транзакций не зависит от
    порядка выполнения;
    
    
    • для полностью корректной работы нужно обходить граф
    зависимостей;
    
    
    • упрощенно: ищем ситуации, когда одна транзакция изменила
    строку, а другая — прочитала предыдущую версию этой строки;
    
    
    • простой подход может вызывать ложно–положительные
    срабатывания.
    

    View Slide

44. DmitryTsepelev
    Fall Saint P 2021 meetup
    Почему не использовать Serializable всегда?
    44
    • придется использовать его для ВСЕХ транзакций;
    
    
    • придется всегда ловить исключение и перезапускать операцию;
    
    
    • Serializable работает медленнее других уровней.
    

    View Slide

45. DmitryTsepelev
    Fall Saint P 2021 meetup 45
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    INSERT INTO users (email) VALUES ('jane@example.com');
    
    
    INSERT INTO users (email) VALUES ('mike@example.com');
    
    
    SELECT * FROM users;
    
    
    ┌────┬──────────────────┐
    
    
    │ id │ email │
    
    
    ├────┼──────────────────┤
    
    
    │ 1 │ john@example.com │
    
    
    │ 2 │ jane@example.com │
    
    
    │ 3 │ mike@example.com │
    
    
    └────┴──────────────────┘
    
    
    ⏱
    
    
    SELECT * FROM users;
    
    
    ❓ ❓ ❓
    

    View Slide

46. DmitryTsepelev
    Fall Saint P 2021 meetup
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    INSERT INTO users (email) VALUES ('jane@example.com');
    
    
    INSERT INTO users (email) VALUES ('mike@example.com');
    
    
    SELECT * FROM users;
    
    
    ┌────┬──────────────────┐
    
    
    │ id │ email │
    
    
    ├────┼──────────────────┤
    
    
    │ 1 │ john@example.com │
    
    
    │ 2 │ jane@example.com │
    
    
    │ 3 │ mike@example.com │
    
    
    └────┴──────────────────┘
    
    
    ⏱
    
    
    SELECT * FROM users;
    
    
    ┌────┬─────────────────────┐
    
    
    │ id │ email │
    
    
    ├────┼─────────────────────┤
    
    
    │ 2 │ jane@example.com │
    
    
    │ 3 │ michael@example.com │
    
    
    │ 1 │ ivan@example.com │
    
    
    └────┴─────────────────────┘
    Проблема: нет явного порядка
    46
    

    View Slide

47. DmitryTsepelev
    Fall Saint P 2021 meetup
    INSERT INTO users (email) VALUES ('john@example.com');
    
    
    INSERT INTO users (email) VALUES ('jane@example.com');
    
    
    INSERT INTO users (email) VALUES ('mike@example.com');
    
    
    SELECT * FROM users;
    
    
    ┌────┬──────────────────┐
    
    
    │ id │ email │
    
    
    ├────┼──────────────────┤
    
    
    │ 1 │ john@example.com │
    
    
    │ 2 │ jane@example.com │
    
    
    │ 3 │ mike@example.com │
    
    
    └────┴──────────────────┘
    
    
    UPDATE users SET email = 'ivan@example.com' WHERE id = 1;
    
    
    SELECT * FROM users;
    
    
    ┌────┬─────────────────────┐
    
    
    │ id │ email │
    
    
    ├────┼─────────────────────┤
    
    
    │ 2 │ jane@example.com │
    
    
    │ 3 │ michael@example.com │
    
    
    │ 1 │ ivan@example.com │
    
    
    └────┴─────────────────────┘
    Проблема: нет явного порядка
    47
    

    View Slide

48. DmitryTsepelev
    Fall Saint P 2021 meetup
    SELECT t_xmin, t_xmax, t_ctid
    
    
    FROM heap_page_items(get_raw_page('users', 0));
    
    
    ┌─────────┬─────────┬────────┐
    
    
    │ t_xmin │ t_xmax │ t_ctid │
    
    
    ├─────────┼─────────┼────────┤
    
    
    │ 8257533 │ 8257536 │ (0,4) │
    
    
    │ 8257534 │ 0 │ (0,2) │
    
    
    │ 8257535 │ 0 │ (0,3) │
    
    
    │ 8257536 │ 0 │ (0,4) │
    
    
    └─────────┴─────────┴────────┘
    Проблема: нет явного порядка
    48
    

    View Slide

49. DmitryTsepelev
    Fall Saint P 2021 meetup
    Проблема: нет явного порядка
    49
    

    View Slide

50. DmitryTsepelev
    Fall Saint P 2021 meetup
    Фича: определяем была ли запись создана в upsert
    50
    INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
    
    
    INSERT INTO users (name, email)
    
    
    VALUES
    
    
    ('John!', 'john@example.com'),
    
    
    ('Jane', 'jane@example.com')
    
    
    ON CONFLICT (email)
    
    
    DO UPDATE SET name = EXCLUDED.name
    
    
    RETURNING id, (xmax = '0') AS inserted, name AS new_name;
    
    
    ┌─────────┬──────────┬──────────┐
    
    
    │ id │ inserted │ new_name │
    
    
    ├─────────┼──────────┼──────────┤
    
    
    │ 1 │ f │ John! │
    
    
    │ 2 │ t │ Jane │
    
    
    └─────────┴──────────┴──────────┘
    

    View Slide

51. DmitryTsepelev
    Fall Saint P 2021 meetup
    Фича: определяем была ли запись создана в upsert
    51
    select *, xmin, xmax, ctid from users;
    
    
    ┌─────────┬──────────────────┬───────┬─────────┬─────────┬───────┐
    
    
    │ id │ email │ name │ xmin │ xmax │ ctid │
    
    
    ├─────────┼──────────────────┼───────┼─────────┼─────────┼───────┤
    
    
    │ 1 │ john@example.com │ John! │ 8843025 │ 8843025 │ (0,2) │
    
    
    │ 2 │ jane@example.com │ Jane │ 8843025 │ 0 │ (0,3) │
    
    
    └─────────┴──────────────────┴───────┴─────────┴─────────┴───────┘
    
    
    🤔🤔🤔
    

    View Slide

52. DmitryTsepelev
    Fall Saint P 2021 meetup
    Фича: определяем была ли запись создана в upsert
    52
    select *, xmin, xmax, ctid from users;
    
    
    ┌─────────┬──────────────────┬───────┬─────────┬─────────┬───────┐
    
    
    │ id │ email │ name │ xmin │ xmax │ ctid │
    
    
    ├─────────┼──────────────────┼───────┼─────────┼─────────┼───────┤
    
    
    │ 1 │ john@example.com │ John! │ 8843025 │ 8843025 │ (0,2) │
    
    
    │ 2 │ jane@example.com │ Jane │ 8843025 │ 0 │ (0,3) │
    
    
    └─────────┴──────────────────┴───────┴─────────┴─────────┴───────┘
    
    
    🤔🤔🤔
    В некоторых случаях xmax может означать всего лишь лок:
    
    
    https://stackover
    fl
    ow.com/a/39204667
    

    View Slide

53. DmitryTsepelev
    Fall Saint P 2021 meetup 53
    INSERT INTO users (email)
    
    
    SELECT random()
    : :
    text
    
    
    FROM generate_series(1, 1000000);
    
    
    SELECT pg_size_pretty(pg_total_relation_size('users'));
    
    
    - -
    72 MB
    
    
    UPDATE users SET email = 'something';
    
    
    SELECT pg_size_pretty(pg_total_relation_size('users'));
    
    
    ❓ ❓ ❓
    

    View Slide

54. DmitryTsepelev
    Fall Saint P 2021 meetup
    Проблема: большой апдейт может раздуть таблицу
    54
    INSERT INTO users (email)
    
    
    SELECT random()
    : :
    text
    
    
    FROM generate_series(1, 1000000);
    
    
    SELECT pg_size_pretty(pg_total_relation_size('users'));
    
    
    - -
    72 MB
    
    
    UPDATE users SET email = 'something';
    
    
    SELECT pg_size_pretty(pg_total_relation_size('users'));
    
    
    - -
    135 MB
    

    View Slide

55. DmitryTsepelev
    Fall Saint P 2021 meetup
    Проблема: удаление данных из таблицы не освобождает место на диске
    55
    VACUUM users;
    
    
    SELECT pg_size_pretty(
    
    
    pg_total_relation_size('users')
    
    
    );
    
    
    - -
    135 MB
    
    
    VACUUM FULL users;
    
    
    SELECT pg_size_pretty(
    
    
    pg_total_relation_size('users')
    
    
    );
    
    
    - -
    64 MB
    

    View Slide

56. DmitryTsepelev
    Fall Saint P 2021 meetup
    Проблема: ID транзакций могут «кончиться»
    • тип ID транзакции XID, 32 бита (~4 000 000 000 значений);
    
    
    • больший тип увеличит место для хранения заголовка тапла;
    
    
    • БД начинает счет заново при достижении максимального
    значения;
    
    
    • VACUUM «замораживает» (freeze) старые таплы.
    56
    

    View Slide

57. DmitryTsepelev
    Fall Saint P 2021 meetup
    Выводы
    • MVCC нужен для обеспечения изоляции транзакций;
    
    
    • на низком уровне данные не обновляются, а записываются;
    
    
    • реализация MVCC иногда приводит к неожиданным сайд–
    эффектам.
    57
    

    View Slide

58. DmitryTsepelev
    Fall Saint P 2021 meetup
    Куда пойти дальше
    • Цикл статей про MVCC:
    
    
    • 🇬🇧 https://postgrespro.com/blog/pgsql/5967856;
    
    
    • 🇷🇺 https://habr.com/ru/company/postgrespro/blog/442804/;
    
    
    • The Internals of PostgreSQL — http://www.interdb.jp/pg/;
    
    
    • Serializable Snapshot Isolation in PostgreSQL — https://arxiv.org/pdf/
    1208.4179.pdf;
    
    
    • Database Internals — https://www.databass.dev.
    58
    

    View Slide

59. evl.ms/blog
    @dmitrytsepelev @evilmartians
    evl.ms/telegram
    Спасибо!
    @dmitrytsepelev
    59
    

    View Slide