MIME-Version: 1.0
References: <CAJyBP14Vtwmds7vM8nO2GWaXHq3_-Dw1-_+iY0onXVJKWETUDg@mail.gmail.com>
 <CAECtzeWsUE5EV_w8yRre28hZBUXPWE-fkFrqf7__B29jWAjP3Q@mail.gmail.com>
In-Reply-To: <CAECtzeWsUE5EV_w8yRre28hZBUXPWE-fkFrqf7__B29jWAjP3Q@mail.gmail.com>
From: Franjo Stipanovic <fritzfs@gmail.com>
Date: Fri, 17 Jan 2025 18:12:07 +0100
Message-ID: <CAJyBP16sumjYjvybFF+pArMu8+ncRYA3=6mEoiLbDeV9eFFP1A@mail.gmail.com>
Subject: Re: Different Autovacuum Settings on Master and Replica in Streaming Replication
To: Guillaume Lelarge <guillaume@lelarge.info>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005c47dc062bea0388"
Archived-At: <https://www.postgresql.org/message-id/CAJyBP16sumjYjvybFF%2BpArMu8%2BncRYA3%3D6mEoiLbDeV9eFFP1A%40mail.gmail.com>
Precedence: bulk

--0000000000005c47dc062bea0388
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you, Guillaume, Tom.

In that case, querying pg_stat_user_tables on replica for last_vacuum,
last_autovacuum, last_analyze, and last_autoanalyze columns makes no sense?
Those specific columns are not transferred from the master? The same goes
for n_* columns? Currently, on my replica, all those columns are null and
0, while on the master they have a value. Seems like only seq_scan,
last_seq_scan, idx_scan, ... are fulfilled.

Also, data behind pg_stats (n_distinct, most_common_vals,
most_common_freqs, etc) is replicated, right? It seems that we can query
those just fine.


On Fri, Jan 17, 2025 at 4:34=E2=80=AFPM Guillaume Lelarge <guillaume@lelarg=
e.info>
wrote:

> Hi,
>
> Le ven. 17 janv. 2025 =C3=A0 15:38, Franjo Stipanovic <fritzfs@gmail.com>=
 a
> =C3=A9crit :
>
>> Question regarding autovacuum settings in a PostgreSQL streaming
>> replication setup. Specifically, I am curious about whether it is possib=
le
>> (and advisable) to have different values for the following settings on t=
he
>> master and replica databases:
>> - autovacuum_vacuum_scale_factor
>> - autovacuum_analyze_scale_factor
>>
>> Can the above settings be configured differently on the master and
>> replica databases in a streaming replication setup? I just tested this o=
n
>> AWS RDS Postgres.
>>
>
> They could but it doesn't matter. VACUUM and ANALYZE aren't executed on
> replicas because they are write operations, and you can't write on replic=
as.
>
>
>> How would those different settings interact with replication, for
>> example, if I apply change on master, would it be replicated to replica =
and
>> override my replica setting?
>>
> Changes on the postgresq.conf file aren't replicated, but ALTER TABLE ...
> (SET autovacuum...) are.
>
>> I was expecting that this is not possible in streaming replication, only
>> in logical replication.
>>
>
> You're right, it's not possible.
>
>
> --
> Guillaume.
>


--=20
best wishes,
Franjo Stipanovic

--0000000000005c47dc062bea0388
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thank you, Guillaume, Tom.<div><br></div><div>In that case=
, querying=C2=A0pg_stat_user_tables on replica for last_vacuum, last_autova=
cuum, last_analyze, and last_autoanalyze columns makes no sense? Those spec=
ific columns=C2=A0are not transferred from the master? The same goes for n_=
* columns? Currently, on my replica, all those columns are null and 0, whil=
e on the master they have a value. Seems like only seq_scan, last_seq_scan,=
 idx_scan, ... are fulfilled.</div><div><br></div><div>Also, data behind pg=
_stats (n_distinct, most_common_vals, most_common_freqs, etc) is replicated=
, right? It seems that we can query those just fine.</div><div><br></div></=
div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" c=
lass=3D"gmail_attr">On Fri, Jan 17, 2025 at 4:34=E2=80=AFPM Guillaume Lelar=
ge &lt;<a href=3D"mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>=
&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div=
 dir=3D"ltr"><div dir=3D"ltr">Hi,<br></div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">Le=C2=A0ven. 17 janv. 2025 =C3=A0=C2=
=A015:38, Franjo Stipanovic &lt;<a href=3D"mailto:fritzfs@gmail.com" target=
=3D"_blank">fritzfs@gmail.com</a>&gt; a =C3=A9crit=C2=A0:<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><p>Question rega=
rding autovacuum settings in a PostgreSQL streaming replication setup. Spec=
ifically, I am curious about whether it is possible (and advisable) to have=
 different values for the following settings on the master and replica data=
bases:<br>-=C2=A0autovacuum_vacuum_scale_factor<br>- autovacuum_analyze_sca=
le_factor</p><p>Can the above settings be configured differently on the mas=
ter and replica databases in a streaming replication setup? I just tested t=
his on AWS RDS Postgres.=C2=A0</p></div></blockquote><div><br></div><div>Th=
ey could but it doesn&#39;t matter. VACUUM and ANALYZE aren&#39;t executed =
on replicas because they are write operations, and you can&#39;t write on r=
eplicas.<br></div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"ltr"><p>How would those different settings intera=
ct with replication, for example, if I apply change on master, would it be =
replicated to replica and override my replica setting?=C2=A0</p></div></blo=
ckquote><div>Changes on the postgresq.conf file aren&#39;t replicated, but =
ALTER TABLE ... (SET autovacuum...) are.<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr"><p>I was expecting that this is n=
ot possible in streaming replication, only in logical replication.</p></div=
></blockquote><div><br></div><div>You&#39;re right, it&#39;s not possible.<=
/div><div><br></div><div><br></div></div><span class=3D"gmail_signature_pre=
fix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"l=
tr"><div><div dir=3D"ltr"><div>Guillaume.<br></div></div></div></div></div>=
</div>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature">best wishes,<br>Franjo Stipanovic</div>

--0000000000005c47dc062bea0388--