Mark Litwintschik

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…

And you think https://tech.marksblogg.com/billion-nyc-taxi-rides-clickhouse-cluster.html 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

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

> ”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.

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.