[OSM-dev] size of postgresql database - without flat file

Paul Norman penorman at mac.com
Sun Jan 29 04:17:17 UTC 2017


On 1/28/2017 3:00 PM, Walter Nordmann wrote:
>
> i'm just installing my brand new server to get my applications 
> (missing boundaries and many more) running again. The server has 2x 
> 960GB SSD and 1x 2TB SATA disk installed.
>
> I'm doing a full planet import using osm2pgsql 0.92. This time i'm 
> doing the import without flat file, because i would like to use the 
> nodes in planet_osm_nodes for some new queries.
>
> But: planet_osm_nodes got an actual size of 785 GB, which is much much 
> more than i exspected. Is that the usual size or may be, i'm doing 
> something wrong? 

With the version of osm2pgsql you have when --extra-attributes is 
specified then tags are added to each object with the username, uid, 
version, timestamp, and changeset. This adds about 100 bytes per node to 
the table, which should add about 350GB. I would expect the size of 
planet_osm_nodes with a plain --slim import to be about 175GB, plus indexes.

Does your 785GB figure include indexes? If so, this sounds about right.

The latest versions of osm2pgsql do not have this issue because the node 
slim table schema has changed to no longer store tags, because they are 
never needed.

It's worth noting that 100 bytes per object for metadata is remarkably 
inefficient because it gets stored in a text[] array like 
"osm_user,lokejul,osm_uid,2034065,osm_version,11,osm_timestamp,2015-04-12T18:39:22Z,osm_changeset,30169891". 
This uses more bytes because it stores "tag" names, and because there 
are more efficient ways to store numbers and timestamps than as text.



More information about the dev mailing list