From OpenStreetMap Wiki
Jump to: navigation, search

This page is for information on pgsnapshot


pgsnapshot size depends on what options are used when setting up the database and the data imported.


Tables and their indexes subject to UPDATEs or DELETEs will have "bloat" in the form of data (dead tuples) on disk that can be deleted. The dead tuples are removed with VACUUM or autovacuum but the disk space isn't reclaimed. For that a CLUSTER or VACUUM FULL is needed, but these take days to complete. For a continually growing database like OSM autovacuum will keep the table bloat below some fixed bounds but indexes will periodically require rebuilding.

It is probably not worth doing a CLUSTER or VACUUM FULL over a reimport.

The numbers below are from running from Sept 2012 to May 2013 (7 months) and show the difference in size between the old index and the reindexed index. The numbers are from btree indexes or GIN tag indexes, not GiST indexes.

table Increase in size of the old index
way_nodes +50%
ways +100%
relation_members +130%


The numbers below are for the full planet kept up to date to April 2013, with linestrings built.

Column name Size
Total 10MB
Index name Size Creation time
pk_users 6808 kB
Grand total
1.91b rows
Column name Size
id 14GB
version 7.3GB
user_id 7.3GB
tstamp 14GB
changeset_id 14GB
tags 17GB
geom 52GB
Total 188GB
Index name Size Creation time
idx_nodes_geom 91GB 14h24m
idx_nodes_tags 15GB 1h13m
pk_nodes 40 GB 59m
Total 146GB 16h36m
Grand total 334GB
185m rows
Column name Size
id 1414MB
version 707MB
tstamp 1414MB
changeset_id 1414MB
tags 18GB
nodes 18GB
bbox 20GB
linestring 39GB
Total 108GB
Index name Size Creation time
pk_ways 3969MB 18m
idx_ways_bbox 9727MB 52m
idx_ways_linestring 9727MB 59m
idx_ways_tags 8706MB 1h38m
Grand total
2.3b rows
Column name Size
Total 110GB
Index name Size Creation time
pk_way_nodes 67GB 1h9m
idx_way_nodes_node_id 47GB 1h56m
Grand total
1.98m rows
Column name Size
id 15MB
version 7735kB
tstamp 15MB
changeset_id 15MB
tags 231MB
Total 353MB
Index name Size Creation time
pk_relations 42MB 5s
idx_relations_tags 1m42s
Grand total
xxx rows
Column name Size
Index name Size Creation time
pk_relation_members 674MB 39s
idx_relation_members_member_id_and_type 674MB 56s
Grand total

Import times

The most efficient way to import the planet is with --write-pgsql-dump. Aside from a more efficient SQL it allows you to use all your memory for osmosis in the first stage then all your memory for index builds later on.

Values are for a 20213MB PBF planet updated to May 31, 2013. Database on 6 disk RAID10 array, all disks consumer 7200RPM drives, 32GB memory, AMD Phenom II x6 1090T. planet file and dump .txt files on RAID10 of 2TB 7200RPM drives with 1TB platters.

Osmosis command was osmosis --read-pbf-fast workers=3 --write-pgsql-dump nodeLocationStoreType=InMemory keepInvalidWays=yes enableBboxBuilder=yes enableLinestringBuilder=yes. Osmosis 0.43 was used. Total time was 4:18:34.

       User time (seconds): 37114.18
       System time (seconds): 773.61
       Percent of CPU this job got: 244%
       Elapsed (wall clock) time (h:mm:ss or m:ss): 4:18:34
       Average shared text size (kbytes): 0
       Average unshared data size (kbytes): 0
       Average stack size (kbytes): 0
       Average total size (kbytes): 0
       Maximum resident set size (kbytes): 126439008
       Average resident set size (kbytes): 0
       Major (requiring I/O) page faults: 1103342
       Minor (reclaiming a frame) page faults: 22992736
       Voluntary context switches: 4913429
       Involuntary context switches: 7905070
       Swaps: 0
       File system inputs: 91933400
       File system outputs: 831028824
       Socket messages sent: 0
       Socket messages received: 0
       Signals delivered: 0
       Page size (bytes): 4096
       Exit status: 0

table dump .txt size \copy time rows size in DB without indexes
users 3.8MB 851ms 234k 10MB
nodes 194GB 2h48m 1.91b 188GB
ways 154GB 2h2m 185m 108GB
way_nodes 48GB 1h40m 2.27b 110GB
relations 306MB 14s 1.98m 353MB
relation_members 563MB 78s 22.4m 1225MB
Total 397GB 6h33m - -

With bboxes only: 3:45:03 wall clock ways.txt: 82GB.

with linestrings only: 3:54:14 wall clock ways.txt 120G

With neither: ways.txt 48GB