Easily doable. On a 10$/month server I frequently run queries doing text operations over 120 million rows for fulltext search of an IRC client backlog.
In 64ms.
Without caching.
Using PHP.
It's definitely doable, but you'll need to heavily fine-tune your queries. My first one was at over 2 hours for the same.
> It's definitely doable, but you'll need to heavily fine-tune your queries
Misrepresentation; then it's not actually over 120 million rows. You're basically encoding which subset to actually search in the query, rather than building a proper overall schema that trivializes queries.
No.
I mean stuff like using aggregation functions to let the database build a bitset out of them, and reading from every row a value into that bitset.
I mean stuff like CLUSTER table ON (pixel_y_index, pixel, x_index) to change the order in which they are stored.
Do those two optimizations alone and you improve speed massively.
I will say that it was an implementation bug which doesn't warrant the swapping out of entire data storage layers.