On Wed, Oct 30, 2013 at 4:23 AM, Leonardo Francalanci <m_lists@yahoo.it> wrote:
1) I haven't achieved what I need: realtime indexing. I can't query the "current 15 minutes" table efficiently. Plus, K*log(N) is not that great when you have a lot of K.
Are partitions read-only once time has moved on, or can stragglers show up that need to be inserted into older partitions?
You could periodically merge older partitions into larger tables, index those aggregated tables, then transactionally disinherit the old partitions and inherit the new aggregated one. This would keep the value of K down, at the expense of re-writing data multiple times (but all method write data multiple times, some just hide it from you).
2) I'm not suggesting that this is top priority. I'm asking if there's something else, other than "we don't have time for this", that I don't know. In fact, I don't even know if those indexes types would really help in my (specific) case. That's why my original question was "why aren't there developments in this area": I didn't mean to imply someone should do it. I just wanted to know if those indexes were already discussed (and maybe dismissed for some reason) in the past...
There have been several ideas discussed, but not a whole lot of time to implement them.
By the way, what is the transaction structure of your inserts? Are they large batches between commits, or is each row committed?