disk-usage-while-importing-an-osm-rendering-database

Beginning: I intended to consist of the upgrading component in this article, however it obtained as well lengthy currently, so I’ll divide it in 2. I.
will certainly upgrade this article when the 2nd appears.

TL; DR variation: import appears to utilize a top of 15x room contrasted to the osm.pbf resource data, and also a last 10x. If.
you’re limited on room, attempt -- number-processes 1, however it will certainly be method slower. You can likewise conserve some 4x dimension if you have.
4x RAM (by not utilizing -- flat-nodes).

I made a decision to attempt and also keep a day-to-day OpenStreetMap making data source for Europe. The strategy is to, at some time, have actually a.
making web server in your home, rather than depending of periodic makings. For this I will certainly utilize my old laptop computer, which has actually a.
1TB SSD and also just 8GiB of RAM, which I prepare to update to 16 at some time. The makers does a couple of various other points (webmail,.
back-ups, cloud with just 2 individuals, and also various other things), however the use is truly reduced, so I assume it’ll be great.

Today I tidied up disk room and also I prepared the primary import. As a result of the room restrictions I needed to know exactly how the.
disk use advances throughout the import. Specifically, I intended to see if there might be a index development order which.
might be helpful for individuals with minimal room for these procedures. Allow’s examine the moments initially[2]:

$ osm2pgsql-- verbose-- data source gis-- cache 0-- number-processes 4-- slim-- flat-nodes $( pwd)/ nodes.cache-- hstore-- multi-geometry.
-- design $osm_carto/ openstreetmap-carto. design-- tag-transform-script $osm_carto/ openstreetmap-carto. lua europe-latest. osm.pbf.

2023-08-18 15:46:20 osm2pgsql variation 1.8.0.
2023-08-18 15:46:20 [0] Data source variation: 15.3 (Debian 15.3-0+ deb12u1).
2023-08-18 15:46:20 [0] PostGIS variation: 3.3.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_nodes'.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_ways'.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_rels'.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_point'.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_line'.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_polygon'.
2023-08-18 15:46:20 [0] Establishing table 'planet_osm_roads'.

2023-08-19 20:49:52 [0] Checking out input submits performed in 104612s (29h 3m 32s).
2023-08-19 20:49:52 [0] Refined 3212638859 nodes in 3676s (1h 1m 16s) - 874k/s.
2023-08-19 20:49:52 [0] Refined 390010251 methods 70030s (19h 27m 10s) - 6k/s.
2023-08-19 20:49:52 [0] Refined 6848902 relationships in 30906s (8h 35m sixes) - 222/s.
2023-08-19 20:49:52 [0] General memory use: top= 85815MByte current= 85654MByte.

Currently I ask yourself why I have -- cache 0 Unfortunately I really did not leave any kind of remarks, so I’ll need to examine my dedicates (I automated.
all this with a manuscript:-RRB-. Unfortunately once again, this insinuated in a dedicate regarding another thing, so any kind of referrals are.
shed:( Below are the table dimensions[1]:

 Call|Kind|Accessibility approach|Dimension.
--------------------+ -------+ ---------------+ ----------------.
planet_osm_ways|table|lot|95_899_467_776.
planet_osm_polygon|table|lot|92_916_039_680 *.
planet_osm_line|table|lot|43_485_192_192 *.
planet_osm_point|table|lot|16_451_903_488 *.
planet_osm_roads|table|lot|6_196_699_136 *.
planet_osm_rels|table|lot|3_488_505_856.
spatial_ref_sys|table|lot|7_102_464.
geography_columns|sight|| 0.
geometry_columns|sight|| 0.

Those significant with * are utilized for making, the remainder are maintained for upgrade objectives. Allow’s see the disk use [click to
get the full image]:

The chart reveals the price at which disk is utilized throughout the import of information. We can just see some room being released around.
1/10th in, and also later on at around 2/3rds, however absolutely nothing significant.

After That osm2pgsql does a great deal of things, consisting of clustering, indexing and also studying:

 2023-08-19 20:49:52 [1] Clustering table 'planet_osm_polygon' by geometry ...
2023-08-19 20:49:52 [2] Clustering table 'planet_osm_line' by geometry ...
2023-08-19 20:49:52 [3] Clustering table 'planet_osm_roads' by geometry ...
2023-08-19 20:49:52 [4] Clustering table 'planet_osm_point' by geometry ...
2023-08-19 20:49:52 [1] Utilizing indigenous order for clustering table 'planet_osm_polygon'.
2023-08-19 20:49:52 [2] Utilizing indigenous order for clustering table 'planet_osm_line'.
2023-08-19 20:49:52 [3] Utilizing indigenous order for clustering table 'planet_osm_roads'.
2023-08-19 20:49:52 [4] Utilizing indigenous order for clustering table 'planet_osm_point'.

2023-08-19 22:35:50 [3] Producing geometry index on table 'planet_osm_roads' ...
2023-08-19 22:50:47 [3] Producing osm_id index on table 'planet_osm_roads' ...
2023-08-19 22:55:52 [3] Examining table 'planet_osm_roads' ...
2023-08-19 22:57:47 [3] Done job [Analyzing table 'planet_osm_roads'] in 7674389ms.
2023-08-19 22:57:47 [3] Beginning job ...
2023-08-19 22:57:47 [3] Done job in 1ms.
2023-08-19 22:57:47 [3] Beginning job ...

2023-08-19 22:57:47 [0] Done postprocessing on table 'planet_osm_nodes' in 0s.

2023-08-19 22:57:47 [3] Structure index on table 'planet_osm_ways'.

2023-08-19 23:32:06 [4] Producing geometry index on table 'planet_osm_point' ...
2023-08-20 00:13:30 [4] Producing osm_id index on table 'planet_osm_point' ...
2023-08-20 00:20:35 [4] Examining table 'planet_osm_point' ...
2023-08-20 00:20:40 [4] Done job in 12647156ms.
2023-08-20 00:20:40 [4] Beginning job ...

2023-08-20 00:20:40 [4] Structure index on table 'planet_osm_rels'.
2023-08-20 02:03:11 [4] Done job in 6151838ms.

2023-08-20 03:17:24 [2] Producing geometry index on table 'planet_osm_line' ...
2023-08-20 03:54:40 [2] Producing osm_id index on table 'planet_osm_line' ...
2023-08-20 04:02:57 [2] Examining table 'planet_osm_line' ...
2023-08-20 04:03:01 [2] Done job in 25988218ms.

2023-08-20 05:26:21 [1] Producing geometry index on table 'planet_osm_polygon' ...
2023-08-20 06:17:31 [1] Producing osm_id index on table 'planet_osm_polygon' ...
2023-08-20 06:30:46 [1] Examining table 'planet_osm_polygon' ...
2023-08-20 06:30:47 [1] Done job in 34854542ms.

2023-08-20 10:48:18 [3] Done job in 42630605ms.

2023-08-20 10:48:18 [0] Done postprocessing on table 'planet_osm_ways' in 42630s (11h 50m 30s).
2023-08-20 10:48:18 [0] Done postprocessing on table 'planet_osm_rels' in 6151s (1h 42m 31s).

2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_point' performed in 12647s (3h 30m 47s).
2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_line' performed in 25988s (7h 13m eights).
2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_polygon' performed in 34854s (9h 40m 54s).
2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_roads' performed in 7674s (2h 7m 54s).

2023-08-20 10:48:18 [0] General memory use: top= 85815MByte current= 727MByte.
2023-08-20 10:48:18 [0] osm2pgsql took 154917s (43h 1m 57s) in general.

I attempted to understand this component. We have 4 employees 1-4 plus one primary string 0. On the 19th, ~ 20:50, all 4 employees.
begin dealing with polygon, line, roadways and also factor specifically. 2h07m54s later on employee 3 coatings clustering.
roadways, which is the moment reported at the end of the run. However quickly begins producing indexes for it, which take ~ 15m and also.
~ 5m each. It after that begins studying roadways, which I think it’s the job that ends up at 22:57 (~ 2m runtime)?

After that 2 confidential jobs, one coatings in 1ms, and also the 2nd remains …? And also quickly begins indexing means
On the other hand, nodes, which had not been reported as being refined by any kind of employee, likewise ends up. Perhaps it’s the primary loophole.
which does it? And also if so, why did it end up just currently, after just 0s? All this takes place on the very same 2nd, 10:48:18

Still on the 19th, at ~ 23:32, employee 4 begins producing indexes for factor This is ~ 3h30m after it began.
clustering it, which is likewise what is reported at the end. Once more, 2 indexes and also one evaluation for this table, after that an.
confidential job … which I think coatings quickly? Due to the fact that on the very same 2nd it develops an index for it, which looks.
like a pattern (W3 did the very same, bear in mind?). It ends up in ~ 1h40m, so I think W3’s “Done job” at the end is the index.
it was producing given that the 19th?

Provided all that, I included some added notes that I assume are the appropriate ones to understand all that. I wish I can utilize.
several of my plenty leisure to repair it, see this concern:

 2023-08-19 20:49:52 [1] Clustering table 'planet_osm_polygon' by geometry ...
2023-08-19 20:49:52 [2] Clustering table 'planet_osm_line' by geometry ...
2023-08-19 20:49:52 [3] Clustering table 'planet_osm_roads' by geometry ...
2023-08-19 20:49:52 [4] Clustering table 'planet_osm_point' by geometry ...
2023-08-19 20:49:52 [1] Utilizing indigenous order for clustering table 'planet_osm_polygon'.
2023-08-19 20:49:52 [2] Utilizing indigenous order for clustering table 'planet_osm_line'.
2023-08-19 20:49:52 [3] Utilizing indigenous order for clustering table 'planet_osm_roads'.
2023-08-19 20:49:52 [4] Utilizing indigenous order for clustering table 'planet_osm_point'.

2023-08-19 22:35:50 [3] Producing geometry index on table 'planet_osm_roads' ...
2023-08-19 22:50:47 [3] Producing osm_id index on table 'planet_osm_roads' ...
2023-08-19 22:55:52 [3] Examining table 'planet_osm_roads' ...
2023-08-19 22:57:47 [3] Done job [Analyzing table 'planet_osm_roads'] in 7674389ms.
2023-08-19 22:57:47 [3] Beginning job [which one?] ...
2023-08-19 22:57:47 [3] Done job in 1ms.
2023-08-19 22:57:47 [3] Beginning job [which one?] ...

2023-08-19 22:57:47 [0] Done postprocessing on table 'planet_osm_nodes' in 0s.

2023-08-19 22:57:47 [3] Structure index on table 'planet_osm_ways'.

2023-08-19 23:32:06 [4] Producing geometry index on table 'planet_osm_point' ...
2023-08-20 00:13:30 [4] Producing osm_id index on table 'planet_osm_point' ...
2023-08-20 00:20:35 [4] Examining table 'planet_osm_point' ...
2023-08-20 00:20:40 [4] Done job [Analyzing table 'planet_osm_point'] in 12647156ms.
2023-08-20 00:20:40 [4] Beginning job ...

2023-08-20 00:20:40 [4] Structure index on table 'planet_osm_rels'.
2023-08-20 02:03:11 [4] Done job [Building index on table 'planet_osm_rels'] in 6151838ms.

2023-08-20 03:17:24 [2] Producing geometry index on table 'planet_osm_line' ...
2023-08-20 03:54:40 [2] Producing osm_id index on table 'planet_osm_line' ...
2023-08-20 04:02:57 [2] Examining table 'planet_osm_line' ...
2023-08-20 04:03:01 [2] Done job [Analyzing table 'planet_osm_line'] in 25988218ms.

2023-08-20 05:26:21 [1] Producing geometry index on table 'planet_osm_polygon' ...
2023-08-20 06:17:31 [1] Producing osm_id index on table 'planet_osm_polygon' ...
2023-08-20 06:30:46 [1] Examining table 'planet_osm_polygon' ...
2023-08-20 06:30:47 [1] Done job [Analyzing table 'planet_osm_polygon'] in 34854542ms.

2023-08-20 10:48:18 [3] Done job [Building index on table 'planet_osm_ways'] in 42630605ms.

2023-08-20 10:48:18 [0] Done postprocessing on table 'planet_osm_ways' in 42630s (11h 50m 30s).
2023-08-20 10:48:18 [0] Done postprocessing on table 'planet_osm_rels' in 6151s (1h 42m 31s).

2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_point' performed in 12647s (3h 30m 47s).
2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_line' performed in 25988s (7h 13m eights).
2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_polygon' performed in 34854s (9h 40m 54s).
2023-08-20 10:48:18 [0] All postprocessing on table 'planet_osm_roads' performed in 7674s (2h 7m 54s).

2023-08-20 10:48:18 [0] General memory use: top= 85815MByte current= 727MByte.
2023-08-20 10:48:18 [0] osm2pgsql took 154917s (43h 1m 57s) in general.

Below’s the chart for that area [click to enlarge]:

If you look better, you can hardly see the indexes being developed, however one of the most turning up functions are the optimals of.
room being released. These represent factors were some job has actually simply completed and also an additional one starts. Associating back.
with those logs, and also taking simply the 4 larger ones, we obtain (in sequential order):

• Gathering the roadways table releases 10GiB.
• Gathering the factor table releases 24GiB.
• Gathering the line table releases 68GiB.
• Gathering the polygon table releases 153GiB.

That makes a great deal of feeling. However there’s absolutely nothing for us below if we wish to reorder things since they’re all begun in.
parallel, and also we might just reorder them if we establish -- number-processes 1 The room being released essentially.
matches (at the very least in regards to orders of size) to the dimensions of the last tables we see above. These are likewise the.
primary making tables. The remainder is room released when ending up producing indexes, however the quantities are so little that I’m.
not mosting likely to concentrate on them. Likewise, notification that the majority of the room is released after the last of those 4 occasions since the.
initial information is larger therefore it takes longer to refine.

As a side note, I produced the above charts utilizing Prometheus, Grafana and also its notes. Among the advantages around.
Grafana is that it has an API that enables you to do a great deal of things (however remarkably, not checklist Control panels, although I.
hunch I might utilize the search API for that). I attempted to do it with Python and also demands but also for some factor it really did not.
job[3]:

Update: the blunder was utilizing requests.put() rather than requests.post() An additional repercussion of composing for hrs.
up until late in the evening.

 #!/ sur/bin/env python3.
import demands.
import datetime
.
time = datetime.datetime.strptime(' 2023-08-18 15:46:20', '% Y-% m-% d %H:% M:% S').
# likewise attempted dashboardId= 1.
information = dict( dashboardUID=' fO9lMi3Zz', panelId= 26, time= time.timestamp(), message=" Establishing tables").

# Update: not place(), article()!
# requests.put(' http://diablo:3000/api/annotations', json= information, auth =(' admin', 'XXXXXXXXXXXXXXX')).
# << Action [404]>>.

requests.post(' http://diablo:3000/api/annotations', json= information, auth =(' admin', 'XXXXXXXXXXXXXXX')).
# << Action [200]>>.

I left discovering why for afterward since I took care of to do the notes with crinkle:

$ python3 -c 'import datetime, sys; time = datetime.datetime.strptime( sys.argv[1], "% Y-% m-% d %H:% M:% S"); print( int( time.timestamp()) * 1000)' '2023-08-20 10:48:18'.
1692521298000.
$ crinkle-- verbose-- demand blog post-- customer 'admin: aing+ ai3eiv7Aexu5Shi' http://diablo:3000/api/annotations-- header 'Content-Type: application/json'.
-- information' {"dashboardId": 1, "panelId": 26, "time": 1692521298000, "message": "Done job [Building index on table planet_osm_ways]"} '.

( Yes. All notes. By hand. And also double-checked the following day, since I was doing them up until so late I made a couple of.
errors.)

Below are the indexes this action develops and also their dimensions:

 Call|Table|Accessibility approach|Dimension.
---------------------------------------------+ -------------------------------------+ ---------------+ ----------------.
planet_osm_ways_nodes_bucket_idx|planet_osm_ways|gin|11_817_369_600.
planet_osm_polygon_way_idx|planet_osm_polygon|essence|11_807_260_672.
planet_osm_ways_pkey|planet_osm_ways|btree|8_760_164_352.
planet_osm_polygon_osm_id_idx|planet_osm_polygon|btree|6_186_663_936.
planet_osm_point_way_idx|planet_osm_point|essence|4_542_480_384.
planet_osm_line_way_idx|planet_osm_line|essence|4_391_354_368.
planet_osm_line_osm_id_idx|planet_osm_line|btree|2_460_491_776.
planet_osm_point_osm_id_idx|planet_osm_point|btree|2_460_352_512.
planet_osm_rels_parts_idx|planet_osm_rels|gin|2_093_768_704.
planet_osm_roads_way_idx|planet_osm_roads|essence|291_995_648.
planet_osm_rels_pkey|planet_osm_rels|btree|153_853_952.
planet_osm_roads_osm_id_idx|planet_osm_roads|btree|148_979_712.
spatial_ref_sys_pkey|spatial_ref_sys|btree|212_992.

Also when the dimensions are rather huge (51GiB overall), it’s listed below the top added room (124GiB), so we can likewise disregard this.

After that it’s time to produce some indexes with an SQL data supplied by osm-carto psql does not publish timestamps for the.
lines, so I utilized my reliable pefan manuscript to include them[2]:

$ time psql-- dbname gis-- echo-all-- data././ osm-carto/indexes. sql|pefan -t.
2023-08-22T21:08:59.516386:-- These are indexes for making efficiency with << a href=" http://www.grulic.org.ar/~mdione/glob//posts/disk-usage-while-importing-an-osm-rendering-database/openstreetmap/">> OpenStreetMap< Carto.
2023-08-22T21:08:59.516772:-- This data is produced with scripts/indexes. py.
2023-08-22T21:08:59.516803: PRODUCE INDEX planet_osm_line_ferry ON planet_osm_line utilizing IDEA (method) in which course=" ferryboat" as well as osm_id > > 0;.
2023-08-22T21:10:17.226963: PRODUCE INDEX.
2023-08-22T21:10:17.227708: PRODUCE INDEX planet_osm_line_label ON planet_osm_line utilizing IDEA (method) in which name IS NOT NULL OR ref IS NOT NULL;.
2023-08-22T21:13:20.074513: PRODUCE INDEX.
2023-08-22T21:13:20.074620: PRODUCE INDEX planet_osm_line_river ON planet_osm_line utilizing IDEA (method) in which river='river';.
2023-08-22T21:14:41.430259: PRODUCE INDEX.
2023-08-22T21:14:41.430431: PRODUCE INDEX planet_osm_line_waterway ON planet_osm_line utilizing IDEA (method) in which river IN (' river', 'canal', 'stream', 'drainpipe', 'ditch');.
2023-08-22T21:16:22.528526: PRODUCE INDEX.
2023-08-22T21:16:22.528618: PRODUCE INDEX planet_osm_point_place ON planet_osm_point utilizing IDEA (method) in which location IS NOT VOID as well as name IS NOT NULL;.
2023-08-22T21:17:05.195416: PRODUCE INDEX.
2023-08-22T21:17:05.195502: PRODUCE INDEX planet_osm_polygon_admin ON planet_osm_polygon utilizing IDEA (ST_PointOnSurface( method)) WHERE name IS NOT NULL as well as border='management' as well as admin_level IN (' 0', '1', '2', '3', '4');.
2023-08-22T21:20:00.114673: PRODUCE INDEX.
2023-08-22T21:20:00.114759: PRODUCE INDEX planet_osm_polygon_military ON planet_osm_polygon utilizing IDEA (method) IN WHICH (landuse=" army" OR armed force='danger_area') as well as structure IS NULL;.
2023-08-22T21:22:53.872835: PRODUCE INDEX.
2023-08-22T21:22:53.872917: PRODUCE INDEX planet_osm_polygon_name ON planet_osm_polygon utilizing IDEA (ST_PointOnSurface( method)) WHERE name IS NOT NULL;.
2023-08-22T21:26:36.166407: PRODUCE INDEX.
2023-08-22T21:26:36.166498: PRODUCE INDEX planet_osm_polygon_name_z6 ON planet_osm_polygon utilizing IDEA (ST_PointOnSurface( method)) WHERE name IS NOT VOID as well as way_area > > 5980000;.
2023-08-22T21:30:00.829190: PRODUCE INDEX.
2023-08-22T21:30:00.829320: PRODUCE INDEX planet_osm_polygon_nobuilding ON planet_osm_polygon utilizing IDEA (method) in which structure IS NULL;.
2023-08-22T21:35:40.274071: PRODUCE INDEX.
2023-08-22T21:35:40.274149: PRODUCE INDEX planet_osm_polygon_water ON planet_osm_polygon utilizing IDEA (method) in which river IN (' dock', 'shore', 'canal') OR landuse IN (' storage tank', 'container') OR "all-natural" IN (' water', 'glacier');.
2023-08-22T21:38:54.905074: PRODUCE INDEX.
2023-08-22T21:38:54.905162: PRODUCE INDEX planet_osm_polygon_way_area_z10 ON planet_osm_polygon utilizing IDEA (method) WHERE way_area > > 23300;.
2023-08-22T21:43:20.125524: PRODUCE INDEX.
2023-08-22T21:43:20.125602: PRODUCE INDEX planet_osm_polygon_way_area_z6 ON planet_osm_polygon utilizing IDEA (method) WHERE way_area > > 5980000;.
2023-08-22T21:47:05.219135: PRODUCE INDEX.
2023-08-22T21:47:05.219707: PRODUCE INDEX planet_osm_roads_admin ON planet_osm_roads making use of IDEA (method) in which border='management';.
2023-08-22T21:47:27.862548: PRODUCE INDEX.
2023-08-22T21:47:27.862655: PRODUCE INDEX planet_osm_roads_admin_low ON planet_osm_roads making use of IDEA (method) in which border='management' as well as admin_level IN (' 0', '1', '2', '3', '4');.
2023-08-22T21:47:30.879559: PRODUCE INDEX.
2023-08-22T21:47:30.879767: PRODUCE INDEX planet_osm_roads_roads_ref ON planet_osm_roads making use of IDEA (method) in which freeway IS NOT VOID as well as ref IS NOT NULL;.
2023-08-22T21:47:41.250887: PRODUCE INDEX.

genuine 38m41,802 s.
customer 0m0,098 s.
sys 0m0,015 s.

The produced indexes and also dimensions:

 Call|Table|Accessibility approach|Dimension.
---------------------------------------------+ -------------------------------------+ ---------------+ ----------------.
planet_osm_polygon_nobuilding|planet_osm_polygon|essence|2_314_887_168.
planet_osm_line_label|planet_osm_line|essence|1_143_644_160.
planet_osm_polygon_way_area_z10|planet_osm_polygon|essence|738_336_768.
planet_osm_line_waterway|planet_osm_line|essence|396_263_424.
planet_osm_polygon_name|planet_osm_polygon|essence|259_416_064.
planet_osm_polygon_water|planet_osm_polygon|essence|188_227_584.
planet_osm_roads_roads_ref|planet_osm_roads|essence|147_103_744.
planet_osm_point_place|planet_osm_point|essence|138_854_400.
planet_osm_roads_admin|planet_osm_roads|essence|47_947_776.
planet_osm_polygon_way_area_z6|planet_osm_polygon|essence|24_559_616.
planet_osm_line_river|planet_osm_line|essence|17_408_000.
planet_osm_polygon_name_z6|planet_osm_polygon|essence|13_336_576.
planet_osm_roads_admin_low|planet_osm_roads|essence|2_424_832.
planet_osm_polygon_military|planet_osm_polygon|essence|925_696.
planet_osm_line_ferry|planet_osm_line|essence|425_984.
planet_osm_polygon_admin|planet_osm_polygon|essence|32_768.

The dimensions are little sufficient to disregard.

The last action is to import exterior information[2]:

 mdione@diablo:~/src/projects/osm/data/osm$ time././ osm-carto/scripts/get-external-data. py-- config././ osm-carto/external-data. yml-- information.-- data source gis-- port 5433-- username $CUSTOMER-- verbose 2>&> & 1|pefan.py -t '% Y-% m-% d %H:% M:% S'.
2023-08-22 23:04:16: DETAILS: origin: Inspecting table simplified_water_polygons.
2023-08-22 23:04:19: DETAILS: origin: Importing right into data source.
2023-08-22 23:04:20: DETAILS: origin: Import full.

2023-08-22 23:04:21: DETAILS: origin: Inspecting table water_polygons.
2023-08-22 23:06:16: DETAILS: origin: Importing right into data source.
2023-08-22 23:07:12: DETAILS: origin: Import full.

2023-08-22 23:07:49: DETAILS: origin: Inspecting table icesheet_polygons.
2023-08-22 23:07:55: DETAILS: origin: Importing right into data source.
2023-08-22 23:07:59: DETAILS: origin: Import full.

2023-08-22 23:08:01: DETAILS: origin: Inspecting table icesheet_outlines.
2023-08-22 23:08:06: DETAILS: origin: Importing right into data source.
2023-08-22 23:08:09: DETAILS: origin: Import full.

2023-08-22 23:08:11: DETAILS: origin: Inspecting table ne_110m_admin_0_boundary_lines_land.
2023-08-22 23:08:12: DETAILS: origin: Importing right into data source.
2023-08-22 23:08:13: DETAILS: origin: Import full.

genuine 3m57,162 s.
customer 0m36,408 s.
sys 0m15,387 s.

Notification exactly how this moment I altered pefan‘s - t alternative to have even more constant timestamps. The produced indexes are:

 Call|Determination|Accessibility approach|Dimension.
-------------------------------------+ -------------+ ---------------+ ----------------.
water_polygons|long-term|lot|1_201_078_272.
icesheet_outlines|long-term|lot|83_951_616.
icesheet_polygons|long-term|lot|74_571_776.
simplified_water_polygons|long-term|lot|34_701_312.
ne_110m_admin_0_boundary_lines_land|long-term|lot|139_264.
external_data|long-term|lot|16_384.

Call|Table|Accessibility approach|Dimension.
---------------------------------------------+ -------------------------------------+ ---------------+ ---------------.
icesheet_outlines_way_idx|icesheet_outlines|essence|3_325_952.
icesheet_polygons_way_idx|icesheet_polygons|essence|679_936.
simplified_water_polygons_way_idx|simplified_water_polygons|essence|630_784.
water_polygons_way_idx|water_polygons|essence|630_784.
ne_110m_admin_0_boundary_lines_land_way_idx|ne_110m_admin_0_boundary_lines_land|essence|24_576.
external_data_pkey|external_data|btree|16_384.

Once more, as well little to care.

Allow’s summarize what we have.

We began with a 28GiB Europe export, which produced.
298GiB of information and also indexes, with a top of 422GiB of use throughout the clustering stage. This ‘information and also indexes’ consists of.
an 83GiB level nodes data, which I require to utilize as a result of the undersized dimension of my making job. This indicates a 10x surge.
on information dimension, however likewise a 15x top use (approximation). As the top takes place in a component of the procedure that can not be.
reordered, any kind of various other reordering in information import or index generation would certainly supply no benefit. Perhaps just if you utilized.
-- number-processes 1, and also provided exactly how the clustering of tables are appointed to employees, I assume it’s currently the right.
order, other than perhaps exchanging the last 2, however they’re likewise the smaller sized 2; polygons was currently the top.

[1] psql makes use of pg_catalog. pg_size_pretty() to publish tables and also indexes dimensions, and also it’s difficult coded on the inquiry that.
it runs. Many thanks to individuals at #postgresql: libera.chat I figured out that the majority of the reduce commands (for example,.
both d+ and also di+ I utilized over) are applied with SQL inquiries. You can discover which inquiries by utilizing established ECHO_HIDDEN 1 and also.
running them once again. I arranged them by lowering dimension, and also I utilized Python’s lengthy int beautifying approach:-RRB- I likewise.
eliminated boring columns.

[2] I got rid of lines from this outcome that I assume do not include brand-new intriguing details, and also I divided it in areas so it’s.
simpler to review and also notes vital turning points for the charts later on.

[3] I typically utilize a highlighter for these bits, however alas, it’s damaged today. I have actually been kicking in the future a.
movement out of ikiwiki to possibly nikola, perhaps it’s time.

openstreetmap osm2pgsql grafana python demands
crinkle pefan ikiwiki