Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

posthog.com

Post reply on HN
Print view View original

41–50 of 79 posts

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#41

Post by Someone »

veidelis wrote: post #36
choilive wrote: post #20

Earlier quoted context omitted.

There are some studies that suggest human brain sizes have been shrinking over the last 20,000 years. The theory is that as civilization developed the demand for individual humans to be independently intelligent has weakened because we developed a "collective brain" and also self-domesticated to be more cooperative.

Honestly there might be truth to it, I don't get the downvotes - why?

The correlation between brain volume and intelligence is fairly weak. Neanderthals had larger brains than humans, for example. Looking outside the hominids, we have fairly smart corvids with relatively tiny brains.

That means the chain of thought “brains volume decreased, so individuals must have gotten less intelligent. Yet, societies grew smarter, so there must be herd intelligence” breaks at “so individuals must have gotten less intelligent”.

I think/guess that argument may have merit when replacing brain volume by number of neurons (https://en.wikipedia.org/wiki/List_of_animals_by_number_of_n...)

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#42

Post by justAnotherHero »

That's great but I really wish you guys would do something about the llm integration, I tried using it two days ago to create a cohort of users using a sql query, and I was surprised to see that it said that it could not create cohorts for me and i had to resort to exporting data from a sql insight as a cohort cannot use a sql query. However the worst part was it just writing in the text input slowed down my m4 pro chip to less than 1 fps after 2 prompts and it really left a bad taste in my mouth.

Perhaps the next target for a 100x improvement

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#43

Post by [deleted] »

duke_of_vandals wrote: post #25
robbie-c wrote: post #15

Earlier quoted context omitted.

It took about 2 days to get a proof of concept, and about a week to get something I could ship to production. I skipped a few features for the PoC (like XML tag support, token positions), so most of the delta was adding those back in!

If you didn’t need to look at the code at all, why not write it in asm instead of Rust, and make it even faster?

[deleted]

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#44

Post by jimbokun »

duendefm wrote: post #5

Well despite my current anti AI sentiment, I have to admit that after reading the article, It was a good use of AI, done by someone with good technical skills. Still I have the feeling that this only works because of the vast accumulated knowledge pre-AI, and if everybody keeps going in this path, it will end up making everyone not advancing their knowledge at the pace they did before. I feel that this AI immersion i…

That can be said for any technology in history that made work easier.

“Whoa slow down with this ‘writing’ technology. No one will ever remember anything if they can just write it down.”

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#45

Post by ndr »

Great loop spotting!

Recently I was messing around with parquet files in Python and ended up needing to ship the results on Windows, without a Windows machine to test on.

Shipping Python to end users is half mad already, and doing it on Windows is exactly the kind of thing I don't want to spend my life maintaining.

So I figured I'd rewrite it in Go. But that meant embedding a DLL, and how would I test it? I could spin up a VM, sure. But GitHub Actions already has a Windows environment, and there was my loop: let the agent push to the repo, run tests in GHA, rinse and repeat.

In under an hour it had a full rewrite of my Python, passing every test and producing row-for-row copies of my Parquet output. And it does work on the user machine!

Spotting a loop like that is as satisfying as noticing you can walk your chess opponent into a smothered mate. Truly empowering.

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#46

Post by hyperpape »

robbie-c wrote: post #35
sscaryterry wrote: post #26

Good read, but "70x" is always misleading.

In what way? This was a geometric mean of the improvements from a small test corpus. In production, where it only parses longer SQL that didn't hit the parser cache, the mean parse time went down by 454x, across millions of parses.

Sounds like the real number is 454x faster, not 70x. Checkmate, nerd!

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#47

Post by sscaryterry »

hyperpape wrote: post #46
robbie-c wrote: post #35

Earlier quoted context omitted.

In what way? This was a geometric mean of the improvements from a small test corpus. In production, where it only parses longer SQL that didn't hit the parser cache, the mean parse time went down by 454x, across millions of parses.

Sounds like the real number is 454x faster, not 70x. Checkmate, nerd!

Go read my message again. Every time somebody states it is X time faster, and its the headline, it is pure clickbait.

Yes, you have benchmarks, they will always be synthetic. I'm not dismissing the work, just the selling of it.

You have succeeded though, I did click on it. I even said "Good read" :)

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#48

Post by Daishiman »

duendefm wrote: post #40
Daishiman wrote: post #7

Earlier quoted context omitted.

> till I have the feeling that this only works because of the vast accumulated knowledge pre-AI I'm not about to say that there's nothing new under the sun, but parsers are a really well-understood problem where 99.9% of people don't need frontier knowledge and wouldn't be in a position to use it anyway. And I don't think that people doing research on parsers would ever rely on LLMs for precisely that reason. But we'…

My point is, we have programming languages like C and C++, we have operating systems like Linux and FreeBSD, we have an empire of software and knowledge accumulated because of the intellectual battles fought by people before AI. With AI, we all are getting our coding easier (and are kind of being forced to), in a way that we will skip these kind of battles. That is, if we all use AI to make our job easier it will hav…

I'm not really sure that I agree. The LLM paradigm basically allows for the same development techniques, for better or worse, but amplified.

So if you were lazily copying the first blog result in Google, getting the first answer from an LLM is equivalent, but the output is actually likely to be better.

If you wanted to do your research on various techniques and evaluate alternatives, LLMs can amplify your capacity to research and to have specific considerations for your specific problem.

LLMs aren't going to solve people's natural inclination towards laziness.

Additionally, while it's true that people may read and learn less about the "lower" levels of software plumbing, it enables enormous possibilities of higher level thinking that before were limited by the amount of manpower you needed.

For example, with LLMs I can try different test sharding strategies or trivially change from factories to fixtures in large test suites. This would have been busywork or drudgery; now I can evaluate several architectural solutions which would not have been possible before.

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#49

Post by westurner »

Could the agent traces from this be used to improve sqlglot?

tobymao/sqlglot: Python SQL Parser and Transpiler; with tests and support for 30+ dialects: https://github.com/tobymao/sqlglot

Ibis depends upon sqlglot: https://github.com/tobymao/sqlglot/network/dependents

Re: I rewrote PostHog's SQL parser, 70x faster, while barely looking at the code

#50

Post by ncruces »

You have a grammar file in a formal language, and want to generate a faster parser in another formal language.

What's wrong with the source language that it's better to use a sufficiently smart random code generator for the target language, and then fuzz the hell out of the output of it until it behaves the same as the slow translated code, than to create a sufficiently smart compiler from the source to target languages?

I mean this sounds like if we replaced GCC with a really smart random assembly generator and a fuzzer for the output.

Post reply on HN