В чем сила, бр..Постгреса? Сила PostgreSQL во многом заключается в его расширяемости, которая позволяет открыть больше функциональности.
В статье рассмотрим четыре популярных расширения на PostgreSQL на 2024 год.
Используя R-деревья, PostGIS оптимизирует пространственные запросы, такие как поиск по близости, пересечение геометрий или включение одной геометрии в другую. Это позволяет выполнить запросы быстро даже для оч больших наборов данных.
PostGIS поддерживает множество геометрических фигур: точки, линии, полигоны и т.д. И предоставляет обширный набор функций для их анализа и обработки. Можно легко выполнить сложные геопространственные анализы: буферизация, объединение и нахождение пересечений.
Благодаря поддержке стандартов OGC, PostGIS интегрируется с большинством ГИС-приложений и веб-картографических сервисов, таких как QGIS и ArcGIS, а также с популярными библиотеками js для работы с картами, например, Leaflet.
PostGIS не ограничивается только векторными данными. С помощью расширения PostGIS Raster можно работать и с растровыми данными, из за этого можно работать как со с спутниковами снимками, так и с картами высот и другими растровым изображениями прямо в базе данных.
GEOMETRY: основной тип данных для хранения геом. объектов: точки, линии и полигоны
GEOGRAPHY: используется для хранения геометрических объектов в сферической системе координат
Создание геометрических объектов
ST_GeomFromText('POINT(30 10)'): создает геометрический объект из текстового представления.
ST_GeogFromText('SRID=4326;POINT(30 10)'): аналогично, но для типа GEOGRAPHY.
ST_MakePoint(longitude, latitude): создает точку из заданных координат.
ST_MakeLine(geom1, geom2): создает линию из двух или более геометрических точек.
ST_MakePolygon(geom): создает полигон из линейного кольца.
Пространственные операции
ST_Distance(geom1, geom2): возвращает минимальное расстояние между двумя геометрическими объектами.
ST_Intersects(geom1, geom2): возвращает true, если два объекта пересекаются.
ST_Contains(geom1, geom2): возвращает true, если geom1 геометрически содержит geom2.
ST_Within(geom1, geom2): возвращает true, если geom1 находится внутри geom2.
ST_Overlap(geom1, geom2): возвращает true, если два объекта перекрываются.
ST_Buffer(geom, radius): создает буфер вокруг геометрического объекта с заданным радиусом.
Пространственные агрегатные функции
ST_Union(geom1, geom2): объединяет несколько геометрических объектов в один.
ST_Collect(geom1, geom2): объединяет геометрические объекты в геометрическую коллекцию.
ST_Extent(geom): возвращает ограничивающий прямоугольник для набора геометрических объектов.
Геометрические преобразования
ST_Transform(geom, srid): преобразует геометрию в другую систему координат.
ST_Simplify(geom, tolerance): упрощает геометрию, удаляя вершины в соответствии с заданной толерантностью.
WITH service_areas AS (
SELECT service_id, ST_Buffer(location, radius) AS area
FROM services
)
SELECT service_id, ST_AsGeoJSON(ST_Union(area)) AS coverage_area
FROM service_areas
GROUP BY service_id;
В запросе используем ST_DWithin для фильтрации объектов в радиусе 1000 метров от заданной точки, в кач-ве примера тут ред сквер. ST_MakePoint создает геометрическую точку, ST_SetSRID назначает этой точке пространственный референс (4326 обозначает WGS 84), а ST_Distance используется для сортировки результатов по расстоянию от заданной точки.
А теперь представим, что компания хочет анализировать зоны покрытия своих сервисов в городе, чтобы определить, где необходимо улучшить сервис. Используя PostGIS, можно легко агрегировать данные и визуализировать зоны покрытия:
WITH service_areas AS (
SELECT service_id, ST_Buffer(location, radius) AS area
FROM services
)
SELECT service_id, ST_AsGeoJSON(ST_Union(area)) AS coverage_area
FROM service_areas
GROUP BY service_id;
ST_Buffer создает полигон вокруг точки с заданным радиусом покрытия. ST_Union объединяет перекрывающиеся или соприкасающиеся полигоны в один, чтобы показать общую зону покрытия для каждого сервиса. Результаты можно экспортировать в формате GeoJSON для визуализации на карте.
Если нужно отслеживать изменения, например, в использовании земельных участков или распространении какого-либо явления во времени, PostGIS позволяет работать с временными рядами геоданных:
SELECT year, ST_Area(ST_Union(land_use_area)) AS total_area
FROM land_use
WHERE land_use_type = 'Парковая зона'
GROUP BY year
ORDER BY year;
ST_Area используется для расчета общей площади использования земли определенного типа по годам, а ST_Union собирает все соответствующие участки в единый геометрический объект для каждого года.
Итак, с PostGIS можно легко создавать интерактивные веб-карты и ГИС-приложения, предоставляющие инструменты для визуализации и анализа геопространственных данных
Другими словами, TimescaleDB представляет собой обычные таблицы, которые на самом деле являются абстракцией множества отдельных таблиц с фактическими данными. Гипертаблица, как единичное представление, состоит из фрагментов, созданных путем разделения данных по времени и, возможно, по ключу разделения, такому как идентификатор устройства, местоположение или идентификатор пользователя.
Пользователь взаимодействует с TimescaleDB через гипертаблицы, выполняя операции создания таблиц, индексов, изменения данных и запросов. Вся работа с TimescaleDB выглядит и ощущается как работа с PostgreSQL, и пользователь может управлять и запрашивать базу данных так же, как и обычную PostgreSQL.
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL
);
SELECT create_hypertable('conditions', 'time');
Вставка данных в TimescaleDB не отличается от вставки в стандартную таблицу PostgreSQL:
INSERT INTO conditions(time, location, temperature) VALUES
('2020-01-01 00:00:00', 'office', 70.0);
Выборка данных производится с использованием стандартного SQL синтаксиса, но может включать специфические для временных рядов функции для агрегации и анализа:
SELECT time, location, AVG(temperature)
FROM conditions
WHERE time > '2020-01-01 00:00:00' AND time < '2020-01-02 00:00:00'
GROUP BY time, location;
TimescaleDB позволяет определять политики агрегации, чтобы автоматически агрегировать и сохранять результаты в отдельных гипертаблицах:
SELECT add_continuous_aggregate_policy('daily_temperature_avg',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 day');
Также есть поддержка непрерывных агрегатов, которые автоматически обновляются при добавлении новых данных:
CREATE VIEW conditions_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
location,
AVG(temperature) AS avg_temp,
AVG(humidity) AS avg_hum
FROM conditions
GROUP BY day, location;
TimescaleDB автоматически управляет чанками гипертаблиц, но можнонастраивать параметры, связанные с размером чанков, их созданием и удалением:
SELECT set_chunk_time_interval('conditions', INTERVAL '1 week');
Есть возможность сжатия данных временных рядов для оптимизации хранения:
ALTER TABLE conditions SET (timescaledb.compress, timescaledb.compress_segmentby = 'location');
SELECT add_compression_policy('conditions', INTERVAL '7 days');
pg_stat_statements автоматически трекает и сохраняет статистику по всем выполненным запросам, включая количество выполнений, общее время выполнения, минимальное, максимальное и среднее время, потребление памяти и другие важные метрики.
Редактируем файл конфигурации postgresql.conf:
# рекомендуется также включить следующие настройки для более детального анализа
# задает максимальное количество SQL-запросов, которые могут быть сохранены
pg_stat_statements.max = 10000
# отслеживает все запросы, включая те, которые не вызывают дискового ввода/вывода
pg_stat_statements.track = all
# сколько строк SQL-запроса сохранять в pg_stat_statements
track_activity_query_size = 1024
После внесения изменений перезапускаем сервер PostgreSQL.
Выполняем SQL-команду для создания расширения в БД
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Теперь, когда pg_stat_statements активирован и настроен, можно начать сбор статистики по запросам:
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
Запрос покажет топ-10 запросов, которые занимают больше всего времени на выполнение, включая количество вызовов каждого запроса, общее время выполнения, количество обработанных строк и процент попаданий в кеш.
Если нужно проанализировать выполнение конкретного запроса, можно использовать его текст для фильтрации:
SELECT query, calls, min_time, max_time, mean_time, total_time
FROM pg_stat_statements
WHERE query LIKE '%ЗАПРОС%'
ORDER BY total_time DESC;
ЗаменяемЗАПРОС на фрагмент SQL-запроса, который хотим проанализировать.
С течением времени может потребоваться очистить собранную статистику:
SELECT pg_stat_reset();
Или для очистки статистики конкретного запроса:
SELECT pg_stat_statements_reset();
С Citus вы получаете новые возможности для вашей базы данных PostgreSQL:
SELECT create_distributed_table('orders', 'customer_id');
Превращаем таблицу orders в распределенную таблицу, используя customer_id в качестве ключа распределения. Citus автоматически распределит строки по узлам в соответствии с этим ключом.
Citus оптимизирует выполнение агрегатных запросов, распараллеливая их исполнение на всех узлах кластера:
ЫELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;
Citus автоматически распределяет выполнение агрегатной функции COUNT по узлам
Citus обрабатывает JOIN-операции между распределенными таблицамиж
-- Пример JOIN-запроса между распределенными таблицами
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
Запрос соединяет заказы с информацией о клиентах, распределенной по тем же ключам
Citus позволяет легко добавлять новые узлы в кластер:
# Добавление нового узла в кластер Citus (команда выполняется в командной строке)
citus_add_node 'host=новый_узел' 'port=5432';
habr.com
В статье рассмотрим четыре популярных расширения на PostgreSQL на 2024 год.
PostGIS
PostGIS добавляет поддержку географических объектов, позволяя выполнять запросы местоположения и анализ пространственных данныхИспользуя R-деревья, PostGIS оптимизирует пространственные запросы, такие как поиск по близости, пересечение геометрий или включение одной геометрии в другую. Это позволяет выполнить запросы быстро даже для оч больших наборов данных.
PostGIS поддерживает множество геометрических фигур: точки, линии, полигоны и т.д. И предоставляет обширный набор функций для их анализа и обработки. Можно легко выполнить сложные геопространственные анализы: буферизация, объединение и нахождение пересечений.
Благодаря поддержке стандартов OGC, PostGIS интегрируется с большинством ГИС-приложений и веб-картографических сервисов, таких как QGIS и ArcGIS, а также с популярными библиотеками js для работы с картами, например, Leaflet.
PostGIS не ограничивается только векторными данными. С помощью расширения PostGIS Raster можно работать и с растровыми данными, из за этого можно работать как со с спутниковами снимками, так и с картами высот и другими растровым изображениями прямо в базе данных.
Основной синтаксис
Типы данныхGEOMETRY: основной тип данных для хранения геом. объектов: точки, линии и полигоны
GEOGRAPHY: используется для хранения геометрических объектов в сферической системе координат
Создание геометрических объектов
ST_GeomFromText('POINT(30 10)'): создает геометрический объект из текстового представления.
ST_GeogFromText('SRID=4326;POINT(30 10)'): аналогично, но для типа GEOGRAPHY.
ST_MakePoint(longitude, latitude): создает точку из заданных координат.
ST_MakeLine(geom1, geom2): создает линию из двух или более геометрических точек.
ST_MakePolygon(geom): создает полигон из линейного кольца.
Пространственные операции
ST_Distance(geom1, geom2): возвращает минимальное расстояние между двумя геометрическими объектами.
ST_Intersects(geom1, geom2): возвращает true, если два объекта пересекаются.
ST_Contains(geom1, geom2): возвращает true, если geom1 геометрически содержит geom2.
ST_Within(geom1, geom2): возвращает true, если geom1 находится внутри geom2.
ST_Overlap(geom1, geom2): возвращает true, если два объекта перекрываются.
ST_Buffer(geom, radius): создает буфер вокруг геометрического объекта с заданным радиусом.
Пространственные агрегатные функции
ST_Union(geom1, geom2): объединяет несколько геометрических объектов в один.
ST_Collect(geom1, geom2): объединяет геометрические объекты в геометрическую коллекцию.
ST_Extent(geom): возвращает ограничивающий прямоугольник для набора геометрических объектов.
Геометрические преобразования
ST_Transform(geom, srid): преобразует геометрию в другую систему координат.
ST_Simplify(geom, tolerance): упрощает геометрию, удаляя вершины в соответствии с заданной толерантностью.
Пару примерчиков
Допустим, у нас есть база данных мест с интересными объектами (например, кафешки, музеи, парки), и мы хотим найти ближайшие к заданной точке. Это классическая задача для PostGIS, которая решается с использованием функции ST_DWithin:WITH service_areas AS (
SELECT service_id, ST_Buffer(location, radius) AS area
FROM services
)
SELECT service_id, ST_AsGeoJSON(ST_Union(area)) AS coverage_area
FROM service_areas
GROUP BY service_id;
В запросе используем ST_DWithin для фильтрации объектов в радиусе 1000 метров от заданной точки, в кач-ве примера тут ред сквер. ST_MakePoint создает геометрическую точку, ST_SetSRID назначает этой точке пространственный референс (4326 обозначает WGS 84), а ST_Distance используется для сортировки результатов по расстоянию от заданной точки.
А теперь представим, что компания хочет анализировать зоны покрытия своих сервисов в городе, чтобы определить, где необходимо улучшить сервис. Используя PostGIS, можно легко агрегировать данные и визуализировать зоны покрытия:
WITH service_areas AS (
SELECT service_id, ST_Buffer(location, radius) AS area
FROM services
)
SELECT service_id, ST_AsGeoJSON(ST_Union(area)) AS coverage_area
FROM service_areas
GROUP BY service_id;
ST_Buffer создает полигон вокруг точки с заданным радиусом покрытия. ST_Union объединяет перекрывающиеся или соприкасающиеся полигоны в один, чтобы показать общую зону покрытия для каждого сервиса. Результаты можно экспортировать в формате GeoJSON для визуализации на карте.
Если нужно отслеживать изменения, например, в использовании земельных участков или распространении какого-либо явления во времени, PostGIS позволяет работать с временными рядами геоданных:
SELECT year, ST_Area(ST_Union(land_use_area)) AS total_area
FROM land_use
WHERE land_use_type = 'Парковая зона'
GROUP BY year
ORDER BY year;
ST_Area используется для расчета общей площади использования земли определенного типа по годам, а ST_Union собирает все соответствующие участки в единый геометрический объект для каждого года.
Итак, с PostGIS можно легко создавать интерактивные веб-карты и ГИС-приложения, предоставляющие инструменты для визуализации и анализа геопространственных данных
TimescaleDB
TimescaleDB увеличивает масштабирование PostgreSQL для временных рядов путем автоматического разделения данных по времени и пространству (ключу разделения), при этом сохраняя стандартный интерфейс PostgreSQL.Другими словами, TimescaleDB представляет собой обычные таблицы, которые на самом деле являются абстракцией множества отдельных таблиц с фактическими данными. Гипертаблица, как единичное представление, состоит из фрагментов, созданных путем разделения данных по времени и, возможно, по ключу разделения, такому как идентификатор устройства, местоположение или идентификатор пользователя.
Пользователь взаимодействует с TimescaleDB через гипертаблицы, выполняя операции создания таблиц, индексов, изменения данных и запросов. Вся работа с TimescaleDB выглядит и ощущается как работа с PostgreSQL, и пользователь может управлять и запрашивать базу данных так же, как и обычную PostgreSQL.
Основные возможности
Гипертаблица — это основная абстракция в TimescaleDB, которая представляет собой виртуальную таблицу, автоматически разделенную на множество физических частей:CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL
);
SELECT create_hypertable('conditions', 'time');
Вставка данных в TimescaleDB не отличается от вставки в стандартную таблицу PostgreSQL:
INSERT INTO conditions(time, location, temperature) VALUES
('2020-01-01 00:00:00', 'office', 70.0);
Выборка данных производится с использованием стандартного SQL синтаксиса, но может включать специфические для временных рядов функции для агрегации и анализа:
SELECT time, location, AVG(temperature)
FROM conditions
WHERE time > '2020-01-01 00:00:00' AND time < '2020-01-02 00:00:00'
GROUP BY time, location;
TimescaleDB позволяет определять политики агрегации, чтобы автоматически агрегировать и сохранять результаты в отдельных гипертаблицах:
SELECT add_continuous_aggregate_policy('daily_temperature_avg',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 day');
Также есть поддержка непрерывных агрегатов, которые автоматически обновляются при добавлении новых данных:
CREATE VIEW conditions_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
location,
AVG(temperature) AS avg_temp,
AVG(humidity) AS avg_hum
FROM conditions
GROUP BY day, location;
TimescaleDB автоматически управляет чанками гипертаблиц, но можнонастраивать параметры, связанные с размером чанков, их созданием и удалением:
SELECT set_chunk_time_interval('conditions', INTERVAL '1 week');
Есть возможность сжатия данных временных рядов для оптимизации хранения:
ALTER TABLE conditions SET (timescaledb.compress, timescaledb.compress_segmentby = 'location');
SELECT add_compression_policy('conditions', INTERVAL '7 days');
pg_stat_statements
pg_stat_statements дает возможность собирать статистику по выполненным SQL-запросамpg_stat_statements автоматически трекает и сохраняет статистику по всем выполненным запросам, включая количество выполнений, общее время выполнения, минимальное, максимальное и среднее время, потребление памяти и другие важные метрики.
Редактируем файл конфигурации postgresql.conf:
# рекомендуется также включить следующие настройки для более детального анализа
# задает максимальное количество SQL-запросов, которые могут быть сохранены
pg_stat_statements.max = 10000
# отслеживает все запросы, включая те, которые не вызывают дискового ввода/вывода
pg_stat_statements.track = all
# сколько строк SQL-запроса сохранять в pg_stat_statements
track_activity_query_size = 1024
После внесения изменений перезапускаем сервер PostgreSQL.
Выполняем SQL-команду для создания расширения в БД
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Теперь, когда pg_stat_statements активирован и настроен, можно начать сбор статистики по запросам:
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
Запрос покажет топ-10 запросов, которые занимают больше всего времени на выполнение, включая количество вызовов каждого запроса, общее время выполнения, количество обработанных строк и процент попаданий в кеш.
Если нужно проанализировать выполнение конкретного запроса, можно использовать его текст для фильтрации:
SELECT query, calls, min_time, max_time, mean_time, total_time
FROM pg_stat_statements
WHERE query LIKE '%ЗАПРОС%'
ORDER BY total_time DESC;
ЗаменяемЗАПРОС на фрагмент SQL-запроса, который хотим проанализировать.
С течением времени может потребоваться очистить собранную статистику:
SELECT pg_stat_reset();
Или для очистки статистики конкретного запроса:
SELECT pg_stat_statements_reset();
Citus
Citus превращает Postgres в распределенную базу данных, обеспечивая высокую производительность на любом масштабе.С Citus вы получаете новые возможности для вашей базы данных PostgreSQL:
- Распределенные таблицы сегментируются по кластеру узлов PostgreSQL, чтобы объединить ресурсы ЦП, памяти, хранилища и ввода-вывода.
- Таблицы ссылок реплицируются на все узлы для соединений и внешних ключей из распределенных таблиц для максимальной производительности чтения.
- Механизм распределенных запросов маршрутизирует и параллелизует операции SELECT, DML и другие операции над распределенными таблицами в кластере.
- Столбчатое хранилище сжимает данные, ускоряет сканирование и поддерживает быстрые проекции как в обычных, так и в распределенных таблицах.
- Запрос с любого узла позволяет использовать всю мощность вашего кластера для распределенных запросов.
Основные возможности
Распределение таблиц по множеству узлов позволяет параллельно обрабатывать запросы и данные:SELECT create_distributed_table('orders', 'customer_id');
Превращаем таблицу orders в распределенную таблицу, используя customer_id в качестве ключа распределения. Citus автоматически распределит строки по узлам в соответствии с этим ключом.
Citus оптимизирует выполнение агрегатных запросов, распараллеливая их исполнение на всех узлах кластера:
ЫELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;
Citus автоматически распределяет выполнение агрегатной функции COUNT по узлам
Citus обрабатывает JOIN-операции между распределенными таблицамиж
-- Пример JOIN-запроса между распределенными таблицами
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
Запрос соединяет заказы с информацией о клиентах, распределенной по тем же ключам
Citus позволяет легко добавлять новые узлы в кластер:
# Добавление нового узла в кластер Citus (команда выполняется в командной строке)
citus_add_node 'host=новый_узел' 'port=5432';
Популярные расширения на PostgreSQL
В чем сила, бр..Постгреса? Сила PostgreSQL во многом заключается в его расширяемости, которая позволяет открыть больше функциональности. В статье рассмотрим четыре популярных расширения на PostgreSQL...
habr.com