Re: Which replication is the best for our case ?

I think it would help immensely if you provided details such as
table_structure, indexes the actual UPDATE query and the reason all rows of
the table must be updated.

On Mon, Jun 29, 2015 at 1:15 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Mon, Jun 29, 2015 at 6:02 AM, ben.play <benjamin(dot)cohen(at)playrion(dot)com>
> wrote:
>
>> Hi guys,
>>
>> We have a PG database with more than 400 GB of data.
>> At this moment, a cron runs each ten minutes and updates about 10 000
>> lines
>> with complex algorithms in PHP.
>>
>> Each time the cron runs, the website is almost down because some queries
>> have to make an update on the FULL table...
>>
>
> Why is it updating the full table of 400GB if it only changes 10,000 lines?
>
> If most of the rows are being updated degenerately (they update the column
> to have the same value it already has) then just add a where clause to
> filter out those degenerate updates, unless the degenerate update is needed
> for locking purposes, which is rare.
>
>
> Therefore, I'm asking if it's possible to duplicate my main database on a
>> slave server in order to run these cron on this second server... then,
>> replicate these changes on the main database (master).
>>
>
> Sounds like you are trying to use a bulldozer to change a lightbulb.
>
> Improving queries (including the effect running some queries has on the
> entire system) starts with "EXPLAIN (ANALYZE, BUFFERS)", not with
> multimaster replication.
>
> Cheers,
>
> Jeff
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.