Azure Cosmos DB for PostgreSQL で分散データベース（Citus）を動かしてみましたのでメモを共有します。
Azure Cosmos DB for PostgreSQL は、オープンソースの Citus により分散されたテーブルを提供する PostgreSQL のマネージドサービスです。詳細は以下のページをご確認ください。
今回使用したデータは、OpenStreetMap のデータです。ビルのポリゴンから、中心座標を計算して、簡易的なアルゴリズムでメッシュに分割したものを、分散テーブル（シャーディング）してみます。
準備
まずは、Azureのポータルで Azure Cosmos DB for PostgreSQL を作成した後に、OpenStreetMap のデータを PostgreSQL にインポートします。インポートには、imposm3を使用します。
Azure Cosmos DB for PostgreSQL を作成
まずは、Azure のポータルから、最小構成で作成してみます。
PostgreSQL は、バージョン 16、Scale は、１ノードの最小構成です。
作成する構成の見積もりが右側に表示されます。
自宅のPCからアクセスできるように、ネットワークの設定をしておきます。
今回は、デフォルトの設定にしています。
今回は、デフォルトの設定にしています。
作成する Azure Cosmos DB for PostgreSQL の構成を確認した後に作成します。
これで、Azure Cosmos DB for PostgreSQLが作成できました。
作成できたら Settings の Connection strings から接続方法をメモしておいてください。（"Show connection strings for" の "*-c" は、コーディネータを示しています）
OpenStreetMap のデータを取得
OpenStreetMap の pbf ファイルをダウンロードします。今回は日本のデータを取得しています。
wget https://download.geofabrik.de/asia/japan-latest.osm.pbf
Azure Cosmos DB for PostgreSQL で PostGIS を有効にする
psql で Azure Cosmos DB for PostgreSQL のコーディネータに接続します。
psql "host=c-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your password} sslmode=require"
create_extensionを使ってExtentionを有効にします。
SELECT create_extension('postgis');
imposm3 で PostgreSQL にインポート
go でビルドとインストールを実行します。
go install github.com/omniscale/imposm3/cmd/imposm@latest
imposm3 の設定ファイルを使用したいので、リポジトリもクローンしておきます。
git clone https://github.com/omniscale/imposm3.git
Azure Cosmos DB for PostgreSQL の Connection strings を参考にして、imposm で OpenStreetMap のデータをインポートします。
imposm import \
-connection postgis://citus:{your password}@c-kentapgsql.xxx.postgres.cosmos.azure.com:5432/gis?sslmode=require \
-mapping ./imposm3/example-mapping.json \
-read japan-latest.osm.pbf \
-write
Azure Cosmos DB for PostgreSQL に インポートしたデータをプロダクションとしてデプロイ（テーブルのスキーマを変更）します。（imposm の仕組みのようですね）
imposm import \
-connection postgis://citus:{your password}@c-kentapgsql.xxx.postgres.cosmos.azure.com:5432/gis?sslmode=require \
-mapping ./imposm3/example-mapping.json \
-deployproduction
テーブルを分散させる準備
Azure Cosmos DB for PostgreSQL の Connection strings を参考にして、psqlコマンドで分散に使用するカラムを用意します。今回は、Geometry で分散させようと思いますので、Geometry をメッシュにして、メッシュで分散させようと思います。
psql で Azure Cosmos DB for PostgreSQL のコーディネータに接続します。
psql "host=c-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your password} sslmode=require"
imposm が作成した osm_buildings に mesh1 と mesh2 のカラムを作成します。
ALTER TABLE osm_buildings ALTER COLUMN mesh1 TYPE varchar(4);
ALTER TABLE osm_buildings ALTER COLUMN mesh2 TYPE varchar(6);
テーブルを確認してみると、以下のような感じになりました。
\d osm_buildings
Table "public.osm_buildings"
Column | Type | Collation | Nullable | Default
----------+-------------------------+-----------+----------+-------------------------------------------
id | bigint | | not null | nextval('osm_buildings_id_seq'::regclass)
osm_id | bigint | | not null |
name | character varying | | |
type | character varying | | |
geometry | geometry(Geometry,3857) | | |
mesh1 | character varying(4) | | |
mesh2 | character varying(6) | | |
Indexes:
"osm_buildings_pkey" PRIMARY KEY, btree (osm_id, id)
"osm_buildings_geom" gist (geometry)
geometry からメッシュを作成するための関数を用意します。（注意：今回は簡易的な実装として独自のメッシュアルゴリズムでメッシュIDを作成しているため、実際の地域メッシュとは異なります。）
-- 簡易的な１次メッシュ生成（本当は桁を合わせて0パディングしないといけない）
CREATE OR REPLACE FUNCTION meshcode1(geom geometry)
RETURNS varchar AS $$
DECLARE
x_str varchar;
y_str varchar;
x_head varchar;
y_head varchar;
BEGIN
x_str := CAST(FLOOR(ST_X(ST_Centroid(geom))) AS TEXT);
y_str := CAST(FLOOR(ST_Y(ST_Centroid(geom))) AS TEXT);
x_head := SUBSTRING(x_str FROM 1 FOR 2);
y_head := SUBSTRING(y_str FROM 1 FOR 2);
RETURN x_head || y_head;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 簡易的な２次メッシュ生成（本当は桁を合わせて0パディングしないといけない）
CREATE OR REPLACE FUNCTION meshcode2(geom geometry)
RETURNS varchar AS $$
DECLARE
x_str varchar;
y_str varchar;
x_head varchar;
y_head varchar;
BEGIN
x_str := CAST(FLOOR(ST_X(ST_Centroid(geom))) AS TEXT);
y_str := CAST(FLOOR(ST_Y(ST_Centroid(geom))) AS TEXT);
x_head := SUBSTRING(x_str FROM 1 FOR 3);
y_head := SUBSTRING(y_str FROM 1 FOR 3);
RETURN x_head || y_head;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
関数の動作を確認してみます。
SELECT ST_AsText(ST_Centroid(geometry)), meshcode1(geometry), meshcode2(geometry) FROM osm_buildings LIMIT 10;
st_astext | meshcode1 | meshcode2
----------------------------------------------+-----------+-----------
POINT(15029170.262225706 4220685.79198919) | 1542 | 150422
POINT(15481058.600412643 4567618.2309200205) | 1545 | 154456
POINT(14518889.312693825 3975316.5797044816) | 1439 | 145397
POINT(14518989.374650465 3975587.9473769744) | 1439 | 145397
POINT(14519045.061951755 3976202.229020028) | 1439 | 145397
POINT(15225775.071204912 4205006.365611101) | 1542 | 152420
POINT(15226012.945533827 4204902.760684182) | 1542 | 152420
POINT(15226580.36520575 4204711.103804201) | 1542 | 152420
POINT(15226487.838102747 4204611.561736697) | 1542 | 152420
POINT(15226000.069640627 4204607.961778946) | 1542 | 152420
(10 rows)
うまく動いていそうなので、osm_buildings テーブルのカラムを更新します。
UPDATE osm_buildings SET mesh1 = meshcode1(geometry), mesh2 = meshcode2(geometry);
テーブルを分散化するために、一旦プライマリキーを削除します。
ALTER TABLE osm_buildings DROP CONSTRAINT osm_buildings_pkey;
mesh1 カラムを使用して分散化の設定をします。
SELECT create_distributed_table('osm_buildings', 'mesh1');
NOTICE: Copying data from local table...
NOTICE: copying the data has completed
DETAIL: The local data in the table is no longer visible, but is still on disk.
HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.osm_buildings$$)
create_distributed_table
プライマリキーを再度設定（ついでに mesh1 と mesh2 も追加）しておきます。
ALTER TABLE osm_buildings ADD PRIMARY KEY (mesh1, mesh2, osm_id, id);
テーブルの分散を解除するときは以下の SQL になります。
SELECT undistribute_table('osm_buildings');
構成毎の処理速度
最小構成からスケールアップしてスケールアウトする３段階で処理速度を確認してみようと思います。
1台の最小構成で処理速度を確認
1台 2vCore, 8GiB RAM、Storage 128 GiB、CoordinatorとWorker構成で処理速度確認
1台の最小構成の Azure Cosmos DB for PostgreSQL で処理速度を確認してみます。
\timing
Timing is on.
SELECT COUNT(DISTINCT mesh1) FROM osm_buildings;
count
-------
47
(1 row)
Time: 6010.955 ms (00:06.011)
SELECT COUNT(DISTINCT mesh2) FROM osm_buildings;
count
-------
1089
(1 row)
Time: 6515.639 ms (00:06.516)
テーブルのデータサイズも確認しておきます。
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-------------------------------+------------+-----------------+----------------------
public | osm_buildings | 7375 MB | 5141 MB | 2234 MB
public | osm_roads | 3189 MB | 2456 MB | 733 MB
public | osm_landusages | 1178 MB | 811 MB | 367 MB
public | osm_waterways | 414 MB | 354 MB | 60 MB
public | osm_roads_gen1 | 220 MB | 150 MB | 70 MB
public | osm_roads_gen0 | 212 MB | 142 MB | 70 MB
public | osm_admin | 159 MB | 26 MB | 133 MB
public | osm_waterways_gen1 | 139 MB | 91 MB | 49 MB
public | osm_waterareas | 135 MB | 99 MB | 36 MB
public | osm_waterways_gen0 | 125 MB | 76 MB | 49 MB
public | osm_landusages_gen1 | 103 MB | 77 MB | 26 MB
public | osm_transport_points | 41 MB | 24 MB | 17 MB
public | osm_barrierways | 30 MB | 21 MB | 9760 kB
public | osm_landusages_gen0 | 27 MB | 23 MB | 3584 kB
public | osm_barrierpoints | 12 MB | 6784 kB | 5552 kB
public | osm_amenities | 9736 kB | 5856 kB | 3880 kB
public | osm_housenumbers | 9144 kB | 5760 kB | 3384 kB
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_waterareas_gen1 | 6160 kB | 5216 kB | 944 kB
public | osm_transport_areas | 4384 kB | 3392 kB | 992 kB
public | osm_places | 1968 kB | 1152 kB | 816 kB
public | osm_aeroways | 1816 kB | 1344 kB | 472 kB
public | osm_waterareas_gen0 | 1488 kB | 1280 kB | 208 kB
public | osm_housenumbers_interpolated | 40 kB | 8192 bytes | 32 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(32 rows)
1台でちょっとスケールアップした構成で処理速度を確認
1台 4vCore, 16GiB RAM、Storage 128 GiB、CoordinatorとWorker構成で処理速度確認します。
1台の最小構成の Azure Cosmos DB for PostgreSQL を、Settings の Scale から 1台 4vCore, 32GiB RAM、Storage 512 GiB、Coordinator＋Worker構成にスケールアップしています。
同じように処理速度を確認します。
\timing
Timing is on.
SELECT COUNT(DISTINCT mesh1) FROM osm_buildings;
count
-------
47
(1 row)
Time: 4011.069 ms (00:04.011)
SELECT COUNT(DISTINCT mesh2) FROM osm_buildings;
count
-------
1089
(1 row)
Time: 4195.404 ms (00:04.195)
3台でスケールアウトした構成で処理速度を確認
さいごに、2台 4vCore, 32GiB RAM、Storage 512 GiB、Worker x 2 構成、1台 4vCore, 16GiB RAM、Storage 512 GiB、Coordinator x 1 で処理速度確認します。
分散ノードを確認します。
SELECT * FROM pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+----------------------------------------------------------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
1 | 0 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com | 5432 | default | t | t | primary | default | t | t
2 | 1 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com | 5432 | default | t | t | primary | default | t | t
3 | 2 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com | 5432 | default | t | t | primary | default | t | t
(3 rows)
分散テーブルの確認
SELECT logicalrelid::regclass AS table_name
FROM pg_dist_partition;
table_name
---------------
osm_buildings
(1 row)
リバランス前（スケールを変更した直後）
スケールを変更した直後で処理時間を計測してみます。
\timing
Timing is on.
SELECT COUNT(DISTINCT mesh1) FROM osm_buildings;
count
-------
47
(1 row)
Time: 4011.069 ms (00:04.011)
SELECT COUNT(DISTINCT mesh2) FROM osm_buildings;
count
-------
1089
(1 row)
Time: 4195.404 ms (00:04.195)
コーディネーターのテーブルサイズを確認します。
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-------------------------------+------------+-----------------+----------------------
public | osm_buildings | 7375 MB | 5141 MB | 2234 MB
public | osm_roads | 3189 MB | 2456 MB | 733 MB
public | osm_landusages | 1178 MB | 811 MB | 367 MB
public | osm_waterways | 414 MB | 353 MB | 60 MB
public | osm_roads_gen1 | 220 MB | 150 MB | 70 MB
public | osm_roads_gen0 | 212 MB | 142 MB | 70 MB
public | osm_admin | 158 MB | 25 MB | 133 MB
public | osm_waterways_gen1 | 139 MB | 90 MB | 49 MB
public | osm_waterareas | 135 MB | 98 MB | 36 MB
public | osm_waterways_gen0 | 124 MB | 76 MB | 49 MB
public | osm_landusages_gen1 | 103 MB | 77 MB | 26 MB
public | osm_transport_points | 41 MB | 24 MB | 17 MB
public | osm_barrierways | 30 MB | 20 MB | 9784 kB
public | osm_landusages_gen0 | 27 MB | 23 MB | 3608 kB
public | osm_barrierpoints | 12 MB | 6496 kB | 5552 kB
public | osm_amenities | 9736 kB | 5856 kB | 3880 kB
public | osm_housenumbers | 8992 kB | 5608 kB | 3384 kB
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_waterareas_gen1 | 6184 kB | 5216 kB | 968 kB
public | osm_transport_areas | 4408 kB | 3392 kB | 1016 kB
public | osm_places | 1944 kB | 1128 kB | 816 kB
public | osm_aeroways | 1816 kB | 1344 kB | 472 kB
public | osm_waterareas_gen0 | 1512 kB | 1280 kB | 232 kB
public | osm_housenumbers_interpolated | 40 kB | 8192 bytes | 32 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(32 rows)
各ワーカーDBに直接接続して、各ワーカーノードのテーブルを確認します。
まずは、１台目のワーカーノードに接続します。
psql "host=w0-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user
=citus password={your password} sslmode=require"
１台目のワーカーノードの各テーブルのサイズを確認します。
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-----------------+------------+-----------------+----------------------
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_buildings | 24 kB | 0 bytes | 24 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(10 rows)
２台目のワーカーノードに接続
psql "host=w1-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your password} sslmode=require"
各テーブルのサイズを確認
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-----------------+------------+-----------------+----------------------
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_buildings | 24 kB | 0 bytes | 24 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(10 rows)
リバランス後（再分散）
分散データベースを「リバランス（再分散）」するには、Citus の rebalance_table_shards 関数や rebalance_table_shards_in_database 関数を使います。
特定テーブルのシャードをリバランスする
SELECT rebalance_table_shards('osm_buildings');
NOTICE: Moving shard 102065 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
NOTICE: Moving shard 102048 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
NOTICE: Moving shard 102054 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
NOTICE: Moving shard 102058 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
NOTICE: Moving shard 102059 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
NOTICE: Moving shard 102046 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
NOTICE: Moving shard 102050 from private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
rebalance_table_shards
------------------------
また、データベース内のすべてのテーブルをリバランスする場合は、以下のコマンドで実行。クラスタ上のすべての分散テーブルのシャードが再分散されます。
SELECT rebalance_table_shards_in_database();
実行時の注意点
- リバランス中は、対象テーブルへの書き込み/読み込みパフォーマンスが低下する場合があります。
- 大規模データではリバランス完了まで時間がかかる場合があります。
- 必ずコーディネータノードで実行してください。
- 十分なバックアップがある状態での運用をおすすめします。
処理速度を計測してみます。
\timing
Timing is on.
SELECT COUNT(DISTINCT mesh1) FROM osm_buildings;
count
-------
47
(1 row)
Time: 1932.354 ms (00:01.932)
SELECT COUNT(DISTINCT mesh2) FROM osm_buildings;
count
-------
1089
(1 row)
Time: 1760.313 ms (00:01.760)
コーディネータのテーブルサイズを確認しておきます。
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-------------------------------+------------+-----------------+----------------------
public | osm_buildings | 7375 MB | 5141 MB | 2234 MB
public | osm_roads | 3189 MB | 2456 MB | 733 MB
public | osm_landusages | 1178 MB | 811 MB | 367 MB
public | osm_waterways | 414 MB | 353 MB | 60 MB
public | osm_roads_gen1 | 220 MB | 150 MB | 70 MB
public | osm_roads_gen0 | 212 MB | 142 MB | 70 MB
public | osm_admin | 158 MB | 25 MB | 133 MB
public | osm_waterways_gen1 | 139 MB | 90 MB | 49 MB
public | osm_waterareas | 135 MB | 98 MB | 36 MB
public | osm_waterways_gen0 | 124 MB | 76 MB | 49 MB
public | osm_landusages_gen1 | 103 MB | 77 MB | 26 MB
public | osm_transport_points | 41 MB | 24 MB | 17 MB
public | osm_barrierways | 30 MB | 20 MB | 9784 kB
public | osm_landusages_gen0 | 27 MB | 23 MB | 3608 kB
public | osm_barrierpoints | 12 MB | 6496 kB | 5552 kB
public | osm_amenities | 9736 kB | 5856 kB | 3880 kB
public | osm_housenumbers | 8992 kB | 5608 kB | 3384 kB
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_waterareas_gen1 | 6184 kB | 5216 kB | 968 kB
public | osm_transport_areas | 4408 kB | 3392 kB | 1016 kB
public | osm_places | 1944 kB | 1128 kB | 816 kB
public | osm_aeroways | 1816 kB | 1344 kB | 472 kB
public | osm_waterareas_gen0 | 1512 kB | 1280 kB | 232 kB
public | osm_housenumbers_interpolated | 40 kB | 8192 bytes | 32 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(32 rows)
シャードキーのハッシュ値が 32に分割（citus.shard_count）されていることを確認。
SELECT * FROM pg_dist_shard;
logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue
---------------+---------+--------------+---------------+---------------
osm_buildings | 102040 | t | -2147483648 | -2013265921
osm_buildings | 102041 | t | -2013265920 | -1879048193
osm_buildings | 102042 | t | -1879048192 | -1744830465
osm_buildings | 102043 | t | -1744830464 | -1610612737
osm_buildings | 102044 | t | -1610612736 | -1476395009
osm_buildings | 102045 | t | -1476395008 | -1342177281
osm_buildings | 102046 | t | -1342177280 | -1207959553
osm_buildings | 102047 | t | -1207959552 | -1073741825
osm_buildings | 102048 | t | -1073741824 | -939524097
osm_buildings | 102049 | t | -939524096 | -805306369
osm_buildings | 102050 | t | -805306368 | -671088641
osm_buildings | 102051 | t | -671088640 | -536870913
osm_buildings | 102052 | t | -536870912 | -402653185
osm_buildings | 102053 | t | -402653184 | -268435457
osm_buildings | 102054 | t | -268435456 | -134217729
osm_buildings | 102055 | t | -134217728 | -1
osm_buildings | 102056 | t | 0 | 134217727
osm_buildings | 102057 | t | 134217728 | 268435455
osm_buildings | 102058 | t | 268435456 | 402653183
osm_buildings | 102059 | t | 402653184 | 536870911
osm_buildings | 102060 | t | 536870912 | 671088639
osm_buildings | 102061 | t | 671088640 | 805306367
osm_buildings | 102062 | t | 805306368 | 939524095
osm_buildings | 102063 | t | 939524096 | 1073741823
osm_buildings | 102064 | t | 1073741824 | 1207959551
osm_buildings | 102065 | t | 1207959552 | 1342177279
osm_buildings | 102066 | t | 1342177280 | 1476395007
osm_buildings | 102067 | t | 1476395008 | 1610612735
osm_buildings | 102068 | t | 1610612736 | 1744830463
osm_buildings | 102069 | t | 1744830464 | 1879048191
osm_buildings | 102070 | t | 1879048192 | 2013265919
osm_buildings | 102071 | t | 2013265920 | 2147483647
(32 rows)
シャードが、各Workerノードに割り当てられていることを確認。
SELECT * FROM citus_shards;
table_name | shardid | shard_name | citus_table_type | colocation_id | nodename | nodeport | shard_size
---------------+---------+----------------------+------------------+---------------+----------------------------------------------------------------+----------+------------
osm_buildings | 102040 | osm_buildings_102040 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 50659328
osm_buildings | 102041 | osm_buildings_102041 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 15835136
osm_buildings | 102042 | osm_buildings_102042 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 24576
osm_buildings | 102043 | osm_buildings_102043 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 24576
osm_buildings | 102044 | osm_buildings_102044 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 115384320
osm_buildings | 102045 | osm_buildings_102045 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 24576
osm_buildings | 102046 | osm_buildings_102046 | distributed | 5 | private-w0-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 458407936
osm_buildings | 102047 | osm_buildings_102047 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 2572288
osm_buildings | 102048 | osm_buildings_102048 | distributed | 5 | private-w1-kentapgsql.xxx.postgres.
cosmos.azure.com | 5432 | 1751113728
osm_buildings | 102049 | osm_buildings_102049 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 14352384
osm_buildings | 102050 | osm_buildings_102050 | distributed | 5 | private-w1-kentapgsql.xxx.postgres.
cosmos.azure.com | 5432 | 446210048
osm_buildings | 102051 | osm_buildings_102051 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 24576
osm_buildings | 102052 | osm_buildings_102052 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 123256832
osm_buildings | 102053 | osm_buildings_102053 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 132177920
osm_buildings | 102054 | osm_buildings_102054 | distributed | 5 | private-w1-kentapgsql.xxx.postgres.
cosmos.azure.com | 5432 | 817176576
osm_buildings | 102055 | osm_buildings_102055 | distributed | 5 | private-c-kentapgsql.xxx.postgres.cosmos.azure.com | 5432 | 589824
osm_buildings | 102056 | osm_buildings_102056 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 24576
osm_buildings | 102057 | osm_buildings_102057 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 252690432
osm_buildings | 102058 | osm_buildings_102058 | distributed | 5 | private-w0-kentapgsql.xxx.postgres.
cosmos.azure.com | 5432 | 653393920
osm_buildings | 102059 | osm_buildings_102059 | distributed | 5 | private-w1-kentapgsql.xxx.postgres.
cosmos.azure.com | 5432 | 503881728
osm_buildings | 102060 | osm_buildings_102060 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 165601280
osm_buildings | 102061 | osm_buildings_102061 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 196608
osm_buildings | 102062 | osm_buildings_102062 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 98304
osm_buildings | 102063 | osm_buildings_102063 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 24576
osm_buildings | 102064 | osm_buildings_102064 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 170229760
osm_buildings | 102065 | osm_buildings_102065 | distributed | 5 | private-w0-kentapgsql.xxx.postgres.
cosmos.azure.com | 5432 | 2240716800
osm_buildings | 102066 | osm_buildings_102066 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 23207936
osm_buildings | 102067 | osm_buildings_102067 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 5464064
osm_buildings | 102068 | osm_buildings_102068 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 122445824
osm_buildings | 102069 | osm_buildings_102069 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 8380416
osm_buildings | 102070 | osm_buildings_102070 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 40960
osm_buildings | 102071 | osm_buildings_102071 | distributed | 5 | private-c-kentapgsql.xxx.postgres.c
osmos.azure.com | 5432 | 24576
(32 rows)
１台目のワーカーのテーブルサイズを確認します。
psql "host=w0-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your password} sslmode=require"
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-----------------+------------+-----------------+----------------------
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_buildings | 24 kB | 0 bytes | 24 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(10 rows)
２台目のワーカーのテーブルサイズを確認。
psql "host=w1-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your password} sslmode=require"
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
schemaname | table_name | total_size | table_only_size | index_and_toast_size
-------------------+-----------------+------------+-----------------+----------------------
public | spatial_ref_sys | 7144 kB | 6896 kB | 248 kB
public | osm_buildings | 24 kB | 0 bytes | 24 kB
cron | job | 24 kB | 0 bytes | 24 kB
partman | part_config | 24 kB | 0 bytes | 24 kB
cron | job_run_details | 16 kB | 0 bytes | 16 kB
columnar_internal | stripe | 16 kB | 0 bytes | 16 kB
partman | part_config_sub | 16 kB | 0 bytes | 16 kB
columnar_internal | chunk | 16 kB | 0 bytes | 16 kB
columnar_internal | options | 8192 bytes | 0 bytes | 8192 bytes
columnar_internal | chunk_group | 8192 bytes | 0 bytes | 8192 bytes
(10 rows)
まとめ
今回は、１台（コーディネータ + ワーカー）と３台（コーディネータ x 1、ワーカー x 2）構成で確認しましたが、4.011秒かかる処理が 1.932秒で処理されており、分散テーブルの威力を確認することができました。地図データを使う場合は、用途にもよりますが、行政区域単位で分散したりすると効率が良いかもしれません。
また、21台（1台当たり 32767 GiB）で１つのクラスターを作成したり、リードレプリカを他のリージョンに置くなど、PostgreSQLをハイパースケールな環境で使うことができそうです。
（以下は、設定だけしてみた画面です。料金が怖くてSaveボタンは押せませんでした。）
カラムナーストレージを使用すると、データを圧縮して効率よくストレージを使うことができるようですので、時間があるときに確認してみたいと思います。
（7/23追記、確認しましたー）
参考：Cosmos DB for PostgreSQLのパーティションとカラムナーストレージ