Unreliability · Substack · by Obakeng Mosadi
Visualizing Local Municipalities Spend with ClickHouse
What the article says, in short
At 10 million rows, Clickhouse was ~9 times faster (40ms versus 354 ms) on the trend query.
In the first run, 32 simultaneous trend queries took 12 seconds each, caused by one function call.
Stellenbosch's derived debtor age buckets add up to the source total of R1,134,152,973.
Nelson Mandela Bay reports around R24bn a year in capital renewal for 2020–2023, then R0.7bn in 2024.
Read the full article ↗
Ideas will appear here.
The benchmark
Tuning ClickHouse queries
Building the data pipeline
Surprises in Treasury data
Running MapMyCity
Measurement
At 10 million rows, ClickHouse ran my national trend query in 40 ms. Postgres took 354 ms, so ClickHouse was about 9 times faster.
From the “Why Clickhouse?” section
Moment
My first benchmark showed ClickHouse collapsing under load: 32 simultaneous trend queries took 12 seconds each. The cause turned out to be one function call.
From the “Why Clickhouse?” section
Measurement
For Stellenbosch's aged debtors, the age buckets we derived (R363,954,840 under 30 days down to R0 over a year) add up exactly to Treasury's total of R1,134,152,973.
From the “From Raw Data To An Analytical Model” section
Finding
Some Treasury numbers add up and still look wrong. Nelson Mandela Bay reports around R24bn a year in capital renewal for 2020 to 2023, against R0.7bn in 2024.
From the “What I Learned” section
Pennywurth found 11 more ideas in the same article.
You pick the ones worth posting.