Why is the Export to PostgreSQL function in QGIS to a remote database extremely slow?

Question

I posted this on Twitter but I'll ask here as well - I need to load spatial data into a remote PostgreSQL database (i.e. a db on a VPS in the cloud or hosted SAAS db).

When I use the Export to PostgreSQL function in QGIS it works, but it is very, very slow. In my test it took over 4 hours to export a 300MB GeoJSON file.

When using ogr2ogr with this same file it took just over 2 minutes (this was on a connection with a slightly faster upload speed, but on this connection the QGIS method is still very slow). Is there a method to access this type of GDAL command directly in QGIS, or a QGIS plugin that achieves a similar function to the ogr2ogr command?

• Bera: Have you tried Convert format "This algorithm is derived from the ogr2ogr utility"
• Malcolm: No I have not, but the question is also, why is the export method above so slow...

Answer 1

It's slow because it's not optimized for loading big files in PostgreSQL like GDAL. It's one feature after another according to code https://github.com/qgis/QGIS/blob/master/src/analysis/processing/qgsalgorithmexporttopostgresql.cpp#L164

Although it seems a good idea to use "Convert format" from the comment, it does not work from our test because you can't set a PostgreSQL/PostGIS target. You may need to create a GUI to wrap native Python GDAL function equivalent to ogr2ogr like below

from osgeo import gdal
# gdal.UseExceptions()
# gdal.SetConfigOption('CPL_DEBUG', 'ON')
gdal.SetConfigOption('PG_USE_COPY', 'YES')
conn_string = "PG:host=localhost dbname=mydatabase user=myuser password=mypassword port=5432"
ds = gdal.OpenEx(conn_string, gdal.OF_VECTOR)
# To ease options reading but end result is one string
params = [
 ['-lco', 'SCHEMA=public'], # Defaut to public, so optional
 ['-lco', 'SPATIAL_INDEX=GIST'], # Only work if recent GDAL, otherwise remove
 ['-lco', 'GEOMETRY_NAME=the_geom'],
 ['-gt', '65536'],
 ['-nlt', 'PROMOTE_TO_MULTI'],
 ['-nln', 'futuretablename']
]
options = ' '.join([' '.join(i) for i in params])
gdal.VectorTranslate(
 ds,
 'ne_50m_rivers_lake_centerlines_scale_rank.geojson', # The input GeoJSON
 options=options
)

File ne_50m_rivers_lake_centerlines_scale_rank.geojson in Python code sample available at https://raw.githubusercontent.com/nvkelso/natural-earth-vector/master/geojson/ne_50m_rivers_lake_centerlines_scale_rank.geojson

Answer 2

In QGIS 3.28 there is the GDAL algorithm "Export to PostgreSQL (available connections)" which does upload large vector files pretty fast compared to the standart QGIS processing algorithm. Plus it gives quite some additional options (like how many features should be in one transaction)

• Malcolm: does this use copy - that is the issue with the current export to postgresql - it saves each feature one by one when ogr2ogr uses copy by default for new tables which is exponentially faster