Re: Which replication is the best for our case ? - Mailing list pgsql-general

On Mon, Jun 29, 2015 at 6:02 AM, ben.play <benjamin.cohen@playrion.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