From: Marcin Mirosław <marcin@mejor.pl>
To: pgsql-performance@postgresql.org
Subject: Re: Optimize update query
Date: Wed, 28 Nov 2012 15:39:55 +0100
Message-ID: <50B6223B.6000100@mejor.pl> (raw)
In-Reply-To: <50B61ABF.4070000@optionshouse.com>
References: <1C199C0D-13DF-47B3-944A-B0560C6D2E5A@autouncle.com>
<50B61ABF.4070000@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marcin@mejor.pl
Subject: Re: Optimize update query
In-Reply-To: <50B6223B.6000100@mejor.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox