Расширяемость
Типы для больших значений
12
Авторские права
© Postgres Professional, 2020 год.
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
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 и др.
«длинные» атрибуты нарезаются на фрагменты меньше страницы
возможно сжатие
размер значения до 1 Гбайта
TOAST-таблица
читается только при обращении к «длинному» атрибуту
возможна частичная декомпрессия значения при чтении
собственная версионность
работает прозрачно для приложения
Любая версия строки в PostgreSQL должна целиком помещаться на
одну страницу. Для «длинных» версий строк применяется технология
TOAST — The Oversized Attributes Storage Technique. Точнее TOAST
применяется к отдельным атрибутам, имеющим тип переменной длины,
например, text и bytea, а также xml и json, которые будут рассмотрены
позже в этом курсе. В любом случае размер одного значения (возможно
сжатого) не должен превышать 1 Гбайта.
Для каждой основной таблицы при необходимости создается отдельная
TOAST-таблица (и к ней специальный индекс). Версии строк в TOAST-
таблице тоже должны помещаться на одну страницу, поэтому
«длинные» значения хранятся порезанными на части, обычно около
2 Кбайт. Из этих частей PostgreSQL прозрачно для приложения
«склеивает» необходимое значение.
TOAST-таблица используется только при обращении к «длинному»
значению. Если требуется прочитать начальную часть сжатого значения
(например функцией substr), то после считывания и склеивания полного
значения будет распакована только первая часть, достаточная для
выдачи результата. А в версии 13 начальные фрагменты будут
считываться порциями и сразу распаковываться, позволяя избежать
считывания «длинного» значения целиком. Но если требуется изменить
несколько байт значения, то оно будет считано и записано полностью.
Для TOAST-таблицы поддерживается своя версионность: если
обновление данных не затрагивает «длинное» значение, новая версия
строки будет ссылаться на то же самое значение в TOAST-таблице —
это экономит место.
7
Стратегии хранения
основная
таблица
сжатие
данных
перенос
в TOAST
Extended
да да да
External
да ― да
Main
да да
в последнюю
очередь
Plain
да ― ―
Столбцы таблиц используют одну из четырех стратегий хранения
значений. Вне зависимости от выбранной стратегии, значения
небольшого размера будут оставаться в основной таблице.
Первые три стратегии относятся к TOAST:
- EXTENDED. Допускается сжатие и перенос в таблицу TOAST. Эта
стратегия используется по умолчанию для типов, поддерживающих
перенос в TOAST.
- EXTERNAL. Допускается только перенос в таблицу TOAST, но не
сжатие. Если загружаемые данные уже сжаты, имеет смысл выбрать
стратегию EXTERNAL для экономии ресурсов на малоэффективное
повторное сжатие.
- MAIN. Допускается сжатие. Перенос в таблицу TOAST возможен как
крайняя мера, если перенос других атрибутов (external и extended) не
уменьшил строку так, чтобы она уместилась на странице.
- 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, как правило, осуществляется со значениями
целиком. Если требуется прочитать или изменить небольшую часть
значения, то придется читать и записывать в буферный кеш полное
значение. Как было сказано ранее, ситуация частично улучшится в 13-й
версии PostgreSQL.
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.
В 12-й версии логическая репликация не поддерживает изменения
в таблицах системного каталога, в том числе в 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 isdirty;
3. Можно воспользоваться функцией lo_put, записывающей данные по
заданному смещению.
