Re: Optimize update query

W dniu 28.11.2012 15:07, Shaun Thomas pisze:
> On 11/28/2012 06:57 AM, Niels Kristian Schjødt wrote:
>
> Before I go crazy, here... you really need to tell us what "not enough"
> means. You didn't provide an explain analyze, so we don't know what your
> actual performance is. But I have my suspicions.
>
>> So as you can see, it's already pretty optimized, it's just not
>> enough :-) So what can I do? the two columns last_observed_at and
>> data_source_id has an index, and it is needed elsewhere, so I can't
>> delete those.
>
> Ok, so part of your problem is that you're tying an advertising system
> directly to the database for direct updates. That's a big no-no. Any
> time you got a huge influx of views, there would be a logjam. You need
> to decouple this so you can use a second tool to load the database in
> larger batches. You'll get much higher throughput this way.

+1, sql databases has limited number of inserts/updates per second. Even
with highend hardware you won't have more than XXX operations per
second. As Thomas said, you should feed something like nosql database
from www server and use other tool to do aggregation and batch inserts
to postgresql. It will scale much better.

Marcin