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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
