Всем привет.
Меня зовут Сергей, я — эксперт компании Bercut. За плечами — более 20 лет работы с различными СУБД (PostgreSQL, Oracle, MS Access, MS FoxPro, InterBase) и высоконагруженными системами на их основе.
В Bercut мы занимаемся разработкой и развитием IT-продуктов, решений для операторов цифровых услуг и мобильных сервисов. Наши системы работают на различном железе, разных СУБД и обслуживают 24x7x365 в режиме онлайн сотни миллионов абонентов.
Сегодня поговорим о том, как оптимизировать хранение данных в PostgreSQL, снизив объем дискового пространства, потребляемого таблицами и ускорить выборку данных. Это может быть особенно актуально после перевода информационной системы с другой СУБД на PostgreSQL.
Навигация:
Эксперимент № 1. Вызовы функций и быстродействие запроса
Эксперимент № 2. Типы данных и размер таблицы
Эксперимент № 3. Положение столбцов переменной длины
Эксперимент № 4. Положение столбцов типа integer
Эксперимент № 6. Тип integer для первичного ключа
Эксперимент № 8. Массовое изменение данных
Последние годы PostgreSQL уверенно набирает популярность на рынке СУБД. Причин тому много, назову некоторые из них:
-
система имеет открытый исходный код:
есть возможность изучить напрямую его, а не только с чьих-то слов и по документации. Количество человек, разбирающихся в строении данной СУБД на порядки больше. Документация в теории может быть неполной, устаревшей или не переведенной/некачественно переведенной на русский язык и и не давать ответы на некоторые вопросы. Чего не скажешь об исходном коде. Если, конечно, в нем разобраться.
можно собрать СУБД из исходных кодов для любой системы и конфигурации оборудования, внести незначительные изменения в любой компонент системы (есть множество параметров, которые настраиваются только на уровне исходного кода), если требуется что-то специфическое. А если не просто параметр поменять, а встроить какую-то возможность, которой в системе нет?
в случае какой-то критичной или трудновоспроизводимой проблемы, есть теоретическая возможность внести изменения в код самостоятельно. Точно и быстро диагностировать проблему и даже устранить, а не ждать помощи от службы поддержки. Известны случаи, когда ожидание помощи от некоторых производителей СУБД растягивалось на годы. То проблема не воспроизводится, то воспроизводится, но никто ничего понять не может. То ключевой сотрудник в отпуске. То ошибка понятна, но требуется критичное исправление ядра системы, а потому нужно ждать очередного релиза, который будет выпущен в следующем году. Я не говорю, что стоит заниматься правкой исходных кодов СУБД – в любом случае их много и они сложные. Но возможность такая есть. Также есть набор регрессионных тестов, чтобы было меньше шансов что-то сломать.
-
Существует большое количество расширений, добавляющих дополнительные возможности в систему. Это и поддержка различных типов данных (геометрических, географических и т.д.) и диагностика и прочее.
-
Если пользователь системы находится в России, то имеется еще одно преимущество – компания-разработчик СУБД также российская. А это значит, что вся документация на русском, не будет никаких проблем с недопониманием из-за трудностей перевода.
-
Это свободно распространяемая СУБД, а значит пользоваться «ванильной» версией можно абсолютно бесплатно. Цена лицензии же на коммерческую версию значительно ниже цен многих конкурентов.
Говоря о переходе с разных СУБД на PostgreSQL, отметим, что миграция — задача непростая требует участия многих специалистов, как по части логики системы (разный синтаксис запросов, языков хранимых процедур, их особенностей работы), так и по структуре данных.
На первый взгляд, таблицы и индексы работают плюс-минус одинаково во всех СУБД. Но! Вот вы:
-
мигрировали схему данных из своей старой СУБД в PostgreSQL.
-
мигрировали код
-
проверили логику систему. Для легкой и успешной проверки системы понадобится хорошее покрытие кода (читай – критически важных для системы бизнес-кейсов) независимыми от СУБД автотестами. Если есть десятки тысяч таких тестов для каждой вашей системы (у нас – есть, вот моя публикация на этот счет), то это несомненный плюс.
-
посмотрели запросы – планы запросов хорошие, работать должны оптимально. Все таблицы, данные и индексы из старой системы в новую перенеслись, выборка по ним идет, все супер. Действительно ли так? Нет. Зная особенности исходной и, главное, целевой СУБД, можно сделать лучше.
Удачная структура данных – это то, с чего начинается любая информационная система.
Статья для тех, кому кажется, что все возможное для оптимизации уже сделано, а хочется большего.
Тестовый пример
Рассмотрим упрощенный пример оптимизации структуры данных для PostgreSQL. Предположим, имеется таблица клиентов customer, у каждого клиента есть:
-
фамилия,
-
имя,
-
отчество,
-
адрес регистрации
-
фактический адрес.
В рамках данного примера будем считать, что адрес — это не отдельная сущность (хранится в другой таблице), а просто набор атрибутов клиента, причем адреса всегда ровно два на клиента. Забудем, что такое КЛАДР, ведь мы разрабатываем информационную систему для далекого сказочного королевства, где вечное лето. Не будет никаких литер, корпусов, строений, дробей и букв в номере дома. Только числовой номер. При этом, один сотрудник компании (или, может, это был сам клиент) вводил адрес, а другой должен когда-нибудь его верифицировать. Каждое из полей в отдельности. Помним, какое длинное имя у короля Таиланда, потому в полях Имя, Фамилия и Отчество заложим строки переменной длины максимум по 200 символов. В любой таблице должен быть первичный ключ, это поле обычно первое в таблице. Далее идут столбцы «Имя», «Фамилия» и Отчество» — первое что приходит в голову, когда речь про клиента. После них все поля, относящиеся к адресу. Это будут внешние ключи, а фактические названия городов и улиц хранятся в отдельных справочниках. Не будем их тут приводить и указывать в скрипте, что это внешние ключи. Для упрощения кода также не покажем, какие в таблице есть ограничения и проверки (почтовый индекс всегда ровно 6 цифр, признак верификации not null и т.д.). Допустим, таблица была в СУБД Oracle и имела вот такой вид:
create table customer( cust_id number primary key, -- идентификатор клиента first_name varchar2(200), -- фамилия midle_name varchar2(200), -- имя last_name varchar2(200), -- отчество -- адрес регистрации is_reg_idx_verif number, -- индекс адреса регистрации верифицирован? reg_index number, -- индекс адреса регистрации is_reg_town_verif number, -- город адреса регистрации верифицирован? reg_town_id number, -- город адреса регистрации is_reg_street_verif number, -- улица адреса регистрации верифицирована? reg_street_id number, -- улица адреса регистрации is_reg_house_verif number, -- дом адреса регистрации верифицирован? reg_house number, -- дом адреса регистрации is_reg_flat_verif number, -- квартира адреса регистрации верифицирована? reg_flat number, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif number, -- индекс адреса проживания верифицирован? fact_index number, -- индекс адреса проживания is_fact_town_verif number, -- город адреса проживания верифицирован? fact_town_id number, -- город адреса проживания is_fact_street_verif number,-- улица адреса проживания верифицирована? fact_street_id number, -- улица адреса проживания is_fact_house_verif number, -- дом адреса регистрации верифицирован? fact_house number, -- дом адреса проживания is_fact_flat_verif number, -- квартира адреса проживания верифицирована? fact_flat number -- квартира адреса проживания );О типе number
Как мы видим, у всех столбцов, имеющих числовой тип, указан просто тип number. В теории, можно было указать number(10) у первичного ключа, пользы от этого мало. Дело в том, что СУБД Oracle расходует на хранение значения атрибута числового типа ровно столько байт, сколько требуется, чтобы уместить это значение. Например, значение 0 занимает 1 байт, значение 255 тоже 1 байт, 1000 – уже два байта и т.д. Если вместо number напишем number(10), будет все ровно тоже самое, только при вставке значения в таблицу будут выполняться две проверки:
-
введенное число – целое
-
количество знаков в числе не превышает 10.
Мы-то знаем, что это первичный ключ, пишем в него всегда значение из последовательности. Для чего нам эти проверки? Даже если каким-то чудом вставим вещественное значение, чем оно помешает? Все индексы, все ссылки по внешним ключам из других таблиц будут работать точно также, как с целыми. Проверки создают дополнительную нагрузку на CPU. Крайне незначительную, но тем не менее. Основная проблема в другом. Задавая тип number(10) вместо number, мы создаем мину замедленного действия. Конечно можно было написать number(15) и такого большого числа хватит навсегда, но есть ли вообще смысл в ограничении?
Скрытый текст
Помните, была «проблема 2000»? При разработке ПО в 1980х годах многие разработчики экономили 1 или 2 байта, сохраняя в дату только 2 цифры года, а не все 4. То есть вместо 10 января 1998 записывали 10 января 98. Казалось — зачем хранить лишние 2 байта в каждой дате, если 2000 год так далеко, что никогда не наступит. А потом оказалось, что 2000 год уже очень близко и система не поймет, какой год наступил — 2000 или 1900. Пришлось срочно править старые программы. Повезло — программ тогда было куда меньше, причем они более простые, чем сейчас. Так вот, «проблема 2000» — это лишь частный случай.
Сейчас мы думаем, что никогда у нас в системе не будет более чем 9 999 999 999 клиентов. Но, предположим, что речь идет не о клиентах, а о платежах, которые выполняются по банковским картам. Пусть есть 10 млн клиентов, каждый из них где-то 5 раз в день рассчитывается своей картой. Допустим, 2 раза в день оплачивает транспорт, 1 раз обедает в столовой, потом еще пьет кофе из автомата, а вечером заходит в продуктовый магазин. Это 50 млн. операций в сутки. Тогда, 9 999 999 999 операций будут совершены примерно за 200 дней. Через 200 дней наша система сломается и потребует срочного моментального вмешательства DBA и прочих технических специалистов. 200 дней — это если наша система является заменой какой-то другой давно работающей системы и потому в ней сразу существует 10 млн. активных клиентов. Если же нет, то клиенты добавлялись в систему постепенно и сломается она не через 200 дней, а через 5-10 лет работы.
А пока DBA, тех. поддержка и разработчики системы не разобрались и не устранили проблему, ни один наш клиент не сможет совершить ни одного платежа. Решение на первый взгляд предельно простое – выполнить команду alter table <<название таблицы>> modify <<название столбца>> number; Но только в теории все так просто.
Сколько времени нужно, чтобы посреди ночи найти и разбудить DBA и разработчиков системы? А чтобы понять, что сломалось, как нужно восстановить работоспособность, применить решение? Хорошо, если логирование дает ответ на вопрос «что происходит». А если нет? Тем более когда система уже лет 10 как работает в таком виде и никого ее из разработчиков уже не найти.
По факту, меняя тип колонки, мы объявляем невалидными все пакеты, использующие эту таблицу. Требуется их перекомпиляция. Это еще несколько минут времени. Причем простой уже не только подсистемы проведения платежей, а вообще всего.
После перекомпиляции все сессии придется рестартовать, иначе они не будут работать с перекомпилированными пакетами, а это еще какое-то время.
Может оказаться, что данный столбец используется в функциональном индексе, тогда система не позволит выполнить alter, пока вы не удалите индекс. Удалим индекс, поменяем тип колонки. Без индекса запросы, которые ранее выполнялись за 0.001 секунды, будут висеть по 5 часов… Значит, требуется после изменения типа столбца, создать индекс заново. Создание индекса на большой таблице может занимать много часов…
А вдруг таблица с этим столбцом не простая, а партиционированная? По хешу от данного столбца имеются подпартиции. Тогда система тоже не позволит выполнить alter. И не получится тут просто, хоть и долго, как с индексом. Решение есть, но нетривиальное.
Наверняка, в коде пакетов и типов системы, встречается что-то типа v_pay_id number(10) ? Придется найти и исправить все эти места, а это много времени на доработку, компиляцию и т.д.
Итак, кто-то лет 10 назад решил якобы сэкономить сколько-то байт на строку (а по факту ничего не сэкономил), теперь система будет неработоспособна, вероятно, сутки или около того. Пока в аварийном режиме все силы разработчиков будут направлены на ее восстановление. Впрочем, есть лайфхак, позволяющий передвинуть часы судного дня еще на 200 дней вперед. Буквально за 1 секунду потушить пожар, присвоив последовательности, из которой берутся значения pay_id значение 0 и шаг -1. То есть, раньше pay_id всегда были только положительные, больше нет доступных положительных значений. Но есть доступные отрицательные и мы задействуем их. Если в коде нет сортировок и сравнений «больше»/«меньше» по значению поля, а оно используется только для идентификации строк в таблице.
Скорее всего, ключевой столбец у нас типа number. Да и все остальные, скорее всего, тоже number. Даже если поле – это признак «верифицировано/не верифицировано». Ведь никаких логических типов boolean таблицах и в sql в Oracle не существует. А если так, то инструмент миграции ничего не знает о том, какие значения могут записываться в таблицу. Потому number может превратиться в numeric или в bigint. Предположим, что инструмент отработал, мы там тоже что-то подправили, особо не думая о новых типах, везде оставили bigint. Почему bigint, а не numeric ? Потому что в документации написано «Однако операции со значениями numeric выполняются гораздо медленнее, чем с целыми числами или с типами с плавающей точкой». Нам же не нужны лишние тормоза? Почему во всех столбцах один и тот же тип? Потому что у нас нет времени думать над типом каждого столбца каждой таблицы, ведь таблиц в нашей системе тысячи, в каждой много столбцов, а помимо просто схемы данных есть еще и логика системы, которую тоже придется проверять и править?
Так вот, нужно обязательно закладывать время на детальный анализ структуры данных, включая и типы всех столбцов, и некоторые другие вещи, о которых скажу позже.
Подготовка БД
Тесты будем проводить на PostgreSQL 16. Да, на днях уже вышла 17 версия, в ней много всего интересного есть, в том числе улучшено быстродействие, но мало кто успел уже перейти на нее.
У нас был сгенерирован скрипт для создания таблицы в PostgreSQL в следующем виде:
-- отношение клиентов create table customer_etalon( cust_id bigint primary key, -- идентификатор клиента first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200), -- отчество -- адрес регистрации is_reg_idx_verif bigint, -- индекс адреса регистрации верифицирован? reg_index bigint, -- индекс адреса регистрации is_reg_town_verif bigint, -- город адреса регистрации верифицирован? reg_town_id bigint, -- город адреса регистрации is_reg_street_verif bigint, -- улица адреса регистрации верифицирована? reg_street_id bigint, -- улица адреса регистрации is_reg_house_verif bigint, -- дом адреса регистрации верифицирован? reg_house bigint, -- дом адреса регистрации is_reg_flat_verif bigint, -- квартира адреса регистрации верифицирована? reg_flat bigint, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif bigint, -- индекс адреса проживания верифицирован? fact_index bigint, -- индекс адреса проживания is_fact_town_verif bigint, -- город адреса проживания верифицирован? fact_town_id bigint, -- город адреса проживания is_fact_street_verif bigint,-- улица адреса проживания верифицирована? fact_street_id bigint, -- улица адреса проживания is_fact_house_verif bigint, -- дом адреса регистрации верифицирован? fact_house bigint, -- дом адреса проживания is_fact_flat_verif bigint, -- квартира адреса проживания верифицирована? fact_flat bigint -- квартира адреса проживания );CREATE TABLE
Query returned successfully in 157 msec.
Теперь попробуем понять, сколько места на диске будет занимать таблица. Пусть в ней имеется 1 млн. клиентов. Чтобы заполнить таблицу, понадобится функция генерации фамилии/имени/отчества путем присвоения туда строки случайных символов длиной от 0 до 30:
create function get_name() returns varchar volatile AS $$ select string_agg( chr(trunc(65+random()*26)::integer), '') from generate_series(1,(trunc(random()*30)::integer)); $$ language sql;CREATE FUNCTION
Query returned successfully in 70 msec.
Теперь сгенерируем 1 млн клиентов со случайными значениями во всех столбцах:
insert into customer_etalon( cust_id, -- идентификатор клиента first_name, -- фамилия midle_name, -- имя last_name, -- отчество -- адрес регистрации is_reg_idx_verif, -- индекс адреса регистрации верифицирован? reg_index, -- индекс адреса регистрации is_reg_town_verif, -- город адреса регистрации верифицирован? reg_town_id, -- город адреса регистрации is_reg_street_verif, -- улица адреса регистрации верифицирована? reg_street_id, -- улица адреса регистрации is_reg_house_verif, -- дом адреса регистрации верифицирован? reg_house, -- дом адреса регистрации is_reg_flat_verif, -- квартира адреса регистрации верифицирована? reg_flat, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif, -- индекс адреса проживания верифицирован? fact_index, -- индекс адреса проживания is_fact_town_verif, -- город адреса проживания верифицирован? fact_town_id, -- город адреса проживания is_fact_street_verif,-- улица адреса проживания верифицирована? fact_street_id, -- улица адреса проживания is_fact_house_verif, -- дом адреса регистрации верифицирован? fact_house, -- дом адреса проживания is_fact_flat_verif, -- квартира адреса проживания верифицирована? fact_flat -- квартира адреса проживания ) select cust_id as cust_id, -- идентификатор клиента get_name() as first_name, -- фамилия get_name() as midle_name, -- имя get_name() as last_name, -- отчество -- адрес регистрации case when trunc(random()*10) = 1 then 1 else 0 end as is_reg_idx_verif, trunc(random()*100000) as reg_index, case when trunc(random()*10) = 1 then 1 else 0 end as is_reg_town_verif, trunc(random()*10000) as reg_town_id, case when trunc(random()*10) = 1 then 1 else 0 end as is_reg_street_verif, trunc(random()*1000) as reg_street_id, case when trunc(random()*10) = 1 then 1 else 0 end as is_reg_house_verif, trunc(random()*1000) reg_house, case when trunc(random()*10) = 1 then 1 else 0 end as is_reg_flat_verif, -- квартира адреса регистрации верифицирована? trunc(random()*1000) as reg_flat, -- квартира адреса регистрации -- фактический адрес проживания case when trunc(random()*10) = 1 then 1 else 0 end as is_fact_idx_verif, -- индекс адреса проживания верифицирован? trunc(random()*100000) as fact_index, -- индекс адреса проживания case when trunc(random()*10) = 1 then 1 else 0 end as is_fact_town_verif, -- город адреса проживания верифицирован? trunc(random()*10000) as fact_town_id, -- город адреса проживания case when trunc(random()*10) = 1 then 1 else 0 end as is_fact_street_verif,-- улица адреса проживания верифицирована? trunc(random()*1000) as fact_street_id, -- улица адреса проживания case when trunc(random()*10) = 1 then 1 else 0 end as is_fact_house_verif, -- дом адреса регистрации верифицирован? trunc(random()*1000) fact_house, -- дом адреса проживания case when trunc(random()*10) = 1 then 1 else 0 end as is_fact_flat_verif, -- квартира адреса проживания верифицирована? trunc(random()*1000) as fact_flat FROM generate_series(1,1000000) cust_id;INSERT 0 1000000
Query returned successfully in 46 secs 996 msec.
Как мы видим, вставка миллиона строк заняла почти 47 секунд. Довольно неплохо, учитывая сколько в запросе было вызовов функций и то, что СУБД развернута далеко не на производственном сервере. Полагаю, что т.к. мы генерируем данные, то никаких чтений с диска не производим, но нагружаем CPU несколькими миллионами вызовов функций, затем записываем на диск большое количество сгенерированных данных.
Посмотрим, теперь, сколько места на диске занимает наша таблица и ее индексы (там есть индекс по первичному ключу).
SELECT pg_size_pretty(pg_table_size('customer_etalon')) AS table_size, pg_size_pretty(pg_indexes_size('customer_etalon')) AS index_size;
239 Мb на миллион строк, это где-то по 250 байт на 1 строку. Для всех экспериментов будем брать данные из этой таблицы, чтобы исключить возможность влияния случайностей заполнения на результаты.
Проверим, что мы получили ровно то, что хотели, просмотрев первые 10 случайных строк:
select * from customer_etalon fetch first 10 rows only
Да, данные получились, что надо.
Эксперимент № 1. Вызовы функций и быстродействие запроса
Если мы заполним таблицу не сгенерированными случайными данными, а просто данными из существующей таблицы, что-то изменится?
Создадим точно такую же таблицу, отличающуюся только именем (не буду приводить весь код ее создания).
create table customer_v0…CREATE TABLE
Query returned successfully in 86 msec.
Заполним ее ранее сгенерированными данными:
insert into customer_v0( cust_id, -- идентификатор клиента first_name, -- фамилия midle_name, -- имя last_name, -- отчество -- адрес регистрации is_reg_idx_verif, -- индекс адреса регистрации верифицирован? reg_index, -- индекс адреса регистрации is_reg_town_verif, -- город адреса регистрации верифицирован? reg_town_id, -- город адреса регистрации is_reg_street_verif, -- улица адреса регистрации верифицирована? reg_street_id, -- улица адреса регистрации is_reg_house_verif, -- дом адреса регистрации верифицирован? reg_house, -- дом адреса регистрации is_reg_flat_verif, -- квартира адреса регистрации верифицирована? reg_flat, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif, -- индекс адреса проживания верифицирован? fact_index, -- индекс адреса проживания is_fact_town_verif, -- город адреса проживания верифицирован? fact_town_id, -- город адреса проживания is_fact_street_verif,-- улица адреса проживания верифицирована? fact_street_id, -- улица адреса проживания is_fact_house_verif, -- дом адреса регистрации верифицирован? fact_house, -- дом адреса проживания is_fact_flat_verif, -- квартира адреса проживания верифицирована? fact_flat -- квартира адреса проживания ) select cust_id, -- идентификатор клиента first_name, -- фамилия midle_name, -- имя last_name, -- отчество -- адрес регистрации is_reg_idx_verif, -- индекс адреса регистрации верифицирован? reg_index, -- индекс адреса регистрации is_reg_town_verif, -- город адреса регистрации верифицирован? reg_town_id, -- город адреса регистрации is_reg_street_verif, -- улица адреса регистрации верифицирована? reg_street_id, -- улица адреса регистрации is_reg_house_verif, -- дом адреса регистрации верифицирован? reg_house, -- дом адреса регистрации is_reg_flat_verif, -- квартира адреса регистрации верифицирована? reg_flat, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif, -- индекс адреса проживания верифицирован? fact_index, -- индекс адреса проживания is_fact_town_verif, -- город адреса проживания верифицирован? fact_town_id, -- город адреса проживания is_fact_street_verif,-- улица адреса проживания верифицирована? fact_street_id, -- улица адреса проживания is_fact_house_verif, -- дом адреса регистрации верифицирован? fact_house, -- дом адреса проживания is_fact_flat_verif, -- квартира адреса проживания верифицирована? fact_flat -- квартира адреса проживания from customer_etalon;INSERT 0 1000000
Query returned successfully in 17 secs 919 msec.
Здесь и далее, в ответе СУБД я буду приводить среднее значение времени, а не то, которое реально показала СУБД в каком-то из вызовов. Среднее получаю путем проведения каждого из экспериментов 10 раз подряд, чтобы исключить влияние заполнения кэшей и случайностей.
Если брать существующие данные, то заполнение нашей тестовой таблицы идет почти в 3 раза быстрее. Вместо десятков миллионов вызовов генератора случайных чисел и других функций, мы не выполнили ни одного вызова. Тут идет, в основном, нагрузка на чтение исходной таблицы и на запись целевой, а CPU нагружаем меньше. Я не планировал именно этот эксперимент, но раз уж все равно надо заполнять все тестовые таблицы одними и теми же данными, почему бы не провести его.
А есть ли различия в размерах таблиц?
SELECT pg_size_pretty(pg_table_size('customer_etalon')) AS etalon_table_size, pg_size_pretty(pg_indexes_size('customer_etalon')) AS etalon_index_size, pg_size_pretty(pg_table_size('customer_v0')) AS v0_table_size, pg_size_pretty(pg_indexes_size('customer_v0')) AS v0_index_size;
Различий в размере таблиц и индексов нет. На всякий случай скажем, что мы не отключали автовакуум. Хотя это не имеет значения – мы же ничего не апдейтили в таблицах и не удаляли.
Эксперимент № 2. Типы данных и размер таблицы
Что плохо в нашей таблице? Мы просто создали ее скриптом, который нам дала одна из утилит миграции в PostgreSQL, но вообще не думали о том, какие реально данные будем записывать в таблицу. У нас есть 10 полей, которые могут содержать признак «верифицировано/не верифицировано». По сути нам нужно 0/1 либо, используя такую возможность PostgreSQL, как логический тип boolean в sql false/true, для чего более чем достаточно 1 байта. Тогда как в создаваемой таблице эти поля имеют тип bigint, предполагающий 8 байт для значений в диапазоне -9223372036854775808 до +9223372036854775807.
Сколько может быть максимум населенных пунктов в нашем сказочном королевстве? И сколько может быть максимум домов на одной улице? Бывают же населенные пункты, состоящие из одного-двух домов, а в стране сотни миллионов жителей. Также мы помним, что самая длинная улица в мире – Сукхумвит в Таиланде, что-то около 491 км. Предположим, что кто-то решит застроить всю эту улицу небольшими домиками по 2 метра в длину стена к стене в 4 ряда с каждой стороны. Тогда всего будет 491 000/2*8 =1 964 000 домов. Что явно меньше, чем максимальное значение типа integer, равного 2 147 483 647. С другой стороны, максимального значения smallint, равного 32 767, в этом вымышленном примере не хватило бы.
Пробуем оптимизировать, заменив тип для полей-признаков с bigint на boolean, а для прочих столбцов с bigint на integer:
create table customer_v1( cust_id bigint primary key, -- идентификатор клиента first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200), -- отчество -- адрес регистрации is_reg_idx_verif boolean, -- индекс адреса регистрации верифицирован? reg_index integer, -- индекс адреса регистрации is_reg_town_verif boolean, -- город адреса регистрации верифицирован? reg_town_id integer, -- город адреса регистрации is_reg_street_verif boolean, -- улица адреса регистрации верифицирована? reg_street_id integer, -- улица адреса регистрации is_reg_house_verif boolean, -- дом адреса регистрации верифицирован? reg_house integer, -- дом адреса регистрации is_reg_flat_verif boolean, -- квартира адреса регистрации верифицирована? reg_flat integer, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif boolean, -- индекс адреса проживания верифицирован? fact_index integer, -- индекс адреса проживания is_fact_town_verif boolean, -- город адреса проживания верифицирован? fact_town_id integer, -- город адреса проживания is_fact_street_verif boolean,-- улица адреса проживания верифицирована? fact_street_id integer, -- улица адреса проживания is_fact_house_verif boolean, -- дом адреса регистрации верифицирован? fact_house integer, -- дом адреса проживания is_fact_flat_verif boolean, -- квартира адреса проживания верифицирована? fact_flat integer -- квартира адреса проживания );CREATE TABLE
Query returned successfully in 151 msec.
Заполним таблицу:
insert into customer_v1( cust_id, -- идентификатор клиента first_name, -- фамилия midle_name, -- имя last_name, -- отчество -- адрес регистрации is_reg_idx_verif, -- индекс адреса регистрации верифицирован? reg_index, -- индекс адреса регистрации is_reg_town_verif, -- город адреса регистрации верифицирован? reg_town_id, -- город адреса регистрации is_reg_street_verif, -- улица адреса регистрации верифицирована? reg_street_id, -- улица адреса регистрации is_reg_house_verif, -- дом адреса регистрации верифицирован? reg_house, -- дом адреса регистрации is_reg_flat_verif, -- квартира адреса регистрации верифицирована? reg_flat, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif, -- индекс адреса проживания верифицирован? fact_index, -- индекс адреса проживания is_fact_town_verif, -- город адреса проживания верифицирован? fact_town_id, -- город адреса проживания is_fact_street_verif,-- улица адреса проживания верифицирована? fact_street_id, -- улица адреса проживания is_fact_house_verif, -- дом адреса регистрации верифицирован? fact_house, -- дом адреса проживания is_fact_flat_verif, -- квартира адреса проживания верифицирована? fact_flat -- квартира адреса проживания ) select cust_id, -- идентификатор клиента first_name, -- фамилия midle_name, -- имя last_name, -- отчество -- адрес регистрации (is_reg_idx_verif = 1) :: boolean, -- индекс адреса регистрации верифицирован? reg_index, -- индекс адреса регистрации (is_reg_town_verif = 1) :: boolean, -- город адреса регистрации верифицирован? reg_town_id, -- город адреса регистрации (is_reg_street_verif = 1) :: boolean, -- улица адреса регистрации верифицирована? reg_street_id, -- улица адреса регистрации (is_reg_house_verif = 1) :: boolean, -- дом адреса регистрации верифицирован? reg_house, -- дом адреса регистрации (is_reg_flat_verif = 1) :: boolean, -- квартира адреса регистрации верифицирована? reg_flat, -- квартира адреса регистрации -- фактический адрес проживания (is_fact_idx_verif = 1) :: boolean, -- индекс адреса проживания верифицирован? fact_index, -- индекс адреса проживания (is_fact_town_verif = 1) :: boolean, -- город адреса проживания верифицирован? fact_town_id, -- город адреса проживания (is_fact_street_verif = 1) :: boolean,-- улица адреса проживания верифицирована? fact_street_id, -- улица адреса проживания (is_fact_house_verif = 1) :: boolean, -- дом адреса регистрации верифицирован? fact_house, -- дом адреса проживания (is_fact_flat_verif = 1) :: boolean, -- квартира адреса проживания верифицирована? fact_flat -- квартира адреса проживания from customer_etalon;INSERT 0 1000000
Query returned successfully in 11 secs 04 msec.
Инсерт мы ускорили на 38%. Причина в том, что данных на диск записать пришлось гораздо меньше.
А правда ли мы что-то сэкономили в размере таблицы?
SELECT pg_size_pretty(pg_table_size('customer_etalon')) AS etalon_table_size, pg_size_pretty(pg_indexes_size('customer_etalon')) AS etalon_index_size, pg_size_pretty(pg_table_size('customer_v0')) AS v0_table_size, pg_size_pretty(pg_indexes_size('customer_v0')) AS v0_index_size, pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size;
Ого, мы уменьшили размер таблицы на 34%! Теперь на 1 строку тратится в среднем 166 байт вместо 250. А сколько времени у нас ушло, чтобы выполнить такую оптимизацию? Пара минут чтобы подумать…
Впрочем, не все так просто. Поменяв тип столбцов с числового на логический, мы внесли изменения в логику системы. А значит, придется править запросы, в которых встречается данные столбцы, процедуры и функции, которые их используют. А если бы мы оставили тип числовым, поменяв с bigint на smallint, то, скорее всего, дорабатывать ничего не потребовалось бы.
Эксперимент № 3. Положение столбцов переменной длины
Помните, в начале статьи я перечислял, в каком порядке и почему идут столбцы в нашей таблице?
Все мы знаем со школы, с уроков математики, что от перестановки мест слагаемых сумма не меняется. А что говорит нам об этом теория баз данных? Столбцы в отношении не упорядочены, строки в отношении не упорядочены. Но то сухая теория, фактически же СУБД совершенно точно хранит строки таблицы в каком-то, только ей известном порядке, и в каждой из строк столбцы тоже идут в определенной последовательности. Обычно она соответствует той, которая была в скрипте создания таблицы.
Спросим у разработчика одной широко известной СУБД, влияет ли на что-то, в каком порядке столбцы были добавлены в таблицу? Наверняка, мы получим ответ «нет». Не совсем так. Если в n каких-то столбцов практически всегда имеется значение null (или значение по умолчанию), а мы поместим эти столбцы последними в списке, система может практически всегда не хранить их значения. Тем самым уменьшая размер таблицы на диске. Впрочем, подбирать такую последовательность столбцов сложно. Если их больше одного, конечно. А если один, то много на диске не сэкономишь.
Но нас интересует PostgreSQL. Что будет, если ФИО будет храниться не со 2-го по 4й столбец от начала, а наоборот от 3го с конца до последнего?
Создадим таблицу:
create table customer_v2( cust_id bigint primary key, -- идентификатор клиента -- адрес регистрации is_reg_idx_verif boolean, -- индекс адреса регистрации верифицирован? reg_index integer, -- индекс адреса регистрации is_reg_town_verif boolean, -- город адреса регистрации верифицирован? reg_town_id integer, -- город адреса регистрации is_reg_street_verif boolean, -- улица адреса регистрации верифицирована? reg_street_id integer, -- улица адреса регистрации is_reg_house_verif boolean, -- дом адреса регистрации верифицирован? reg_house integer, -- дом адреса регистрации is_reg_flat_verif boolean, -- квартира адреса регистрации верифицирована? reg_flat integer, -- квартира адреса регистрации -- фактический адрес проживания is_fact_idx_verif boolean, -- индекс адреса проживания верифицирован? fact_index integer, -- индекс адреса проживания is_fact_town_verif boolean, -- город адреса проживания верифицирован? fact_town_id integer, -- город адреса проживания is_fact_street_verif boolean,-- улица адреса проживания верифицирована? fact_street_id integer, -- улица адреса проживания is_fact_house_verif boolean, -- дом адреса регистрации верифицирован? fact_house integer, -- дом адреса проживания is_fact_flat_verif boolean, -- квартира адреса проживания верифицирована? fact_flat integer, -- квартира адреса проживания first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200) -- отчество );CREATE TABLE
Query returned successfully in 178 msec.
Вставим данные (здесь и далее не буду приводить текст инсерта полностью, он отличается от инсерта в эксперименте 2 только названием таблицы, в которую идет вставка):
insert into customer_v2…;INSERT 0 1000000
Query returned successfully in 14 secs 889 msec.
Странно, но скорость вставки упала по сравнению с предыдущим примером на 34%. Впрочем, это все равно на 17% быстрее первоначального варианта.
А есть ли влияние на скорость извлечение данных? Это нам важнее, ведь данные записываются 1 раз, а извлекаются многократно. Предположим, нам необходимо посчитать, сколько всего есть клиентов, у какого числа из них проведена верификация для каждого из полей. Каждое поле — независимо друг от друга:
explain (analyze) select count(*), sum(case when is_reg_idx_verif then 1 else 0 end), sum(case when is_reg_town_verif then 1 else 0 end), sum(case when is_reg_street_verif then 1 else 0 end), sum(case when is_reg_house_verif then 1 else 0 end), sum(case when is_reg_flat_verif then 1 else 0 end), sum(case when is_fact_idx_verif then 1 else 0 end), sum(case when is_fact_town_verif then 1 else 0 end), sum(case when is_fact_street_verif then 1 else 0 end), sum(case when is_fact_house_verif then 1 else 0 end), sum(case when is_fact_flat_verif then 1 else 0 end) from customer_v1;
Запрос по таблице, где сначала идут поля переменной длины выполнялся параллельно двумя рабочими процессами и одним управляющим процессом, среднее время выполнения 565,87 мс. Осуществляется полный просмотр таблицы — по-другому никак, т.к. нас интересуют все строки таблицы. Если у вас часто встречаются запросы, выполняющие просмотр всех строк большой таблицы – это означает, что система реализована/спроектирована неоптимально.
Помните же, почему на скрине вы видите 642, а я написал про 565?
Теперь тоже самое для таблицы со столбцами переменной длины в конце (селект отличается от предыдущего только названием таблицы):
explain (analyze) … from customer_v2;
Запрос по таблице, где поля переменной длины в конце, тоже выполнялся параллельно двумя процессами, а время выполнения составило 435,4014 мс.
За счет перемещения полей переменной длины в конец таблицы, мы ускорили запрос на 23%!
Когда PostgreSQL читает строки из таблицы, вначале идут поля фиксированной длины, то такое расположение полей позволяет для первой считанной строки ему кэшировать смещение положения столбов относительно начала строки и таким образом начиная со второй строки быстрее понимать, где начинается значения каждого из столбцов запроса.
Я не привел еще данные запроса по таблице customer_v0. По ней запрос отработал в среднем за 922,543 мс. То есть, изменив только типы столбцов, мы уже ускорили запрос почти в 2 раза. А тут еще 23% дополнительно.
Впрочем, не стоит переоценивать эти 23%. В нашем примере запрос читает миллион строк. Такие запросы часто встречаются в хранилищах данных, в них действительно можно сэкономить. Но если речь про систему OLTP, то в ней подавляющее большинство запросов (но не все!) возвращают по 1-2 строки и там никакого ускорения перестановкой столбцов мы не добьемся.
Что с размером таблицы?
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size, pg_size_pretty(pg_table_size('customer_v2')) AS v2_table_size, pg_size_pretty(pg_indexes_size('customer_v2')) AS v2_index_size;
Как мы видим, после перемещения столбцов переменной длины в конец, таблица стала чуть (на 2%) больше. Но это же небольшая плата за ускорение запроса на 23%?
Эксперимент № 4. Положение столбцов типа integer
Обратили внимание, что в нашей тестовой таблице столбцы типа boolean и integer постоянно чередуются. Это логично – разработчик помещал рядом с полем, содержащим значение, поле, содержащее признак верификации этого значения. Но не будет ли лучше, если сначала будут идти столбцы все одного типа, а затем – все столбцы другого типа?
Создаем таблицу:
create table customer_v3( cust_id bigint primary key, -- идентификатор клиента reg_index integer, -- индекс адреса регистрации reg_town_id integer, -- город адреса регистрации reg_street_id integer, -- улица адреса регистрации reg_house integer, -- дом адреса регистрации reg_flat integer, -- квартира адреса регистрации -- фактический адрес проживания fact_index integer, -- индекс адреса проживания fact_town_id integer, -- город адреса проживания fact_street_id integer, -- улица адреса проживания fact_house integer, -- дом адреса проживания fact_flat integer, -- квартира адреса проживания -- признаки верификации is_reg_idx_verif boolean, -- индекс адреса регистрации верифицирован? is_reg_town_verif boolean, -- город адреса регистрации верифицирован? is_reg_street_verif boolean, -- улица адреса регистрации верифицирована? is_reg_house_verif boolean, -- дом адреса регистрации верифицирован? is_reg_flat_verif boolean, -- квартира адреса регистрации верифицирована? is_fact_idx_verif boolean, -- индекс адреса проживания верифицирован? is_fact_town_verif boolean, -- город адреса проживания верифицирован? is_fact_street_verif boolean,-- улица адреса проживания верифицирована? is_fact_house_verif boolean, -- дом адреса регистрации верифицирован? is_fact_flat_verif boolean, -- квартира адреса проживания верифицирована? first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200) -- отчество );CREATE TABLE
Query returned successfully in 1 secs 29 msec.
Заполним таблицу:
insert into customer_v3…;INSERT 0 1000000
Query returned successfully in 11 secs 537 msec.
Помещение всех столбцов типа boolean после столбцов integer ускорило вставку данных на 23%! Как тебе такое, Илон Маск Егор Рогов? Егор-то, конечно, знает, в чем дело. Всем рекомендую прочитать его книгу «PostgreSQL 16 изнутри».
А что с размером таблицы?
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size, pg_size_pretty(pg_table_size('customer_v2')) AS v2_table_size, pg_size_pretty(pg_indexes_size('customer_v2')) AS v2_index_size, pg_size_pretty(pg_table_size('customer_v3')) AS v3_table_size, pg_size_pretty(pg_indexes_size('customer_v3')) AS v3_index_size;
Мы еще на 18% снизили размер таблицы! Теперь на 1 строку тратится в среднем всего 138 байт.
Теперь посмотрим, что со скоростью выборки:
explain (analyze) … from customer_v3;
Среднее время выполнения из десяти прогонов запроса составило 415,4703 мс, что на 5% быстрее варианта, когда типы столбцов постоянно чередовались.
Все дело в том, что PostgreSQL выравнивает столбцы типа integer по ширине 4х байтового слова. То есть, если столбец integer идет первым в таблице, то он располагается с 0-го по 3-й байт. Если за 1-м столбцом integer идет 2-й столбец integer, то 2-й располагается с 4-го по 7-й байт.
Если же первым идет boolean, а вторым integer, то в 0-байте boolean, а integer с 4-го по 7-й байт. Байты с 1-го по 3-й не используются. Таким образом, на каждое чередование полей типов boolean – integer теряется впустую 3 байта. И так на каждое чередование столбцов. В каждой строке таблицы.
Устранив потери, мы уменьшили таблицу. Снизив размер таблицы, ускорили запрос.
Эксперимент № 5. Тип smallint
Можно нельзя построить дома по 4 в ряд с каждой стороны улицы по 2 метра длиной, каждый стеной к стене, а в каждом доме по миллиону квартир. Жить в таком сказочном королевстве никто не сможет. Потому примем решение, что максимум 32 767 домов на одну улицу и максимум 32 767 квартир в одном доме (что равно максимальному значению типа smallint) нам более чем достаточно.
Создадим таблицу:
create table customer_v4( cust_id bigint primary key, -- идентификатор клиента reg_index integer, -- индекс адреса регистрации reg_town_id integer, -- город адреса регистрации reg_street_id integer, -- улица адреса регистрации -- фактический адрес проживания fact_index integer, -- индекс адреса проживания fact_town_id integer, -- город адреса проживания reg_house smallint, -- дом адреса регистрации reg_flat smallint, -- квартира адреса регистрации fact_street_id smallint, -- улица адреса проживания fact_house smallint, -- дом адреса проживания fact_flat smallint, -- квартира адреса проживания -- признаки верификации is_reg_idx_verif boolean, -- индекс адреса регистрации верифицирован? is_reg_town_verif boolean, -- город адреса регистрации верифицирован? is_reg_street_verif boolean, -- улица адреса регистрации верифицирована? is_reg_house_verif boolean, -- дом адреса регистрации верифицирован? is_reg_flat_verif boolean, -- квартира адреса регистрации верифицирована? is_fact_idx_verif boolean, -- индекс адреса проживания верифицирован? is_fact_town_verif boolean, -- город адреса проживания верифицирован? is_fact_street_verif boolean,-- улица адреса проживания верифицирована? is_fact_house_verif boolean, -- дом адреса регистрации верифицирован? is_fact_flat_verif boolean, -- квартира адреса проживания верифицирована? first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200) -- отчество );CREATE TABLE
Query returned successfully in 219 msec.
Заполним ее данными:
insert into customer_v4…;INSERT 0 1000000
Query returned successfully in 10 secs 81 msec.
Вставка еще немного (на 6%) ускорилась. Да, читаем мы из исходной таблицы все тот же объем данных. Но в целевую пишем еще меньше, чем в прошлый раз.
Смотрим размер таблицы:
Достигнут очередной рекорд. Теперь всего 122 Mb, то есть 128 байт на строку. Мы уменьшили таблицу еще на 8%.
Выполняем запрос:
explain (analyze) … from customer_v4;
Среднее время выполнения запроса составило 382,668 мс, что на 8% быстрее, чем в прошлом примере.
Можем ли мы еще уменьшить таблицу? Можем. Заметили, что у нас имеется 10 полей типа boolean? На хранение каждого поля тратится 1 байт. Но на самом деле нам достаточно 1 бита. Тогда вместо 10 полей boolean можно добавить в таблицу 1 поле типа smallint (в нем 16 бит) и выставлять/проверять отдельные биты как сейчас выставляются поля. Получим экономию 10-2=8 байт на каждую строку. Но это существенное изменение структуры данных, придется переписывать логику приложения, многие запросы, потому пока не будем рассматривать этот вариант оптимизации.
Эксперимент № 6. Тип integer для первичного ключа
У нас же таблица клиентов, а не платежей клиентов? Значит, использование типа bigint в качестве первичного ключа избыточно. Вполне достаточно обычного integer. Мы пишем систему не планетарного масштаба, а только для одного сказочного королевства.
Наверняка сейчас еще немного сэкономим.
Создаем таблицу:
create table customer_v5( cust_id integer primary key, -- идентификатор клиента reg_index integer, -- индекс адреса регистрации reg_town_id integer, -- город адреса регистрации reg_street_id integer, -- улица адреса регистрации -- фактический адрес проживания fact_index integer, -- индекс адреса проживания fact_town_id integer, -- город адреса проживания reg_house smallint, -- дом адреса регистрации reg_flat smallint, -- квартира адреса регистрации fact_street_id smallint, -- улица адреса проживания fact_house smallint, -- дом адреса проживания fact_flat smallint, -- квартира адреса проживания -- признаки верификации is_reg_idx_verif boolean, -- индекс адреса регистрации верифицирован? is_reg_town_verif boolean, -- город адреса регистрации верифицирован? is_reg_street_verif boolean, -- улица адреса регистрации верифицирована? is_reg_house_verif boolean, -- дом адреса регистрации верифицирован? is_reg_flat_verif boolean, -- квартира адреса регистрации верифицирована? is_fact_idx_verif boolean, -- индекс адреса проживания верифицирован? is_fact_town_verif boolean, -- город адреса проживания верифицирован? is_fact_street_verif boolean,-- улица адреса проживания верифицирована? is_fact_house_verif boolean, -- дом адреса регистрации верифицирован? is_fact_flat_verif boolean, -- квартира адреса проживания верифицирована? first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200) -- отчество ); CREATE TABLE
Query returned successfully in 234 msec.
Теперь заполним ее.
insert into customer_v5…;INSERT 0 1000000
Query returned successfully in 13 secs 31 msec.
Вот это поворот, вставка замедлилась на 23%…
Выполним запрос
explain (analyze) … from customer_v5;
Запрос стал выполняться в среднем 410,8806 мс, что на 7% медленнее. Но хотя бы экономия дискового пространства есть?
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size, pg_size_pretty(pg_table_size('customer_v2')) AS v2_table_size, pg_size_pretty(pg_indexes_size('customer_v2')) AS v2_index_size, pg_size_pretty(pg_table_size('customer_v3')) AS v3_table_size, pg_size_pretty(pg_indexes_size('customer_v3')) AS v3_index_size, pg_size_pretty(pg_table_size('customer_v4')) AS v4_table_size, pg_size_pretty(pg_indexes_size('customer_v4')) AS v4_index_size, pg_size_pretty(pg_table_size('customer_v5')) AS v5_table_size, pg_size_pretty(pg_indexes_size('customer_v5')) AS v5_index_size;
Экономия в размере есть, но только на 3%. Логично, на одном столбце много не сэкономишь. Обратите внимание, что совсем не изменился размер индекса, содержащего первичный ключ.
Эксперимент № 7. Тип numeric
А почему мы сразу сбросили со счетов тип numeric ? Если его применить, это же все как в исходной СУБД будет – любая поддерживаемая системой точность, никаких ограничений по минимальным и максимальным значениям. Занимать значение будет столько байт, сколько нужно для его хранения. У нас есть номера домов и квартир. Обычно на улице не бывает больше 255 домов. И далеко не в каждом доме есть, хотя бы, 255 квартир. Значит, мы будем расходовать обычно один байт на такое поле, а не всегда два байта, как в smallint? Или нет?
Мы не рассматривали numeric, т.к. в документации сказано, что он медленный. Но давайте это проверим.
Создадим таблицу:
create table customer_v6( cust_id numeric primary key, -- идентификатор клиента reg_index numeric, -- индекс адреса регистрации reg_town_id numeric, -- город адреса регистрации reg_street_id numeric, -- улица адреса регистрации -- фактический адрес проживания fact_index numeric, -- индекс адреса проживания fact_town_id numeric, -- город адреса проживания reg_house numeric, -- дом адреса регистрации reg_flat numeric, -- квартира адреса регистрации fact_street_id numeric, -- улица адреса проживания fact_house numeric, -- дом адреса проживания fact_flat numeric, -- квартира адреса проживания -- признаки верификации is_reg_idx_verif boolean, -- индекс адреса регистрации верифицирован? is_reg_town_verif boolean, -- город адреса регистрации верифицирован? is_reg_street_verif boolean, -- улица адреса регистрации верифицирована? is_reg_house_verif boolean, -- дом адреса регистрации верифицирован? is_reg_flat_verif boolean, -- квартира адреса регистрации верифицирована? is_fact_idx_verif boolean, -- индекс адреса проживания верифицирован? is_fact_town_verif boolean, -- город адреса проживания верифицирован? is_fact_street_verif boolean,-- улица адреса проживания верифицирована? is_fact_house_verif boolean, -- дом адреса регистрации верифицирован? is_fact_flat_verif boolean, -- квартира адреса проживания верифицирована? first_name varchar(200), -- фамилия midle_name varchar(200), -- имя last_name varchar(200) -- отчество );CREATE TABLE
Query returned successfully in 321 msec.
Заполним ее:
insert into customer_v6…;INSERT 0 1000000
Query returned successfully in 33 secs 62 msec.
Вот это да, скорость вставки упала на 210%. Это неприемлемо.
Проверим, что с запросом данных. Будет ли на него какое-то влияние, ведь запрос не использует ни одного столбца типа numeric.
explain (analyze) … from customer_v6;
Запрос выполняет в среднем за 486,3474 мс, что на 27% медленнее. Еще бы, PostgreSQL читает больше данных и для каждой строки таблицы приходится заново вычислять смещение нужных нам столбцов от начала строки, чтобы получить к ним доступ.
А что с размером таблицы?
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size, pg_size_pretty(pg_table_size('customer_v2')) AS v2_table_size, pg_size_pretty(pg_indexes_size('customer_v2')) AS v2_index_size, pg_size_pretty(pg_table_size('customer_v3')) AS v3_table_size, pg_size_pretty(pg_indexes_size('customer_v3')) AS v3_index_size, pg_size_pretty(pg_table_size('customer_v4')) AS v4_table_size, pg_size_pretty(pg_indexes_size('customer_v4')) AS v4_index_size, pg_size_pretty(pg_table_size('customer_v5')) AS v5_table_size, pg_size_pretty(pg_indexes_size('customer_v5')) AS v5_index_size, pg_size_pretty(pg_table_size('customer_v6')) AS v6_table_size, pg_size_pretty(pg_indexes_size('customer_v6')) AS v6_index_size;
Таблица выросла со 118 до 144 Mb, на 22%, что тоже плохо.
Эксперимент № 8. Массовое изменение данных
Может сложиться впечатление, что какая разница, 161 Mb или 118? Все равно это немного на миллион строк. Но это всего одна таблица, в реальной системе таблиц тысячи, из них больших десятки или сотни. У каждой таблицы еще есть несколько индексов, они тоже большие. Если бы речь шла о таблице платежей, пусть платежи хранятся всего один год, мы бы имели вместо 1 млн. строк 5* 1 000 000 * 365 = 1 825 000 000 строк. Тогда вместо 118 Mb размер таблицы 215 350 Mb. Вообще, когда речь идет о деньгах, срок хранения информации в один год — это очень мало. Допустим, наша система предназначена для банка, банк выдает ипотеку на 30 лет. Значит, мы должны хранить все платежи, минимум 30 лет. Таблица увеличивается до 215 350 * 30 = 6 460 500 Mb = 6 309 Gb.
А не может ли наша таблица с миллионом строк вдруг вырасти в несколько раз?
Мы разово заполнили таблицу и ничего в ней не меняли. В реальности, данные вставляются в таблицу постепенно, иногда удаляются, иногда обновляются.
Предположим, мы заполнили таблицу, система работала какое-то время и тут вдруг выяснилось, что ранее верифицированный почтовый индекс, должен считаться не верифицированным. Нужно во всех строках таблицы проставить is_reg_idx_verif = false.
Пишем апдейт:
update customer_v1 set is_reg_idx_verif = false;Хорошая команда? Нет. Плохая до ужаса. Почему? Давайте попробуем ее выполнить.
UPDATE 1000000
Query returned successfully in 1 min 6 secs.
Мы же сделали то, что требовалось – сняли признак верификации. Поменяли ровно 1 столбец во всех строках таблицы. А зачем, ведь в 9 из 10 строк и так было false? Почему апдейт работал так долго? Быстрее было создать новую таблицу, заполнить ее данными из существующей, заменив при этом значения только в одном столбце. А потом удалить исходную таблицу и переименовать целевую. Тем более, что в PostgreSQL эти все процессы можно выполнить в рамках одной транзакции.
Что произошло с нашей таблицей?
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size;
Таблица и индекс выросли в 2 раза, при том, что мы не добавили в нее ни одной строки…
Как получилось 316 Mb на таблицу и 43 Mb на индекс ? Ведь только что было всего 158 Mb на таблицу и 21 Mb на индекс.
Что-то тут не то. А если проапдейтим еще раз? У нас же не изменится ни одной строки, значит с таблицей ничего случиться не должно.
Выполняем тот же апдейт.
update customer_v1 set is_reg_idx_verif = false;UPDATE 1000000
Query returned successfully in 1 min 18 secs.
Еще дольше.
Похоже, правда ничего больше не поменялось.
Вдруг выясняется, что нужно проапдейтить не только поле is_reg_idx_verif, а все поля с признаком верификации. Делаем 10 копий данного апдейта, в каждой копии заменив обновляемое поле. Опасаемся, что пока выполняем апдейты, кто-то из пользователей внесет изменения в таблицу. В итоге в одной из строк останется не снятый признак верификации. Чтобы такого не случилось, помещаем все апдейты в одну транзакцию.
BEGIN; update customer_v1 set is_reg_idx_verif = false; update customer_v1 set is_reg_town_verif = false; update customer_v1 set is_reg_street_verif = false; update customer_v1 set is_reg_house_verif = false; update customer_v1 set is_reg_flat_verif = false; update customer_v1 set is_fact_idx_verif = false; update customer_v1 set is_fact_town_verif = false; update customer_v1 set is_fact_street_verif = false; update customer_v1 set is_fact_house_verif = false; update customer_v1 set is_fact_flat_verif = false; commit;Query returned successfully in 29 min 8 secs.
Конечно, применить такое решение мог только новичок в работе с базами данных. Такого специалиста допускать к работе с продуктивом нельзя. Только тестовая база данных.
Если 1 апдейт длится 1 минуту, то почему на 10 апдейтов ушло 29 минут, а не 10 ?
После 29 минут ожиданий, что мы имеем? Никто в системе полчаса не мог ничего редактировать в таблице (я имею ввиду — редактировать существующие строки. Добавлять новые строки могли все). Было остановлено обслуживание клиентов по всему королевству.
Проверим размеры.
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size;
Во это да, наша таблица стала в 10 раз больше исходной. А вот индекс вырос только в 5 раз. Но как, Холмс?
Дело в том, что в отличие от некоторых других СУБД, в PostgreSQL нет журнала отката для получения согласованного состояния данных на определенный момент времени. Но получать согласованное состояние нужно.
Любой запрос, читающий данные из каких-то таблиц, может работать тысячную долю секунды, может несколько часов. Но абсолютно все возвращаемые им строки из всех таблиц находятся в состоянии, актуальном на момент начала выполнения запроса. А в системе, помимо нашей сессии, может быть еще очень много других сессий. Когда мы что-то апдейтим в таблице, PostgreSQL меняет данные прямо в таблице. Но апдейтится не существующая строка, а создается новая версия той же строки. Отсюда рост таблицы. В индекс также вставляется ссылка на новую версию строки, потому и индекс растет. Предыдущая версия строки помечается как неактуальная с того момента, когда мы закоммитим нашу транзакцию. Пока не закоммитили, она актуальна для всех транзакций, кроме нашей. И для всех запросов, которые были запущены до того момента, когда мы закоммитим. Сам по себе коммит – предельно легкая операция, неважно сколько всего было проапдейчено. Впрочем, как и rollback (чувствую удивление разработчиков некоторых других СУБД). И не бывает ошибок вида «слишком старый снимок». Для обеспечения такой возможности, в каждой версии строки каждой таблицы в PostgreSQL есть скрытые столбцы xmin и xmax, содержащие номера транзакций, создавших и обновивших версию строки. По ним и по списку транзакций (снимок, созданный в момент начала выполнения запроса) PostgreSQL разбирается, какую из версий строк для транзакции делать видимой.
Итого, был миллион версий строк, проапдейтили каждую, получили два миллиона. Потому удвоился и размер таблицы и размер индекса. Но те версии, что до апдейта, более не актуальны. Отработал автовакуум, он отметил, что они свободны. Место на диске не освободилось и не освободится никогда, если только мы не запустим vacuum full. Но это очень плохая операция, если мы не можем остановить работу системы, т.к. таблица на время ее работы блокируется целиком (даже для чтения). Так что, вряд ли мы ее запустим скоро. Зато, когда мы проапдейтили повторно, ни таблица ни индекс больше не выросли. Потому что созданные 3-и версии каждой из строк записались на те места страниц данных, где хранились ненужные никому более первые версии.
А почему, когда мы проапдейтили еще 10 раз подряд, таблица снова значительно выросла, но при этом индекс вырос гораздо меньше? Так как мы много апдейтили внутри одной транзакции, то те версии строк, что были до апдейта, в теории могли понадобиться другим сессиям (или вдруг бы мы в конце вызвали rollback). Потому автовакууму не разрешается их помечать свободными. Тогда система создавала четвертую версию строки, пятую, шестую и т.д. Под все эти версии требовалось место на диске в файле таблицы. При этом, т.к. мы не апдейтили ни один ключевой столбец, то срабатывал так называемый hot-update. Оптимизация, позволяющая в некоторых случаях избежать обновления индексов. Ведь индекс – куда более сложная структура, чем таблица, обновлять ее тяжело. Ссылки на новые версии строк при hot-update не добавились в индекс. Если какой-то запрос через индекс зайдет в таблицу, он будет адресован на третью версию строки, а уже из нее, по ссылкам между версиями строк, доберется до нужной ему версии.
Какие можно сделать выводы?
-
Апдейтить много строк за 1 транзакцию – плохая идея. Таблица и ее индексы будут расти.
-
Длинные транзакции, да и просто долгие запросы, пусть даже обычные select – плохая идея. Все таблицы и все их индексы во всей БД будут расти. Т.к. запрос/транзакция будут держать горизонт базы данных, что не позволит автовакууму очищать неактуальные версии строк по всей БД. А другие сессии в это время будут апдейтить разные таблицы… Потом, конечно, вакуум отработает, но место на диске это уже не освободит. Чем больше становится таблица, тем медленнее начинают работать запросы к ней.
Теперь выясним, сколько времени будет работать запрос получения данных.
explain (analyze) …Одно выполнение запроса теперь занимает в среднем 24 секунды, что в 40 раз медленнее. Почему так, если таблица увеличилась только в 10 раз? Таблица стала настолько большой, что она перестала помещаться в буферный кэш, приходится каждый раз читать ее всю с диска.
Вот так одним неловким решением можно затормозить хорошо работавшую систему.
Пробуем исправить:
vacuum full customer_v1;Query returned successfully in 1 min 31 secs.
SELECT pg_size_pretty(pg_table_size('customer_v1')) AS v1_table_size, pg_size_pretty(pg_indexes_size('customer_v1')) AS v1_index_size;
Размер вернулся, как был. Среднее время выполнения запроса тоже. Но мы не можем выполнять такую команду на работающей системе.
Какое должно было быть решение? В первом случае вместо
update customer_v1 set is_reg_idx_verif = false;стоило написать
update customer_v1 set is_reg_idx_verif = false where is_reg_idx_verif = true ;Это бы значительно снизило число обновляемых строк. СУБД сделала ровно то, что мы попросили – проапдейтила все строки. Ей не важно, что в большей части строк уже и так ничего не поменяется.
Проверим на исходной таблице (для чистоты эксперимента мы ее удалили drop table customer_v1; заново создали и заполнили скриптом):
UPDATE 99487
Query returned successfully in 5 secs 253 msec.
Мы проапдейтили где-то 10% от строк таблицы вместо 100% и времени на это ушло в 10 раз меньше, чем изначально.
Рост и таблицы и индекса незначительный в сравнении с первоначальным ростом в 2 раза.
В случае, когда мы хотели проапдейтить кучу полей, это нужно было делать все в одном апдейте и его тоже ограничить.
update customer_v1 set is_reg_idx_verif = false, is_reg_town_verif = false, is_reg_street_verif = false, is_reg_house_verif = false, is_reg_flat_verif = false, is_fact_idx_verif = false, is_fact_town_verif = false, is_fact_street_verif = false, is_fact_house_verif = false, is_fact_flat_verif = false where is_reg_idx_verif = true or is_reg_town_verif = true or is_reg_street_verif = true or is_reg_house_verif = true or is_reg_flat_verif = true or is_fact_idx_verif = true or is_fact_town_verif = true or is_fact_street_verif = true or is_fact_house_verif = true or is_fact_flat_verif = true;Если же мы предполагаем, что все равно количество обновляемых строк велико (если было 10% строк для одного поля, то для 10 полей будет до 100% строк), то мы можем пойти разными путями:
-
Создать новую таблицу, заполнить ее данными из исходной за исключением обновляемого столбца, которому установить требуемое значение. Затем удалить исходную таблицу, а новую переименовать.
-
Создать процедуру, которая будет апдейтить в цикле относительно небольшими порциями с вызовом вакуума между порциями.
-
Проапдейтить за 1 раз, но во время технологического окна вызвать vacuum full по таблице.
Нужно еще учесть, что кроме нас в БД тоже кто-то работает. Это могут быть сотни или тысячи пользователей, а также автоматические процессы.
Чем еще плох апдейт сразу большого числа строк одной командой – если с таблицей работают другие сессии, то некоторые из них будут заблокированы. А некоторые, наоборот, заблокируют наш апдейт. И тогда вместо 5 секунд мы будем обновлять неизвестно сколько часов, мешая работе неизвестного числа других сессий. Потому лучше получать строки через select for update skip locked и обновлять их небольшими порциями с промежуточным коммитом и паузой либо вызовом вакуума.
Выводы
Для успешного приложения на СУБД PostgreSQL, если уже выполнены условия по оптимальным индексам и планам запросов, важны типы столбцов и их очередность в таблице.
-
Важно подбирать корректные типы для всех столбцов каждой таблицы так, чтобы с одной стороны не было перерасхода дискового пространства на хранение этого столбца, а с другой стороны, чтобы диапазона доступных для этого типа данных значений точно навсегда хватало для всех возможных его значений. Логические признаки (true/false) в больших таблицах, если не потребуется выборка по ним через индекс, возможно группировать и хранить в одном поле в разных битах одного поля.
-
Сначала должны идти все столбцы типов с фиксированной длиной, причем сначала типа bigint, integer, smallint, потом boolean, а затем в столбцы с типами переменной длиной. Потому что PostgreSQL выравнивает столбцы по длине 8хбайтного/4хбайтного/2хбайтного слова и кэширует смещение столбца относительно начала строки при чтении таблицы. Путем изменения типов столбцов и изменения очередности столбцов в таблице, нам удалось снизить размер тестовой таблицы с 239Mb до 118 Mb — в 2 раза. Примерно в 2 раза ускорились и запросы к ней. Можно было еще больше снизить размер таблицы? Да, если хранить признаки «да/нет» в виде одного бита в определенном поле, а не в отдельных полях. Да, если у нас коммерческая версия PostgreSQL — есть возможность применить сжатие блоков данных. Но сжатие, наверняка, будет в ущерб скорости работы с таблицей.
-
Нужно избегать массовых апдейтов, долгих запросов и длинных сессий, т.к. это ведет к росту некоторых или всех таблиц и индексов в БД и, как следствие, к деградации производительности. А в случае апдейтов/делейтов еще и к блокировкам других сессий.
ссылка на оригинал статьи https://habr.com/ru/articles/859700/
Добавить комментарий