-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathmigration.sql
223 lines (193 loc) · 13 KB
/
migration.sql
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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
-- for initial database setup start with init-database.sql
-- note "STORAGE" option in column definition requires PostgreSQL 16 or later
CREATE SCHEMA banking_stage_results_2;
CREATE TABLE banking_stage_results_2.transactions(
transaction_id bigserial,
signature varchar(88) NOT NULL,
PRIMARY KEY (transaction_id) INCLUDE(signature),
UNIQUE(signature) INCLUDE (transaction_id)
);
ALTER INDEX banking_stage_results_2.transactions_pkey SET (FILLFACTOR=80);
ALTER INDEX banking_stage_results_2.transactions_signature_transaction_id_key SET (FILLFACTOR=80);
ALTER TABLE banking_stage_results_2.transactions SET (toast_tuple_target=128);
CREATE TABLE banking_stage_results_2.transaction_infos (
transaction_id BIGINT PRIMARY KEY,
processed_slot BIGINT NOT NULL,
is_successful BOOL NOT NULL,
cu_requested BIGINT NOT NULL,
cu_consumed BIGINT NOT NULL,
prioritization_fees BIGINT NOT NULL,
supp_infos text
);
-- page layout: move supp_infos to toast; everything else should stay on main
ALTER TABLE banking_stage_results_2.transaction_infos SET (toast_tuple_target=128);
ALTER TABLE banking_stage_results_2.transaction_infos ALTER COLUMN supp_infos SET STORAGE extended;
CREATE TABLE banking_stage_results_2.errors (
error_code int primary key,
error_text text NOT NULL
);
-- page layout: keep everything on main
ALTER TABLE banking_stage_results_2.errors ALTER COLUMN error_text SET STORAGE main;
CREATE TABLE banking_stage_results_2.transaction_slot (
transaction_id BIGINT,
slot BIGINT NOT NULL,
error_code INT NOT NULL,
count INT NOT NULL,
utc_timestamp TIMESTAMP NOT NULL,
PRIMARY KEY (transaction_id, slot, error_code)
);
-- page layout: keep everything on main (note: TIMESTAMP uses storage plain)
ALTER TABLE banking_stage_results_2.transaction_slot SET (toast_tuple_target=128);
CREATE INDEX idx_transaction_slot_timestamp ON banking_stage_results_2.transaction_slot(utc_timestamp);
CREATE INDEX idx_transaction_slot_slot ON banking_stage_results_2.transaction_slot(slot);
CREATE TABLE banking_stage_results_2.blocks (
slot BIGINT PRIMARY KEY,
successful_transactions BIGINT NOT NULL,
processed_transactions BIGINT NOT NULL,
total_cu_used BIGINT NOT NULL,
total_cu_requested BIGINT NOT NULL,
block_hash varchar(44) NOT NULL,
leader_identity varchar(44) NOT NULL,
supp_infos text
);
-- page layout: blockhash is frequently used
ALTER TABLE banking_stage_results_2.blocks SET (toast_tuple_target=200);
ALTER TABLE banking_stage_results_2.blocks ALTER COLUMN block_hash SET STORAGE main;
CREATE INDEX idx_blocks_block_hash ON banking_stage_results_2.blocks(block_hash);
CREATE TABLE banking_stage_results_2.accounts(
acc_id bigserial,
account_key varchar(44) NOT NULL,
PRIMARY KEY (acc_id) INCLUDE(account_key),
UNIQUE(account_key) INCLUDE (acc_id)
);
ALTER TABLE banking_stage_results_2.accounts SET (toast_tuple_target=128);
ALTER INDEX banking_stage_results_2.accounts_pkey SET (FILLFACTOR=80);
ALTER INDEX banking_stage_results_2.accounts_account_key_acc_id_key SET (FILLFACTOR=80);
-- table+index size (605mn rows): 146GB total = 48GB main table + 49 GB index by acc_id + 49 GB index by account_key
CREATE TABLE banking_stage_results_2.accounts_map_transaction(
transaction_id BIGINT NOT NULL,
acc_id BIGINT NOT NULL,
is_writable BOOL NOT NULL,
is_signer BOOL NOT NULL,
is_atl BOOL NOT NULL,
PRIMARY KEY (transaction_id, acc_id)
);
-- page layout: very small rows, keep everything on main
ALTER TABLE banking_stage_results_2.accounts_map_transaction SET (toast_tuple_target=128);
CREATE TABLE banking_stage_results_2.accounts_map_blocks (
acc_id BIGINT NOT NULL,
slot BIGINT NOT NULL,
is_write_locked BOOL NOT NULL,
total_cu_consumed BIGINT NOT NULL,
total_cu_requested BIGINT NOT NULL,
prioritization_fees_info text NOT NULL,
supp_infos text,
PRIMARY KEY (acc_id, slot, is_write_locked)
);
-- page layout: move prioritization_fees_info and supp_infos to toast; everything else should stay on main
ALTER TABLE banking_stage_results_2.accounts_map_blocks SET (toast_tuple_target=128);
ALTER TABLE banking_stage_results_2.accounts_map_blocks ALTER COLUMN prioritization_fees_info SET STORAGE extended;
ALTER TABLE banking_stage_results_2.accounts_map_blocks ALTER COLUMN supp_infos SET STORAGE extended;
CREATE INDEX idx_accounts_map_blocks_slot ON banking_stage_results_2.accounts_map_blocks(slot);
CREATE UNLOGGED TABLE banking_stage_results_2.accounts_map_transaction_latest(
acc_id BIGINT PRIMARY KEY,
-- max 120 int8 ids (see LIMIT_LATEST_TXS_PER_ACCOUNT)
tx_ids BIGINT[] NOT NULL
);
-- page layout: tx_ids is the only relevant data must remain in main; tx_ids size is intentionally limited to fit a single page; compression is okey
-- do not limit tuple size, it is not relevant
ALTER TABLE banking_stage_results_2.accounts_map_transaction_latest SET (toast_tuple_target=4080);
ALTER TABLE banking_stage_results_2.accounts_map_transaction_latest ALTER COLUMN tx_ids SET STORAGE main;
INSERT INTO banking_stage_results_2.errors (error_text, error_code) VALUES
('AccountBorrowOutstanding', 0),
('AccountInUse', 1),
('AccountLoadedTwice', 2),
('AccountNotFound', 3),
('AddressLookupTableNotFound', 4),
('AlreadyProcessed', 5),
('BlockhashNotFound', 6),
('CallChainTooDeep', 7),
('ClusterMaintenance', 8),
('DuplicateInstruction', 9),
('InstructionError', 10),
('InsufficientFundsForFee', 11),
('InsufficientFundsForRent', 12),
('InvalidAccountForFee', 13),
('InvalidAccountIndex', 14),
('InvalidAddressLookupTableData', 15),
('InvalidAddressLookupTableIndex', 16),
('InvalidAddressLookupTableOwner', 17),
('InvalidLoadedAccountsDataSizeLimit', 18),
('InvalidProgramForExecution', 19),
('InvalidRentPayingAccount', 20),
('InvalidWritableAccount', 21),
('MaxLoadedAccountsDataSizeExceeded', 22),
('MissingSignatureForFee', 23),
('ProgramAccountNotFound', 24),
('ResanitizationNeeded', 25),
('SanitizeFailure', 26),
('SignatureFailure', 27),
('TooManyAccountLocks', 28),
('UnbalancedTransaction', 29),
('UnsupportedVersion', 30),
('WouldExceedAccountDataBlockLimit', 31),
('WouldExceedAccountDataTotalLimit', 32),
('WouldExceedMaxAccountCostLimit', 33),
('WouldExceedMaxBlockCostLimit', 34),
('WouldExceedMaxVoteCostLimit', 35);
CREATE OR REPLACE FUNCTION array_dedup_append(base bigint[], append bigint[], n_limit int)
RETURNS bigint[]
AS $$
DECLARE
tmplist bigint[];
el bigint;
len int;
BEGIN
tmplist := base;
FOREACH el IN ARRAY append LOOP
tmplist := array_remove(tmplist, el);
tmplist := tmplist || el;
END LOOP;
len := CARDINALITY(tmplist);
RETURN tmplist[(len + 1 - n_limit):];
END
$$ LANGUAGE plpgsql IMMUTABLE CALLED ON NULL INPUT;
ALTER TABLE banking_stage_results_2.accounts
SET (
autovacuum_vacuum_scale_factor=0,
autovacuum_vacuum_threshold=100,
autovacuum_vacuum_insert_scale_factor=0,
autovacuum_vacuum_insert_threshold=100,
autovacuum_analyze_scale_factor=0,
autovacuum_analyze_threshold=100
);
ALTER TABLE banking_stage_results_2.transactions
SET (
autovacuum_vacuum_scale_factor=0,
autovacuum_vacuum_threshold=1000,
autovacuum_vacuum_insert_scale_factor=0,
autovacuum_vacuum_insert_threshold=1000,
autovacuum_analyze_scale_factor=0,
autovacuum_analyze_threshold=1000
);
ALTER TABLE banking_stage_results_2.accounts_map_transaction
SET (
autovacuum_vacuum_scale_factor=0,
autovacuum_vacuum_threshold=10000,
autovacuum_vacuum_insert_scale_factor=0,
autovacuum_vacuum_insert_threshold=10000,
autovacuum_analyze_scale_factor=0,
autovacuum_analyze_threshold=10000
);
-- not written ATM
ALTER TABLE banking_stage_results_2.accounts_map_transaction_latest
SET (
autovacuum_vacuum_scale_factor=0,
autovacuum_vacuum_threshold=100,
autovacuum_vacuum_insert_scale_factor=0,
autovacuum_vacuum_insert_threshold=100,
autovacuum_analyze_scale_factor=0,
autovacuum_analyze_threshold=100
);
ALTER TABLE banking_stage_results_2.accounts_map_transaction_latest SET (FILLFACTOR=90);
ALTER INDEX banking_stage_results_2.accounts_map_transaction_latest_pkey SET (FILLFACTOR=50);