Привет, Хабр!

Думаю, вы знаете, что поиск эффективных решений – это половина успеха. Я сам прошел через все эти тернии, когда работа с данными казалась слишком сложной и запутанной. И именно тогда я открыл для себя некоторые возможности PostgreSQL, которые значительно упростили мою жизнь.

Сегодня я хочу поговорить о трех фичах PostgreSQL, которые помогут сделать работу более продуктивной и вдохновить меня на создание более сложных и интересных проектов.

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

Фича №1: Массивы и работа с JSON

PostgreSQL выделяется среди реляционных баз данных благодаря поддержке массивов и JSON-форматов. Эта функциональность позволяет хранить и манипулировать сложными структурами данных без необходимости использования дополнительных таблиц.

Массивы в PostgreSQL позволяют хранить несколько значений одного типа данных в одной ячейке таблицы.

Создание таблицы с массивами:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[] -- массив текстовых значений для тегов
);

Вставка данных в таблицу с массивами:

INSERT INTO products (name, tags)
VALUES ('Продукт 1', ARRAY['новинка', 'распродажа']),
       ('Продукт 2', ARRAY['популярное', 'скидка']);

Извлечение данных из массива:

-- найти все продукты, содержащие тег 'новинка'
SELECT * FROM products
WHERE 'новинка' = ANY(tags);

JSON предоставляет возможность хранения и манипуляции полуструктурированными данными.

Создание таблицы с JSON:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    profile JSONB -- бинарное представление JSON
);

Вставка JSON-данных:

INSERT INTO users (name, profile)
VALUES ('Иван Иванов', '{"age": 30, "city": "Москва", "interests": ["футбол", "чтение"]}'),
       ('Мария Смирнова', '{"age": 25, "city": "Санкт-Петербург", "interests": ["музыка", "путешествия"]}');

Извлечение данных из JSON:

-- извлечь возраст и город пользователя
SELECT 
    name,
    profile->>'age' AS age,
    profile->>'city' AS city
FROM users;

-- Найти пользователей с интересом "музыка"
SELECT * FROM users
WHERE 'музыка' = ANY(profile->'interests');

Где использовать?

• Хранение списков предпочтений, например избранные продукты или метки.

• Хранение ответов API.

• Хранение агрегированных данных, таких как статистика и аналитика, в формате JSON для простоты обработки.

Допустим, есть приложение для соц. сети. Каждый пользователь имеет профиль, который может включать различные атрибуты: имя, возраст, город и интересы. Используя массивы и JSON в PostgreSQL, можно хранить и извлекать эти данные:

-- создание таблицы
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100),
    attributes JSONB,
    tags TEXT[]
);

-- вставка данных
INSERT INTO user_profiles (username, attributes, tags)
VALUES ('user1', '{"age": 28, "location": "NY", "preferences": {"newsletter": true}}', ARRAY['active', 'premium']),
       ('user2', '{"age": 34, "location": "LA", "preferences": {"newsletter": false}}', ARRAY['inactive']);

-- запрос для извлечения данных
SELECT 
    username,
    attributes->>'age' AS age,
    attributes->>'location' AS location
FROM user_profiles
WHERE 'active' = ANY(tags);

Фича №2: Расширения

Расширения в PostgreSQL – это способ добавить дополнительные возможности и функции в базу данных. С помощью их можно расширить функциональность без необходимости вносить изменения в ядро самой БД. П

Одним из самых популярных расширений в PostgreSQL –pg_trgm, который позволяет реализовать полнотекстовый поиск. Также стоит отметить PostGIS, который добавляет поддержку географических данных и функций.

Усановка и использование расширения pg_trgm:

-- установка расширения
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- создание индекса для полнотекстового поиска
CREATE INDEX trgm_idx ON articles USING gin (content gin_trgm_ops);

-- поиск похожих записей
SELECT * FROM articles
WHERE content % 'поиск';

Предположим, что есть база данных статей или блога, и хочется добавить возможность поиска по содержимому:

-- установка расширения
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- создание таблицы статей
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

-- вставка данных
INSERT INTO articles (title, content)
VALUES ('Статья 1', 'Это пример текста для полнотекстового поиска.'),
       ('Статья 2', 'Поиск похожих текстов в базе данных.');

-- создание индекса
CREATE INDEX content_trgm_idx ON articles USING gin (content gin_trgm_ops);

-- поиск статьи с использованием триграммного поиска
SELECT * FROM articles
WHERE content % 'поиск';

Установка расширения PostGIS:

-- установка PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;

-- создание таблицы с географическими данными
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates GEOGRAPHY(POINT)
);

-- вставка данных
INSERT INTO locations (name, coordinates)
VALUES ('Place 1', ST_GeographyFromText('SRID=4326;POINT(-122.4194 37.7749)')),
       ('Place 2', ST_GeographyFromText('SRID=4326;POINT(-118.2437 34.0522)'));

Со списком расширений можно ознакомиться здесь.

Фича №3: CTE и рекурсивные запросы

Общие табличные выражения и рекурсивные запросы в PostgreSQL дают возможность упрощать и организовывать сложные SQL-запросы.

Преимущества:

• CTE позволяет разбить сложные запросы на более простые и понятные части.

• Возможность создавать временные результирующие наборы данных, которые могут использоваться в основном запросе.

• Позволяет сократить повторяющийся код и улучшить производительность за счет разбивки операций на подзапросы.

Пример использования CTE для разбиения сложных запросов:

WITH top_products AS (
    SELECT id, name, sales
    FROM products
    WHERE sales > 1000
),
top_customers AS (
    SELECT id, name, purchases
    FROM customers
    WHERE purchases > 500
)
SELECT tp.name AS product_name, tc.name AS customer_name
FROM top_products tp
JOIN top_customers tc ON tp.id = tc.id;

Рекурсивные запросы позволяют работать с иерархическими структурами, например такими, как категории продуктов или организационная структура.

Рекурсивный запрос для создания иерархии категорий:

WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL


    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM category_hierarchy;

Рассмотрим пример создания иерархической структуры для компании, где каждый сотрудник может иметь подчиненных:

-- создание таблицы сотрудников
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- вставка данных
INSERT INTO employees (name, manager_id)
VALUES ('CEO', NULL),
       ('Manager 1', 1),
       ('Manager 2', 1),
       ('Employee 1', 2),
       ('Employee 2', 2),
       ('Employee 3', 3);

-- рекурсивный запрос для иерархии сотрудников
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Заключение

Надеюсь, данная статья поможет вам углубить свои знания PostgreSQL и вдохновит на использование его возможностей в своих проектах.

А какие фичи знаете вы?

Приходите на ближайшие открытые уроки, посвященные базам данных:

• 15 августа: «Отказоустойчивый кластер PostgreSQL». Поговорим про доступность, чем она достигается и как связана с построением архитектуры. Рассмотрим реализацию failover в PostgreSQL и настроим кластер, чтобы падение одной из нод нашей системы не приводило к сбоям всей системы в целом. Запись по ссылке

• 21 августа: «Кластерные возможности MongoDB». Разберем варианты репликации MongoDB, как шардировать кластер MongoDB и как выбрать ключ шардирования. Запись по ссылке