Search code examples
postgresqlpartitionvacuumautovacuumpostgresql-11

Setting autovacuum on partitioned tables in Postgres 11


I'm trying to adjust autovacuum settings on a partitioned table, on PostgreSQL 11.

e.g:

# create table test (ts timestamp) partition by range (ts);
CREATE TABLE
# alter table test set (autovacuum_analyze_scale_factor = 0.1);
ERROR:  unrecognized parameter "autovacuum_analyze_scale_factor"

Is it possible to alter such settings on partitioned tables?


Solution

  • It seems that you can only set parameters on table partitions rather than on parent table.

    postgres=# create table test (ts timestamp) partition by range (ts);
    CREATE TABLE
    postgres=# create table test_2018 partition of test for values from ('2018-01-01 00:00:00') to ('2018-12-31 23:59:59');
    CREATE TABLE
    postgres=# alter table test_2018 set (autovacuum_analyze_scale_factor = 0.1);
    ALTER TABLE
    postgres=# alter table test set (autovacuum_analyze_scale_factor = 0.1);
    ERROR:  unrecognized parameter "autovacuum_analyze_scale_factor"
    postgres=#