Extensii PostgreSQL care merită știute

Ai nevoie de adrese de e-mail la care majusculele să nu conteze, așa că normalizezi fiecare adresă în aplicație și speri că nicio interogare nu uită. Câteva luni mai târziu vrei o căutare care iartă o literă greșită într-un nume de produs, așa că aduci toate rândurile și calculezi potrivirile în cod. Ambele merg și ambele sunt lucruri pe care PostgreSQL le are deja cu extensia potrivită. Cei mai mulți dezvoltatori dau peste extensii după ce au reconstruit una.

O extensie transformă o bază de date generală într-una croită pentru o problemă anume. Nu este un plugin care rulează lângă server și nici un serviciu care intermediază interogări: este un pachet de obiecte SQL instalat într-o singură bază de date.

Ce este de fapt o extensie

Încărcarea unei extensii „înseamnă, în esență, rularea fișierului de script al extensiei”, iar scriptul „creează de obicei obiecte SQL noi, cum ar fi funcții, tipuri de date, operatori și metode de suport pentru indexuri”, după cum spune pagina CREATE EXTENSION. Fiecare extensie are un fișier de control care o numește și indică scriptul.

PostgreSQL ține minte ce obiecte au venit din ce extensie. CREATE EXTENSION „înregistrează identitățile tuturor obiectelor create, ca să poată fi șterse din nou dacă se emite DROP EXTENSION”. Fără ea, o extensie este SQL pe care nu îl mai poți scoate în siguranță.

CREATE EXTENSION și întrebarea despre permisiuni

Comanda instalează în baza de date curentă: CREATE EXTENSION citext;. Nu este un comutator la nivel de server: o a doua bază de date pe același server are nevoie de propria instrucțiune, iar numele extensiilor trebuie să fie „unice la nivel de bază de date”.

Prima opreliște sunt permisiunile. Manualul spune că încărcarea „cere de obicei aceleași privilegii ca cele necesare pentru a crea obiectele componente. Pentru multe extensii asta înseamnă privilegii de superuser.” Unele sunt marcate trusted: „dacă extensia este marcată trusted în fișierul de control, poate fi instalată de orice utilizator care are privilegiul CREATE pe baza de date curentă”, cu obiectele din ea deținute de superuserul de bootstrap. pgcrypto, pg_trgm și citext sunt trusted, de aceea rolul aplicației le poate instala de obicei.

De ce disponibilitatea depinde de unde rulează baza de date

La Supabase, extensiile sunt o funcție a produsului: serviciul vine „preconfigurat cu peste 50 de extensii”, le pornești din panou sau rulezi aceeași comandă SQL din editor, iar majoritatea ajung în schema extensions, pe care public o vede. O versiune nouă apare doar după un upgrade de software.

Neon publică un tabel cu extensiile suportate pentru fiecare versiune PostgreSQL, iar o liniuță înseamnă „încă nesuportată pentru versiunea aceea”. Câteva sunt experimentale și cer SET neon.allow_unstable_extensions = 'true'; înainte de instalare, altele au nevoie de biblioteci preîncărcate configurate prin API-ul de proiect, iar o versiune nouă cere repornirea compute-ului.

Amazon RDS arată același tipar, cu versiuni. La momentul scrierii, matricea RDS listează pgcrypto 1.4 pe PostgreSQL 18 și 1.3 pe 16, PostGIS 3.6.3 pe 18 și 3.4.6 pe 16, iar PostGIS apare ca indisponibil pentru candidatele PostgreSQL 19. Pagina avertizează și că „versiunile extensiilor nu se actualizează automat în timpul upgrade-urilor de versiune de engine” și că AWS poate renunța la o extensie cu 12 luni preaviz, sau mai puțin în caz de probleme de securitate.

Nimic din asta nu e un motiv să eviți un furnizor gestionat, ci să verifici lista de extensii înainte ca arhitectura să depindă de una. O interogare care are nevoie de PostGIS este un angajament diferit de una care are nevoie de pg_trgm, iar doar lista furnizorului îți spune pe care îl poți ține.

O extensie este cod care rulează în baza ta de date

Manualul este direct: instalarea unei extensii ca superuser „cere să ai încredere că autorul extensiei a scris scriptul de instalare în mod sigur”, și recomandă instalarea extensiilor nevalidate într-o schemă în care utilizatorii neîncrezători nu au CREATE. Scriptul rulează cu drepturile celui care îl instalează, de aceea pornești doar ce ai nevoie.

Ștergerea merită aceeași atenție. DROP EXTENSION elimină „obiectele membre și alte rutine dependente în mod explicit”. Implicit refuză dacă depinde altceva de ele; CASCADE șterge și dependenții, ceea ce e felul în care o coloană de tip extensie dispare împreună cu datele ei.

Ca să vezi ce oferă serverul tău, interoghează catalogul: SELECT name, default_version, installed_version FROM pg_available_extensions;. Vederea listează ce e disponibil, NULL în installed_version înseamnă disponibil dar neîncărcat, iar pg_extension arată ce e instalat. Răspunde mai repede decât documentația furnizorului când ești deja conectat.

pgcrypto: hashuri, hashuri cu cheie și parole

pgcrypto „oferă funcții criptografice pentru PostgreSQL” și are nevoie de OpenSSL; „nu se instalează dacă suportul OpenSSL nu a fost selectat la compilarea PostgreSQL”. Funcțiile care merită știute sunt digest(data, type) pentru un hash simplu, hmac(data, key, type) pentru unul cu cheie, gen_random_bytes(count) pentru octeți aleatori și crypt(password, salt) cu gen_salt(type) pentru parole. Tipurile de digest includ md5, sha1, familia SHA-2 și orice altceva suportă OpenSSL.

Situația potrivită este o amprentă pe care o compari, nu un secret pe care îl păstrezi. Un hash de conținut care îți permite să sari peste reprocesarea unui document neschimbat sau un HMAC peste o adresă de e-mail, cu cheia ținută în afara bazei de date, sunt amândouă potriviri bune, iar encode(digest(...), 'hex') îți dă o valoare pe care o poți indexa. Parolele sunt cealaltă potrivire bună, dar prin crypt() cu gen_salt('bf'), pentru că algoritmii aceia sunt lenți intenționat, cu sare și adaptivi, ceea ce manualul numește singura cale de a îngreuna atacul prin forță brută.

Situația greșită este digest() pe o coloană de parole. Un singur SHA-256 este rapid prin construcție, nu are sare și nici factor de efort, deci parole identice dau hashuri identice, iar un tabel furat e ieftin de atacat. Un hash fără sare peste o valoare cu entropie mică, cum ar fi un e-mail, se recuperează prin ghicire. Dacă ai nevoie de hash de parolă în baza de date, folosește crypt() cu bf sau sha512crypt; dacă poți, fă-o în aplicație. gen_random_uuid() din pgcrypto este documentat ca „obsolete”, pentru că apelează funcția din core, deci nu ai motiv să instalezi pgcrypto doar pentru identificatori.

uuid-ossp sau funcțiile din core

uuid-ossp oferă uuid_generate_v1(), uuid_generate_v1mc(), uuid_generate_v3(), uuid_generate_v4() și uuid_generate_v5(), plus constante de namespace precum uuid_ns_url(). Pagina lui spune că „este necesar doar pentru cerințe speciale dincolo de ce oferă core-ul PostgreSQL” și trimite la funcțiile UUID built-in.

Trimiterea aceea este partea utilă. PostgreSQL 13 a adăugat gen_random_uuid() în core; notele de versiune spun „anterior funcțiile de generare UUID erau disponibile doar în modulele externe uuid-ossp și pgcrypto”. PostgreSQL 18 a adăugat uuidv7(), un UUID „version 7 (time-ordered)”, și uuidv4() ca alias explicit pentru varianta aleatoare. Răspunsul implicit pentru o cheie primară aleatoare pe un server actual este deci gen_random_uuid(), fără nicio extensie.

Situația potrivită pentru uuid-ossp este un UUID pe care trebuie să îl deduci, nu să îl generezi: uuid_generate_v3() sau uuid_generate_v5() transformă un namespace și un nume în același UUID de fiecare dată, ceea ce dă un identificator stabil unui rând importat. Situația greșită este să îl instalezi pentru uuid_generate_v4() din obișnuință. Costă o extensie și nu aduce nimic peste funcția built-in. Pe PostgreSQL 18, un tabel cu multe inserări câștigă și din uuidv7(): o valoare ordonată temporal se așază spre capătul indexului, nu împrăștiată prin el.

citext: text care nu ține cont de majuscule

citext este un tip de șir care „apelează intern lower când compară valori” și în rest „se comportă aproape exact ca text”. Declari o coloană citext și 'Larry' = 'larry' este adevărat, inclusiv într-un index unic, adică exact cazul pe care îl vrei pentru o coloană de e-mail sau de nume de utilizator.

Situația potrivită este un identificator de autentificare într-o bază a cărei collation se potrivește cu utilizatorii tăi, de obicei adrese ASCII. Situația greșită este o coloană cu nume sau text în mai multe limbi, ori una unde contează corectitudinea Unicode. Documentația spune explicit că plierea majusculelor „depinde de setarea LC_CTYPE a bazei de date”, că felul în care compară este fixat la crearea bazei și că „nu este cu adevărat case-insensitive în termenii definiți de standardul Unicode”; nu tratează cazurile în care o majusculă are două echivalente minuscule. Este și mai lent decât text, pentru că operatorii copiază datele și le transformă în minuscule, iar doar text suportă deduplicarea B-tree.

De aceea pagina propune în schimb collation-urile nedeterministe, care compară fără majuscule și fără diacritice și tratează mai corect cazurile Unicode. Cu o coloană citext existentă, ține minte că schema ei trebuie să fie în search_path, altfel se folosesc operatorii normali, sensibili la majuscule, și nimeni nu observă până nu intră un duplicat.

pg_trgm: căutare aproximativă care intră în index

pg_trgm sparge șirurile în trigrame de trei caractere și le compară. similarity(a, b) întoarce un număr de la zero la unu, iar operatorul % este adevărat când similaritatea trece de pg_trgm.similarity_threshold, implicit 0.3. Important este suportul de index: indexurile GiST și GIN cu clasele de operatori trigram accelerează LIKE, ILIKE, expresiile regulate și =, nu doar operatorii de similaritate.

Situația potrivită este o casetă de căutare peste nume pe care le controlezi: clienți, produse, articole, unde o literă greșită tot trebuie să găsească rândul, sau unde ILIKE '%termen%' pe un tabel mare a ajuns scanare secvențială. Situația greșită este căutarea exactă, unde un B-tree e mai mic și mai rapid, sau căutarea full-text cu relevanță, care e treaba lui tsvector și înțelege cuvinte, nu fragmente de caractere. Două limite merită știute înainte să promiți funcția: un șablon „fără trigrame extractibile degenerează într-o scanare a întregului index”, iar ordonarea după distanță ORDER BY t <-> 'termen' este eficientă pe GiST, nu pe GIN.

PostGIS: când locația este datele

PostGIS „extinde capacitățile bazei de date relaționale PostgreSQL” ca să poată stoca, indexa și căuta date de locație: forme în două și trei dimensiuni, indexuri spațiale și funcții pentru distanțe, intersecții și geocodare. Este cel mai mare element din listă și singurul cu o amprentă operațională serioasă.

Situația potrivită este o întrebare la care baza de date trebuie să răspundă spațial: ce zone de livrare conțin o adresă sau ce puncte cad într-o rază față de un vehicul aflat în mișcare, cu un index care face munca. Situația greșită este o pereche de coloane cu latitudine și longitudine pe care le stochezi, le arăți pe hartă și nu le interoghezi niciodată geometric. Două coloane numerice și o formulă de distanță în aplicație sunt suficiente, iar PostGIS ar adăuga o extensie a cărei versiune o controlează hostul; RDS o marchează indisponibilă pentru preview-ul de PostgreSQL 19.

Un mod scurt de a decide

Pune trei întrebări înainte să adaugi una. Lista furnizorului o include, și la ce versiune? Este trusted, ca rolul aplicației să o poată instala fără superuser? Și treaba pe care o face își are locul în bază, nu în aplicație?

Apoi pune CREATE EXTENSION într-o migrare, lângă tabelele care au nevoie de ea, ca un mediu nou să semene cu producția. Despre fluxul acesta am scris în migrări pornite de la local. O extensie este o dependență ca oricare alta: numește-o, ține-i versiunea, scoate-o când funcția a dispărut. Alternativa, o bază de date care cară în liniște șase module pe care nimeni nu își amintește cine le-a pornit, este felul în care o bază generală devine una specifică din greșeală.