21

Running PostgreSQL 9.0.1, with streaming replication keeping one read-only mirror instance up to date. Auto-vaccuum is on on the primary, except for a few tables which are not vacuumed by the auto-vacuum daemon, in an effort to reduce business-hour IO. These tables are "materialised views".

Each night at midnight, we run a vacuum across the database in order to clean up those tables that are excluded from the auto-vacuum. I'm wondering if that process replicates across to the mirror, or if I need to set up vacuum on the mirror as well?

Scott Herbert
  • 586
  • 1
  • 6
  • 13
  • 1
    Excellent question. The streaming replication uses the Write-Ahead Log, so it boils down to whether the changes VACUUM makes are logged or not. – DerfK Dec 13 '10 at 22:55
  • 1
    Interestingly, I have auto-vacuum turned ON on the read-only mirror, but looking in the table stats it doesn't appear have ever run; as all tables list 0 live/dead tuples, and there are no histories visible of vacuums or analyzes. – Scott Herbert Dec 14 '10 at 00:23
  • http://developer.postgresql.org/pgdocs/postgres/hot-standby.html - 25.5.2. Handling query conflicts - "Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still "see" any of the rows to be removed............. This seems to suggest that VACUUM is WAL logged, and therefore it's a "yes" to my question. Would love to get some more info from a PG guru though! – Scott Herbert Dec 14 '10 at 00:40
  • The data from dynamic views is expected to be different on primary vs. the standby. These views use system functions to gather the data, and those functions read the data from in-memory data structures rather than from a physical table. For eg., performing an ANALYZE on primary will update the optimizer statistics (used for query planning) on the standby too, but the time the ANALYZE was performed on the table won’t reflect in the pg_stat_user_tables since that information is not logged in transaction logs. – Gurjeet Singh Oct 01 '13 at 22:07
  • So the conclusion is that AUTOVACUUM occuring on the master has no effect on it running on the slave, correct? Assuming this is the case, if I have a table that has NO updates/deletes, just inserts, would it be feasible to disable autovacuum in the master, since I'm just writing to it, and the reads happen on the slave. – Henley Oct 04 '13 at 18:58

1 Answers1

20

Vacuum and autovacuum replicate like any other write operation. (Well, they are obviously somewhat special internally, but as far as your question is concerned, they are normal write operations.) Running vacuum or autovacuum on the slave doesn't do anything and is not necessary.

Peter Eisentraut
  • 3,575
  • 1
  • 23
  • 21