Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.
Установка и управление расширениями
Так как для каждой версии PostgreSQL содержание пакета contrib различается, для начала стоит посмотреть, какие функции доступны в пакете в вашей версии.
Просмотр списка стандартных расширений
Для того чтобы посмотреть расширения, доступные для установки, версию по умолчанию, версию установленного приложения и небольшое описание выполняемых функций, нужно запустить следующую команду:
SELECT * FROM pg_available_extensionsВажно : некоторые возможности доступны только с учётной записи суперпользователя или с учётной записи, имеющей права на установку. Суперпользователь – это такой всемогущий администратор сервера базы данных (в PostgreSQL по умолчанию имеет имя postgres).
Давайте подробно разберем команду, с помощью которой можно установить любые P ostgreSQL расширения:
CREATE EXTENSION IF NOT EXISTS extension_name
WITH SCHEMA schema_name
VERSION version
CASCADEВ команде мы также указали необязательные, но полезные параметры, которые можно использовать при установке.
- IF NOT EXIST: проверка существования расширения с таким именем.
- WITH SCHEMA: имя схемы, для которой будет произведена установка. Если не указано, то установится в текущую схему.
- VERSION: версия для установки. Если не указано, то будет установлена последняя.
- CASCADE: автоматическая установка всех дополнительных расширений, необходимых для работы.
Важно : после установки с помощью этой команды требуется внести определенные записи в файл конфигурации PostgreSQL, а потом перезапустить сервер.
Новая версия почти всегда лучше старой, не так ли? Создатели дорабатывают код, исправляют ошибки и предлагают новые функции, поэтому обновлять расширение важно и нужно. Для обновления версии до нужной используем команду:
ALTER EXTENSION extension_name
UPDATE TO versionЕсли не указать параметр version , будет установлена последняя версия.
Иногда расширение больше не нужно, а место в памяти для его хранения хотелось бы использовать более рационально. Удалить его можно следующей командой:
DROP EXTENSION IF EXISTS extension_name
CASCADE | RESTRICTДополнительные параметры:
- IF EXIST: проверка существования расширения.
- CASCADE: автоматическое удаление всего, что зависит от этого расширения.
- RESTRICT: не удалять расширение, если от него зависят другие объекты.
Топ самых полезных расширений для PostgreSQL
PostgreSQL pg_stat_statements**** позволяет нам узнать, какие именно запросы нагружают систему: сколько раз выполняются и сколько длятся. Так мы можем оценить работу базы данных, найти узкие места и оптимизировать процессы. Так как базы данных бывают весьма объемными, то запросы к ней должны быть оптимальны по времени исполнения. Эти метрики как раз можно увидеть с помощью pg_stat_statements.
Команда показывает SQL запрос (query), время его выполнения (total_min), среднее время выполнения запроса в миллисекундах (avg_ms), а также количество вызовов запроса (calls).
SELECT query, (total_exec_time / 1000 / 60) as total_min, mean_exec_time as avg_ms, calls
FROM pg_stat_statements
ORDER BY 1
DESC LIMIT 10;Если вас интересует, как выполнить в PostgreSQL шифрование данных, то вот нужное расширение. Оно использует криптографические функции для шифрования данных (например, паролей). В стандартной конфигурации поддерживаются следующие алгоритмы шифрования: md5, sha1, sha224, sha256, sha384 и sha512. Список алгоритмов можно расширить, сменив значение «по умолчанию» на использование OpenSSL в настройках инструмента.
Для того чтобы использовать разные индексы базы данных PostgreSQL (B-tree и GiST), нужно установить расширение btree_gist. Оно пригодится, если в базе есть пространственные данные, например, координаты города или магазина. Что это за индексы и для чего их использовать?
- Тип индекса B-tree используется по умолчанию и позволяет проиндексировать любые данные, которые могут быть отсортированы (например, числа и даты). Этот тип индекса хорош и практически универсален, но совсем не подходит для работы с данными, которые невозможно отсортировать.
- Тип индекса GiST умеет распределять данные любого типа, в том числе геоданные.
Важно : В дополнение к типичным операторам поиска для индекса B-tree, в btree_gist также поддерживается индекс**** PostgreSQL «не равно».
Временные ряды позволяют отслеживать изменения данных с течением времени (например, динамика поступающих заявок, объем продаж, температура воздуха).
Для хранения временных данных существуют специальные базы данных, например, InfluxDB или ClickHouse. Тем не менее, использование специальной базы не подходит для работы с другими типами данных. Поэтому в некоторых случаях гораздо удобнее использовать расширение Timescaledb, речь о котором пойдёт ниже.
Timescaledb**** позволяет хранить временные данные и работать с ними. Для работы скачайте нужную версию на официальном сайте и следуйте инструкции для установки. Затем добавьте расширение функцией CREATE EXTENSION, о которой мы рассказывали выше.
Это расширение базы данных позволяет хранить пары ключей и значений в одном поле данных в PostgreSQL. Подобный тип данных встречается в объектно-ориентированных языках программирования, например в Python.
С помощью hstore вы можете сохранить связку данных, не нагружая БД дополнительным полем. Например, при работе с базой данных книжного магазина, удобно создать всего один столбец, который будет одновременно хранить количество страниц, жанр и описание иллюстраций для каждой книги.
- Создадим таблицу с полем типа hstore :
CREATE TABLE books (
id serial PRIMARY KEY,
name varchar,
attributes hstore
);- Заполним значения в таблице:
INSERT INTO books (name, attributes) VALUES (
'Harry Potter and the Philosophers Stone',
'author => "J. K. Rowling",
pages => 223,
series => "Harry Potter"'
);- Выведем данные по книгам из серии «Гарри Поттер»:
SELECT name, attributes->'author' as author
FROM books
WHERE attributes->'series' = 'Harry Potter'Все атрибуты для отдельной книги будут выглядеть вот так:
SELECT * FROM books WHERE attributes->'series' = 'Harry Potter'Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.