Продолжим тему из публикации «Как поместить весь мир в обычный ноутбук: PostgreSQL и OpenStreetMap».

Сегодня визуализируем 15 мест где в России больше всего зданий. В этом нам поможет мой проект openstreetmap_h3 и PostgreSQL 15. На данный момент OpenStreetMap H3 единственное решение для импорта OSM данных партиционированных по H3 геоиндексу в PostgreSQL и Citus massive parallel processing. Сохраним данные в колоночное хранилище и посмотрим на план запроса для него.

Чем поможет колоночное хранилище в обработке геоданных?

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

В этой публикации мы будем агрегировать данные в большой таблице где нас интересуют для этих операций лишь два столбца и сможем извлечь пользу от этой модели данных на NVMe накопителе. Раньше я работал с колоночными базами данных AWS Redshift, Dremio, QuestDB и HeavyDB, но не смотря на их производительность, до функционала/протестированности и стабильности PostgreSQL им еще пока еще ох как далеко. Вот и отлично, попробуем получить все плюсы экосистемы PostgreSQL и колоночного хранилища citus. Вперед, к заветной цели на самых свежих версиях программ нашего аналитического стека!

Подготовка данных

Инструкцию как подготовить и загрузить данные я давал в публикации «Две беды: дороги и полнота данных. Считаем протяженность дорог родины по данным OpenStreetMap».

Для подготовки скриптов для колоночного(столбцового) хранилища всего лишь нужно добавить опцию columnar_storage:

java -jar target/osm-to-pgsnapshot-schema-ng-1.0-SNAPSHOT.jar -columnar_storage -source_pbf ~/dev/map/russia/russia-latest.osm.pbf

Сгенерированные моей утилитой скрипты базы данных остаются как и для «классического» heap storage в PostgreSQL, с основным отличием, что создаётся расширение, указываем что данные в нем будут некомпрессированные. Это нужно потому что храним данные на NVMe и на ноутбуке скорее упремся в производительность процессора при декомпрессии zstd, чем в пропускную способность подсистемы ввода/вывода). В бэклоге проекта ожидает реализации запрошенная мной фича на разные алгоритмы компрессии для разных колонок в citus, что в некоторых сценариях позволило бы повысить производительность запросов для больших геоданных. Каждая партиция создаётся с опцией «USING COLUMNAR»:

CREATE EXTENSION citus;
ALTER SYSTEM SET columnar.compression TO 'none';

CREATE TABLE "nodes_000" (like nodes) USING COLUMNAR;
CREATE TABLE "ways_000" (like ways) USING COLUMNAR;
CREATE TABLE  "multipolygon_000" PARTITION OF multipolygon FOR VALUES FROM (-32768) TO (2264) USING COLUMNAR;

Аналитика и факты о плане запроса

Запустим PostgreSQL 15 (докер образ базы данных из прошлой публикации уже содержит citus_columnar) и открытые данные OpenStreetMap.

docker start postgis15-russia_08_01_2023-citus

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

$ PSQL_PAGER="pspg" psql -h 172.17.0.1 -p 5432 -U postgres -d osmworld

osmworld=# \dx
                                    List of installed extensions
      Name      | Version |   Schema   |                        Description                         
----------------+---------+------------+------------------------------------------------------------
 citus          | 11.1-1  | pg_catalog | Citus distributed database
 citus_columnar | 11.1-1  | pg_catalog | Citus Columnar extension
 h3             | 4.1.1   | public     | H3 bindings for PostgreSQL
 hstore         | 1.8     | public     | data type for storing sets of (key, value) pairs
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis        | 3.3.2   | public     | PostGIS geometry and geography spatial types and functions

В этот раз у нас есть активированное citus_columnar 11.1–1 Citus Columnar extension. Результаты команды \d+

Теперь мы можем посчитать сколько зданий в таблице ways в каждом из шестигранников сетки разбиения H3 на уровне 3 для страны и выбрать из них только 15 в порядке уменьшения числа строений:

osmworld=# select h3_3, count(*) from ways where building group by 1 order by 2 desc limit 15;
 h3_3  | count  
-------+--------
  4522 | 574555
  4358 | 416860
 11635 | 359298
  4492 | 346534
 11309 | 326960
 11588 | 309015
  4118 | 258346
  4481 | 253767
 11605 | 249752
  4264 | 240154
 11382 | 219327
  4275 | 213822
  4229 | 210797
 11634 | 207777
 11604 | 207101
(15 rows)

Time: 1212,481 ms (00:01,212)

Данные уже поделены в базе на секции с помощью иерархической гексагональной геопространственной систем индексирования H3 и приблизительный «радиус» ячейки разбиения в наших широтах около 70км.

Посмотрим на план этого запроса в explain analyze, откуда узнаем что сканирование было только по колонкам h3_3, building (Columnar Projected Columns: building, h3_3), запрос выполнялся параллельно в 6 потоков по партициям, в каждой из которой был HashAggregate и результат выбирался из параллельных topN heapsort.

В нашем примере данные расположены на одном узле, но при этом возможно распределенние колоночных таблиц nodes, ways, multipolygon в Citus на множество узлов и выполнение запросов massive parallel processing надстройкой над PostgreSQL/PostGIS для горизонтального масштабирования решения по обработке данных.

                     ->  HashAggregate  (cost=2778.44..2778.51 rows=7 width=10) (actual time=30.077..30.080 rows=7 loops=1)
                           Group Key: ways_17.h3_3
                           Batches: 1  Memory Usage: 24kB
                           ->  Custom Scan (ColumnarScan) on ways_017 ways_17  (cost=0.00..2283.02 rows=99085 width=2) (actual time=0.331..16.357 rows=99054 loops=1)
                                 Filter: building
                                 Rows Removed by Filter: 87805
                                 Columnar Projected Columns: building, h3_3
 Planning Time: 5.612 ms
 JIT:
   Functions: 2598
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 170.676 ms, Inlining 0.000 ms, Optimization 34.001 ms, Emission 667.951 ms, Total 872.627 ms
 Execution Time: 1259.503 ms

Визуализация плана запроса в pgMustard и в explain.Tensor

Визуализация выполнения запроса в pgMustard

Визуализация выполнения запроса в explain.Tensor

Запущу тот же самый запрос на данных расположенных в классическом heap storage из postgis15-russia_08_01_2023:

osmworld=# select h3_3, count(*) from ways where building group by 1 order by 2 desc limit 15;
 h3_3  | count  
-------+--------
  4522 | 574555
  4358 | 416860
 11635 | 359298
  4492 | 346534
 11309 | 326960
 11588 | 309015
  4118 | 258346
  4481 | 253767
 11605 | 249752
  4264 | 240154
 11382 | 219327
  4275 | 213822
  4229 | 210797
 11634 | 207777
 11604 | 207101
(15 rows)

Time: 12787,813 ms (00:12,788)

Индексы h3 — это все замечательно, но хотелось бы увидеть на карте где расположены эти регионы! Визуализируем это в QGIS с помощью запроса с порядковыми номерами по числу зданий:

select buildings.*,h3b.bounds,row_number() OVER ( order by count desc) as rownum 
   from h3_3_bounds_complex h3b 
   inner join 
	(select h3_3, count(*) from ways where building group by 1 order by 2 desc limit 15) buildings
   on h3b.id=buildings.h3_3

Итог

Колоночное хранилище в PostgreSQL 15 легко создать и использовать с помощью citus_columnar и в определенных запросах время на порядок меньше чем запросы в классическом heap хранилище. В примере этой статьи получилось 10x ускорение запроса (1212мс против 12 787мс для примера из публикации). При этом отличия скриптов базы данных от классического постгреса получились минимальными, а запросы и инструментарий остались теми же самыми, благодаря тому что Citus — всего лишь расширение для PostgreSQL.

Интриги в аналитике данных OpenStreetMap не вышло и все эти места расположены в европейской части нашей страны. Первое место — Москва, второе — Санкт‑Петербург, третье — Краснодар. Сибирь и Дальний Восток не входят в top 15, так как Новосибирск на 22 месте, а Владивосток на 25 месте.

Колоночная база данных в PostgreSQL 15 и факты о территории России по данным OpenStreetMap