pg_repack slows down PostgreSQL replication

Question

I have a master PostgreSQL 9.5 server and a standby server. For replication I use repmgr (WAL streaming). Typically the delay between master and standby is <5s:

$ psql -t -c "SELECT extract(epoch from now() - pg_last_xact_replay_timestamp());"
  0.044554

Periodically pg_repack is invoked on master in order to optimize indexes and tables. Repacking tables causes massive changes in WAL streaming and significantly slows down replication, so that the difference between master and standby could be more than 1h.

Is there a way how to reduce such delay? Is it possible to synchronize newly incoming data with higher priority than repack changes?

Answer 1

I was about to ask a similar question, but it is related to the replication streaming ("physical"/block related) that streams/replicates the actual data writes to the disk(s). With vacuum full (reindexes), and truncates/restores, and now pg_repack, the tables are rewritten to disk, causing a lot of data writes that need to be streamed to the other side...

Thus, no, I don't believe you'll be able to do the "prioritization" as the moment the rebuilt table are "swapped" into active table, the new updates/writes on the master, will be going to the rebuild table, not the old table, and then the replica needs that table "available"!

I've been getting into the habit of killing the replication, doing the major data changes (perhaps a good practise to have it as a "backup" available before the data changes) and then doing a new full pg_basebackup/replication restarts

Hope this helps to explain the situation you are in and how I've been solving it till now :)

That said do go read: https://www.depesz.com/2013/06/21/bloat-removal-by-tuples-moving/

Depesz explains a mechanism that helped him move data to the beginning of the table "on the fly" with data available all the time using code similar to:

with x as (
delete from test where id in (999997,999998,999999) returning *
)
insert into test
    select * from x;

this is then run in batches, with $vacuum$ statements running together to clean up the space. Doing this in a slow/managed method, you could be able to do the "repack" with not too far behind replicas.

Just be careful of triggers on update/insert/delete !

Answer 2

The WAL is a sequential log, so a segment in the WAL can only be applied if all the preceding segments have been applied. So it makes no logical sense to prioritise some WAL segments over others.

When you use pg_repack you are making changes to the database data, so it's written to the WAL. When you say that "this slows replication", I would probably refine that as "it delays replication", because of the volume of replication required, rather than slowing it.

The only solution I can see is to either delay the pg_repack to a less critical time (if there is one), or modify the method of optimising the tables and indexes to minimise the amount of WAL writes (such as what @Hvisage pointed to).

Answer 3

You can artificially slow down pg_repack by limiting the CPU it can use, by using cpulimit.

I've tested it in production where I was repacking a 3TB table, which ended up causing a replication lag of ~30min while doing inserts into the new table. So, those inserts had to be slowed down and I ended up limiting the CPU to 40% using cpulimit.

sudo cpulimit -p <postgres-pid-of-pg_repack> -l 40

40 is not a universal number, but you can come up with your own by doing a hit a trial by looking at the replication lag.