Why can ogr2ogr upload data to a not previously existing table faster than to an existing empty one?

Question

Recently I observed that it takes much less time for ogr2ogr to upload data to a PostGIS database if I do not create the destination table first.

I would like to understand why this happens. Below is a reproducible example.

---

Create mock GeoJSON

To create a GeoJSON with a million features, with columns geometry and col0, I do:

import geopandas as gpd
import shapely.geometry

count = 1000000

gpd.GeoDataFrame.from_dict(
    dict(
        geometry=[
            shapely.geometry.Polygon([[0, 0], [1, 0], [1, 1], [0, 1], [0, 0]])
            for _ in range(count)
        ],
        col0=["example_string" for _ in range(count)],
    )
).set_crs(4326).to_file("localdata.geojson", engine="pyogrio", layer="main")

I use this GeoJSON as source for a local database (see below).

---

Prepare DB, do not prepare table, ingest

I use this bash script to create a local PostGIS database named source:

dropdb --if-exists source --host localhost --port 5432 --username postgres
createdb source --host localhost --port 5432 --username postgres

psql --username postgres --dbname source --command "CREATE EXTENSION postgis;"

Notice that I do not create a table, only the database. Then, I load the GeoJSON data to the database, to a table named localdata:

ogr2ogr -f "PostgreSQL" \
    PG:"port=5432 host=postgres host=localhost dbname=source" \
    localdata.geojson \
    -nln localdata

This ogr2ogr command took 12.20 seconds. Alternatively, I could create the table in advance:

---

Prepare DB, prepare table, check for lack of indexes, ingest

As previously, let's create a database named source, but now let's also create table localdata before using ogr2ogr:

dropdb --if-exists source --host localhost --port 5432 --username postgres
createdb source --host localhost --port 5432 --username postgres

psql --username postgres --dbname source --command "CREATE EXTENSION postgis;"
psql --username postgres \
     --dbname source \
     --command "CREATE TABLE localdata ( col0 TEXT, wkb_geometry GEOMETRY);"

I do not create any indexes. To be sure there aren't any, I run:

psql --username postgres \
     --dbname source \
     --command "select count(*) from pg_indexes where tablename like '%localdata%'"

Output:

 count 
-------
     0
(1 row)

Reassuring. Then, I load GeoJSON data to database, same command as previously:

ogr2ogr -f "PostgreSQL" \
    PG:"port=5432 host=postgres host=localhost dbname=source" \
    localdata.geojson \
    -nln localdata

This command took 42.71 seconds.

---

I tried a few times, with differently sized GeoJSONs, and this pattern is always there: ogr2ogr is slower if the table already exists.

In my real-world scenario, I have an empty table (without indexes, just like the one above), to which I would like to ingest some data. Understanding the above described runtimes might help me speed up this process.

Why is uploading to a table already created slower than uploading to a not previously created table?

Answer 1

I believe that the difference comes from this documented feature https://gdal.org/drivers/vector/pg.html#configuration-options

PG_USE_COPY=value: This may be "YES" for using COPY for inserting data to Postgresql. COPY is significantly faster than INSERT. COPY is used by default when inserting from a table that has just been created.

If you have an existing table then COPY is not used by default.

Notice that GDAL creates a spatial index into a new table by default https://gdal.org/drivers/vector/pg.html#layer-creation-options. You should take also that into account when running benchmarks.