ZStandard Compression Level

[marklit]

It looks like level 10 is being used at the moment. Given this is written once a week, it might be worth turning this up to the max of 22. When this project picks up popularity, as much space that could be saved with this codec will have been.

Row groups of 100K rows seem like a good starting point for debates and benchmarks.

[marklit]

At the moment, Buildings uses 122K-row row-groups. I ran the simple aggregate on the dataset and it needed over 5K individual GET requests to return a result.

EXPLAIN ANALYZE
    SELECT   COUNT(*),
             type
    FROM     READ_PARQUET('https://data.openstreetmap.us/layercake/buildings.parquet')
    GROUP BY 2
    ORDER BY 1 DESC;

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE     SELECT   COUNT(*),              type     FROM     READ_PARQUET('https://data.openstreetmap.us/layercake/buildings.parquet')     GROUP BY 2     ORDER BY 1 DESC;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││            in: 2.2 MiB            ││
││            out: 0 bytes           ││
││              #HEAD: 1             ││
││             #GET: 5372            ││
││              #PUT: 0              ││
││              #POST: 0             ││
││             #DELETE: 0            ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 194.39s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          ORDER_BY         │
│    ────────────────────   │
│     count_star() DESC     │
│                           │
│           2 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│        count_star()       │
│             1             │
│                           │
│           2 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│         Groups: #0        │
│                           │
│        Aggregates:        │
│        count_star()       │
│                           │
│           2 Rows          │
│          (0.88s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│            type           │
│                           │
│       657586639 Rows      │
│          (0.04s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│         Function:         │
│        READ_PARQUET       │
│                           │
│     Projections: type     │
│    Total Files Read: 1    │
│                           │
│       657586639 Rows      │
│         (4502.25s)        │
└───────────────────────────┘

The following data is from bounding box over a neighbourhood in Tallinn produced a 153 KB Parquet file when spatially sorted and compressed using ZSTD level 22.

It needed to download 28.7 MB from Cloudflare across 33 queries.

This sort of data extraction task could be pretty common with this feed so making the row-groups larger might help optimise this sort of workload. I'm not sure if the individual GET commands, which will run in parallel, being reduced with have a greater impact than the amount of data that needs to be sifted through. Cloudflare has great latency but there are still limits to it.

EXPLAIN ANALYZE
    SELECT * 
    FROM READ_PARQUET('https://data.openstreetmap.us/layercake/buildings.parquet')
    WHERE "bbox".xmin BETWEEN 24.736551590059058 AND 24.75338572387809
    AND   "bbox".ymin BETWEEN 59.43186218817514 AND 59.44438735770407;

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE               SELECT * FROM read_parquet('https://data.openstreetmap.us/layercake/buildings.parquet')                              WHERE "bbox".xmin BETWEEN 24.736551590059058 AND 24.75338572387809               AND "bbox".ymin BETWEEN 59.43186218817514 AND 59.44438735770407                              ;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││            in: 28.7 MiB           ││
││            out: 0 bytes           ││
││              #HEAD: 1             ││
││              #GET: 33             ││
││              #PUT: 0              ││
││              #POST: 0             ││
││             #DELETE: 0            ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 99.07s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│            type           │
│             id            │
│            tags           │
│            bbox           │
│          geometry         │
│                           │
│         1197 Rows         │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│         Function:         │
│        READ_PARQUET       │
│                           │
│        Projections:       │
│            bbox           │
│            type           │
│             id            │
│            tags           │
│          geometry         │
│                           │
│          Filters:         │
│  bbox.xmin>=24.736551 AND │
│  bbox.xmin<=24.753386 AND │
│   bbox.ymin>=59.43186 AND │
│    bbox.ymin<=59.444386   │
│                           │
│    Total Files Read: 1    │
│                           │
│         1197 Rows         │
│         (106.82s)         │
└───────────────────────────┘

[ianthetechie]

I think the current row group size is already 100k. Do you have any benchmarks on space savings from higher compression? I thought I posted some test results I did here a month or 2 ago and didn't find a ton of improvement from significantly increasing it. But that may depend on the layer. I also don't think I tried level 22... I wanna say I tried 15 or something.

[marklit]

I did a bunch of benchmarking and ZSTD between 3 and 22 had little to no wall clock difference for me. Some of these datasets were global in nature as well. 22 is the best ZSTD can do and from the video encoding world I often read something along the lines that encoding time doesn't matter as it'll save 1000s or millions of people bandwidth and storage space. GeoFabrik did a post the other week about how they're getting hammered with requests. I suspect all of the OSM feeds suffer the same fate.

If we have 5K+ row-groups in the Buildings database and the user's query doesn't give something to the query engine to narrow the footprint then yeah, we'll be hitting Cloudflare like crazy.

If a row-group of 2M or 5M on that dataset, we'd bring down that count considerably.

If you have some sort of test end point on Cloudflare, it might be worth running a few tests on it and see what sort of difference there is for the major use cases.

[jake-low]

Interesting point about the tradeoff between row group size and number of requests. This is relevant for Layercake from both a usability standpoint (making typical queries fast) and from a cost standpoint (Cloudflare R2 charges per request, but not per KB of data transferred, so fewer, larger transfers costs less).

As for compression level, we did a bit of testing when we enabled ZSTD compression in #14, but it wasn't exhaustive, and it might be worth exploring raising the compression level further. I suspect that postprocessing step (which performs both Hillbert sorting and compression) is I/O bound on our current hardware, and so there might be headroom to raise the compression level without any impact on total runtime (just an increase in % CPU utilization).

[ianthetechie]

Yeah I also suspect we should tune row group by layer. For example, addresses will be predominantly points. Whereas boundaries are massive multi polygons. These probably shouldn't have the same settings.

[marklit]

I suspect ZSTD compression level doesn't make much of a impact on CPU as it might suggest.

DuckDB has done a ton of reengineering around their sorting algorithms this year and I would be surprised to see an upgrade to 1.4.0 alone speed things up.

There seems to be a few different ways to spatially sort data as well and switching between those methods might impact wall clock time of the build more than anything else.

[marklit]

I just ran a quick benchmark on a 210 MB Parquet file from the TUM / GBA dataset. This was with DuckDB 1.4.0 which should have the latest and greatest sorting algorithms implemented.

.timer on

COPY (
    FROM '/mnt/i/gis/Global/GBA/parquet/e115_n45_e120_n40.parquet'
    ORDER BY RANDOM()
)
TO 'random.parquet' (
    FORMAT            'PARQUET',
    CODEC             'ZSTD',
    COMPRESSION_LEVEL 22,
    ROW_GROUP_SIZE    15000);

00:00:25.68 elapsed, 271 MB

COPY (
    FROM 'random.parquet'
    ORDER BY ST_Hilbert(
        geometry,
        (
            SELECT ST_Extent(ST_Extent_Agg(geometry))
            FROM 'random.parquet'
        )
    )
)
TO 'st_hilbert.parquet' (
    FORMAT            'PARQUET',
    CODEC             'ZSTD',
    COMPRESSION_LEVEL 22,
    ROW_GROUP_SIZE    15000);

(00:00:07.58 elapsed), 210 MB

COPY (
    FROM 'random.parquet'
    ORDER BY HILBERT_ENCODE([bbox.ymin,
                             bbox.xmin]::double[2])
)
TO 'hilbert_encode.parquet' (
    FORMAT            'PARQUET',
    CODEC             'ZSTD',
    COMPRESSION_LEVEL 22,
    ROW_GROUP_SIZE    15000);

(00:00:07.11 elapsed), 210 MB

COPY (
    FROM 'random.parquet'
    ORDER BY MORTON_ENCODE([bbox.ymin,
                            bbox.xmin]::double[2])
)
TO 'morton_encode.parquet' (
    FORMAT            'PARQUET',
    CODEC             'ZSTD',
    COMPRESSION_LEVEL 22,
    ROW_GROUP_SIZE    15000);

(00:00:06.91 elapsed), 211 MB

I'm not sure if at the scale we're working at, either strong compression or a less-than-fastest sorting method make much of a wall-clock difference to this workload.

Let me know what you're seeing at your side but I think the file size of the deliverable seems to be the objective here and is without hardware constraints of the CI system you're using.

[jake-low]

@brawer brought this up again in #30. I think it's worth considering. I will try to do some ad-hoc tests on the production build server soon to see what the impact would be of these changes on the wall clock runtime for building Layercake data.