Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tYtcj-00GKA8-GG for pgsql-general@arkaria.postgresql.org; Fri, 17 Jan 2025 21:11:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tYtca-006MPd-N8 for pgsql-general@arkaria.postgresql.org; Fri, 17 Jan 2025 21:11:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tYtca-006MMZ-7W for pgsql-general@lists.postgresql.org; Fri, 17 Jan 2025 21:11:16 +0000 Received: from mail-qv1-xf2a.google.com ([2607:f8b0:4864:20::f2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tYtcX-0005Od-1S for pgsql-general@lists.postgresql.org; Fri, 17 Jan 2025 21:11:16 +0000 Received: by mail-qv1-xf2a.google.com with SMTP id 6a1803df08f44-6d8ece4937fso21801306d6.2 for ; Fri, 17 Jan 2025 13:11:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lelarge-info.20230601.gappssmtp.com; s=20230601; t=1737148272; x=1737753072; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=3lEf6JzOpjjgctnJuBcXSAXcvQs+1w4JP5Iv+nI+EA8=; b=rMFBk3fkWy3YaFs4cnvZxF9Q7Q0vpMC3VGs5hNnLU9BnxoT6XlpAIayto7Zm0CiCsG 8sTLIqCu6aJsf4+Htb2BsA35jdJY+O5Agc4zwzrNOFzl0G6H5RCadzDtPSBh4kTVJZ2e qez1a7MBDHCUy5KWWzur4iBmJlkHQyCCL6arpBAc1GtGwuTkkXpOjRjk6lcHT40gPnbx VXKhILGHuNVQRWWXTNtx2rVKTLBmt69pkmpWhcICWfUhcmA6OQIZmCTyW4f4VzO2iwQz BYSOnB0LTFczL0LzgyoLUEKXsM85fhgNcsnHKiyMhM8oQwmtAlnUYIk3heX+E42NZqkw bwFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737148272; x=1737753072; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=3lEf6JzOpjjgctnJuBcXSAXcvQs+1w4JP5Iv+nI+EA8=; b=DYcaxOndcl8QOj3KR0ByRh/g4e53whxuhQ1HXvoUcbaqSSv/XEW0ttnuoV2RvJDEbW u4hAYtv6hzQJ5MQ4pxOlibqw67pp4zdgzkWpK53H8+DBOZGJP10t6wvDRu/uqjGMhYcR /KHHZLpJWNcy+Z6O0lLviPhv7/MViGLkS4VCdXrsTNJs0/Z9EGrWBp0FZ4zoddo+RcZm cKdItUY6glBUaAWImCoUUccO5vMmr4w679KnvzDWIKGd/Ff0dVNV6nlTfHNMb5lI9xes qyrHYRKWCJbxjj2mZXvc34PVqdgJPnNU/e0HphPeb+lHI4VrCR4BpYIFCR89pwo9IJWG HbTA== X-Forwarded-Encrypted: i=1; AJvYcCVzsmWrVmc/ITi+hD8oPYoDuAgnodx1HfPqfCiJb+f7zWNriI0ARoZo/prc1wavnUX7IrUmbNUYDWg2tTf5@lists.postgresql.org X-Gm-Message-State: AOJu0Yyo7eYsTwgBjt5CCiwqF9J233EzjLRhYk59mcgohW/mXps3vR1d H43HhL290WjMr7HYZVWzzKcLzvvmieh4JP9yWbi5yIciqQvrLSMndTNvVC9KxiIAQCyIGPGojXt +ODa0uDmGvAVS9IFnpar9NmnVAHwp+1gOocP+CQ== X-Gm-Gg: ASbGncuLzKqGsba6HX9U/AOTdOtolhRfNgUhXHrcGVEt5c81gMmGIgFXzm0X1aFnZeo YTkZNUrVtr3tbgEbLGReo0Tda0M6lS9T/XTONjmbKtibBEOsnuzfxYpACOPKyF5tCaiwlYeUb X-Google-Smtp-Source: AGHT+IHvHHLdPm52O7R7BoHLSex5UatwvRWjBbQ2zt4EoU59pa51Jnv5oy2y3aVkkgl7RLmzvnd1W7XVRBRioPNd7F4= X-Received: by 2002:a05:6214:5014:b0:6e1:a5c1:d534 with SMTP id 6a1803df08f44-6e1b226452dmr64909646d6.41.1737148271880; Fri, 17 Jan 2025 13:11:11 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Guillaume Lelarge Date: Fri, 17 Jan 2025 22:11:01 +0100 X-Gm-Features: AbW1kvZT_wki1hK81XGUuHns_td46frax2ZGxQz_0j5ko0mvkgQPxZHCejhzv2A Message-ID: Subject: Re: Different Autovacuum Settings on Master and Replica in Streaming Replication To: Franjo Stipanovic Cc: Tom Lane , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ab0091062bed590a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ab0091062bed590a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Le ven. 17 janv. 2025 =C3=A0 18:12, Franjo Stipanovic a =C3=A9crit : > 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 sens= e? > 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. > Right. It makes no sense to replicate the activity statistics of the primary to the secondary. You'll get the activity statistics of the primary on the primary, and the activity statistics of the secondary on the secondary. And so, all activity statistics columns related to write operations will be either NULL or 0 on the secondary/replica because there are no write operations on a replica. Seems like only seq_scan, last_seq_scan, idx_scan, ... are fulfilled. > > Those come from some read operations on the replica. > 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. > > Right. Those aren't activity statistics. They are data statistics, and they are replicated. They're needed by the planner on the primary and on the secondary. > On Fri, Jan 17, 2025 at 4:34=E2=80=AFPM Guillaume Lelarge > wrote: > >> Hi, >> >> Le ven. 17 janv. 2025 =C3=A0 15:38, Franjo Stipanovic a >> =C3=A9crit : >> >>> Question regarding autovacuum settings in a PostgreSQL streaming >>> replication setup. Specifically, I am curious about whether it is possi= ble >>> (and advisable) to have different values for the following settings on = the >>> 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 = on >>> 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 repli= cas. >> >> >>> 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, onl= y >>> in logical replication. >>> >> >> You're right, it's not possible. >> >> >> -- >> Guillaume. >> > > > -- > best wishes, > Franjo Stipanovic > --=20 Guillaume. --000000000000ab0091062bed590a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Le=C2=A0ven. 17 janv. 2025 =C3=A0=C2=A018:12,= Franjo Stipanovic <fritzfs@gmail.c= om> a =C3=A9crit=C2=A0:
Thank you, Guillaume, Tom.

In that case, querying=C2=A0pg_stat_user_tables on replica for last_vacu= um, last_autovacuum, last_analyze, and last_autoanalyze columns makes no se= nse? Those specific columns=C2=A0are not transferred from the master? The s= ame goes for n_* columns? Currently, on my replica, all those columns are n= ull and 0, while on the master they have a value.
<= div>
Right. It makes no sense to replicate the activity stati= stics of the primary to the secondary. You'll get the activity statisti= cs of the primary on the primary, and the activity statistics of the second= ary on the secondary.

And so, all activity statist= ics columns related to write operations will be either NULL or 0 on the sec= ondary/replica because there are no write operations on a replica.

Seems like only seq_scan, last_seq_scan, idx_scan, ... are fulfil= led.


Those come = from some read operations on the replica.
=C2=A0
Also, data behind pg_stats (n_distinct, most_common_vals, most_common_freq= s, etc) is replicated, right? It seems that we can query those just fine.


Right. Those aren= 't activity statistics. They are data statistics, and they are replicat= ed. They're needed by the planner on the primary and on the secondary.<= br>

<= div dir=3D"ltr">

On Fri, Jan 17, 2025 at 4:34=E2=80=AFPM Guillaum= e Lelarge <g= uillaume@lelarge.info> wrote:
Hi,

Le=C2=A0ven. = 17 janv. 2025 =C3=A0=C2=A015:38, Franjo Stipanovic <fritzfs@gmail.com> a =C3=A9crit= =C2=A0:

Question regarding autovacuum settings in a PostgreSQL streamin= g replication setup. Specifically, I am curious about whether it is possibl= e (and advisable) to have different values for the following settings on th= e master and replica databases:
-=C2=A0autovacuum_vacuum_scale_factor- autovacuum_analyze_scale_factor

Can the above settings be configur= ed differently on the master and replica databases in a streaming replicati= on setup? I just tested this on AWS RDS Postgres.=C2=A0


They could but it doesn't matter. VACUUM and ANA= LYZE aren't executed on replicas because they are write operations, and= you can't write on replicas.
=C2=A0

How would those = different settings interact with replication, for example, if I apply chang= e on master, would it be replicated to replica and override my replica sett= ing?=C2=A0

Changes on the postgresq.conf file ar= en't replicated, but ALTER TABLE ... (SET autovacuum...) are.
=

I was= expecting that this is not possible in streaming replication, only in logi= cal replication.


You're right= , it's not possible.


--
Guillaume.


--
best wishes,
Franjo Stipanovic


--
Guillaume.
<= /div>
--000000000000ab0091062bed590a--