Faster Geospatial Enrichment | Hacker News Reader

Faster Geospatial Enrichment

tech.marksblogg.com

97 pointstodsacerdoti17 comments

Nearly completely nonsensical test. Aside from what was already written by others, this pretty much only tests function used to convert h3 geometry to h3 point. It is provided in clickhouse out of the box, in other two you gotta get it from outside sources. The bigquery one is written in javascript - no wonder it's slow. Only useful fact is comparison of sizes of result tables between postgres and clickhouse.

Unless there is an alternative, benchmarking the software that you are going to use seems pretty reasonable.

I think OP was pointing out that this was an apple to oranges comparison—in that the comparison ends up being of little use due to poor rigor configuring each test case.

The thing is that it isn't informative. It only tells you that in this particular benchmark, such-and-such is so-much faster. This does not extrapolate to your real world use cases unless by some fluke it happens to be a major bottleneck in your application.

The only actually informative benchmark is to port your application to use each of the the database engines, and then benchmark your application. That will maybe tell you something. Odds are what you were using before has a home field advantage by being fairly well optimized, so even then it doesn't tell you as much as you might believe.

As I noted, you'd probably want to do this conversion before import anyway, so none of the results may be of practical interest if you decide to go down that road. But maybe query performance tests on already imported data would be of interest.

Looks like apples to oranges - ClickHouse was creating new table while PostgreSQL was expanding and updating existing table which is naturally slower, and it was done 3 times instead of updating all 3 columns in the same operation. It is about the worst possible way to do it. For equal comparison the PostgreSQL test should have been running creation of new table too - ie. the same "create table as (select *, 3 enrichment columns from open_cell_towers)"

You're right. It's 2.1x faster doing it that way. Updated the post. Cheers for the heads up.

That's what I always say: if ClickHouse wasn't the fastest in your OLAP benchmark, you are almost certainly doing something wrong :)

As others have mentioned it's a difficult test as some have native support for H3, but Mark has some great real-life benchmarks, I would prefer to see the load times included in the total time as they can be significant.

I think you can improve the BigQuery benchmark with a few simple tweaks similar to how you did on Postgres even using on-demand:

- Use CTAS rather than altering (very slow on BQ) - Store the lat,lng as a Geography Point type on ingestion (increases performance significantly vs casting) - Consider spatially clustering the table around the H3 output (wont affect benchmark speed but will make subsequent processing speedy!)

If you're interested in H3, you might also be interested in S2: https://s2geometry.io/

ClickHouse has support for both of them.

By the way, since ClickHouse is a column database, so it can be faster to ALTER TABLE ... ADD COLUMN with the default value and then send a separate query to materialise those columns because the ALTER itself doesn't do that.

I have to wonder -- considering that H3 is apparently available as a C library -- how fast it would be to simply enrich the lon/lat data with H3 data using a simple Unix filter using that C library before even importing it anywhere.

(One also wonders if the column-based database doesn't "cheat" when doing operations of the type "make a new table by adding new columns", since it could in principle COW-reuse all the unchanged columns in memory on that 70-second query.)

ClickHouse has no such optimizations for reuse of columns.

Even memcpy would be more that fast enough, though. This just seemed like a weird test to me since instead of testing "which [database] can convert a set of coordinates into H3 identifiers the fastest", it seems more like a test of which database can change its schema eagerly the fastest. Not saying that this is not a useful thing to have, but when you can already import data in the correct format, it seems like waste of effort for the thing allegedly being tested. (It's possible that I missed some detail, of course.)

Indeed - https://gist.github.com/ajfriend/3eccf3ec01b33f46dc14f5dbb13...

Is BigQuery using on-demand or flat-pricing though? The number of slots available will considerably change the processing time.

Reply on news.ycombinator.com