On 3/31/2015 1:58 AM, Fabio Ugo Venchiarutti wrote:
> 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
>
>
Have you timed it? It sounds like it should work ok, but just to play
devils advocate it sounds a little over complex.
Heres my thinking: Have one index, have lots of read-only nodes, do the
connection-level routing. I think the PG caching and OS caching will be
smart enough to cache the parts of the index that's hot per node. You
are also less likely to confuse the planner.
For example, I have one db, with web hits logging, with a timestamp
index. Its upward of 25 gig, but I only every query the last 24 hours
to get ip/session/hit counts. That part of the index/db is cached very
well and responds very quick. If I ever hit data that's many days old
it slow's down hitting the HD.
I don't know for sure which method would be faster though. The only
real difference between the two methods is the number of indexes. I'd
bet there is no speed difference between them. PG will figure out what
data is hot and cache it.
-Andy
