=== Стенд и версии ===
api mtusi-messenger:lab2 Up About a minute (healthy)
postgres postgres:17.7-alpine@sha256:bb377b7239d2774ac8cc76f481596ce96c5a6b5e9d141f6d0a0ee371a6e7c0f2 Up About a minute (healthy)
valkey valkey/valkey:8.1-alpine@sha256:081c2f5cb575efc901aa80ff9cdbd1ec6a301682fd35e1ebb4b0990a4a4a8507 Up Less than a second (health: starting)
Darwin arm64
Docker version 29.7.2, build a7dcaa6

=== 1. Засев истории: цель 120000 сообщений, чаты неравномерные ===
  seed_duration  | seeded_rows | target_total | messages_total 
-----------------+-------------+--------------+----------------
 00:00:00.637022 |       66707 |       120000 |         120000
(1 row)


=== 2. Сравнение индексов и цены записи (каждый вариант в откатываемой транзакции) ===
 messages_total 
----------------
         120000
(1 row)

############ Вариант A: messages_history_idx (chat_id, created_at, id) ############
 index_size 
------------
 11 MB
(1 row)

                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..52.46 rows=50 width=89) (actual time=0.015..0.024 rows=50 loops=1)
   Buffers: shared hit=53
   ->  Index Scan using messages_history_idx on messages  (cost=0.42..7478.52 rows=7184 width=89) (actual time=0.014..0.021 rows=50 loops=1)
         Index Cond: ((chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid) AND (ROW(created_at, id) > ROW('2026-10-23 20:11:34.584521+00'::timestamp with time zone, 'aa6010bb-02c6-4d17-986b-0f5103547576'::uuid)))
         Buffers: shared hit=53
 Planning:
   Buffers: shared hit=19
 Planning Time: 0.097 ms
 Execution Time: 0.047 ms
(9 rows)

 pages_measured 
----------------
           5000
(1 row)

 hundred_pages_duration 
------------------------
 00:00:00.001077
(1 row)

 insert_10k_duration 
---------------------
 00:00:00.085221
(1 row)

############ Вариант B: только (chat_id) ############
 index_size 
------------
 856 kB
(1 row)

                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3274.42..3274.55 rows=50 width=90) (actual time=2.027..2.031 rows=50 loops=1)
   Buffers: shared hit=475 read=35
   I/O Timings: shared read=0.088
   ->  Sort  (cost=3274.42..3292.14 rows=7088 width=90) (actual time=2.026..2.028 rows=50 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 33kB
         Buffers: shared hit=475 read=35
         I/O Timings: shared read=0.088
         ->  Bitmap Heap Scan on messages  (cost=428.23..3038.96 rows=7088 width=90) (actual time=1.631..1.839 rows=3829 loops=1)
               Recheck Cond: (chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid)
               Filter: (ROW(created_at, id) > ROW('2026-10-23 20:11:34.584521+00'::timestamp with time zone, 'aa6010bb-02c6-4d17-986b-0f5103547576'::uuid))
               Rows Removed by Filter: 34464
               Heap Blocks: exact=475
               Buffers: shared hit=475 read=35
               I/O Timings: shared read=0.088
               ->  Bitmap Index Scan on messages_chat_only_idx  (cost=0.00..426.46 rows=38156 width=0) (actual time=0.397..0.397 rows=38293 loops=1)
                     Index Cond: (chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid)
                     Buffers: shared read=35
                     I/O Timings: shared read=0.088
 Planning:
   Buffers: shared hit=27 read=1
   I/O Timings: shared read=0.009
 Planning Time: 0.123 ms
 Execution Time: 2.056 ms
(24 rows)

 pages_measured 
----------------
           5000
(1 row)

 hundred_pages_duration 
------------------------
 00:00:02.52905
(1 row)

 insert_10k_duration 
---------------------
 00:00:00.080589
(1 row)

############ Вариант C: (chat_id, created_at, id) INCLUDE (sender_id, text) ############
 index_size 
------------
 14 MB
(1 row)

                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..45.99 rows=50 width=89) (actual time=0.014..0.018 rows=50 loops=1)
   Buffers: shared hit=1 read=3
   I/O Timings: shared read=0.005
   ->  Index Only Scan using messages_history_covering_idx on messages  (cost=0.42..6560.61 rows=7197 width=89) (actual time=0.013..0.015 rows=50 loops=1)
         Index Cond: ((chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid) AND (ROW(created_at, id) > ROW('2026-10-23 20:11:34.584521+00'::timestamp with time zone, 'aa6010bb-02c6-4d17-986b-0f5103547576'::uuid)))
         Heap Fetches: 0
         Buffers: shared hit=1 read=3
         I/O Timings: shared read=0.005
 Planning:
   Buffers: shared hit=28 read=1
   I/O Timings: shared read=0.003
 Planning Time: 0.118 ms
 Execution Time: 0.027 ms
(13 rows)

 pages_measured 
----------------
           5000
(1 row)

 hundred_pages_duration 
------------------------
 00:00:00.000734
(1 row)

 insert_10k_duration 
---------------------
 00:00:00.08596
(1 row)

############ Схема после эксперимента (ROLLBACK вернул индексы) ############
      indexname       
----------------------
 messages_history_idx
 messages_pkey
(2 rows)


Самый большой чат: 69ee7d45-68b4-4606-befe-efaeecdedd32

=== 3. Состояние источника до бэкапа ===
   relation   |  rows  
--------------+--------
 chat_members |   1036
 chats        |     68
 messages     | 120000
 sessions     |     45
 users        |     78
(5 rows)

 big_chat_rows |           big_chat_md5           
---------------+----------------------------------
         38293 | f18725746ed1acbdda044e804cdc4c7c
(1 row)

                  id                  |          created_at           |   text   
--------------------------------------+-------------------------------+----------
 39cb2935-f53c-4e54-a75d-8b197126a6b4 | 2026-10-23 20:11:07.699544+00 | load-3-0
 62d3d8a1-fdaa-4a6c-812f-79ef268ada42 | 2026-10-23 20:11:07.700168+00 | load-1-0
 4fa775f1-6033-4d84-9e6c-0f39844e6201 | 2026-10-23 20:11:07.700828+00 | load-0-0
(3 rows)

 marker_rows 
-------------
           0
(1 row)


=== 4. Бэкап pg_dump -Fc ===
backup_seconds=0.743
dump_size=5.7M

=== 5. Запись после бэкапа: граница потери данных ===
в источнике маркеров: 1

=== 6. Новое хранилище: отдельный контейнер и отдельный том ===
/var/run/postgresql:5432 - accepting connections

=== 7. Восстановление в новое хранилище ===
restore_seconds=0.354

=== 8. Сверка восстановленной копии ===
   relation   |  rows  
--------------+--------
 chat_members |   1036
 chats        |     68
 messages     | 120000
 sessions     |     45
 users        |     78
(5 rows)

 big_chat_rows |           big_chat_md5           
---------------+----------------------------------
         38293 | f18725746ed1acbdda044e804cdc4c7c
(1 row)

                  id                  |          created_at           |   text   
--------------------------------------+-------------------------------+----------
 39cb2935-f53c-4e54-a75d-8b197126a6b4 | 2026-10-23 20:11:07.699544+00 | load-3-0
 62d3d8a1-fdaa-4a6c-812f-79ef268ada42 | 2026-10-23 20:11:07.700168+00 | load-1-0
 4fa775f1-6033-4d84-9e6c-0f39844e6201 | 2026-10-23 20:11:07.700828+00 | load-0-0
(3 rows)

 marker_rows 
-------------
           0
(1 row)


=== 9. Итог ===
backup_seconds=0.743 restore_seconds=0.354
messages_source_without_marker=120000 messages_restored=120000
big_chat_md5_source=f18725746ed1acbdda044e804cdc4c7c
big_chat_md5_restored=f18725746ed1acbdda044e804cdc4c7c
RESULT: восстановленная копия совпадает с источником на момент бэкапа
маркеров после бэкапа в восстановленной копии: 0 (ожидается 0: всё, что подтверждено после снимка, в дампе отсутствует)
