[xq3000]

There is not a lot of specific details here but have you already tried and exhausted things like analyzing execution plans, index optimizations, index encoding, value encoding, denormalization, read replicas, and [front] caching?

[dbnewbie]

I have not looked into “index encoding”. In fact, I haven’t even heard of that, thank you for the suggestion!

In terms of execution plan, the query we are doing is relatively basic even though it includes some aggregation. The aggregation is rule (CASE) based and very simple. It feels like there is no way to quickly (sub-50ms) retrieve information from a database once you reach the high tens of millions of rows.

[xq3000]

By index encoding I just mean putting “encoded” values into the indexed column of your table. Something like this: “city:department:sku”.

This technique is a bit advanced, borrowed from hierarchical databases, and optimizes for specific queries known upfront, so it’s cool but not very flexible. There is a lot more to making it work. You can watch [1], if interested.

But I’d also +1 other suggestions here on fine tuning your db engine and just scaling up the server.

[1] https://youtu.be/jzeKPKpucS0

Disclaimer: I’m with AWS.

[dbnewbie]

Interesting, that does sound like an advanced indexing technique, but also sounds like a really good idea. It reminds me of the old flat file database formats I read about.