So far, we have learned a lot about replication. However, it is important to take a look at replication conflicts. The main question that arises is this: how can a conflict ever happen in the first place?

Consider the following example:

The problem here is that the master does not know that there is a transaction happening on the slave. Therefore, the DROP TABLE command does not block until the reading transaction is gone. If those two transactions happened on the same node, this would, of course, be the case. However, we are looking at two servers here. The DROP TABLE command will execute normally, and a request to kill those data files on disk will reach the slave through the transaction log. The slave is not in trouble: if the table is removed from disk, the SELECT clause has to die—if the slave waits for the SELECT clause to complete before applying WAL, it might fall hopelessly behind.

The ideal solution is a compromise that can be controlled using a configuration variable:

max_standby_streaming_delay = 30s
         # max delay before canceling queries
         # when reading streaming WAL;

The idea is to wait for 30 seconds before resolving the conflict by killing the query on the slave. Depending on our application, we might want to change this variable to a more or less aggressive setting. Note that 30 seconds is for the entire replication stream and not for a single query. It might be that a single query is killed a lot earlier because some other query has already waited for some time.

While the DROP TABLE command is clearly a conflict, there are some operations that are less obvious. Here is an example:

BEGIN;
...
DELETE FROM tab WHERE id < 10000;
COMMIT;
...
VACUUM tab;

Once again, let's assume that there is a long-running SELECT clause happening on the slave. The DELETE clause is clearly not the problem here as it only flags the row as deleted—it doesn't actually remove it. The commit isn't a problem either, because it simply marks the transaction as done. Physically, the row is still there.

The problem starts when an operation such as VACUUM kicks in. It will destroy the row on disk. Of course, these changes will make it to WAL and eventually reach the slave, which will then be in trouble.

To prevent typical problems caused by standard OLTP workloads, the PostgreSQL development team has introduced a config variable:

hot_standby_feedback = off 
         # send info from standby to prevent 
         # query conflicts 

If this setting is on, the slave will periodically send the oldest transaction ID to the master. VACUUM will then know that there is an older transaction going on somewhere in the system and defer the cleanup age to a later point when it is safe to clean out those rows. In fact, the hot_standby_feedback parameter causes the same effect as a long transaction on the master.

As we can see, the hot_standby_feedback parameter is off by default. Why is that the case? Well, there is a good reason for this: if it is off, a slave does not have a real impact on the master. Transaction log streaming does not consume a lot of CPU power, making streaming replication cheap and efficient. However, if a slave (which might not even be under our control) keeps transactions open for too long, our master might suffer from table bloat due to late cleanup. In a default setup, this is less desirable than reduced conflicts.

Having hot_standby_feedback = on will usually avoid 99% of all OLTP-related conflicts, which is especially important if your transactions take longer than just a couple of milliseconds.