3 ms·

Are SQL window functions really so bad? Setting up a completely separate system for a particular kind of query seems like the wrong approach.

dialtone 10y ago
The approach here is very different. It's not that window functions are bad, but that the traditional SQL database, or even column oriented database, doesn't have a disk format that is versed to this type of analysis for the high volumes that AdRoll has. TrailDB can filter through 10s of millions of events per second on a Macbook, just storing billions of events in a more traditional DB or warehouse would use far more than the space that TrailDBs use. On top of that it's relatively easy to build visualisations on top of these databases with an interactive UI to do exploration. So the window function being complicated was perhaps just the spark but there are real advantages in speed, efficiency and expressiveness in this type of database.
jflatow 10y ago
Its not really about SQL or NoSQL - I believe AdRoll internally has systems that map SQL queries onto TrailDB where its needed. Its about efficiency. TrailDB compresses the hell out of event streams grouped by some key (typically user). I think it's generally something like 100x - it does so by making certain assumptions about your data. Similarly, when operating on your event sequences, many of those operations can be performed as integer instructions on the compressed data. TrailDB gives you order(s) of magnitude of leverage. That's the difference between doing analytics on a single machine and a cluster in many cases. There aren't too many other databases that were designed to gracefully handle tens of trillions of events, so its really not surprising that you need a different tool for that job.
snaky 10y ago
Efficient data structures and algos, carefully tuned for one particular scenario. Seems like a good candidate for the one of PostgreSQL pluggable storage engines we will likely see in the future[1]. I hope the data structures are not patented. [1] http://akorotkov.github.io/blog/2016/04/06/extensible-access-methods/ http://akorotkov.github.io/blog/2016/04/06/extensible-access...

dialtone 10y ago

The approach here is very different. It's not that window functions are bad, but that the traditional SQL database, or even column oriented database, doesn't have a disk format that is versed to this type of analysis for the high volumes that AdRoll has. TrailDB can filter through 10s of millions of events per second on a Macbook, just storing billions of events in a more traditional DB or warehouse would use far more than the space that TrailDBs use. On top of that it's relatively easy to build visualisations on top of these databases with an interactive UI to do exploration. So the window function being complicated was perhaps just the spark but there are real advantages in speed, efficiency and expressiveness in this type of database.

jflatow 10y ago

Its not really about SQL or NoSQL - I believe AdRoll internally has systems that map SQL queries onto TrailDB where its needed. Its about efficiency. TrailDB compresses the hell out of event streams grouped by some key (typically user). I think it's generally something like 100x - it does so by making certain assumptions about your data. Similarly, when operating on your event sequences, many of those operations can be performed as integer instructions on the compressed data. TrailDB gives you order(s) of magnitude of leverage. That's the difference between doing analytics on a single machine and a cluster in many cases. There aren't too many other databases that were designed to gracefully handle tens of trillions of events, so its really not surprising that you need a different tool for that job.

snaky 10y ago

Efficient data structures and algos, carefully tuned for one particular scenario. Seems like a good candidate for the one of PostgreSQL pluggable storage engines we will likely see in the future[1]. I hope the data structures are not patented. [1] http://akorotkov.github.io/blog/2016/04/06/extensible-access-methods/ http://akorotkov.github.io/blog/2016/04/06/extensible-access...