LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kekekekenta(Kentaro Aoki)

Azure Cosmos DB for PostgreSQL でカラムナーと分散テーブルを確認してみた

Posted at

前回は、Azure Cosmos DB for PostgreSQL の分散テーブルを確認してみました。今回は、カラムナーにしたテーブルを確認していきます。

今回の構成は前回と同じ構成です。

Coordinator node: 4 vCores / 16 GiB RAM, 512 GiB storage
Worker nodes: 2 nodes, 4 vCores / 32 GiB RAM, 512 GiB storage

image.png

カラムナーのテーブルを作成

前回作成したテーブルと同じカラムでカラムナーのテーブルを作成する。

CREATE TABLE IF NOT EXISTS public.osm_buildings_columnar
(
    id bigint NOT NULL DEFAULT nextval('osm_buildings_id_seq'::regclass),
    osm_id bigint NOT NULL,
    name character varying COLLATE pg_catalog."default",
    type character varying COLLATE pg_catalog."default",
    geometry geometry(Geometry,3857),
    mesh1 character varying(4) COLLATE pg_catalog."default" NOT NULL,
    mesh2 character varying(6) COLLATE pg_catalog."default" NOT NULL
) USING COLUMNAR;

前回作成したテーブルのデータを新しく作成したカラムナーのテーブルにコピーする。

INSERT INTO osm_buildings_columnar SELECT * FROM osm_buildings;

この段階でテーブルのサイズを確認する。

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_buildings_columnar        | 2505 MB    | 2505 MB         | 0 bytes
 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
 columnar_internal | chunk                         | 3632 kB    | 2872 kB         | 760 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
 columnar_internal | chunk_group                   | 304 kB     | 152 kB          | 152 kB
 columnar_internal | stripe                        | 72 kB      | 16 kB           | 56 kB
 public            | osm_housenumbers_interpolated | 40 kB      | 8192 bytes      | 32 kB
 columnar_internal | options                       | 24 kB      | 8192 bytes      | 16 kB
 partman           | part_config                   | 24 kB      | 0 bytes         | 24 kB
 cron              | job                           | 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
(33 rows)

カラムナーのテーブルを分散テーブルに設定

新しく作成したカラムナーのテーブルに、分散テーブルを設定する。

SELECT create_distributed_table('osm_buildings_columnar', '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_columnar$$)
 create_distributed_table 
--------------------------
 
(1 row)

分散テーブルとして設定されているか確認する。

SELECT logicalrelid::regclass AS table_name
FROM pg_dist_partition;
       table_name       
------------------------
 osm_buildings
 osm_buildings_columnar
(2 rows)

新しく作成したカラムナーのテーブルに、プライマリキーを設定する。

ALTER TABLE osm_buildings_columnar ADD PRIMARY KEY (mesh1, mesh2, osm_id, id);

プライマリキーを設定した段階でテーブルのサイズを確認する。

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_buildings_columnar        | 3721 MB    | 2505 MB         | 1216 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
 columnar_internal | chunk                         | 4272 kB    | 3352 kB         | 920 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
 columnar_internal | chunk_group                   | 400 kB     | 232 kB          | 168 kB
 columnar_internal | stripe                        | 80 kB      | 24 kB           | 56 kB
 public            | osm_housenumbers_interpolated | 40 kB      | 8192 bytes      | 32 kB
 columnar_internal | options                       | 24 kB      | 8192 bytes      | 16 kB
 partman           | part_config                   | 24 kB      | 0 bytes         | 24 kB
 cron              | job                           | 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
(33 rows)

シャードキーのハッシュ値をされていることを確認。

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
 osm_buildings_columnar |  102073 | t            | -2147483648   | -2013265921
 osm_buildings_columnar |  102074 | t            | -2013265920   | -1879048193
 osm_buildings_columnar |  102075 | t            | -1879048192   | -1744830465
 osm_buildings_columnar |  102076 | t            | -1744830464   | -1610612737
 osm_buildings_columnar |  102077 | t            | -1610612736   | -1476395009
 osm_buildings_columnar |  102078 | t            | -1476395008   | -1342177281
 osm_buildings_columnar |  102079 | t            | -1342177280   | -1207959553
 osm_buildings_columnar |  102080 | t            | -1207959552   | -1073741825
 osm_buildings_columnar |  102081 | t            | -1073741824   | -939524097
 osm_buildings_columnar |  102082 | t            | -939524096    | -805306369
 osm_buildings_columnar |  102083 | t            | -805306368    | -671088641
 osm_buildings_columnar |  102084 | t            | -671088640    | -536870913
 osm_buildings_columnar |  102085 | t            | -536870912    | -402653185
 osm_buildings_columnar |  102086 | t            | -402653184    | -268435457
 osm_buildings_columnar |  102087 | t            | -268435456    | -134217729
 osm_buildings_columnar |  102088 | t            | -134217728    | -1
 osm_buildings_columnar |  102089 | t            | 0             | 134217727
 osm_buildings_columnar |  102090 | t            | 134217728     | 268435455
 osm_buildings_columnar |  102091 | t            | 268435456     | 402653183
 osm_buildings_columnar |  102092 | t            | 402653184     | 536870911
 osm_buildings_columnar |  102093 | t            | 536870912     | 671088639
 osm_buildings_columnar |  102094 | t            | 671088640     | 805306367
 osm_buildings_columnar |  102095 | t            | 805306368     | 939524095
 osm_buildings_columnar |  102096 | t            | 939524096     | 1073741823
 osm_buildings_columnar |  102097 | t            | 1073741824    | 1207959551
 osm_buildings_columnar |  102098 | t            | 1207959552    | 1342177279
 osm_buildings_columnar |  102099 | t            | 1342177280    | 1476395007
 osm_buildings_columnar |  102100 | t            | 1476395008    | 1610612735
 osm_buildings_columnar |  102101 | t            | 1610612736    | 1744830463
 osm_buildings_columnar |  102102 | t            | 1744830464    | 1879048191
 osm_buildings_columnar |  102103 | t            | 1879048192    | 2013265919
 osm_buildings_columnar |  102104 | t            | 2013265920    | 2147483647
(64 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.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   50659328
 osm_buildings          |  102041 | osm_buildings_102041          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   15835136
 osm_buildings          |  102042 | osm_buildings_102042          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings          |  102043 | osm_buildings_102043          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings          |  102044 | osm_buildings_102044          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  115384320
 osm_buildings          |  102045 | osm_buildings_102045          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings          |  102046 | osm_buildings_102046          | distributed      |             5 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  458407936
 osm_buildings          |  102047 | osm_buildings_102047          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    2572288
 osm_buildings          |  102048 | osm_buildings_102048          | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 | 1751113728
 osm_buildings          |  102049 | osm_buildings_102049          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   14352384
 osm_buildings          |  102050 | osm_buildings_102050          | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  446210048
 osm_buildings          |  102051 | osm_buildings_102051          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings          |  102052 | osm_buildings_102052          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  123256832
 osm_buildings          |  102053 | osm_buildings_102053          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  132177920
 osm_buildings          |  102054 | osm_buildings_102054          | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  817176576
 osm_buildings          |  102055 | osm_buildings_102055          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |     589824
 osm_buildings          |  102056 | osm_buildings_102056          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings          |  102057 | osm_buildings_102057          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  252690432
 osm_buildings          |  102058 | osm_buildings_102058          | distributed      |             5 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  653393920
 osm_buildings          |  102059 | osm_buildings_102059          | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  503881728
 osm_buildings          |  102060 | osm_buildings_102060          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  165601280
 osm_buildings          |  102061 | osm_buildings_102061          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |     196608
 osm_buildings          |  102062 | osm_buildings_102062          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      98304
 osm_buildings          |  102063 | osm_buildings_102063          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings          |  102064 | osm_buildings_102064          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  170229760
 osm_buildings          |  102065 | osm_buildings_102065          | distributed      |             5 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 | 2240716800
 osm_buildings          |  102066 | osm_buildings_102066          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   23207936
 osm_buildings          |  102067 | osm_buildings_102067          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    5464064
 osm_buildings          |  102068 | osm_buildings_102068          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  122445824
 osm_buildings          |  102069 | osm_buildings_102069          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    8380416
 osm_buildings          |  102070 | osm_buildings_102070          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      40960
 osm_buildings          |  102071 | osm_buildings_102071          | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102073 | osm_buildings_columnar_102073 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   25608192
 osm_buildings_columnar |  102074 | osm_buildings_columnar_102074 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    7667712
 osm_buildings_columnar |  102075 | osm_buildings_columnar_102075 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102076 | osm_buildings_columnar_102076 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102077 | osm_buildings_columnar_102077 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   57892864
 osm_buildings_columnar |  102078 | osm_buildings_columnar_102078 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102079 | osm_buildings_columnar_102079 | distributed      |             5 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  217104384
 osm_buildings_columnar |  102080 | osm_buildings_columnar_102080 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    1245184
 osm_buildings_columnar |  102081 | osm_buildings_columnar_102081 | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  836403200
 osm_buildings_columnar |  102082 | osm_buildings_columnar_102082 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    7331840
 osm_buildings_columnar |  102083 | osm_buildings_columnar_102083 | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  213966848
 osm_buildings_columnar |  102084 | osm_buildings_columnar_102084 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102085 | osm_buildings_columnar_102085 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   61038592
 osm_buildings_columnar |  102086 | osm_buildings_columnar_102086 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   66412544
 osm_buildings_columnar |  102087 | osm_buildings_columnar_102087 | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  393256960
 osm_buildings_columnar |  102088 | osm_buildings_columnar_102088 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |     270336
 osm_buildings_columnar |  102089 | osm_buildings_columnar_102089 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102090 | osm_buildings_columnar_102090 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |  125411328
 osm_buildings_columnar |  102091 | osm_buildings_columnar_102091 | distributed      |             5 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  302538752
 osm_buildings_columnar |  102092 | osm_buildings_columnar_102092 | distributed      |             5 | private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 |  239050752
 osm_buildings_columnar |  102093 | osm_buildings_columnar_102093 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   80510976
 osm_buildings_columnar |  102094 | osm_buildings_columnar_102094 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |     106496
 osm_buildings_columnar |  102095 | osm_buildings_columnar_102095 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      57344
 osm_buildings_columnar |  102096 | osm_buildings_columnar_102096 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
 osm_buildings_columnar |  102097 | osm_buildings_columnar_102097 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   85106688
 osm_buildings_columnar |  102098 | osm_buildings_columnar_102098 | distributed      |             5 | private-w0-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com |     5432 | 1058619392
 osm_buildings_columnar |  102099 | osm_buildings_columnar_102099 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   11239424
 osm_buildings_columnar |  102100 | osm_buildings_columnar_102100 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    2629632
 osm_buildings_columnar |  102101 | osm_buildings_columnar_102101 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |   60383232
 osm_buildings_columnar |  102102 | osm_buildings_columnar_102102 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |    4194304
 osm_buildings_columnar |  102103 | osm_buildings_columnar_102103 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      40960
 osm_buildings_columnar |  102104 | osm_buildings_columnar_102104 | distributed      |             5 | private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com  |     5432 |      24576
(64 rows)

テーブルのシャードをリバランスする

SELECT rebalance_table_shards('osm_buildings_columnar');

NOTICE:  Ignoring move of shard 102092 from private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432, because the move only brings a small improvement relative to the shard its size
DETAIL:  The balance improvement of 0.413528 is lower than the improvement_threshold of 0.5
NOTICE:  Ignored 1 moves, all of which are shown in notices above
HINT:  If you do want these moves to happen, try changing improvement_threshold to a lower value than what it is now (0.5).
NOTICE:  Moving shard 102083 from private-w1-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 to private-c-kentapgsql.yjjdautcvi53fg.postgres.cosmos.azure.com:5432 ...
 rebalance_table_shards 
------------------------
  
(1 row)

カラムナーで分散テーブルの処理速度を確認

処理速度を確認してみる。

\timing

Timing is on.

SELECT COUNT(DISTINCT mesh1) FROM osm_buildings_columnar;
 count 
-------
    47
(1 row)

Time: 1845.034 ms (00:01.845)

SELECT COUNT(DISTINCT mesh2) FROM osm_buildings_columnar;
 count 
-------
  1089
(1 row)

Time: 1412.126 ms (00:01.412)

各ワーカーのテーブルサイズを確認

image.png

1台目のワーカーノードのテーブルサイズを確認してみる。

psql "host=w0-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your pasword} sslmode=require"

psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

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
 columnar_internal | chunk                  | 1568 kB    | 1208 kB         | 360 kB
 columnar_internal | chunk_group            | 168 kB     | 80 kB           | 88 kB
 columnar_internal | stripe                 | 40 kB      | 8192 bytes      | 32 kB
 columnar_internal | options                | 24 kB      | 8192 bytes      | 16 kB
 public            | osm_buildings_columnar | 24 kB      | 16 kB           | 8192 bytes
 cron              | job                    | 24 kB      | 0 bytes         | 24 kB
 partman           | part_config            | 24 kB      | 0 bytes         | 24 kB
 public            | osm_buildings          | 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
(11 rows)

2台目のワーカーノードのテーブルサイズを確認してみる。

psql "host=w1-kentapgsql.xxx.postgres.cosmos.azure.com port=5432 dbname=gis user=citus password={your pasword} sslmode=require"

psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

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
 columnar_internal | chunk                  | 1672 kB    | 1256 kB         | 416 kB
 columnar_internal | chunk_group            | 200 kB     | 96 kB           | 104 kB
 columnar_internal | stripe                 | 40 kB      | 8192 bytes      | 32 kB
 columnar_internal | options                | 24 kB      | 8192 bytes      | 16 kB
 public            | osm_buildings_columnar | 24 kB      | 16 kB           | 8192 bytes
 cron              | job                    | 24 kB      | 0 bytes         | 24 kB
 partman           | part_config            | 24 kB      | 0 bytes         | 24 kB
 public            | osm_buildings          | 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
(11 rows)

さいごに

前回のカラムナーを使用していないテーブルと比較すると、テーブルサイズが小さくなったことに加え、処理速度も若干速くなった気がしますね。いやーすごい。これがスケールアウトできると思うとわくわくしますね！

schemaname table_name total_size table_only_size index_and_toast_size
public osm_buildings 7375 MB 5141 MB 2234 MB
public osm_buildings_columnar 3721 MB 2505 MB 1216 MB
schemaname table_name sec
public osm_buildings 1.932
public osm_buildings_columnar 1.845

参考：Cosmos DB for PostgreSQLのパーティションとカラムナーストレージ

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?