Re: Different Autovacuum Settings on Master and Replica in Streaming Replication

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(at)lelarge(dot)info>
wrote:

> Hi,
>
> Le ven. 17 janv. 2025 à 15:38, Franjo Stipanovic <fritzfs(at)gmail(dot)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