Optimizarea Performanței PostgreSQL pentru Baze de Date Masive

Într-o eră digitală în care volumul de date crește exponențial, capacitatea de a stoca, manipula și, mai ales, de a interoga eficient baze de date masive devine o provocare fundamentală. Această provocare este amplificată în scenarii de securitate cibernetică, unde analiza rapidă a unor cantități enorme de credențiale compromise poate face diferența între un incident minor și o breșă majoră. Vom explora călătoria fascinantă a unui produs intern, Leakozorus, și modul în care echipa sa a depășit limitările tehnice, transformând o soluție lentă într-una de înaltă performanță, bazându-se pe optimizări inteligente ale PostgreSQL. Pregătește-te să descoperi trucuri esențiale care pot revoluționa modul în care gestionezi și interoghezi seturi de date colosale.

De ce Leakozorus?

Leakozorus este un instrument intern de credential stuffing, conceput pentru a agrega scurgeri de baze de date și a efectua interogări complexe. Deși există produse similare pe piață (cum ar fi haveibeenpwned.com), Leakozorus este orientat mai mult spre securitatea ofensivă (red team) și poate fi integrat cu alte produse. Scopul său principal este de a recupera conturi compromise (nume de utilizator, e-mail, parole sau hash-uri), de a identifica reutilizarea parolelor și de a efectua căutări de subdomenii. Este utilizat de pentesteri și membri ai echipei de red team pentru a identifica vulnerabilități ușor de exploatat și de echipa de răspuns la incidente pentru a detecta reutilizarea parolelor și obiceiurile proaste de securitate. Creșterea semnificativă a numărului de atacuri cibernetice și a breșelor de date în urma pandemiei a pus o presiune imensă pe Leakozorus, scoțând în evidență limitările designului său original și necesitatea urgentă de a îmbunătăți atât performanța, cât și capacitatea platformei.

Arhitectura Sistemului

Problema principală a primei versiuni a Leakozorus era viteza lentă a interogărilor. Operațiunile de scriere erau încete, iar căutările exacte de e-mailuri sau, și mai rău, căutările fuzzy, erau extrem de lente, ducând adesea la erori de memorie. Un alt aspect problematic era timpul necesar pentru adăugarea și actualizarea datelor, care se făceau manual și durau săptămâni sau chiar luni. Acestea au fost motivele cheie pentru reproiectarea sistemului, având în vedere următoarele obiective:

• Rezultate rapide și precise.
• Interfață ușor de utilizat și interfață linie de comandă.
• Procese de gestionare a datelor simplificate.
• Accesibilitate din alte instrumente, prin intermediul unui API Web.

După teste și benchmark-uri, s-a optat pentru o bază de date centralizată PostgreSQL, datorită robustezii, vitezei și a caracteristicilor avansate de indexare. Sistemul rulează pe un server dedicat cu 128 GB RAM, 24 de nuclee CPU și 16TB de stocare, optimizat pentru aplicații cu I/O intens. Din punct de vedere software, sistemul este împărțit în cinci componente: un reverse proxy (Nginx), un backend API RESTful (FastAPI cu Gunicorn), worker-i (scrisi în Rust) pentru parsarea și inserarea datelor, o interfață frontend (React) și, desigur, baza de date PostgreSQL.

Provocarea Căutărilor Fuzzy

Diferența majoră a Leakozorus față de produse similare este capacitatea de a efectua căutări fuzzy sau pe baza de modele, nu doar căutări exacte. O căutare exactă ar fi '[email protected]', în timp ce o căutare pe baza de model ar fi '*john*doe*@local.com'. Utilizatorii tind să folosească adrese de e-mail similare, iar găsirea '[email protected]', '[email protected]' sau '[email protected]' este esențială. Căutările fuzzy permit identificarea e-mailurilor care sunt 'aproape' de un anumit șir. Această cerință a dus la necesitatea creării multor indexuri, fără a degrada performanța, ci doar crescând cerințele de stocare.

Trucuri de Optimizare PostgreSQL

Pentru a atinge o performanță optimă, au fost aplicate multiple strategii de optimizare a PostgreSQL.

Configurarea PostgreSQL: Un Punct de Plecare Solid

Un generator de configurație PostgreSQL precum pgtune.leopard.in.ua a fost punctul de plecare, selectând modul 'Data Warehouse'. Configurația rezultată a fost ulterior ajustată manual. Parametrii cheie au fost:

• max_connections = 20: Minimizarea numărului de conexiuni pentru a aloca resurse interogărilor.
• shared_buffers = 33GB (aproximativ 25% din RAM): Memoria tampon partajată.
• effective_cache_size = 99GB (aproximativ 75% din RAM): Estimarea cache-ului sistemului de operare.
• maintenance_work_mem = 2GB: Memoria pentru operațiuni de întreținere.
• checkpoint_completion_target = 0.9: Controlul scrierilor pe disc.
• wal_buffers = 16MB: Memoria pentru jurnalul de tranzacții.
• random_page_cost = 1.1, effective_io_concurrency = 200, work_mem = 72089kB, min_wal_size = 4GB, max_wal_size = 16GB: Parametri legați de I/O și memorie de lucru.
• max_worker_processes = 24, max_parallel_workers_per_gather = 12, max_parallel_workers = 24, max_parallel_maintenance_workers = 4: Procese de lucru paralele.

Unul dintre cei mai importanți parametri ajustați a fost default_statistics_target = 10000. Acesta controlează cantitatea de statistici colectate de PostgreSQL despre indexuri. Statistici mai detaliate ajută planificatorul de interogări să ia decizii mai bune, mai ales în cazul datelor cu distribuții inegale (ex: Gmail fiind domeniul dominant). De asemenea, autovacuum = off și autocommit = off au fost dezactivate pentru a aloca toate resursele interogărilor utilizatorilor, iar statement_timeout = 180s a fost setat pentru a preveni interogările excesiv de lungi.

Indexuri "Regulate" și Limitările lor

Indexurile, precum B-Tree (cel mai comun tip în PostgreSQL), sunt structuri de date care accelerează căutările. Sunt excelente pentru egalitate și verificări de interval (=, <, BETWEEN) și pentru căutări de prefix (LIKE 'prefix%'). Cu toate acestea, ele sunt ineficiente pentru căutări de tip LIKE '%substring%' sau LIKE '%suffix', deoarece ar necesita o scanare secvențială a întregii tabele, inacceptabilă pentru miliarde de intrări.

Indexuri Trigram (GIN vs. GIST)

Pentru a rezolva problema căutărilor de substring (LIKE '%john%'), s-au folosit indexuri trigram, disponibile începând cu PostgreSQL 9.1. Acestea funcționează prin indexarea tuturor trigramelor (secvențe de trei caractere) dintr-un șir. Astfel, o căutare LIKE '%john%' poate filtra rapid rândurile unde trigramele 'joh' și 'ohn' sunt prezente. Un avantaj suplimentar este că aceste indexuri oferă și operatori bazați pe similaritate. Între GIN și GIST, s-a preferat GIN (Generalized Inverted Index) în locul GIST (Generalized Search Tree). Deși GIN-urile sunt mai lente de construit și ocupă mai mult spațiu, ele sunt mai rapide la interogare și oferă rezultate exacte, minimizând falsurile pozitive, esențial în cazul Leakozorus.

Indexuri pe Expresii: Inteligența Funcțională

Pentru căutările de sufix, cum ar fi SELECT * FROM table WHERE domain LIKE '%domain.com', indexurile B-Tree standard sunt ineficiente. Soluția a fost crearea de indexuri pe expresii SQL, nu doar pe coloane. Concret, s-a creat un index pe expresia reverse(domain). Astfel, interogarea a fost transformată în SELECT * FROM table WHERE reverse(domain) LIKE reverse('%domain.com'), permițând utilizarea eficientă a indexului B-Tree pe șirul inversat.

Căutări Mai Inteligente prin Moștenire de Tabele

În loc să aibă o singură tabelă masivă, datele au fost împărțite pe scurgeri individuale, utilizând sistemul de moștenire al PostgreSQL. Fiecare scurgere nouă devine o tabelă moștenită. Această abordare oferă multiple avantaje:

• Mai ușor de întreținut.
• Indexarea mai rapidă a tabelelor mici comparativ cu o singură tabelă mare.
• Filtrare ușoară la nivel de interogare.
• Operațiunile de inserare/actualizare într-o tabelă nu afectează căutările în alte tabele.
• Permite adăugarea/eliminarea/actualizarea scurgerilor fără a face sistemul temporar inutilizabil.

Deși SELECT * from main_table WHERE domain = 'synacktiv.com' ar căuta în toate tabelele moștenite, iar planificatorul PostgreSQL este inteligent, problema apare dacă o căutare este inițiată pe o scurgere care nu este încă indexată, ducând la scanări secvențiale lente. Soluția adoptată a fost construirea manuală a interogărilor SQL. Sistemul analizează cererea utilizatorului și construiește o interogare UNION ALL care include doar tabelele relevante și deja indexate. De exemplu, dacă un utilizator caută o parolă, interogarea va viza doar tabelele unde parolele sunt indexate, ignorând tabelele cu doar nume de utilizator sau cele în curs de indexare.

Rezultate și Benchmarks

Aplicarea tuturor acestor trucuri a dus la rezultate impresionante, testate pe peste 15 miliarde de intrări (95 de dump-uri de e-mailuri, parole, nume de utilizator și hash-uri). Iată o comparație a timpilor de răspuns:

Căutările exacte sunt semnificativ mai rapide decât cele fuzzy, ceea ce este acceptabil având în vedere diferența de utilizare. Căutările exacte sunt folosite în principal prin API de către serviciile interne, necesitând un timp de răspuns sub 1000ms. Căutările fuzzy, utilizate în special de echipele de red team, pot tolera un timp de așteptare de aproximativ un minut. Performanța atinsă este remarcabilă, demonstrând eficacitatea strategiilor de optimizare implementate.

Întrebări Frecvente (FAQ)

De ce a fost ales PostgreSQL pentru Leakozorus?

PostgreSQL a fost ales pentru robustețea, viteza și setul său bogat de funcționalități pentru indexare și gestionarea datelor, fiind ideal pentru volume mari de date și interogări complexe.

Ce sunt indexurile trigram și la ce ajută?

Indexurile trigram (ex: GIN) descompun șirurile de caractere în secvențe de trei caractere. Ele accelerează semnificativ căutările de substring (LIKE '%ceva%') care ar fi extrem de lente cu indexuri B-Tree tradiționale.

Cum ajută moștenirea tabelelor în scalabilitate?

Moștenirea tabelelor permite împărțirea unei baze de date mari în tabele mai mici, gestionabile individual. Aceasta facilitează indexarea, întreținerea și permite ca operațiunile pe o anumită scurgere de date să nu blocheze căutările pe alte scurgeri.

Cât de important este parametrul default_statistics_target?

Este crucial. O valoare mare (ex: 10000) permite PostgreSQL să colecteze statistici mult mai detaliate despre distribuția datelor. Aceste statistici sunt folosite de planificatorul de interogări pentru a alege cel mai eficient mod de a executa o interogare, evitând scanările secvențiale costisitoare.

Concluzie

Călătoria de optimizare a Leakozorus a oferit o înțelegere mai profundă a mecanismelor interne ale PostgreSQL și a demonstrat cum un sistem eficient, cu întreținere redusă, poate oferi rezultate rapide și precise. Timpul necesar pentru inserarea de noi date a fost redus la câteva secunde, procesul fiind acum automatizat. Deși optimizările actuale sunt impresionante, există întotdeauna loc de îmbunătățire, iar feedback-ul utilizatorilor și avansurile tehnologice viitoare (cum ar fi indexurile Bloom) vor continua să modeleze evoluția sistemului. Această experiență subliniază nu doar capacitatea de a construi sisteme performante, ci și importanța conștientizării riscurilor legate de securitatea cibernetică și de utilizarea unor parole puternice și unice. Vederea unei parole expuse poate fi un imbold puternic pentru adoptarea unor practici de securitate mai bune, cum ar fi utilizarea managerilor de parole, transformând o discuție tehnică într-o lecție valoroasă de securitate personală.

Dacă vrei să descoperi și alte articole similare cu Optimizarea Performanței PostgreSQL pentru Baze de Date Masive, poți vizita categoria Fitness.