PostgreSQL에서 autovacuum이 일어나는 조건에 대해서 포스팅하려고 합니다. 이 포스팅은 PostgreSQL 버전 13을 기준으로 합니다.
Autovacuum 은 'vacuum', 'vacuum analyze', 'vacuum freeze'와 같은 동작을 자동으로 수행합니다. 해당 동작이 어떤 목적을 가지고 있는지 이번 포스팅에서 다루지는 않겠습니다.
Autovacuum이 동작하는 로직을 확인하기위해 PostgreSQL 13의 코드에 접근하여 확인했습니다.
https://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c
* If we found stats for the table, and autovacuum is currently enabled,
* make a threshold-based decision whether to vacuum and/or analyze. If
* autovacuum is currently disabled, we must be here for anti-wraparound
* vacuuming only, so don't vacuum (or analyze) anything that's not being
* forced.
*/
if (PointerIsValid(tabentry) && AutoVacuumingActive())
{
reltuples = classForm->reltuples;
vactuples = tabentry->dead_tuples;
instuples = tabentry->ins_since_vacuum;
anltuples = tabentry->mod_since_analyze;
/* If the table hasn't yet been vacuumed, take reltuples as zero */
if (reltuples < 0)
reltuples = 0;
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
/*
* Note that we don't need to take special consideration for stat
* reset, because if that happens, the last vacuum and analyze counts
* will be reset too.
*/
if (vac_ins_base_thresh >= 0)
elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
NameStr(classForm->relname),
vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh);
else
elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f)",
NameStr(classForm->relname),
vactuples, vacthresh, anltuples, anlthresh);
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh) ||
(vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
*doanalyze = (anltuples > anlthresh);
}
else
{
/*
* Skip a table not found in stat hash, unless we have to force vacuum
* for anti-wrap purposes. If it's not acted upon, there's no need to
* vacuum it.
*/
*dovacuum = force_vacuum;
*doanalyze = false;
}위 내용 중 필요한 내용만 살펴보겠습니다.
reltuples = classForm->reltuples;
vactuples = tabentry->dead_tuples;
instuples = tabentry->ins_since_vacuum;
anltuples = tabentry->mod_since_analyze;
/* If the table hasn't yet been vacuumed, take reltuples as zero */
if (reltuples < 0)
reltuples = 0;
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh) ||
(vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
*doanalyze = (anltuples > anlthresh);
따라서 결론만 간단히 정리하자면 다음과 같습니다.
[vacuum]
dead_tuple > autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold
n_ins_since_vacuum > autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold[Analyze]
change_since_analyze > autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_thresholdI am going to post about the conditions under which autovacuum occurs in PostgreSQL. This post is based on PostreSQL 13.
Autovacuum automatically perfoms actions such as 'vacuum', 'vacuum analyze', and 'vacuum freeze'. I will not describe the purpose of these actions.
I accessed the code of PostgreSQL 13 to check the logic that makes Autovacuum work.
https://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c
* If we found stats for the table, and autovacuum is currently enabled,
* make a threshold-based decision whether to vacuum and/or analyze. If
* autovacuum is currently disabled, we must be here for anti-wraparound
* vacuuming only, so don't vacuum (or analyze) anything that's not being
* forced.
*/
if (PointerIsValid(tabentry) && AutoVacuumingActive())
{
reltuples = classForm->reltuples;
vactuples = tabentry->dead_tuples;
instuples = tabentry->ins_since_vacuum;
anltuples = tabentry->mod_since_analyze;
/* If the table hasn't yet been vacuumed, take reltuples as zero */
if (reltuples < 0)
reltuples = 0;
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
/*
* Note that we don't need to take special consideration for stat
* reset, because if that happens, the last vacuum and analyze counts
* will be reset too.
*/
if (vac_ins_base_thresh >= 0)
elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
NameStr(classForm->relname),
vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh);
else
elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f)",
NameStr(classForm->relname),
vactuples, vacthresh, anltuples, anlthresh);
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh) ||
(vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
*doanalyze = (anltuples > anlthresh);
}
else
{
/*
* Skip a table not found in stat hash, unless we have to force vacuum
* for anti-wrap purposes. If it's not acted upon, there's no need to
* vacuum it.
*/
*dovacuum = force_vacuum;
*doanalyze = false;
}Let's look at only the necessary parts from the above.
reltuples = classForm->reltuples;
vactuples = tabentry->dead_tuples;
instuples = tabentry->ins_since_vacuum;
anltuples = tabentry->mod_since_analyze;
/* If the table hasn't yet been vacuumed, take reltuples as zero */
if (reltuples < 0)
reltuples = 0;
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh) ||
(vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
*doanalyze = (anltuples > anlthresh);
So, to summarize the conclusion briefly, it is as follows.
[vacuum]
dead_tuple > autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold
n_ins_since_vacuum > autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold[Analyze]
change_since_analyze > autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold
DB Engineer