Офіційні розширення 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

• Функціонал: Додає тип даних для текстових рядків без врахування регістру.
• Призначення: Корисно там, де необхідно проводити порівняння незалежно від регістру, наприклад, для зберігання й перевірки логінів, email-адрес чи тегів — дозволяє уникати постійного використання LOWER() у запитах і спрощує бізнес-логіку.
• Команда встановлення:

  CREATE EXTENSION citext;

5. uuid-ossp

• Функціонал: Додає функції генерації універсальних унікальних ідентифікаторів (UUID) різних версій (1, 3, 4, 5).
• Призначення: Для створення глобальних унікальних ідентифікаторів — унікальні ключі, сесійні ідентифікатори, мітки в розподілених системах тощо.
• Команда встановлення:

  CREATE EXTENSION "uuid-ossp";

  Примітка: Оскільки у назві розширення є дефіс, використовуйте подвійні лапки в команді CREATE EXTENSION.

6. intarray

• Функціонал: Додає розширені функції та оператори для роботи з масивами цілих чисел.
• Призначення: Сценарії, де потрібні операції з масивами прямо на рівні бази даних — об'єднання, перетин, перевірка на включення, сортування тощо.
• Команда встановлення:

  CREATE EXTENSION intarray;

7. btree_gin

• Функціонал: Додає підтримку GIN-індексування для B-tree-типів даних (цілі числа, рядки, дати тощо).
• Призначення: GIN-індекси переважно застосовують для полів із множинними значеннями (масиви, hstore, tsvector); це розширення дозволяє комбінувати GIN із B-tree елементами для підвищення продуктивності складних запитів.
• Команда встановлення:

  CREATE EXTENSION btree_gin;

8. btree_gist

• Функціонал: Додає підтримку GiST-індексування для B-tree-типів.
• Призначення: GiST — універсальна індексна структура для складних запитів (наприклад, діапазонних, включення, складні індекси по кільком колонкам).
• Команда встановлення:

  CREATE EXTENSION btree_gist;

9. cube

• Функціонал: Додає тип даних для багатовимірних кубів/точок із відповідними функціями й операторами.
• Призначення: Корисно для роботи з багатовимірними даними — геопросторовими вимірюваннями, OLAP-аналізом, науковими розрахунками.
• Команда встановлення:

  CREATE EXTENSION cube;

10. fuzzystrmatch

• Функціонал: Запроваджує різні функції для визначення схожості рядків (Levenshtein, Soundex, Metaphone тощо).
• Призначення: Для виявлення схожих рядків, перевірки орфографії, обробки дублікатів, очищення даних тощо.
• Команда встановлення:

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

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

  CREATE EXTENSION plpgsql;

12. tablefunc

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

  CREATE EXTENSION tablefunc;

13. dblink

• Функціонал: Дозволяє з'єднуватися з іншими базами PostgreSQL ізсередини поточної БД та виконувати крос-базові запити.
• Призначення: Для простого обміну даними чи міграції між базами без складної логіки на боці додатку.
• Команда встановлення:

  CREATE EXTENSION dblink;

14. postgres_fdw

• Функціонал: Зовнішній обгортник даних (Foreign Data Wrapper) для доступу до таблиць у віддалених 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. За замовчуванням у вас є користувач postgres, база також називається postgres.

   psql -U ваш_користувач -d ваша_база

   Наприклад, для підключення до бази postgres:

   psql -U postgres -d postgres

   У разі потреби введіть пароль, який ви вказали для root/postgres у ServBay.

2. Встановіть розширення: Після підключення ви опинетеся в оболонці psql. Виконайте команду CREATE EXTENSION із назвою потрібного модуля-розширення.

   CREATE EXTENSION module_name;

   Наприклад, для pg_trgm:

   CREATE EXTENSION pg_trgm;

3. Перевірте встановлення: Для перегляду всіх активованих у поточній БД розширень скористайтеся командою \dx:

   \dx

Приклад: Активація розширення 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=# -- Повторна перевірка списку розширень: pg_trgm з’явився
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

Щоб отримати повний перелік модулів розширень, доступних у вашій інсталяції ServBay (та відповідній версії PostgreSQL), виконайте у будь-якій БД такий SQL-запит:

SELECT * FROM pg_available_extensions ORDER BY name;

Після виконання команди отримаєте таблицю з усіма розширеннями, які підтримує ваша версія PostgreSQL під ServBay: з назвами, версіями, статусом встановлення та коротким описом.

Щобільше, типовий результат виглядає так (список може бути значно більший, а версії змінюватися у різних збірках 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-команду для активації потрібного розширення у своїй базі. Фокусуйтеся на розробці вашого додатку, а розширені функції БД (аналіз продуктивності, повнотекстовий пошук, безпека даних тощо) підключайте за кілька кліків!