Setting autovacuum on partitioned tables in Postgres 11
0
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?
postgresql partition vacuum autovacuum postgresql-11
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
add a comment |
0
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?
postgresql partition vacuum autovacuum postgresql-11
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
add a comment |
0
0
0
0
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?
postgresql partition vacuum autovacuum postgresql-11
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
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?
postgresql partition vacuum autovacuum postgresql-11
postgresql partition vacuum autovacuum postgresql-11
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
edited Dec 31 '18 at 11:19
Sylvain
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
asked Dec 30 '18 at 19:24
SylvainSylvain
1,66731723
1,66731723
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
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=#
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53980719%2fsetting-autovacuum-on-partitioned-tables-in-postgres-11%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
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=#
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
add a comment |
0
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=#
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
add a comment |
0
0
0
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=#
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
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=#
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
answered Jan 1 at 8:30
C.C. HsuC.C. Hsu
511
511
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53980719%2fsetting-autovacuum-on-partitioned-tables-in-postgres-11%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
