Closed sdudoladov closed 8 months ago
:+1:
:-1: aka don't merge for now.
it does not work for sequences where start_value deviates from min_value and cte is not needed, I know
converting to draft then
In the formula
(abs(last_value::numeric - start_value) + 1) /
(CASE WHEN increment_by > 0 THEN (max_value::numeric - start_value) ELSE (start_value::numeric - min_value) END + 1) * 100
(abs(last_value::numeric - start_value) + 1)
is the number of values used so far.
CASE WHEN increment_by > 0 THEN (max_value::numeric - start_value) ELSE (start_value::numeric - min_value) END + 1
is the total sequence size
So any change in the formula/query must at least account for the following cases:
-- For testing we assume a seq is fine if fewer than 80% of its values are used up
-- seq with enough values left
CREATE SEQUENCE not_exhausted_seq MINVALUE 1 MAXVALUE 10;
SELECT setval('not_exhausted_seq', 7);
-- seq that will soon become exhausted
CREATE SEQUENCE nearly_exhausted_seq MINVALUE 1 MAXVALUE 10;
SELECT setval('nearly_exhausted_seq', 8);
-- seq with no values left
CREATE SEQUENCE exhausted_seq MINVALUE 1 MAXVALUE 10;
SELECT setval('exhausted_seq', 10);
-- seq with an increment other than one
CREATE SEQUENCE nearly_exhausted_seq_with_increment_2 MINVALUE 1 MAXVALUE 10 INCREMENT 2;
SELECT setval('nearly_exhausted_seq_with_increment_2', 8);
-- descending seq in the negative range
-- that one is especially important because for now we fix seq exhaustion by pushing the seq into the negative range
CREATE SEQUENCE nearly_exhausted_seq_in_the_negative_range MINVALUE -10 MAXVALUE -1 INCREMENT -1;
SELECT setval('nearly_exhausted_seq_in_the_negative_range', -9);
-- seq that spans both positive and negative range
CREATE SEQUENCE nearly_exhausted_seq_in_positive_negative_range MINVALUE -5 MAXVALUE 4 INCREMENT -1;
SELECT setval('nearly_exhausted_seq_in_positive_negative_range', -4);
-- seq with the start_value different from the min_value
CREATE SEQUENCE nearly_exhausted_seq_with_nondefault_start_value MINVALUE 1 MAXVALUE 10 START WITH 3;
SELECT setval('nearly_exhausted_seq_with_nondefault_start_value', 9);
-- this seq is not a problem because there is an explicit cycle
CREATE SEQUENCE nearly_exhausted_seq_with_cycle MINVALUE 1 MAXVALUE 10 CYCLE;
SELECT setval('nearly_exhausted_seq_with_cycle', 8);
-- last_value can be NULL if you lack privileges or haven't used the seq yet
CREATE SEQUENCE seq_with_null_last_value MINVALUE 1 MAXVALUE 10;
-- seq with an increment big enough in relation to the seq size
-- it is almost exhausted as there is only one step left before it overflows
-- whether it triggers an alert or not depends on the threshold value: for 80% it will not trigger
CREATE SEQUENCE nearly_exhausted_seq_big_increment MINVALUE 1 MAXVALUE 10 INCREMENT 5;
-- returns 1
SELECT nextval('nearly_exhausted_seq_big_increment');
-- returns 6: at this point the sequence is close to exhaustion
SELECT nextval('nearly_exhausted_seq_big_increment');
if you re-create all of these, the expected output for sequences that have exhausted 80% of their values is
=# select get_nearly_exhausted_sequences(80);
get_nearly_exhausted_sequences
-----------------------------------------------------------------
(public,nearly_exhausted_seq,80.00)
(public,exhausted_seq,100.00)
(public,nearly_exhausted_seq_with_increment_2,80.00)
(public,nearly_exhausted_seq_in_the_negative_range,90.00)
(public,nearly_exhausted_seq_in_positive_negative_range,90.00)
(public,nearly_exhausted_seq_with_nondefault_start_value,87.50)
(public,nearly_exhausted_seq_big_increment,100.00)
(7 rows)
The only more edge case I could think about is increment steps bigger than 1/-1. But probably nobody use jumps of 10 or 100 batches. It could be good for a ZMON check to check the trend on last_value
so that we don't alert when there are no changes like in the zalos case.
EDIT:
add this column shows how many increments would be left:
abs(floor(CASE WHEN increment_by > 0 THEN (max_value::numeric - last_value) / increment_by ELSE (start_value::numeric - min_value) / increment_by END)) increments_left
Or another try to incorporate increment:
SELECT schemaname, sequencename,
round(abs(
ceil((abs(last_value::numeric - start_value) + 1) / increment_by) /
floor((CASE WHEN increment_by > 0
THEN (max_value::numeric - start_value)
ELSE (start_value::numeric - min_value)
END + 1) / increment_by) * 100
), 2) AS seq_precent_used
FROM pg_sequences
WHERE NOT CYCLE
AND last_value IS NOT NULL;
schemaname | sequencename | seq_precent_used
------------+--------------------------------------------------+------------------
public | not_exhausted_seq | 70.00
public | nearly_exhausted_seq | 80.00
public | exhausted_seq | 100.00
public | nearly_exhausted_seq_with_increment_2 | 80.00
public | nearly_exhausted_seq_in_the_negative_range | 90.00
public | nearly_exhausted_seq_in_positive_negative_range | 90.00
public | nearly_exhausted_seq_with_nondefault_start_value | 87.50
public | nearly_exhausted_seq_big_increment | 100.00
:+1:
👍
We want to monitor sequences that are close to their max values.
Unfortunately
pg_sequences
reports sequences only for the database one is connected to. For that reason we need to install the relevant function into each and every database. _zmon_schema.dump does not fit that criterion butmetric_helpers
does.