Administrace komentářů

Tu je definicia tabulky, particie som zrusil a vratil sa k monolitickej tabulke, pretoze inserty boli este pomalsie.

CREATE UNLOGGED TABLE public.csvaddresses(
address character varying(34) COLLATE pg_catalog."default" NOT NULL,
private_key character varying(51) COLLATE pg_catalog."default",
CONSTRAINT csvaddresses_address_pk PRIMARY KEY (address) USING INDEX TABLESPACE crypto)
WITH (OIDS = FALSE) TABLESPACE crypto;

stlpec address moze mat dlzku 33 alebo 34 znakov

btree som zvolil lebo som si nebol isty ci hash index mozem pouzit v spojitosti s primarnym klucom, kedze chcem aby key/value boli unikatne.

tu su vzorove data - su to skutocne data, nie su obfuskovane, ani nijak upravene:

'176wsMBztSjpgSUJn3XY36gRT5gMRjPTu8','5KQ97zSKN3F368Yx91MmeXUezBiCziQS4gM6EaBr6Kb68u2Hxkt'
'1MyLinoP3PxWFuZVMrV4gFZixqqqK1qppJ','5JFSptsznKycpRxMhpDjysELbNvWsUNS3DZjJnqBA7pV7S5wbxU'
'1FrrVykiPsQTUNncJtfjKDBHRaKp2Tv1Bp','5KRBPVtAEctVcffBfJKNFAv4Ak8SYxfKHqA2mCuSuVExghzsgiU'
'14GeuhxLMdnQwLHKCtmiPiYiFxRUhANV5Z','5J5QbjmQZLRy5zkaWutn68z7CBsdvf7Gt46qPCk2PzeG9SEuAyz'
'1F8Z4N17FEgaokEpNUNUZuPmMAec8wSATj','5Kb6WQ5AzV7Rsv1XGBuTuh2w4vBkjP7d3vyGvQmVtyyq8JsUhGe'
'1BARTFB38UDZBymaKSDMftvCqMTV764nVE','5KXfrNqWm1NYGej2jDtVF73d3VWpSBuExbsrQwCS6QoGTnayzz7'
'1DMcSTmu7v6rhgVKQCuCaDTdH2vyvX5Pv1','5Jv5DLGBA3ip9XEabDxS8n6SgaZqEXQjoFvaDfDgVRjCto1RwqS'
'16yt1YqLuPqEn2vrAgwPqP5ZZhLhMdD3bC','5J8pkqkaX49cRYMM1o9Wb8fieSJgJWdbTw2jxvKYHeCGPY6L5K7'
'16om5EuYn2zbKqCznbK7g1ZZVSfEWu9ACg','5KY1bCQC1XEFW8Jus9fLnj7pvyGRm4speKDxwEEssCwjVwurVR2'
'1EYmvuVioUEV4AzaqAQRaikMnUYrCnr9yS','5JxojRJ29VXr38DFkKFLK96PdMneVjmhfm69gnMfd5w4BjZaEXt'
'18yfQtKAavtkEvrxSHZyYFgUnsZMTeufBq','5KaufvZ8uKnzancuChdLzVbGFcswJc66r7aLBxJbaNj7HDh81AE'
'15QoMdtZ73cgAkNMXCb4sk9qMrCXmaMJU3','5KKttwPpDguBo6gy6iPKDLiiwVtnTYiQv2NarxuFk5WCupVAoRi'
'1N664oBTfjCBbzPyN6r4iwndCVCvo8EkGx','5K4GjKeobmxdebY4CjcAnBHSrsox88TyGvKd5YW6Vp83avDNMUj'
'138xDo1VqcMJGJGpA5MRGBJSR8y8L66y2o','5KQKQduF8gNt9pbxxvX7bLq9AGGB9LoVGsAgJP8smtuPtEe9RU8'
'1EbpKC9uDRwaj9YEopBPSed9v8neS2thTo','5JqPNMM5XnpnFYRKis9GkpqFc9nRDSeB1HxFmAL3Kcy7wpjRUE9'

na strankach https://wiki.postgresql.org/wiki/Index_Maintenance som nasiel SQL prikaz na zobrazenie statistik "Index size/usage statistics". Screenshot prikladam do prilohy. Su tam vidiet aj docasne tabulky "*_tmp_*" do ktorych som importoval data z CSV suboru pomocou COPY, aby som sa vyhol importu dat do velkej "pomalej" tabulky.

data vkladam pomocou utility napisanej v C s vyzuitim kniznice libpq. nevyuzivam ziadne transacie iba cisto jeden prikaz - INSERT v cykle

paramValues[0] = (char *)crypto_address;
paramValues[1] = (char *)private_key;

PGresult *res = PQexecParams(
conn,										         /* connection string */
"INSERT INTO csvaddresses(address, private_key) VALUES($1, $2) ON CONFLICT DO NOTHING;", /* sql query */
2,										         /* number of parameters */
NULL,											 /* oid param type - default data type */
(const char * const *)paramValues,							 /* variable value */
NULL,											 /* length */
NULL,											 /* format */
0											 /* result format - text mode */
);

ako je vidiet na prilozenom screenshote indexy sa v ziadnom pripade nevojdu do pamate, index ma 233GB co je cca 50% velkosti tabulky.

tak isto som sa pohraval aj s myslienkov na nasadenie BDB/DB4, alebo RocksDB, ale nebol som si isty ci su vhodne na tento use case.