GeoParquet Read vs Write Performance

[youngpm]

Hi!

I was curious what the most performant way of writing out GeoParquet from DuckDB is, I've been surprised how fast it is to read in (geo)parquet, while writing it back out is quite a bit slower.

For example, I've staged s3://overturemaps-us-west-2/release/2024-09-18.0/theme=buildings/type=building/part-00041-d0628531-2fbd-47aa-8608-f61ff708b14c-c000.zstd.parquet locally first. Then, to pull it into duckdb:

explain analyze create table building as select * from read_parquet('data/part-00041-d0628531-2fbd-47aa-8608-f61ff708b14c-c000.zstd.parquet');
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││               Total Time: 3.25s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘

which is amazing!

If I immediately export it back to parquet, I get:

explain analyze COPY building TO 'building.parquet' WITH (FORMAT PARQUET, COMPRESSION 'zstd');
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 610.99s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘

If I add PER_THREAD_OUTPUT it's about 2x as fast. Is there any other tricks to try?

I'm very new to parquet and duckdb so I'm not sure what all to expect for write performance!

[mtravis]

hello @youngpm

That export time is really slow for a file that size. Try removing the WITH (FORMAT PARQUET, COMPRESSION 'zstd')

Parquet is default export type and I think it will use snappy compression which is fine for testing.

[youngpm]

Hey @mtravis , I gave that a go:

explain analyze COPY building TO 'building-default.parquet';
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 218.42s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘

So a 3x improvement, not bad! Although still about 100x slower than reading into duckdb.

Interestingly, the progress bar goes to 99% in the first few seconds but bogs down afterwards (true of my original post too), not sure what that's telling me.

@Maxxen Do you think this is worth filing an issue for? Just not sure if there's a bug or its expected.

[Maxxen]

I don't think this is a bug, Parquet is a heavily read-optimized format so I think its expected that writes are much slower depending on the column types/compression codecs used. The "Geo"-Parquet code path only adds a small amount of additional processing to calculate the geo-specific metadata/statistics as required by the geoparquet specification, so I'd only be inclined to investigate write-speed if it turns out that writing geoparquet is significantly slower than normal parquet.

[Maxxen]

As for the progress bar, DuckDB currently only tracks progress from the "source" of the pipeline, but when writing parquet we have to buffer almost all the output (which will be temporarily spilled to disk if it grows too large) so we can divide it up into pages, calculate stats, compress etc. So the 99% just means all the source data has been scanned but we're still processing to write the output.

[youngpm]

Makes sense, thanks!

[youngpm]

@Maxxen An update!

It looks like setting ROW_GROUP_SIZE causes huge performance differences on export:

explain analyze COPY building TO 'building-zstd.parquet' (FORMAT PARQUET, CODEC 'ZSTD', ROW_GROUP_SIZE 100000);
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││               Total Time: 3.10s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘

compared to using the default ROW_GROUP_SIZE (default is 122,880 according to the docs):

explain analyze COPY building TO 'building-zstd-def.parquet' (FORMAT PARQUET, CODEC 'ZSTD');
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 585.98s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘