Greetings
I'm working for a startup and our core DB is growing rather fast.
Our target scale is large enough that we expect some of our core tables'
indexes to grow bigger than the memory on any single node over the next
couple years (our current intended design involves conventional
stream-replication-based, write-on-one-read-from-many load balancing).
We don't fancy the idea of using inheritance through partitioning due to
the maintenance overhead and our reliance on validation
constraints/triggers.
My proposal will be to instead create a number of partial indexes
covering predefined ranges of client IDs, then use a connection-level
routing mechanism that relies on what range the relevant client's data
belongs to in order to address the right node and match the right
partial index.
The idea is to have any given read-only node hold just one of the
partial indexes in its cache and never fetch index pages off its
secondary storage.
Scaling would just be a matter of increasing the partitioning density.
I'm going to assume that I'm not the first one to come up with this
strategy (and that there already is a name for it. If so, what is it?).
Is it a valid setup or am I missing some key aspect of how index
partitioning is meant to work?
TIA
Best regards
Fabio Ugo Venchiarutti
