Fastest way to add shapely geometries to a PostGis database using geoalchemy2

Question

I am looking for the absolute fastest approach to adding +10,000 shapely geometry objects to a PostGIS database using geoalchemy2.

I'm having no speed issues when uploading to a localhost postgres server, but when I upload directly to my remote server it takes 35x as long!

I realize this might be a server question and not appropriate for this site, but curious if anyone has a successful workflow for:

Quickly uploading shapely geometries to a PostGIS database using geoalchemy2

Answer 1

The Problem is your uplink to the remote server.

The "uplink" to your localhost postgresql server is the speed of your harddrive (~100MB/s for HDD and 500MB/s for SDD). Fast internet connections usually have up to 10Mbit/s, which is roughly 1MB/s. So one solution is to get a better connection with a higher uplink speed.

Another solution would be:

• Add data to localhost
• Dump the data using for example pg_dump with custom format
• compress the dump again (zip, rar, 7zip etc)
• upload the dump to your server
• restore the dump using pg_restore

Usually the dumps in custom format are much smaller than the original data, so its worth a try.