I'm importing some shapefiles into Postgres/PostGIS. I'm using Windows 8 and the shapefile importer/exporter GUI. It maxes out one CPU core. I'm wondering if there is a way I can use multiple cores to increase the speed?

  • 1
    If I remember correctly, PG/PGIS only uses one core for each connection. So I think you should be able to run multiple importers at the same time - each having its own connection. But I don't know if the database (or hard disks) will be the bottleneck. – Chau Jun 26 '13 at 12:10

Unfortunately, the short answer is no. There is no option to leverage multiple cores for either the import/export GUI or the underlying command prompt utility shp2pgsql. You can speed the import up by "using COPY rather than INSERT" (from the Import Options page), but that appears to be the default, and it won't use multiple cores.

As @Chau mentions, multiple imports should use different cores. So you could break the shape file up into parts, and then run concurrent imports. Breaking the shape file into pieces will likely take a similar amount of time as the database import, however.

If this is a matter of sharing data, (i.e., multiple users are importing the shape file), you can run multi-threaded restores of the data using pg-restore using the --jobs option. Basically, one user would convert the shape file into database format and then dump the spatial table in the "custom" format. That dump file could be shared; restoring it would be faster than importing from a shape file.

  • 1
    Since importing is (mostly) I/O bound, would this even help performance to any noticeable capacity? – Jason Scheirer Jul 2 '13 at 16:05
  • There will likely be an I/O bottleneck in the import; maybe that explains the lack of multi-core support in the tools? I have not measured the impact of the --jobs option specifically, but in my experience the postgres utilities pg_dump and pg_restore are noticeably faster than using pgsql2shp and shp2pgsql when moving data between databases. – Nathan Gerhart Jul 2 '13 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.