Re: Which replication is the best for our case ?

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