On Fri, Jun 05, 2015 at 11:54:01PM +0000, deavid wrote: > Thanks to everybody for answering. I wasn't expecting this attention; this > is a great community :-) > > Jim asked me about something real. Well, the problem is this showed up more > than five years ago, and keeps popping from time to time since in different > circumstances. I solved them in different ways each time, depending the > exact use-case. I wanted to generalize, because seems a good feature for > several situations; and I don't expect a solution for me as each time I hit > with this I found some way to sort it out. > As Jim said, we need here are figures for real examples, and i don't have > yet. I'll do my "homework" and email back with exact problems with exact > timing. Give me a week or two. > > Also, some of you are talking about IO. Well, it's hard to say without the > figures here, but I'm pretty sure I'm hitting CPU time only. We use SSD on > those big databases, and also in my tests i tried setting fsync=off. > > So the problem is: i see a low iowait, and CPU time for one core is at > 80-90% most of the time. I can buy more ram, better disks, or cpu's with > more cores. But one cpu core would have more-or-less the same speed no > matter how much money you invest. > > When someone wants a delayed-write index is similar to setting > "synchronous_commit = off". We want to give an OK to the backend as soon > as is possible and do this work in background. But we also want some > reliability against crashes. > > Also, if the task is done in background it may be done from other backend, > so probably several indexes could be packed at once using different backend > processes. We could use the entire cpu if our index writes aren't tied to > the session who wrote the row. > > PD: I'm very interested on existent approaches like GIN or BRIN (this one > is new to me). Thanks a lot; i'll try them in my tests.
Hi David, Here is an interesting read comparing LSM and Fractal Tree indexing: http://highscalability.com/blog/2014/8/6/tokutek-white-paper-a-comparison-of-log-structured-merge-lsm.html Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
