CODEWITHSUNDEEP
sqlpostgresqlvacuumautovacuum
dsmorey
dsmorey

Reputation: 473

Postgresql: Autovacuum partitioned tables

We have a very large table that was partitioned into monthly tables. We have no autovacuum parameters set in the postgresql.conf file, so it's on by default with default parameters.

The past months tables table_201404, table_201403 do not get written to or updated/deleted once they are passed, they are only read from for historical data. Why is it that we are noticing autovacuum processes running on these tables? Is it because they are part of a main partition and PostgreSQL is seeing those tables as one?

We are toying with the idea of setting autovacuum_enabled to off for these past tables, but I wanted to consult the wisdom of Stackoverflow first.

Thanks all...

Upvotes: 4

Views: 1765

Answers (1)

jjanes
jjanes

Reputation: 44137

Even read-only tables need to be vacuumed for wrap-around once every 2 billion transactions, and under the default settings are vacuumed for wrap-around once every 150 million transactions.

The transaction IDs stored with each row are 32 bits, so they wrap around eventually. To prevent this from causing problems, any very old transactionID has to be replaced with a magic value meaning "Older than all other IDs". So the table has to be scanned to do that replacement. If the table never changes, eventually every transaction ID will be replaced with the magic value and conceptually that table no longer needs to be scanned. But that fact is not stored anywhere, so the table still needs to be scanned every now then so that the system can observe that they are all still OK. Fortunately the scan is done in sequentially and only needs to read, not write, so it should be fairly efficient.

It is possible that the whole thing will be redone in 9.5 so that tables like that would no longer need to be scanned.

Upvotes: 5

Related Questions