# Занятие 08: Расширяемость / Типы для больших значений > Исходная страница в нативном Markdown: Расширяемость / Типы для больших значений --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev2_08_ext_lob.html](https://edu.postgrespro.ru/16/dev2-16/dev2_08_ext_lob.html) > **Archive:** [DEV2-handouts-16.zip](https://edu.postgrespro.ru/16/DEV2-handouts-16.zip) > **Страниц PDF:** 23 ## Page 1: Расширяемость / Типы для больших значений {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Хранение в файловой системе - Технология TOAST - Тип bytea - Большие объекты ## Page 3: Варианты хранения {#source-page-003} **Файловая система** - в базе данных только ссылки на файлы - Встроенные типы - PostgreSQL - стандарт SQL - двоичные данные - bytea - blob - символьные данные - text - clob - Подсистема «больших объектов» ### Комментарии преподавателя Для хранения и обработки больших объемов данных в стандарте SQL предусмотрены типы: ● clob (character large object) — для символьных данных; ● blob (binary large object) — для двоичных данных. В PostgreSQL типы clob и blob отсутствуют. Вместо них для символьных данных можно использовать тип text, а для двоичных — bytea (byte array). Работа с данными типа text не зависит от размера значения, поэтому в дальнейшем будем говорить только о двоичных данных. Например, об изображениях, видео, аудио и пр. Как правило такие данные хранят содержимое файлов соответствующих форматов. В качестве альтернативы типу bytea, можно использовать «отчасти устаревшую» (цитата из документации) подсистему работы с большими объектами. Но прежде чем размещать данные файлов внутри базы, следует рассмотреть возможность хранения их «в обычном виде» в файловой системе. ## Page 4: Файловая система {#source-page-004} ![Source slide page 4: Файловая система](images/page-004.png){#source-figure-004 caption="Source slide page 4: Файловая система"} ### Комментарии преподавателя Если говорить о двоичных данных большого объема, то один из вариантов — отказаться от размещения их в СУБД. В этом случае файлы с двоичными данными располагаются непосредственно в файловой системе, а внутри базы данных поддерживаются только ссылки на них. ## Page 5: Файловая система {#source-page-005} **Преимущества** - скорость чтения и записи файлов - не увеличивается размер базы данных - Недостатки - не используются возможности СУБД: транзакции, управление доступом - отсутствуют гарантии согласованности, долговечности данных - усложняется архитектура системы, - например, в части резервного копирования и восстановления ### Комментарии преподавателя Такой подход может быть оправдан в первую очередь производительностью. Скорость чтения и записи файлов увеличивается за счет избежания затрат, характерных для СУБД: ● двойная запись на диск (в WAL и файлы данных); ● двойное кеширование в оперативной памяти (файловый кеш и буферный кеш СУБД); ● накладные расходы на хранение в базе данных и обработку (нарезка на фрагменты для хранения и склейка их при чтении); ● возможное распухание таблиц и индексов приводит к дополнительному чтению устаревших фрагментов значения. Кроме того, удается сократить расходы на обслуживание. Не требуется очистка и переиндексация таблиц с большими данными. Копирование файлов, в том числе инкрементальное, можно выполнять средствами ОС. А резервные копии только базы данных будут создаваться быстрее, меньшего размера и потребуют меньше времени на восстановление. С другой стороны, становится невозможным использовать преимущества СУБД: ● транзакционная обработка: атомарность и согласованность записи, конкурентный доступ, восстановление после сбоя; ● управление доступом пользователей на чтение и запись данных. Архитектура системы может потребовать пересмотра и доработки процедур резервного копирования и восстановления, использования реплик, обнаружения неиспользуемых файлов и пр. ## Page 6: TOAST {#source-page-006} **Хранение «длинных» атрибутов в отдельной таблице** - применяется для типов переменной длины: 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-таблице — это экономит место. ## Page 7: Стратегии хранения {#source-page-007} - основная - сжатие - данных - TOAST- - таблица - таблица - Extended да да да - External да ― да - в самую - последнюю - Main да в последнюю - очередь - очередь - Plain да ― ― ### Комментарии преподавателя Столбцы таблиц используют одну из четырех стратегий хранения значений, устанавливаемую в зависимости от типа данных: ● EXTENDED. Допускается сжатие и перенос в таблицу TOAST. Для типов, поддерживающих перенос в TOAST, обычно используется эта стратегия. ● EXTERNAL. Допускается только перенос в таблицу TOAST, но не сжатие. Если загружаемые данные уже сжаты, имеет смысл выбрать стратегию EXTERNAL для экономии затрат на малоэффективное повторное сжатие. ● MAIN. Поля с этой стратегией сжимаются только в том случае, если обработки полей EXTENDED и EXTERNAL оказалось недостаточно, чтобы уместить версию строки на странице. Перенос в таблицу TOAST тоже возможен, но только как крайняя мера. ● PLAIN. Хранение только в основной таблице. Используется для типов, значения которых имеют небольшой размер. При создании пользовательского типа данных можно задать стратегию, применяемую по умолчанию. На уровне столбца таблицы можно переопределить стратегию, а также указать метод сжатия. ## Page 8: Тип bytea {#source-page-008} ![Source slide page 8: Тип bytea](images/page-008.png){#source-figure-008 caption="Source slide page 8: Тип bytea"} ### Комментарии преподавателя Хранить двоичные объекты в базе данных можно двумя способами. Первый способ заключается в создании столбцов с типом bytea в таблицах. Тип bytea использует технологию хранения TOAST. Поэтому значения большого размера размещаются не в самой таблице, а нарезаются на фрагменты и хранятся в TOAST-таблице, связанной с основной. ## Page 9: Тип bytea {#source-page-009} **Преимущества** - транзакционность, управление доступом - согласованность и долговечность больших данных - интерфейс SQL - Недостатки - увеличение размера базы данных - меньшая скорость, чем при работе с файлами - размер значения до 1 Гбайта - значения считываются и записываются целиком ### Комментарии преподавателя Главное преимущество хранения больших значений в базе данных — это использование возможностей СУБД. Транзакционный механизм гарантирует согласованность данных и защиту от сбоев. А разграничивать доступ пользователей к значениям можно при помощи средств СУБД. Для работы с типом bytea используется язык SQL, что делает эти действия удобными. Двоичные типы данных, функции и операторы для работы с ними: Как мы уже говорили ранее, хранение внутри базы данных увеличивает размер базы и расходы на ее сопровождение, а также уменьшает скорость чтения и записи данных из-за необходимости нарезать значение на фрагменты и склеивать их, помещая в дополнительный кеш. К недостаткам использования типа bytea можно отнести и ограничение TOAST на размер одного значения в 1 Гбайт. Также отметим, что при работе c TOAST изменение даже небольшой части значения приводит к считыванию в буферный кеш всех занятых значением страниц и к перезаписи всего значения. С чтением части значения дело обстоит лучше: возможно извлечение только необходимого фрагмента. ## Page 10: Тип bytea и TOAST {#source-page-010} ```sql => CREATE DATABASE ext_lob; ``` ```sql CREATE DATABASE ``` ```sql => \c ext_lob ``` ```text You are now connected to database "ext_lob" as user "student". ``` По умолчанию двоичные данные выводятся в шестнадцатеричном формате. ```text => SHOW bytea_output; ``` ```text bytea_output -------------- hex (1 row) ``` Значения начинаются с '\\x', далее каждый байт представлен двумя шестнадцатеричными цифрами: ```sql => SELECT 'Hello'::bytea; ``` ```text bytea -------------- \x48656c6c6f (1 row) ``` Добавим нулевой символ к строке: ```sql => SELECT 'Hello'::bytea || '\x00'::bytea; ``` ```text ?column? ---------------- \x48656c6c6f00 (1 row) ``` Шестнадцатеричный формат появился в версии 9.0. До этого был доступен только формат «спецпоследовательностей». ```text => SET bytea_output = 'escape'; ``` ```text SET ``` В этом формате ASCII-символы отображаются как есть, а остальные представлены спецпоследовательностями: ```sql => SELECT 'Hello'::bytea || '\x00'::bytea; ``` ```text ?column? ----------- Hello\000 (1 row) ``` Параметр bytea\_output определяет только формат вывода двоичных данных. Входные данные принимаются в любом из этих двух форматов. ```text => RESET bytea_output; ``` ```text RESET ``` Теперь создадим таблицу со столбцом типа bytea. ```sql => CREATE TABLE demo_bytea( filename text, data bytea ); ``` ```sql CREATE TABLE ``` Таблица TOAST создается автоматически для хранения больших значений data и filename (значения типа text также могут быть большими). Найдем имя служебной таблицы: ```sql => SELECT reltoastrelid::regclass AS toast_table FROM pg_class WHERE oid = 'demo_bytea'::regclass; ``` ## Page 11: Page 11 {#source-page-011} ```text toast_table ------------------------- pg_toast.pg_toast_16567 (1 row) ``` Служебные таблицы TOAST всегда располагаются в специальной схеме pg\_toast — чтобы не пересекаться с обычными объектами базы данных. Получить список TOAST-таблиц можно командой psql: ```text => \dtS pg_toast.pg_toast_16567* ``` ```text List of relations Schema | Name | Type | Owner ----------+----------------+-------------+--------- pg_toast | pg_toast_16567 | TOAST table | student (1 row) ``` Посмотрим на структуру TOAST-таблицы: ```sql => \d pg_toast.pg_toast_16567 ``` ```text TOAST table "pg_toast.pg_toast_16567" Column | Type ------------+--------- chunk_id | oid chunk_seq | integer chunk_data | bytea Owning table: "public.demo_bytea" Indexes: "pg_toast_16567_index" PRIMARY KEY, btree (chunk_id, chunk_seq) ``` chunk\_id — идентификатор значения, chunk\_seq — порядковый номер фрагмента значения, chunk\_data — данные фрагмента. Можно обратить внимание, что вывод команды \\d для TOAST-таблиц содержит название основной таблицы. Доступ к значениям в TOAST всегда осуществляется по индексу. Это самый быстрый способ получить все фрагменты одного значения для склейки, но доступ ко всем значениям будет заведомо неэффективен. Кроме того, чтение большого объема данных из TOAST-таблиц может приводить к вытеснению полезных данных из буферного кеша. Механизм буферного кольца, предотвращающий массовое вытеснение, для TOAST-таблиц не используется, так как задействуется только при полном последовательном сканировании таблицы, но не при индексном доступе. Добавим в demo\_bytea строку. В качестве данных возьмем логическую резервную копию базы данных приложения. ```sh student$ pg_dump -d bookstore2 > /tmp/bookstore2.sql ``` ```sh student$ ls -l /tmp/bookstore2.sql ``` ```text -rw-rw-r-- 1 student student 1791242 апр 10 16:04 /tmp/bookstore2.sql ``` Заметим время вставки. ```text => \timing on ``` ```text Timing is on. ``` Для считывания файла воспользуемся встроенной функцией pg\_read\_binary\_file. ```sql => INSERT INTO demo_bytea(filename, data) VALUES ( 'bookstore2.sql', pg_read_binary_file('/tmp/bookstore2.sql') ); ``` ```sql INSERT 0 1 Time: 135,614 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Использование TOAST прозрачно для приложения. Нам не нужно обращаться к служебной таблице в запросах. Вот первые 16 байт загруженного файла в двоичном виде: ```sql => SELECT substring(data,1,16) FROM demo_bytea; ``` ## Page 12: Page 12 {#source-page-012} ```text substring ------------------------------------ \x2d2d0a2d2d20506f737467726553514c (1 row) ``` Общий размер загруженного значения соответствует размеру файла: ```sql => SELECT length(data) FROM demo_bytea; ``` ```text length --------- 1791242 (1 row) ``` Однако размер таблицы demo\_bytea составляет всего одну страницу, данных загруженного файла в ней нет: ```sql => SELECT pg_relation_size('demo_bytea'); ``` ```text pg_relation_size ------------------ 8192 (1 row) ``` Значение столбца data попало в TOAST-таблицу. Можно убедиться, что в служебной таблице появилось одно значение: ```sql => SELECT count(distinct(chunk_id)) FROM pg_toast.pg_toast_16567; ``` ```text count ------- 1 (1 row) ``` Сколько места требуется для хранения TOAST-таблицы, если сравнивать с размером файла: меньше, больше или ровно столько же? ```sql => SELECT pg_relation_size('pg_toast.pg_toast_16567'); ``` ```text pg_relation_size ------------------ 794624 (1 row) ``` Почему потребовалось меньше места? ```sql => \d+ demo_bytea ``` ```text Table "public.demo_bytea" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ----------+-------+-----------+----------+---------+----------+-------------+------------- -+------------- filename | text | | | | extended | | | data | bytea | | | | extended | | | Access method: heap ``` Стратегия хранения extended предполагает сжатие данных при помещении в TOAST. А текстовые данные хорошо сжимаются. Начиная с PostgreSQL 14 появилась возможность выбора метода сжатия: pglz (используется по умолчанию) и lz4. Изменим метод сжатия для столбца data и повторим наш опыт, предварительно опустошив таблицу. ```sql => ALTER TABLE demo_bytea ALTER COLUMN data SET COMPRESSION lz4; ``` ```sql ALTER TABLE ``` ```text => TRUNCATE demo_bytea; ``` ```text TRUNCATE TABLE ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => INSERT INTO demo_bytea(filename, data) VALUES ( 'bookstore2.sql', pg_read_binary_file('/tmp/bookstore2.sql') ); ``` ## Page 13: Page 13 {#source-page-013} ```sql INSERT 0 1 Time: 33,870 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Вставка данных выполнилась заметно быстрее, но места для сжатых данных потребуется больше — lz4 сжимает данные не так эффективно: ```sql => SELECT pg_relation_size('pg_toast.pg_toast_16567'); ``` ```text pg_relation_size ------------------ 892928 (1 row) ``` Сжатие вообще можно запретить, выбрав стратегию external: ```sql => ALTER TABLE demo_bytea ALTER COLUMN data SET STORAGE external; ``` ```sql ALTER TABLE ``` Это изменение будет действовать только для новых строк, поэтому снова опустошим таблицу и загрузим файл заново. ```text => TRUNCATE demo_bytea; ``` ```text TRUNCATE TABLE ``` ```sql => INSERT INTO demo_bytea(filename, data) VALUES ( 'bookstore2.sql', pg_read_binary_file('/tmp/bookstore2.sql') ); ``` ```sql INSERT 0 1 ``` Сколько теперь потребуется места для хранения TOAST-таблицы, если сравнивать с размером исходного файла: меньше, больше или ровно столько же? ```sql => SELECT pg_relation_size('pg_toast.pg_toast_16567'); ``` ```text pg_relation_size ------------------ 1843200 (1 row) ``` Теперь для хранения используется немного больше места. Почему? ```sql => SELECT chunk_id, chunk_seq, substring(chunk_data,1,16), length(chunk_data) FROM pg_toast.pg_toast_16567 ORDER BY 1,2 LIMIT 3; ``` ```text chunk_id | chunk_seq | substring | length ----------+-----------+------------------------------------+-------- 16580 | 0 | \x2d2d0a2d2d20506f737467726553514c | 1996 16580 | 1 | \x726d617420746578742c20726174696e | 1996 16580 | 2 | \x73656172636820746578742c206f7264 | 1996 (3 rows) ``` На размер повлияли накладные расходы на хранение фрагментов в отдельных строках с дополнительными столбцами и служебной информацией. ## Page 14: Большие объекты {#source-page-014} ![Source slide page 14: Большие объекты](images/page-014.png){#source-figure-014 caption="Source slide page 14: Большие объекты"} ### Комментарии преподавателя Другой вариант хранения двоичных данных большого размера внутри базы данных — использование подсистемы «больших объектов», появившейся задолго до TOAST. Все большие объекты сохраняются в отдельной таблице системного каталога pg\_largeobject. Для ссылок на большие объекты в столбцах других таблиц предоставляются идентификаторы типа oid. Доступ к этим объектам осуществляется при помощи специального интерфейса, похожего на интерфейс работы с файлами. ## Page 15: Большие объекты {#source-page-015} **Преимущества** - транзакционность, управление доступом - согласованность и долговечность больших данных - размер значения до 4 Тбайт - потоковое чтение и запись фрагментов значения - Недостатки - увеличение размера базы данных - меньшая скорость, чем при работе с файлами - специальный интерфейс - 32) - риск появления осиротевших объектов - не поддерживается логической репликацией - ограничение на количество больших объектов (2 ### Комментарии преподавателя Большие объекты наследуют те же преимущества и недостатки, что и тип bytea, относительно хранения в файловой системе. По сравнению с типом bytea у больших объектов есть преимущества. Во-первых, размер одного объекта может достигать 4 Тбайт. А во-вторых, интерфейс для работы с большими объектами позволяет работать с произвольным фрагментом значения, а не со всем значением целиком, как обычно происходит с TOAST. С другой стороны, использование специального интерфейса делает работу с большими объектами не такой удобной, как с чистым SQL. К тому же драйвер PostgreSQL должен поддерживать этот интерфейс. Идентификатор большого объекта хранится в 4-байтовом типе oid, что ограничивает количество объектов в системе числом 4294967296. Таблицы TOAST тоже используют oid, но ограничение на количество значений действует в пределах таблицы, а не на всю систему. Кроме того, хранение всех больших объектов в отдельной таблице увеличивает риск появления осиротевших объектов, на которые не осталось ссылок в обычных таблицах. Для поиска и удаления осиротевших объектов можно использовать утилиту vacuumlo, а для предотвращения их появления — расширение lo. Логическая репликация не поддерживает изменения в таблицах системного каталога, в том числе в pg\_largeobject, то есть большие объекты не реплицируются. ## Page 16: Использование large objects {#source-page-016} Большие объекты хранятся в таблице системного каталога pg\_largeobject, структура которой похожа на структуру TOAST-таблицы. ```sql => \d pg_largeobject ``` ```text Table "pg_catalog.pg_largeobject" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- loid | oid | | not null | pageno | integer | | not null | data | bytea | | not null | Indexes: "pg_largeobject_loid_pn_index" PRIMARY KEY, btree (loid, pageno) ``` Создадим таблицу для хранения ссылок на большие объекты. ```sql => CREATE TABLE demo_largeobject( filename text, link oid ); ``` ```sql CREATE TABLE ``` Для работы с большими объектами будем использовать интерфейсные функции SQL. ```sql => INSERT INTO demo_largeobject VALUES ( 'bookstore2.sql', lo_import('/tmp/bookstore2.sql') ); ``` ```sql INSERT 0 1 ``` Функция lo\_import загружает файл с сервера в pg\_largeobject и возвращает указатель на него (OID). Функция lo\_get считывает указанную часть значения: ```sql => SELECT filename, link, lo_get(link,1,16) FROM demo_largeobject; ``` ```text filename | link | lo_get ----------------+-------+------------------------------------ bookstore2.sql | 16586 | \x2d0a2d2d20506f737467726553514c20 (1 row) ``` Что будет, если удалить строку из таблицы demo\_largeobject? ```sql => DELETE FROM demo_largeobject; ``` ```sql DELETE 1 ``` Строка удалится, а большой объект станет «потерянным»: ```text => \lo_list ``` ```text Large objects ID | Owner | Description -------+---------+------------- 16586 | student | (1 row) ``` Дополнительная утилита vacuumlo, поставляемая с сервером, позволяет найти большие объекты, на которые не осталось ссылок, и удалить их: ```sh student$ vacuumlo --verbose ext_lob ``` ```sql Connected to database "ext_lob" Checking link in public.demo_largeobject ​Removing lo 16586 ​Successfully removed 1 large objects from database "ext_lob". ``` ```text => \lo_list ``` ```text Large objects ID | Owner | Description ----+-------+------------- (0 rows) ``` ## Page 17: Page 17 {#source-page-017} Для предотвращения потери ссылок также можно воспользоваться расширением lo. ```sql => CREATE EXTENSION lo; ``` ```sql CREATE EXTENSION ``` Расширение создает тип данных lo (обертка над oid) и функцию lo\_manage для использования в триггерных функциях. ```sql => CREATE TABLE demo_lo (filename text, link lo); ``` ```sql CREATE TABLE ``` ```sql => CREATE TRIGGER t_link BEFORE UPDATE OR DELETE ON demo_lo FOR EACH ROW EXECUTE FUNCTION lo_manage(link); ``` ```sql CREATE TRIGGER ``` Загрузим большой объект и поместим ссылку на него в таблицу. ```sql => INSERT INTO demo_lo VALUES ( 'bookstore2.sql', lo_import('/tmp/bookstore2.sql') ); ``` ```sql INSERT 0 1 ``` Убедимся, что все на месте, и удалим. ```sql => SELECT filename, lo_get(link,1,16) FROM demo_lo; ``` ```text filename | lo_get ----------------+------------------------------------ bookstore2.sql | \x2d0a2d2d20506f737467726553514c20 (1 row) ``` ```sql => DELETE FROM demo_lo; ``` ```sql DELETE 1 ``` ```text => \lo_list ``` ```text Large objects ID | Owner | Description ----+-------+------------- (0 rows) ``` Табличный триггер удалил связанный большой объект. ## Page 18: Итоги {#source-page-018} **Для работы с двоичными данными предоставлены** - тип bytea, использующий технологию TOAST - подсистема «больших объектов» - Использование файловой системы позволяет добиться высокой скорости за счет отказа от возможностей СУБД ## Page 19: Практика {#source-page-019} 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 уже создана в базе данных, но ничего не возвращает. Заголовок функции: ```sql CREATE FUNCTION webapi.get_image(book_id integer) RETURNS bytea ``` ## Page 20: 1. Перекладывание сканов обложек в базу данных {#source-page-020} Сканы обложек находятся в каталоге covers: ```sh student$ ls ~/covers/ | head -n 10 ``` ```text abelson_prog.jpg adelson_chess.jpg aho_compilers.jpg aho_structalgo.jpg ben-ari_pl.jpg bogdesko_calligraphy.jpg boswell_readable.jpg bringhurst_style.jpg brooks_myth.jpg camp_no.jpg ``` Названия файлов хранятся в таблице books в столбце cover\_filename. Расширим таблицу столбцом для хранения картинок: ```sql => ALTER TABLE books ADD cover bytea; ``` ```sql ALTER TABLE ``` Используемую по умолчанию компрессию данных стоит отключить: дополнительно сжать файлы JPEG не удастся, а ресурсы на упаковку/распаковку можно сэкономить. Установить нужную стратегию хранения можно только отдельной командой после добавления столбца: ```sql => ALTER TABLE books ALTER COLUMN cover SET STORAGE external; ``` ```sql ALTER TABLE ``` Переложим файлы в этот столбец: ```sql => UPDATE books b SET cover = pg_read_binary_file( '/home/student/covers/' || b.cover_filename ); ``` ```sql UPDATE 100 ``` ### 2. Функция, возвращающая скан обложки ```sql => CREATE OR REPLACE FUNCTION webapi.get_image(book_id bigint) RETURNS bytea LANGUAGE sql STABLE SECURITY DEFINER RETURN (SELECT b.cover FROM books b WHERE b.book_id = get_image.book_id); ``` ```sql CREATE FUNCTION ``` ### 3. Время выполнения SELECT \\\* Сравните время самостоятельно; здесь мы не приводим результат выполнения запросов из-за большого размера. Вывод должен быть очевиден: в промышленном коде следует обращаться только к тем столбцам, которые действительно необходимы. ## Page 21: Практика+ {#source-page-021} 1. Создайте таблицу с полем типа bytea. Загрузите в таблицу любой файл, этот же файл загрузите как большой объект. Выполните контрольную точку, чтобы в кеше не осталось грязных буферов. 2. Добавьте один произвольный байт к значению в столбце типа bytea таблицы. Сколько грязных буферов, относящихся к TOAST-таблице, появилось в буферном кеше? 3. Добавьте один произвольный байт к значению большого объекта. Сколько грязных буферов, относящихся к таблице pg\_largeobject, появилось в буферном кеше? ### Комментарии преподавателя 2 и 3. Чтобы получить количество грязных буферов для таблицы, установите расширение pg\_buffercache и воспользуйтесь запросом: ```sql SELECT count(*) FROM pg_buffercache b WHERE b.relfilenode = pg_relation_filenode('table_name'::regclass) AND b.relforknumber = 0 -- основной слой AND isdirty; ``` 3. Можно воспользоваться функцией lo\_put, записывающей данные по заданному смещению. ## Page 22: 1. Подготовка {#source-page-022} ```sql => CREATE DATABASE ext_lob; ``` ```sql CREATE DATABASE ``` ```sql => \c ext_lob ``` ```text You are now connected to database "ext_lob" as user "student". ``` Расширение для просмотра буферного кеша: ```sql => CREATE EXTENSION pg_buffercache; ``` ```sql CREATE EXTENSION ``` Создадим таблицу со столбцом типа bytea: ```sql => CREATE TABLE demo_bytea (filename text, data bytea); ``` ```sql CREATE TABLE ``` Загрузим файл с одной из обложек книг в таблицу: ```sql => INSERT INTO demo_bytea VALUES ( 'novikov_dbtech.jpg', pg_read_binary_file('/home/student/covers/novikov_dbtech.jpg') ); ``` ```sql INSERT 0 1 ``` Этот же файл загрузим как большой объект и запомним его oid: ```sql => SELECT lo_import('/home/student/covers/novikov_dbtech.jpg') AS "oid"; ``` ```text oid ------- 16581 (1 row) ``` Для чистоты эксперимента очистим таблицы и сбросим все грязные буферы на диск: ```sql => VACUUM (analyze) demo_bytea, pg_largeobject; ``` ```sql VACUUM ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ### 2. Изменение bytea Добавим к значению нулевой байт: ```sql => UPDATE demo_bytea SET data = data || '\x00'::bytea; ``` ```sql UPDATE 1 ``` Сколько всего страниц в основном слое TOAST-таблицы? ```sql => SELECT reltoastrelid::regclass AS toast_table FROM pg_class WHERE oid = 'demo_bytea'::regclass; ``` ```text toast_table ------------------------- pg_toast.pg_toast_16575 (1 row) ``` ```sql => SELECT pg_relation_size('pg_toast.pg_toast_16575', 'main') / 8192 buffers; ``` ```text buffers --------- 294 (1 row) ``` А сколько грязных буферов хранит страницы основного слоя? ## Page 23: Page 23 {#source-page-023} ```sql => SELECT count(*) FROM pg_buffercache b WHERE b.relfilenode = pg_relation_filenode('pg_toast.pg_toast_16575') AND b.relforknumber = 0 -- основной слой AND b.isdirty; ``` ```text count ------- 294 (1 row) ``` То есть после изменения одного байта все страницы TOAST-таблицы стали грязными. ### 3. Изменение большого объекта Добавим в конец объекта нулевой байт: ```sql => SELECT size FROM pg_stat_file('/home/student/covers/novikov_dbtech.jpg'); ``` ```text size --------- 1170438 (1 row) ``` ```sql => SELECT lo_put(16581, 1170438, '\x00'::bytea); ``` ```text lo_put -------- (1 row) ``` Посчитаем грязные буферы таблицы pg\_largeobject: ```sql => SELECT count(*) FROM pg_buffercache b WHERE b.relfilenode = pg_relation_filenode('pg_largeobject') AND b.relforknumber = 0 -- основной слой AND b.isdirty; ``` ```text count ------- 1 (1 row) ``` Грязным стал всего один буфер.