spring-yoomoney-db-queue/sql/create_tables.sql at main · Mark1708/spring-yoomoney-db-queue · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
CREATE TABLE queue_tasks_1
(
    id                BIGSERIAL PRIMARY KEY,
    queue_name        TEXT NOT NULL,
    payload           TEXT,
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT now(),
    next_process_at   TIMESTAMP WITH TIME ZONE DEFAULT now(),
    attempt           INTEGER                  DEFAULT 0,
    reenqueue_attempt INTEGER                  DEFAULT 0,
    total_attempt     INTEGER                  DEFAULT 0
);

CREATE TABLE queue_tasks_2
(
    id                BIGSERIAL PRIMARY KEY,
    queue_name        TEXT NOT NULL,
    payload           TEXT,
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT now(),
    next_process_at   TIMESTAMP WITH TIME ZONE DEFAULT now(),
    attempt           INTEGER                  DEFAULT 0,
    reenqueue_attempt INTEGER                  DEFAULT 0,
    total_attempt     INTEGER                  DEFAULT 0
) WITH (
      autovacuum_vacuum_cost_delay = 5,
      autovacuum_vacuum_cost_limit = 500,
      autovacuum_vacuum_scale_factor = 0.0001
      );
CREATE INDEX queue_tasks_name_time_desc_idx
    ON queue_tasks_2 USING btree (queue_name, next_process_at, id DESC);

CREATE TABLE queue_tasks_3
(
    id                bigserial   NOT NULL,
    queue_name        text        NOT NULL,
    payload           text        NULL,
    created_at        timestamptz NULL DEFAULT now(),
    next_process_at   timestamptz NULL DEFAULT now(),
    attempt           int4        NULL DEFAULT 0,
    reenqueue_attempt int4        NULL DEFAULT 0,
    total_attempt     int4        NULL DEFAULT 0,
    request_id        text        NULL,
    CONSTRAINT queue_tasks_o_pkey PRIMARY KEY (id)
)
    WITH (
        fillfactor = 30,
        autovacuum_vacuum_cost_delay = 5,
        autovacuum_vacuum_cost_limit = 500,
        autovacuum_vacuum_scale_factor = 0.0001
        );
CREATE UNIQUE INDEX queue_tasks_o_name_time_desc_idx ON queue_tasks_3 USING btree (queue_name, next_process_at, id DESC) WITH (fillfactor ='30');

CREATE TABLE queue_tasks_4
(
    id                bigserial PRIMARY KEY,
    queue_name        text        NOT NULL,
    payload           text        NULL,
    created_at        timestamptz NULL DEFAULT now(),
    next_process_at   timestamptz NULL DEFAULT now(),
    attempt           int4        NULL DEFAULT 0,
    reenqueue_attempt int4        NULL DEFAULT 0,
    total_attempt     int4        NULL DEFAULT 0,
    request_id        text        NULL
)
    PARTITION BY hash (id);

CREATE UNIQUE INDEX queue_tasks_4_name_time_desc_idx ON queue_tasks_4 USING btree (queue_name, next_process_at, id DESC) WITH (fillfactor ='30');

CREATE TABLE queue_tasks_4_0 PARTITION OF queue_tasks_4 FOR VALUES WITH (modulus 6, remainder 0);
CREATE TABLE queue_tasks_4_1 PARTITION OF queue_tasks_4 FOR VALUES WITH (modulus 6, remainder 1);
CREATE TABLE queue_tasks_4_2 PARTITION OF queue_tasks_4 FOR VALUES WITH (modulus 6, remainder 2);
CREATE TABLE queue_tasks_4_3 PARTITION OF queue_tasks_4 FOR VALUES WITH (modulus 6, remainder 3);
CREATE TABLE queue_tasks_4_4 PARTITION OF queue_tasks_4 FOR VALUES WITH (modulus 6, remainder 4);
CREATE TABLE queue_tasks_4_5 PARTITION OF queue_tasks_4 FOR VALUES WITH (modulus 6, remainder 5);

ALTER TABLE queue_tasks_4_0
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_4_1
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_4_2
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_4_3
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_4_4
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_4_5
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);

CREATE TABLE queue_tasks_5
(
    id                bigserial PRIMARY KEY,
    queue_name        text        NOT NULL,
    payload           text        NULL,
    created_at        timestamptz NULL DEFAULT now(),
    next_process_at   timestamptz NULL DEFAULT now(),
    attempt           int4        NULL DEFAULT 0,
    reenqueue_attempt int4        NULL DEFAULT 0,
    total_attempt     int4        NULL DEFAULT 0,
    request_id        text        NULL
)
    PARTITION BY hash (id);

CREATE UNIQUE INDEX queue_tasks_5_name_time_desc_idx ON queue_tasks_5 USING btree (queue_name, next_process_at, id DESC) WITH (fillfactor ='30');

CREATE TABLE queue_tasks_5_0 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 0);
CREATE TABLE queue_tasks_5_1 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 1);
CREATE TABLE queue_tasks_5_2 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 2);
CREATE TABLE queue_tasks_5_3 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 3);
CREATE TABLE queue_tasks_5_4 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 4);
CREATE TABLE queue_tasks_5_5 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 5);
CREATE TABLE queue_tasks_5_6 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 6);
CREATE TABLE queue_tasks_5_7 PARTITION OF queue_tasks_5 FOR VALUES WITH (modulus 8, remainder 7);

ALTER TABLE queue_tasks_5_0
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_1
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_2
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_3
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_4
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_5
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_6
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_5_7
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);

CREATE TABLE queue_tasks_6
(
    id                bigserial PRIMARY KEY,
    queue_name        text        NOT NULL,
    payload           text        NULL,
    created_at        timestamptz NULL DEFAULT now(),
    next_process_at   timestamptz NULL DEFAULT now(),
    attempt           int4        NULL DEFAULT 0,
    reenqueue_attempt int4        NULL DEFAULT 0,
    total_attempt     int4        NULL DEFAULT 0,
    request_id        text        NULL
)
    PARTITION BY hash (id);

CREATE UNIQUE INDEX queue_tasks_6_name_time_desc_idx ON queue_tasks_6 USING btree (queue_name, next_process_at, id DESC) WITH (fillfactor ='30');

CREATE TABLE queue_tasks_6_0 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 0);
CREATE TABLE queue_tasks_6_1 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 1);
CREATE TABLE queue_tasks_6_2 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 2);
CREATE TABLE queue_tasks_6_3 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 3);
CREATE TABLE queue_tasks_6_4 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 4);
CREATE TABLE queue_tasks_6_5 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 5);
CREATE TABLE queue_tasks_6_6 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 6);
CREATE TABLE queue_tasks_6_7 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 7);
CREATE TABLE queue_tasks_6_8 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 8);
CREATE TABLE queue_tasks_6_9 PARTITION OF queue_tasks_6 FOR VALUES WITH (modulus 10, remainder 9);

ALTER TABLE queue_tasks_6_0
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_1
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_2
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_3
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_4
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_5
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_6
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_7
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_8
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);
ALTER TABLE queue_tasks_6_9
    SET (fillfactor = 30, autovacuum_vacuum_cost_delay = 5, autovacuum_vacuum_cost_limit = 500, autovacuum_vacuum_scale_factor = 0.0001);