Официальные расширения PostgreSQL

ServBay — это мощная локальная среда веб-разработки, интегрирующая множество инструментов для разработчиков, в том числе полнофункциональную базу данных PostgreSQL. Преимущество PostgreSQL заключается не только в базовых возможностях, но и в обширной экосистеме официальных расширений. В ServBay предустановлено большинство официальных расширений PostgreSQL, и разработчики могут легко включать и использовать эти модули прямо в своей локальной среде, значительно расширяя функциональность базы данных для различных сложных задач.

В этой статье рассматриваются наиболее востребованные официальные расширения PostgreSQL и приводятся инструкции по их установке и использованию в ServBay.

Обзор популярных расширений

Официальные расширения PostgreSQL охватывают множество функций: от мониторинга производительности и новых типов данных до полнотекстового поиска. Вот некоторые из наиболее часто используемых расширений среди разработчиков:

1. pg_stat_statements

• Описание: Собирает и записывает статистику по всем SQL-запросам, выполненным на сервере, включая количество вызовов, общее время выполнения, количество обработанных строк и прочие параметры.
• Применение: Помогает администраторам и разработчикам анализировать производительность запросов, выявлять медленные операции и узкие места.
• Команда для установки:

  CREATE EXTENSION pg_stat_statements;

  Примечание: после установки обычно требуется прописать параметр shared_preload_libraries = 'pg_stat_statements' в файле postgresql.conf и перезапустить сервер PostgreSQL, чтобы модуль начал сбор данных. В ServBay предусмотрена удобная возможность изменения конфигурации.

2. pg_trgm

• Описание: Предоставляет функции и индексы для измерения сходства текстов на основе триграмм (трёхсимвольных сочетаний).
• Применение: Используется для эффективного нечеткого поиска, поиска по похожести и полнотекстового поиска, особенно пригодно при отсутствии точной формулировки или наличии опечаток.
• Команда для установки:

  CREATE EXTENSION pg_trgm;

3. hstore

• Описание: Вводит тип данных для хранения наборов пар «ключ-значение» в одном поле PostgreSQL.
• Применение: Оптимально для сценариев с необходимости хранения динамических, неструктурированных или полуструктурированных данных — например, конфигураций, метаданных, пользовательских настроек — без строгой структуры таблиц.
• Команда для установки:

  CREATE EXTENSION hstore;

4. citext

• Описание: Позволяет использовать регистронезависимый тип текстовых строк.
• Применение: Удобно для запросов, где важно игнорировать регистр, например, при хранении/сравнении имен пользователей, почтовых адресов, тегов — без необходимости постоянно применять функцию LOWER().
• Команда для установки:

  CREATE EXTENSION citext;

5. uuid-ossp

• Описание: Предоставляет функции для генерации универсальных уникальных идентификаторов (UUID) различных стандартов (версии 1, 3, 4, 5).
• Применение: Используется для создания глобально уникальных идентификаторов, например, для первичных ключей, сессионных ID, уникальных маркеров в распределённых системах.
• Команда для установки:

  CREATE EXTENSION "uuid-ossp";

  Примечание: название расширения содержит дефис, поэтому в команде CREATE EXTENSION оно должно быть заключено в двойные кавычки.

6. intarray

• Описание: Обеспечивает дополнительные функции и операторы для работы с массивами целых чисел.
• Применение: Подходит для сценариев, где требуется выполнять на уровне базы сложные операции с integer-массивами — объединения, пересечения, проверки включения, сортировки и пр.
• Команда для установки:

  CREATE EXTENSION intarray;

7. btree_gin

• Описание: Добавляет индексные возможности GIN (Generalized Inverted Index) для B-tree-типов данных (числа, строки, даты и т.д.).
• Применение: Индексы GIN обычно применяются к типам с множественными значениями (массивы, hstore, tsvector). Модуль btree_gin позволяет применять GIN к btree-ключам в этих типах, ускоряя запросы.
• Команда для установки:

  CREATE EXTENSION btree_gin;

8. btree_gist

• Описание: Добавляет поддержку индексов GiST (Generalized Search Tree) на btree-типах данных.
• Применение: GiST — универсальная структура индексации. С помощью btree_gist можно создавать GiST-индексы на btree-данных, что полезно для сложных запросов, например, по диапазонам или для комбинированных индексов по нескольким столбцам.
• Команда для установки:

  CREATE EXTENSION btree_gist;

9. cube

• Описание: Вводит тип данных для представления многомерных кубов и точек, а также соответствующие операторы и функции.
• Применение: Используется для работы с многомерными данными (геопространственные данные, аналитика по нескольким измерениям, научные расчёты).
• Команда для установки:

  CREATE EXTENSION cube;

10. fuzzystrmatch

• Описание: Реализует функции для измерения степени схожести и расстояния между строками (например, Левенштейна, Soundex, Metaphone и др.).
• Применение: Для нечеткого сравнения строк, проверки орфографии, очистки и дедупликации данных.
• Команда для установки:

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

• Описание: Процедурный язык PostgreSQL, позволяющий создавать хранимые процедуры, триггеры и анонимные блоки кода.
• Применение: Для реализации сложной бизнес-логики, автоматизации задач, обработки данных на стороне сервера. Язык plpgsql обычно уже установлен по умолчанию, но его можно явно активировать или восстановить через CREATE EXTENSION.
• Команда для установки:

  CREATE EXTENSION plpgsql;

12. tablefunc

• Описание: Включает функции для обработки и трансформации таблиц, самая известная из которых — crosstab.
• Применение: Для реализации кросс-табличных (сводных) запросов, когда уникальные значения одного столбца преобразуются в столбцы — часто требуется для отчётности и аналитики.
• Команда для установки:

  CREATE EXTENSION tablefunc;

13. dblink

• Описание: Позволяет выполнять SQL-запросы из одного экземпляра PostgreSQL к другому.
• Применение: Для передачи данных между разными базами или простой миграции данных без необходимости реализовывать взаимодействие на уровне приложения.
• Команда для установки:

  CREATE EXTENSION dblink;

14. postgres_fdw

• Описание: Реализует адаптер внешних данных, позволяющий получать доступ к таблицам других серверов PostgreSQL как к локальным.
• Применение: Для интеграции данных между базами и объединённых запросов, отличаясь от dblink большей мощностью и гибкостью, а также поддержкой различных оптимизаций.
• Команда для установки:

  CREATE EXTENSION postgres_fdw;

15. pgcrypto

• Описание: Включает разнообразные функции для шифрования и дешифрования, хеширования, симметричного и асимметричного шифрования и др.
• Применение: Для реализации защиты данных на уровне базы: шифрования, хранения паролей в хешированном виде, создания и проверки цифровых подписей.
• Команда для установки:

  CREATE EXTENSION pgcrypto;

Это лишь часть официальных расширений, поставляемых с ServBay. Гордостью проекта является интеграция практически всех основных расширений от сообщества PostgreSQL, чтобы максимально удовлетворить нужды разработчиков.

Установка и использование расширений PostgreSQL в ServBay

Процесс установки и эксплуатации расширений PostgreSQL в среде ServBay предельно прост. Обратите внимание: расширения устанавливаются для конкретной базы данных, то есть их нужно активировать отдельно в каждой базе, в которой вы хотите использовать модуль.

Требования:

• Убедитесь, что ServBay запущен, а служба базы данных PostgreSQL активна.

Пошаговая инструкция:

1. Подключитесь к целевой базе данных PostgreSQL: Откройте терминал (например, Terminal или iTerm2 на macOS) и с помощью утилиты psql подключитесь к нужной базе, где планируете установить расширение. По умолчанию в дистрибутиве ServBay логин для PostgreSQL — postgres, имя базы — также postgres.

   psql -U your_username -d your_database

   Например, подключение к базе данных по умолчанию:

   psql -U postgres -d postgres

   Если для подключения требуется пароль, используйте пароль пользователя root/postgres, заданный в настройках ServBay.

2. Установите расширение: После авторизации вы попадёте в командную строку psql. Введите команду CREATE EXTENSION с именем нужного расширения вместо module_name.

   CREATE EXTENSION module_name;

   Пример установки расширения pg_trgm:

   CREATE EXTENSION pg_trgm;

3. Проверьте результат установки: Для просмотра списка установленных расширений в текущей базе используйте команду \dx — вы увидите все активированные модули:

   \dx

Пример: активация расширения pg_trgm

Ниже приведён полный цикл работы для установки расширения pg_trgm через psql внутри базы postgres:

# Выполните в терминале команду для подключения к базе
psql -U postgres -d postgres

# Успешное подключение, появится приглашение psql
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- Просмотр текущих расширений (например, по умолчанию доступны только plpgsql и pgcrypto)
postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# -- Установка pg_trgm
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- Проверяем, что расширение появилось в списке
postgres=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 pg_trgm  | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# -- Всё готово, теперь функции и операторы pg_trgm можно использовать в этой базе

Просмотр списка доступных расширений PostgreSQL в ServBay

Перед установкой расширения зачастую полезно получить полный перечень модулей, доступных в вашей версии PostgreSQL от ServBay. Это поможет найти новые инструменты, которые могут пригодиться для оптимизации или расширения функциональности.

Для получения списка всех расширений исполните следующий SQL-запрос из любой базы PostgreSQL:

SELECT * FROM pg_available_extensions ORDER BY name;

Этот запрос вернёт таблицу с перечнем всех поддерживаемых расширений для вашей сборки PostgreSQL, их названиями, текущей и стандартной версиями, а также описаниями.

Типичный результат (список может различаться в зависимости от версии ServBay/PostgreSQL):

             name             | default_version | installed_version |                                                       comment                                                       
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_jieba                     | 1.1.1           |                   | a parser for full-text search of Chinese
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_statements           | 1.10            |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             | 1.6               | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgcrypto                     | 1.3             | 1.3               | cryptographic functions
 pgrouting                    | 3.6.2           |                   | pgRouting Extension
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 postgis                      | 3.4.2           |                   | PostGIS geometry and geography spatial types and functions
 postgis_sfcgal               | 3.4.2           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2             |                   | PostGIS tiger geocoder and reverse geocoder
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
 zhparser                     | 2.2             |                   | a parser for full-text search of Chinese
(55 rows)

Обратите внимание: приведённый выше список — лишь пример. Реально доступный перечень расширений и версии могут меняться в зависимости от обновлений ServBay и самой PostgreSQL.

Заключение

ServBay существенно облегчает работу с расширениями PostgreSQL в локальной среде за счёт предустановки большинства популярных модулей. Вам не придётся что-либо компилировать — просто выполните простую SQL-команду, чтобы активировать нужное расширение для своей базы данных, и продолжайте работу над проектом. Используя эти расширения, вы легко добавите такие продвинутые возможности, как профилирование производительности, полнотекстовый поиск, безопасность данных и многое другое.