Расширяемость
Типы для больших значений
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Хранение в файловой системе
Технология TOAST
Тип bytea
Большие объекты
3
Варианты хранения
Файловая система
в базе данных только ссылки на файлы
Встроенные типы
Подсистема «больших объектов»
PostgreSQL стандарт SQL
двоичные данные bytea blob
символьные данные text clob
Для хранения и обработки больших объемов данных в стандарте SQL
предусмотрены типы:
●
clob (character large object) — для символьных данных;
●
blob (binary large object) — для двоичных данных.
В PostgreSQL типы clob и blob отсутствуют. Вместо них для символьных
данных можно использовать тип text, а для двоичных — bytea (byte
array).
Работа с данными типа text не зависит от размера значения, поэтому
в дальнейшем будем говорить только о двоичных данных. Например,
об изображениях, видео, аудио и пр. Как правило такие данные хранят
содержимое файлов соответствующих форматов.
В качестве альтернативы типу bytea, можно использовать «отчасти
устаревшую» (цитата из документации) подсистему работы с большими
объектами.
Но прежде чем размещать данные файлов внутри базы, следует
рассмотреть возможность хранения их «в обычном виде» в файловой
системе.
4
Файловая система
backend
WAL
0110011001110011
1100110011100110
1001100111001101
0011001110011011
буферный кеш PostgreSQL
кеш операционной системы
Если говорить о двоичных данных большого объема, то один из
вариантов — отказаться от размещения их в СУБД. В этом случае
файлы с двоичными данными располагаются непосредственно
в файловой системе, а внутри базы данных поддерживаются только
ссылки на них.
5
Файловая система
Преимущества
скорость чтения и записи файлов
не увеличивается размер базы данных
Недостатки
не используются возможности СУБД: транзакции, управление доступом
отсутствуют гарантии согласованности, долговечности данных
усложняется архитектура системы,
например, в части резервного копирования и восстановления
Такой подход может быть оправдан в первую очередь
производительностью. Скорость чтения и записи файлов увеличивается
за счет избежания затрат, характерных для СУБД:
●
двойная запись на диск (в WAL и файлы данных);
●
двойное кеширование в оперативной памяти (файловый кеш
и буферный кеш СУБД);
●
накладные расходы на хранение в базе данных и обработку
(нарезка на фрагменты для хранения и склейка их при чтении);
●
возможное распухание таблиц и индексов приводит
к дополнительному чтению устаревших фрагментов значения.
Кроме того, удается сократить расходы на обслуживание. Не требуется
очистка и переиндексация таблиц с большими данными. Копирование
файлов, в том числе инкрементальное, можно выполнять средствами
ОС. А резервные копии только базы данных будут создаваться быстрее,
меньшего размера и потребуют меньше времени на восстановление.
С другой стороны, становится невозможным использовать
преимущества СУБД:
●
транзакционная обработка: атомарность и согласованность записи,
конкурентный доступ, восстановление после сбоя;
●
управление доступом пользователей на чтение и запись данных.
Архитектура системы может потребовать пересмотра и доработки
процедур резервного копирования и восстановления, использования
реплик, обнаружения неиспользуемых файлов и пр.
6
TOAST
Хранение «длинных» атрибутов в отдельной таблице
применяется для типов переменной длины: text, bytea, xml, json и др.
«длинные» атрибуты нарезаются на фрагменты меньше страницы
возможно сжатие (методы pglz и lz4)
размер значения до 1 Гбайта
TOAST-таблица
читается только при обращении к «длинному» атрибуту
возможна частичная декомпрессия значения при чтении
собственная версионность
работает прозрачно для приложения
Любая версия строки в PostgreSQL должна целиком помещаться на
одну страницу. Для «длинных» версий строк применяется технология
TOAST — The Oversized Attributes Storage Technique. Точнее, TOAST
применяется к отдельным атрибутам, имеющим тип переменной длины,
например, text и bytea, а также xml и json, которые будут рассмотрены
позже в этом курсе. В любом случае размер одного значения (возможно
сжатого) не должен превышать 1 Гбайта.
Для каждой основной таблицы при необходимости создается отдельная
TOAST-таблица (и к ней специальный индекс). Версии строк в TOAST-
таблице тоже должны помещаться на одну страницу, поэтому
«длинные» значения хранятся порезанными на части, обычно около
2 Кбайт. Из этих частей PostgreSQL прозрачно для приложения
«склеивает» необходимое значение.
TOAST-таблица используется только при обращении к «длинному»
значению. Если эти данные упакованы, то начальные фрагменты
сжатого атрибута считываются порциями и сразу распаковываются,
позволяя избежать извлечения «длинного» значения целиком. Но если
требуется изменить несколько байт значения, то оно будет считано
и записано полностью.
Для TOAST-таблицы поддерживается своя версионность: если
обновление данных не затрагивает «длинное» значение, новая версия
строки будет ссылаться на то же самое значение в TOAST-таблице —
это экономит место.
7
Стратегии хранения
основная
таблица
сжатие
данных
TOAST-
таблица
Extended
да да да
External
да ― да
Main
да
в последнюю
очередь
в самую
последнюю
очередь
Plain
да ― ―
Столбцы таблиц используют одну из четырех стратегий хранения
значений, устанавливаемую в зависимости от типа данных:
●
EXTENDED. Допускается сжатие и перенос в таблицу TOAST.
Для типов, поддерживающих перенос в TOAST, обычно используется
эта стратегия.
●
EXTERNAL. Допускается только перенос в таблицу TOAST, но не
сжатие. Если загружаемые данные уже сжаты, имеет смысл выбрать
стратегию EXTERNAL для экономии затрат на малоэффективное
повторное сжатие.
●
MAIN. Поля с этой стратегией сжимаются только в том случае, если
обработки полей EXTENDED и EXTERNAL оказалось недостаточно,
чтобы уместить версию строки на странице. Перенос
в таблицу TOAST тоже возможен, но только как крайняя мера.
●
PLAIN. Хранение только в основной таблице. Используется для
типов, значения которых имеют небольшой размер.
При создании пользовательского типа данных можно задать стратегию,
применяемую по умолчанию.
На уровне столбца таблицы можно переопределить стратегию, а также
указать метод сжатия.
8
Тип bytea
backend
WAL
10011011
10011001
11001101
00110011
11100110
01100110
01110011
11001100
буферный кеш PostgreSQL
кеш операционной системы
pg_toast_NNN
Хранить двоичные объекты в базе данных можно двумя способами.
Первый способ заключается в создании столбцов с типом bytea
в таблицах.
Тип bytea использует технологию хранения TOAST. Поэтому значения
большого размера размещаются не в самой таблице, а нарезаются
на фрагменты и хранятся в TOAST-таблице, связанной с основной.
9
Тип bytea
Преимущества
транзакционность, управление доступом
согласованность и долговечность больших данных
интерфейс SQL
Недостатки
увеличение размера базы данных
меньшая скорость, чем при работе с файлами
размер значения до 1 Гбайта
значения считываются и записываются целиком
Главное преимущество хранения больших значений в базе данных —
это использование возможностей СУБД. Транзакционный механизм
гарантирует согласованность данных и защиту от сбоев.
А разграничивать доступ пользователей к значениям можно при
помощи средств СУБД.
Для работы с типом bytea используется язык SQL, что делает эти
действия удобными.
Двоичные типы данных, функции и операторы для работы с ними:
Как мы уже говорили ранее, хранение внутри базы данных увеличивает
размер базы и расходы на ее сопровождение, а также уменьшает
скорость чтения и записи данных из-за необходимости нарезать
значение на фрагменты и склеивать их, помещая в дополнительный
кеш.
К недостаткам использования типа bytea можно отнести и ограничение
TOAST на размер одного значения в 1 Гбайт.
Также отметим, что при работе c TOAST изменение даже небольшой
части значения приводит к считыванию в буферный кеш всех занятых
значением страниц и к перезаписи всего значения. С чтением части
значения дело обстоит лучше: возможно извлечение только
необходимого фрагмента.
11
Большие объекты
backend
WAL
буферный кеш PostgreSQL
11100110
11001101
10011001
10011011
00110011
01100010
01111001
11001100
кеш операционной системы
pg_largeobject
Другой вариант хранения двоичных данных большого размера внутри
базы данных — использование подсистемы «больших объектов»,
появившейся задолго до TOAST.
Все большие объекты сохраняются в отдельной таблице системного
каталога pg_largeobject. Для ссылок на большие объекты в столбцах
других таблиц предоставляются идентификаторы типа oid. Доступ
к этим объектам осуществляется при помощи специального
интерфейса, похожего на интерфейс работы с файлами.
12
Большие объекты
Преимущества
транзакционность, управление доступом
согласованность и долговечность больших данных
размер значения до 4 Тбайт
потоковое чтение и запись фрагментов значения
Недостатки
увеличение размера базы данных
меньшая скорость, чем при работе с файлами
специальный интерфейс
ограничение на количество больших объектов (2
32
)
риск появления осиротевших объектов
не поддерживается логической репликацией
Большие объекты наследуют те же преимущества и недостатки, что
и тип bytea, относительно хранения в файловой системе.
По сравнению с типом bytea у больших объектов есть преимущества.
Во-первых, размер одного объекта может достигать 4 Тбайт.
А во-вторых, интерфейс для работы с большими объектами позволяет
работать с произвольным фрагментом значения, а не со всем
значением целиком, как обычно происходит с TOAST.
С другой стороны, использование специального интерфейса делает
работу с большими объектами не такой удобной, как с чистым SQL.
К тому же драйвер PostgreSQL должен поддерживать этот интерфейс.
Идентификатор большого объекта хранится в 4-байтовом типе oid,
что ограничивает количество объектов в системе числом 4 294 967 296.
Таблицы TOAST тоже используют oid, но ограничение на количество
значений действует в пределах таблицы, а не на всю систему.
Кроме того, хранение всех больших объектов в отдельной таблице
увеличивает риск появления осиротевших объектов, на которые
не осталось ссылок в обычных таблицах. Для поиска и удаления
осиротевших объектов можно использовать утилиту vacuumlo, а для
предотвращения их появления — расширение lo.
Логическая репликация не поддерживает изменения в таблицах
системного каталога, в том числе в pg_largeobject, то есть большие
объекты не реплицируются.
14
Итоги
Для работы с двоичными данными предоставлены
тип bytea, использующий технологию TOAST
подсистема «больших объектов»
Использование файловой системы позволяет добиться
высокой скорости за счет отказа от возможностей СУБД
15
Практика
1. Добавьте в таблицу books поле cover с типом bytea.
Загрузите в поле cover обложки книг из файлов формата
jpeg. Файлы находятся в каталоге /home/student/covers.
2. Создайте функцию webapi.get_image, возвращающую
обложку книги по переданному идентификатору.
3. Сравните время, за которое выполняется запрос ко всем
столбцам таблицы books (SELECT *) и к столбцам без cover.
1. Для чтения файлов можно использовать функцию pg_read_binary_file.
2. Функция get_image уже создана в базе данных, но ничего не
возвращает. Заголовок функции:
CREATE FUNCTION webapi.get_image(book_id integer) RETURNS bytea
16
Практика+
1. Создайте таблицу с полем типа bytea.
Загрузите в таблицу любой файл, этот же файл загрузите как
большой объект.
Выполните контрольную точку, чтобы в кеше не осталось
грязных буферов.
2. Добавьте один произвольный байт к значению в столбце
типа bytea таблицы.
Сколько грязных буферов, относящихся к TOAST-таблице,
появилось в буферном кеше?
3. Добавьте один произвольный байт к значению большого
объекта.
Сколько грязных буферов, относящихся к таблице
pg_largeobject, появилось в буферном кеше?
2 и 3. Чтобы получить количество грязных буферов для таблицы,
установите расширение pg_buffercache и воспользуйтесь запросом:
SELECT count(*)
FROM pg_buffercache b
WHERE b.relfilenode = pg_relation_filenode('table_name'::regclass)
AND b.relforknumber = 0 -- основной слой
AND isdirty;
3. Можно воспользоваться функцией lo_put, записывающей данные
по заданному смещению.
