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