Talk with the author of Streaming Replication

Yesterday I talked with Masao Fujii, the author of Streaming Replication. Streaming Replication(SR) is a long awaited feature, "built in replication" of PostgreSQL. The great feature will come with PostgreSQL 9.0, which is supposed to be released this Summer. With SR, pgpool-II will provide two important features: load balancer and failover control. Since SR is a master/slave type replication system, the standby server (slave) do not accept write queries such as INSERT, DELETE, UPDATE, CREATE TABLE and so on. Any write query sent to pgpool will be redirected to the primary server (master) and users do not need to worry about it. On the other hand, as SELECT can be executed both on the primary and the standby, pgpool will choose one of them and send the SELECT to it. This technique is known as "load balancing" and this will significantly improve the over all performance.

One thing we were worried about was, what if the connection between the primary server and the standby server goes down? There's no easy way for pgpool to know it. If pgpool does not know that, SELECT can be sent to the standby whose data is far behind the primary. Apparently this is what users want to avoid. To overcome the problem, pgpool checks the transaction log number (LSN) on both the primary and the standby. If standby's LSN is too small comparing with the one on the primary, we might have a problem. So pgpool stops the load balancing and send SELECT only to the primary. How behind is "behind" should be defined by new directive in pgpool.conf. Good thing with this is, the technique will also detects the delay because of the heavy load of the standby.

We will continue to discuss how to improve the usability of SR and pgpool-II.