public inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH v3 4/7] Row pattern recognition patch (executor).
16+ messages / 7 participants
[nested] [flat]

* [PATCH v3 4/7] Row pattern recognition patch (executor).
@ 2023-07-26 10:49  Tatsuo Ishii <[email protected]>
  0 siblings, 0 replies; 16+ messages in thread

From: Tatsuo Ishii @ 2023-07-26 10:49 UTC (permalink / raw)

---
 src/backend/executor/nodeWindowAgg.c | 701 ++++++++++++++++++++++++++-
 src/backend/utils/adt/windowfuncs.c  |  38 +-
 src/include/catalog/pg_proc.dat      |   6 +
 src/include/nodes/execnodes.h        |  18 +
 src/include/windowapi.h              |   8 +
 5 files changed, 758 insertions(+), 13 deletions(-)

diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 310ac23e3a..0586bf57d6 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -36,6 +36,7 @@
 #include "access/htup_details.h"
 #include "catalog/objectaccess.h"
 #include "catalog/pg_aggregate.h"
+#include "catalog/pg_collation_d.h"
 #include "catalog/pg_proc.h"
 #include "executor/executor.h"
 #include "executor/nodeWindowAgg.h"
@@ -48,6 +49,7 @@
 #include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
+#include "utils/fmgroids.h"
 #include "utils/expandeddatum.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
@@ -159,6 +161,14 @@ typedef struct WindowStatePerAggData
 	bool		restart;		/* need to restart this agg in this cycle? */
 } WindowStatePerAggData;
 
+/*
+ * Map between Var attno in a target list and the parsed attno.
+ */
+typedef struct AttnoMap {
+	List		*attno;			/* att number in target list (list of AttNumber) */
+	List		*attnosyn;		/* parsed att number (list of AttNumber) */
+} AttnoMap;
+
 static void initialize_windowaggregate(WindowAggState *winstate,
 									   WindowStatePerFunc perfuncstate,
 									   WindowStatePerAgg peraggstate);
@@ -182,8 +192,9 @@ static void begin_partition(WindowAggState *winstate);
 static void spool_tuples(WindowAggState *winstate, int64 pos);
 static void release_partition(WindowAggState *winstate);
 
-static int	row_is_in_frame(WindowAggState *winstate, int64 pos,
+static int  row_is_in_frame(WindowAggState *winstate, int64 pos,
 							TupleTableSlot *slot);
+
 static void update_frameheadpos(WindowAggState *winstate);
 static void update_frametailpos(WindowAggState *winstate);
 static void update_grouptailpos(WindowAggState *winstate);
@@ -195,9 +206,19 @@ static Datum GetAggInitVal(Datum textInitVal, Oid transtype);
 
 static bool are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
 					  TupleTableSlot *slot2);
-static bool window_gettupleslot(WindowObject winobj, int64 pos,
-								TupleTableSlot *slot);
 
+static void attno_map(Node *node, AttnoMap *map);
+static bool attno_map_walker(Node *node, void *context);
+static int row_is_in_reduced_frame(WindowObject winobj, int64 pos);
+
+static int64 evaluate_pattern(WindowObject winobj, int64 current_pos, 
+							  char *vname, StringInfo encoded_str, bool *result);
+
+static bool get_slots(WindowObject winobj, int64 current_pos);
+
+static int search_str_set(char *pattern, StringInfo *str_set, int set_size);
+static void search_str_set_recurse(char *pattern, StringInfo *str_set, int set_size, int set_index,
+								   char *encoded_str, int *resultlen);
 
 /*
  * initialize_windowaggregate
@@ -673,6 +694,9 @@ eval_windowaggregates(WindowAggState *winstate)
 	WindowObject agg_winobj;
 	TupleTableSlot *agg_row_slot;
 	TupleTableSlot *temp_slot;
+	bool		reduced_frame_set;
+	bool		check_reduced_frame;
+	int			num_rows_in_reduced_frame;
 
 	numaggs = winstate->numaggs;
 	if (numaggs == 0)
@@ -790,6 +814,7 @@ eval_windowaggregates(WindowAggState *winstate)
 			(winstate->frameOptions & FRAMEOPTION_EXCLUSION) ||
 			winstate->aggregatedupto <= winstate->frameheadpos)
 		{
+			elog(DEBUG1, "peraggstate->restart  is set");
 			peraggstate->restart = true;
 			numaggs_restart++;
 		}
@@ -861,8 +886,10 @@ eval_windowaggregates(WindowAggState *winstate)
 	 * If we created a mark pointer for aggregates, keep it pushed up to frame
 	 * head, so that tuplestore can discard unnecessary rows.
 	 */
+#ifdef NOT_USED
 	if (agg_winobj->markptr >= 0)
 		WinSetMarkPosition(agg_winobj, winstate->frameheadpos);
+#endif
 
 	/*
 	 * Now restart the aggregates that require it.
@@ -919,6 +946,10 @@ eval_windowaggregates(WindowAggState *winstate)
 		ExecClearTuple(agg_row_slot);
 	}
 
+	reduced_frame_set = false;
+	check_reduced_frame = false;
+	num_rows_in_reduced_frame = 0;
+
 	/*
 	 * Advance until we reach a row not in frame (or end of partition).
 	 *
@@ -930,12 +961,18 @@ eval_windowaggregates(WindowAggState *winstate)
 	{
 		int			ret;
 
+		elog(DEBUG1, "===== loop in frame starts: " INT64_FORMAT, winstate->aggregatedupto);
+
 		/* Fetch next row if we didn't already */
 		if (TupIsNull(agg_row_slot))
 		{
 			if (!window_gettupleslot(agg_winobj, winstate->aggregatedupto,
 									 agg_row_slot))
+			{
+				if (check_reduced_frame)
+					winstate->aggregatedupto--;
 				break;			/* must be end of partition */
+			}
 		}
 
 		/*
@@ -944,10 +981,47 @@ eval_windowaggregates(WindowAggState *winstate)
 		 */
 		ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot);
 		if (ret < 0)
+		{
+			if (winstate->patternVariableList != NIL && check_reduced_frame)
+				winstate->aggregatedupto--;
 			break;
+		}
 		if (ret == 0)
 			goto next_tuple;
 
+		if (winstate->patternVariableList != NIL)
+		{
+			if (!reduced_frame_set)
+			{
+				num_rows_in_reduced_frame = row_is_in_reduced_frame(winstate->agg_winobj, winstate->aggregatedupto);
+				reduced_frame_set = true;
+				elog(DEBUG1, "set num_rows_in_reduced_frame: %d pos: " INT64_FORMAT,
+					 num_rows_in_reduced_frame, winstate->aggregatedupto);
+
+				if (num_rows_in_reduced_frame <= 0)
+					break;
+
+				else if (num_rows_in_reduced_frame > 0)
+					check_reduced_frame = true;
+			}
+
+			if (check_reduced_frame)
+			{
+				elog(DEBUG1, "decrease num_rows_in_reduced_frame: %d pos: " INT64_FORMAT,
+					 num_rows_in_reduced_frame, winstate->aggregatedupto);
+				num_rows_in_reduced_frame--;
+				if (num_rows_in_reduced_frame < 0)
+				{
+					/*
+					 * No more rows remain in the reduced frame. Finish
+					 * accumulating row into the aggregates.
+					 */
+					winstate->aggregatedupto--;
+					break;
+				}
+			}
+		}
+
 		/* Set tuple context for evaluation of aggregate arguments */
 		winstate->tmpcontext->ecxt_outertuple = agg_row_slot;
 
@@ -976,6 +1050,8 @@ next_tuple:
 		ExecClearTuple(agg_row_slot);
 	}
 
+	elog(DEBUG1, "===== break loop in frame starts: " INT64_FORMAT, winstate->aggregatedupto);
+
 	/* The frame's end is not supposed to move backwards, ever */
 	Assert(aggregatedupto_nonrestarted <= winstate->aggregatedupto);
 
@@ -2053,6 +2129,8 @@ ExecWindowAgg(PlanState *pstate)
 
 	CHECK_FOR_INTERRUPTS();
 
+	elog(DEBUG1, "ExecWindowAgg called. pos: " INT64_FORMAT , winstate->currentpos);
+
 	if (winstate->status == WINDOWAGG_DONE)
 		return NULL;
 
@@ -2388,6 +2466,12 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 	TupleDesc	scanDesc;
 	ListCell   *l;
 
+	TargetEntry	*te;
+	Expr		*expr;
+	Var			*var;
+	int			nargs;
+	AttnoMap	attnomap;
+
 	/* check for unsupported flags */
 	Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
 
@@ -2483,6 +2567,16 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 	winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate, scanDesc,
 												   &TTSOpsMinimalTuple);
 
+	winstate->prev_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+												 &TTSOpsMinimalTuple);
+
+	winstate->next_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+												 &TTSOpsMinimalTuple);
+
+	winstate->null_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+												 &TTSOpsMinimalTuple);
+	winstate->null_slot = ExecStoreAllNullTuple(winstate->null_slot);
+
 	/*
 	 * create frame head and tail slots only if needed (must create slots in
 	 * exactly the same cases that update_frameheadpos and update_frametailpos
@@ -2667,6 +2761,69 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 	winstate->inRangeAsc = node->inRangeAsc;
 	winstate->inRangeNullsFirst = node->inRangeNullsFirst;
 
+	/* Set up SKIP TO type */
+	winstate->rpSkipTo = node->rpSkipTo;
+	/* Set up row pattern recognition PATTERN clause */
+	winstate->patternVariableList = node->patternVariable;
+	winstate->patternRegexpList = node->patternRegexp;
+
+	/* Set up row pattern recognition DEFINE clause */
+
+	/*
+	 * Collect mapping between varattno and varattnosyn in the targetlist.
+	 * XXX: For now we only check RPR's argument. Eventually we have to
+	 * recurse the targetlist to find out all mappings in Var nodes.
+	 */
+	attnomap.attno = NIL;
+	attnomap.attnosyn = NIL;
+
+	foreach (l, node->plan.targetlist)
+	{
+		te = lfirst(l);
+		if (IsA(te->expr, WindowFunc))
+		{
+			WindowFunc	*func = (WindowFunc *)te->expr;
+
+			/* sanity check */
+			nargs = list_length(func->args);
+			if (nargs != 1)
+				continue;
+
+			expr = (Expr *) lfirst(list_head(func->args));
+			if (!IsA(expr, Var))
+				continue;
+
+			var = (Var *)expr;
+			elog(DEBUG1, "resname: %s varattno: %d varattnosyn: %d",
+				 te->resname, var->varattno, var->varattnosyn);
+			attnomap.attno = lappend_int(attnomap.attno, var->varattno);
+			attnomap.attnosyn = lappend_int(attnomap.attnosyn, var->varattnosyn);
+		}
+	}
+
+	winstate->defineVariableList = NIL;
+	winstate->defineClauseList = NIL;
+	if (node->defineClause != NIL)
+	{
+		foreach(l, node->defineClause)
+		{
+			char		*name;
+			ExprState	*exps;
+
+			te = lfirst(l);
+			name = te->resname;
+			expr = te->expr;
+
+			elog(DEBUG1, "defineVariable name: %s", name);
+			winstate->defineVariableList = lappend(winstate->defineVariableList,
+												   makeString(pstrdup(name)));
+			/* tweak expr so that it referes to outer slot */
+			attno_map((Node *)expr, &attnomap);
+			exps = ExecInitExpr(expr, (PlanState *) winstate);
+			winstate->defineClauseList = lappend(winstate->defineClauseList, exps);
+		}
+	}
+
 	winstate->all_first = true;
 	winstate->partition_spooled = false;
 	winstate->more_partitions = false;
@@ -2674,6 +2831,77 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 	return winstate;
 }
 
+/*
+ * Rewrite Var node's varattno to the varattno which is used in the target
+ * list using AttnoMap.  We also rewrite varno so that it sees outer tuple
+ * (PREV) or inner tuple (NEXT).
+ */
+static void
+attno_map(Node *node, AttnoMap *map)
+{
+	(void) expression_tree_walker(node, attno_map_walker, (void *) map);
+}
+
+static bool
+attno_map_walker(Node *node, void *context)
+{
+	FuncExpr	*func;
+	int			nargs;
+	Expr		*expr;
+	Var			*var;
+	AttnoMap	*attnomap;
+	ListCell	*lc1, *lc2;
+	
+	if (node == NULL)
+		return false;
+
+	attnomap = (AttnoMap *) context;
+
+	if (IsA(node, FuncExpr))
+	{
+		func = (FuncExpr *)node;
+
+		if (func->funcid == F_PREV || func->funcid == F_NEXT)
+		{
+			/* sanity check */
+			nargs = list_length(func->args);
+			if (list_length(func->args) != 1)
+				elog(ERROR, "PREV/NEXT must have 1 argument but function %d has %d args", func->funcid, nargs);
+
+			expr = (Expr *) lfirst(list_head(func->args));
+			if (!IsA(expr, Var))
+				elog(ERROR, "PREV/NEXT's arg is not Var");	/* XXX: is it possible that arg type is Const? */
+			var = (Var *)expr;
+
+			if (func->funcid == F_PREV)
+				var->varno = OUTER_VAR;
+			else
+				var->varno = INNER_VAR;
+		}
+		return expression_tree_walker(node, attno_map_walker, (void *) context);
+	}
+	else if (IsA(node, Var))
+	{
+		var = (Var *)node;	 
+
+		elog(DEBUG1, "original varno: %d varattno: %d", var->varno, var->varattno);
+
+		forboth(lc1, attnomap->attno, lc2, attnomap->attnosyn)
+		{
+			int	attno = lfirst_int(lc1);
+			int	attnosyn = lfirst_int(lc2);
+
+			elog(DEBUG1, "walker: varattno: %d varattnosyn: %d",attno, attnosyn);
+			if (var->varattno == attnosyn)
+			{
+				elog(DEBUG1, "loc: %d rewrite varattno from: %d to %d", var->location, attnosyn, attno);
+				var->varattno = attno;
+			}
+		}
+	}
+	return expression_tree_walker(node, attno_map_walker, (void *) context);
+}
+
 /* -----------------
  * ExecEndWindowAgg
  * -----------------
@@ -2691,6 +2919,8 @@ ExecEndWindowAgg(WindowAggState *node)
 	ExecClearTuple(node->agg_row_slot);
 	ExecClearTuple(node->temp_slot_1);
 	ExecClearTuple(node->temp_slot_2);
+	ExecClearTuple(node->prev_slot);
+	ExecClearTuple(node->next_slot);
 	if (node->framehead_slot)
 		ExecClearTuple(node->framehead_slot);
 	if (node->frametail_slot)
@@ -2740,6 +2970,8 @@ ExecReScanWindowAgg(WindowAggState *node)
 	ExecClearTuple(node->agg_row_slot);
 	ExecClearTuple(node->temp_slot_1);
 	ExecClearTuple(node->temp_slot_2);
+	ExecClearTuple(node->prev_slot);
+	ExecClearTuple(node->next_slot);
 	if (node->framehead_slot)
 		ExecClearTuple(node->framehead_slot);
 	if (node->frametail_slot)
@@ -3080,7 +3312,7 @@ are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
  *
  * Returns true if successful, false if no such row
  */
-static bool
+bool
 window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
 {
 	WindowAggState *winstate = winobj->winstate;
@@ -3100,7 +3332,7 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
 		return false;
 
 	if (pos < winobj->markpos)
-		elog(ERROR, "cannot fetch row before WindowObject's mark position");
+		elog(ERROR, "cannot fetch row: " INT64_FORMAT " before WindowObject's mark position: " INT64_FORMAT,  pos, winobj->markpos );
 
 	oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory);
 
@@ -3420,14 +3652,54 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
 	WindowAggState *winstate;
 	ExprContext *econtext;
 	TupleTableSlot *slot;
-	int64		abs_pos;
-	int64		mark_pos;
 
 	Assert(WindowObjectIsValid(winobj));
 	winstate = winobj->winstate;
 	econtext = winstate->ss.ps.ps_ExprContext;
 	slot = winstate->temp_slot_1;
 
+	if (WinGetSlotInFrame(winobj, slot,
+						  relpos, seektype, set_mark,
+						  isnull, isout) == 0)
+	{
+		econtext->ecxt_outertuple = slot;
+		return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+							econtext, isnull);
+	}
+
+	if (isout)
+		*isout = true;
+	*isnull = true;
+	return (Datum) 0;
+}
+
+/*
+ * WinGetSlotInFrame
+ * slot: TupleTableSlot to store the result
+ * relpos: signed rowcount offset from the seek position
+ * seektype: WINDOW_SEEK_HEAD or WINDOW_SEEK_TAIL
+ * set_mark: If the row is found/in frame and set_mark is true, the mark is
+ *		moved to the row as a side-effect.
+ * isnull: output argument, receives isnull status of result
+ * isout: output argument, set to indicate whether target row position
+ *		is out of frame (can pass NULL if caller doesn't care about this)
+ *
+ * Returns 0 if we successfullt got the slot. false if out of frame.
+ * (also isout is set)
+ */
+int
+WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot,
+					 int relpos, int seektype, bool set_mark,
+					 bool *isnull, bool *isout)
+{
+	WindowAggState *winstate;
+	int64		abs_pos;
+	int64		mark_pos;
+	int			num_reduced_frame;
+
+	Assert(WindowObjectIsValid(winobj));
+	winstate = winobj->winstate;
+
 	switch (seektype)
 	{
 		case WINDOW_SEEK_CURRENT:
@@ -3494,6 +3766,12 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
 						 winstate->frameOptions);
 					break;
 			}
+			num_reduced_frame = row_is_in_reduced_frame(winobj, winstate->frameheadpos);
+			if (num_reduced_frame < 0)
+				goto out_of_frame;
+			else if (num_reduced_frame > 0)
+				if (relpos >= num_reduced_frame)
+					goto out_of_frame;
 			break;
 		case WINDOW_SEEK_TAIL:
 			/* rejecting relpos > 0 is easy and simplifies code below */
@@ -3565,6 +3843,12 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
 					mark_pos = 0;	/* keep compiler quiet */
 					break;
 			}
+
+			num_reduced_frame = row_is_in_reduced_frame(winobj, winstate->frameheadpos + relpos);
+			if (num_reduced_frame < 0)
+				goto out_of_frame;
+			else if (num_reduced_frame > 0)
+				abs_pos = winstate->frameheadpos + relpos + num_reduced_frame - 1;
 			break;
 		default:
 			elog(ERROR, "unrecognized window seek type: %d", seektype);
@@ -3583,15 +3867,13 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
 		*isout = false;
 	if (set_mark)
 		WinSetMarkPosition(winobj, mark_pos);
-	econtext->ecxt_outertuple = slot;
-	return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
-						econtext, isnull);
+	return 0;
 
 out_of_frame:
 	if (isout)
 		*isout = true;
 	*isnull = true;
-	return (Datum) 0;
+	return -1;
 }
 
 /*
@@ -3622,3 +3904,400 @@ WinGetFuncArgCurrent(WindowObject winobj, int argno, bool *isnull)
 	return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
 						econtext, isnull);
 }
+
+WindowAggState *
+WinGetAggState(WindowObject winobj)
+{
+	return winobj->winstate;
+}
+
+/*
+ * row_is_in_reduced_frame
+ * Determine whether a row is in the current row's reduced window frame according
+ * to row pattern matching
+ *
+ * The row must has been already determined that it is in a full window frame
+ * and fetched it into slot.
+ *
+ * Returns:
+ * = 0, RPR is not defined.
+ * >0, if the row is the first in the reduced frame. Return the number of rows in the reduced frame.
+ * -1, if the row is unmatched row
+ * -2, if the row is in the reduced frame but needed to be skipped because of
+ * AFTER MATCH SKIP PAST LAST ROW
+ */
+static
+int row_is_in_reduced_frame(WindowObject winobj, int64 pos)
+{
+	WindowAggState *winstate = winobj->winstate;
+	ListCell	*lc1, *lc2;
+	bool		expression_result;
+	int			num_matched_rows;
+	int64		original_pos;
+	bool		anymatch;
+	StringInfo	encoded_str;
+	StringInfo	pattern_str = makeStringInfo();
+
+	/*
+	 * Array of pattern variables evaluted to true.
+	 * Each character corresponds to pattern variable.
+	 * Example:
+	 * str_set[0] = "AB";
+	 * str_set[1] = "AC";
+	 * In this case at row 0 A and B are true, and A and C are true in row 1.
+	 */
+	#define ENCODED_STR_ARRAY_ALLOC_SIZE	128
+	StringInfo	*str_set = NULL;
+	int		str_set_index;
+	int		str_set_size;
+
+	if (winstate->patternVariableList == NIL)
+	{
+		/*
+		 * RPR is not defined. Assume that we are always in the the reduced
+		 * window frame.
+		 */
+		return 0;
+	}
+
+	/* save original pos */
+	original_pos = pos;
+
+	/*
+	 * Check whether the row speicied by pos is in the reduced frame. The
+	 * second and subsequent rows need to be recognized as "unmatched" rows if
+	 * AFTER MATCH SKIP PAST LAST ROW is defined.
+	 */
+	if (winstate->rpSkipTo == ST_PAST_LAST_ROW &&
+		pos > winstate->headpos_in_reduced_frame &&
+		pos < (winstate->headpos_in_reduced_frame + winstate->num_rows_in_reduced_frame))
+		return -2;
+		
+	/*
+	 * Loop over until none of pattern matches or encounters end of frame.
+	 */
+	for (;;)
+	{
+		int64	result_pos = -1;
+
+		/*
+		 * Loop over each PATTERN variable.
+		 */
+		anymatch = false;
+		encoded_str = makeStringInfo();
+
+		forboth(lc1, winstate->patternVariableList, lc2, winstate->patternRegexpList)
+		{
+			char	*vname = strVal(lfirst(lc1));
+			char	*quantifier = strVal(lfirst(lc2));
+
+			elog(DEBUG1, "pos: " INT64_FORMAT " pattern vname: %s quantifier: %s", pos, vname, quantifier);
+
+			expression_result = false;
+
+			/* evaluate row pattern against current row */
+			result_pos = evaluate_pattern(winobj, pos, vname, encoded_str, &expression_result);
+			if (expression_result)
+			{
+				elog(DEBUG1, "expression result is true");
+				anymatch = true;
+			}
+
+			/*
+			 * If out of frame, we are done.
+			 */
+			 if (result_pos < 0)
+				 break;
+		}
+
+		if (!anymatch)
+		{
+			/* none of patterns matched. */
+			break;
+		}
+
+		/* build encoded string array */
+		if (str_set == NULL)
+		{
+			str_set_index = 0;
+			str_set_size = ENCODED_STR_ARRAY_ALLOC_SIZE * sizeof(StringInfo);
+			str_set = palloc(str_set_size);
+		}
+
+		str_set[str_set_index++] = encoded_str;
+
+		elog(DEBUG1, "pos: " INT64_FORMAT " str_set_index: %d encoded_str: %s", pos, str_set_index, encoded_str->data);
+
+		if (str_set_index >= str_set_size)
+		{
+			str_set_size *= 2;
+			str_set = repalloc(str_set, str_set_size);
+		}
+
+		/* move to next row */
+		pos++;
+
+		if (result_pos < 0)
+		{
+			/* out of frame */
+			break;
+		}
+	}
+
+	if (str_set == NULL)
+	{
+		/* no matches found in the first row */
+		return -1;
+	}
+
+	elog(DEBUG1, "pos: " INT64_FORMAT " encoded_str: %s", pos, encoded_str->data);
+
+	/* build regular expression */
+	pattern_str = makeStringInfo();
+	appendStringInfoChar(pattern_str, '^');
+	forboth(lc1, winstate->patternVariableList, lc2, winstate->patternRegexpList)
+	{
+		char	*vname = strVal(lfirst(lc1));
+		char	*quantifier = strVal(lfirst(lc2));
+
+		appendStringInfoChar(pattern_str, vname[0]);
+		if (quantifier[0])
+			appendStringInfoChar(pattern_str, quantifier[0]);
+		elog(DEBUG1, "vname: %s quantifier: %s", vname, quantifier);
+	}
+
+	elog(DEBUG1, "pos: " INT64_FORMAT " pattern: %s", pos, pattern_str->data);
+
+	/* look for matching pattern variable sequence */
+	num_matched_rows = search_str_set(pattern_str->data, str_set, str_set_index);
+	if (num_matched_rows <= 0)
+		return -1;
+
+	/*
+	 * We are at the first row in the reduced frame.  Save the number of
+	 * matched rows as the number of rows in the reduced frame.
+	 */
+	winstate->headpos_in_reduced_frame = original_pos;
+	winstate->num_rows_in_reduced_frame = num_matched_rows;
+
+	return num_matched_rows;
+}
+
+/*
+ * search set of encode_str.
+ * set_size: size of set_str array.
+ */
+static
+int search_str_set(char *pattern, StringInfo *str_set, int set_size)
+{
+	char		*encoded_str = palloc0(set_size+1);
+	int			resultlen = 0;
+
+	search_str_set_recurse(pattern, str_set, set_size, 0, encoded_str, &resultlen);
+	elog(DEBUG1, "search_str_set returns %d", resultlen);
+	return resultlen;
+}
+
+static
+void search_str_set_recurse(char *pattern, StringInfo *str_set, int set_size, int set_index, char *encoded_str, int *resultlen)
+{
+	char	*p;
+
+	if (set_index >= set_size)
+	{
+		Datum	d;
+		text	*res;
+		char	*substr;
+
+		/*
+		 * We first perform pattern matching using regexp_instr, then call
+		 * textregexsubstr to get matched substring to know how log the
+		 * matched string is. That is the number of rows in the reduced window
+		 * frame.  The reason why we can't call textregexsubstr is, it error
+		 * out if pattern is not match.
+		 */
+		if (DatumGetInt32(DirectFunctionCall2Coll(regexp_instr, DEFAULT_COLLATION_OID,
+												  PointerGetDatum(cstring_to_text(encoded_str)),
+												  PointerGetDatum(cstring_to_text(pattern)))) > 0)
+		{
+			d = DirectFunctionCall2Coll(textregexsubstr,
+										DEFAULT_COLLATION_OID,
+										PointerGetDatum(cstring_to_text(encoded_str)),
+										PointerGetDatum(cstring_to_text(pattern)));
+			if (d != 0)
+			{
+				int		len;
+
+				res = DatumGetTextPP(d);
+				substr = text_to_cstring(res);
+				len = strlen(substr);
+				if (len > *resultlen)
+					/* remember the longest match */
+					*resultlen = len;
+			}
+		}
+		return;
+	}
+
+	p = str_set[set_index]->data;
+	while (*p)
+	{
+		encoded_str[set_index] = *p;
+		p++;
+		search_str_set_recurse(pattern, str_set, set_size, set_index + 1, encoded_str, resultlen);
+	}
+}
+
+
+/*
+ * Evaluate expression associated with PATTERN variable vname.
+ * relpos is relative row position in a frame (starting from 0).
+ * "quantifier" is the quatifier part of the PATTERN regular expression.
+ * Currently only '+' is allowed.
+ * result is out paramater representing the expression evaluation result
+ * is true of false.
+ * Return values are:
+ * >=0: the last match absolute row position
+ * other wise out of frame.
+ */
+static
+int64 evaluate_pattern(WindowObject winobj, int64 current_pos, 
+						char *vname, StringInfo encoded_str, bool *result)
+{
+	WindowAggState	*winstate = winobj->winstate;
+	ExprContext		*econtext = winstate->ss.ps.ps_ExprContext;
+	ListCell		*lc1, *lc2;
+	ExprState		*pat;
+	Datum			eval_result;
+	bool			out_of_frame = false;
+	bool			isnull;
+
+	forboth (lc1, winstate->defineVariableList, lc2, winstate->defineClauseList)
+	{
+		char	*name = strVal(lfirst(lc1));
+
+		if (strcmp(vname, name))
+			continue;
+
+		/* set expression to evaluate */
+		pat = lfirst(lc2);
+
+		/* get current, previous and next tuples */
+		if (!get_slots(winobj, current_pos))
+		{
+			out_of_frame = true;
+		}
+		else
+		{
+			/* evaluate the expression */
+			eval_result = ExecEvalExpr(pat, econtext, &isnull);
+			if (isnull)
+			{
+				/* expression is NULL */
+				elog(DEBUG1, "expression for %s is NULL at row: " INT64_FORMAT, vname, current_pos);
+				*result = false;
+			}
+			else
+			{
+				if (!DatumGetBool(eval_result))
+				{
+					/* expression is false */
+					elog(DEBUG1, "expression for %s is false at row: " INT64_FORMAT, vname, current_pos);
+					*result = false;
+				}
+				else
+				{
+					/* expression is true */
+					elog(DEBUG1, "expression for %s is true at row: " INT64_FORMAT, vname, current_pos);
+					appendStringInfoChar(encoded_str, vname[0]);
+					*result = true;
+				}
+			}
+			break;
+		}
+
+		if (out_of_frame)
+		{
+			*result = false;
+			return -1;
+		}
+	}
+	return current_pos;
+}
+
+/*
+ * Get current, previous and next tuples.
+ * Returns false if current row is out of partition/full frame.
+ */
+static
+bool get_slots(WindowObject winobj, int64 current_pos)
+{
+	WindowAggState *winstate = winobj->winstate;
+	TupleTableSlot *slot;
+	int		ret;
+	ExprContext *econtext;
+
+	econtext = winstate->ss.ps.ps_ExprContext;
+
+	/* set up current row tuple slot */
+	slot = winstate->temp_slot_1;
+	if (!window_gettupleslot(winobj, current_pos, slot))
+	{
+		elog(DEBUG1, "current row is out of partition at:" INT64_FORMAT, current_pos);
+		return false;
+
+		ret = row_is_in_frame(winstate, current_pos, slot);
+		if (ret <= 0)
+		{
+			elog(DEBUG1, "current row is out of frame at: " INT64_FORMAT, current_pos);
+			return false;
+		}
+	}
+	econtext->ecxt_scantuple = slot;
+
+	/* for PREV */
+	if (current_pos > 0)
+	{
+		slot = winstate->prev_slot;
+		if (!window_gettupleslot(winobj, current_pos - 1, slot))
+		{
+			elog(DEBUG1, "previous row is out of partition at: " INT64_FORMAT, current_pos - 1);
+			econtext->ecxt_outertuple = winstate->null_slot;
+		}
+		else
+		{
+			ret = row_is_in_frame(winstate, current_pos - 1, slot);
+			if (ret <= 0)
+			{
+				elog(DEBUG1, "previous row is out of frame at: " INT64_FORMAT, current_pos - 1);
+				econtext->ecxt_outertuple = winstate->null_slot;
+			}
+			else
+			{
+				econtext->ecxt_outertuple = slot;
+			}
+		}
+	}
+	else
+		econtext->ecxt_outertuple = winstate->null_slot;
+
+	/* for NEXT */
+	slot = winstate->next_slot;
+	if (!window_gettupleslot(winobj, current_pos + 1, slot))
+	{
+		elog(DEBUG1, "next row is out of partiton at: " INT64_FORMAT, current_pos + 1);
+		econtext->ecxt_innertuple = winstate->null_slot;
+	}
+	else
+	{
+		ret = row_is_in_frame(winstate, current_pos + 1, slot);
+		if (ret <= 0)
+		{
+			elog(DEBUG1, "next row is out of frame at: " INT64_FORMAT, current_pos + 1);
+			econtext->ecxt_innertuple = winstate->null_slot;
+		}
+		else
+			econtext->ecxt_innertuple = slot;
+	}
+	return true;
+}
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index b87a624fb2..e4cab36ec9 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -13,6 +13,9 @@
  */
 #include "postgres.h"
 
+#include "catalog/pg_collation_d.h"
+#include "executor/executor.h"
+#include "nodes/execnodes.h"
 #include "nodes/supportnodes.h"
 #include "utils/builtins.h"
 #include "windowapi.h"
@@ -36,11 +39,19 @@ typedef struct
 	int64		remainder;		/* (total rows) % (bucket num) */
 } ntile_context;
 
+/*
+ * rpr process information.
+ * Used for AFTER MATCH SKIP PAST LAST ROW
+ */
+typedef struct SkipContext
+{
+	int64		pos;	/* last row absolute position */
+} SkipContext;
+
 static bool rank_up(WindowObject winobj);
 static Datum leadlag_common(FunctionCallInfo fcinfo,
 							bool forward, bool withoffset, bool withdefault);
 
-
 /*
  * utility routine for *_rank functions.
  */
@@ -673,7 +684,7 @@ window_last_value(PG_FUNCTION_ARGS)
 	bool		isnull;
 
 	result = WinGetFuncArgInFrame(winobj, 0,
-								  0, WINDOW_SEEK_TAIL, true,
+								  0, WINDOW_SEEK_TAIL, false,
 								  &isnull, NULL);
 	if (isnull)
 		PG_RETURN_NULL();
@@ -713,3 +724,26 @@ window_nth_value(PG_FUNCTION_ARGS)
 
 	PG_RETURN_DATUM(result);
 }
+
+/*
+ * prev
+ * Dummy function to invoke RPR's navigation operator "PREV".
+ * This is *not* a window function.
+ */
+Datum
+window_prev(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+}
+
+/*
+ * next
+ * Dummy function to invoke RPR's navigation operation "NEXT".
+ * This is *not* a window function.
+ */
+Datum
+window_next(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+}
+
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..fa100b2665 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10397,6 +10397,12 @@
 { oid => '3114', descr => 'fetch the Nth row value',
   proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement int4', prosrc => 'window_nth_value' },
+{ oid => '6122', descr => 'previous value',
+  proname => 'prev', provolatile => 's', prorettype => 'anyelement',
+  proargtypes => 'anyelement', prosrc => 'window_prev' },
+{ oid => '6123', descr => 'next value',
+  proname => 'next', provolatile => 's', prorettype => 'anyelement',
+  proargtypes => 'anyelement', prosrc => 'window_next' },
 
 # functions for range types
 { oid => '3832', descr => 'I/O',
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index cb714f4a19..4fd3bd1a93 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2519,6 +2519,14 @@ typedef struct WindowAggState
 	int64		groupheadpos;	/* current row's peer group head position */
 	int64		grouptailpos;	/* " " " " tail position (group end+1) */
 
+	/* these fields are used in Row pattern recognition: */
+	RPSkipTo	rpSkipTo;		/* Row Pattern Skip To type */	
+	List	   *patternVariableList;	/* list of row pattern variables names (list of String) */
+	List	   *patternRegexpList;	/* list of row pattern regular expressions ('+' or ''. list of String) */
+	List	   *defineVariableList;	/* list of row pattern definition variables (list of String) */
+	List	   *defineClauseList;	/* expression for row pattern definition
+									 * search conditions ExprState list */
+
 	MemoryContext partcontext;	/* context for partition-lifespan data */
 	MemoryContext aggcontext;	/* shared context for aggregate working data */
 	MemoryContext curaggcontext;	/* current aggregate's working data */
@@ -2555,6 +2563,16 @@ typedef struct WindowAggState
 	TupleTableSlot *agg_row_slot;
 	TupleTableSlot *temp_slot_1;
 	TupleTableSlot *temp_slot_2;
+
+	/* temporary slots for RPR */
+	TupleTableSlot *prev_slot;	/* PREV row navigation operator */
+	TupleTableSlot *next_slot;	/* NEXT row navigation operator */
+	TupleTableSlot *null_slot;	/* all NULL slot */
+
+	/* head of the reduced window frame */
+	int64		headpos_in_reduced_frame;
+	/* number of rows in the reduced window frame */
+	int64		num_rows_in_reduced_frame;
 } WindowAggState;
 
 /* ----------------
diff --git a/src/include/windowapi.h b/src/include/windowapi.h
index b8c2c565d1..1e292648e9 100644
--- a/src/include/windowapi.h
+++ b/src/include/windowapi.h
@@ -58,7 +58,15 @@ extern Datum WinGetFuncArgInFrame(WindowObject winobj, int argno,
 								  int relpos, int seektype, bool set_mark,
 								  bool *isnull, bool *isout);
 
+extern int WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot,
+							 int relpos, int seektype, bool set_mark,
+							 bool *isnull, bool *isout);
+
 extern Datum WinGetFuncArgCurrent(WindowObject winobj, int argno,
 								  bool *isnull);
 
+extern WindowAggState *WinGetAggState(WindowObject winobj);
+
+extern bool window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot);
+
 #endif							/* WINDOWAPI_H */
-- 
2.25.1


----Next_Part(Wed_Jul_26_21_21_34_2023_317)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v3-0005-Row-pattern-recognition-patch-docs.patch"



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-08-12 13:41  Frédéric Yhuel <[email protected]>
  0 siblings, 1 reply; 16+ messages in thread

From: Frédéric Yhuel @ 2024-08-12 13:41 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; +Cc: Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>



On 8/7/24 23:39, Nathan Bossart wrote:
> I've attached a new patch to show roughly what I think this new GUC should
> look like.  I'm hoping this sparks more discussion, if nothing else.
>

Thank you. FWIW, I would prefer a sub-linear growth, so maybe something 
like this:

vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples, 
vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);

This would give :

* 386M (instead of 5.1 billion currently) for a 25.6 billion tuples table ;
* 77M for a 2.56 billion tuples table (Robert's example) ;
* 15M (instead of 51M currently) for a 256M tuples table ;
* 3M (instead of 5M currently) for a 25.6M tuples table.

The other advantage is that you don't need another GUC.

> On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote:
>> By the way, I wonder if there were any off-list discussions after Robert's
>> conference at PGConf.dev (and I'm waiting for the video of the conf).
> 
> I don't recall any discussions about this idea, but Robert did briefly
> mention it in his talk [0].
> 
> [0] https://www.youtube.com/watch?v=RfTD-Twpvac
> 

Very interesting, thanks!






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-06 12:51  wenhui qiu <[email protected]>
  parent: Frédéric Yhuel <[email protected]>
  0 siblings, 1 reply; 16+ messages in thread

From: wenhui qiu @ 2024-11-06 12:51 UTC (permalink / raw)
  To: Frédéric Yhuel <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

Hi frederic.yhuel

> Thank you. FWIW, I would prefer a sub-linear growth, so maybe something
> like this

>   vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
>   vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);

>   This would give :

>   * 386M (instead of 5.1 billion currently) for a 25.6 billion tuples
table ;
>   * 77M for a 2.56 billion tuples table (Robert's example) ;
>   * 15M (instead of 51M currently) for a 256M tuples table ;
>   * 3M (instead of 5M currently) for a 25.6M tuples table.
> The other advantage is that you don't need another GUC.
Argee ,We just need to change the calculation formula，But I prefer this
formula because it calculates a smoother value.

vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
reltuples,vac_base_thresh
+ vac_scale_factor * log2(reltuples) * 10000);
or
vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples)
, sqrt(1000.0 * reltuples));

Frédéric Yhuel <[email protected]> 于2024年8月12日周一 21:41写道：

>
>
> On 8/7/24 23:39, Nathan Bossart wrote:
> > I've attached a new patch to show roughly what I think this new GUC
> should
> > look like.  I'm hoping this sparks more discussion, if nothing else.
> >
>
> Thank you. FWIW, I would prefer a sub-linear growth, so maybe something
> like this:
>
> vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
> vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);
>
> This would give :
>
> * 386M (instead of 5.1 billion currently) for a 25.6 billion tuples table ;
> * 77M for a 2.56 billion tuples table (Robert's example) ;
> * 15M (instead of 51M currently) for a 256M tuples table ;
> * 3M (instead of 5M currently) for a 25.6M tuples table.
>
> The other advantage is that you don't need another GUC.
>
> > On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote:
> >> By the way, I wonder if there were any off-list discussions after
> Robert's
> >> conference at PGConf.dev (and I'm waiting for the video of the conf).
> >
> > I don't recall any discussions about this idea, but Robert did briefly
> > mention it in his talk [0].
> >
> > [0] https://www.youtube.com/watch?v=RfTD-Twpvac
> >
>
> Very interesting, thanks!
>
>
>


^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-08 17:44  Nathan Bossart <[email protected]>
  parent: wenhui qiu <[email protected]>
  0 siblings, 1 reply; 16+ messages in thread

From: Nathan Bossart @ 2024-11-08 17:44 UTC (permalink / raw)
  To: wenhui qiu <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, Nov 06, 2024 at 08:51:07PM +0800, wenhui qiu wrote:
>> Thank you. FWIW, I would prefer a sub-linear growth, so maybe something
>> like this
> 
>>   vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
>>   vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);
> 
>>   This would give :
> 
>>   * 386M (instead of 5.1 billion currently) for a 25.6 billion tuples
> table ;
>>   * 77M for a 2.56 billion tuples table (Robert's example) ;
>>   * 15M (instead of 51M currently) for a 256M tuples table ;
>>   * 3M (instead of 5M currently) for a 25.6M tuples table.
>> The other advantage is that you don't need another GUC.
> Argee ,We just need to change the calculation formula,But I prefer this
> formula because it calculates a smoother value.
> 
> vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
> reltuples,vac_base_thresh
> + vac_scale_factor * log2(reltuples) * 10000);
> or
> vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples)
> , sqrt(1000.0 * reltuples));

I apologize for the curt response, but I don't understand how we could
decide which of these three complicated formulas to use, let alone how we
could expect users to reason about the behavior.

-- 
nathan






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-09 14:08  wenhui qiu <[email protected]>
  parent: Nathan Bossart <[email protected]>
  0 siblings, 1 reply; 16+ messages in thread

From: wenhui qiu @ 2024-11-09 14:08 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

Hi Nathan Bossart
> I apologize for the curt response, but I don't understand how we could
>   decide which of these three complicated formulas to use, let alone how
we
> could expect users to reason about the behavior.
      Sorry ,I forgot to explain the reason in my last email，In fact, I
submitted the patch to the community，（[email protected]） told me
there has a same idea ，so ，
      Let me explain those two formulas here，about （   vacthresh = (float4)
fmin(vac_base_thresh + (vac_scale_factor * reltuples), sqrt(1000.0 *
reltuples));   A few days ago, I was looking at the sql server
documentation and found that sql server has optimized the algorithm related
to updating statistics in the 2016 ,version,I think we can also learn from
the implementation method of sql server to optimize the problem of
automatic vacuum triggered by large tables，The Document link(
https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver...
),about （ vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
 reltuples,vac_base_thresh+ vac_scale_factor * log2(reltuples) * 10000);）I
came to the conclusion by trying to draw a function graph，I personally
think it is a smooth formula

####
SQL SERVER
[image: image.png]
log2
[image: image.png]
log

[image: image.png]

original

[image: image.png]

Thanks

Nathan Bossart <[email protected]> 于2024年11月9日周六 01:44写道：

> On Wed, Nov 06, 2024 at 08:51:07PM +0800, wenhui qiu wrote:
> >> Thank you. FWIW, I would prefer a sub-linear growth, so maybe something
> >> like this
> >
> >>   vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
> >>   vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);
> >
> >>   This would give :
> >
> >>   * 386M (instead of 5.1 billion currently) for a 25.6 billion tuples
> > table ;
> >>   * 77M for a 2.56 billion tuples table (Robert's example) ;
> >>   * 15M (instead of 51M currently) for a 256M tuples table ;
> >>   * 3M (instead of 5M currently) for a 25.6M tuples table.
> >> The other advantage is that you don't need another GUC.
> > Argee ,We just need to change the calculation formula,But I prefer this
> > formula because it calculates a smoother value.
> >
> > vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
> > reltuples,vac_base_thresh
> > + vac_scale_factor * log2(reltuples) * 10000);
> > or
> > vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor *
> reltuples)
> > , sqrt(1000.0 * reltuples));
>
> I apologize for the curt response, but I don't understand how we could
> decide which of these three complicated formulas to use, let alone how we
> could expect users to reason about the behavior.
>
> --
> nathan
>


Attachments:

  [image/png] image.png (109.3K, ../../CAGjGUALGwH9LJUxe+ZbjnW_Cg4B4qyFrXFv+oWYCYnLcwggBJQ@mail.gmail.com/3-image.png)
  download | view image

  [image/png] image.png (92.9K, ../../CAGjGUALGwH9LJUxe+ZbjnW_Cg4B4qyFrXFv+oWYCYnLcwggBJQ@mail.gmail.com/4-image.png)
  download | view image

  [image/png] image.png (96.2K, ../../CAGjGUALGwH9LJUxe+ZbjnW_Cg4B4qyFrXFv+oWYCYnLcwggBJQ@mail.gmail.com/5-image.png)
  download | view image

  [image/png] image.png (110.2K, ../../CAGjGUALGwH9LJUxe+ZbjnW_Cg4B4qyFrXFv+oWYCYnLcwggBJQ@mail.gmail.com/6-image.png)
  download | view image

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-09 15:59  Nathan Bossart <[email protected]>
  parent: wenhui qiu <[email protected]>
  0 siblings, 2 replies; 16+ messages in thread

From: Nathan Bossart @ 2024-11-09 15:59 UTC (permalink / raw)
  To: wenhui qiu <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Sat, Nov 09, 2024 at 10:08:51PM +0800, wenhui qiu wrote:
>       Sorry ,I forgot to explain the reason in my last email,In fact, I
> submitted the patch to the community,([email protected]) told me
> there has a same idea ,so ,
>       Let me explain those two formulas here,about (   vacthresh = (float4)
> fmin(vac_base_thresh + (vac_scale_factor * reltuples), sqrt(1000.0 *
> reltuples));   A few days ago, I was looking at the sql server
> documentation and found that sql server has optimized the algorithm related
> to updating statistics in the 2016 ,version,I think we can also learn from
> the implementation method of sql server to optimize the problem of
> automatic vacuum triggered by large tables,The Document link(
> https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver...
> ),about ( vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
>  reltuples,vac_base_thresh+ vac_scale_factor * log2(reltuples) * 10000);)I
> came to the conclusion by trying to draw a function graph,I personally
> think it is a smooth formula

AFAICT the main advantage of these formulas is that you don't need another
GUC, but they also makes the existing ones more difficult to configure.
Plus, there's no way to go back to the existing behavior.

-- 
nathan






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-10 11:25  wenhui qiu <[email protected]>
  parent: Nathan Bossart <[email protected]>
  1 sibling, 0 replies; 16+ messages in thread

From: wenhui qiu @ 2024-11-10 11:25 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

Hi Nathan Bossart
> AFAICT the main advantage of these formulas is that you don't need another
> GUC, but they also makes the existing ones more difficult to configure.
> Plus, there's no way to go back to the existing behavior.
There is indeed this problem,But I think this formula should not be a
linear relationship in the first place,SQL Server was realized and
optimized eight years ago. I think we can definitely draw on the experience
of SQL Server.Maybe many people are worried that frequent vacuum will
affect io performance, but we can learn from the experience of SQL Server
in vacuum analysis   .

Nathan Bossart <[email protected]> 于2024年11月9日周六 23:59写道：

> On Sat, Nov 09, 2024 at 10:08:51PM +0800, wenhui qiu wrote:
> >       Sorry ,I forgot to explain the reason in my last email,In fact, I
> > submitted the patch to the community,([email protected]) told me
> > there has a same idea ,so ,
> >       Let me explain those two formulas here,about (   vacthresh =
> (float4)
> > fmin(vac_base_thresh + (vac_scale_factor * reltuples), sqrt(1000.0 *
> > reltuples));   A few days ago, I was looking at the sql server
> > documentation and found that sql server has optimized the algorithm
> related
> > to updating statistics in the 2016 ,version,I think we can also learn
> from
> > the implementation method of sql server to optimize the problem of
> > automatic vacuum triggered by large tables,The Document link(
> >
> https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver...
> > ),about ( vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
> >  reltuples,vac_base_thresh+ vac_scale_factor * log2(reltuples) *
> 10000);)I
> > came to the conclusion by trying to draw a function graph,I personally
> > think it is a smooth formula
>
> AFAICT the main advantage of these formulas is that you don't need another
> GUC, but they also makes the existing ones more difficult to configure.
> Plus, there's no way to go back to the existing behavior.
>
> --
> nathan
>


^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-13 10:03  Frédéric Yhuel <[email protected]>
  parent: Nathan Bossart <[email protected]>
  1 sibling, 2 replies; 16+ messages in thread

From: Frédéric Yhuel @ 2024-11-13 10:03 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; wenhui qiu <[email protected]>; +Cc: Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>



On 11/9/24 16:59, Nathan Bossart wrote:
> AFAICT the main advantage of these formulas is that you don't need another
> GUC, but they also makes the existing ones more difficult to configure.

I wouldn't say that's the main advantage. It doesn't seem very clean to 
me to cap to a fixed value. Because you could take Robert's 
demonstration with a bigger table, and come to the same conclusion:

Let's compare the current situation to the situation post-Nathan's-patch 
with a cap of 100M. Consider a table 100 times larger than the one of 
Robert's previous example, so pgbench scale factor 2_560_000, size on 
disk 32TB.
Currently, that table will be vacuumed for bloat when the number of
dead tuples exceeds 20% of the table size, because that's the default
value of autovacuum_vacuum_scale_factor. The table has 256 billion
tuples, so that means that we're going to vacuum it when there are
more than 51 billion dead tuples. Post-patch, we will vacuum when we
have 100 million dead tuples. Suppose a uniform workload that slowly
updates rows in the table. If we were previously autovacuuming the
table once per day (1440 minutes) we're now going to try to vacuum it
almost every minute (1440 minutes / 512 = 168 seconds).

(compare with every 55 min with my formula)

Of course, this a theoretical example that is probably unrealistic. I 
don't know, really. I don't know if Robert's example was realistic in 
the first place.

In any case, we should do the tests that Robert suggested and/or come up 
with a good mathematical model, because we are in the dark at the moment.

> Plus, there's no way to go back to the existing behavior.

I think we should indeed provide a retro-compatible behaviour (so maybe 
another GUC after all).







^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2024-11-13 10:33  wenhui qiu <[email protected]>
  parent: Frédéric Yhuel <[email protected]>
  1 sibling, 1 reply; 16+ messages in thread

From: wenhui qiu @ 2024-11-13 10:33 UTC (permalink / raw)
  To: Frédéric Yhuel <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

HI
> In any case, we should do the tests that Robert suggested and/or come up
> with a good mathematical model, because we are in the dark at the moment.
I think SQL Server has given us great inspiration
>I think we should indeed provide a retro-compatible behaviour (so maybe
> another GUC after all).
I am ready to implement a new guc parameter,Enable database administrators
to configure appropriate calculation methods（The default value is the
original calculation formula）


Frédéric Yhuel <[email protected]> 于2024年11月13日周三 18:03写道：

>
>
> On 11/9/24 16:59, Nathan Bossart wrote:
> > AFAICT the main advantage of these formulas is that you don't need
> another
> > GUC, but they also makes the existing ones more difficult to configure.
>
> I wouldn't say that's the main advantage. It doesn't seem very clean to
> me to cap to a fixed value. Because you could take Robert's
> demonstration with a bigger table, and come to the same conclusion:
>
> Let's compare the current situation to the situation post-Nathan's-patch
> with a cap of 100M. Consider a table 100 times larger than the one of
> Robert's previous example, so pgbench scale factor 2_560_000, size on
> disk 32TB.
> Currently, that table will be vacuumed for bloat when the number of
> dead tuples exceeds 20% of the table size, because that's the default
> value of autovacuum_vacuum_scale_factor. The table has 256 billion
> tuples, so that means that we're going to vacuum it when there are
> more than 51 billion dead tuples. Post-patch, we will vacuum when we
> have 100 million dead tuples. Suppose a uniform workload that slowly
> updates rows in the table. If we were previously autovacuuming the
> table once per day (1440 minutes) we're now going to try to vacuum it
> almost every minute (1440 minutes / 512 = 168 seconds).
>
> (compare with every 55 min with my formula)
>
> Of course, this a theoretical example that is probably unrealistic. I
> don't know, really. I don't know if Robert's example was realistic in
> the first place.
>
> In any case, we should do the tests that Robert suggested and/or come up
> with a good mathematical model, because we are in the dark at the moment.
>
> > Plus, there's no way to go back to the existing behavior.
>
> I think we should indeed provide a retro-compatible behaviour (so maybe
> another GUC after all).
>
>


^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-07 22:57  Nathan Bossart <[email protected]>
  parent: wenhui qiu <[email protected]>
  0 siblings, 1 reply; 16+ messages in thread

From: Nathan Bossart @ 2025-01-07 22:57 UTC (permalink / raw)
  To: wenhui qiu <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

Here is a rebased patch for cfbot.  AFAICT we are still pretty far from
consensus on which approach to take, unfortunately.

-- 
nathan

From df17b1f8b18300bc8426c06ab4e4d2d1c9169d85 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 7 Aug 2024 16:22:37 -0500
Subject: [PATCH v3 1/1] autovacuum_max_threshold

---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 11 +++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 9 files changed, 76 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf53..3c03bdd5790 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8712,6 +8712,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+      <term><varname>autovacuum_max_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>autovacuum_max_threshold</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum number of updated or deleted tuples needed to
+        trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+        the value calculated with
+        <varname>autovacuum_vacuum_threshold</varname> and
+        <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+        100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+        maximum number of updated or deleted tuples that will trigger a
+        <command>VACUUM</command> operation.  This parameter can only be set in
+        the <filename>postgresql.conf</filename> file or on the server command
+        line; but the setting can be overridden for individual tables by
+        changing storage parameters.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
       <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 70fa929caa4..77cee4a2888 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1682,6 +1682,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+    <term><literal>autovacuum_max_threshold</literal>, <literal>toast.autovacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_max_threshold</varname></primary>
+     <secondary>storage parameter</secondary>
+    </indexterm>
+    </term>
+   <listitem>
+    <para>
+     Per-table value for <xref linkend="guc-autovacuum-max-threshold"/>
+     parameter.
+    </para>
+   </listitem>
+  </varlistentry>
+
    <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
     <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
     <indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index e587abd9990..c28c7517aeb 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 0, INT_MAX
 	},
+	{
+		{
+			"autovacuum_max_threshold",
+			"Maximum number of tuple updates or deletes prior to vacuum",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-1, 0, INT_MAX
+	},
 	{
 		{
 			"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)},
 		{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+		{"autovacuum_max_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)},
 		{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
 		{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..680b3dcae48 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -120,6 +120,7 @@ int			autovacuum_max_workers;
 int			autovacuum_work_mem = -1;
 int			autovacuum_naptime;
 int			autovacuum_vac_thresh;
+int			autovacuum_max_thresh;
 double		autovacuum_vac_scale;
 int			autovacuum_vac_ins_thresh;
 double		autovacuum_vac_ins_scale;
@@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
  * threshold.  This threshold is calculated as
  *
  * threshold = vac_base_thresh + vac_scale_factor * reltuples
+ * if (threshold > vac_max_thresh)
+ *     threshold = vac_max_thres;
  *
  * For analyze, the analysis done is that the number of tuples inserted,
  * deleted and updated since the last analyze exceeds a threshold calculated
@@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid,
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
+				vac_max_thresh,
 				vac_ins_base_thresh,
 				anl_base_thresh;
 	float4		vac_scale_factor,
@@ -2974,6 +2978,10 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= 0)
+		? relopts->vacuum_max_threshold
+		: autovacuum_max_thresh;
+
 	vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
 		? relopts->vacuum_ins_scale_factor
 		: autovacuum_vac_ins_scale;
@@ -3047,6 +3055,9 @@ relation_needs_vacanalyze(Oid relid,
 			reltuples = 0;
 
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
+			vacthresh = (float4) vac_max_thresh;
+
 		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..2a18ba80c87 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3425,6 +3425,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..d0e1c9b53c0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -667,6 +667,8 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_max_threshold = 100000000	# max number of row updates before
+					# vacuum
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa28..6ede5090cc0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1361,6 +1361,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_freeze_max_age",
 	"autovacuum_freeze_min_age",
 	"autovacuum_freeze_table_age",
+	"autovacuum_max_threshold",
 	"autovacuum_multixact_freeze_max_age",
 	"autovacuum_multixact_freeze_min_age",
 	"autovacuum_multixact_freeze_table_age",
@@ -1377,6 +1378,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_freeze_max_age",
 	"toast.autovacuum_freeze_min_age",
 	"toast.autovacuum_freeze_table_age",
+	"toast.autovacuum_max_threshold",
 	"toast.autovacuum_multixact_freeze_max_age",
 	"toast.autovacuum_multixact_freeze_min_age",
 	"toast.autovacuum_multixact_freeze_table_age",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 54e01c81d68..b5c7b9b8abb 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 33d1e4a4e2e..48b95f211f3 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.5 (Apple Git-154)



Attachments:

  [text/plain] v3-0001-autovacuum_max_threshold.patch (9.9K, ../../Z32xZZwOi5pXZjYU@nathan/2-v3-0001-autovacuum_max_threshold.patch)
  download | inline diff:
From df17b1f8b18300bc8426c06ab4e4d2d1c9169d85 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 7 Aug 2024 16:22:37 -0500
Subject: [PATCH v3 1/1] autovacuum_max_threshold

---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 11 +++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 9 files changed, 76 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf53..3c03bdd5790 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8712,6 +8712,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+      <term><varname>autovacuum_max_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>autovacuum_max_threshold</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum number of updated or deleted tuples needed to
+        trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+        the value calculated with
+        <varname>autovacuum_vacuum_threshold</varname> and
+        <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+        100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+        maximum number of updated or deleted tuples that will trigger a
+        <command>VACUUM</command> operation.  This parameter can only be set in
+        the <filename>postgresql.conf</filename> file or on the server command
+        line; but the setting can be overridden for individual tables by
+        changing storage parameters.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
       <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 70fa929caa4..77cee4a2888 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1682,6 +1682,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+    <term><literal>autovacuum_max_threshold</literal>, <literal>toast.autovacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_max_threshold</varname></primary>
+     <secondary>storage parameter</secondary>
+    </indexterm>
+    </term>
+   <listitem>
+    <para>
+     Per-table value for <xref linkend="guc-autovacuum-max-threshold"/>
+     parameter.
+    </para>
+   </listitem>
+  </varlistentry>
+
    <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
     <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
     <indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index e587abd9990..c28c7517aeb 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 0, INT_MAX
 	},
+	{
+		{
+			"autovacuum_max_threshold",
+			"Maximum number of tuple updates or deletes prior to vacuum",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-1, 0, INT_MAX
+	},
 	{
 		{
 			"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)},
 		{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+		{"autovacuum_max_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)},
 		{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
 		{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..680b3dcae48 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -120,6 +120,7 @@ int			autovacuum_max_workers;
 int			autovacuum_work_mem = -1;
 int			autovacuum_naptime;
 int			autovacuum_vac_thresh;
+int			autovacuum_max_thresh;
 double		autovacuum_vac_scale;
 int			autovacuum_vac_ins_thresh;
 double		autovacuum_vac_ins_scale;
@@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
  * threshold.  This threshold is calculated as
  *
  * threshold = vac_base_thresh + vac_scale_factor * reltuples
+ * if (threshold > vac_max_thresh)
+ *     threshold = vac_max_thres;
  *
  * For analyze, the analysis done is that the number of tuples inserted,
  * deleted and updated since the last analyze exceeds a threshold calculated
@@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid,
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
+				vac_max_thresh,
 				vac_ins_base_thresh,
 				anl_base_thresh;
 	float4		vac_scale_factor,
@@ -2974,6 +2978,10 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= 0)
+		? relopts->vacuum_max_threshold
+		: autovacuum_max_thresh;
+
 	vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
 		? relopts->vacuum_ins_scale_factor
 		: autovacuum_vac_ins_scale;
@@ -3047,6 +3055,9 @@ relation_needs_vacanalyze(Oid relid,
 			reltuples = 0;
 
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
+			vacthresh = (float4) vac_max_thresh;
+
 		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..2a18ba80c87 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3425,6 +3425,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..d0e1c9b53c0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -667,6 +667,8 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_max_threshold = 100000000	# max number of row updates before
+					# vacuum
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa28..6ede5090cc0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1361,6 +1361,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_freeze_max_age",
 	"autovacuum_freeze_min_age",
 	"autovacuum_freeze_table_age",
+	"autovacuum_max_threshold",
 	"autovacuum_multixact_freeze_max_age",
 	"autovacuum_multixact_freeze_min_age",
 	"autovacuum_multixact_freeze_table_age",
@@ -1377,6 +1378,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_freeze_max_age",
 	"toast.autovacuum_freeze_min_age",
 	"toast.autovacuum_freeze_table_age",
+	"toast.autovacuum_max_threshold",
 	"toast.autovacuum_multixact_freeze_max_age",
 	"toast.autovacuum_multixact_freeze_min_age",
 	"toast.autovacuum_multixact_freeze_table_age",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 54e01c81d68..b5c7b9b8abb 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 33d1e4a4e2e..48b95f211f3 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.5 (Apple Git-154)



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-08 13:48  Frédéric Yhuel <[email protected]>
  parent: Nathan Bossart <[email protected]>
  0 siblings, 1 reply; 16+ messages in thread

From: Frédéric Yhuel @ 2025-01-08 13:48 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; wenhui qiu <[email protected]>; +Cc: Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>



On 1/7/25 23:57, Nathan Bossart wrote:
> Here is a rebased patch for cfbot.  AFAICT we are still pretty far from
> consensus on which approach to take, unfortunately.
> 

For what it's worth, although I would have preferred the sub-linear 
growth thing, I'd much rather have this than nothing.

And I have to admit that the proposed formulas were either too 
convoluted or wrong.

This very patch is more straightforward. Please let me know if I can 
help and how.







^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-08 20:01  Nathan Bossart <[email protected]>
  parent: Frédéric Yhuel <[email protected]>
  0 siblings, 2 replies; 16+ messages in thread

From: Nathan Bossart @ 2025-01-08 20:01 UTC (permalink / raw)
  To: Frédéric Yhuel <[email protected]>; +Cc: wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, Jan 08, 2025 at 02:48:10PM +0100, Frédéric Yhuel wrote:
> For what it's worth, although I would have preferred the sub-linear growth
> thing, I'd much rather have this than nothing.

+1, this is how I feel, too.  But I also don't want to add something that
folks won't find useful.

> And I have to admit that the proposed formulas were either too convoluted or
> wrong.
> 
> This very patch is more straightforward. Please let me know if I can help
> and how.

I read through the thread from the top, and it does seem like there is
reasonably strong support for the hard cap.  Upon a closer review of the
patch, I noticed that the relopt was defined such that you couldn't disable
autovacuum_max_threshold on a per-table basis, so I fixed that in v4.

-- 
nathan

From 6004127a57ff6062da7e4696cd9358d18e6b6141 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 8 Jan 2025 13:11:52 -0600
Subject: [PATCH v4 1/1] Introduce autovacuum_max_threshold.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Author: Nathan Bossart, Frédéric Yhuel
Reviewed-by: Melanie Plageman, Robert Haas, Laurenz Albe, Michael Banck, Joe Conway, Sami Imseih, David Rowley, wenhui qiu
Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 12 ++++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 9 files changed, 77 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf53..3c03bdd5790 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8712,6 +8712,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+      <term><varname>autovacuum_max_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>autovacuum_max_threshold</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum number of updated or deleted tuples needed to
+        trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+        the value calculated with
+        <varname>autovacuum_vacuum_threshold</varname> and
+        <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+        100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+        maximum number of updated or deleted tuples that will trigger a
+        <command>VACUUM</command> operation.  This parameter can only be set in
+        the <filename>postgresql.conf</filename> file or on the server command
+        line; but the setting can be overridden for individual tables by
+        changing storage parameters.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
       <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 70fa929caa4..77cee4a2888 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1682,6 +1682,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+    <term><literal>autovacuum_max_threshold</literal>, <literal>toast.autovacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_max_threshold</varname></primary>
+     <secondary>storage parameter</secondary>
+    </indexterm>
+    </term>
+   <listitem>
+    <para>
+     Per-table value for <xref linkend="guc-autovacuum-max-threshold"/>
+     parameter.
+    </para>
+   </listitem>
+  </varlistentry>
+
    <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
     <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
     <indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index e587abd9990..fbae300a128 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 0, INT_MAX
 	},
+	{
+		{
+			"autovacuum_max_threshold",
+			"Maximum number of tuple updates or deletes prior to vacuum",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-2, -1, INT_MAX
+	},
 	{
 		{
 			"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)},
 		{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+		{"autovacuum_max_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)},
 		{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
 		{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..ea48fba73f8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -120,6 +120,7 @@ int			autovacuum_max_workers;
 int			autovacuum_work_mem = -1;
 int			autovacuum_naptime;
 int			autovacuum_vac_thresh;
+int			autovacuum_max_thresh;
 double		autovacuum_vac_scale;
 int			autovacuum_vac_ins_thresh;
 double		autovacuum_vac_ins_scale;
@@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
  * threshold.  This threshold is calculated as
  *
  * threshold = vac_base_thresh + vac_scale_factor * reltuples
+ * if (threshold > vac_max_thresh)
+ *     threshold = vac_max_thres;
  *
  * For analyze, the analysis done is that the number of tuples inserted,
  * deleted and updated since the last analyze exceeds a threshold calculated
@@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid,
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
+				vac_max_thresh,
 				vac_ins_base_thresh,
 				anl_base_thresh;
 	float4		vac_scale_factor,
@@ -2974,6 +2978,11 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	/* -1 is used to disable max threshold */
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= -1)
+		? relopts->vacuum_max_threshold
+		: autovacuum_max_thresh;
+
 	vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
 		? relopts->vacuum_ins_scale_factor
 		: autovacuum_vac_ins_scale;
@@ -3047,6 +3056,9 @@ relation_needs_vacanalyze(Oid relid,
 			reltuples = 0;
 
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
+			vacthresh = (float4) vac_max_thresh;
+
 		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..2a18ba80c87 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3425,6 +3425,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..d0e1c9b53c0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -667,6 +667,8 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_max_threshold = 100000000	# max number of row updates before
+					# vacuum
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa28..6ede5090cc0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1361,6 +1361,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_freeze_max_age",
 	"autovacuum_freeze_min_age",
 	"autovacuum_freeze_table_age",
+	"autovacuum_max_threshold",
 	"autovacuum_multixact_freeze_max_age",
 	"autovacuum_multixact_freeze_min_age",
 	"autovacuum_multixact_freeze_table_age",
@@ -1377,6 +1378,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_freeze_max_age",
 	"toast.autovacuum_freeze_min_age",
 	"toast.autovacuum_freeze_table_age",
+	"toast.autovacuum_max_threshold",
 	"toast.autovacuum_multixact_freeze_max_age",
 	"toast.autovacuum_multixact_freeze_min_age",
 	"toast.autovacuum_multixact_freeze_table_age",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 54e01c81d68..b5c7b9b8abb 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 33d1e4a4e2e..48b95f211f3 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.5 (Apple Git-154)



Attachments:

  [text/plain] v4-0001-Introduce-autovacuum_max_threshold.patch (10.3K, ../../Z37ZlVqUzSapV3ZD@nathan/2-v4-0001-Introduce-autovacuum_max_threshold.patch)
  download | inline diff:
From 6004127a57ff6062da7e4696cd9358d18e6b6141 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 8 Jan 2025 13:11:52 -0600
Subject: [PATCH v4 1/1] Introduce autovacuum_max_threshold.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Author: Nathan Bossart, Frédéric Yhuel
Reviewed-by: Melanie Plageman, Robert Haas, Laurenz Albe, Michael Banck, Joe Conway, Sami Imseih, David Rowley, wenhui qiu
Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 12 ++++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 9 files changed, 77 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf53..3c03bdd5790 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8712,6 +8712,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+      <term><varname>autovacuum_max_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>autovacuum_max_threshold</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum number of updated or deleted tuples needed to
+        trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+        the value calculated with
+        <varname>autovacuum_vacuum_threshold</varname> and
+        <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+        100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+        maximum number of updated or deleted tuples that will trigger a
+        <command>VACUUM</command> operation.  This parameter can only be set in
+        the <filename>postgresql.conf</filename> file or on the server command
+        line; but the setting can be overridden for individual tables by
+        changing storage parameters.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
       <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 70fa929caa4..77cee4a2888 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1682,6 +1682,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+    <term><literal>autovacuum_max_threshold</literal>, <literal>toast.autovacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_max_threshold</varname></primary>
+     <secondary>storage parameter</secondary>
+    </indexterm>
+    </term>
+   <listitem>
+    <para>
+     Per-table value for <xref linkend="guc-autovacuum-max-threshold"/>
+     parameter.
+    </para>
+   </listitem>
+  </varlistentry>
+
    <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
     <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
     <indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index e587abd9990..fbae300a128 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 0, INT_MAX
 	},
+	{
+		{
+			"autovacuum_max_threshold",
+			"Maximum number of tuple updates or deletes prior to vacuum",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-2, -1, INT_MAX
+	},
 	{
 		{
 			"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)},
 		{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+		{"autovacuum_max_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)},
 		{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
 		{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..ea48fba73f8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -120,6 +120,7 @@ int			autovacuum_max_workers;
 int			autovacuum_work_mem = -1;
 int			autovacuum_naptime;
 int			autovacuum_vac_thresh;
+int			autovacuum_max_thresh;
 double		autovacuum_vac_scale;
 int			autovacuum_vac_ins_thresh;
 double		autovacuum_vac_ins_scale;
@@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
  * threshold.  This threshold is calculated as
  *
  * threshold = vac_base_thresh + vac_scale_factor * reltuples
+ * if (threshold > vac_max_thresh)
+ *     threshold = vac_max_thres;
  *
  * For analyze, the analysis done is that the number of tuples inserted,
  * deleted and updated since the last analyze exceeds a threshold calculated
@@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid,
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
+				vac_max_thresh,
 				vac_ins_base_thresh,
 				anl_base_thresh;
 	float4		vac_scale_factor,
@@ -2974,6 +2978,11 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	/* -1 is used to disable max threshold */
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= -1)
+		? relopts->vacuum_max_threshold
+		: autovacuum_max_thresh;
+
 	vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
 		? relopts->vacuum_ins_scale_factor
 		: autovacuum_vac_ins_scale;
@@ -3047,6 +3056,9 @@ relation_needs_vacanalyze(Oid relid,
 			reltuples = 0;
 
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
+			vacthresh = (float4) vac_max_thresh;
+
 		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..2a18ba80c87 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3425,6 +3425,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..d0e1c9b53c0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -667,6 +667,8 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_max_threshold = 100000000	# max number of row updates before
+					# vacuum
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa28..6ede5090cc0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1361,6 +1361,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_freeze_max_age",
 	"autovacuum_freeze_min_age",
 	"autovacuum_freeze_table_age",
+	"autovacuum_max_threshold",
 	"autovacuum_multixact_freeze_max_age",
 	"autovacuum_multixact_freeze_min_age",
 	"autovacuum_multixact_freeze_table_age",
@@ -1377,6 +1378,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_freeze_max_age",
 	"toast.autovacuum_freeze_min_age",
 	"toast.autovacuum_freeze_table_age",
+	"toast.autovacuum_max_threshold",
 	"toast.autovacuum_multixact_freeze_max_age",
 	"toast.autovacuum_multixact_freeze_min_age",
 	"toast.autovacuum_multixact_freeze_table_age",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 54e01c81d68..b5c7b9b8abb 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 33d1e4a4e2e..48b95f211f3 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.5 (Apple Git-154)



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-08 21:32  Vinícius Abrahão <[email protected]>
  parent: Nathan Bossart <[email protected]>
  1 sibling, 1 reply; 16+ messages in thread

From: Vinícius Abrahão @ 2025-01-08 21:32 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, Jan 8, 2025 at 8:01 PM Nathan Bossart <[email protected]>
wrote:

> On Wed, Jan 08, 2025 at 02:48:10PM +0100, Frédéric Yhuel wrote:
> > For what it's worth, although I would have preferred the sub-linear
> growth
> > thing, I'd much rather have this than nothing.
>
> +1, this is how I feel, too.  But I also don't want to add something that
> folks won't find useful.
>
> > And I have to admit that the proposed formulas were either too
> convoluted or
> > wrong.
> >
> > This very patch is more straightforward. Please let me know if I can help
> > and how.
>
> I read through the thread from the top, and it does seem like there is
> reasonably strong support for the hard cap.  Upon a closer review of the
> patch, I noticed that the relopt was defined such that you couldn't disable
> autovacuum_max_threshold on a per-table basis, so I fixed that in v4.
>
> --
> nathan
>


nathan,

Please also provide the tests on the new parameter you want to introduce.

Best,
vini


^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-09 00:01  Robert Treat <[email protected]>
  parent: Nathan Bossart <[email protected]>
  1 sibling, 0 replies; 16+ messages in thread

From: Robert Treat @ 2025-01-09 00:01 UTC (permalink / raw)
  To: Nathan Bossart <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, Jan 8, 2025 at 3:01 PM Nathan Bossart <[email protected]> wrote:
>
> On Wed, Jan 08, 2025 at 02:48:10PM +0100, Frédéric Yhuel wrote:
> > For what it's worth, although I would have preferred the sub-linear growth
> > thing, I'd much rather have this than nothing.
>
> +1, this is how I feel, too.  But I also don't want to add something that
> folks won't find useful.
>
> > And I have to admit that the proposed formulas were either too convoluted or
> > wrong.
> >
> > This very patch is more straightforward. Please let me know if I can help
> > and how.
>
> I read through the thread from the top, and it does seem like there is
> reasonably strong support for the hard cap.  Upon a closer review of the
> patch, I noticed that the relopt was defined such that you couldn't disable
> autovacuum_max_threshold on a per-table basis, so I fixed that in v4.
>

To be frank, this patch feels like a solution in search of a problem,
and as I read back through the thread, it isn't clear what problem
this is intended to fix.

There is some talk of "simplifying" autovacuum configuration, but some
noted that we already have a rather complex set of GUCs to deal with,
and adding another one, along with more math, into the equation
doesn't seem simpler to mel I'd like to think the bar should be that
the problem should be clear. So what is the problem?

Is the patch supposed to help with wraparound prevention?
autovac_freeze_max_age already covers that, and when it doesn't
vacuum_failsafe_age helps out.

A couple of people mentioned issues around hitting the index wall when
vacuuming large tables, but we believe that problem is mostly resolved
due to radix based tid storage, so this doesn't solve that. (To the
degree you don't think v17 has baked into enough production workloads
to be sure, I'd agree, but that's also an argument against doing more
work that might not be needed)

Maybe the hope is that this setting will cause vacuum to run more
often to help ameliorate i/o work from freeze vacuums kicking in, but
I suspect that Melanie's nearby work on eager vacuuming is a smarter
solution towards this problem (warning, it also may want to add more
gucs), so I think we're not solving that, and in fact might be
undercutting it.

I guess that means this is supposed to help with bloat management? but
only on large tables? I guess because you run vacuums more often?
Except that the adages of running vacuums more often don't apply as
cleanly to large tables, because those tables typically come with
large indexes, and while we have a lot of machinery in place to help
with repeated scans of the heap, that same machinery doesn't exist for
scanning the indexes, which gives you sort of an exponential curve
around vacuum times as table size (but actually index size) grows
larger. On the upside, this does mean we're less likely to see a 50x
boost in vacuums on large tables that some seemed concerned about, but
on the downside its because we're probably going to increase the
probability of vacuum worker starvation.

But getting back to goals, if your goal is to help with bloat
management, trying to tie that to a number that doesn't cleanly map to
the meta information of the table in question is a poor way to do it.
Meaning, to the degree that you are skeptical that vacuuming based on
20% of the rows of a table might not really be 20% of the size of the
table, it's certainly going to be a closer map than 100million rows in
a n number of tables of unknown (but presumably greater than
500million?) numbers of rows of unknown sizes. And again, we have a
means to tackle these bloat cases already; lowering
vacuum_scale_factor.

This isn't to say the system is perfect; I do think there are some
fundamental issues that need addressing, but adding this guc just
feels a little less baked than usual.

Robert Treat
https://xzilla.net






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-09 18:20  Nathan Bossart <[email protected]>
  parent: Vinícius Abrahão <[email protected]>
  0 siblings, 0 replies; 16+ messages in thread

From: Nathan Bossart @ 2025-01-09 18:20 UTC (permalink / raw)
  To: Vinícius Abrahão <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, Jan 08, 2025 at 09:32:58PM +0000, Vinícius Abrahão wrote:
> Please also provide the tests on the new parameter you want to introduce.

I skimmed around and didn't see any existing tests for these kinds of
parameters, which of course isn't a great reason not to add tests, but it's
also not clear what such tests might look like.  Do you have any ideas?

-- 
nathan






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-14 19:30  Robert Haas <[email protected]>
  parent: Frédéric Yhuel <[email protected]>
  1 sibling, 0 replies; 16+ messages in thread

From: Robert Haas @ 2025-01-14 19:30 UTC (permalink / raw)
  To: Frédéric Yhuel <[email protected]>; +Cc: Nathan Bossart <[email protected]>; wenhui qiu <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, Nov 13, 2024 at 5:03 AM Frédéric Yhuel
<[email protected]> wrote:
> Let's compare the current situation to the situation post-Nathan's-patch
> with a cap of 100M. Consider a table 100 times larger than the one of
> Robert's previous example, so pgbench scale factor 2_560_000, size on
> disk 32TB.

This is a great thought experiment.

> Currently, that table will be vacuumed for bloat when the number of
> dead tuples exceeds 20% of the table size, because that's the default
> value of autovacuum_vacuum_scale_factor. The table has 256 billion
> tuples, so that means that we're going to vacuum it when there are
> more than 51 billion dead tuples.

Are we, though? In previous releases, maintenance_work_mem was capped
at 1GB, and it took 6 bytes per dead TID, so we were limited to 1/6 of
a billion dead tuples per indexvac cycle. So, if we really were
vacuuming only every 51 billion dead tuples, we would be doing about
300 indexvac cycles per vacuum. I think somebody in this situation
would likely have needed to adjust the settings or things would just
stop working, long before they got to this point. It's not impossible
that somebody out there has a low-criticality, largely-unmonitored
system that is like this, but I've never seen anything like it.

> Post-patch, we will vacuum when we
> have 100 million dead tuples. Suppose a uniform workload that slowly
> updates rows in the table. If we were previously autovacuuming the
> table once per day (1440 minutes) we're now going to try to vacuum it
> almost every minute (1440 minutes / 512 = 168 seconds).
>
> (compare with every 55 min with my formula)

If we were vacuuming the table one per day, and it had 51 billion dead
tuples each time, that would mean we were generating ~591000 dead
tuples per second during that day. I'm not sure that's physically
possible with PostgreSQL on any hardware. I am almost positive that
you couldn't get by with vacuuming once a day if you were. I actually
think there's no amount of vacuuming that can turn this into a
success, at least on old releases with the 1GB limit, and maybe even
now.  In 168 seconds you would have generated almost 100 million dead
tuples, which is already closing in on the 1GB autovacuum_work_mem
limit, so you probably need to vacuum at least that often to avoid
having to do multiple indexvac passes, but you also probably can't
finish vacuuming the table in 168 seconds, so you're probably just
going to get runaway bloat no matter what you do. The new dead TID
store should help, but I suspect if you are generating dead tuples
this fast on a table this large you are in a lot of trouble even on
the latest release.

> Of course, this a theoretical example that is probably unrealistic. I
> don't know, really. I don't know if Robert's example was realistic in
> the first place.

I intended it to be realistic, but I might not have entirely
succeeded. Even if it's a bit off, I think it's far closer to being
realistic as I presented it than in your hundred-times-larger
scenario, which makes me think that the higher cap is more sensible
than the one you originally proposed. I don't think that the argument
I made can be scaled up or down by an arbitrary multiple without
becoming silly. The practical limits here have to do with the
capabilities of the hardware that is possible to buy, and they'll need
to be adjusted if, say, disks get ten times bigger and a hundred times
faster and memory becomes cheap as water.

-- 
Robert Haas
EDB: http://www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

end of thread, other threads:[~2025-01-14 19:30 UTC | newest]

Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-26 10:49 [PATCH v3 4/7] Row pattern recognition patch (executor). Tatsuo Ishii <[email protected]>
2024-08-12 13:41 Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-11-06 12:51 ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
2024-11-08 17:44   ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2024-11-09 14:08     ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
2024-11-09 15:59       ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2024-11-10 11:25         ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
2024-11-13 10:03         ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-11-13 10:33           ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
2025-01-07 22:57             ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-08 13:48               ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2025-01-08 20:01                 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-08 21:32                   ` Re: New GUC autovacuum_max_threshold ? Vinícius Abrahão <[email protected]>
2025-01-09 18:20                     ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-09 00:01                   ` Re: New GUC autovacuum_max_threshold ? Robert Treat <[email protected]>
2025-01-14 19:30           ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox