Записки программиста
Недавно мы разобрались, как PostgreSQL хранит данные на диске. Но как убедиться, что СУБД именно так и работает? Вдруг мы что-то упустили или недопоняли? Можно прочитать данные с диска с помощью утилиты hexdump и посмотреть, что там реально записано. Но это трудоемко, ведь все битики придется декодировать вручную. К счастью, с PostgreSQL идет расширение pageinspect, которое может декодировать битики за нас.
Pageinspect имеет много возможностей. Все они описаны в документации. Помимо прочего, расширение умеет декодировать индексы Hash, B-Tree, GiST, и другие. В рамках этого поста мы не будем уходить в такие дебри и рассмотрим только базовый функционал.
Для эксперимента воспользуемся той же таблицей, что и в прошлый раз:
CREATE TABLE phonebook (
"id" SERIAL PRIMARY KEY NOT NULL ,
"name" NAME NOT NULL ,
"phone" INT NOT NULL ) ;
INSERT INTO phonebook ( "name" , "phone" )
VALUES ( ‘Alice’ , 123 ) , ( ‘Bob’ , 456 ) , ( ‘Charlie’ , 789 ) ;
Заметьте, что таблица имеет первичный ключ. По нему автоматически будет создан B-Tree индекс.
Чтобы прочитать нулевую страницу таблицы phonebook и декодировать ее заголовок, выполним следующий запрос:
=# SELECT * FROM page_header(get_raw_page(‘phonebook’, 0));
-[ RECORD 1 ]———
lsn | 0/12631C00
checksum | 0
flags | 0
lower | 36
upper | 7904
special | 8192
pagesize | 8192
version | 4
prune_xid | 0
Здесь get_raw_page() считывает страницу в виде бинаря, а page_header() декодирует ее заголовок и возвращает, как record .
Также мы можем заглянуть в кортежи, их заголовки и содержимое ItemIdData[]:
=# SELECT lp, lp_off, lp_flags, lp_len, t_ctid, t_infomask2
FROM heap_page_items(get_raw_page(‘phonebook’, 0));
lp | lp_off | lp_flags | lp_len | t_ctid | t_infomask2
—-+———+———-+———+———+————-
1 | 8096 | 1 | 96 | (0,1) | 3
2 | 8000 | 1 | 96 | (0,2) | 3
3 | 7904 | 1 | 96 | (0,3) | 3
Здесь lp* соответствуют элементам массива ItemIdData[]. Столбец lp представляет собой индекс массива, а остальные — декодированные поля соответствующего элемента. Вспомним, что хранится в ItemIdData:
Pageinspect не отображает флаги в текстовом виде, но их можно подсмотреть в исходном коде:
Видим, что у нас здесь три нормальных (lp_flags = 1) кортежа. Наконец, t_ctid и t_infomask2 — это одноименные поля из структуры HeapTupleHeaderData. Напомню, что t_ctid представляет собой ItemPointer на более новую версию кортежа. Более новых версий пока нет, поэтому все кортежи ссылаются сами на себя. Поле t_infomask2 может хранить следующую информацию:
/* 11 bits for number of attributes */
#define HEAP_NATTS_MASK 0x07FF
#define HEAP_KEYS_UPDATED 0x2000
#define HEAP_HOT_UPDATED 0x4000
#define HEAP_ONLY_TUPLE 0x8000
Пока что оно просто говорит, что все кортежи имеют по три атрибута. Никаких других флагов не проставлено.
Что будет, если обновить одну из строк? Например:
=# UPDATE phonebook SET name = ‘Alex’ WHERE name = ‘Alice’;
UPDATE 1
=# SELECT lp, lp_off, lp_flags, lp_len, t_ctid, to_hex(t_infomask2)
FROM heap_page_items(get_raw_page(‘phonebook’, 0));
lp | lp_off | lp_flags | lp_len | t_ctid | to_hex
—-+———+———-+———+———+———
1 | 8096 | 1 | 96 | (0,4) | 4003
2 | 8000 | 1 | 96 | (0,2) | 3
3 | 7904 | 1 | 96 | (0,3) | 3
4 | 7808 | 1 | 96 | (0,4) | 8003
Поле t_ctid первого кортежа теперь указывает на четвертый кортеж. Кроме того, у старого кортежа был проставлен флаг HEAP_HOT_UPDATED, а у нового — HEAP_ONLY_TUPLE. Другими словами, мы видим HOT-цепочку. Следовательно, индекс по первичному ключу не был перестроен.
Если теперь выполнить VACCUM:
=# VACUUM phonebook;
VACUUM
=# SELECT lp, lp_off, lp_flags, lp_len, t_ctid, to_hex(t_infomask2)
FROM heap_page_items(get_raw_page(‘phonebook’, 0));
lp | lp_off | lp_flags | lp_len | t_ctid | to_hex
—-+———+———-+———+———+———
1 | 4 | 2 | 0 | |
2 | 8096 | 1 | 96 | (0,2) | 3
3 | 8000 | 1 | 96 | (0,3) | 3
4 | 7904 | 1 | 96 | (0,4) | 8003
… то СУБД поймет, что старый кортеж больше никому не виден, а значит, может быть удален. При этом в поле lp_flags соответствующего ItemIdData проставляется LP_REDIRECT , а поле lp_off указывает на более новую версию кортежа. Таким образом, хоть кортеж и был удален, HOT-цепочка не рвется.
При желании можно придумать еще множество захватывающих экспериментов. Придумать и провести которые я предлагаю вам самостоятельно. Моей целью было лишь показать, что pageinspect прост и приятен в использовании. Он может быть использован для отладки кода, диагностики проблем на проде, а также служить средством изучения внутренностей СУБД. Звучит как что-то, чем полезно уметь пользоваться.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.
Michael Paquier — PostgreSQL committer
pageinspect is an extension module of PostgreSQL core allowing to have a look at the contents of relations (index or table) in the database at a low level. In the case of PostgreSQL, tuples of a table are stored in blocks of data whose size can be changed with –with-blocksize at configure step. This module is particularly useful for debugging when implementing a new functionality that changes visibility of data like what could do an autovacuum or map visibility feature, or simply to understand the internals of Postgres without having to read much codea .
Without entering in details in the page and page item structures, be sure to have a look at the documentation about database page layout.
In order to install this module for the source tree, simply do the following.
Once done, the following files are installed in the share/ folder of your installation path.
Since PostgreSQL 9.1, it is necessary to use CREATE EXTENSION to finish the installation of the module. Hence connect to the database and run the following SQL command.
By doing that the following objects are created in
In those functions, get_raw_page is the most important one because it allows fetching a raw page of data for a given relation. Its output is not that useful as-is, it is however possible to deparse its output to get various readable information.
- page_header gives information about the page header with general information like the log sequence number (LSN, WAL number) of the last change done on the page, or information about remaining space on page based on its upper and lower offset, tuples item pointer being stored from the top of the page and tuple data+header being stored at the bottom of the page
- get_raw_page gives information about the tuple items, I’ll come back to that in more details later in this post
- fsm_page_contents gives an output of the FSM (freespace map, used to locate quickly on which page a tuple can be stored based on the free space available)
There are also additional functions helping to vizualize information about b-trees with bt_metap, bt_page_items and bt_page_stats.
What I really wanted to show in this post is how you can use pageinspect to visualize the changes on your database if you do some simple DML or maintenance operations. So let’s take an example:
Table aa being a fresh relation, its first record has been added in the first page of the relation storage.
When doing an INSERT command, what PostgreSQL does is setting the minimum transaction ID from where the tuple becomes visible for other session backends.
Then, how is used the space available on the page? It is possible to know more about that by having a look at the global page information with page_header.
A page structure in PostgreSQL is particular. At the top of the page 28 bytes are used for the page header information (PageHeaderData in bufpage.h). From the top, item pointers (ItemPointer) of 4 bytes are used for each tuple entry to redirect to the place on page where tuple is located. Tuple header and tuple data are actually stored at the bottom of the page, their length may vary depending on the data stored.
In the case of the first record “1” of table aa, the lower offset is defined at 28 (position of pointer on page, just after the 28 bytes of the page header). The upper offset shows that 32 bytes are used to store 28 bytes of data and 4 bytes of header. By inserting a second tuple “2”, here is what happens:
4 bytes of ItemPointer data has been added on top of the page and 32 bytes are added to the bottom for the tuple data and header.
After the secind tuple insertion, here is how the page changed.
In the case of a DELETE, what is done is to update in the page t_xmax for the tuple entry, maximum transaction ID where the tuple is visible.
For an UPDATE, what actually happens is an INSERT and a DELETE, a new entry is added in page with a fresh t_min, and the old tuple entry has its t_max updated.
Note also that the CTID of the tuple indicating the couple (pagenumber, tuple position) of the old tuple has been changed to indicate the new tuple inserted.
A last thing, what happens on those pages when performing a VACUUM?
When running the VACUUM on relation ‘aa’, my session was the only one on the server, so what has been done is removing the tuples seen as dead, as no other sessions would need them. Hence tuple entries 1 and 2 are simply removed and can be used for new fresh tuples. Note also the new value of flags for the page header, before it was set to 1 and now it became 5. In this case the page is set as PD_ALL_VISIBLE, meaning that all the tuples are visible to all the backends.
install extensions in postgresql
For example, if your administrative user was named postgres and your database was also named postgres and the module you wanted was tablefunc, you would type:
psql -U postgres -d postgres -f tablefunc.sql
or use \i command in psql:
\i /usr/share/postgresql/9.1/extension/tablefunc—1.0.sql
2. Version after 9.1 included
After 9.1(included) version, postgresql provide new command to install extensions.
CREATE EXTENSION «tablefunc»;
That is much easier!
Популярные расширения для PostgreSQL: как установить и для чего использовать
Облачные базы данных Selectel поддерживают 40 расширений для PostgreSQL. Некоторые добавляют небольшие радости оптимизации баз данных, другие — заменяют отдельные модули разработки на стороне приложения. На данный момент расширениями пользуются 26% пользователей DBaaS. Мы узнали, какие экстеншены наиболее популярны у клиентов и где они их применяют.
Если вы опытный DBA, вы точно нужны в комментариях — расскажите, какие расширения используете и как они решают ваши задачи.
PostGIS
Расширение предназначено для работы с геоданными.
Почему его выбирают
- Поддерживает пространственные индексы R-Tree/GiST и функции обработки геоданных.
- Оперирует такими геометрическими объектами, как точка, линия, полигон, мультиточка, мультилиния, мультиполигон и геометрическая коллекция. Они определены в формате Well Known Text Open GIS (с расширениями XYZ, XYM, XYZM).
- В PostGIS входят другие экстеншены по работе с геокодингом: address_standardizer; address_standardizer_data_us; postgis_tiger_geocoder, postgis_topology.
- Позволяет создавать запросы, совмещающие тесты на попадание объекта в охват и заданный радиус.
TimescaleDB
Расширение позволяет хранить временные ряды (time series-данные) и управлять ими.
В облачных базах данных Selectel расширение представлено отдельным типом БД для PostgreSQL (в качестве расширения TimescaleDB не добавляется). К созданной базе можно подключать дополнительные экстеншены. Например, расширения pg_stat_statements или postgres_fdw, которые мы рассмотрим отдельно.
Почему его выбирают
- Оптимизирует хранение time series-данных: время вставки новых значений не увеличивается при увеличении количества данных.
- Не нужно переходить на сторонние решения, заточенные под time series-данные, — ClickHouse, InfluxDB и другие.
- Работа в одном программном стеке. Можно хранить как временные ряды, так и другие типы данных на одной платформе.
uuid-ossp
Расширение генерирует уникальный идентификатор UUID вместо обычного ID.
Почему его выбирают
- Уникальный идентификатор исключает возможность конфликта идентификаторов при работы с базами данных. Это важно при копировании, объединении, масштабировании баз данных, а также при переходе на распределенную БД.
- Упрощает разработку за счет исключения дублей похожих идентификаторов.
- Возможна генерация идентификаторов с других платформ.
pg_stat_statements
Расширение собирает статистику по работе всех баз данных: какие запросы какое время выполнялись, есть ли запросы, особо нагружающие систему, и т.д.
Почему его выбирают
- Можно выследить запрос, снижающий производительность базы данных, и оптимизировать запросы.
postgres_fdw
Расширение позволяет обращаться к внешним СУБД, файлам и веб-сервисам.
Почему его выбирают
- Можно получать данные из нескольких баз, не используя сторонние инструменты.
- Пригодится для шардинга, где нужно поделить одну большую базу данных на несколько инстансов в вертикальной или горизонтальной логике.
- Поможет провести бесшовную миграцию с одной базы данных на другую или объединить несколько БД.
- Готовые FDW (foreign-data wrappers) есть у MySQL, Redis, MongoDB, ClickHouse, Kafka и других СУБД.
hstore
Расширение позволяет объединять в одной базе данных значения с разными атрибутами.
Почему его выбирают
- Можно работать одновременно с реляционными данными и теми, что нельзя отнести к одной колонке. Не нужно добавлять отдельный столбец для каждого возможного атрибута.
- С hstore можно использовать разные типы индексов. GIN или GiST будут индексировать каждый ключ и значение в пределах расширения. При фильтрации используется добавленный индекс.
pgcrypto
Расширение содержит модуль криптографических функций и позволяет хранить избранные поля баз данных в зашифрованном виде.
Почему его выбирают
- Полезно для БД, где критической ценностью обладает лишь часть данных. Полное шифрование базы данных приводит к снижению ее производительности — часть времени уйдет на дешифровку. Частичное шифрование решает проблему.
- Важно учитывать, что функции pgcrypto выполняются внутри сервера баз данных. Все данные и пароли открыто передаются между функциями pgcrypto и клиентскими приложениями, поэтому важно доверять системе и администратору баз данных.
pg_trgm
Расширение позволяет искать текстовые документы по триграммам (последовательность из трех букв, входящая в индексируемый текст).
Почему его выбирают
- Поиск с pg_trgm нечувствителен к опечаткам. Даже если пользователь ошибся в фамилии, заполняя форму с контактами, или запрос написан с ошибкой, вы получите нужный документ. Функция максимально полезна в сочетании с полнотекстовым поиском.
- Помогает ускорять LIKE/ILIKE-запросы, если нужно запросить данные с использованием метода сопоставления с образцом.
citext
Расширение адаптирует данные для регистронезависимой проверки.
Почему его выбирают
- Подходит для хранения электронных адресов, в написании которых часто используются символы разного регистра.
- Позволяет реализовывать сложную логику проверки данных с использованием нескольких таблиц.
btree_gist
Расширение позволяет работать сразу с двумя индексами PostgreSQL — btree и GiST.
Почему его выбирают
- Подходит для типов данных, где не работает жесткая семантика сравнения — «больше», «меньше» или «равно», характерная для btree.
- Предоставляет оператор «не равно» и оператор расстояния для поиска ближайших соседей с использованием индексов GiST.
- Полезно для баз данных, где часть полей индексируется только с GiST, а другая — представляет собой простые типы данных.
Как установить расширения в облачных базах данных Selectel
Установить расширения можно в панели управления Selectel. Расширения нужно добавлять для каждой существующей базы данных.
Если у вас нет базы данных, создайте ее по инструкции.
Зайдите в раздел Облачная платформа и выберите Базы данных.
Выберите нужный кластер и на его странице откройте вкладку Базы данных.
В карточке нужной базы данных в блоке Расширения нажмите кнопку Добавить. Выберите в списке расширение и сохраните изменения.