2026-10-04
Postgres
postgres
Overview
- Sources
- Где лежат наши данные
- Что такое транзакция
- Почему данных нет в файле сразу
- Что будет, если выдернуть питание
- Два пода? Два потока?
- Мусор после UPDATE
- Индексы наше всё
Sources
Использую готовый опенсорсный проект pagila от MIT https://github.com/devrimgunduz/pagila. Также рекомендую скачать и ознакомиться с кодом PostgreSQL. Версия 18.6 https://github.com/postgres/postgres/tree/REL_18_STABLE.
Our data.
Самое главное в любой базе данных - это наши данные. Давайте посмотрим где они лежат, в каком виде, чтобы приземлить и понимать с чем мы работаем.
Подключаемся к базе данных с помощью команды, обратите внимание, что у вас порт может быть другим.
psql -p 5555 -d pagila
После успешного подключения мы видим, что мы зашли на базу данных pagila=#
tether757@DESKTOP-HHRSD0U:~/pagila$ psql -p 5555 -d pagila
psql (18.6)
Type "help" for help.
Давайте посмотрим какие есть таблицы командой \dt;
pagila=# \dt;
List of tables
Schema | Name | Type | Owner
--------+------------------+-------------------+-----------
public | actor | table | tether757
public | address | table | tether757
public | category | table | tether757
public | city | table | tether757
public | country | table | tether757
public | customer | table | tether757
public | film | table | tether757
public | film_actor | table | tether757
public | film_category | table | tether757
public | inventory | table | tether757
public | language | table | tether757
public | payment | partitioned table | tether757
public | payment_p2022_01 | table | tether757
public | payment_p2022_02 | table | tether757
public | payment_p2022_03 | table | tether757
public | payment_p2022_04 | table | tether757
public | payment_p2022_05 | table | tether757
public | payment_p2022_06 | table | tether757
public | payment_p2022_07 | table | tether757
public | payment_p2022_08 | table | tether757
public | payment_p2022_09 | table | tether757
public | payment_p2022_10 | table | tether757
public | payment_p2022_11 | table | tether757
public | payment_p2022_12 | table | tether757
public | payment_p2023_01 | table | tether757
public | payment_p2023_02 | table | tether757
public | payment_p2023_03 | table | tether757
public | payment_p2023_04 | table | tether757
public | payment_p2023_05 | table | tether757
public | payment_p2023_06 | table | tether757
public | payment_p2023_07 | table | tether757
public | payment_p2023_08 | table | tether757
public | payment_p2023_09 | table | tether757
public | payment_p2023_10 | table | tether757
public | payment_p2023_11 | table | tether757
public | payment_p2023_12 | table | tether757
public | payment_p2024_01 | table | tether757
public | payment_p2024_02 | table | tether757
public | payment_p2024_03 | table | tether757
public | payment_p2024_04 | table | tether757
public | payment_p2024_05 | table | tether757
public | payment_p2024_06 | table | tether757
public | payment_p2024_07 | table | tether757
public | payment_p2024_08 | table | tether757
public | payment_p2024_09 | table | tether757
public | payment_p2024_10 | table | tether757
public | payment_p2024_11 | table | tether757
public | payment_p2024_12 | table | tether757
public | payment_p2025_01 | table | tether757
public | payment_p2025_02 | table | tether757
public | payment_p2025_03 | table | tether757
public | payment_p2025_04 | table | tether757
public | payment_p2025_05 | table | tether757
public | payment_p2025_06 | table | tether757
public | payment_p2025_07 | table | tether757
public | payment_p2025_08 | table | tether757
public | payment_p2025_09 | table | tether757
public | payment_p2025_10 | table | tether757
public | payment_p2025_11 | table | tether757
public | payment_p2025_12 | table | tether757
public | payment_p2026_01 | table | tether757
public | payment_p2026_02 | table | tether757
public | payment_p2026_03 | table | tether757
public | payment_p2026_04 | table | tether757
public | payment_p2026_05 | table | tether757
public | payment_p2026_06 | table | tether757
public | payment_p2026_07 | table | tether757
public | rental | table | tether757
public | staff | table | tether757
public | store | table | tether757
(70 rows)
Рассмотрим таблицу film для примера. Она содержит фильмы. Узнаем местонахождение файлов таблицы film командой select pg_relation_filepath('film');. Select выдаст расположение в виде привычного нам формата base/24686/24757.
pagila=# select pg_relation_filepath('film');
pg_relation_filepath
----------------------
base/24686/24757
(1 row)
Узнаем полный путь и информацию выполнив команды ls -la, добавив в начало $HOME/pgdata
tether757@DESKTOP-HHRSD0U:~$ ls -la $HOME/pgdata/base/24686/24757
-rw------- 1 tether757 tether757 524288 Sep 13 18:37 /home/tether757/pgdata/base/24686/24757
Сменим директорию командой cd /home/tether757/pgdata/base/24686. Вместо моего пользователя tether757 подставьте своего и также уберите последний кусок пути, оставив только 24686. Давайте посмотрим содержание директории командой ls.
tether757@DESKTOP-HHRSD0U:~$ cd /home/tether757/pgdata/base/24686
tether757@DESKTOP-HHRSD0U:~/pgdata/base/24686$ ls
112 13471_vm 24776 24884 25040 25220_fsm 25412 25571_fsm 25655 25717 25762 25793 25824 2606_fsm 2619 2680 2834 3380 3597 4146 6104
113 13474 24777 24884_fsm 25040_fsm 25232 25412_fsm 25571_vm 25657 25719 25763 25794 25825 2606_vm 2619_fsm 2681 2835 3381 3598 4147 6106
1247 13475 24777_fsm 24896 25052 25232_fsm 25424 25585 25659 25721 25764 25795 25826 2607 2619_vm 2682 2836 3394 3599 4148 6110
1247_fsm 1417 24777_vm 24896_fsm 25052_fsm 25244 25424_fsm 25586 25661 25723 25765 25796 25827 2607_fsm 2620 2683 2836_fsm 3394_fsm 3600 4149 6111
1247_vm 1418 24784 24896_vm 25064 25244_fsm 25436 25587 25663 25725 25766 25797 25828 2607_vm 2620_fsm 2684 2836_vm 3394_vm 3600_fsm 4150 6112
1249 174 24784_fsm 24908 25064_fsm 25256 25436_fsm 25588 25665 25727 25767 25798 25829 2608 2650 2685 2837 3395 3600_vm 4151 6113
1249_fsm 175 24784_vm 24908_fsm 25076 25256_fsm 25448 25588_fsm 25667 25729 25768 25799 25830 2608_fsm 2651 2686 2838 3429 3601 4152 6116
1249_vm 2187 24796 24908_vm 25076_fsm 25268 25448_fsm 25607 25669 25731 25769 25800 25831 2608_vm 2652 2687 2838_fsm 3430 3601_fsm 4153 6117
1255 2224 24797 24920 25088 25268_fsm 25460 25609 25671 25733 25770 25801 25832 2609 2653 2688 2838_vm 3431 3601_vm 4154 6175
1255_fsm 2228 24797_fsm 24920_fsm 25088_fsm 25280 25460_fsm 25611 25673 25735 25771 25802 25833 2609_fsm 2654 2689 2839 3433 3602 4155 6176
1255_vm 2328 24797_vm 24920_vm 25100 25280_fsm 25472 25613 25675 25737 25772 25803 25834 2609_vm 2655 2690 2840 3439 3602_fsm 4156 6228
1259 2336 24808 24932 25100_fsm 25292 25472_fsm 25615 25677 25739 25773 25804 25835 2610 2656 2691 2840_fsm 3440 3602_vm 4157 6229
1259_fsm 2337 24809 24932_fsm 25112 25292_fsm 25484 25617 25679 25741 25774 25805 25836 2610_fsm 2657 2692 2840_vm 3455 3603 4158 6237
1259_vm 24712 24810 24932_vm 25112_fsm 25304 25484_fsm 25619 25681 25743 25775 25806 2600 2610_vm 2658 2693 2841 3456 3603_fsm 4159 6238
13456 24713 24811 24944 25124 25304_fsm 25496 25621 25683 25745 25776 25807 2600_fsm 2611 2659 2696 2995 3456_fsm 3603_vm 4160 6239
13456_fsm 24713_fsm 24811_fsm 24944_fsm 25124_fsm 25316 25496_fsm 25623 25685 25746 25777 25808 2600_vm 2612 2660 2699 2996 3456_vm 3604 4163 6351
13456_vm 24729 24820 24944_vm 25136 25316_fsm 25508 25625 25687 25747 25778 25809 2601 2612_fsm 2661 2701 3079 3466 3605 4164 6352
13459 24730 24821 24956 25136_fsm 25328 25508_fsm 25627 25689 25748 25779 25810 2601_fsm 2612_vm 2662 2702 3079_fsm 3467 3606 4165 826
13460 24733 24822 24956_fsm 25148 25328_fsm 25520 25629 25691 25749 25780 25811 2601_vm 2613 2663 2703 3079_vm 3468 3607 4166 827
13461 24734 24823 24956_vm 25148_fsm 25340 25520_fsm 25631 25693 25750 25782 25812 2602 2615 2664 2704 3080 3501 3608 4167 828
13461_fsm 24734_fsm 24831 24968 25160 25340_fsm 25532 25633 25695 25751 25783 25813 2602_fsm 2615_fsm 2665 2753 3081 3502 3609 4168 PG_VERSION
13461_vm 24743 24832 24980 25160_fsm 25352 25532_fsm 25635 25697 25752 25784 25814 2602_vm 2615_vm 2666 2753_fsm 3085 3503 3712 4169 pg_filenode.map
13464 24744 24843 24980_fsm 25172 25352_fsm 25544 25637 25699 25753 25785 25815 2603 2616 2667 2753_vm 3118 3534 3764 4170 pg_internal.init
13465 24745 24844 24992 25172_fsm 25364 25545 25639 25701 25754 25786 25816 2603_fsm 2616_fsm 2668 2754 3119 3541 3764_fsm 4171
13466 24746 24844_fsm 24992_fsm 25184 25364_fsm 25545_fsm 25641 25703 25755 25787 25817 2603_vm 2616_vm 2669 2755 3164 3541_fsm 3764_vm 4172
13466_fsm 24754 24844_vm 25004 25184_fsm 25376 25545_vm 25643 25705 25756 25788 25818 2604 2617 2670 2756 3256 3541_vm 3766 4173
13466_vm 24755 24853 25004_fsm 25196 25376_fsm 25558 25645 25707 25757 25789 25819 2604_fsm 2617_fsm 2673 2757 3257 3542 3767 4174
13469 24756 24854 25016 25196_fsm 25388 25561 25647 25709 25758 2579 25820 2605 2617_vm 2674 2830 3258 3574 3997 5002
13470 24757 24862 25016_fsm 25208 25388_fsm 25562 25649 25711 25759 25790 25821 2605_fsm 2618 2675 2831 3350 3575 4143 548
13471 24757_fsm 24863 25028 25208_fsm 25400 25570 25651 25713 25760 25791 25822 2605_vm 2618_fsm 2678 2832 3351 3576 4144 549
13471_fsm 24775 24869 25028_fsm 25220 25400_fsm 25571 25653 25715 25761 25792 25823 2606 2618_vm 2679 2833 3379 3596 4145 6102
Как мы видим, здесь множество файлов нашей базы данных. Файловая система ничего не знает о том, что это за файл. А вот постгрес знает. Есть файлы, необходимые для работы самого постгреса. У них константное наименование. Например: 1259 - это реестр отношений pg_class. Таблица содержащая информацию о таблица, индексах и не только. 1249 - данные о колонках. Именно здесь лежит, что в таблице film есть колонка title типа text. 2604 - pg_attrdef - значения по умолчанию 2606 - pg_constraint - ограничения
Константами они являются потому что сам постгрес хардкодом их задает при создании кластера выполняя команды из файла postgres.bki. Например pg_class (он же 1259):
insert ( 83 pg_class 11 10 -1 f c C f t ',' 1259 - 0 273 2290 2291 2402 2403 - - - d x f 0 -1 0 0 _null_ _null_ _null_ ) -
Вставляются данные pg_class:
insert ( 1259 oid 26 4 1 -1 0 t i p '' t f f '' '' f t 0 0 _null_ _null_ _null_ _null_ _null_ )
insert ( 1259 relname 19 NAMEDATALEN 2 -1 0 f c p '' t f f '' '' f t 0 950 _null_ _null_ _null_ _null_ _null_ )
insert ( 1259 relnamespace 26 4 3 -1 0 t i p '' t f f '' '' f t 0 0 _null_ _null_ _null_ _null_ _null_ )
insert ( 1259 reltype 26 4 4 -1 0 t i p '' t f f '' '' f t 0 0 _null_ _null_ _null_ _null_ _null_ )
... etc
Таблица pg_class выполняет функцию реестра файлов. По сути своей таблица содержащая информацию о файлах(таблицах, индексах, последовательностях, toast таблицах, материализованных представлениях и партициях). У нее есть множество колонок, например oid, relfilenode, relkind. Колонка relfilenode содержит название файла в файловой системе. Колонка relkind содержит тип файла.
Все возможные типы описаны в pg_class.h
#define RELKIND_RELATION 'r' /* ordinary table */
#define RELKIND_INDEX 'i' /* secondary index */
#define RELKIND_SEQUENCE 'S' /* sequence object */
#define RELKIND_TOASTVALUE 't' /* for out-of-line values */
#define RELKIND_VIEW 'v' /* view */
#define RELKIND_MATVIEW 'm' /* materialized view */
#define RELKIND_COMPOSITE_TYPE 'c' /* composite type */
#define RELKIND_FOREIGN_TABLE 'f' /* foreign table */
#define RELKIND_PARTITIONED_TABLE 'p' /* partitioned table */
#define RELKIND_PARTITIONED_INDEX 'I' /* partitioned index */
#define RELPERSISTENCE_PERMANENT 'p' /* regular table */
#define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */
#define RELPERSISTENCE_TEMP 't' /* temporary table */
/* default selection for replica identity (primary key or nothing) */
#define REPLICA_IDENTITY_DEFAULT 'd'
/* no replica identity is logged for this relation */
#define REPLICA_IDENTITY_NOTHING 'n'
/* all columns are logged as replica identity */
#define REPLICA_IDENTITY_FULL 'f'
/*
* an explicitly chosen candidate key's columns are used as replica identity.
* Note this will still be set if the index has been dropped; in that case it
* has the same meaning as 'n'.
*/
#define REPLICA_IDENTITY_INDEX 'i'
Вернемся к файлам в нашей директорию /pgdata/base/24686. Чтобы увидеть типы файлов, достанем информацию из таблицы pg_class И также определим размер файла с помощью функции pg_size_pretty, подав туда object identifier (колонка oid) самой же строки. И увидим в колонке relfilenode - название файла в директории ОС, relname - понятное название сущности для постгреса, relkind - тип файла и pg_size_pretty - размер файла.
pagila=# SELECT relfilenode, relname, relkind, pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relnamespace = 'public'::regnamespace AND relfilenode > 0
ORDER BY relfilenode;
relfilenode | relname | relkind | pg_size_pretty
-------------+-----------------------------------------------------+---------+----------------
24712 | customer_customer_id_seq | S | 8192 bytes
24713 | customer | r | 256 kB
24733 | actor_actor_id_seq | S | 8192 bytes
24734 | actor | r | 16 kB
24745 | category_category_id_seq | S | 8192 bytes
24746 | category | r | 8192 bytes
24756 | film_film_id_seq | S | 8192 bytes
24757 | film | r | 512 kB
24777 | film_actor | r | 240 kB
24784 | film_category | r | 104 kB
24796 | address_address_id_seq | S | 8192 bytes
24797 | address | r | 96 kB
24810 | city_city_id_seq | S | 8192 bytes
24811 | city | r | 40 kB
24822 | country_country_id_seq | S | 8192 bytes
24823 | country | r | 8192 bytes
24843 | inventory_inventory_id_seq | S | 8192 bytes
24844 | inventory | r | 240 kB
24853 | language_language_id_seq | S | 8192 bytes
24854 | language | r | 8192 bytes
24869 | payment_payment_id_seq | S | 8192 bytes
24884 | payment_p2022_01 | r | 56 kB
24896 | payment_p2022_02 | r | 184 kB
24908 | payment_p2022_03 | r | 208 kB
24920 | payment_p2022_04 | r | 192 kB
24932 | payment_p2022_05 | r | 208 kB
24944 | payment_p2022_06 | r | 200 kB
24956 | payment_p2022_07 | r | 176 kB
24968 | payment_p2022_08 | r | 8192 bytes
24980 | payment_p2022_09 | r | 56 kB
24992 | payment_p2022_10 | r | 56 kB
25004 | payment_p2022_11 | r | 56 kB
25016 | payment_p2022_12 | r | 112 kB
25028 | payment_p2023_01 | r | 56 kB
25040 | payment_p2023_02 | r | 56 kB
25052 | payment_p2023_03 | r | 56 kB
25064 | payment_p2023_04 | r | 56 kB
25076 | payment_p2023_05 | r | 112 kB
25088 | payment_p2023_06 | r | 56 kB
25100 | payment_p2023_07 | r | 56 kB
25112 | payment_p2023_08 | r | 112 kB
25124 | payment_p2023_09 | r | 56 kB
25136 | payment_p2023_10 | r | 112 kB
25148 | payment_p2023_11 | r | 56 kB
25160 | payment_p2023_12 | r | 112 kB
25172 | payment_p2024_01 | r | 112 kB
25184 | payment_p2024_02 | r | 112 kB
25196 | payment_p2024_03 | r | 112 kB
25208 | payment_p2024_04 | r | 56 kB
25220 | payment_p2024_05 | r | 112 kB
25232 | payment_p2024_06 | r | 112 kB
25244 | payment_p2024_07 | r | 56 kB
25256 | payment_p2024_08 | r | 56 kB
25268 | payment_p2024_09 | r | 48 kB
25280 | payment_p2024_10 | r | 112 kB
25292 | payment_p2024_11 | r | 112 kB
25304 | payment_p2024_12 | r | 56 kB
25316 | payment_p2025_01 | r | 56 kB
25328 | payment_p2025_02 | r | 56 kB
25340 | payment_p2025_03 | r | 112 kB
25352 | payment_p2025_04 | r | 56 kB
25364 | payment_p2025_05 | r | 112 kB
25376 | payment_p2025_06 | r | 112 kB
25388 | payment_p2025_07 | r | 112 kB
25400 | payment_p2025_08 | r | 112 kB
25412 | payment_p2025_09 | r | 56 kB
25424 | payment_p2025_10 | r | 112 kB
25436 | payment_p2025_11 | r | 56 kB
25448 | payment_p2025_12 | r | 56 kB
25460 | payment_p2026_01 | r | 112 kB
25472 | payment_p2026_02 | r | 56 kB
25484 | payment_p2026_03 | r | 112 kB
25496 | payment_p2026_04 | r | 56 kB
25508 | payment_p2026_05 | r | 112 kB
25520 | payment_p2026_06 | r | 56 kB
25532 | payment_p2026_07 | r | 112 kB
25544 | rental_rental_id_seq | S | 8192 bytes
25545 | rental | r | 4992 kB
25558 | rental_by_category | m | 0 bytes
25570 | staff_staff_id_seq | S | 8192 bytes
25571 | staff | r | 248 kB
25587 | store_store_id_seq | S | 8192 bytes
25588 | store | r | 32 kB
25607 | payment_p2022_01_pkey | i | 56 kB
25609 | payment_p2022_02_pkey | i | 104 kB
25611 | payment_p2022_03_pkey | i | 144 kB
25613 | payment_p2022_04_pkey | i | 112 kB
25615 | payment_p2022_05_pkey | i | 136 kB
25617 | payment_p2022_06_pkey | i | 120 kB
25619 | payment_p2022_07_pkey | i | 96 kB
25621 | payment_p2022_08_pkey | i | 16 kB
25623 | payment_p2022_09_pkey | i | 40 kB
25625 | payment_p2022_10_pkey | i | 40 kB
25627 | payment_p2022_11_pkey | i | 40 kB
25629 | payment_p2022_12_pkey | i | 40 kB
25631 | payment_p2023_01_pkey | i | 40 kB
25633 | payment_p2023_02_pkey | i | 40 kB
25635 | payment_p2023_03_pkey | i | 40 kB
25637 | payment_p2023_04_pkey | i | 40 kB
25639 | payment_p2023_05_pkey | i | 40 kB
25641 | payment_p2023_06_pkey | i | 40 kB
25643 | payment_p2023_07_pkey | i | 40 kB
25645 | payment_p2023_08_pkey | i | 40 kB
25647 | payment_p2023_09_pkey | i | 40 kB
25649 | payment_p2023_10_pkey | i | 40 kB
25651 | payment_p2023_11_pkey | i | 40 kB
25653 | payment_p2023_12_pkey | i | 40 kB
25655 | payment_p2024_01_pkey | i | 40 kB
25657 | payment_p2024_02_pkey | i | 40 kB
25659 | payment_p2024_03_pkey | i | 40 kB
25661 | payment_p2024_04_pkey | i | 40 kB
25663 | payment_p2024_05_pkey | i | 40 kB
25665 | payment_p2024_06_pkey | i | 40 kB
25667 | payment_p2024_07_pkey | i | 40 kB
25669 | payment_p2024_08_pkey | i | 40 kB
25671 | payment_p2024_09_pkey | i | 40 kB
25673 | payment_p2024_10_pkey | i | 40 kB
25675 | payment_p2024_11_pkey | i | 40 kB
25677 | payment_p2024_12_pkey | i | 40 kB
25679 | payment_p2025_01_pkey | i | 40 kB
25681 | payment_p2025_02_pkey | i | 40 kB
25683 | payment_p2025_03_pkey | i | 40 kB
25685 | payment_p2025_04_pkey | i | 40 kB
25687 | payment_p2025_05_pkey | i | 40 kB
25689 | payment_p2025_06_pkey | i | 40 kB
25691 | payment_p2025_07_pkey | i | 40 kB
25693 | payment_p2025_08_pkey | i | 40 kB
25695 | payment_p2025_09_pkey | i | 40 kB
25697 | payment_p2025_10_pkey | i | 40 kB
25699 | payment_p2025_11_pkey | i | 40 kB
25701 | payment_p2025_12_pkey | i | 40 kB
25703 | payment_p2026_01_pkey | i | 40 kB
25705 | payment_p2026_02_pkey | i | 40 kB
25707 | payment_p2026_03_pkey | i | 40 kB
25709 | payment_p2026_04_pkey | i | 40 kB
25711 | payment_p2026_05_pkey | i | 40 kB
25713 | payment_p2026_06_pkey | i | 40 kB
25715 | payment_p2026_07_pkey | i | 48 kB
25717 | actor_pkey | i | 16 kB
25719 | address_pkey | i | 56 kB
25721 | category_pkey | i | 16 kB
25723 | city_pkey | i | 32 kB
25725 | country_pkey | i | 16 kB
25727 | customer_pkey | i | 56 kB
25729 | film_actor_pkey | i | 136 kB
25731 | film_category_pkey | i | 104 kB
25733 | film_pkey | i | 40 kB
25735 | inventory_pkey | i | 120 kB
25737 | language_pkey | i | 16 kB
25739 | rental_pkey | i | 1152 kB
25741 | staff_pkey | i | 48 kB
25743 | store_pkey | i | 32 kB
25745 | film_fulltext_idx | i | 80 kB
25746 | idx_actor_last_name | i | 16 kB
25747 | idx_fk_address_id | i | 56 kB
25748 | idx_fk_city_id | i | 40 kB
25749 | idx_fk_country_id | i | 16 kB
25750 | idx_fk_film_id | i | 88 kB
25751 | idx_fk_inventory_id | i | 600 kB
25752 | idx_fk_language_id | i | 16 kB
25753 | idx_fk_original_language_id | i | 16 kB
25754 | idx_fk_payment_p2022_01_customer_id | i | 40 kB
25755 | idx_fk_payment_p2022_01_staff_id | i | 16 kB
25756 | idx_fk_payment_p2022_02_customer_id | i | 64 kB
25757 | idx_fk_payment_p2022_02_staff_id | i | 32 kB
25758 | idx_fk_payment_p2022_03_customer_id | i | 64 kB
25759 | idx_fk_payment_p2022_03_staff_id | i | 48 kB
25760 | idx_fk_payment_p2022_04_customer_id | i | 56 kB
25761 | idx_fk_payment_p2022_04_staff_id | i | 32 kB
25762 | idx_fk_payment_p2022_05_customer_id | i | 64 kB
25763 | idx_fk_payment_p2022_05_staff_id | i | 48 kB
25764 | idx_fk_payment_p2022_06_customer_id | i | 64 kB
25765 | idx_fk_payment_p2022_06_staff_id | i | 48 kB
25766 | idx_fk_store_id | i | 16 kB
25767 | idx_last_name | i | 40 kB
25768 | idx_store_id_film_id | i | 112 kB
25769 | idx_title | i | 56 kB
25770 | idx_unq_manager_staff_id | i | 32 kB
25771 | idx_unq_rental_rental_date_inventory_id_customer_id | i | 1616 kB
25772 | payment_p2022_01_customer_id_idx | i | 40 kB
25773 | payment_p2022_02_customer_id_idx | i | 64 kB
25774 | payment_p2022_03_customer_id_idx | i | 64 kB
25775 | payment_p2022_04_customer_id_idx | i | 56 kB
25776 | payment_p2022_05_customer_id_idx | i | 64 kB
25777 | payment_p2022_06_customer_id_idx | i | 64 kB
25778 | rental_category | i | 8192 bytes
25779 | customer_uuid_key | i | 72 kB
25780 | rental_uuid_key | i | 1608 kB
25782 | payment_p2022_01_uuid_payment_date_idx | i | 48 kB
25783 | payment_p2022_02_uuid_payment_date_idx | i | 112 kB
25784 | payment_p2022_03_uuid_payment_date_idx | i | 128 kB
25785 | payment_p2022_04_uuid_payment_date_idx | i | 120 kB
25786 | payment_p2022_05_uuid_payment_date_idx | i | 128 kB
25787 | payment_p2022_06_uuid_payment_date_idx | i | 120 kB
25788 | payment_p2022_07_uuid_payment_date_idx | i | 112 kB
25789 | payment_p2022_08_uuid_payment_date_idx | i | 16 kB
25790 | payment_p2022_09_uuid_payment_date_idx | i | 48 kB
25791 | payment_p2022_10_uuid_payment_date_idx | i | 48 kB
25792 | payment_p2022_11_uuid_payment_date_idx | i | 48 kB
25793 | payment_p2022_12_uuid_payment_date_idx | i | 48 kB
25794 | payment_p2023_01_uuid_payment_date_idx | i | 48 kB
25795 | payment_p2023_02_uuid_payment_date_idx | i | 48 kB
25796 | payment_p2023_03_uuid_payment_date_idx | i | 48 kB
25797 | payment_p2023_04_uuid_payment_date_idx | i | 48 kB
25798 | payment_p2023_05_uuid_payment_date_idx | i | 48 kB
25799 | payment_p2023_06_uuid_payment_date_idx | i | 48 kB
25800 | payment_p2023_07_uuid_payment_date_idx | i | 48 kB
25801 | payment_p2023_08_uuid_payment_date_idx | i | 48 kB
25802 | payment_p2023_09_uuid_payment_date_idx | i | 48 kB
25803 | payment_p2023_10_uuid_payment_date_idx | i | 48 kB
25804 | payment_p2023_11_uuid_payment_date_idx | i | 48 kB
25805 | payment_p2023_12_uuid_payment_date_idx | i | 48 kB
25806 | payment_p2024_01_uuid_payment_date_idx | i | 48 kB
25807 | payment_p2024_02_uuid_payment_date_idx | i | 48 kB
25808 | payment_p2024_03_uuid_payment_date_idx | i | 48 kB
25809 | payment_p2024_04_uuid_payment_date_idx | i | 48 kB
25810 | payment_p2024_05_uuid_payment_date_idx | i | 48 kB
25811 | payment_p2024_06_uuid_payment_date_idx | i | 48 kB
25812 | payment_p2024_07_uuid_payment_date_idx | i | 48 kB
25813 | payment_p2024_08_uuid_payment_date_idx | i | 48 kB
25814 | payment_p2024_09_uuid_payment_date_idx | i | 40 kB
25815 | payment_p2024_10_uuid_payment_date_idx | i | 48 kB
25816 | payment_p2024_11_uuid_payment_date_idx | i | 48 kB
25817 | payment_p2024_12_uuid_payment_date_idx | i | 48 kB
25818 | payment_p2025_01_uuid_payment_date_idx | i | 48 kB
25819 | payment_p2025_02_uuid_payment_date_idx | i | 48 kB
25820 | payment_p2025_03_uuid_payment_date_idx | i | 48 kB
25821 | payment_p2025_04_uuid_payment_date_idx | i | 48 kB
25822 | payment_p2025_05_uuid_payment_date_idx | i | 48 kB
25823 | payment_p2025_06_uuid_payment_date_idx | i | 48 kB
25824 | payment_p2025_07_uuid_payment_date_idx | i | 48 kB
25825 | payment_p2025_08_uuid_payment_date_idx | i | 48 kB
25826 | payment_p2025_09_uuid_payment_date_idx | i | 48 kB
25827 | payment_p2025_10_uuid_payment_date_idx | i | 48 kB
25828 | payment_p2025_11_uuid_payment_date_idx | i | 48 kB
25829 | payment_p2025_12_uuid_payment_date_idx | i | 48 kB
25830 | payment_p2026_01_uuid_payment_date_idx | i | 48 kB
25831 | payment_p2026_02_uuid_payment_date_idx | i | 48 kB
25832 | payment_p2026_03_uuid_payment_date_idx | i | 48 kB
25833 | payment_p2026_04_uuid_payment_date_idx | i | 48 kB
25834 | payment_p2026_05_uuid_payment_date_idx | i | 48 kB
25835 | payment_p2026_06_uuid_payment_date_idx | i | 48 kB
25836 | payment_p2026_07_uuid_payment_date_idx | i | 48 kB
(243 rows)
Давайте заглянем в файл, для начала посмотрим привычным запросом первую строчку таблицы film. Здесь мы видим film_id, title, description
pagila=# SELECT * FROM film LIMIT 1;
film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | last_update | special_features | fulltext | length_hours
---------+---------------+-----------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------------------+------------------+------------------------------------------------------------------------------------------------------------------+--------------
483 | JERICHO MULAN | A Amazing Yarn of a Hunter And a Butler who must Defeat a Boy in A Jet Boat | 2016 | 1 | | 3 | 2.99 | 171 | 29.99 | NC-17 | 2025-07-31 09:26:30.712241+05 | {Commentaries} | 'amaz':4 'boat':20 'boy':16 'butler':11 'defeat':14 'hunter':8 'jericho':1 'jet':19 'mulan':2 'must':13 'yarn':5 | 2.85
(1 row)
В постгресе есть понятие страницы. Страница - это кусок файла фиксированного размера 8192 байта (можно менять размер).
Файл нарезан на такие куски подряд, без промежутков и без общего заголовка. Посмотри размер таблицы film.
relfilenode | relname | relkind | pg_size_pretty
-------------+-----------------------------------------------------+---------+----------------
24757 | film | r | 512 kB
Вычислим количество страниц у таблицы film: 512 / 8 = 64 страниц. Страница - это единица всего в постгрес. Он не читает строки. Он читает именно страницами. Записывает в журнал WAL, держит данные в буферном кэше страницами.
Давайте посмотрим структуру страницы на уровне кода:
typedef struct PageHeaderData
{
/* XXX LSN is member of *any* block, not only page-organized ones */
PageXLogRecPtr pd_lsn; /* LSN: next byte after last byte of xlog
* record for last change to this page */
uint16 pd_checksum; /* checksum */
uint16 pd_flags; /* flag bits, see below */
LocationIndex pd_lower; /* offset to start of free space */
LocationIndex pd_upper; /* offset to end of free space */
LocationIndex pd_special; /* offset to start of special space */
uint16 pd_pagesize_version;
TransactionId pd_prune_xid; /* oldest prunable XID, or zero if none */
ItemIdData pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */
} PageHeaderData;
Далее, давайте определим в каком файле лежит эта строка. В постгресе есть понятие кортеж. Кортеж - это версия строки, к понятию версия мы вернемся чуть позже. Есть несколько видов кортежей:
Кортеж индекса
typedef struct IndexTupleData
{
ItemPointerData t_tid; /* reference TID to heap tuple */
/* ---------------
* t_info is laid out in the following fashion:
*
* 15th (high) bit: has nulls
* 14th bit: has var-width attributes
* 13th bit: AM-defined meaning
* 12-0 bit: size of tuple
* ---------------
*/
unsigned short t_info; /* various info about tuple */
} IndexTupleData; /* MORE DATA FOLLOWS AT END OF STRUCT */
Кортеж таблицы
typedef struct HeapTupleData
{
uint32 t_len; /* length of *t_data */
ItemPointerData t_self; /* SelfItemPointer */
Oid t_tableOid; /* table the tuple came from */
#define FIELDNO_HEAPTUPLEDATA_DATA 3
HeapTupleHeader t_data; /* -> tuple header and data */
} HeapTupleData;
Кортеж служебных полей
struct MinimalTupleData
{
uint32 t_len; /* actual length of minimal tuple */
char mt_padding[MINIMAL_TUPLE_PADDING];
/* Fields below here must match HeapTupleHeaderData! */
uint16 t_infomask2; /* number of attributes + various flags */
uint16 t_infomask; /* various flag bits, see below */
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */
/* MORE DATA FOLLOWS AT END OF STRUCT */
};
Сделаем запрос в функцию heap_page_items, подадим в функцию байты первой страницы таблицы film, именно в ней наша первая строка. Запросим указатель lp (line pointer), lp_off - смещение кортежа с начала страницы, lp_len длина кортежа в байтах.
pagila=# SELECT lp, lp_off, lp_len FROM heap_page_items(get_raw_page('film', 0)) ORDER BY lp LIMIT 1;
lp | lp_off | lp_len
----+--------+--------
1 | 7832 | 360
(1 row)
Запоминаем значение смещения и длины. Используя утилиту octal dump (она в coreutils GNU, ничего устанавливать не нужно) переводим в читаемый нами вид. Выполняем команду od -A d -t x1z -j 7832 -N 360 $HOME/pgdata/base/24686/24757 Если кратко: -j 7832 позволяет нам пропустить первые 7832 байта, а -N 360 прочитать 360 байта.
tether757@DESKTOP-HHRSD0U:~/pgdata/base/24686$ od -A d -t x1z -j 7832 -N 360 $HOME/pgdata/base/24686/24757
0007832 72 04 00 00 75 04 00 00 00 00 00 00 00 00 00 00 >r...u...........<
0007848 01 00 0f 00 93 09 20 df 3f 00 00 00 00 00 00 00 >...... .?.......<
0007864 e3 01 00 00 1d 4a 45 52 49 43 48 4f 20 4d 55 4c >.....JERICHO MUL<
0007880 41 4e 99 41 20 41 6d 61 7a 69 6e 67 20 59 61 72 >AN.A Amazing Yar<
0007896 6e 20 6f 66 20 61 20 48 75 6e 74 65 72 20 41 6e >n of a Hunter An<
0007912 64 20 61 20 42 75 74 6c 65 72 20 77 68 6f 20 6d >d a Butler who m<
0007928 75 73 74 20 44 65 66 65 61 74 20 61 20 42 6f 79 >ust Defeat a Boy<
0007944 20 69 6e 20 41 20 4a 65 74 20 42 6f 61 74 00 00 > in A Jet Boat..<
0007960 e0 07 00 00 01 00 00 00 03 00 0f 00 81 02 00 ac >................<
0007976 26 00 ab 00 0f 00 81 1d 00 ac 26 00 7c 60 00 00 >&.........&.|`..<
0007992 b1 87 77 d0 30 de 02 00 4b 01 00 00 00 00 00 00 >..w.0...K.......<
0008008 00 19 00 00 00 01 00 00 00 01 00 00 00 40 00 00 >.............@..<
0008024 00 43 6f 6d 6d 65 6e 74 61 72 69 65 73 00 00 00 >.Commentaries...<
0008040 60 02 00 00 0b 00 00 00 09 00 00 00 09 80 00 00 >`...............<
0008056 07 00 01 00 0d 80 01 00 0d 20 02 00 0d c0 02 00 >......... ......<
0008072 0f 60 03 00 07 20 04 00 0b a0 04 00 09 40 05 00 >.`... .......@..<
0008088 09 c0 05 00 61 6d 61 7a 01 00 04 00 62 6f 61 74 >....amaz....boat<
0008104 01 00 14 00 62 6f 79 00 01 00 10 00 62 75 74 6c >....boy.....butl<
0008120 65 72 01 00 0b 00 64 65 66 65 61 74 01 00 0e 00 >er....defeat....<
0008136 68 75 6e 74 65 72 01 00 08 00 6a 65 72 69 63 68 >hunter....jerich<
0008152 6f 00 01 00 01 00 6a 65 74 00 01 00 13 00 6d 75 >o.....jet.....mu<
0008168 6c 61 6e 00 01 00 02 00 6d 75 73 74 01 00 0d 00 >lan.....must....<
0008184 79 61 72 6e 01 00 05 00 >yarn....<
0008192
Рассмотрим внимательнее, левый столбец не относится к данным octal dump его печатает, чтобы показать нам смещения. Например: 0007832 0007848 0007864
Octal dump печатает по 16 байтов на строку. Поэтому будем отделять по кускам. Рассмотрим заголовок кортежа. Здесь лежат метаданные данных. В нашей случае заголовок занял 32 байта.
- Фиксированная часть (xmin, xmax, cid, ctid, маски, hoff) 23 байта;
- N байт (t_bits - только если есть NULL, по биту на колонку);
- padding до кратного MAXALIGN, то бишь округление; Первые 4 байта: 72 04 00 00 - это значение t_xmin, номер транзакции создавший запись, к этому вернемся чуть позже. Берем вторые 4 байта: 75 04 00 00 - это значение t_xmax, номер транзакции убивший, изменивший, или же номер транзакции заблокировавший с помощью SELECT FOR UPDATE, или например инсерт в дочернюю таблицу Следующие 4 байта: 00 00 00 00 - это значение t_cid, номер команды в транзакции, который добавил эту запись или удалил. Следующие 6 байтов: 00 00 00 00 | 01 00 — это t_ctid, физический адрес версии. Первые 4 байта — номер страницы (0), последние 2 — номер указателя на этой странице (1). Итого (0,1). Здесь он указывает сам на себя, значит эта версия актуальна. Если бы указывал на другой кортеж — это была бы старая версия, а по ссылке лежала бы новая. Следующие 2 байта: 0f 00 — это t_infomask2. Младшие 11 бит хранят число колонок в кортеже: 0x000f = 15. Старшие биты заняты флагами HOT-обновлений. Следующие 2 байта: 93 09 — это t_infomask, набор битовых флагов о состоянии кортежа. 0x0993 = 2451. Расшифровать вручную неудобно, но есть функция: SELECT heap_tuple_infomask_flags(2451, 15); Следующий 1 байт: 20 — это t_hoff, 0x20 = 32. Смещение, на котором заканчивается заголовок и начинаются данные колонок: 7832 + 32 = 7864. Поле нужно именно потому, что размер заголовка непостоянен. Следующие 2 байта: df 3f — это t_bits, битовая карта NULL-колонок. Один бит на колонку, 15 колонок — значит 2 байта. Единица означает, что значение записано, ноль — что колонка NULL: df = 1101 1111 -> 1 1 1 1 1 0 1 1 3f = 0011 1111 -> 1 1 1 1 1 1 0 0 Ноль на шестой позиции — это original_language_id. Его в данных нет вообще: NULL не занимает места в кортеже, информация о нём живёт в одном бите. Карта присутствует только если есть хотя бы один NULL — об этом говорит флаг HEAP_HASNULL. Оставшиеся 7 байтов: 00 00 00 00 00 00 00 — выравнивание. 23 байта фиксированной части плюс 2 байта карты = 25, округляем вверх до кратного 8 и получаем 32 — то самое значение t_hoff.
0007864 e3 01 00 00 1d 4a 45 52 49 43 48 4f 20 4d 55 4c >.....JERICHO MUL<
0007880 41 4e 99 41 20 41 6d 61 7a 69 6e 67 20 59 61 72 >AN.A Amazing Yar<
0007896 6e 20 6f 66 20 61 20 48 75 6e 74 65 72 20 41 6e >n of a Hunter An<
0007912 64 20 61 20 42 75 74 6c 65 72 20 77 68 6f 20 6d >d a Butler who m<
0007928 75 73 74 20 44 65 66 65 61 74 20 61 20 42 6f 79 >ust Defeat a Boy<
0007944 20 69 6e 20 41 20 4a 65 74 20 42 6f 61 74 00 00 > in A Jet Boat..<
0007960 e0 07 00 00 01 00 00 00 03 00 0f 00 81 02 00 ac >................<
0007976 26 00 ab 00 0f 00 81 1d 00 ac 26 00 7c 60 00 00 >&.........&.|`..<
0007992 b1 87 77 d0 30 de 02 00 4b 01 00 00 00 00 00 00 >..w.0...K.......<
0008008 00 19 00 00 00 01 00 00 00 01 00 00 00 40 00 00 >.............@..<
0008024 00 43 6f 6d 6d 65 6e 74 61 72 69 65 73 00 00 00 >.Commentaries...<
0008040 60 02 00 00 0b 00 00 00 09 00 00 00 09 80 00 00 >`...............<
0008056 07 00 01 00 0d 80 01 00 0d 20 02 00 0d c0 02 00 >......... ......<
0008072 0f 60 03 00 07 20 04 00 0b a0 04 00 09 40 05 00 >.`... .......@..<
0008088 09 c0 05 00 61 6d 61 7a 01 00 04 00 62 6f 61 74 >....amaz....boat<
0008104 01 00 14 00 62 6f 79 00 01 00 10 00 62 75 74 6c >....boy.....butl<
0008120 65 72 01 00 0b 00 64 65 66 65 61 74 01 00 0e 00 >er....defeat....<
0008136 68 75 6e 74 65 72 01 00 08 00 6a 65 72 69 63 68 >hunter....jerich<
0008152 6f 00 01 00 01 00 6a 65 74 00 01 00 13 00 6d 75 >o.....jet.....mu<
0008168 6c 61 6e 00 01 00 02 00 6d 75 73 74 01 00 0d 00 >lan.....must....<
0008184 79 61 72 6e 01 00 05 00 >yarn....<
0008192
Transaction
Давайте посмотрим, что написано в документации постгреса.
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.
Transactions can be created explicitly using BEGIN or START TRANSACTION and ended using COMMIT or ROLLBACK.
Every transaction is identified by a unique VirtualTransactionId (also called virtualXID or vxid), which is comprised of a backend's process number (or procNumber) and a sequentially-assigned number local to each backend, known as localXID. For example, the virtual transaction ID 4/12532 has a procNumber of 4 and a localXID of 12532.
The internal transaction ID type xid is 32 bits wide and wraps around every 4 billion transactions. Когда айди достигает 4 миллиардов, то круг идет заново. Xids are used as the basis for PostgreSQL's MVCC concurrency mechanism and streaming replication.
| virtualXID | xid | |
|---|---|---|
| когда выдается | всегда, при старте транзакции | только при первой записи |
| живет | до конца транзакции | навсегда (пока не заморозит |
| где виден | pg_lock, pg_stat_activity | в кортеже, xmin, xmax |
Читающие транзакции не тратят номера.
Пример:
pagila=# BEGIN;
BEGIN
pagila=*# SELECT count(*) FROM film;
count
-------
1000
(1 row)
pagila=*# SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
pagila=*# SELECT txid_current();
txid_current
--------------
1305
(1 row)
pagila=*# COMMIT;
COMMIT
Функция txid_current_if_assigned() нам покажет что не создан для читающей транзакции xid. А вот функция txid_current() сынициирует xid, так как вызовет функцию AssignTransactionId();
/*
* AssignTransactionId
*
* Assigns a new permanent FullTransactionId to the given TransactionState.
* We do not assign XIDs to transactions until/unless this is called.
* Also, any parent TransactionStates that don't yet have XIDs are assigned
* one; this maintains the invariant that a child transaction has an XID
* following its parent's.
*/
static void
AssignTransactionId(TransactionState s)
Давайте создадим таблицу, вставим в нее данные и посмотрим, что в кортеж проставляется айди транзакции xid.
pagila=# CREATE TABLE demo(id int, val text);
CREATE TABLE
Начнем транзакцию.
pagila=# BEGIN;
BEGIN
pagila=*# SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
pagila=*# INSERT INTO demo VALUES (1, 'FIRST');
INSERT 0 1
pagila=*# SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
1307
(1 row)
pagila=*# COMMIT;
COMMIT
С помощью SELECT txid_current_if_assigned(); мы узнаем xid без его создания, в случае его отсутствия.
Давайте проверим какое значение xmin выставлен на нашем кортеже. И да, он равен xid транзакции, которые его создал.
pagila=# SELECT lp, t_xmin, t_xmax FROM heap_page_items(get_raw_page('demo', 0));
lp | t_xmin | t_xmax
----+--------+--------
1 | 1307 | 0
(1 row)
Но в кортеже записан только номер. Закоммитилась транзакция 1307 или нет - в кортеже не сказано. Ответ лежит в каталоге pg_xact.
tether757@DESKTOP-HHRSD0U:~$ ls -la $HOME/pgdata/pg_xact/
total 16
drwx------ 2 tether757 tether757 4096 Sep 10 22:32 .
drwx------ 19 tether757 tether757 4096 Sep 18 21:46 ..
-rw------- 1 tether757 tether757 8192 Sep 18 21:51 0000
tether757@DESKTOP-HHRSD0U:~$ od -A d -t x1z -j 324 -N 8 $HOME/pgdata/pg_xact/0000
0000324 55 55 55 >UUU.....<
0000332
В каждой строчке прописаны байты. Статус транзакций вмещается в 2 бита. Соответственно одна запись в один байт вмещает статус сразу 4 транзакций.
| byte | what does it mean? |
|---|---|
| 0x55 | четыре committed подряд |
| 0x00 | четыре in progess (or not used) |
| 0xAA | four aborted подряд |
| 0x40 | три нуля и один committed |
Давайте попытаемся инсертнуть и ролбекнем.
pagila=# BEGIN;
BEGIN
pagila=*# INSERT INTO demo VALUES (99, 'ABORTED');
INSERT 0 1
pagila=*# SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
1308
(1 row)
pagila=*# ROLLBACK;
ROLLBACK
Полезем снова в каталог pg_act
tether757@DESKTOP-HHRSD0U:~$ od -A d -t x1z -j 324 -N 4 $HOME/pgdata/pg_xact/0000
0000324 55 55 02 >UU..<
0000332
И увидим, что изменения еще не попали на диск. Давайте заставим постгрес сбросить данные на диск.
pagila=# CHECKPOINT;
CHECKPOINT
Shared buffer
Когда мы вставляем изменяем данные, эти данные не сразу попадают на диск. Некоторое время они обитают только в shared buffer.
Нам понадобится extension pg_buffercache.
pagila=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
Создадим таблицу shared_buffers_demo и вставим туда строку
pagila=# CREATE TABLE shared_buffers_demo(id int, val text);
INSERT INTO shared_buffers_demo VALUES (1, 'SOME_VALUE');
SELECT pg_relation_filepath('shared_buffers_demo');
CREATE TABLE
INSERT 0 1
pg_relation_filepath
----------------------
base/24686/40983
(1 row)
В другом окне давайте вызовем приложение strings, попытаемся прочитать файл таблицы.
tether757@DESKTOP-HHRSD0U:~$ strings $HOME/pgdata/base/24686/40983
И увидим ничего.
Вернемся в окно с базой данных и вызовем CHECKPOINT, что сбросит принудительно данные из памяти на диск.
pagila=# CHECKPOINT;
CHECKPOINT
Идем в окно и еще раз вызовем и увидим, что данные записались на диск.
tether757@DESKTOP-HHRSD0U:~$ strings $HOME/pgdata/base/24686/40983
SOME_VALUE
У постгреса существует 3 сервиса и один вынужденный путь, которые занимаются сбрасыванием на диск данных:
- checkpointer - (запускается раз в 5 минут (можно настроить), или же по объему max_wal_size)
- bgwriter - фоновый писатель, работает постоянно, маленькими порциями, delay 200 мс. Нужен, чтобы backed не упирался в запись, когда ему нужен свободный буффер.
- walwriter - сбрасывает буфер WAL, отдельно от страниц данных.
- backend eviction - постгрес вынужденно сбрасывает на диск.
Данные кэшируются не только на уровне приложения, но еще и на уровне операционной системы.
Постгрес кэширует данные на своей стороне ради контроля, например, чтобы самому регулировать когда грязная страница уйдет на диск. Свой кэш дает:
- контроль над порядком записи. Право не отпускать страницу на диск, пока WAL до ее LSN не зафиксирован. ОС такого обещания не дает. Durability без этого построить нельзя.
- своя политики вытеснения. Clock-sweep с usage_count + ring buffers.
- экономия на вызовах syscall и copy между os cache и postgres cache.
worst path: disk -> read system call -> os page cache -> copy (8KB) -> postgres shared_buffers.
важно понимать:
- Мы имеем двойную буферизацию. Данные лежат и на уровне OS и на уровне постгреса. Поэтому необдуманно увеличивать размер shared_buffers опасно.
pagila=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM film WHERE film_id = 500;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using film_pkey on film (cost=0.28..8.30 rows=1 width=402) (actual time=0.228..0.231 rows=1.00 loops=1)
Index Cond: (film_id = 500)
Index Searches: 1
Buffers: shared read=3
Planning:
Buffers: shared hit=140 read=15
Planning Time: 5.676 ms
Execution Time: 0.851 ms
(8 rows)
Buffers: shared read=3 - говорит нам о том, что мы прочитали из OS 3 страницы. Planning: Buffers: shared hit=140 read=15 - планировщик сходил за метаданными в системные каталоги. 140 из постгрес кэша. 15 из OS. При этом EXPLAIN не знает - прочитаны ли данные с диска или взяты с OS cache.
pagila=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM film WHERE film_id = 500;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using film_pkey on film (cost=0.28..8.30 rows=1 width=402) (actual time=0.108..0.112 rows=1.00 loops=1)
Index Cond: (film_id = 500)
Index Searches: 1
Buffers: shared hit=3
Planning Time: 0.245 ms
Execution Time: 0.162 ms
(6 rows
Buffers: shared hit=3- говорит нам о том, что мы прочитали из shared_buffers 3 страницы. Planning: Buffers на этот вообще ничего не написал. Потому что метаданные попали в кэш бэкенд процесса (relcache, catcache). В первый раз планировщик сходил почитать страницы метаданных, что-то уже было в shared_buffers, а что-то из OS и как итог засунул в локальный кэш бэкенд процесса relcache и catcache.
Давайте попробуем прикинуть настройки постгреса, с выделенным 16 ГБ ОЗУ.
- shared_buffers - пример классическую отправную точку 25%. 4 ГБ.
- work_mem - память для каждой сортировки и хеш. 16-64 МБ. На каждую! сортировку. 10 соединений могут дать 640 МБ в сумме.
- maintenance_work_mem - необходим приложению для вакуума и созданию индексов. 512МБ - 1ГБ
- effective_cache_size - показатель использует планировщик. Чем этот параметр больше, тем охотнее будет использовать индексы планировщик.
Durability. WAL
А что будет если приложение внезапно отключиться? Например, у нас все данные лежат в памяти, а на диске их еще нет. Что тогда?
Существует так называемый журнал WAL (Write Ahead Log). Он обеспечивает букву D из известной вам аббревиатуры ACID.
Давайте попробуем убить постгрес во время инсерта данных.
Для примера создадим таблицу, вставим строку. Принудительно скинем на диск на данные с помощью CHECKPOINT. И также вставим еще одну строку, но скидывать принудительно не будет.
pagila=# CREATE TABLE crashtest(id int, val text);
INSERT INTO crashtest VALUES (1, 'SURVIVES');
CHECKPOINT;
INSERT INTO crashtest VALUES (2, 'ALSO_SURVIVES');
CREATE TABLE
INSERT 0 1
CHECKPOINT
INSERT 0 1
Узнаем под каким наименованием файл таблицы crashtest. Файл имеет название 40988.
pagila=# SELECT pg_relation_filepath('crashtest');
pg_relation_filepath
----------------------
base/24686/40988
(1 row)
Перейдем в OS. Прочитаем данные с файла и увидим только значение SURVIVES. Вторая строка со значением ALSO_SURVIVES еще не упал диск. Он пока что только в памяти приложения.
tether757@DESKTOP-HHRSD0U:~$ strings $HOME/pgdata/base/24686/40988
SURVIVES
Убьем "внезапно" постгрес.
tether757@DESKTOP-HHRSD0U:~$ pkill -9 -f "postgres -D $HOME/pgdata"
Поднимем его обратно и подключимся к бд.
tether757@DESKTOP-HHRSD0U:~$ export PATH=$HOME/pgdev/bin:$PATH
tether757@DESKTOP-HHRSD0U:~$ pg_ctl -D $HOME/pgdata -o "-p 5555" -l $HOME/pgdata/logfile start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started
tether757@DESKTOP-HHRSD0U:~$ psql -U tether757 -d pagila -p 5555
psql (18.6)
Type "help" for help.
Сделаем запрос и узнаем данные в таблице crashtest и увидим, что вторая строка также есть. Как же он появился? Ведь мы видели, что приложение не скинуло на диск.
pagila=# SELECT * FROM crashtest;
id | val
----+---------------
1 | SURVIVES
2 | ALSO_SURVIVES
(2 rows)
В этом нам помогут логи постгреса.
tether757@DESKTOP-HHRSD0U:~$ tail -20 $HOME/pgdata/logfile
2026-09-19 20:10:18.096 +05 [1580] LOG: database system was shut down at 2026-09-19 18:06:06 +05
2026-09-19 20:10:18.101 +05 [1574] LOG: database system is ready to accept connections
2026-09-19 20:10:28.212 +05 [1588] FATAL: lock file "postmaster.pid" already exists
2026-09-19 20:10:28.212 +05 [1588] HINT: Is another postmaster (PID 1574) running in data directory "/home/tether757/pgdata"?
2026-09-19 20:13:38.411 +05 [1578] LOG: checkpoint starting: immediate force wait
2026-09-19 20:13:38.424 +05 [1578] LOG: checkpoint complete: wrote 27 buffers (0.2%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.005 s, total=0.014 s; sync files=23, longest=0.002 s, average=0.001 s; distance=171 kB, estimate=171 kB; lsn=0/5733978, redo lsn=0/5733920
2026-09-19 20:13:38.427 +05 [1592] ERROR: relation "shared_buffers_demo" does not exist at character 29
2026-09-19 20:13:38.427 +05 [1592] STATEMENT: SELECT pg_relation_filepath('shared_buffers_demo');
2026-09-19 20:14:35.107 +05 [1592] FATAL: terminating connection due to unexpected postmaster exit
2026-09-19 20:15:17.605 +05 [1694] LOG: starting PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
2026-09-19 20:15:17.605 +05 [1694] LOG: listening on IPv4 address "127.0.0.1", port 5555
2026-09-19 20:15:17.608 +05 [1694] LOG: listening on Unix socket "/tmp/.s.PGSQL.5555"
2026-09-19 20:15:17.611 +05 [1700] LOG: database system was interrupted; last known up at 2026-09-19 20:13:38 +05
2026-09-19 20:15:17.755 +05 [1700] LOG: database system was not properly shut down; automatic recovery in progress
2026-09-19 20:15:17.757 +05 [1700] LOG: redo starts at 0/5733920
2026-09-19 20:15:17.757 +05 [1700] LOG: invalid record length at 0/5735680: expected at least 24, got 0
2026-09-19 20:15:17.757 +05 [1700] LOG: redo done at 0/5735648 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-09-19 20:15:17.760 +05 [1698] LOG: checkpoint starting: end-of-recovery immediate wait
2026-09-19 20:15:17.768 +05 [1698] LOG: checkpoint complete: wrote 2 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.002 s, total=0.010 s; sync files=4, longest=0.001 s, average=0.001 s; distance=7 kB, estimate=7 kB; lsn=0/5735680, redo lsn=0/5735680
2026-09-19 20:15:17.769 +05 [1694] LOG: database system is ready to accept connections
Видим, что приложение было выключено не нормально.
2026-09-19 20:15:17.755 +05 [1700] LOG: database system was not properly shut down; automatic recovery in progress
Видим, что журнал начал проигрывать
2026-09-19 20:15:17.757 +05 [1700] LOG: redo starts at 0/5733920
Определил инвалид значение конца журнала
2026-09-19 20:15:17.757 +05 [1700] LOG: invalid record length at 0/5735680: expected at least 24, got 0
Закончил восстанавливать данные
2026-09-19 20:15:17.757 +05 [1700] LOG: redo done at 0/5735648 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
Сделал чекпоинт на диск. То бишь всё восстановление сначала происходит в памяти и потом уходит на диск.
2026-09-19 20:15:17.760 +05 [1698] LOG: checkpoint starting: end-of-recovery immediate wait
Именно так и обеспечивается Durability в PostgreSQL.
А что же внутри журнала?
Посмотрим, что лежит в директории журнала. Директория имеет наименование pg_wal.
tether757@DESKTOP-HHRSD0U:~$ cd $HOME/pgdata/pg_wal/
tether757@DESKTOP-HHRSD0U:~/pgdata/pg_wal$ ls
000000010000000000000005 000000010000000000000006 000000010000000000000007 000000010000000000000008 archive_status summaries
Прочитаем текущее положение офсета в журнале WAL. Оно равно 0/5737550
pagila=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/5737550
(1 row)
Вставим строку в таблицу crashtest. И прочитаем новое положение журнала. Оно стало равным 0/5737658.
pagila=# INSERT INTO crashtest VALUES (3, 'THIRD');
INSERT 0 1
pagila=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/5737658
(1 row)
Прочитаем из журнала данный отрезок командой pg_waldump, подав туда отрезки офсета.
tether757@DESKTOP-HHRSD0U:~/pgdata/pg_wal$ pg_waldump -p $HOME/pgdata/pg_wal -s 0/5737550 -e 0/5737658
rmgr: Heap len (rec/tot): 54/ 218, tx: 1319, lsn: 0/05737550, prev 0/05737518, desc: INSERT off: 3, flags: 0x00, blkref #0: rel 1663/24686/40988 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 1319, lsn: 0/05737630, prev 0/05737550, desc: COMMIT 2026-09-20 12:49:00.224347 +05
Журнал хранит не SQL-текст, а физическое описание: "на такой-то странице по такому-то указателю появился такой-то кортеж".
Также заглянем в реализацию src/backend/storage/buffer/bufmgr.c Функция записи в журнал XLogFlush вызывается раньше функции записи данных smgrwrite.
/*
* Force XLOG flush up to buffer's LSN. This implements the basic WAL
* rule that log updates must hit disk before any of the data-file changes
* they describe do.
*
* However, this rule does not apply to unlogged relations, which will be
* lost after a crash anyway. Most unlogged relation pages do not bear
* LSNs since we never emit WAL records for them, and therefore flushing
* up through the buffer LSN would be useless, but harmless. However,
* GiST indexes use LSNs internally to track page-splits, and therefore
* unlogged GiST pages bear "fake" LSNs generated by
* GetFakeLSNForUnloggedRel. It is unlikely but possible that the fake
* LSN counter could advance past the WAL insertion point; and if it did
* happen, attempting to flush WAL through that location would fail, with
* disastrous system-wide consequences. To make sure that can't happen,
* skip the flush if the buffer isn't permanent.
*/
if (buf_state & BM_PERMANENT)
XLogFlush(recptr); // journal first
...
smgrwrite(reln, // page of data
BufTagGetForkNum(&buf->tag),
buf->tag.blockNum,
bufToWrite,
false);
Concurrency handling
Все это время мы смотрели на работу бд в режиме одного бэкенд сервиса.
Теперь представим, что их теперь больше чем один. PostgreSQL manages concurrency through multiversion concurrency control (MVCC), which gives each transaction a "snapshot" of the database, allowing changes to be made without affecting other transactions.
Snapshot - это снимок состояния.
В работе с бд есть 4 аномалии из стандарта SQL (https://www.postgresql.org/docs/18/transaction-iso.html):
- dirty read. A transaction reads data written by a concurrent uncommitted transaction.
- nonrepeatable read. A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
- phantom read. A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
- serialization anomaly. The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.
PostgreSQL offers four levels of transaction isolation: Read Uncommitted, Read Committed, Repeatable Read and Serializable.
Transaction Isolation Levels
| Isolation Level | Dirty Read | Nonrepeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| Read uncommitted | Allowed, but not in PG | Possible | Possible | Possible |
| Read committed | Not possible | Possible | Possible | Possible |
| Repeatable read | Not possible | Not possible | Allowed, but not in PG | Possible |
| Serializable | Not possible | Not possible | Not possible | Not possible |
Read uncommitted. Документация говорит, что Dirty Read невозможен в постгресе. Давайте попробуем установить этот уровень изоляции. Зайдем с двух разных сессий (В реальности это могут быть два разных пода вашего приложения или два разных соединения из одного пода). Первая сессия. Начнем транзакцию и установим значение rental_rate.
pagila=# BEGIN;
BEGIN
pagila=*# UPDATE film SET rental_rate = 99.99 WHERE film_id = 1;
UPDATE 1
Вторая сессия. Начнем транзакцию, установим ее явно READ UNCOMMITTED. Попросим показать уровень изоляции транзакции. И достанем значение rental_rate. Видим 1.99, вместо 99.99. Хотя мы и указали уровень изоляции, но PostgreSQL ввиду своей архитектуры MVCC не позволяет произойти аномалии Dirty Read. Например, СУБД MySQL позволяет это сделать.
pagila=# BEGIN ISOLATION LEVEL READ UNCOMMITTED;
BEGIN
pagila=*# SHOW transaction_isolation;
transaction_isolation
-----------------------
read uncommitted
(1 row)
pagila=*# SELECT rental_rate FROM film WHERE film_id = 1;
rental_rate
-------------
1.99
(1 row)
Первая сессия. Перейдем обратно на первую сессию и закоммитим изменения.
pagila=*# commit;
COMMIT
Вторая сессия. Перейдем обратно на вторую сессию и снова достанем значение rental_rate. И мы видим, что изменения сделанные первой сессией наконец-то стали видимыми.
pagila=*# SELECT rental_rate FROM film WHERE film_id = 1;
rental_rate
-------------
99.99
(1 row)
pagila=*# COMMIT;
COMMIT
Давайте сразу поговорим и об аномалии non-repeatable read. Аномалия non-repeatable read про неконсистентное значение строки с которой работаешь. Во второй сессии мы получили совершенно разные результаты одного и того же запроса чтения. Сначала
pagila=*# SELECT rental_rate FROM film WHERE film_id = 1;
rental_rate
-------------
1.99
(1 row)
Затем
pagila=*# SELECT rental_rate FROM film WHERE film_id = 1;
rental_rate
-------------
99.99
(1 row)
А что из себя представляет аномалия phantom read? Она проявляется не в работе со строкой, а в работе с условиями. Чтобы, воспроизвести аномалию будем работать с изоляцией по умолчанию Read Committed. Попробуем воспроизвести. Также открываем две сессии в разных вкладках.
Первая сессия. Открываем транзакция, узнаем количество строк с rental_rate < 1.
pagila=# BEGIN;
BEGIN
pagila=*# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
340
(1 row)
Вторая сессия. Вставляем с автокоммитом строку, попадающую под условие WHERE rental_rate < 1.
pagila=# INSERT INTO film (title, language_id, rental_rate)
VALUES ('PHANTOM MOVIE', 1, 0.50);
INSERT 0 1
Первая сессия. Возвращаемся в первую сессию и узнаем количество строк с rental_rate < 1. Значение изменилось.
pagila=*# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
341
(1 row)
Первая сессия. Закрываем транзакцию
pagila=*# COMMIT;
COMMIT
Как видите изоляция READ COMMITTED позволяет еще не существующие строки с помощью условия WHERE. Давайте попробуем предотвратить это с помощью изоляции REPEATABLE READ.
Первая сессия. Начнем транзакцию с уровнем изоляции REPEATABLE READ.
pagila=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
pagila=*# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
341
(1 row)
Вторая сессия. Вставим строку подходящее под условие поиска.
pagila=# INSERT INTO film (title, language_id, rental_rate)
VALUES ('PHANTOM MOVIE 2', 1, 0.50);
INSERT 0 1
Первая сессия. Вернемся в первую сессию и сделает тот же запрос. И увидим, что изоляция нам помогла не получить несуществующие строки.
pagila=*# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
341
(1 row)
Первая сессия. Закончим транзакцию и сделаем тот же запрос. Теперь мы видим строки, которые были добавлены другой транзакцией.
pagila=*# COMMIT;
COMMIT
pagila=# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
342
(1 row)
Отличие Non-repeatable Read и Phantom Read аномалий на первый взгляд никакая. Нам всё также доступны строки, которые добавлены другой транзакцией. Но Non-Repeatable Read можно вылечить блокировкой строк, а Phantom Read нет. Давайте попробуем повторить опыты с блокировкой для Non-Repeatable Read и Phantom Read. Добавим FOR UPDATE в конце. Первая сессия. Начнем транзакцию и сделаем селект с блокировкой.
pagila=# BEGIN;
BEGIN
pagila=*# SELECT rental_rate FROM film WHERE film_id = 1 FOR UPDATE;
rental_rate
-------------
99.99
(1 row)
Вторая сессия. Обновляем ту же строку, что используем в первой сессии. Вы увидите, что вторая сессия ушла в WAITING первой.
pagila=# UPDATE film SET rental_rate = 55.55 WHERE film_id = 1;
Первая сессия. Сделаем селект из таблицы pg_stat_activity. Узнаем какие транзакции сейчас активны и увидим, что транзакция второй сессии ждет.
pagila=*# SELECT pid, wait_event_type, wait_event, state, left(query, 50) FROM pg_stat_activity WHERE backend_type = 'client backend';
pid | wait_event_type | wait_event | state | left
------+-----------------+---------------+--------+----------------------------------------------------
1117 | | | active | SELECT pid, wait_event_type, wait_event, state, le
3726 | Lock | transactionid | active | UPDATE film SET rental_rate = 55.55 WHERE film_id
(2 rows)
Первая сессия. Закончим транзакцию.
pagila=*# COMMIT;
COMMIT
Вторая сессия. Наконец-то увидим, что транзакция закончилась.
UPDATE 1
Аномалии не было: пока мы держали строку, изменить её никто не мог.
Теперь давайте попробуем предотвратить Phantom Read с помощью лока. Первая сессия. Начнем транзакцию. Сделаем предварительный селект количества по условию. И также сделаем селект с блокировкой строк.
pagila=# BEGIN;
BEGIN
pagila=*# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
345
(1 row)
pagila=*# SELECT film_id FROM film WHERE rental_rate < 1 FOR UPDATE;
film_id
---------
475
360
213
11
...
998
(345 rows)
Вторая сессия. Вставляем строку подходящую под условие.
pagila=# INSERT INTO film (title, language_id, rental_rate)
VALUES ('GHOST_LOCK MOVIE', 1, 0.50);
INSERT 0 1
Первая сессия. Сделаем селект с условием и увидим, что нам доступна строка, которой еще не существует хоть мы и заблокировали строки до этого. Логично, ведь строки, то не было еще, то есть мы и не могли ее никак заблокировать.
pagila=*# SELECT count(*) FROM film WHERE rental_rate < 1;
count
-------
346
(1 row)
Первая сессия. Заканчиваем транзакцию
pagila=*# COMMIT;
COMMIT
Как итог: Блокировка не помогает защититься от phantom read при дефолтном выборе изоляции.
Что же такое Serialization Anomaly?
- read-only transaction anomaly
- write skew anomaly (читают общее условие, пишут в разные строки) Заведём два счёта и правило: суммарный баланс не должен уходить в минус.
Какие блокировки существуют?
Explicit Locking Table-Level Locks ACCESS SHARE (AccessShareLock) Conflicts with the ACCESS EXCLUSIVE lock mode only. The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode. ROW SHARE (RowShareLock) Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE options is specified (in addition to ACCESS SHARE locks on any other tables that are referenced without any explicit FOR ... locking option). ROW EXCLUSIVE (RowExclusiveLock) Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The commands UPDATE, DELETE, INSERT, and MERGE acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table. SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs. Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands). SHARE (ShareLock) Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes. Acquired by CREATE INDEX (without CONCURRENTLY). SHARE ROW EXCLUSIVE (ShareRowExclusiveLock) Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time. Acquired by CREATE TRIGGER and some forms of ALTER TABLE. EXCLUSIVE (ExclusiveLock) Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode. Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY. ACCESS EXCLUSIVE (AccessExclusiveLock) Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way. Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Once acquired, a lock is normally held until the end of the transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it. Table 13.2. Conflicting Lock Modes
Table 13.2. Conflicting Lock Modes
| Requested \ Held | ACCESS SHARE | ROW SHARE | ROW EXCL. | SHARE UPDATE EXCL. | SHARE | SHARE ROW EXCL. | EXCL. | ACCESS EXCL. |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE | X | |||||||
| ROW SHARE | X | X | ||||||
| ROW EXCL. | X | X | X | X | ||||
| SHARE UPDATE EXCL. | X | X | X | X | X | |||
| SHARE | X | X | X | X | X | |||
| SHARE ROW EXCL. | X | X | X | X | X | X | ||
| EXCL. | X | X | X | X | X | X | X | |
| ACCESS EXCL. | X | X | X | X | X | X | X | X |
| Lock mode | Acquired by |
|---|---|
| ACCESS SHARE | SELECT |
| ROW SHARE | SELECT ... FOR UPDATE / FOR SHARE |
| ROW EXCLUSIVE | INSERT, UPDATE, DELETE, MERGE |
| SHARE UPDATE EXCLUSIVE | VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY |
| SHARE | CREATE INDEX |
| SHARE ROW EXCLUSIVE | CREATE TRIGGER, some ALTER TABLE forms |
| EXCLUSIVE | REFRESH MATERIALIZED VIEW CONCURRENTLY |
| ACCESS EXCLUSIVE | DROP, TRUNCATE, VACUUM FULL, most ALTER TABLE forms |
Row-Level Locks FOR UPDATE FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values of certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future. FOR NO KEY UPDATE Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock. FOR SHARE Behaves similarly to FOR NO KEY UPDATE, except that it acquires a shared lock rather than exclusive lock on each retrieved row. A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE. FOR KEY SHARE Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.
PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.
Table 13.3. Conflicting Row-Level Locks
| Requested \ Held | FOR KEY SHARE | FOR SHARE | FOR NO KEY UPDATE | FOR UPDATE |
|---|---|---|---|---|
| FOR KEY SHARE | X | |||
| FOR SHARE | X | X | ||
| FOR NO KEY UPDATE | X | X | X | |
| FOR UPDATE | X | X | X | X |
| Lock mode | Acquired by |
|---|---|
| FOR KEY SHARE | SELECT ... FOR KEY SHARE, foreign key checks on the referenced row |
| FOR SHARE | SELECT ... FOR SHARE |
| FOR NO KEY UPDATE | UPDATE that doesn't change key columns, SELECT ... FOR NO KEY UPDATE |
| FOR UPDATE | DELETE, UPDATE changing key columns, SELECT ... FOR UPDATE |
Page-Level Locks 0 information lol. need to see into code.
Deadlocks
First transaction
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 1;
Second transaction
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 2;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 1;
Advisory Locks. Локи в общей памяти. Пример их использования. Например, эти блокировки использует Flyway.
private boolean tryLockTransactional() throws SQLException {
List<Boolean> results = this.jdbcTemplate.query("SELECT pg_try_advisory_xact_lock(" + this.lockNum + ")", (rs) -> rs.getBoolean("pg_try_advisory_xact_lock"), new Object[0]);
return results.size() == 1 && (Boolean)results.get(0);
}
private boolean tryLock() throws SQLException {
List<Boolean> results = this.jdbcTemplate.query("SELECT pg_try_advisory_lock(" + this.lockNum + ")", (rs) -> rs.getBoolean("pg_try_advisory_lock"), new Object[0]);
return results.size() == 1 && (Boolean)results.get(0);
}
Vacuum and bloat, horizon
PostgreSQL databases require periodic maintenance known as vacuuming. For many installations, it is sufficient to let vacuuming be performed by the autovacuum daemon, whose purpose is to automate the execution of VACUUM and ANALYZE commands. Some database administrators will want to supplement or replace the daemon's activities with manually-managed VACUUM commands, which typically are executed according to a schedule by cron or Task Scheduler scripts
PostgreSQL's VACUUM command has to process each table on a regular basis for several reasons:
- To recover or reuse disk space occupied by updated or deleted rows.
- To update data statistics used by the PostgreSQL query planner.
- To update the visibility map, which speeds up index-only scans.
- To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.
Each of these reasons dictates performing VACUUM operations of varying frequency and scope, as explained in the following subsections.
There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.) VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table it is working on, and therefore cannot be done in parallel with other use of the table. Generally, therefore, administrators should strive to use standard VACUUM and avoid VACUUM FULL.
VACUUM creates a substantial amount of I/O traffic, which can cause poor performance for other active sessions.
VACUUM FULL rewrites table into new table. It described in cluster.c file.
static void
rebuild_relation(Relation OldHeap, Relation index, bool verbose)
...
/* Copy the heap data into the new table in the desired order */
copy_table_data(NewHeap, OldHeap, index, verbose,
&swap_toast_by_content, &frozenXid, &cutoffMulti);
...
Что такое bloat? Давайте посмотрим на примере. Создадим таблицу и вставим 100_000 записей.
pagila=# CREATE TABLE bloat_demo(id int PRIMARY KEY, val text);
CREATE TABLE
pagila=# INSERT INTO bloat_demo SELECT i, repeat('x', 100) FROM generate_series(1, 100000) i;
INSERT 0 100000
Узнаем размер таблицы. Она занимает 13 МБ.
pagila=# SELECT pg_size_pretty(pg_relation_size('bloat_demo'));
pg_size_pretty
----------------
13 MB
(1 row)
Узнаем количество живых и мертвых кортежей.
pagila=# SELECT tuple_count, dead_tuple_count, free_percent FROM pgstattuple('bloat_demo');
tuple_count | dead_tuple_count | free_percent
-------------+------------------+--------------
100000 | 0 | 0.64
(1 row)
Сделаем апдейт, чтобы добавить еще 100_000 строк. Таблица увеличилась в размерах в 2 раза.
pagila=# UPDATE bloat_demo SET val = repeat('y', 100);
UPDATE 100000
pagila=# SELECT pg_size_pretty(pg_relation_size('bloat_demo'));
pg_size_pretty
----------------
27 MB
(1 row)
Посмотрим на количество мертвых строк. Их 0.
pagila=# SELECT dead_tuple_count, free_percent FROM pgstattuple('bloat_demo');
dead_tuple_count | free_percent
------------------+--------------
0 | 74.9
(1 row)
pagila=# INSERT INTO bloat_demo SELECT i, repeat('z', 100) FROM generate_series(1, 50000) i;
INSERT 0 50000
pagila=# SELECT pg_size_pretty(pg_relation_size('bloat_demo'));
pg_size_pretty
----------------
27 MB
(1 row)
Узнаем местоположение и наименование файла таблицы. Получим файл с именем 49190
pagila=# SELECT pg_relation_filepath('bloat_demo');
pg_relation_filepath
----------------------
base/24686/49190
(1 row)
Сделаем VACUUM FULL таблицы. Проверим местоположение и наименование файла таблицы. Получили другой файл 49198
pagila=# VACUUM FULL bloat_demo;
VACUUM
pagila=# SELECT pg_relation_filepath('bloat_demo');
pg_relation_filepath
----------------------
base/24686/49198
(1 row)
Память очистилась
pagila=# SELECT pg_size_pretty(pg_relation_size('bloat_demo'));
pg_size_pretty
----------------
13 MB
(1 row)
Сделаем list в директории базы данных. И файл 49190 исчез после VACUUM FULL.
tether757@DESKTOP-HHRSD0U:~/pgdata/base/24686$ ls *4919*
49198 49198_fsm 49198_vm
Что такое горизонт? Это максимальный xmin Long transaction keeps horizon.
Долгая транзакция мешает работе VACUUM, потому что держит горизонт.
Давайте проверим.
Откроем первую сессию. Начнем транзакцию.
pagila=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
pagila=*# SELECT count(*) FROM film;
count
-------
1006
(1 row)
Вторая сессия. Начнем вторую транзакцию. Обновим 100_000 строк в таблице bloat_demo.
pagila=# UPDATE bloat_demo SET val = 'again';
UPDATE 100000
pagila=# VACUUM VERBOSE bloat_demo;
Вторая сессия. Вызовем VACUUM с VERBOSE (with commentaries).
pagila=# VACUUM VERBOSE bloat_demo;
INFO: vacuuming "pagila.public.bloat_demo"
INFO: finished vacuuming "pagila.public.bloat_demo": index scans: 0
pages: 0 removed, 2256 remain, 2256 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 200000 remain, **100000 are dead but not yet removable**
removable cutoff: 1341, which was 1 XIDs old when operation ended
new relfrozenxid: 1341, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.249 MB/s
buffer usage: 4531 hits, 0 reads, 1 dirtied
WAL usage: 1 records, 1 full page images, 6607 bytes, 0 buffers full
system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s
INFO: vacuuming "pagila.pg_toast.pg_toast_49190"
INFO: finished vacuuming "pagila.pg_toast.pg_toast_49190": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 1341, which was 1 XIDs old when operation ended
new relfrozenxid: 1341, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 11.695 MB/s, avg write rate: 11.695 MB/s
buffer usage: 19 hits, 1 reads, 1 dirtied
WAL usage: 2 records, 1 full page images, 1407 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
Первая сессия. Закончим первую сессию.
pagila=*# COMMIT;
COMMIT
Вторая сессия. Вернемся и еще раз сделаем VACUUM. Видим, что значение are dead but not yet removable - теперь 0.
pagila=# VACUUM VERBOSE bloat_demo;
INFO: vacuuming "pagila.public.bloat_demo"
INFO: finished vacuuming "pagila.public.bloat_demo": index scans: 0
pages: 0 removed, 2256 remain, 1 scanned (0.04% of total), 0 eagerly scanned
tuples: 0 removed, 100000 remain, **0 are dead but not yet removable**
removable cutoff: 1343, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 20 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pagila.pg_toast.pg_toast_49190"
INFO: finished vacuuming "pagila.pg_toast.pg_toast_49190": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 1343, which was 0 XIDs old when operation ended
new relfrozenxid: 1343, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 0 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
То есть VACUUM в теории может вообще не очистить мертвые строки. Только повторный вызов при более благоприятных условиях уберет мертвые строки.
Поэтому долгие транзакции очень опасны. К примеру рест запросы в методе обернутым @Transactional бывают причиной bloat.
@Transactional
public void process(Long id) {
Order o = repo.findById(id);
var result = restTemplate.postForObject(url, req, Response.class);
o.setStatus(result.status());
}
Indexes
Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.
Давайте посмотрим влияние индекса. Сначала сделаем таблицу без индексов и заполним ее данными.
pagila=# CREATE TABLE idx_demo(id int, val text);
CREATE TABLE
pagila=# INSERT INTO idx_demo SELECT i, 'row_' || i FROM generate_series(1, 200000) i;
INSERT 0 200000
Сделаем принудительно ANALYZE, чтобы у планировщика точно были актуальные данные по количеству строк.
pagila=# ANALYZE idx_demo;
ANALYZE
Сделаем EXPLAIN на селекте. Так как у нас нет индекса, планировщик выберет Seq Scan. И мы можем увидеть, что было прочитано 1082 страницы по Buffers: shared hit=1082, и время выполнения Execution Time: 25.010 ms.
pagila=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM idx_demo WHERE id = 150000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on idx_demo (cost=0.00..3582.00 rows=1 width=14) (actual time=18.795..24.982 rows=1.00 loops=1)
Filter: (id = 150000)
Rows Removed by Filter: 199999
Buffers: shared hit=1082
Planning:
Buffers: shared hit=13
Planning Time: 0.169 ms
Execution Time: 25.010 ms
(8 rows)
Стоит также обратить внимание на количество отброшенных строк Rows Removed by Filter: 199999. То есть мы прочитали все 200_000.
Теперь добавим индекс.
pagila=# CREATE INDEX idx_demo_id ON idx_demo(id);
CREATE INDEX
Сделаем также EXPLAIN на селекте. На этот раз планировщик выбрал Index Scan. Количество страниц уменьшилось до 4: Buffers: shared hit=1 read=3. И время выполнения уменьшилось значительно Execution Time: 0.070 ms.
pagila=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM idx_demo WHERE id = 150000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_demo_id on idx_demo (cost=0.42..8.44 rows=1 width=14) (actual time=0.050..0.051 rows=1.00 loops=1)
Index Cond: (id = 150000)
Index Searches: 1
Buffers: shared hit=1 read=3
Planning:
Buffers: shared hit=15 read=1
Planning Time: 0.256 ms
Execution Time: 0.070 ms
(8 rows)
Мы видим огромную разницу времени выполнения. Стало Execution Time: 0.070 ms. А было Execution Time: 25.010 ms.
Основной трейдофф использования индекса - это размер. Давайте узнаем размер таблицы idx_demo. Она составляет 8656 kB.
pagila=# SELECT pg_size_pretty(pg_relation_size('idx_demo'));
pg_size_pretty
----------------
8656 kB
(1 row)
И также узнаем размер индекса. Она заняла почти половину от таблицы. Внушительно, не правда ли?
pagila=# SELECT pg_size_pretty(pg_relation_size('idx_demo_id'));
pg_size_pretty
----------------
4408 kB
(1 row)
Заглянем внутрь индекса. Сначала достанем метаданные индекса.
pagila=# SELECT * FROM bt_metap('idx_demo_id');
magic | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
340322 | 4 | 290 | 2 | 290 | 2 | 0 | -1 | t
(1 row)
Здесь нам интересны колонки:
- level - это максимальный уровень дерева индекса.
- last_cleanup_num_delpages - это количество удаленных страниц во время последнего вакума. VACUUM также очищает и индексы. А VACUUM FULL создает новый файл с индексами без bloat.
Мы увидели, что для таблицы со строками 200_000 уровень дерева индекса всего лишь 2.