Обсуждение: is pg_log_standby_snapshot() really needed?
Hi, I'm testing the ability to have a logical replica subscribed from a standby. Of course, I'm doing this in a laboratory with no activity so everything get stuck after creating the subscription (the main slot). This is clearly because every time it will create a temp slot for copy a table it needs the running xacts from the primary. Now, I was solving this by executing CHECKPOINT on the primary, and also noted that pg_switch_wal() works too. After that, I read about pg_log_standby_snapshot(). So, I wonder if that function is really needed because as I said I solved it with already existing functionality. Or if it is really needed maybe it is a bug that a CHECKPOINT and pg_switch_wal() have the same effect? -- Jaime Casanova Director de Servicios Profesionales SYSTEMGUARDS - Consultores de PostgreSQL
Hi, On 6/7/23 7:32 AM, Jaime Casanova wrote: > Hi, > > I'm testing the ability to have a logical replica subscribed from a standby. > > Of course, I'm doing this in a laboratory with no activity so > everything get stuck after creating the subscription (the main slot). > This is clearly because every time it will create a temp slot for copy > a table it needs the running xacts from the primary. > > Now, I was solving this by executing CHECKPOINT on the primary, and > also noted that pg_switch_wal() works too. After that, I read about > pg_log_standby_snapshot(). > > So, I wonder if that function is really needed because as I said I > solved it with already existing functionality. Or if it is really > needed maybe it is a bug that a CHECKPOINT and pg_switch_wal() have > the same effect? > Even if CHECKPOINT and pg_switch_wal() do produce the same effect, I think they are expensive (as compare to pg_log_standby_snapshot() which does nothing but emit a xl_running_xacts). For this reason, I think pg_log_standby_snapshot() is worth to have/keep. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
On Wed, Jun 7, 2023 at 5:19 AM Drouvot, Bertrand <bertranddrouvot.pg@gmail.com> wrote: > > Hi, > > On 6/7/23 7:32 AM, Jaime Casanova wrote: > > > > So, I wonder if that function is really needed because as I said I > > solved it with already existing functionality. Or if it is really > > needed maybe it is a bug that a CHECKPOINT and pg_switch_wal() have > > the same effect? > > > > Even if CHECKPOINT and pg_switch_wal() do produce the same effect, I think > they are expensive (as compare to pg_log_standby_snapshot() which does nothing but > emit a xl_running_xacts). > > For this reason, I think pg_log_standby_snapshot() is worth to have/keep. > CHECKPOINT could be expensive in a busy system, but the problem pg_log_standby_snapshot() is solving is about a no-activity system, and in a no-activity system CHECKPOINT is very fast. Even with very low activity SUBSCRIPTION flows fine. As an example I put an INSERT happening every 10s and SUBSCRIPTION never stuck no CHECKPOINT nor pg_log_standby_snapshot() needed. -- Jaime Casanova Director de Servicios Profesionales SYSTEMGUARDS - Consultores de PostgreSQL
Hi, On 2023-06-07 13:50:30 -0500, Jaime Casanova wrote: > CHECKPOINT could be expensive in a busy system, but the problem > pg_log_standby_snapshot() is solving is about a no-activity system, > and in a no-activity system CHECKPOINT is very fast. There's no easy way for the subscriber to know if the system is active or not. The only realistic way is to unconditionally issue the relevant command. And that's not at all ok for CHECKPOINT. > Even with very low activity SUBSCRIPTION flows fine. As an example I > put an INSERT happening every 10s and SUBSCRIPTION never stuck no > CHECKPOINT nor pg_log_standby_snapshot() needed. Nobody forces you to issue pg_log_standby_snapshot(). If things work fine without it for you, cool. But it's pretty trivial to see that it doesn't always: With pg_log_standby_snapshot() as normal, recovery/035_standby_logical_decoding takes 15.63s on my machine. Without it I lost patience after 2 minutes. And the test was only at the start (8 out of 78 subtests). Greetings, Andres Freund
Hi, On 6/7/23 8:50 PM, Jaime Casanova wrote: > On Wed, Jun 7, 2023 at 5:19 AM Drouvot, Bertrand > <bertranddrouvot.pg@gmail.com> wrote: >> >> Hi, >> >> On 6/7/23 7:32 AM, Jaime Casanova wrote: >>> >>> So, I wonder if that function is really needed because as I said I >>> solved it with already existing functionality. Or if it is really >>> needed maybe it is a bug that a CHECKPOINT and pg_switch_wal() have >>> the same effect? >>> >> >> Even if CHECKPOINT and pg_switch_wal() do produce the same effect, I think >> they are expensive (as compare to pg_log_standby_snapshot() which does nothing but >> emit a xl_running_xacts). >> >> For this reason, I think pg_log_standby_snapshot() is worth to have/keep. >> > > CHECKPOINT could be expensive in a busy system, but the problem > pg_log_standby_snapshot() is solving is about a no-activity system, > and in a no-activity system CHECKPOINT is very fast. a no-activity system at the time the logical replication slot is being created. Means at the time the system is "non active" it may be possible that the checkpoint would still have a lot to do. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
On Thu, Jun 8, 2023 at 12:12 AM Drouvot, Bertrand <bertranddrouvot.pg@gmail.com> wrote: > > a no-activity system at the time the logical replication slot is being created. > Means at the time the system is "non active" it may be possible that the checkpoint > would still have a lot to do. > ok, this doesn't deserve that much attention anyway... It doesn't seem to do any harm, so I'm not spending more time on it -- Jaime Casanova Director de Servicios Profesionales SYSTEMGUARDS - Consultores de PostgreSQL