Is there any plan to support reading from postgis via Arrow Flight SQL?

[tumluliu]

or, is it really worth supporting that? The question is based on some very simple perf testing results. I prepared two polygon datasets extracted from OSM:

• buildings in Munich: 674 thousand polygons containing 4.33 million coords
• buildings in Bavaria: 5.98 million polygons containing 37.36 million coords

And created 2 GeoParquet files with QGIS (SNAPPY compression + GEOARROW encoding), 50MB and 462MB respectively. Then have the raw polygon datasets imported into postgis (pg15+postgis3.3) as well.

I tried firstly with the reader from geoarrow-rs/io/postgis and measured the running time of read_postgis() fn with:

async fn main() {
    println!("Start reading geometries from postgis...");
    let connection_url = "postgresql://user:pass@localhost:5432/testdb";
    let pool = PgPoolOptions::new().connect(connection_url).await.unwrap();
    let sql1 = "SELECT * FROM munich_osm_buildings;";
    let mut start = Instant::now();
    let _table1 = read_postgis(&pool, sql1).await.unwrap();
    let mut duration = start.elapsed();
    println!("Done. Time elapsed in read_postgis() for munich_osm_buildings is: {:?}", duration);

    let sql2 = "SELECT * FROM bayern_osm_buildings;";
    start = Instant::now();
    let _table2 = read_postgis(&pool, sql2).await.unwrap();
    duration = start.elapsed();
    println!("Done. Time elapsed in read_postgis() for bayern_osm_buildings is: {:?}", duration);
}

and got:

❯ cargo run
   Compiling geoarrow-postgis v0.1.0 (/home/me/projects/geoarrow-postgis)
    Finished dev [unoptimized + debuginfo] target(s) in 3.70s
     Running `target/debug/geoarrow-postgis`
Start reading geometries from postgis...
Done. Time elapsed in read_postgis() for munich_osm_buildings is: 4.349586901s
Done. Time elapsed in read_postgis() for bayern_osm_buildings is: 37.877833901s

After that, I tried loading the 2 GeoParquet files with pyarrow.parquet:

import pyarrow.parquet as parquet
from time import perf_counter as pc

t0 = pc()
# 674 thousand polygons
# 4.33 million coords
gdf = parquet.read_pandas('munich_buildings_osm.parquet')
print(f"time for reading Munich buildings geoparquet: {pc()-t0}")

t0 = pc()
# 5.98 million polygons
# 37.36 million coords
gdf = parquet.read_pandas('bayern_buildings_osm.parquet')
print(f"time for reading Bayern buildings geoparquet: {pc()-t0}")

and got:

time for reading Munich buildings geoparquet: 0.10657382477074862
time for reading Bayern buildings geoparquet: 0.2329485327936709

actually what I observed is that sometimes the geoparquet file loading for Bayern could be even faster than that for Munich. I don't know why.

So here are my questions:

1. do you see any fault or problem with my experiments?
2. if 1 is no, then do you think the performance of reading from postgis could reach to the similar level (in milliseconds) to that from geoparquet file?
3. would the Arrow Flight SQL be helpful here?
4. if 3 is yes, do you think it would make sense to implement another module under io, sth. like postgis-flight for such purpose? Will that be a very complex task in your opinion?

Thank you very much.

p.s. I actually have the Apache Arrow Flight SQL adapter for PostgreSQL up and running already, and can successfully fetch some rows of the polygons in wkt from the above db with flight_sql_client. But I'm not sure if it makes sense to continue with implementing sth. like a MixedGeometryArray constructor on top of that.

[kylebarron]

❯ cargo run
   Compiling geoarrow-postgis v0.1.0 (/home/me/projects/geoarrow-postgis)
    Finished dev [unoptimized + debuginfo] target(s) in 3.70s
     Running `target/debug/geoarrow-postgis`
Start reading geometries from postgis...
Done. Time elapsed in read_postgis() for munich_osm_buildings is: 4.349586901s
Done. Time elapsed in read_postgis() for bayern_osm_buildings is: 37.877833901s

Benchmarks run in debug mode are completely meaningless. You need to compile and run with --release.

[tumluliu]

Thanks a lot @kylebarron . That makes a lot of sense. I've rerun it with --release and here is updated results:

❯ cargo run --release
    Finished release [optimized] target(s) in 0.06s
     Running `target/release/geoarrow-postgis`
Start reading geometries from postgis...
Done. Time elapsed in read_postgis() for munich_osm_buildings is: 434.996374ms
Done. Time elapsed in read_postgis() for bayern_osm_buildings is: 3.61819654s

It's indeed much better. But the reading from bayern_osm_buildings table is still around 10x slower than that from geoparquet file. Do you think Arrow Flight SQL would help improve the performance?

[kylebarron]

The existing Postgis reader is not particularly efficient. My plan for the Postgis reader is to wait for the Rust ADBC driver to become more stable and then to use that for reading from Postgres, and just have a thin wrapper around that for parsing geometries.

I'm not intricately familiar with Arrow Flight and Flight SQL but I see them as pretty complex and out of scope for geoarrow-rs at the moment. It should be possible to use Arrow Flight yourself and convert an Arrow object to a geoarrow object with one or two lines of code

[tumluliu]

It's great to know the ADBC-based implementation is on the roadmap. Thank you!