Mark Litwintschik

For data engineering the two best by far I know of: 1. BI Cortex - sadly seemingly not active anymore: https://bicortex.com/ 2. Mark Litwintschik's Tech Blog: https://tech.marksblogg.com/

QGIS is the shit. I absolutely love it, great for visualizing GeoJSON, GeoTIFF files, open data feeds, etc. My one gripe is that their macOS installers have been out of date for ages now, the best way I've found is to actually install from Conda Forge directly: > brew install micromamba > mamba install qgis It's really crazy the number of open geospatial data feeds that exist out there from NASA, NOAA, and ESA. If you're interested in checking any of this stuff out, I highly encourage following Mark Litwinchik's blog, this guy is a legend and he does most of his work with open tools like QGIS and DuckDB https://tech.marksblogg.com/

I was looking for @marklit (Mark Litwintschik / https://tech.marksblogg.com) in the list as he's a geospatial-focussed blogger I've seen regularly on HN with interesting blog posts where he finds and presents open source datasets I'd never thought about, walks through some basic processing/querying steps, and provides some examples of what can be produced with the data. Many of the blog posts have left me thinking about possibilities to setup bots for uploading maps to Wikimedia Commons (for embedding within Wikipedias etc) based on these interesting datasets, or at least automating via scripts the development/upload of maps on a country-by-country basis (or other criteria) for static once-off datasets. Unfortunately he doesn't show in the top 100. Also unfortunately, there is no blogger described in the top 100 as having a geospatial interest/focus.

> primary way to get a clean-ish OSM shapefile Shapefiles shouldn't be what you're after, Parquet can almost always do a better job unless you need to either edit something or use really advanced geometry not yet supported in Parquet. Also, this is your best source for bulk OSM data: https://tech.marksblogg.com/overture-dec-2024-update.html If you're using ArcGIS Pro, use this plugin: https://tech.marksblogg.com/overture-maps-esri-arcgis-pro.html

Agreed, great blog. "satellites spotting ships" was only 82d ago. https://tech.marksblogg.com/yolo-umbra-sar-satellites-ship-detection.h… https://news.ycombinator.com/item?id=40716032

For what it's worth, there are some benchmarks of kdb+ (the database built on k) here - https://tech.marksblogg.com/billion-nyc-taxi-kdb.html (it's overall a fantastic series of blog posts).

If you can give me some information about the IP address or the IP range, I can take a closer look. I guess it is because of IPv4 trading or IP address shuffling. As far as I know, Hertzer, like many hosting companies, is buying IPv4 addresses around the world. Here is an article on the IPv4 trades: https://tech.marksblogg.com/ipinfo-free-ip-address-location-database.h… When a company buys an IP address block or relocates an IP block from one of its data centers to another, the location of those IP addresses changes. If your IP address is static, but we have made an error in geolocation, I would love to take a closer look. You can email our support ([email protected]) and send a link to the comment. We can discuss it further from there.

I have just looked up those charts on Timescales website [1] and I am a bit surprised. Never extensively used any of those DBs, but I have seen their sources and must say that expected bigger gaps [2]. Also worth looking: the Taxi Rides Benchmark on Postgres vs Clickhouse [3]. [1]: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-… [2]: https://www.pradeepchhetri.xyz/clickhousevstimescaledb/ [3]: https://tech.marksblogg.com/benchmarks.html

Splitgraph co-founder here. cstore_fdw is great (we even use it in Splitgraph to store data [0])! It's not as fast as purpose-built columnar stores like MonetDB. However, it plugs seamlessly into PostgreSQL and supports all types, even those added via extensions. Read performance for OLAP workloads like aggregations is better than PostgreSQL [1] and it has a much smaller IO load and disk footprint (you can get long runs of similar values in column-oriented storage, which compress better). As a nice bonus, you can simply swap cstore_fdw files in and out of the database without having to "load" them into PostgreSQL. We use this idea too to enable data sharing. [0] https://www.splitgraph.com/docs/concepts/objects [1] https://tech.marksblogg.com/billion-nyc-taxi-rides-postgresql.html

Sharing for anyone who didn't know there is a very good dataset you can use now. If you don't have a nvme ssd in your computer, I highly recommend getting one for fast i/o. http://commoncrawl.org/ http://commoncrawl.org/the-data/ http://index.commoncrawl.org/ related.. Mark's blog is amazing and worth more than any data science degree imho. https://tech.marksblogg.com/petabytes-of-website-data-spark-emr.html https://tech.marksblogg.com

This is fantastic, thanks so much for putting this together. In the past I've relied a bit too much on benchmarks here [0] for assessment of warehousing solutions, but it's pretty clearly skewed toward certain query patterns due to operating on a single table (no joins) and limited aggregation. Using TPC with a wider variety of queries and evaluating on dimensions other than performance provides much better perspective. [0] http://tech.marksblogg.com/benchmarks.html

Great to see more competition in GPU+DW space! Some questions: 1. How does BlazingDB compare to MapD ? 2. How do you skip ingest - are you using Apache Arrow like Dremio as an efficient data representation format? 3. Do you have any benchmarks, or maybe where would you see BlazingDB on this list? http://tech.marksblogg.com/benchmarks.html using same hardware as MapD ? 4. Can you run your solution in a cluster? :)

I absolutely love this blog series. Can't wait to read what's next :) First time I noticed (mention of) recap at http://tech.marksblogg.com/benchmarks.html

To be clear, I love Postgres. We use Postgres as our app db. But clickhouse is hands down better for analytical workloads[0] (think aggregate queries over a billion rows). > Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Agreed. Fortunately we control the processes that insert data and can handle these issues during ingress. > But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse Yes! This was surprisingly easy. We set up a way[1] for users to connect their pg data in Luabase. 0 - https://tech.marksblogg.com/benchmarks.html 1 - https://luabase.notion.site/Postgres-c6abe018218d49348ad754982ac4ee25

And you think https://tech.marksblogg.com/billion-nyc-taxi-rides-clickhouse-cluster.… for example is something that can be considered fast? It takes the user 55 minutes just to load its data into a state so that it can be "queryable". After importing then they spend 34 more minutes making the data into a columnar representation. Alright so 89 minutes in and we still haven't run queries. Oh but its not distribute yet. Darn I have to run some non standard sql commands like CREATE TABLE trips_mergetree_x3 AS trips_mergetree_third ENGINE = Distributed(perftest_3shards, default, trips_mergetree_third, rand()); Ok can I query my data yet? No you have to move it into this distributed representation and that takes 15 more minutes. Oh ok... And now? Yes you can run your queries but they aren't really very fast. SELECT cab_type, count(*) FROM trips_mergetree_x3 GROUP BY cab_type; Can take 2.5 seconds on a 108 cpu core cluster for only 1.1BN rows? Thats not fast. That's particularly slow given that requires you to ingest and optimize your data. Maybe you want to show us an example of some simple tests you have run with blazing and clickhouse. As I read it now its not worth our time to look into becuase its so very different from what we are trying to offer which is: Connect to your files wherever you have them ETL quickly Train / Classify Move on!

> How suitable or unsuitable is APL for matrix multiplication Matrix-multiply is just plus-dot-times or +.× > which language has faster execution times, Python or APL? This is tricky to answer. For most problems, I'd say APL, but this is isn't quite fair. A competent Python programmer who has made it "as fast as possible" without resorting to extensions will be creamed by a novice APL programmer, so you need to consider third-party modules like NumPy just to compete. Now NumPy is very well-optimised, but you'll really struggle to do well against k/q[1] an APLish that actually focuses on performance, and if you run into a problem where it doesn't, there are extensions you can use to eke out even more performance. At this point, you might try using Tensorflow -- which gives Python a great edge -- but are we really programming Python anymore? kdb+ can use Tensorflow as a library as well... [1]: http://tech.marksblogg.com/billion-nyc-taxi-kdb.html fastest non-GPU

A great article, and I am a big fan of algolia, Citus and Redshift. However this article ends up making an odd apples to oranges comparison. They state that "However, achieving sub-second aggregation performances on very large datasets is prohibitively expensive with RedShift", this suggests that they want to do sub-second aggregations across raw event data. However, later in the article, the solution they build is to use rollup tables for sub-second responses. You can also do rollup tables in Redshift, and I can assure you (if you enable the fast query acceleration option) you can get sub-second queries from the rolled up lower-cardinality tables. If you want even better response times, you can store the rollups in plain old Postgres and use something like dblink or postgres_fdw to perform the periodic aggregations on Redshift and insert into the local rollup tables (see [1]). In this model the solution ends up being very similar to their solution with Citus.... and I would predict that this is cheaper than Citus Cloud as Redshift really is a great price point for a hosted system. So the question of performing sub-second aggregations across the raw data remains unanswered... however that really is the ideal end game as you can then offer way more flexibility in terms of filtering than any rollup based solution. Right now, research suggests Clickhouse, Redshift or BigQuery are probably the fastest solutions for that. Not sure about Druid, I dont know it. GPU databasees…

https://news.ycombinator.com/item?id=43256349 https://www.usgs.gov/centers/eros/science/usgs-eros-archive-products-o… https://tech.marksblogg.com/satellogic-open-data-feed.html https://planetarycomputer.microsoft.com/

XOR is used in one of the LEET fizzbuzz answers: https://tech.marksblogg.com/fastest-fizz-buzz.html

https://tech.marksblogg.com/fastest-fizz-buzz.html Not sure I'd call writing Assembly "elegant" but hey!

Nothing matches kdb's speed except GPU-accelerated DBs: https://tech.marksblogg.com/benchmarks.html

Waay faster. In my experience it has been the fastest OLAP db I've ever used, and by a wide margin. I don't think any other system gets close for the price/performance ratio you get with ClickHouse. In this blog [1] you can find a very nice comparison of different dbs on the same dataset/same queries The fastest Presto benchmark there [2] vs ClickHouse on a single node core i5 [3] vs ClickHouse in cluster mode [4] [1] https://tech.marksblogg.com/benchmarks.html [2] https://tech.marksblogg.com/billion-nyc-taxi-rides-spark-2-4-versus-pr… [3] https://tech.marksblogg.com/billion-nyc-taxi-clickhouse.html NOTE: not really comparable, since it's on single machine, but I think it really shows what ClickHouse is capable of [4] https://tech.marksblogg.com/billion-nyc-taxi-rides-clickhouse-cluster.…

It's pretty well known that GPU crushes CPU in raw analytics DB performance, e.g. all the top ones here are GPU based: https://tech.marksblogg.com/benchmarks.html Question is how does it perform when the working set doesn't fit in GPU memory?

I’ve used Presto and Athena and neither could really hold a candle to ClickHouse. Presto was handy, but I’d personally much rather go to the effort of getting things into a proper columnar database than put up with the quirks of the likes of Presto. If you want, there’s a detailed analysis of various db’s performance on various tasks here: https://tech.marksblogg.com/benchmarks.html It’s one of the top performing ones, even the single machine setup comfortably beats the likes of Presto, Athena, etc

> ”K programs routinely outperform hand-coded C. This is theoretically impossible. K compiles into C. Every k program has a C equivalent that runs exactly as fast. Yet it is true in practice. Why? Because it is easier to see your error in four lines of code than in four hundred lines of C.” How true is this? I hear this claim a lot but haven't seen any real benchmarks. E.g, https://benchmarksgame-team.pages.debian.net/benchmarksgame/index.html doesn't have K or any related languages. I'm guessing K is fast due to autovectorization for certain cases, but are there benchmarks that provide hard numbers? The existence of a benchmark prohibition clause in the kdb license makes me skeptical of its performance claims. https://tech.marksblogg.com/benchmarks.html has a kdb benchmark, but due to the use of Xeon Phis, it can't be compared with other benchmarks there.

related: benchmarks from various approaches https://tech.marksblogg.com/benchmarks.html

> https://tech.marksblogg.com/omnisci-macos-macbookpro-mbp.html Is this what a keyword-stuffed URL looks like? This is terrible! This does nothing at all to communicate semantic meaning about the post's content

The same blog has other benchmarks[1], one of which is SQLite[2]. [1] https://tech.marksblogg.com/benchmarks.html [2] https://tech.marksblogg.com/billion-nyc-taxi-rides-sqlite-parquet-hdfs…

Mark did a benchmark of SQLite using its internal file format a few years ago (https://tech.marksblogg.com/billion-nyc-taxi-rides-sqlite-parquet-hdfs… clocking the import at 5.5 hours. It looks like this was done though on a spinning disk, so given a proper SSD, and a newer version of SQLite, it might be much faster.

For a list of benchmarks by the same author. https://tech.marksblogg.com/benchmarks.html Caveat: these benchmarks only test the simplest of operations like aggregation (GROUP BY, COUNT, AVG) and sorts (ORDER BY). No JOINs or window operations are performed. Even basic filtering (WHERE) doesn't seem to have been tested. YMMV.