public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v9 6/7] Row pattern recognition patch (tests).
17+ messages / 7 participants
[nested] [flat]
* [PATCH v9 6/7] Row pattern recognition patch (tests).
@ 2023-10-04 05:51 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Tatsuo Ishii @ 2023-10-04 05:51 UTC (permalink / raw)
---
src/test/regress/expected/rpr.out | 633 +++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/rpr.sql | 308 ++++++++++++++
3 files changed, 942 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/rpr.out
create mode 100644 src/test/regress/sql/rpr.sql
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
new file mode 100644
index 0000000000..194f763bce
--- /dev/null
+++ b/src/test/regress/expected/rpr.out
@@ -0,0 +1,633 @@
+--
+-- Test for row pattern definition clause
+--
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+);
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+SELECT * FROM stock;
+ company | tdate | price
+----------+------------+-------
+ company1 | 07-01-2023 | 100
+ company1 | 07-02-2023 | 200
+ company1 | 07-03-2023 | 150
+ company1 | 07-04-2023 | 140
+ company1 | 07-05-2023 | 150
+ company1 | 07-06-2023 | 90
+ company1 | 07-07-2023 | 110
+ company1 | 07-08-2023 | 130
+ company1 | 07-09-2023 | 120
+ company1 | 07-10-2023 | 130
+ company2 | 07-01-2023 | 50
+ company2 | 07-02-2023 | 2000
+ company2 | 07-03-2023 | 1500
+ company2 | 07-04-2023 | 1400
+ company2 | 07-05-2023 | 1500
+ company2 | 07-06-2023 | 60
+ company2 | 07-07-2023 | 1100
+ company2 | 07-08-2023 | 1300
+ company2 | 07-09-2023 | 1200
+ company2 | 07-10-2023 | 1300
+(20 rows)
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- last_value() should remain consistent
+SELECT company, tdate, price, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | last_value
+----------+------------+-------+------------
+ company1 | 07-01-2023 | 100 | 140
+ company1 | 07-02-2023 | 200 |
+ company1 | 07-03-2023 | 150 |
+ company1 | 07-04-2023 | 140 |
+ company1 | 07-05-2023 | 150 |
+ company1 | 07-06-2023 | 90 | 120
+ company1 | 07-07-2023 | 110 |
+ company1 | 07-08-2023 | 130 |
+ company1 | 07-09-2023 | 120 |
+ company1 | 07-10-2023 | 130 |
+ company2 | 07-01-2023 | 50 | 1400
+ company2 | 07-02-2023 | 2000 |
+ company2 | 07-03-2023 | 1500 |
+ company2 | 07-04-2023 | 1400 |
+ company2 | 07-05-2023 | 1500 |
+ company2 | 07-06-2023 | 60 | 1200
+ company2 | 07-07-2023 | 1100 |
+ company2 | 07-08-2023 | 1300 |
+ company2 | 07-09-2023 | 1200 |
+ company2 | 07-10-2023 | 1300 |
+(20 rows)
+
+-- omit "START" in DEFINE but it is ok because "START AS TRUE" is
+-- implicitly defined. per spec.
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | 90 | 120
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | 60 | 1200
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- match everything
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 130
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1300
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023
+ company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023
+ company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w,
+ count(*) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | 07-01-2023 | 07-03-2023 | 3
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-06-2023 | 3
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-09-2023 | 3
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 07-01-2023 | 07-03-2023 | 3
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-06-2023 | 3
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-09-2023 | 3
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+--
+-- Aggregates
+--
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP PAST LAST ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | max | min | sum | avg | count
+----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
+ company1 | 07-02-2023 | 200 | | | | | | |
+ company1 | 07-03-2023 | 150 | | | | | | |
+ company1 | 07-04-2023 | 140 | | | | | | |
+ company1 | 07-05-2023 | 150 | | | | | | | 0
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | | | | | | |
+ company1 | 07-08-2023 | 130 | | | | | | |
+ company1 | 07-09-2023 | 120 | | | | | | |
+ company1 | 07-10-2023 | 130 | | | | | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | |
+ company2 | 07-03-2023 | 1500 | | | | | | |
+ company2 | 07-04-2023 | 1400 | | | | | | |
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | | | | | | |
+ company2 | 07-08-2023 | 1300 | | | | | | |
+ company2 | 07-09-2023 | 1200 | | | | | | |
+ company2 | 07-10-2023 | 1300 | | | | | | | 0
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP TO NEXT ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | max | min | sum | avg | count
+----------+------------+-------+-------------+------------+------+------+------+-----------------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
+ company1 | 07-02-2023 | 200 | | | | | | | 0
+ company1 | 07-03-2023 | 150 | | | | | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 90 | 150 | 90 | 380 | 126.6666666666666667 | 3
+ company1 | 07-05-2023 | 150 | | | | | | | 0
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | 110 | 120 | 130 | 110 | 360 | 120.0000000000000000 | 3
+ company1 | 07-08-2023 | 130 | | | | | | | 0
+ company1 | 07-09-2023 | 120 | | | | | | | 0
+ company1 | 07-10-2023 | 130 | | | | | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | | 0
+ company2 | 07-03-2023 | 1500 | | | | | | | 0
+ company2 | 07-04-2023 | 1400 | 1400 | 60 | 1500 | 60 | 2960 | 986.6666666666666667 | 3
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | 1100 | 1200 | 1300 | 1100 | 3600 | 1200.0000000000000000 | 3
+ company2 | 07-08-2023 | 1300 | | | | | | | 0
+ company2 | 07-09-2023 | 1200 | | | | | | | 0
+ company2 | 07-10-2023 | 1300 | | | | | | | 0
+(20 rows)
+
+--
+-- Error cases
+--
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+ERROR: syntax error at or near "ORDER"
+LINE 6: ORDER BY tdate
+ ^
+-- pattern variable name must appear in DEFINE
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: syntax error at or near "END"
+LINE 8: PATTERN (START UP+ DOWN+ END)
+ ^
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: FRAME must start at current row when row patttern recognition is used
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: SEEK is not supported
+LINE 8: SEEK
+ ^
+HINT: Use INITIAL.
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..896531002b 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -98,7 +98,7 @@ test: publication subscription
# Another group of parallel tests
# select_views depends on create_view
# ----------
-test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass
+test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr
# ----------
# Another group of parallel tests (JSON related)
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
new file mode 100644
index 0000000000..c19e0fed0d
--- /dev/null
+++ b/src/test/regress/sql/rpr.sql
@@ -0,0 +1,308 @@
+--
+-- Test for row pattern definition clause
+--
+
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+);
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+
+SELECT * FROM stock;
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- last_value() should remain consistent
+SELECT company, tdate, price, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- omit "START" in DEFINE but it is ok because "START AS TRUE" is
+-- implicitly defined. per spec.
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- match everything
+
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w,
+ count(*) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+--
+-- Aggregates
+--
+
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP PAST LAST ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP TO NEXT ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+
+--
+-- Error cases
+--
+
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+
+-- pattern variable name must appear in DEFINE
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
--
2.25.1
----Next_Part(Wed_Oct__4_15_03_28_2023_821)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v9-0007-Allow-to-print-raw-parse-tree.patch"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-04-26 13:40 Joe Conway <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Joe Conway @ 2024-04-26 13:40 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On 4/26/24 09:31, Robert Haas wrote:
> On Fri, Apr 26, 2024 at 9:22 AM Joe Conway <[email protected]> wrote:
>> Although I don't think 500000 is necessarily too small. In my view,
>> having autovac run very quickly, even if more frequently, provides an
>> overall better user experience.
>
> Can you elaborate on why you think that? I mean, to me, that's almost
> equivalent to removing autovacuum_vacuum_scale_factor entirely,
> because only for very small tables will that calculation produce a
> value lower than 500k.
If I understood Nathan's proposed calc, for small tables you would still
get (thresh + sf * numtuples). Once that number exceeds the new limit
parameter, then the latter would kick in. So small tables would retain
the current behavior and large enough tables would be clamped.
> We might need to try to figure out some test cases here. My intuition
> is that this is going to vacuum large tables insanely aggressively.
It depends on workload to be sure. Just because a table is large, it
doesn't mean that dead rows are generated that fast.
Admittedly it has been quite a while since I looked at all this that
closely, but if A/V runs on some large busy table for a few milliseconds
once every few minutes, that is far less disruptive than A/V running for
tens of seconds once every few hours or for minutes ones every few days
-- or whatever. The key thing to me is the "few milliseconds" runtime.
The short duration means that no one notices an impact, and the longer
duration almost guarantees that an impact will be felt.
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-04-26 14:12 Robert Haas <[email protected]>
parent: Joe Conway <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Robert Haas @ 2024-04-26 14:12 UTC (permalink / raw)
To: Joe Conway <[email protected]>; +Cc: Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On Fri, Apr 26, 2024 at 9:40 AM Joe Conway <[email protected]> wrote:
> > Can you elaborate on why you think that? I mean, to me, that's almost
> > equivalent to removing autovacuum_vacuum_scale_factor entirely,
> > because only for very small tables will that calculation produce a
> > value lower than 500k.
>
> If I understood Nathan's proposed calc, for small tables you would still
> get (thresh + sf * numtuples). Once that number exceeds the new limit
> parameter, then the latter would kick in. So small tables would retain
> the current behavior and large enough tables would be clamped.
Right. But with a 500k threshold, "large enough" is not very large at
all. The default scale factor is 20%, so the crossover point is at 2.5
million tuples. That's pgbench scale factor 25, which is a 320MB
table.
> It depends on workload to be sure. Just because a table is large, it
> doesn't mean that dead rows are generated that fast.
That is true, as far as it goes.
> Admittedly it has been quite a while since I looked at all this that
> closely, but if A/V runs on some large busy table for a few milliseconds
> once every few minutes, that is far less disruptive than A/V running for
> tens of seconds once every few hours or for minutes ones every few days
> -- or whatever. The key thing to me is the "few milliseconds" runtime.
> The short duration means that no one notices an impact, and the longer
> duration almost guarantees that an impact will be felt.
Sure, I mean, I totally agree with that, but how is a vacuum on a
large table going to run for milliseconds? If it can skip index
vacuuming, sure, then it's quick, because it only needs to scan the
heap pages that are not all-visible. But as soon as index vacuuming is
triggered, it's going to take a while. You can't afford to trigger
that constantly.
Let's compare the current situation to the situation post-patch with a
cap of 500k. Consider a table 1024 times larger than the one I
mentioned above, so pgbench scale factor 25600, size on disk 320GB.
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 2.56 billion
tuples, so that means that we're going to vacuum it when there are
more than 510 million dead tuples. Post-patch, we will vacuum when we
have 500 thousand 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 / 1024 = 84 seconds).
Unless I'm missing something major, that's completely bonkers. It
might be true that it would be a good idea to vacuum such a table more
often than we do at present, but there's no shot that we want to do it
that much more often. The pgbench_accounts_pkey index will, I believe,
be on the order of 8-10GB at that scale. We can't possibly want to
incur that much extra I/O every minute, and I don't think it's going
to finish in milliseconds, either.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-01 18:19 Imseih (AWS), Sami <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Imseih (AWS), Sami @ 2024-05-01 18:19 UTC (permalink / raw)
To: Robert Haas <[email protected]>; Joe Conway <[email protected]>; +Cc: Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
I've been following this discussion and would like to add my
2 cents.
> Unless I'm missing something major, that's completely bonkers. It
> might be true that it would be a good idea to vacuum such a table more
> often than we do at present, but there's no shot that we want to do it
> that much more often.
This is really an important point.
Too small of a threshold and a/v will constantly be vacuuming a fairly large
and busy table with many indexes.
If the threshold is large, say 100 or 200 million, I question if you want autovacuum
to be doing the work of cleanup here? That long of a period without a autovacuum
on a table means there maybe something misconfigured in your autovacuum settings.
At that point aren't you just better off performing a manual vacuum and
taking advantage of parallel index scans?
Regards,
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-01 18:50 Robert Haas <[email protected]>
parent: Imseih (AWS), Sami <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Robert Haas @ 2024-05-01 18:50 UTC (permalink / raw)
To: Imseih (AWS), Sami <[email protected]>; +Cc: Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
On Wed, May 1, 2024 at 2:19 PM Imseih (AWS), Sami <[email protected]> wrote:
> > Unless I'm missing something major, that's completely bonkers. It
> > might be true that it would be a good idea to vacuum such a table more
> > often than we do at present, but there's no shot that we want to do it
> > that much more often.
>
> This is really an important point.
>
> Too small of a threshold and a/v will constantly be vacuuming a fairly large
> and busy table with many indexes.
>
> If the threshold is large, say 100 or 200 million, I question if you want autovacuum
> to be doing the work of cleanup here? That long of a period without a autovacuum
> on a table means there maybe something misconfigured in your autovacuum settings.
>
> At that point aren't you just better off performing a manual vacuum and
> taking advantage of parallel index scans?
As far as that last point goes, it would be good if we taught
autovacuum about several things it doesn't currently know about;
parallelism is one. IMHO, it's probably not the most important one,
but it's certainly on the list. I think, though, that we should
confine ourselves on this thread to talking about what the threshold
ought to be.
And as far as that goes, I'd like you - and others - to spell out more
precisely why you think 100 or 200 million tuples is too much. It
might be, or maybe it is in some cases but not in others. To me,
that's not a terribly large amount of data. Unless your tuples are
very wide, it's a few tens of gigabytes. That is big enough that I can
believe that you *might* want autovacuum to run when you hit that
threshold, but it's definitely not *obvious* to me that you want
autovacuum to run when you hit that threshold.
To make that concrete: If the table is 10TB, do you want to vacuum to
reclaim 20GB of bloat? You might be vacuuming 5TB of indexes to
reclaim 20GB of heap space - is that the right thing to do? If yes,
why?
I do think it's interesting that other people seem to think we should
be vacuuming more often on tables that are substantially smaller than
the ones that seem like a big problem to me. I'm happy to admit that
my knowledge of this topic is not comprehensive and I'd like to learn
from the experience of others. But I think it's clearly and obviously
unworkable to multiply the current frequency of vacuuming for large
tables by a three or four digit number. Possibly what we need here is
something other than a cap, where, say, we vacuum a 10GB table twice
as often as now, a 100GB table four times as often, and a 1TB table
eight times as often. Or whatever the right answer is. But we can't
just pull numbers out of the air like that: we need to be able to
justify our choices. I think we all agree that big tables need to be
vacuumed more often than the current formula does, but we seem to be
rather far apart on the values of "big" and "more".
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-02 02:02 David Rowley <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: David Rowley @ 2024-05-02 02:02 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Sat, 27 Apr 2024 at 02:13, Robert Haas <[email protected]> wrote:
> Let's compare the current situation to the situation post-patch with a
> cap of 500k. Consider a table 1024 times larger than the one I
> mentioned above, so pgbench scale factor 25600, size on disk 320GB.
> 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 2.56 billion
> tuples, so that means that we're going to vacuum it when there are
> more than 510 million dead tuples. Post-patch, we will vacuum when we
> have 500 thousand 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 / 1024 = 84 seconds).
I've not checked your maths, but if that's true, that's not going to work.
I think there are fundamental problems with the parameters that drive
autovacuum that need to be addressed before we can consider a patch
like this one.
Here are some of the problems that I know about:
1. Autovacuum has exactly zero forward vision and operates reactively
rather than proactively. This "blind operating" causes tables to
either not need vacuumed or suddenly need vacuumed without any
consideration of how busy autovacuum is at that current moment.
2. There is no prioritisation for the order in which tables are autovacuumed.
3. With the default scale factor, the larger a table becomes, the more
infrequent the autovacuums.
4. Autovacuum is more likely to trigger when the system is busy
because more transaction IDs are being consumed and there is more DML
occurring. This results in autovacuum having less work to do during
quiet periods when there are more free resources to be doing the
vacuum work.
In my opinion, the main problem with Frédéric's proposed GUC/reloption
is that it increases the workload that autovacuum is responsible for
and, because of #2, it becomes more likely that autovacuum works on
some table that isn't the highest priority table to work on which can
result in autovacuum starvation of tables that are more important to
vacuum now.
I think we need to do a larger overhaul of autovacuum to improve
points 1-4 above. I also think that there's some work coming up that
might force us into this sooner than we think. As far as I understand
it, AIO will break vacuum_cost_page_miss because everything (providing
IO keeps up) will become vacuum_cost_page_hit. Maybe that's not that
important as that costing is quite terrible anyway.
Here's a sketch of an idea that's been in my head for a while:
Round 1:
1a) Give autovacuum forward vision (#1 above) and instead of vacuuming
a table when it (atomically) crosses some threshold, use the existing
scale_factors and autovacuum_freeze_max_age to give each table an
autovacuum "score", which could be a number from 0-100, where 0 means
do nothing and 100 means nuclear meltdown. Let's say a table gets 10
points for the dead tuples meeting the current scale_factor and maybe
an additional point for each 10% of proportion the size of the table
is according to the size of the database (gives some weight to space
recovery for larger tables). For relfrozenxid, make the score the
maximum of dead tuple score vs the percentage of the age(relfrozenxid)
is to 2 billion. Use a similar maximum score calc for age(relminmxid)
2 billion.
1b) Add a new GUC that defines the minimum score a table must reach
before autovacuum will consider it.
1c) Change autovacuum to vacuum the tables with the highest scores first.
Round 2:
2a) Have autovacuum monitor the score of the highest scoring table
over time with buckets for each power of 2 seconds in history from
now. Let's say 20 buckets, about 12 days of history. Migrate scores
into older buckets to track the score over time.
2b) Have autovacuum cost limits adapt according to the history so that
if the maximum score of any table is trending upwards, that autovacuum
speeds up until the score buckets trend downwards towards the present.
2c) Add another GUC to define the minimum score that autovacuum will
be "proactive". Must be less than the minimum score to consider
autovacuum (or at least, ignored unless it is.). This GUC would not
cause an autovacuum speedup due to 2b) as we'd only consider tables
which meet the GUC added in 1b) in the score history array in 2a).
This stops autovacuum running faster than autovacuum_cost_limit when
trying to be proactive.
While the above isn't well a well-baked idea. The exact way to
calculate the scores isn't well thought through, certainly. However, I
do think it's an idea that we should consider and improve upon. I
believe 2c) helps solve the problem of large tables becoming bloated
as autovacuum could get to these sooner when the workload is low
enough for it to run proactively.
I think we need at least 1a) before we can give autovacuum more work
to do, especially if we do something like multiply its workload by
1024x, per your comment above.
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-02 06:44 Frédéric Yhuel <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Frédéric Yhuel @ 2024-05-02 06:44 UTC (permalink / raw)
To: Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; +Cc: Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
Le 01/05/2024 à 20:50, Robert Haas a écrit :
> Possibly what we need here is
> something other than a cap, where, say, we vacuum a 10GB table twice
> as often as now, a 100GB table four times as often, and a 1TB table
> eight times as often. Or whatever the right answer is.
IMO, it would make more sense. So maybe something like this:
vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000);
(it could work to compute a score, too, like in David's proposal)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-02 16:01 Imseih (AWS), Sami <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Imseih (AWS), Sami @ 2024-05-02 16:01 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>; David Rowley <[email protected]>
> And as far as that goes, I'd like you - and others - to spell out more
> precisely why you think 100 or 200 million tuples is too much. It
> might be, or maybe it is in some cases but not in others. To me,
> that's not a terribly large amount of data. Unless your tuples are
> very wide, it's a few tens of gigabytes. That is big enough that I can
> believe that you *might* want autovacuum to run when you hit that
> threshold, but it's definitely not *obvious* to me that you want
> autovacuum to run when you hit that threshold.
Vacuuming the heap alone gets faster the more I do it, thanks to the
visibility map. However, the more indexes I have, and the larger ( in the TBs),
the indexes become, autovacuum workers will be
monopolized vacuuming these indexes.
At 500k tuples, I am constantly vacuuming large indexes
and monopolizing autovacuum workers. At 100 or 200
million tuples, I will also monopolize autovacuum workers
as they vacuums indexes for many minutes or hours.
At 100 or 200 million, the monopolization will occur less often,
but it will still occur leading an operator to maybe have to terminate
the autovacuum an kick of a manual vacuum.
I am not convinced a new tuple based threshold will address this,
but I may also may be misunderstanding the intention of this GUC.
> To make that concrete: If the table is 10TB, do you want to vacuum to
> reclaim 20GB of bloat? You might be vacuuming 5TB of indexes to
> reclaim 20GB of heap space - is that the right thing to do? If yes,
> why?
No, I would not want to run autovacuum on 5TB indexes to reclaim
a small amount of bloat.
Regards,
Sami
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-07 14:31 Robert Haas <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Robert Haas @ 2024-05-07 14:31 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Nathan Bossart <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, May 1, 2024 at 10:03 PM David Rowley <[email protected]> wrote:
> Here are some of the problems that I know about:
>
> 1. Autovacuum has exactly zero forward vision and operates reactively
> rather than proactively. This "blind operating" causes tables to
> either not need vacuumed or suddenly need vacuumed without any
> consideration of how busy autovacuum is at that current moment.
> 2. There is no prioritisation for the order in which tables are autovacuumed.
> 3. With the default scale factor, the larger a table becomes, the more
> infrequent the autovacuums.
> 4. Autovacuum is more likely to trigger when the system is busy
> because more transaction IDs are being consumed and there is more DML
> occurring. This results in autovacuum having less work to do during
> quiet periods when there are more free resources to be doing the
> vacuum work.
I agree with all of these points. For a while now, I've been thinking
that we really needed a prioritization scheme, so that we don't waste
our time on low-priority tasks when there are high-priority tasks that
need to be completed. But lately I've started to think that what
matters most is the rate at which autovacuum work is happening
overall. I feel like prioritization is mostly going to matter when
we're not keeping up, and I think the primary goal should be to keep
up. I think we could use the same data to make both decisions -- if
autovacuum were proactive rather than reactive, that would mean that
we know something about what is going to happen in the future, and I
think that data could be used both to decide whether we're keeping up,
and also to prioritize. But if I had to pick a first target, I'd
forget about trying to make things happen in the right order and just
try to make sure we get all the things done.
> I think we need at least 1a) before we can give autovacuum more work
> to do, especially if we do something like multiply its workload by
> 1024x, per your comment above.
I guess I view it differently. It seems to me that right now, we're
not vacuuming large tables often enough. We should fix that,
independently of anything else. If the result is that small and medium
sized tables get vacuumed less often, then that just means there were
never enough resources to go around in the first place. We haven't
taken a system that was working fine and broken it: we've just moved
the problem from one category of tables (the big ones) to a different
category of tables. If the user wants to solve that problem, they need
to bump up the cost limit or add hardware. I don't see that we have
any particular reason to believe such users will be worse off on
average than they are today. On the other hand, users who do have a
sufficiently high cost limit and enough hardware will be better off,
because we'll start doing all the vacuuming work that needs to be done
instead of only some of it.
Now, if we start vacuuming any class of table whatsoever 1024x as
often as we do today, we are going to lose. But that would still be
true even if we did everything on your list. Large tables need to be
vacuumed more frequently than we now do, but not THAT much more
frequently. Any system that produces that result is just using a wrong
algorithm, or wrong constants, or something. Even if all the necessary
resources are available, nobody is going to thank us for vacuuming
gigantic tables in a tight loop. The problem with such a large
increase is not that we don't have prioritization, but that such a
large increase is fundamentally the wrong thing to do. On the other
hand, I think a more modest increase is the right thing to do, and I
think it's the right thing to do whether we have prioritization or
not.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-07 21:17 Nathan Bossart <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2024-05-07 21:17 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, May 07, 2024 at 10:31:00AM -0400, Robert Haas wrote:
> On Wed, May 1, 2024 at 10:03 PM David Rowley <[email protected]> wrote:
>> I think we need at least 1a) before we can give autovacuum more work
>> to do, especially if we do something like multiply its workload by
>> 1024x, per your comment above.
>
> I guess I view it differently. It seems to me that right now, we're
> not vacuuming large tables often enough. We should fix that,
> independently of anything else. If the result is that small and medium
> sized tables get vacuumed less often, then that just means there were
> never enough resources to go around in the first place. We haven't
> taken a system that was working fine and broken it: we've just moved
> the problem from one category of tables (the big ones) to a different
> category of tables. If the user wants to solve that problem, they need
> to bump up the cost limit or add hardware. I don't see that we have
> any particular reason to believe such users will be worse off on
> average than they are today. On the other hand, users who do have a
> sufficiently high cost limit and enough hardware will be better off,
> because we'll start doing all the vacuuming work that needs to be done
> instead of only some of it.
>
> Now, if we start vacuuming any class of table whatsoever 1024x as
> often as we do today, we are going to lose. But that would still be
> true even if we did everything on your list. Large tables need to be
> vacuumed more frequently than we now do, but not THAT much more
> frequently. Any system that produces that result is just using a wrong
> algorithm, or wrong constants, or something. Even if all the necessary
> resources are available, nobody is going to thank us for vacuuming
> gigantic tables in a tight loop. The problem with such a large
> increase is not that we don't have prioritization, but that such a
> large increase is fundamentally the wrong thing to do. On the other
> hand, I think a more modest increase is the right thing to do, and I
> think it's the right thing to do whether we have prioritization or
> not.
This is about how I feel, too. In any case, I +1'd a higher default
because I think we need to be pretty conservative with these changes, at
least until we have a better prioritization strategy. While folks may opt
to set this value super low, I think that's more likely to lead to some
interesting secondary effects. If the default is high, hopefully these
secondary effects will be minimized or avoided.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-08 17:30 Imseih (AWS), Sami <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Imseih (AWS), Sami @ 2024-05-08 17:30 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Robert Haas <[email protected]>; +Cc: David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
> This is about how I feel, too. In any case, I +1'd a higher default
> because I think we need to be pretty conservative with these changes, at
> least until we have a better prioritization strategy. While folks may opt
> to set this value super low, I think that's more likely to lead to some
> interesting secondary effects. If the default is high, hopefully these
> secondary effects will be minimized or avoided.
There is also an alternative of making this GUC -1 by default, which
means it has not effect and any value larger will be used in the threshold
calculation of autovacuunm. A user will have to be careful not to set it too low,
but that is going to be a concern either way.
This idea maybe worth considering as it does not change the default
behavior of the autovac threshold calculation, and if a user has cases in
which they have many tables with a few billion tuples that they wish to
see autovacuumed more often, they now have a GUC to make
that possible and potentially avoid per-table threshold configuration.
Also, I think coming up with a good default will be challenging,
and perhaps this idea is a good middle ground.
Regards,
Sami
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-09 14:58 Robert Haas <[email protected]>
parent: Imseih (AWS), Sami <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Robert Haas @ 2024-05-09 14:58 UTC (permalink / raw)
To: Imseih (AWS), Sami <[email protected]>; +Cc: Nathan Bossart <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Frédéric Yhuel <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, May 8, 2024 at 1:30 PM Imseih (AWS), Sami <[email protected]> wrote:
> There is also an alternative of making this GUC -1 by default, which
> means it has not effect and any value larger will be used in the threshold
> calculation of autovacuunm. A user will have to be careful not to set it too low,
> but that is going to be a concern either way.
Personally, I'd much rather ship it with a reasonable default. If we
ship it disabled, most people won't end up using it at all, which
sucks, and those who do will be more likely to set it to a ridiculous
value, which also sucks. If we ship it with a value that has a good
chance of being within 2x or 3x of the optimal value on a given user's
system, then a lot more people will benefit from it.
> Also, I think coming up with a good default will be challenging,
> and perhaps this idea is a good middle ground.
Maybe. I freely admit that I don't know exactly what the optimal value
is here, and I think there is some experimentation that is needed to
try to get some better intuition there. At what table size does the
current system actually result in too little vacuuming, and how can we
demonstrate that? Does the point at which that happens depend more on
the table size in gigabytes, or more on the number of rows? These are
things that someone can research and about which they can present
data.
As I see it, a lot of the lack of agreement up until now is people
just not understanding the math. Since I think I've got the right idea
about the math, I attribute this to other people being confused about
what is going to happen and would tend to phrase it as: some people
don't understand how catastrophically bad it will be if you set this
value too low. However, another possibility is that it is I who am
misunderstanding the math. In that case, the correct phrasing is
probably something like: Robert wants a completely useless and
worthless value for this parameter that will be of no help to anyone.
Regardless, at least some of us are confused. If we can reduce that
confusion, then people's ideas about what values for this parameter
might be suitable should start to come closer together.
I tend to feel like the disagreement here is not really about whether
it's a good idea to increase the frequency of vacuuming on large
tables by three orders of magnitude compared to what we do now, but
rather than about whether that's actually going to happen.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-13 15:14 Frédéric Yhuel <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Frédéric Yhuel @ 2024-05-13 15:14 UTC (permalink / raw)
To: Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; +Cc: Nathan Bossart <[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]>
Le 09/05/2024 à 16:58, Robert Haas a écrit :
> As I see it, a lot of the lack of agreement up until now is people
> just not understanding the math. Since I think I've got the right idea
> about the math, I attribute this to other people being confused about
> what is going to happen and would tend to phrase it as: some people
> don't understand how catastrophically bad it will be if you set this
> value too low.
FWIW, I do agree with your math. I found your demonstration convincing.
500000 was selected with the wet finger.
Using the formula I suggested earlier:
vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000);
your table of 2.56 billion tuples will be vacuumed if there are
more than 10 million dead tuples (every 28 minutes).
If we want to stick with the simple formula, we should probably choose a
very high default, maybe 100 million, as you suggested earlier.
However, it would be nice to have the visibility map updated more
frequently than every 100 million dead tuples. I wonder if this could be
decoupled from the vacuum process?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-05-13 20:22 Robert Haas <[email protected]>
parent: Frédéric Yhuel <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Robert Haas @ 2024-05-13 20:22 UTC (permalink / raw)
To: Frédéric Yhuel <[email protected]>; +Cc: Imseih (AWS), Sami <[email protected]>; Nathan Bossart <[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 Mon, May 13, 2024 at 11:14 AM Frédéric Yhuel
<[email protected]> wrote:
> FWIW, I do agree with your math. I found your demonstration convincing.
> 500000 was selected with the wet finger.
Good to know.
> Using the formula I suggested earlier:
>
> vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples,
> vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000);
>
> your table of 2.56 billion tuples will be vacuumed if there are
> more than 10 million dead tuples (every 28 minutes).
Yeah, so that is about 50x what we do now (twice an hour vs. once a
day). While that's a lot more reasonable than the behavior that we'd
get from a 500k hard cap (every 84 seconds), I suspect it's still too
aggressive.
I find these things much easier to reason about in gigabytes than in
time units. In that example, the table was 320GB and was getting
vacuumed after accumulating 64GB of bloat. That seems like a lot. It
means that the table can grow from 320GB all the way up until 384GB
before we even think about starting to vacuum it, and then we might
not start right away, depending on resource availability, and we may
take some time to finish, possibly considerable time, depending on the
number and size of indexes and the availability of I/O resources. So
actually the table might very plausibly be well above 400GB before we
get done processing it, or potentially even more. I think that's not
aggressive enough.
But how much would we like to push that 64GB of bloat number down for
a table of this size? I would argue that if we're vacuuming the table
when it's only got 1GB of bloat, or 2GB of bloat, that seems
excessive. Unless the system is very lightly loaded and has no
long-running transactions at all, we're unlikely to be able to vacuum
aggressively enough to keep a 320GB table at a size of 321GB or 322GB.
Without testing or doing any research, I'm going to guess that a
realistic number is probably in the range of 10-20GB of bloat. If the
table activity is very light, we might be able to get it even lower,
like say 5GB, but the costs ramp up very quickly as you push the
vacuuming threshold down. Also, if the table accumulates X amount of
bloat during the time it takes to run one vacuum, you can never
succeed in limiting bloat to a value less than X (and probably more
like 1.5*X or 2*X or something).
So without actually trying anything, which I do think somebody should
do and report results, my guess is that for a 320GB table, you'd like
to multiply the vacuum frequency by a value somewhere between 3 and
10, and probably much closer to 3 than to 10. Maybe even less than 3.
Not sure exactly. Like I say, I think someone needs to try some
different workloads and database sizes and numbers of indexes, and try
to get a feeling for what actually works well in practice.
> If we want to stick with the simple formula, we should probably choose a
> very high default, maybe 100 million, as you suggested earlier.
>
> However, it would be nice to have the visibility map updated more
> frequently than every 100 million dead tuples. I wonder if this could be
> decoupled from the vacuum process?
Yes, but if a page has had any non-HOT updates, it can't become
all-visible again without vacuum. If it has had only HOT updates, then
a HOT-prune could make it all-visible. I don't think we do that
currently, but I think in theory we could.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-06-18 03:06 Nathan Bossart <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2024-06-18 03:06 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Frédéric Yhuel <[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]>
I didn't see a commitfest entry for this, so I created one to make sure we
don't lose track of this:
https://commitfest.postgresql.org/48/5046/
--
nathan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-06-18 10:36 Frédéric Yhuel <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Frédéric Yhuel @ 2024-06-18 10:36 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Robert Haas <[email protected]>; +Cc: 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]>
Le 18/06/2024 à 05:06, Nathan Bossart a écrit :
> I didn't see a commitfest entry for this, so I created one to make sure we
> don't lose track of this:
>
> https://commitfest.postgresql.org/48/5046/
>
OK thanks!
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).
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2024-08-07 21:39 Nathan Bossart <[email protected]>
parent: Frédéric Yhuel <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Nathan Bossart @ 2024-08-07 21:39 UTC (permalink / raw)
To: Frédéric Yhuel <[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]>
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.
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
--
nathan
From ccfaee370835d755b663357bf139fea729a5f454 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 7 Aug 2024 16:22:37 -0500
Subject: [PATCH v2 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.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 a1a1d58a43..10855b0e19 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8615,6 +8615,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 93b3f664f2..19b5ea7421 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1596,6 +1596,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 49fd35bfc5..c18ee4c497 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 4e4a0ccbef..d43d48abc8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -119,6 +119,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;
@@ -2887,6 +2888,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
@@ -2925,6 +2928,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,
@@ -2966,6 +2970,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;
@@ -3039,6 +3047,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 c0a52cdcc3..9aae183786 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3403,6 +3403,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 9ec9f97e92..e7cec694f3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -663,6 +663,8 @@
#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.c b/src/bin/psql/tab-complete.c
index 024469474d..f3ce76e23d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1295,6 +1295,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",
@@ -1311,6 +1312,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 cae1e8b329..efbab7bfa9 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -32,6 +32,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 8700204953..bcc6e2607b 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.3 (Apple Git-146)
Attachments:
[text/plain] v2-0001-autovacuum_max_threshold.patch (9.8K, ../../ZrPprRLBzi1YUBLn@nathan/2-v2-0001-autovacuum_max_threshold.patch)
download | inline diff:
From ccfaee370835d755b663357bf139fea729a5f454 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 7 Aug 2024 16:22:37 -0500
Subject: [PATCH v2 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.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 a1a1d58a43..10855b0e19 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8615,6 +8615,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 93b3f664f2..19b5ea7421 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1596,6 +1596,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 49fd35bfc5..c18ee4c497 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 4e4a0ccbef..d43d48abc8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -119,6 +119,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;
@@ -2887,6 +2888,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
@@ -2925,6 +2928,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,
@@ -2966,6 +2970,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;
@@ -3039,6 +3047,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 c0a52cdcc3..9aae183786 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3403,6 +3403,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 9ec9f97e92..e7cec694f3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -663,6 +663,8 @@
#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.c b/src/bin/psql/tab-complete.c
index 024469474d..f3ce76e23d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1295,6 +1295,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",
@@ -1311,6 +1312,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 cae1e8b329..efbab7bfa9 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -32,6 +32,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 8700204953..bcc6e2607b 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.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-08-07 21:39 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-04 05:51 [PATCH v9 6/7] Row pattern recognition patch (tests). Tatsuo Ishii <[email protected]>
2024-04-26 13:40 Re: New GUC autovacuum_max_threshold ? Joe Conway <[email protected]>
2024-04-26 14:12 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
2024-05-01 18:19 ` Re: New GUC autovacuum_max_threshold ? Imseih (AWS), Sami <[email protected]>
2024-05-01 18:50 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
2024-05-02 06:44 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-05-02 16:01 ` Re: New GUC autovacuum_max_threshold ? Imseih (AWS), Sami <[email protected]>
2024-05-02 02:02 ` Re: New GUC autovacuum_max_threshold ? David Rowley <[email protected]>
2024-05-07 14:31 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
2024-05-07 21:17 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2024-05-08 17:30 ` Re: New GUC autovacuum_max_threshold ? Imseih (AWS), Sami <[email protected]>
2024-05-09 14:58 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
2024-05-13 15:14 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-05-13 20:22 ` Re: New GUC autovacuum_max_threshold ? Robert Haas <[email protected]>
2024-06-18 03:06 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2024-06-18 10:36 ` Re: New GUC autovacuum_max_threshold ? Frédéric Yhuel <[email protected]>
2024-08-07 21:39 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[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