Different Autovacuum Settings on Master and Replica in Streaming Replication

Franjo Stipanovic <fritzfs@gmail.com> writes:

Question regarding autovacuum settings in a PostgreSQL streaming
replication setup. Specifically, I am curious about whether it is possible
(and advisable) to have different values for the following settings on the
master and replica databases:
- autovacuum_vacuum_scale_factor
- autovacuum_analyze_scale_factor

A streaming replica will not run vacuum on its own, as it just absorbs
the physical changes made by vacuum on the master. Hence, it matters
not at all how you have those values set on the replica.

If the replica is promoted to master, then it will start to use
whatever settings it has (and no, postgresql.conf is not copied
by the replication infrastructure).

regards, tom lane

Hi,

Le ven. 17 janv. 2025 à 15:38, Franjo Stipanovic <fritzfs@gmail.com> a
écrit :

Question regarding autovacuum settings in a PostgreSQL streaming
replication setup. Specifically, I am curious about whether it is possible
(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 replicas.

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.

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 PM Guillaume Lelarge <guillaume@lelarge.info>
wrote:

Hi,

Le ven. 17 janv. 2025 à 15:38, Franjo Stipanovic <fritzfs@gmail.com> a
écrit :

Question regarding autovacuum settings in a PostgreSQL streaming
replication setup. Specifically, I am curious about whether it is possible
(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 replicas.

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.

--
best wishes,
Franjo Stipanovic

Le ven. 17 janv. 2025 à 18:12, Franjo Stipanovic <fritzfs@gmail.com> a
écrit :

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.

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 PM Guillaume Lelarge <guillaume@lelarge.info>
wrote:

Hi,

Le ven. 17 janv. 2025 à 15:38, Franjo Stipanovic <fritzfs@gmail.com> a
écrit :

Question regarding autovacuum settings in a PostgreSQL streaming
replication setup. Specifically, I am curious about whether it is possible
(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 replicas.

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.

--
best wishes,
Franjo Stipanovic

--
Guillaume.