I-imeyile ifumaneka ngexesha le-1:47 ngosuku. "I-analysis panel yethu ithatha imizuzu ye-4 ukuyila. Abasebenzisi bayibophelela. Ngaba sinokufumana oku?" Ndingathanda i-akhawunti yethu ye-AWS. Sithengisa i-$8,000 ngenyanga kwi-database ebonakalayo ngokubanzi i-maths kwi-tablets. I-maths engaphezulu! I-boss yam yaba imibuzo enomatshini omncinci: "Ukuba usebenzisa i-database eyahlukileyo?" Yintoni. Easy. Yintoni? Kwiintsuku ezidlulileyo, ndibandakanya iindidi: 
 
 
 
 
 
 
 
 
 I-ClickHouse (ngaba wonke umntu ushiye ngokukhawuleza) I-DuckDB (i-the new hotness) I-PostgreSQL (eyenza ukuba usebenzisa ngoku) I-Snowflake (eyenza iingcali bethu) I-TimescaleDB (okanye umntu kwi-Reddit wabelane) MongoDB (ngoko ke ngoko ke) I-Elasticsearch (siza kufumaneka ngoku) MySQL (kunye ukubona ukuba kungabonakali) Emva koko, ndingayifumana into enomntu omncinci: Ndingathanda ukuyifaka zonke iinkcukacha kunye neenkcukacha ezininzi kwaye ndingathanda into. Yeyona le ncwadi. I-The Setup: I-Same Question, Eight Different Answers Yenziwe ngexesha elandelayo: Qinisekisa ukuba ukhangela zonke iimoto ebandayo ezidlulileyo ekhaya yakho ngoMsombuluko. Ngaba unako: 
 
 
 
 Wabelane kwi-notebook njengoko zithunyelwa (njenge-database ye-traditional) Yenza iifoto zeenkcukacha kunye neengxaki ezidlulileyo (njenge-database yeenkcukacha) Just count the red ones and ignore everything else (njenge database kolumn-store) Zonke iindlela zisebenza. Kodwa le nto ilula kakhulu? Yintoni ilula kakhulu? Yintoni ilula xa umnqweno wakho uqala ukuhambisa iiyunithi ze-50 kwi-street yakho? Ndingathanda ukuba uyazi. Ndiyathanda iindidi ze-6 ze-e-commerce ezininzi ezivela kwi-storage yethu: The Dataset: 
 
 
 
 
 847 million iziganeko zokupakisha ngamnye 120 million abathengi eyodwa 450,000 iimveliso Okungenani i-180 GB yeedatha ebomvu Iingxaki ezincinane ezisetyenziselwa yonke i-analytics dashboard: The Test: 
 
 
 
 
 
 Yintoni iingxowa ziye zithunyelwe ngosuku? Yintoni iimveliso zethu ezidlulileyo eziyi-10 ngonyaka? Yintoni abathengi wabanika kakhulu? Yintoni ubuncinane ubuncinane ubuncinane bethu ngamanye amazwe? Ukubonisa i-customer ye-cash history ephelele Iingxaki ezininzi ezibonakalayo efanelekileyo xa ungenza i-847 million iilwimi ukufumana. EyoMsombuluko Morning: Loading iinkcukacha I-surprise yokuqala: Akukho zonke abasebenzisa idatha ngokulinganayo. Ixesha elide iiyure ze-4 ukuchitha yonke into. Njengoko ukhangela i-paint, kodwa i-paint iinkcukacha. PostgreSQL ekugqibeleni kwi-47 imizuzu. I-compressed idatha kakhulu ukuba i-180 GB iye yi-23 GB. I-checked iiyure ezisixhenxe ukuqinisekisa ukuba akukho ingxaki. ClickHouse akukho ndingathanda ukuba "ukubhalise" idatha. Oku kuphela ... ukuyifaka ngqo kwiifayile. "Ukuba ukuyifaka?" uye ndingathanda. Fair point. DuckDB 11 iiyure. 11 iiyure. Ndiqala ukubheja ngaphambi kokufutshane, kwaye ke kwakhona ukutyelela ekuteni. MongoDB Yaye ndandisa iifayile, kwaye wathi, "Ndiya, ndiya kufumana kwakhona." Emva koko yenza ezinye ukucubungula kwihlabathi ukuba awukwazi ukubona. Yonke into ndingathanda njengoko ukunceda imoto yakho kwi-mechanic who won't let you watch. Snowflake Iindleko ngoku: $ 47 ngexesha yekhompyutha kuphela ukuqhuba idatha. UTshazimpuzi: I-First Query "Ukuba ngalinye iingxowa zithunyelwe ngosuku?" Yinto ingxaki elula kakhulu. Faka zonke iimveliso; zihlanganisa emva kwexesha. Yenza. Results: 
 
 
 
 
 
 
 
 
 I-ClickHouse: I-0.3 iiveki DuckDB: 0.4 imizuzu PostgreSQL: 43 imizuzu TimescaleDB: iiyure ezisixhenxe MongoDB: 2 imizuzu, 17 imizuzu (kwixesha lokugqibela) Snowflake: 1.8 imizuzu Elasticsearch: 8 imizuzu MySQL: Ndicinga emva kweenyanga ze-5 Nceda siphinde ezininzi kwi-perspective. I-ClickHouse uxhaswa ngexesha elidlulayo ukuchitha. I-PostgreSQL - i-database ethandwa ngexesha elidlulayo ukuchitha i-microwave ye-burrito. I-MongoDB yaba kufuneka ufikelele ngaphezu kwe-coffee break yam, ke ndidala ngokupheleleyo. Kodwa apha apho kuza kubaluleke. Iindleko ezihambelana Speed is not everything. Sometimes the fastest car has the worst gas mileage. Ndicwangcisa zonke iingxaki i-100 amaxesha kunye nokuthintela iindleko efanelekileyo: PostgreSQL: 
 
 
 
 ixesha: 43 imizuzu Xabiso ngalinye: $0.02 Xabiso ngenyanga (100 imibuzo / ngosuku): $60 ClickHouse: 
 
 
 
 ixesha Query: 0.3 imizuzu Xabiso ngalinye: $0.03 Xabiso ngenyanga (100 imibuzo / ngosuku): $90 Wait. I-ClickHouse yaba ngexabiso, nangona i-140 amaxabiso ezininzi? Ngaphezulu Ngenxa yokusetyenziswa kweCPU ezininzi. Yinto efana nokuguqula i-Prius kwi-Ferrari - i-Ferrari ikhawulezileyo, kodwa iveza i-gas. DuckDB: 
 
 
 
 Ixesha le-Query: 0.4 iiveki Xabiso ngalinye: $0.00 Iindleko yenyanga: $0 (ukusebenza kwi-server ye-application) Yintoni iye yabadlala yonke into. EyoMsombuluko: Iingxaki ezininzi Iingxaki ezilandelayo zilungele kakhulu. "Show me a specific customer's purchase history" kufuneka ukubuyekeza zonke iiyure ze-847 million ukufumana mhlawumbi iiyure ze-50. Yinto apho iingxaki zangena . Ukucinga Test: Find customer #8472's purchases 
 
 
 
 
 I-ClickHouse: 0.9 iiyunithi (ngokugcisa yonke into, baye zibonise iiyunithi ze-47) PostgreSQL: 0.2 iiveki (ngaba iindeksi, uqhagamshelane ngqo kubo) I-DuckDB: i-1.2 iiveki (ngokugcisa yonke into njenge-ClickHouse) MongoDB: 0.1 iiveki (eyenziwe ngexesha elifanelekileyo) I-PostgreSQL – i-database ye-”slow” – wayengumthengisi. Ngenxa yokuba ngexesha elungileyo ayikho elungileyo yonke into. Yinto elungileyo ukuba kufuneka. EyoMsombuluko: Xa Ndiyathuba Ndiyathuba Ndingathanda ukuba ndingathanda ukuba ungenza iinkqubo ezininzi kakhulu. Ngoko ndingayenza into emnqweno: Ndingathanda yonke imibuzo emininzi ngexesha elifanayo, 50 imibuzo ngamnye. Ukunciphisa ngokucacileyo. Iiyure ze-43 ziye kwenziwa kwimizuzu ze-3. I-predictable. PostgreSQL: Kwakhona iiyure ze-0.3 ngenyanga ngalinye. I-CPU yandiswa nje njengoko i-free. ClickHouse: I-Crashed. I-Just Completely Died. Kuboniswa, xa uqhagamshelane kwi-application yakho, uqhagamshelane iimveliso kunye nabanye yonke into. Xa i-overloaded, i-overloaded . DuckDB: Yintoni Yenziwa $ 12 ngenxa 10 imizuzu yokusebenza. Emva koko uqhagamshelane i-imeyile emangalisayo malunga ne "impendulo yokusebenza kwe-cost optimization." Snowflake: Ndingathanda ukuba ndingathanda. Iingxaki zikhuselwa, kodwa iziphumo zibonakalisa... ingxaki? Ngaba iingxaki zikhusela iinkcukacha? Ngaba awukwazi ukufumana. MongoDB: I-Friday: I-part ye-Nobody Talks About Ingaba uyazi into engaba awunayo kwiiposi zebhulogi "Ukuhambisa Database"? Ukusebenza Iqela lethu le-DevOps uyazi. Ixabiso lokufunda: $0. PostgreSQL: Ndingathanda ukufundisa iilwimi olutsha yeenkcukacha. I-documentation yenzelwe kwi-Russian ngu-omnye owaqhelekanga kum. Ixabiso ye-training: ndingathanda i-$10k ukuba sinikezele i-expert. ClickHouse: Ukufundisa i-docs kwiiyure ezimbini. Yenza i-prototype kwiintsuku eyodwa. Iindleko ze-training: ngokuqinisekileyo akukho. DuckDB: I-Called Sales iiyure ezimbini ukufumana i-akhawunti yethu. Kwakhona akuyona ngexesha le-100% ukuba i-pricing yayo isebenza. Snowflake: Yintoni ndingathanda ngokwenene Emva kwezinsuku ezimbini ze-testing, apha into efunyenwe: 1. Akukho “Best” Database Yinto efana nokufunda, "Yintoni imoto engcono?" Yintoni: 
 
 
 
 
 Ukusuka umntu omnye kwi-city? (Bike) Ukuhambisa i-Family? (Ukuhambisa imoto) Ukuhamba 40 abantu? (i-bus) Ukusuka 500 abantu? (i-Airplane) Iimpawu ezahlukeneyo ziquka izixhobo ezahlukeneyo. I-Column-Stores Is Genuinly Magic (Uma izinto ezithile) Izixhobo ze-ClickHouse kunye ne-DuckDB Ukusuka kwi-by . Ukucinga Ukucinga Ukubonisa ifayibha kunye neeminyaka emininzi. Ukuba ufuna ukongeza isithombe eyodwa, akufuneka ukhangele iisithombe ezininzi. Iinkonzo ze-column zibonise le nto. Iinkonzo ze-Road akufuneka. Kuba iingxaki "ukongeza zonke iintengiso" oku 100-1000x ngokukhawuleza. Ukubonisa yonke into malunga ne-customer #8472" iingxaki, kuba ngempumelelo. 3. Speed ixabiso imali (ngezantsi) Iinkcukacha ze-databases ezincinane zisebenzisa iinkcukacha ezininzi. I-ClickHouse isebenza iingxaki zethu ezincinane ngokukhawuleza kunokwenzeka, kodwa isebenzisa i-16 iingcali ze-CPU ukuyisebenzisa. I-DuckDB yaba ngexesha elifanayo, kodwa ilungele kwi-application yethu. Akukho iinkonzo ezintsha. Akukho iindleko ezintsha. I-Cost Real Is Not the Database (I-Cost Real Is Not the Database) Yintoni ixesha i-team yakho ithatha: 
 
 
 
 Ukufundisa Ukucinga Ukubonisa i-boss yakho ukuba i-akhawunti yandiswa I-PostgreSQL yaba "ngokugqithisileyo" kwiingxaki zethu ze-agregate. Kodwa yaba elula kakhulu. Kwakhona iqela lethu yaziwa ukuba usebenzise. Kwiimeko, i-" Good Enough and Familiar" ifumaneka kwi-"Perfect and Confusing". Yintoni ndingathanda Ngaba unayo i-akhawunti ye-8000 / ngenyanga? Ngaba awukwazi ukuguqulwa database. Nangona kunjalo, i: 
 
 
 
 I-Kept PostgreSQL ye-transactional stuff (i-accounts ye-user, i-orders, i-catalogue ye-product) Ukongezwa kwe-DuckDB ye-analytics dashboard (kuveliswa ngexesha elinye kwi-app yethu, ngaphandle kwe-infrastructure entsha) I-pre-calculated iingxaki ezininzi ezininzi ngehora (ukugcina iziphumo, zithumelele ngokukhawuleza) ixabiso olutsha ngenyanga: $ 8,100 ($ 100 ukuba i-Server ye-DuckDB) ixesha loading Dashboard: 4 imizuzu → 0.8 iiveki Ukuphendula kweBoss: "Ukuba awukwazi ukwenza oku ngokukhawuleza?" I-Engineer's answer: "Ukuba ngamanye amaxesha kufuneka utshintshe amaxabiso ezisibhozo ukuze ufunde ukuba ufuna kuphela i-0.2 ye-one." Iimpawu ze-Benchmark Actual (IiNqaku zeNerds) Ukuba ufuna iinkcukacha zentsimbi, apha yintoni ndingathanda: Qhagamshelana 1: Ukubhekiselela Kwiimali (847M iiyure → 180 imiphumela) ClickHouse:     0.31s  ($0.03/query)  
DuckDB:         0.42s  ($0.00/query)
Snowflake:      1.80s  ($0.12/query)
TimescaleDB:   12.00s  ($0.02/query)
PostgreSQL:    43.00s  ($0.02/query)
Elasticsearch:  8.00s  ($0.04/query)
MySQL:        300.00s+ (stopped it)
MongoDB:      137.00s  (then crashed)
 Qhagamshelana 2: Top 10 imveliso ngexabiso ClickHouse:     0.28s
DuckDB:         0.51s
Snowflake:      2.10s
PostgreSQL:    67.00s
TimescaleDB:   18.00s
 Qhagamshelana 3: I-Customer Lookup (Find 1 of 47 iimveliso ze-client) MongoDB:        0.11s  (indexed)
PostgreSQL:     0.23s  (indexed)
MySQL:          0.31s  (indexed)
ClickHouse:     0.94s  (full scan)
DuckDB:         1.18s  (full scan)
TimescaleDB:    0.41s  (indexed)
 ixesha lokufaka data (180 GB) DuckDB:          0m  (reads files directly)
ClickHouse:     47m  (compressed to 23 GB)
MySQL:          89m
TimescaleDB:   142m
PostgreSQL:    238m
Snowflake:     ???  (async, took ~30m but unclear)
MongoDB:       687m  (11+ hours)
 Ukusebenza kwe-storage (180 GB ingxaki) ClickHouse:     23 GB  (87% compression)
DuckDB:        180 GB  (0%, reads source files)
PostgreSQL:    340 GB  (89% overhead - indexes)
MongoDB:       520 GB  (189% overhead)
Snowflake:      41 GB  (77% compression)
TimescaleDB:    89 GB  (51% overhead)
 Ukucinga Ukuqhathanisa iinxalenye ezininzi, usebenzisa i-column store (ClickHouse / DuckDB). Ukufumana iingxelo ezithile: usebenzisa i-rad-store kunye neengxelo (PostgreSQL / MongoDB). Ukusetyenziswa yonke into: usebenzise zombini kwaye ushiye ukucwangcisa ukuba i-database eyodwa uyakwazi yonke into. Yintoni engaba umntu uyazi malunga namathuba: I-best is not the fastest one. It is the one your team can actually use without calling you at 3 am. Khetha ngokuchanelekileyo.

Read My Stories

Le audio iveliswe ngolwimi lokuqala lwebali!

Ndidlala iiveki ezimbini ukuhlola iibhanki ze-8 kunye nexabiso efanayo - Nazi yinto efanelekileyo

About Author

IZIMVO

ZIJONGE IIMPAWU

ELI NQAKU LINIKEZELWE KU

Related Stories

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps