...
Pregătire interviuri

100 de întrebări de interviu PostgreSQL, cu răspunsuri

Întrebările despre PostgreSQL care apar des la interviurile tehnice, fiecare cu un răspuns scurt, explicația pe larg și ce urmărește intervievatorul. Citește-le sau exersează cu răspunsurile ascunse. Înveți de la zero cu Curs PostgreSQL: primele 10 lecții sunt gratuite.

Pagină coordonată de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la

  • 100întrebări
  • 47junior
  • 39mid
  • 14senior
  • 145 minde citit
Întrebări de interviu PostgreSQL cu răspunsuri

Acestea sunt 100 de întrebări de interviu despre PostgreSQL, adunate din interviurile pentru posturi de backend, Java, Node.js și Database Administrator. Nu repetă întrebările generale de SQL, ci merg pe ce este specific PostgreSQL: sistemul de tipuri și conversiile, JSONB și căutarea full-text, indecșii și planificatorul, MVCC, nivelurile de izolare și blocările, PL/pgSQL și triggerele, rolurile și Row Level Security, apoi întreținerea unui server real: VACUUM, statistici, partiții, secvențe și noutățile din PostgreSQL 18.

Fiecare întrebare are un răspuns scurt, bun de spus în primele 30 de secunde, apoi explicația completă, cu exemple de cod și mesajele de eroare exacte pe care le vezi în psql, plus motivul pentru care intervievatorul o pune. Încearcă să răspunzi cu voce tare înainte să citești, iar pentru întrebările cu cod rulează exemplele în laboratorul din curs: un răspuns pe care l-ai verificat singur se ține minte mult mai bine.

psql, tipuri de date și particularități PostgreSQL

1 Junior

Lucrezi zilnic în psql. Ce fac meta-comenzile \d, \dt, \d+ produse, \x și \conninfo și de ce nu se termină cu punct și virgulă?

Răspuns scurt: Sunt comenzi ale clientului psql, nu SQL: \dt listează tabelele, \d produse arată coloanele, indecșii și constrângerile, \d+ adaugă detalii (mărime, definiția view-urilor), \x comută afișarea pe verticală, iar \conninfo spune la ce bază și cu ce utilizator ești conectat.

Comenzile care încep cu backslash sunt interpretate de psql, nu de server. De aceea nu se termină cu ; și nu merg din aplicația ta sau din pgAdmin: psql le transformă intern în interogări pe catalogul sistemului.

Cele folosite cel mai des:

  • \l listează bazele de date, \c magazin te mută pe altă bază;
  • \dt, \dv, \di, \df, \dn, \du listează tabelele, view-urile, indecșii, funcțiile, schemele și rolurile; acceptă tipare (\dt vanzari.*);
  • \d produse arată coloanele cu tipuri, NOT NULL și valori implicite, apoi indecșii, constrângerile CHECK, cheile străine și triggerele;
  • \d+ produse adaugă mărimea, stocarea coloanelor și, la view-uri, definiția;
  • \x (sau \x auto) afișează fiecare rând ca listă de perechi coloană-valoare, util la tabele late;
  • \timing afișează durata fiecărei interogări;
  • \e deschide ultima interogare în editor, \i fisier.sql rulează un script.

Un truc util la interviu: psql -E (sau \set ECHO_HIDDEN on) îți arată interogarea SQL pe care o rulează psql pentru fiecare meta-comandă. Așa înveți catalogul (pg_class, pg_attribute, pg_index) și poți scrie aceleași verificări în scripturi.

Ce urmărește intervievatorul: Intervievatorul verifică dacă ai lucrat efectiv cu PostgreSQL în linia de comandă. Greșeala tipică este să crezi că \d e SQL și să încerci să-l rulezi din aplicație.

2 Junior

Care este diferența dintre text, varchar(n) și char(n) în PostgreSQL? Câștigi performanță dacă declari varchar(50) în loc de text?

Răspuns scurt: În PostgreSQL, text și varchar(n) sunt stocate identic și au aceeași performanță; varchar(n) adaugă doar verificarea lungimii. char(n) completează cu spații și e aproape mereu o alegere proastă.

Toate trei folosesc aceeași reprezentare internă (varlena): lungimea, apoi octeții textului. Nu există un câștig de viteză sau de spațiu dacă declari o lungime maximă, spre deosebire de alte baze de date unde VARCHAR(255) influențează alocarea memoriei.

  • text: fără limită (practic până la 1 GB pe valoare).
  • varchar(n): la fel ca text, plus o verificare: un text mai lung dă eroarea value too long for type character varying(50). varchar fără n este identic cu text.
  • char(n): completează valoarea cu spații până la n caractere. Spațiile de la final sunt ignorate la comparații, dar apar la concatenare și în aplicații. Ocupă mai mult, nu mai puțin.

Recomandarea din documentația PostgreSQL și din practică: folosește text, iar dacă ai o regulă de business pentru lungime, pune-o într-o constrângere:

SQL
CREATE TABLE clienti (
  nume text NOT NULL CHECK (char_length(nume) <= 100)
);

Avantajul constrângerii: o poți schimba fără să modifici tipul coloanei. Mărirea limitei unui varchar(n) nu rescrie tabelul în versiunile recente, dar micșorarea ei sau trecerea la alt tip poate rescrie tot tabelul.

Ce urmărește intervievatorul: Se verifică dacă știi specificul PostgreSQL, nu reguli aduse din MySQL sau SQL Server. Răspunsul greșit tipic: „varchar(n) e mai rapid pentru că ocupă mai puțin”.

Eroarea, în practică:value too long for type character varying(n)

3 Junior

Ce afișează fiecare instrucțiune de mai jos în PostgreSQL? De ce prima și a doua merg, iar a treia dă eroare?

SQL
SELECT '5'::int + 3;
SELECT '5' || 3;
SELECT 5 || 3;

Răspuns scurt: Prima afișează 8 (textul '5' convertit explicit în integer), a doua afișează textul '53' (operatorul || acceptă un text și orice valoare convertită la text), iar a treia dă operator does not exist: integer || integer, pentru că nu există concatenare între două numere.

În PostgreSQL, un literal între apostrofuri, cum e '5', are inițial tipul unknown. Tipul final îl decide contextul:

  • '5'::int + 3: conversia este explicită, deci se face adunare de întregi: 8.
  • '5' || 3: operatorul || are o variantă text || anynonarray. '5' devine text, iar 3 este convertit la text pentru concatenare: rezultatul este '53'.
  • 5 || 3: ambele valori sunt integer, iar PostgreSQL nu are operatorul integer || integer și nici nu convertește implicit două numere în text. Primești:
ERROR:  operator does not exist: integer || integer
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Reparația este o conversie explicită: 5::text || 3 sau concat(5, 3).

Lecția de fond: PostgreSQL face foarte puține conversii implicite între categorii de tipuri (din versiunea 8.3 a eliminat multe dintre ele). Unde MySQL ar transforma liniștit un număr în text sau invers, PostgreSQL îți cere să spui ce vrei. De aceea vezi des operator does not exist când compari o coloană integer cu un parametru trimis ca text din aplicație.

Ce urmărește intervievatorul: Se verifică înțelegerea sistemului de tipuri și a mesajului operator does not exist. Candidații slabi spun că a doua interogare dă 8 sau eroare.

Eroarea, în practică:operator does not exist: integer = text

4 Junior

Care este diferența dintre o coloană serial și una GENERATED ALWAYS AS IDENTITY? Pe care o alegi într-un proiect nou și de ce?

Răspuns scurt: Ambele folosesc o secvență, dar serial e o scurtătură veche (integer + secvență + DEFAULT nextval), în timp ce identity este standard SQL, legată strâns de coloană, cu drepturi și copiere mai simple. Într-un proiect nou alegi identity.

serial nu este un tip real. Comanda

SQL
CREATE TABLE a (id serial PRIMARY KEY);

se transformă în: o secvență a_id_seq, o coloană integer cu DEFAULT nextval('a_id_seq') și o legătură de proprietate între ele. Problemele practice:

  • oricine poate insera un id explicit, iar secvența rămâne în urmă: următorul INSERT fără id dă duplicate key value violates unique constraint;
  • secvența e un obiect separat: rolul aplicației are nevoie de USAGE pe ea, altfel primește permission denied for sequence a_id_seq;
  • CREATE TABLE b (LIKE a INCLUDING ALL) copiază un DEFAULT care folosește aceeași secvență.

Cu identity (PostgreSQL 10+):

SQL
CREATE TABLE a (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
  • ALWAYS refuză id-urile explicite (cannot insert a non-DEFAULT value into column "id"), cu excepția lui OVERRIDING SYSTEM VALUE, folosit conștient la importuri;
  • BY DEFAULT permite id-uri manuale, ca serial;
  • drepturile pe tabel ajung pentru inserare, iar ALTER TABLE a ALTER COLUMN id RESTART WITH 1000 modifică secvența fără să-i știi numele.

Alege și bigint pentru tabelele care pot crește mult: trecerea ulterioară de la integer la bigint rescrie tot tabelul.

Ce urmărește intervievatorul: Se verifică dacă știi practicile moderne PostgreSQL. Greșeala tipică: „serial e tipul de autoincrement din PostgreSQL” fără să știi ce generează.

Eroarea, în practică:cannot insert a non-DEFAULT value into columncolumn can only be updated to DEFAULT

5 Junior

Ce afișează interogarea de mai jos în PostgreSQL? De ce round(2.5) și round(2.5::float8) dau rezultate diferite?

SQL
SELECT round(2.5) AS n, round(2.5::float8) AS f,
       round(3.5::float8) AS f2, 1/3 AS i, 1/3.0 AS d;

Răspuns scurt: Afișează n = 3, f = 2, f2 = 4, i = 0 și d = 0.33333333333333333333. Pe numeric, round rotunjește jumătatea în sus (departe de zero), iar pe double precision folosește regula „la par” a procesorului.

Rezultatul real:

 n | f | f2 | i |           d
---+---+----+---+------------------------
 3 | 2 |  4 | 0 | 0.33333333333333333333

Explicațiile:

  • 2.5 scris direct este un literal numeric. Pentru numeric, round rotunjește valorile aflate exact la jumătate departe de zero: 2.5 → 3, -2.5 → -3.
  • 2.5::float8 este double precision. Aici PostgreSQL folosește rotunjirea sistemului, de obicei „round half to even” (rotunjirea bancherului): 2.5 → 2, 3.5 → 4. Scopul ei este să nu introducă o eroare sistematică în sus la sume mari.
  • 1/3 este împărțire de întregi, deci se taie partea zecimală: 0.
  • 1/3.0 are un operand numeric, deci rezultatul e numeric cu 20 de zecimale.

Consecința practică: calculele cu bani se fac în numeric, iar rotunjirea o faci explicit, într-un singur loc, cu round(x, 2). Dacă o parte din calcul trece prin float8 (de exemplu, o coloană real sau o funcție care întoarce double precision), rezultatele pot diferi cu un ban de cele din aplicație, iar diferențele apar exact la valorile terminate în 5.

Ce urmărește intervievatorul: Intervievatorul caută atenția la tipuri numerice și la rotunjire, sursa multor diferențe de un ban în facturi. Răspunsul slab: „round rotunjește mereu în sus la .5”.

Eroarea, în practică:function round(double precision, integer) missing

6 Junior

Ce afișează expresiile de mai jos? Explică aritmetica datelor în PostgreSQL.

SQL
SELECT date '2025-01-31' + interval '1 month' AS a,
       date '2025-03-31' - interval '1 month' AS b,
       date '2025-01-31' + 1 AS c,
       date '2025-03-14' - date '2025-01-01' AS d;

Răspuns scurt: a și b dau 2025-02-28 00:00:00 (timestamp, cu ziua ajustată la sfârșitul lunii), c dă data 2025-02-01, iar d dă numărul întreg 72 (zile).

Regulile de bază:

  • date + integer și date - integer adaugă sau scad zile și întorc tot date: date '2025-01-31' + 1 = 2025-02-01.
  • date - date întoarce un integer, numărul de zile: 72.
  • date + interval întoarce un timestamp (cu ora 00:00:00), pentru că intervalul poate conține ore și minute.

Adunarea lunilor are o capcană: 31 ianuarie + 1 lună ar fi 31 februarie, care nu există, așa că PostgreSQL ajustează la ultima zi a lunii: 28 februarie. La fel, 31 martie - 1 lună = 28 februarie. Consecința: (d + interval '1 month') - interval '1 month' nu e mereu egal cu d.

Pentru „sfârșitul lunii”, forma sigură este:

SQL
SELECT date_trunc('month', date '2025-02-10') + interval '1 month' - interval '1 day';

Alte funcții utile: age(a, b) pentru diferența în ani, luni și zile, date_trunc('week', ts) pentru începutul săptămânii (luni), extract(isodow FROM d) pentru ziua săptămânii (1 = luni), make_date(2025, 3, 14) pentru construirea unei date.

Când lucrezi cu timestamptz, adunarea unui interval de 1 day ține cont și de trecerea la ora de vară în fusul sesiunii, iar 24 hours nu.

Ce urmărește intervievatorul: Se verifică dacă știi tipurile rezultate și cazurile limită de calendar. Greșeala tipică: a presupune că adunarea unei luni e reversibilă.

Eroarea, în practică:date/time field value out of range

7 Junior

Un ORM a creat tabelul cu CREATE TABLE "Utilizatori" ("Id" serial, "Email" text). În psql, SELECT * FROM Utilizatori dă relation "utilizatori" does not exist. De ce, și cum eviți problema?

Răspuns scurt: PostgreSQL transformă în litere mici identificatorii scriși fără ghilimele, deci caută utilizatori. Tabelul a fost creat cu ghilimele, ca Utilizatori, și trebuie scris mereu "Utilizatori". Soluția durabilă: nume doar cu litere mici, în snake_case.

Regula PostgreSQL pentru identificatori (nume de tabele, coloane, funcții):

  • fără ghilimele duble, numele este convertit în litere mici: Utilizatori, UTILIZATORI și utilizatori înseamnă toate utilizatori;
  • între ghilimele duble, numele se păstrează exact, cu majuscule, spații sau diacritice: "Utilizatori" este alt obiect decât utilizatori.

ORM-urile care pun ghilimele (Entity Framework, unele configurații Hibernate, Prisma) creează nume cu majuscule. După aceea, orice interogare scrisă de mână trebuie să le repete exact:

SQL
SELECT "Id", "Email" FROM "Utilizatori";

Eroarea relation "utilizatori" does not exist îți arată numele căutat: e scris cu litere mici, semn că problema e conversia.

Cum eviți:

  • convenția clasică PostgreSQL: tabele și coloane în snake_case, cu litere mici (utilizatori, data_inscrierii);
  • configurezi ORM-ul să genereze astfel de nume (strategii de denumire în Hibernate, UseSnakeCaseNamingConvention în EF Core);
  • verifici numele reale cu \dt sau SELECT relname FROM pg_class WHERE relname ILIKE 'utilizatori'.

Atenție și la confuzia inversă: ghilimelele duble sunt pentru identificatori, apostrofurile pentru texte. WHERE email = "ana@x.ro" caută o coloană numită ana@x.ro.

Ce urmărește intervievatorul: Se verifică o capcană frecventă în proiectele cu ORM. Candidatul bun recunoaște imediat litera mică din mesaj ca indiciu.

Eroarea, în practică:table does not exist (DROP)

8 Junior

Când folosești o coloană de tip array (text[], integer[]) în PostgreSQL? Cum cauți un element, cum desfaci array-ul în rânduri și de la ce index începe numerotarea?

Răspuns scurt: Array-urile sunt potrivite pentru liste mici, fără atribute proprii, citite împreună cu rândul (etichete, numere de telefon). Cauți cu = ANY(col) sau col @> ARRAY[...], desfaci cu unnest, iar indexarea începe de la 1.

Operațiile de bază:

SQL
SELECT titlu FROM articole WHERE 'postgresql' = ANY (etichete);
SELECT titlu FROM articole WHERE etichete @> ARRAY['postgresql', 'jsonb'];
SELECT titlu, unnest(etichete) AS eticheta FROM articole;
SELECT etichete[1] AS prima, cardinality(etichete) AS nr FROM articole;
SELECT autor_id, array_agg(titlu ORDER BY titlu) FROM articole GROUP BY autor_id;

Detalii care apar la interviu:

  • numerotarea începe de la 1; etichete[0] întoarce NULL, nu eroare;
  • pe o expresie trebuie paranteze: (ARRAY[10,20,30])[1]; fără ele primești syntax error at or near "[";
  • @> (conține), <@ (este conținut) și && (au elemente comune) pot folosi un index GIN pe coloană;
  • array_length(a, 1) dă NULL pentru array gol, cardinality(a) dă 0.

Când nu folosești array: când elementele au propriile atribute (o etichetă cu descriere), când ai nevoie de chei străine către alt tabel (nu poți pune FK pe elementele unui array) sau când lista crește nelimitat și se modifică des (fiecare modificare rescrie tot array-ul). Atunci tabelul de legătură clasic e mai bun.

Ce urmărește intervievatorul: Se verifică dacă știi tipurile specifice PostgreSQL și limitele lor. Greșeala tipică: array-uri folosite în locul unei relații mulți-la-mulți cu integritate referențială.

Eroarea, în practică:malformed array literal

9 Junior

La ce folosește generate_series în PostgreSQL? Dă trei exemple practice.

Răspuns scurt: generate_series(start, stop[, pas]) produce un set de rânduri cu numere sau momente de timp. E folosit pentru calendare (zile fără vânzări), pentru date de test și pentru a genera combinații sau intervale.

generate_series este o funcție care întoarce un set de rânduri (set-returning function), deci o folosești în FROM ca pe un tabel.

1. Calendar complet pentru rapoarte, cu zilele fără vânzări:

SQL
SELECT z::date AS zi, count(c.id) AS comenzi
FROM generate_series(date '2025-03-01', date '2025-03-31', interval '1 day') AS z
LEFT JOIN comenzi c ON c.data = z::date
GROUP BY z
ORDER BY z;

2. Date de test în volum, ca să verifici un index sau un plan:

SQL
INSERT INTO clienti (nume, email)
SELECT 'Client ' || g, 'client' || g || '@exemplu.ro'
FROM generate_series(1, 100000) AS g;

3. Intervale orare pentru un sistem de programări:

SQL
SELECT t AS inceput, t + interval '30 minutes' AS sfarsit
FROM generate_series(timestamp '2025-03-14 09:00', timestamp '2025-03-14 17:00', interval '30 minutes') AS t;

Detalii: cu date și interval întoarce timestamp (sau timestamptz), deci convertești cu ::date. Pasul poate fi negativ pentru numărătoare inversă. Combinată cu WITH ORDINALITY, primești și numărul rândului. În alte baze de date același lucru cere un CTE recursiv sau un tabel calendar separat.

Ce urmărește intervievatorul: Se verifică familiaritatea cu instrumentele PostgreSQL pentru rapoarte și testare. Un candidat bun pomenește generarea de date de test pentru măsurarea performanței.

10 Mid

Ai un tip CREATE TYPE stare_comanda AS ENUM ('noua', 'platita', 'livrata'). Cum adaugi starea 'returnata'? Cum ștergi starea 'platita'? Ce restricții are ALTER TYPE ... ADD VALUE într-o tranzacție?

Răspuns scurt: Adaugi cu ALTER TYPE stare_comanda ADD VALUE 'returnata' AFTER 'livrata'. Ștergerea unei valori nu există direct: creezi un tip nou și migrezi coloana. Valoarea adăugată într-o tranzacție nu poate fi folosită până la COMMIT.

Adăugarea este simplă și rapidă (nu rescrie tabelele):

SQL
ALTER TYPE stare_comanda ADD VALUE 'returnata' AFTER 'livrata';
ALTER TYPE stare_comanda ADD VALUE IF NOT EXISTS 'anulata';
ALTER TYPE stare_comanda RENAME VALUE 'noua' TO 'creata';

Restricția de tranzacție: până în PostgreSQL 12, ADD VALUE nu putea rula deloc într-un bloc de tranzacție. De la 12 poate, dar valoarea nouă nu poate fi folosită în aceeași tranzacție; primești unsafe use of new value "returnata" of enum type stare_comanda cu indicația de a da COMMIT întâi. Contează în migrările care adaugă valoarea și imediat fac UPDATE cu ea: le împarți în două migrări.

Ștergerea unei valori nu e suportată. Pașii:

SQL
CREATE TYPE stare_comanda_v2 AS ENUM ('noua', 'livrata', 'returnata');
UPDATE comenzi SET stare = 'livrata' WHERE stare = 'platita';
ALTER TABLE comenzi ALTER COLUMN stare TYPE stare_comanda_v2 USING stare::text::stare_comanda_v2;
DROP TYPE stare_comanda;
ALTER TYPE stare_comanda_v2 RENAME TO stare_comanda;

ALTER COLUMN ... TYPE rescrie tabelul sub blocare exclusivă, deci pe tabele mari e o operație de planificat. De aceea, dacă lista de stări se schimbă des, un tabel de referință cu cheie străină sau o constrângere CHECK pe text sunt mai flexibile.

Ce urmărește intervievatorul: Se verifică experiența de întreținere a schemelor, nu doar crearea lor. Răspunsul slab ignoră restricția de tranzacție, care strică des migrările.

Eroarea, în practică:invalid input value for enumunsafe use of new value of enum type

11 Mid

Ce sunt tipurile range (daterange, tstzrange, int4range) și multirange din PostgreSQL? Ce înseamnă [2025-03-01,2025-04-01) și ce operatori folosești?

Răspuns scurt: Un range este un interval de valori într-o singură coloană, cu capete incluse [ sau excluse ). Operatorii principali sunt @> (conține), && (se suprapun), -|- (sunt adiacente), iar multirange-urile (PostgreSQL 14+) țin mai multe intervale disjuncte.

[2025-03-01,2025-04-01) înseamnă toate zilele de la 1 martie inclusiv până la 1 aprilie exclusiv, adică luna martie. Forma semi-deschisă este cea canonică pentru tipurile discrete (daterange, int4range): PostgreSQL transformă [2025-03-01,2025-03-31] în [2025-03-01,2025-04-01).

SQL
SELECT daterange('2025-03-01', '2025-04-01') @> date '2025-03-14';  -- true
SELECT tstzrange('2025-03-14 09:00+02', '2025-03-14 10:00+02')
    && tstzrange('2025-03-14 09:30+02', '2025-03-14 11:00+02');      -- true
SELECT lower(r), upper(r), upper_inc(r), isempty(r)
FROM (SELECT daterange('2025-03-01', '2025-04-01') AS r) t;

Avantaje față de două coloane inceput, sfarsit:

  • condițiile de suprapunere sunt simple și corecte (fără erori la capete);
  • se pot indexa cu GiST și SP-GiST;
  • permit constrângeri de excludere: „nu există două rezervări suprapuse”;
  • range_agg (14+) adună intervalele într-un multirange, iar * dă intersecția.

Multirange-urile, de exemplu datemultirange, reprezintă perioade cu goluri: zilele în care o cameră e ocupată, programul de lucru pe intervale. {[2025-03-01,2025-03-05), [2025-03-10,2025-03-12)} este o singură valoare.

Capcane: un range poate fi gol (empty) sau nemărginit ([2025-03-01,)), iar upper() dă NULL pentru capătul infinit.

Ce urmărește intervievatorul: Se verifică dacă folosești instrumentele native ale PostgreSQL pentru intervale. Un candidat mid știe de ce capătul de sus exclus evită suprapunerile la granițe.

12 Mid

Într-o bază de date creată cu colația C, ORDER BY nume afișează „Ștefan” după „Zoe”, iar „ana” după „Bogdan”. De ce? Cum obții ordinea alfabetică românească și ce sunt colațiile nedeterministe?

Răspuns scurt: Colația C compară octeții UTF-8: majusculele înaintea minusculelor, iar literele cu diacritice (coduri mai mari) la final. Pentru ordinea corectă folosești o colație ICU sau de limbă (COLLATE "ro-x-icu"), iar colațiile nedeterministe permit comparații fără majuscule sau diacritice.

Colația stabilește cum se compară și se sortează textele. Este aleasă la crearea bazei (initdb --locale, CREATE DATABASE ... LOCALE_PROVIDER icu ICU_LOCALE 'ro-RO') și poate fi schimbată pe coloană sau în interogare.

Cu C, ordinea este cea a codurilor: A-Z (65-90), apoi a-z (97-122), apoi caracterele non-ASCII, ca „Ș” (U+0218). De aici rezultatele ciudate. Containerele Docker și multe imagini cloud pornesc cu C sau C.UTF-8.

Rezolvări:

SQL
SELECT nume FROM clienti ORDER BY nume COLLATE "ro-x-icu";
ALTER TABLE clienti ALTER COLUMN nume TYPE text COLLATE "ro-x-icu";

Colațiile nedeterministe (PostgreSQL 12+, doar ICU) consideră egale texte care diferă ca octeți:

SQL
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE TABLE utilizatori (email text COLLATE ci UNIQUE);

Acum 'Ana@X.ro' = 'ana@x.ro' este adevărat, iar indexul unic refuză ambele variante. Limitări: LIKE pe colații nedeterministe e suportat abia din PostgreSQL 18, iar comparațiile sunt mai lente.

Un detaliu operațional important: schimbarea bibliotecii de colații (actualizarea glibc sau ICU pe server) poate schimba ordinea și strica indecșii pe text. PostgreSQL avertizează cu collation version mismatch, iar reparația este REINDEX.

Ce urmărește intervievatorul: Se verifică experiența cu date reale în limba română și cu medii de producție. Mențiunea despre actualizarea glibc separă candidații care au întreținut servere.

Eroarea, în practică:collation for encoding "UTF8" does not exist

13 Junior

Ce sunt $$ ... $$ (dollar quoting) și E'...' în PostgreSQL? Când folosești fiecare?

Răspuns scurt: $$...$$ delimitează un text fără să trebuiască să dublezi apostrofurile, folosit mai ales pentru corpul funcțiilor. E'...' este un text în care backslash-ul introduce secvențe speciale, ca \n sau \t.

Textele standard SQL se scriu între apostrofuri, iar un apostrof din interior se dublează: 'Ion''s'. În corpul unei funcții care conține la rândul ei texte, dublarea devine rapid ilizibilă.

Dollar quoting rezolvă problema: tot ce se află între $$ și $$ este text, exact cum e scris.

SQL
CREATE FUNCTION salut(nume text) RETURNS text LANGUAGE sql AS $$
  SELECT 'Bună, ' || nume || '! Azi e ' || to_char(current_date, 'DD.MM');
$$;

Dacă textul conține la rândul lui $$, folosești o etichetă: $corp$ ... $corp$, $sql$ ... $sql$. Etichetele se pot imbrica, util la SQL dinamic.

Escape strings E'...' interpretează backslash-ul:

SQL
SELECT E'Linia 1\nLinia 2', E'Tab:\taici', E'Apostrof: \'';

Într-un text obișnuit, cu standard_conforming_strings = on (implicit din PostgreSQL 9.1), backslash-ul este un caracter normal: 'C:\date' rămâne exact așa. În versiunile vechi, backslash-ul era special și în textele obișnuite, sursa multor bug-uri de import.

Pentru aplicații, regula de aur rămâne: nu construiești SQL din texte concatenate, ci trimiți valorile ca parametri. Atunci apostrofurile și backslash-urile nu mai contează.

Ce urmărește intervievatorul: Se verifică familiaritatea cu sintaxa folosită în funcții și migrări. Candidatul slab confundă dollar quoting cu parametrii $1, $2 ai interogărilor pregătite.

14 Senior

Pe 30 martie 2025, la ora 03:00, România trece la ora de vară (ceasul sare la 04:00). Ce întoarce SELECT timestamptz '2025-03-30 03:30 Europe/Bucharest'? Ce probleme apar la programarea joburilor și la rapoartele pe ore în jurul schimbării orei?

Răspuns scurt: Întoarce 2025-03-30 04:30:00+03: ora 03:30 nu există, iar PostgreSQL o interpretează după decalajul de dinaintea saltului și o mută după salt. Joburile programate în ora lipsă sau în ora dublă din octombrie pot rula de zero sau de două ori.

Rezultatul real este:

      timestamptz
------------------------
 2025-03-30 04:30:00+03

Ora locală 03:30 nu există în ziua aceea, iar PostgreSQL nu dă eroare: o interpretează ca 03:30 cu decalajul de iarnă (+02), adică momentul afișat în fusul de vară ca 04:30. În octombrie apare problema inversă: ora 03:30 există de două ori, iar PostgreSQL alege una dintre ele (cea cu decalajul de vară, conform regulii documentate).

Consecințe practice:

  • un job programat „zilnic la 03:30 ora locală” fie se mută, fie rulează de două ori;
  • un raport pe ore cu date_trunc('hour', ts AT TIME ZONE 'Europe/Bucharest') are o zi cu 23 de ore și una cu 25;
  • ts + interval '1 day' pe timestamptz păstrează ora de ceas (adaugă 23 sau 25 de ore la schimbare), iar ts + interval '24 hours' păstrează durata.

Recomandări:

  • stochezi momente în timestamptz și faci calculele de durată în UTC;
  • definești regulile de business („la miezul nopții ora României”) cu fusul explicit, AT TIME ZONE 'Europe/Bucharest', și le testezi pe datele de schimbare;
  • programezi joburile tehnice în UTC sau la ore care nu cad în intervalul 03:00–04:00;
  • în teste, setezi SET TIME ZONE explicit, nu te bazezi pe fusul serverului.

Ce urmărește intervievatorul: Se verifică profunzimea înțelegerii fusurilor orare, sursă de bug-uri care apar de două ori pe an. Candidatul senior știe diferența dintre 1 day și 24 hours.

Tabele, constrângeri și migrări de schemă

15 Junior

Ce este TOAST în PostgreSQL? De ce un SELECT * pe un tabel cu o coloană text sau jsonb foarte mare este mult mai lent decât un SELECT id, nume?

Răspuns scurt: TOAST este mecanismul prin care PostgreSQL comprimă și mută în afara rândului valorile mari (peste aproximativ 2 KB). SELECT * trebuie să le citească și să le decomprime din tabelul TOAST, în timp ce SELECT id, nume nu le atinge deloc.

O pagină de date PostgreSQL are 8 KB, iar un rând nu se poate întinde pe mai multe pagini. Pentru valorile mari, TOAST (The Oversized-Attribute Storage Technique) face două lucruri:

  • comprimă valoarea (implicit pglz, iar din PostgreSQL 14 se poate alege lz4, mai rapid);
  • dacă rândul rămâne prea mare, mută valoarea în tabelul TOAST asociat (pg_toast.pg_toast_<oid>), tăiată în bucăți, iar în rând păstrează doar un pointer.

Pragul implicit este aproximativ 2 KB pe rând (toast_tuple_target).

Consecințe practice:

  • SELECT * pe un tabel cu documente jsonb de 50 KB citește și decomprimă fiecare document, chiar dacă aplicația afișează doar o listă cu titluri; SELECT id, titlu e mult mai rapid;
  • un UPDATE pe altă coloană nu rescrie valoarea TOAST (pointerul se copiază), dar un UPDATE pe documentul mare îl rescrie complet;
  • pg_relation_size('tabel') nu include TOAST; folosești pg_total_relation_size sau pg_table_size.

Poți controla strategia pe coloană:

SQL
ALTER TABLE articole ALTER COLUMN continut SET STORAGE EXTERNAL;   -- fără compresie
ALTER TABLE articole ALTER COLUMN continut SET COMPRESSION lz4;
SELECT pg_column_size(continut) FROM articole LIMIT 5;

ORM-urile care încarcă implicit toate coloanele sunt o sursă frecventă de lentoare din cauza TOAST; soluția e proiecția explicită sau încărcarea leneșă a coloanei mari.

Ce urmărește intervievatorul: Se verifică înțelegerea stocării fizice și legătura cu performanța aplicației. Răspunsul slab: „SELECT * e lent pentru că aduce mai multe coloane”, fără mecanism.

16 Junior

Ce diferență este, pe un tabel cu 100 de milioane de rânduri, între ALTER TABLE comenzi ADD COLUMN sursa text DEFAULT 'web' și ALTER TABLE comenzi ADD COLUMN cod uuid DEFAULT gen_random_uuid()?

Răspuns scurt: Prima este instantanee în PostgreSQL 11+: valoarea implicită constantă se salvează o singură dată în catalog. A doua rescrie tot tabelul sub blocare exclusivă, pentru că o funcție volatilă trebuie calculată separat pentru fiecare rând.

Din PostgreSQL 11 există fast default: când adaugi o coloană cu o valoare implicită care nu depinde de rând (o constantă sau o expresie STABLE/IMMUTABLE evaluată o dată, cum e now() la momentul comenzii), PostgreSQL scrie valoarea în pg_attribute (attmissingval). Rândurile vechi nu se modifică: la citire, coloana lipsă primește valoarea din catalog. Operația durează milisecunde, chiar pe un tabel uriaș, și ia blocarea ACCESS EXCLUSIVE doar pentru un moment.

gen_random_uuid() este VOLATILE: fiecare rând trebuie să primească altă valoare. PostgreSQL rescrie tot tabelul (și toți indecșii), ținând blocarea exclusivă pe toată durata: nici citirile nu trec. Pe 100 de milioane de rânduri înseamnă minute sau ore de indisponibilitate.

Varianta sigură pentru valori volatile:

SQL
ALTER TABLE comenzi ADD COLUMN cod uuid;                       -- instant
ALTER TABLE comenzi ALTER COLUMN cod SET DEFAULT gen_random_uuid();  -- doar rândurile noi
-- completare în loturi, de exemplu câte 10.000 de id-uri, cu pauze:
UPDATE comenzi SET cod = gen_random_uuid() WHERE id BETWEEN 1 AND 10000 AND cod IS NULL;

La final, dacă vrei NOT NULL, adaugi întâi CHECK (cod IS NOT NULL) NOT VALID, îl validezi, apoi SET NOT NULL folosește constrângerea validată și nu mai scanează tabelul (PostgreSQL 12+).

Verifici volatilitatea unei funcții cu SELECT provolatile FROM pg_proc WHERE proname = 'gen_random_uuid' (v = volatile).

Ce urmărește intervievatorul: Se verifică experiența cu migrări pe tabele mari și cunoașterea fast default. Greșeala costisitoare: rularea directă a variantei volatile în producție.

17 Junior

Ce sunt coloanele generate în PostgreSQL? Care e diferența dintre STORED și VIRTUAL (PostgreSQL 18) și ce restricții au?

Răspuns scurt: O coloană generată este calculată automat din alte coloane ale aceluiași rând. STORED o calculează la scriere și o salvează pe disc, VIRTUAL (implicit din PostgreSQL 18) o calculează la citire. Expresia trebuie să fie imutabilă și nu poate citi alte tabele.

Exemplu:

SQL
CREATE TABLE linii_factura (
  cantitate integer NOT NULL,
  pret numeric(10,2) NOT NULL,
  valoare numeric(12,2) GENERATED ALWAYS AS (cantitate * pret) STORED,
  valoare_tva numeric(12,2) GENERATED ALWAYS AS (round(cantitate * pret * 0.19, 2)) VIRTUAL
);

STORED (PostgreSQL 12+): valoarea se calculează la INSERT și UPDATE și ocupă spațiu ca o coloană normală. Se poate indexa și citirea nu costă nimic în plus.

VIRTUAL (PostgreSQL 18, devine forma implicită când nu scrii nimic): nu ocupă spațiu, se calculează la fiecare citire. Nu poate fi indexată direct (poți indexa expresia) și nu poate folosi tipuri definite de utilizator.

Restricții comune:

  • expresia trebuie să fie IMMUTABLE: nu poți folosi now(), random(), subinterogări sau alte tabele; to_char(data, ...) pe timestamptz nu e imutabil, deci e refuzat;
  • nu poți scrie direct în coloană: INSERT ... VALUES (..., 10) dă cannot insert a non-DEFAULT value into column "valoare";
  • o coloană generată nu poate face parte din cheia de partiționare și nu poate depinde de altă coloană generată.

Când alegi coloane generate în loc de view sau calcul în aplicație: când valoarea derivată e folosită în filtre și indecși (o coloană tsvector pentru căutare full-text, un lower(email) pentru unicitate) și vrei garanția că nu se desincronizează niciodată.

Ce urmărește intervievatorul: Se verifică noutățile recente din PostgreSQL și înțelegerea imutabilității. Candidații confundă des coloanele generate cu valorile DEFAULT.

Eroarea, în practică:generation expression is not immutable

18 Junior

Ce este un DOMAIN în PostgreSQL și când este mai bun decât o constrângere CHECK repetată în fiecare tabel?

Răspuns scurt: Un domain este un tip de date bazat pe alt tip, cu constrângeri și valoare implicită atașate. Este util când aceeași regulă (email valid, sumă pozitivă, cod poștal) se aplică în multe tabele: o scrii o dată și o modifici într-un singur loc.

Exemplu:

SQL
CREATE DOMAIN suma_pozitiva AS numeric(12,2) CHECK (VALUE > 0);
CREATE DOMAIN cod_postal AS text CHECK (VALUE ~ '^[0-9]{6}$');

CREATE TABLE plati (id bigint GENERATED ALWAYS AS IDENTITY, suma suma_pozitiva NOT NULL);
CREATE TABLE adrese (id bigint GENERATED ALWAYS AS IDENTITY, cod cod_postal);

În verificare, VALUE este valoarea testată. O valoare invalidă dă value for domain suma_pozitiva violates check constraint "suma_pozitiva_check" (SQLSTATE 23514, la fel ca un CHECK obișnuit).

Avantaje:

  • regula este definită o singură dată, cu un nume care spune ce înseamnă valoarea;
  • funcțiile pot primi și întoarce domenii, deci regula se aplică și parametrilor;
  • ALTER DOMAIN ... ADD CONSTRAINT ... NOT VALID urmat de VALIDATE CONSTRAINT schimbă regula peste tot.

Limitări:

  • o constrângere de domeniu nu poate vedea alte coloane ale rândului (pentru „data_sfarsit > data_inceput” ai nevoie de CHECK pe tabel);
  • NOT NULL pe domeniu are capcane cu LEFT JOIN-urile și cu valorile implicite NULL; documentația recomandă NOT NULL pe coloană, nu pe domeniu;
  • unele ORM-uri și drivere văd tipul domeniului ca necunoscut și trebuie configurate să-l trateze ca tipul de bază.

Domeniile sunt și o formă de documentație: o coloană email email_valid spune mai mult decât text.

Ce urmărește intervievatorul: Se verifică dacă știi să folosești baza de date pentru reguli de integritate reutilizabile. Răspunsul slab nu menționează limitarea la o singură valoare.

Eroarea, în practică:value for domain violates check constraint

19 Mid

Trebuie să adaugi o cheie străină comenzi.client_id → clienti.id pe un tabel de producție cu 200 de milioane de comenzi. De ce ALTER TABLE ... ADD FOREIGN KEY poate bloca aplicația și cum o faci în doi pași?

Răspuns scurt: Comanda simplă verifică toate rândurile existente ținând blocări care opresc scrierile pe ambele tabele pe toată durata verificării. Varianta sigură: adaugi constrângerea cu NOT VALID (instantaneu, se aplică doar rândurilor noi), apoi rulezi VALIDATE CONSTRAINT, care verifică datele vechi cu o blocare mai slabă.

ADD FOREIGN KEY ia blocarea SHARE ROW EXCLUSIVE pe ambele tabele și scanează tot tabelul copil ca să verifice că fiecare client_id există. Pe 200 de milioane de rânduri, verificarea poate dura zeci de minute, timp în care INSERT, UPDATE și DELETE pe comenzi și clienti așteaptă.

Varianta în doi pași:

SQL
ALTER TABLE comenzi
  ADD CONSTRAINT fk_comenzi_client FOREIGN KEY (client_id) REFERENCES clienti (id) NOT VALID;

ALTER TABLE comenzi VALIDATE CONSTRAINT fk_comenzi_client;
  • Primul pas este aproape instantaneu: constrângerea există și este aplicată tuturor rândurilor noi sau modificate, dar datele vechi nu sunt verificate.
  • Al doilea pas scanează tabelul cu blocarea SHARE UPDATE EXCLUSIVE pe comenzi, care permite citiri și scrieri normale. Dacă găsește un rând invalid, eșuează, iar constrângerea rămâne NOT VALID (vezi convalidated = false în pg_constraint).

Același tipar funcționează pentru CHECK. Pentru NOT NULL, adaugi CHECK (coloana IS NOT NULL) NOT VALID, o validezi, apoi SET NOT NULL, care din PostgreSQL 12 folosește constrângerea validată și nu mai scanează.

Înainte de toate, setezi SET lock_timeout = '3s' în sesiunea de migrare: dacă primul pas nu obține blocarea repede (din cauza unei tranzacții lungi), renunță în loc să blocheze coada.

Ce urmărește intervievatorul: Se verifică experiența cu migrări fără întrerupere. Candidatul mid știe că NOT VALID nu înseamnă „neaplicată”, ci „nevalidată pentru datele vechi”.

Eroarea, în practică:insert or update violates foreign key constraint

20 Junior

Care este diferența dintre CREATE TABLE copie AS SELECT * FROM produse, CREATE TABLE copie (LIKE produse INCLUDING ALL) și SELECT * INTO copie FROM produse? Ce se copiază și ce nu?

Răspuns scurt: CREATE TABLE AS și SELECT INTO copiază doar coloanele și datele, fără chei, constrângeri, indecși sau valori implicite. LIKE ... INCLUDING ALL copiază structura completă (implicit, constrângeri, indecși, identity, comentarii), dar fără date.

CREATE TABLE copie AS SELECT ... creează tabelul din rezultatul interogării: coloanele au numele și tipurile rezultatului, iar datele sunt copiate. Nu se copiază cheia primară, NOT NULL, CHECK, cheile străine, indecșii sau DEFAULT. Cu WITH NO DATA primești doar structura de bază. Eticheta afișată este SELECT n.

SELECT * INTO copie FROM produse face același lucru, dar sintaxa e moștenită și intră în conflict cu SELECT ... INTO variabila din PL/pgSQL; documentația recomandă CREATE TABLE AS.

CREATE TABLE copie (LIKE produse INCLUDING ALL) copiază definiția: coloane, NOT NULL, DEFAULT, CHECK, indecși (inclusiv cheia primară), identity, coloane generate, statistici extinse, comentarii și setările de stocare. Nu copiază datele și nu copiază cheile străine. Poți alege ce incluzi: INCLUDING DEFAULTS INCLUDING INDEXES, sau INCLUDING ALL EXCLUDING INDEXES.

Folosire tipică:

SQL
CREATE TABLE comenzi_import (LIKE comenzi INCLUDING DEFAULTS);
-- încarci datele cu COPY, le validezi, apoi:
INSERT INTO comenzi SELECT * FROM comenzi_import;

Atenție la un detaliu: cu serial, INCLUDING DEFAULTS copiază nextval('produse_id_seq'), deci tabelul nou folosește aceeași secvență. Cu identity, copia primește propria secvență.

Ce urmărește intervievatorul: Se verifică precizia la copierea tabelelor pentru importuri și teste. Greșeala tipică: crezi că CREATE TABLE AS păstrează cheia primară.

21 Mid

Care dintre aceste operații sunt instantanee pe un tabel mare și care rescriu tot tabelul: redenumirea unei coloane, ștergerea unei coloane, schimbarea tipului din integer în bigint, mărirea unui varchar(50) la varchar(100), SET NOT NULL?

Răspuns scurt: Redenumirea, ștergerea coloanei și mărirea lui varchar sunt doar modificări de catalog, instantanee. integer → bigint rescrie tabelul și indecșii. SET NOT NULL nu rescrie, dar scanează tot tabelul, dacă nu există o constrângere CHECK validată care să o dovedească.

Toate iau blocarea ACCESS EXCLUSIVE, dar durata diferă enorm:

  • RENAME COLUMN: schimbă un nume în catalog, milisecunde. Riscul e în aplicație, care folosește încă vechiul nume.
  • DROP COLUMN: instantaneu. Coloana e doar marcată ca ștearsă (attisdropped); spațiul se recuperează pe măsură ce rândurile sunt rescrise sau la VACUUM FULL.
  • varchar(50) → varchar(100) sau → text: instantaneu, pentru că datele existente sunt garantat valide. Micșorarea la varchar(20) scanează tabelul.
  • integer → bigint: reprezentarea internă diferă (4 octeți față de 8), deci PostgreSQL rescrie fiecare rând și reconstruiește toți indecșii care conțin coloana. Pe un tabel mare: minute sau ore, cu aplicația blocată.
  • SET NOT NULL: scanează tabelul ca să verifice că nu există NULL-uri, fără să-l rescrie. Din PostgreSQL 12, sare peste scanare dacă există o constrângere CHECK (col IS NOT NULL) validată.
  • ADD COLUMN cu DEFAULT constant: instantaneu din PostgreSQL 11.

Pentru trecerea la bigint fără întrerupere, varianta uzuală: coloană nouă id_nou bigint, trigger care o ține sincronizată, completare în loturi, index creat CONCURRENTLY, apoi o tranzacție scurtă care schimbă numele și constrângerile.

Poți verifica dacă o comandă rescrie tabelul comparând relfilenode din pg_class înainte și după, pe o copie de test.

Ce urmărește intervievatorul: Se verifică intuiția despre costul real al migrărilor. Candidatul bun pomenește și blocarea, nu doar durata.

22 Junior

Încerci DROP TABLE clienti și primești cannot drop table clienti because other objects depend on it. Ce înseamnă, cum vezi ce depinde de tabel și când folosești CASCADE?

Răspuns scurt: Alte obiecte (chei străine din alte tabele, view-uri, funcții care îl referă prin tipul lui) depind de tabel, iar PostgreSQL refuză ștergerea implicită (RESTRICT). Mesajul DETAIL le listează. CASCADE le șterge și pe ele, deci îl folosești doar după ce ai citit lista.

PostgreSQL ține evidența dependențelor între obiecte în pg_depend. Comportamentul implicit al lui DROP este RESTRICT: dacă ceva depinde de obiect, comanda eșuează și îți spune ce:

ERROR:  cannot drop table clienti because other objects depend on it
DETAIL:  constraint comenzi_client_id_fkey on table comenzi depends on table clienti
view v_clienti_activi depends on table clienti
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Cu DROP TABLE clienti CASCADE:

  • view-ul v_clienti_activi este șters complet;
  • din tabelul comenzi se șterge doar constrângerea de cheie străină, nu tabelul și nici datele lui.

Riscul este că CASCADE poate șterge în lanț view-uri peste view-uri, folosite de rapoarte despre care nu știi. De aceea:

  • citești întâi lista din DETAIL (sau rulezi comanda într-o tranzacție, cu ROLLBACK, ca să vezi mesajele NOTICE: drop cascades to ...);
  • recreezi conștient obiectele dependente în aceeași migrare.

Situații înrudite: ALTER TABLE ... DROP COLUMN pe o coloană folosită într-un view dă aceeași eroare; schimbarea tipului unei coloane folosite de un view dă cannot alter type of a column used by a view or rule. În ambele cazuri, ștergi view-ul, modifici tabelul și recreezi view-ul în aceeași tranzacție, profitând de DDL-ul tranzacțional.

Ce urmărește intervievatorul: Se verifică prudența cu operațiile distructive și cunoașterea dependențelor. Răspunsul periculos: „pun CASCADE și merge”.

Eroarea, în practică:cannot drop table because other objects depend on it

23 Mid

După un import de date de test cu id-uri explicite (INSERT INTO produse (id, ...) VALUES (1, ...), (2, ...)), aplicația primește la fiecare produs nou duplicate key value violates unique constraint "produse_pkey". De ce, și cum repari?

Răspuns scurt: Secvența din spatele coloanei identity sau serial nu a fost folosită de import, deci a rămas la valoarea 1 și generează id-uri care există deja. O aduci la zi cu setval la maximul din tabel.

O secvență este un contor separat de tabel. nextval este apelat doar când INSERT-ul nu dă o valoare pentru id. Dacă importul a scris explicit id-urile 1…500 (posibil cu OVERRIDING SYSTEM VALUE la GENERATED ALWAYS, sau direct la BY DEFAULT și serial), secvența a rămas la 1. Primul produs nou primește id-ul 1, care există deja.

Reparația:

SQL
SELECT setval(pg_get_serial_sequence('produse', 'id'),
              coalesce((SELECT max(id) FROM produse), 0) + 1, false);
  • pg_get_serial_sequence găsește numele secvenței (merge și pentru identity);
  • al treilea argument false înseamnă că următorul nextval întoarce exact valoarea dată.

Pentru identity există și forma directă:

SQL
ALTER TABLE produse ALTER COLUMN id RESTART WITH 501;

Cum verifici: SELECT last_value, is_called FROM produse_id_seq; comparat cu SELECT max(id) FROM produse;.

Cum previi:

  • după orice import cu id-uri explicite (inclusiv pg_restore doar al datelor sau scripturi de seed), rulezi pasul de setval; pg_dump îl include automat;
  • folosești GENERATED ALWAYS, care obligă importurile să declare explicit OVERRIDING SYSTEM VALUE, deci nimeni nu suprascrie id-urile din greșeală;
  • în teste, golești tabelele cu TRUNCATE ... RESTART IDENTITY.

Ce urmărește intervievatorul: Se verifică o problemă foarte frecventă în proiecte reale. Candidatul bun recunoaște imediat cauza și știe pg_get_serial_sequence.

Eroarea, în practică:duplicate key value violates unique constraint

24 Mid

Ce sunt tabelele TEMPORARY și UNLOGGED în PostgreSQL? Când folosești fiecare și ce riști?

Răspuns scurt: Un tabel temporar există doar în sesiunea care l-a creat și dispare la deconectare sau la COMMIT. Un tabel UNLOGGED este permanent și comun, dar nu scrie în WAL: e rapid, se golește după o cădere și nu ajunge pe replici.

Tabele temporare:

SQL
CREATE TEMP TABLE import_tmp (LIKE produse INCLUDING DEFAULTS) ON COMMIT DROP;
  • vizibile doar în sesiunea curentă; alte sesiuni pot avea tabele temporare cu același nume;
  • stau în schema pg_temp_N, care e căutată prima în search_path, deci un tabel temporar „ascunde” un tabel permanent cu același nume;
  • nu scriu în WAL și folosesc temp_buffers;
  • autovacuum nu le procesează: dacă le folosești mult, rulezi singur ANALYZE pe ele, altfel planificatorul nu are statistici;
  • cu PgBouncer în modul transaction, tabelele temporare create fără ON COMMIT DROP pot rămâne în conexiunea folosită apoi de alt client.

Tabele UNLOGGED:

SQL
CREATE UNLOGGED TABLE cache_sesiuni (cheie text PRIMARY KEY, valoare jsonb, expira timestamptz);
  • scrierile sunt de câteva ori mai rapide, pentru că nu trec prin WAL;
  • după o oprire bruscă a serverului, tabelul este golit automat (nu se poate garanta consistența fără WAL);
  • nu sunt replicate fizic: pe replică apar goale și nu pot fi citite;
  • se pot transforma cu ALTER TABLE ... SET LOGGED, operație care rescrie tabelul.

Utilizări potrivite: tabele intermediare pentru ETL, cache-uri care pot fi reconstruite, date de sesiune. Nepotrivite: orice dată pe care nu ți-o permiți să o pierzi.

Ce urmărește intervievatorul: Se verifică alegerea instrumentului potrivit și înțelegerea WAL. Greșeala gravă: date de business în tabele UNLOGGED pentru viteză.

25 Junior

Scrie interogarea care listează coloanele tabelului produse cu tipul, dacă acceptă NULL și valoarea implicită. Când folosești information_schema și când pg_catalog?

Răspuns scurt: SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'produse' ORDER BY ordinal_position;. information_schema este standard și portabil, pg_catalog este specific PostgreSQL, mai complet și mai rapid.

Varianta standard:

SQL
SELECT column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'produse'
ORDER BY ordinal_position;

Varianta pe catalogul PostgreSQL:

SQL
SELECT a.attname AS coloana,
       format_type(a.atttypid, a.atttypmod) AS tip,
       a.attnotnull AS not_null,
       pg_get_expr(d.adbin, d.adrelid) AS implicit
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'public.produse'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Diferențe:

  • information_schema este definit de standardul SQL și există și în MySQL sau SQL Server, deci e bun pentru unelte portabile. Afișează doar obiectele pe care ai drepturi și nu cunoaște conceptele specifice PostgreSQL (tipul exact numeric(10,2) apare ca numeric plus coloane separate, array-urile ca ARRAY).
  • pg_catalog are tot: format_type dă tipul exact, attidentity/attgenerated arată identity și coloanele generate, pg_index, pg_constraint, pg_stat_* dau indecși, constrângeri și statistici. Interogările sunt mai rapide (vederile din information_schema sunt construite peste el).

Trucul 'public.produse'::regclass transformă numele în OID, iar atttypmod păstrează precizia și lungimea. Așa scrii verificări automate în teste de migrare: „coloana pret este numeric(10,2) și NOT NULL”.

Ce urmărește intervievatorul: Se verifică dacă poți explora o schemă din SQL, nu doar din interfețe grafice. Candidatul bun știe de ::regclass și format_type.

26 Senior

Partiționezi tabelul comenzi pe luni, după creat_la. Încerci PRIMARY KEY (id) și primești unique constraint on partitioned table must include all partitioning columns. De ce există regula și cum garantezi totuși unicitatea unui id sau a unui numar_comanda pe tot tabelul?

Răspuns scurt: Indecșii unici pe un tabel partiționat sunt locali fiecărei partiții, deci pot garanta unicitatea globală doar dacă includ cheia de partiționare. Folosești PRIMARY KEY (id, creat_la), iar pentru coduri strict unice un tabel-registru separat.

Nu există indecși globali în PostgreSQL. Fiecare partiție are propriul index, iar un index unic local poate verifica doar valorile din partiția lui. Dacă creat_la face parte din cheie, același (id, creat_la) nu poate apărea în două partiții (fiecare valoare de creat_la merge într-o singură partiție), deci unicitatea locală implică unicitatea globală. Fără creat_la, două partiții ar putea avea același id fără să afle una de alta.

Soluțiile uzuale:

SQL
CREATE TABLE comenzi (
  id bigint GENERATED ALWAYS AS IDENTITY,
  creat_la timestamptz NOT NULL,
  numar_comanda text NOT NULL,
  PRIMARY KEY (id, creat_la)
) PARTITION BY RANGE (creat_la);
  • id vine dintr-o singură secvență, deci este unic în practică, chiar dacă baza garantează formal doar perechea;
  • cheile străine către comenzi trebuie să includă și ele (id, creat_la), ceea ce complică modelul; de aceea mulți aleg ca tabelele copil să fie partiționate la fel;
  • pentru un cod care trebuie să fie strict unic global (numar_comanda), un tabel nepartiționat de „registru” (numere_comenzi(numar PRIMARY KEY, comanda_id, creat_la)) primește INSERT-ul în aceeași tranzacție;
  • alternativ, codul poate conține perioada (2025-03-000123), iar unicitatea pe (numar_comanda, creat_la) devine suficientă.

La interviu merită pomenite și costurile: interogările fără creat_la în WHERE ating toate partițiile, iar căutarea după id singur devine lentă; de aceea alegerea cheii de partiționare se face după tiparele reale de acces.

Ce urmărește intervievatorul: Se verifică înțelegerea arhitecturii partiționării, nu doar sintaxa. Un senior discută compromisurile pentru chei străine și căutări.

Eroarea, în practică:unique constraint must include partitioning columns

JSONB, jsonpath și căutare full-text

Exersează capitolul cu exerciții reale: Curs PostgreSQL online →
27 Junior

Care este diferența dintre tipurile json și jsonb în PostgreSQL? Ce afișează SELECT '{"a":1, "b":2, "a":3}'::json, '{"a":1, "b":2, "a":3}'::jsonb?

Răspuns scurt: json păstrează textul exact, cu spații, ordinea cheilor și cheile duplicate. jsonb îl transformă într-un format binar: elimină spațiile, sortează cheile și păstrează doar ultima valoare pentru o cheie duplicată. Exemplul afișează {"a":1, "b":2, "a":3} și {"a": 3, "b": 2}.

Ambele tipuri validează că textul este JSON corect, dar îl stochează diferit.

json:

  • salvează textul original, exact cum a venit;
  • fiecare operator (->, ->>) trebuie să parseze din nou tot textul, deci citirile sunt mai lente;
  • nu are operatori de egalitate și conținere și nu se poate indexa cu GIN.

jsonb:

  • este parsat o singură dată, la scriere, într-o structură binară;
  • cheile sunt ordonate (întâi după lungime, apoi alfabetic), spațiile dispar, iar la chei duplicate rămâne ultima valoare:
         json          |      jsonb
-----------------------+------------------
 {"a":1, "b":2, "a":3} | {"a": 3, "b": 2}
  • are operatorii @>, ?, ?|, ?&, @?, @@, egalitate și se poate indexa cu GIN;
  • numerele sunt păstrate ca numeric, deci 1.0 și 1.00 rămân distincte la afișare, dar sunt egale la comparare.

Când alegi json: rar, de exemplu când trebuie să păstrezi exact documentul primit (un webhook semnat, unde semnătura depinde de octeți) sau doar îl stochezi și îl dai mai departe. În rest, alegerea implicită este jsonb.

Ce urmărește intervievatorul: Se verifică dacă știi de ce jsonb e tipul folosit în practică. Detaliul cu cheile duplicate arată că ai lucrat efectiv cu el.

Eroarea, în practică:could not identify an equality operator for json

28 Junior

Coloana doc jsonb conține {"client": {"nume": "Ana", "oras": "Cluj"}, "total": 150}. Ce întorc doc -> 'client', doc ->> 'client', doc #>> '{client,oras}'? De ce doc ->> 'client' ->> 'oras' dă eroare?

Răspuns scurt: -> întoarce jsonb, ->> întoarce text, iar #>> urmează o cale și întoarce text: Cluj. Lanțul eșuează pentru că primul ->> dă deja text, iar pentru text ->> unknown nu există operator: operator does not exist: text ->> unknown.

Operatorii de bază pe jsonb:

  • doc -> 'client' întoarce {"nume": "Ana", "oras": "Cluj"} ca jsonb, deci poți continua navigarea;
  • doc ->> 'client' întoarce același obiect, dar ca text;
  • doc -> 'client' ->> 'oras' întoarce Cluj (text);
  • doc #> '{client,oras}' întoarce "Cluj" (jsonb, cu ghilimele), iar doc #>> '{client,oras}' întoarce Cluj (text);
  • pentru array-uri folosești indice numeric, numărat de la 0: doc -> 'linii' -> 0.

Regula de reținut: -> la toate nivelurile intermediare, ->> doar la final. Dacă pui ->> în mijloc, următorul operator primește text:

ERROR:  operator does not exist: text ->> unknown
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

O altă capcană: ->> dă text, deci comparațiile numerice cer conversie: (doc ->> 'total')::numeric > 100. Fără conversie, doc ->> 'total' > '100' compară texte, iar '99' > '100' este adevărat.

Din PostgreSQL 14 poți folosi și indexarea cu paranteze drepte: doc['client']['oras'], care întoarce jsonb și funcționează și în UPDATE ... SET doc['total'] = '200'.

O cheie inexistentă nu dă eroare, ci NULL.

Ce urmărește intervievatorul: Se verifică precizia cu tipurile operatorilor JSON, sursa celor mai frecvente erori la interogări pe jsonb.

Eroarea, în practică:operator does not exist: text ->> unknown

29 Mid

Ai un tabel evenimente(date jsonb) cu 10 milioane de rânduri. Ce index GIN creezi pentru WHERE date @> '{"tip": "plata"}' și ce diferență este între clasele jsonb_ops și jsonb_path_ops? Ce interogări nu pot folosi indexul?

Răspuns scurt: Pentru @>, USING gin (date jsonb_path_ops) e de obicei cel mai bun: mai mic și mai rapid. jsonb_ops (implicit) suportă în plus ?, ?|, ?&. Niciunul nu ajută la date ->> 'suma' > '100', unde trebuie un B-tree pe expresie.

Un index GIN pe jsonb desface fiecare document în elemente și ține, pentru fiecare element, lista rândurilor care îl conțin.

jsonb_ops (clasa implicită) indexează separat fiecare cheie și fiecare valoare. Suportă @>, ?, ?|, ?&, @? și @@. Dezavantaj: e mare, iar o căutare ca {"tip": "plata"} găsește întâi toate rândurile cu cheia tip și toate cu valoarea plata, apoi le intersectează.

jsonb_path_ops indexează un hash al fiecărei căi complete până la valoare (tip → plata). Suportă doar @>, @? și @@, dar indexul este de obicei de 2–3 ori mai mic și căutările sunt mai precise.

SQL
CREATE INDEX evenimente_date_gin ON evenimente USING gin (date jsonb_path_ops);
SELECT count(*) FROM evenimente WHERE date @> '{"tip": "plata"}';

Interogări care nu folosesc indexul GIN:

  • WHERE date ->> 'tip' = 'plata': operatorul este egalitate pe text, nu conținere. Ori o rescrii cu @>, ori creezi CREATE INDEX ON evenimente ((date ->> 'tip'));
  • comparații de interval: (date ->> 'suma')::numeric > 100 cere un index B-tree pe expresia exactă;
  • LIKE pe valori din document.

Cost de reținut: GIN este scump la scriere. Opțiunea fastupdate (implicită) adună inserările într-o listă de așteptare, ceea ce ajută la scriere, dar poate încetini unele citiri până la curățare.

Ce urmărește intervievatorul: Se verifică dacă știi să indexezi corect jsonb și să potrivești operatorul cu indexul, nu doar că „GIN e pentru JSON”.

30 Junior

Cum modifici o singură cheie dintr-o coloană jsonb? De ce UPDATE produse SET atribute = jsonb_set(atribute, '{culoare}', to_jsonb(culoare_noua)) a golit atributele unor produse?

Răspuns scurt: Folosești jsonb_set, operatorul || pentru îmbinare și - sau #- pentru ștergere. Atributele s-au pierdut pentru că jsonb_set este o funcție strictă: dacă culoare_noua este NULL, to_jsonb(NULL) e NULL, iar tot rezultatul devine NULL. Folosești jsonb_set_lax sau coalesce.

Operațiile de modificare:

SQL
UPDATE produse SET atribute = jsonb_set(atribute, '{dimensiuni,latime}', '42');
UPDATE produse SET atribute = atribute || '{"garantie": 24, "culoare": "negru"}';
UPDATE produse SET atribute = atribute - 'temporar';
UPDATE produse SET atribute = atribute #- '{dimensiuni,adancime}';
UPDATE produse SET atribute['stoc'] = '10';   -- PostgreSQL 14+
  • jsonb_set(doc, cale, valoare, create_missing) înlocuiește valoarea de la cale; cu create_missing = true (implicit) adaugă ultima cheie dacă lipsește, dar nu creează niveluri intermediare;
  • || îmbină două obiecte la primul nivel: cheile din dreapta le suprascriu pe cele din stânga; obiectele imbricate nu se combină recursiv;
  • - șterge o cheie, #- o cale.

Capcana NULL: jsonb_set este declarată STRICT. Dacă orice argument este SQL NULL, funcția întoarce NULL, iar UPDATE-ul suprascrie tot documentul cu NULL. Variante sigure:

SQL
SET atribute = jsonb_set_lax(atribute, '{culoare}', to_jsonb(culoare_noua));  -- valoarea devine null JSON
SET atribute = jsonb_set(atribute, '{culoare}', coalesce(to_jsonb(culoare_noua), 'null'));

Din punct de vedere fizic, orice modificare rescrie tot documentul (o nouă versiune a rândului, eventual și în TOAST). Pentru documente mari actualizate des, cheile modificate frecvent merită mutate în coloane obișnuite.

Ce urmărește intervievatorul: Se verifică experiența practică cu actualizări parțiale și cunoașterea comportamentului funcțiilor stricte, o cauză reală de pierdere de date.

Eroarea, în practică:cannot set path in scalar

31 Mid

Ce este SQL/JSON path (jsonpath) în PostgreSQL? Explică operatorii @? și @@, funcția jsonb_path_query și diferența dintre modurile lax și strict.

Răspuns scurt: jsonpath este un limbaj de căutare în documente JSON, cu filtre și expresii. @? întreabă dacă o cale găsește ceva, @@ evaluează o condiție, iar jsonb_path_query întoarce elementele găsite. În lax (implicit) cheile lipsă sunt ignorate, în strict produc erori.

Exemple pe un document de comandă:

SQL
SELECT doc @? '$.linii[*] ? (@.pret > 100)' FROM comenzi;              -- există o linie scumpă?
SELECT doc @@ '$.total > 500' FROM comenzi;                               -- condiție booleană
SELECT jsonb_path_query(doc, '$.linii[*] ? (@.cant >= 2).produs') FROM comenzi;
SELECT jsonb_path_query_array(doc, '$.linii[*].pret') FROM comenzi;
SELECT jsonb_path_exists(doc, '$.linii[*] ? (@.pret > $min)', '{"min": 100}') FROM comenzi;

Elementele limbajului: $ este documentul, .cheie navighează, [*] parcurge un array, ? (...) filtrează, @ este elementul curent, iar variabilele ($min) vin dintr-un al treilea argument jsonb, deci nu concatenezi valori în text.

lax (implicit):

  • o cheie lipsă întoarce un rezultat gol, fără eroare;
  • array-urile sunt desfăcute automat la nevoie.

strict: aceeași cheie lipsă dă o eroare:

SQL
SELECT jsonb_path_query('{"a": 1}', 'strict $.b');
-- ERROR:  JSON object does not contain key "b"

Strict este util la validarea structurii, lax la căutări pe date neuniforme. Cu silent => true funcțiile suprimă erorile.

Avantaj practic: @? și @@ pot folosi un index GIN pe coloană (ambele clase de operatori), deci filtrele complexe pe documente rămân rapide.

Ce urmărește intervievatorul: Se verifică dacă știi instrumentele moderne pentru JSON, introduse în PostgreSQL 12 și extinse ulterior.

Eroarea, în practică:syntax error in jsonpath input

32 Junior

Un endpoint trebuie să întoarcă lista de comenzi a unui client, fiecare cu liniile ei, ca un singur document JSON. Cum construiești răspunsul direct în PostgreSQL?

Răspuns scurt: Cu jsonb_build_object pentru obiecte și jsonb_agg pentru liste, eventual imbricate printr-o subinterogare corelată sau LATERAL. Baza de date întoarce un singur rând, cu un document gata de trimis, în loc de zeci de interogări din ORM.

Exemplu:

SQL
SELECT jsonb_build_object(
  'client', c.nume,
  'comenzi', coalesce(jsonb_agg(
     jsonb_build_object(
       'id', co.id,
       'data', co.data,
       'linii', (SELECT jsonb_agg(jsonb_build_object('produs', p.nume, 'cant', l.cantitate)
                                  ORDER BY p.nume)
                 FROM linii l JOIN produse p ON p.id = l.produs_id
                 WHERE l.comanda_id = co.id)
     ) ORDER BY co.data DESC) FILTER (WHERE co.id IS NOT NULL), '[]')
) AS raspuns
FROM clienti c
LEFT JOIN comenzi co ON co.client_id = c.id
WHERE c.id = 42
GROUP BY c.id, c.nume;

Detalii care contează:

  • jsonb_agg(... ORDER BY ...) controlează ordinea elementelor;
  • fără FILTER (WHERE co.id IS NOT NULL), un client fără comenzi primește [{"id": null, ...}];
  • jsonb_agg pe zero rânduri întoarce NULL, de aici coalesce(..., '[]');
  • to_jsonb(rand) sau row_to_json transformă un rând întreg în obiect, cu numele coloanelor drept chei;
  • json_build_object (fără b) păstrează ordinea cheilor dată de tine, util când contează pentru client.

Avantajele: un singur drum dus-întors la baza de date și fără problema N+1. Dezavantajul: logica de prezentare ajunge în SQL, iar interogările mari de acest tip sunt mai greu de testat și de versionat decât codul aplicației.

Ce urmărește intervievatorul: Se verifică dacă știi să folosești funcțiile JSON pentru API-uri și să eviți capcanele cu NULL și liste goale.

33 Mid

Ce aduce JSON_TABLE în PostgreSQL 17? Cum transformi un array JSON de linii de comandă într-un tabel relațional?

Răspuns scurt: JSON_TABLE (standard SQL/JSON) transformă un document JSON într-un set de rânduri cu coloane tipizate, folosind expresii jsonpath. Înlocuiește combinațiile de jsonb_array_elements și conversii manuale și vine împreună cu JSON_VALUE, JSON_QUERY și JSON_EXISTS.

Exemplu pe un document primit de la un API:

SQL
SELECT c.id, jt.*
FROM comenzi c,
     JSON_TABLE(c.doc, '$.linii[*]'
       COLUMNS (
         nr FOR ORDINALITY,
         produs text PATH '$.produs',
         cantitate int PATH '$.cant' DEFAULT 1 ON EMPTY,
         pret numeric(10,2) PATH '$.pret' NULL ON ERROR
       )) AS jt;

Ce primești:

  • fiecare element al array-ului devine un rând;
  • fiecare coloană are tipul declarat, cu reguli explicite pentru valori lipsă (ON EMPTY) și invalide (ON ERROR);
  • FOR ORDINALITY numerotează rândurile;
  • NESTED PATH desface array-uri din interiorul elementelor.

Varianta veche, încă foarte folosită:

SQL
SELECT c.id, l ->> 'produs', (l ->> 'cant')::int, (l ->> 'pret')::numeric
FROM comenzi c, jsonb_array_elements(c.doc -> 'linii') AS l;

Funcțiile scalare din același standard:

  • JSON_VALUE(doc, '$.total' RETURNING numeric) extrage o valoare scalară cu tip;
  • JSON_QUERY(doc, '$.client') extrage un obiect sau un array;
  • JSON_EXISTS(doc, '$.linii[*] ? (@.pret > 100)') testează o condiție.

Avantajul principal: sintaxa este standard (aceeași ca în Oracle sau DB2), iar tratarea erorilor de tip este declarativă, nu o succesiune de CASE și conversii care pică la prima valoare greșită.

Ce urmărește intervievatorul: Se verifică dacă ții pasul cu versiunile recente și dacă știi să transformi date semi-structurate în rânduri.

34 Junior

Documentele din produse.atribute au prețul uneori ca număr ("pret": 10.5), alteori ca text ("pret": "10.5"). Interogarea WHERE (atribute -> 'pret')::numeric > 10 pică pe unele rânduri. De ce și cum o faci robustă?

Răspuns scurt: Conversia directă din jsonb în numeric funcționează doar când valoarea JSON este număr; pentru un șir dă cannot cast jsonb string to type numeric. Folosești ->> (text) și apoi conversia, verifici tipul cu jsonb_typeof și, pe termen lung, normalizezi datele la scriere.

În PostgreSQL există două căi de conversie:

  • (atribute -> 'pret')::numeric convertește direct o valoare JSON. Merge doar dacă valoarea este număr JSON; pentru "10.5" (șir) primești cannot cast jsonb string to type numeric;
  • (atribute ->> 'pret')::numeric extrage întâi textul (10.5 în ambele cazuri), apoi îl convertește. Pică doar dacă textul nu este un număr valid ("zece"), cu invalid input syntax for type numeric.

Pentru date murdare, verifici înainte:

SQL
SELECT id, jsonb_typeof(atribute -> 'pret') AS tip, count(*)
FROM produse GROUP BY 1, 2;

SELECT * FROM produse
WHERE CASE WHEN jsonb_typeof(atribute -> 'pret') IN ('number', 'string')
                AND atribute ->> 'pret' ~ '^\d+(\.\d+)?$'
           THEN (atribute ->> 'pret')::numeric > 10 END;

CASE garantează ordinea evaluării; un AND simplu nu o garantează, iar conversia poate rula înainte de verificare. Din PostgreSQL 16 există și pg_input_is_valid(text, 'numeric').

Soluția durabilă este la scriere:

  • normalizezi documentele printr-un UPDATE (jsonb_set(atribute, '{pret}', to_jsonb((atribute ->> 'pret')::numeric)));
  • adaugi o constrângere CHECK (jsonb_typeof(atribute -> 'pret') = 'number');
  • dacă prețul e folosit des în filtre, îl muți într-o coloană numeric reală.

Ce urmărește intervievatorul: Se verifică experiența cu date JSON venite din surse diferite și înțelegerea conversiilor. Detaliul despre ordinea evaluării în AND arată maturitate.

Eroarea, în practică:cannot cast jsonb string to type integer

35 Junior

Cum implementezi căutarea full-text în articolele unui blog în română cu PostgreSQL? Explică tsvector, tsquery, configurația romanian și indexul potrivit.

Răspuns scurt: Transformi textul într-un tsvector (lista de rădăcini ale cuvintelor, fără cuvinte de legătură) cu to_tsvector('romanian', ...), transformi căutarea în tsquery cu websearch_to_tsquery('romanian', ...), le compari cu @@, sortezi cu ts_rank și indexezi cu GIN, de obicei pe o coloană generată.

Pași:

SQL
ALTER TABLE articole ADD COLUMN cautare tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('romanian', coalesce(titlu, '')), 'A') ||
    setweight(to_tsvector('romanian', coalesce(continut, '')), 'B')
  ) STORED;
CREATE INDEX articole_cautare_gin ON articole USING gin (cautare);

SELECT titlu, ts_rank(cautare, q) AS scor
FROM articole, websearch_to_tsquery('romanian', 'programare baze de date -mysql') AS q
WHERE cautare @@ q
ORDER BY scor DESC
LIMIT 10;

Ce se întâmplă:

  • to_tsvector('romanian', 'Programatorii programează') aplică un stemmer pentru română: formele flexionare ajung la aceeași rădăcină, iar cuvintele foarte comune („de”, „și”) sunt eliminate;
  • websearch_to_tsquery acceptă sintaxa de motor de căutare: ghilimele pentru fraze, - pentru excludere, or;
  • setweight dă titlului importanță mai mare în ts_rank;
  • ts_headline produce fragmente cu termenii evidențiați.

Limitări de știut:

  • configurația romanian nu elimină diacriticele: „ștergere” și „stergere” sunt termeni diferiți. Pentru asta construiești o configurație proprie cu dicționarul unaccent;
  • nu tolerează greșeli de tipar; pentru asta există pg_trgm;
  • pentru volume foarte mari sau cerințe de relevanță avansate, un motor dedicat (OpenSearch, Meilisearch) poate fi mai potrivit.

Coloana generată STORED evită recalcularea la fiecare interogare și ține vectorul sincronizat automat.

Ce urmărește intervievatorul: Se verifică dacă știi că PostgreSQL acoperă căutarea în text fără un sistem separat și dacă îi cunoști limitele pentru limba română.

Eroarea, în practică:text search configuration does not exist

36 Mid

Utilizatorii caută „stefan” și se așteaptă să găsească „Ștefan”. Încerci CREATE INDEX ON clienti (unaccent(lower(nume))) și primești functions in index expression must be marked IMMUTABLE. De ce, și cum rezolvi?

Răspuns scurt: unaccent(text) este declarată STABLE, pentru că rezultatul depinde de dicționarul configurat, care se poate schimba. Indexurile acceptă doar funcții IMMUTABLE. Soluția uzuală este o funcție proprie, marcată IMMUTABLE, care cheamă unaccent cu dicționarul fixat explicit.

Un index pe expresie stochează rezultatul calculat la scriere. Dacă funcția ar putea întoarce alt rezultat mai târziu pentru același argument, indexul ar deveni greșit fără ca cineva să afle. De aceea PostgreSQL acceptă în indecși doar funcții IMMUTABLE.

Varianta cu un singur argument, unaccent(text), caută dicționarul după search_path, deci este STABLE. Rezolvarea folosită pe scară largă:

SQL
CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE FUNCTION f_unaccent(t text) RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN public.unaccent('public.unaccent'::regdictionary, t);

CREATE INDEX clienti_nume_norm ON clienti (lower(f_unaccent(nume)));

SELECT * FROM clienti WHERE lower(f_unaccent(nume)) = lower(f_unaccent('stefan'));
  • dicționarul și schema sunt fixate, deci rezultatul nu mai depinde de setările sesiunii;
  • marcajul IMMUTABLE este o promisiune pe care o faci tu: dacă modifici fișierul de reguli al dicționarului, trebuie să rulezi REINDEX;
  • interogarea trebuie să folosească exact aceeași expresie ca indexul.

Pentru căutare parțială (LIKE '%stef%'), combini funcția cu un index trigram: CREATE INDEX ... USING gin (lower(f_unaccent(nume)) gin_trgm_ops).

Alternativa fără funcție proprie este o colație ICU nedeterministă care ignoră accentele (und-u-ks-level1), cu limitările ei pentru LIKE și performanță.

Aceeași eroare apare și la date(creat_la) pe timestamptz (rezultatul depinde de fusul orar al sesiunii) sau la to_char.

Ce urmărește intervievatorul: Se verifică înțelegerea volatilității funcțiilor și o problemă foarte concretă pentru aplicațiile în limba română.

Eroarea, în practică:functions in index expression must be IMMUTABLE

Indecși, planificator și EXPLAIN

37 Mid

Raportul zilnic filtrează WHERE creat_la::date = '2025-03-14' pe comenzi.creat_la timestamptz. Încerci CREATE INDEX ON comenzi ((creat_la::date)) și primești functions in index expression must be marked IMMUTABLE. De ce, și cum scrii filtrul ca să folosească indexul existent pe creat_la?

Răspuns scurt: Conversia timestamptz → date depinde de fusul orar al sesiunii (TimeZone), deci nu e imutabilă. Scrii filtrul ca interval semi-deschis pe coloana brută: creat_la >= '2025-03-14' AND creat_la < '2025-03-15', care folosește indexul B-tree obișnuit pe creat_la.

timestamptz stochează un moment absolut (în UTC). Ziua calendaristică a acelui moment depinde de fus: 2025-03-13 23:30 UTC este 14 martie la București și 13 martie la Londra. De aceea creat_la::date este STABLE, nu IMMUTABLE, iar un index pe ea ar da rezultate diferite în sesiuni cu fusuri diferite.

Mai mult, chiar și fără index nou, filtrul creat_la::date = ... nu poate folosi indexul pe creat_la, pentru că funcția se aplică pe coloană.

Varianta corectă este un interval pe coloana brută:

SQL
SELECT count(*) FROM comenzi
WHERE creat_la >= timestamptz '2025-03-14 00:00 Europe/Bucharest'
  AND creat_la <  timestamptz '2025-03-15 00:00 Europe/Bucharest';
  • intervalul semi-deschis (>= și <) nu pierde momentele de la 23:59:59.5 și nu dublează miezul nopții;
  • fusul explicit face raportul independent de setările sesiunii;
  • indexul B-tree pe creat_la este folosit direct.

Dacă chiar ai nevoie de un index pe zi (de exemplu pentru grupări frecvente), fixezi fusul în expresie, ceea ce o face imutabilă:

SQL
CREATE INDEX ON comenzi (((creat_la AT TIME ZONE 'Europe/Bucharest')::date));

iar interogarea trebuie să conțină exact aceeași expresie. Aceeași regulă se aplică la date_trunc('day', creat_la) pe timestamptz.

Ce urmărește intervievatorul: Se verifică dacă știi să scrii filtre „sargable” și dacă înțelegi legătura dintre fusuri orare și imutabilitate.

Eroarea, în practică:functions in index expression must be IMMUTABLE

38 Junior

Tabelul utilizatori are UNIQUE (email, tenant_id), iar tenant_id poate fi NULL pentru conturile globale. Se pot insera două conturi globale cu același email? Cum schimbi comportamentul în PostgreSQL 15+?

Răspuns scurt: Da: implicit, NULL-urile sunt considerate distincte într-o constrângere unică, deci ('ana@x.ro', NULL) poate apărea de mai multe ori. Din PostgreSQL 15 scrii UNIQUE NULLS NOT DISTINCT (email, tenant_id); în versiunile vechi, un index unic parțial pe email WHERE tenant_id IS NULL.

Standardul SQL spune că NULL înseamnă „necunoscut”, deci două valori NULL nu sunt egale. Constrângerile unice urmează regula: o pereche care conține NULL nu intră niciodată în conflict.

SQL
CREATE TABLE utilizatori (email text NOT NULL, tenant_id int, UNIQUE (email, tenant_id));
INSERT INTO utilizatori VALUES ('ana@x.ro', NULL), ('ana@x.ro', NULL);  -- reușește

Pentru regula de business „un email global apare o singură dată” ai două variante.

PostgreSQL 15+:

SQL
ALTER TABLE utilizatori
  ADD CONSTRAINT utilizatori_email_tenant_uq UNIQUE NULLS NOT DISTINCT (email, tenant_id);

Acum NULL-urile sunt tratate ca egale între ele, iar al doilea INSERT dă duplicate key value violates unique constraint.

Orice versiune, cu doi indecși:

SQL
CREATE UNIQUE INDEX ON utilizatori (email, tenant_id) WHERE tenant_id IS NOT NULL;
CREATE UNIQUE INDEX ON utilizatori (email) WHERE tenant_id IS NULL;

Varianta cu indecși parțiali este utilă și pentru alte reguli: „un singur cont activ per email” (WHERE sters_la IS NULL), „o singură adresă implicită per client” (WHERE implicita).

Atenție la ON CONFLICT: pentru a ținti un index parțial, repeți condiția: ON CONFLICT (email) WHERE tenant_id IS NULL DO NOTHING.

Ce urmărește intervievatorul: Se verifică înțelegerea NULL-urilor în constrângeri unice, o sursă tipică de duplicate în aplicații multi-tenant.

39 Mid

Ai rulat CREATE INDEX CONCURRENTLY pe un tabel mare, iar comanda a eșuat după 20 de minute cu o eroare de unicitate. Ce a rămas în baza de date, cum verifici și cum repari? De ce nu poți rula comanda într-o tranzacție?

Răspuns scurt: Rămâne un index INVALID, nefolosit la citiri, dar actualizat la fiecare scriere. Îl găsești cu indisvalid = false, îl ștergi cu DROP INDEX CONCURRENTLY și îl recreezi după corectarea datelor. Comanda folosește mai multe tranzacții interne, deci nu poate sta într-un bloc.

CREATE INDEX CONCURRENTLY construiește indexul fără să blocheze scrierile. Pentru asta lucrează în etape: creează intrarea în catalog, așteaptă terminarea tranzacțiilor existente, face o primă scanare, apoi o a doua pentru rândurile modificate între timp, și la fiecare pas așteaptă din nou tranzacțiile vechi. Fiecare etapă este o tranzacție separată, de aceea:

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

(Atenție la unelte de migrare care învelesc automat fiecare fișier în BEGIN/COMMIT; ele au de obicei o opțiune de dezactivare.)

Dacă o etapă eșuează (duplicate la un index unic, anulare, timeout), indexul rămâne:

SQL
SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

În \d comenzi apare cu sufixul INVALID. Un astfel de index:

  • nu este folosit de planificator;
  • este întreținut la INSERT și UPDATE, deci costă la scriere;
  • pentru un index unic, poate chiar refuza duplicate noi.

Repararea:

SQL
DROP INDEX CONCURRENTLY comenzi_cod_uq;
-- corectezi duplicatele, apoi:
CREATE UNIQUE INDEX CONCURRENTLY comenzi_cod_uq ON comenzi (cod);

Sau REINDEX INDEX CONCURRENTLY comenzi_cod_uq (PostgreSQL 12+), după corectarea datelor.

Costul variantei concurente: durează de 2–3 ori mai mult și așteaptă orice tranzacție lungă deschisă, deci o sesiune uitată „idle in transaction” o poate bloca la nesfârșit.

Ce urmărește intervievatorul: Se verifică experiența operațională cu indecși pe tabele de producție, inclusiv ce rămâne după un eșec.

Eroarea, în practică:CREATE INDEX CONCURRENTLY inside a transaction block

40 Senior

Ai indexul (tenant_id, creat_la) și interogarea WHERE creat_la > now() - interval '1 hour', fără tenant_id. Pe PostgreSQL 16 face scanare completă, pe 18 folosește indexul. Ce s-a schimbat și când contează ordinea coloanelor în continuare?

Răspuns scurt: PostgreSQL 18 a introdus skip scan pentru indecșii B-tree compuși: când prima coloană are puține valori distincte, indexul este parcurs separat pentru fiecare valoare a ei. Ordinea coloanelor rămâne importantă: skip scan e eficient doar când prima coloană are cardinalitate mică.

Regula clasică pentru un index B-tree (a, b): este eficient pentru filtre pe a sau pe a și b, iar un filtru doar pe b nu poate folosi structura arborelui, pentru că valorile lui b sunt sortate doar în interiorul fiecărui a. Planificatorul alegea o scanare completă a tabelului sau, uneori, a indexului întreg.

Skip scan (PostgreSQL 18): executorul ia prima valoare a lui tenant_id, caută în index creat_la > ... pentru ea, apoi sare direct la următoarea valoare de tenant_id și repetă. Dacă există 50 de tenanți, sunt 50 de căutări scurte în arbore, mult mai ieftine decât citirea a 100 de milioane de rânduri. În EXPLAIN apare o scanare de index cu condiția doar pe a doua coloană, iar Index Searches: 50 arată numărul de căutări.

Când contează în continuare ordinea:

  • dacă prima coloană are milioane de valori distincte (client_id), skip scan ar face milioane de căutări, deci planificatorul nu îl alege;
  • pentru ORDER BY creat_la LIMIT 20 fără filtru pe tenant, indexul nu dă rândurile gata sortate;
  • egalitățile se pun în față, iar coloana de interval sau de sortare la final: (tenant_id, stare, creat_la).

Practic: skip scan reduce nevoia de indecși suplimentari creați „pentru orice eventualitate”, dar nu înlocuiește proiectarea indecșilor după interogările frecvente. Verifici planul pe versiunea reală din producție, nu pe cea de pe laptop.

Ce urmărește intervievatorul: Se verifică dacă știi noutățile din PostgreSQL 18 și dacă înțelegi structura B-tree suficient cât să prezici când optimizarea ajută.

41 Mid

Planul arată Index Only Scan using comenzi_client_total on comenzi ... Heap Fetches: 48210 pentru o interogare care întoarce 50.000 de rânduri. Ce înseamnă Heap Fetches și cum îl reduci?

Răspuns scurt: Indexul nu știe dacă un rând e vizibil. Doar pentru paginile marcate „all-visible” în visibility map PostgreSQL sare peste tabel. 48.210 citiri din heap din 50.000 înseamnă o visibility map aproape goală: rulezi VACUUM și reglezi autovacuum.

Din cauza MVCC, un index B-tree conține intrări și pentru versiuni de rânduri moarte sau încă invizibile. Informația despre vizibilitate (xmin, xmax) există doar în rândul din tabel. Un index-only scan poate evita citirea tabelului doar pentru paginile marcate ca „all-visible” în visibility map, o structură mică (2 biți pe pagină) actualizată de VACUUM.

Heap Fetches: 48210 spune că aproape fiecare rând a necesitat totuși citirea tabelului, deci scanarea s-a comportat ca un index scan obișnuit, adesea chiar mai lent.

Cauze uzuale:

  • tabelul a primit recent multe scrieri (un import, actualizări masive), iar autovacuum nu a trecut încă;
  • un tabel cu inserări predominante nu declanșa autovacuum până în PostgreSQL 13; acum există autovacuum_vacuum_insert_threshold, dar pragurile implicite pot fi prea mari;
  • o tranzacție lungă împiedică marcarea paginilor ca vizibile.

Rezolvări:

SQL
VACUUM (ANALYZE) comenzi;
ALTER TABLE comenzi SET (autovacuum_vacuum_scale_factor = 0.02,
                         autovacuum_vacuum_insert_scale_factor = 0.02);
SELECT relallvisible, relpages FROM pg_class WHERE relname = 'comenzi';

Raportul relallvisible / relpages arată procentul de pagini vizibile, iar planificatorul îl folosește când estimează costul unui index-only scan.

Pentru ca planul să fie posibil, indexul trebuie să conțină toate coloanele cerute de interogare, eventual prin INCLUDE (total).

Ce urmărește intervievatorul: Se verifică legătura dintre indecși, MVCC și VACUUM, pe un detaliu din EXPLAIN pe care mulți îl ignoră.

42 Mid

Tabelul masuratori(senzor_id, ts, valoare) primește 50 de milioane de rânduri pe lună, doar prin inserare, în ordinea timpului. Indexul B-tree pe ts are 1,1 GB. Când este un index BRIN o alternativă bună și când nu?

Răspuns scurt: BRIN păstrează doar minimul și maximul pe grupuri de pagini, deci are câțiva KB. E excelent când valorile cresc odată cu poziția fizică, ca la jurnale inserate în ordine, și inutil pentru date amestecate sau căutări punctuale.

Un BRIN (Block Range INdex) nu indexează rânduri, ci intervale de pagini:

SQL
CREATE INDEX masuratori_ts_brin ON masuratori USING brin (ts) WITH (pages_per_range = 64);

Pentru fiecare grup de pagini păstrează valoarea minimă și maximă a lui ts. La WHERE ts BETWEEN ..., PostgreSQL sare peste toate grupurile al căror interval nu se suprapune cu filtrul și citește doar paginile rămase (Bitmap Heap Scan cu Rows Removed by Index Recheck).

Condiția esențială este corelația dintre valoare și poziția fizică:

SQL
SELECT attname, correlation FROM pg_stats WHERE tablename = 'masuratori';

O corelație aproape de 1 (sau -1) înseamnă BRIN eficient. Ea se strică dacă:

  • inserezi des date întârziate, cu ts vechi;
  • faci UPDATE-uri care mută rândurile în alte pagini;
  • ștergi date vechi, iar spațiul eliberat e refolosit de rânduri noi.

Când nu e potrivit:

  • căutări de câteva rânduri (WHERE id = 42): BRIN citește cel puțin un grup întreg de pagini;
  • coloane fără ordine fizică (senzor_id).

O combinație bună pentru serii de timp: BRIN pe ts pentru rapoarte pe intervale mari, partiționare lunară pentru ștergerea datelor vechi și un B-tree mic (senzor_id, ts) doar dacă interogările pe un singur senzor sunt frecvente. În versiunile noi există și clasa minmax_multi_ops, mai tolerantă la valori izolate în afara ordinii.

Ce urmărește intervievatorul: Se verifică dacă știi tipurile de index potrivite pentru volume mari și condiția fizică de care depind.

43 Senior

Interogarea WHERE judet = 'Cluj' AND oras = 'Cluj-Napoca' este estimată la 120 de rânduri, dar întoarce 48.000, iar planificatorul alege un nested loop dezastruos. De ce greșește estimarea și cum îl ajuți cu CREATE STATISTICS?

Răspuns scurt: Planificatorul presupune că filtrele pe coloane diferite sunt independente și înmulțește selectivitățile. Orașul determină județul, deci estimarea iese mult prea mică. CREATE STATISTICS ... (dependencies, mcv) ON judet, oras îi oferă statistici despre combinațiile de valori.

Pentru fiecare coloană, ANALYZE adună statistici separate: valorile cele mai frecvente, histograme, număr de valori distincte. La două condiții, selectivitatea combinată se calculează ca produs: dacă 4% din clienți sunt din județul Cluj și 3% din Cluj-Napoca, estimarea este 0,12%. În realitate, toți cei din Cluj-Napoca sunt și din județul Cluj, deci rezultatul corect este 3%: de 25 de ori mai mult.

O estimare mult prea mică are efecte în lanț: planificatorul alege nested loop cu index în loc de hash join, alocă prea puțină memorie, ordinea JOIN-urilor devine greșită.

Statistici extinse (PostgreSQL 10+, MCV din 12):

SQL
CREATE STATISTICS clienti_judet_oras (dependencies, ndistinct, mcv) ON judet, oras FROM clienti;
ANALYZE clienti;
SELECT * FROM pg_stats_ext WHERE statistics_name = 'clienti_judet_oras';
  • dependencies: cât de mult o coloană o determină pe cealaltă;
  • ndistinct: numărul de combinații distincte, util la GROUP BY judet, oras;
  • mcv: cele mai frecvente combinații, cu frecvența lor reală, cea mai precisă pentru egalități.

Din PostgreSQL 14 poți crea statistici și pe expresii (ON (lower(email))).

Cum diagnostichezi: în EXPLAIN (ANALYZE) compari rows= estimat cu actual rows la fiecare nod și cauți primul nod unde diferența e de ordinul zecilor sau sutelor. Alte cauze posibile: statistici vechi, default_statistics_target prea mic pentru distribuții neuniforme (îl crești pe coloană cu ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000), funcții sau expresii pe coloane.

Ce urmărește intervievatorul: Se verifică înțelegerea profundă a planificatorului. Un senior caută cauza estimării greșite, nu forțează planul cu setări globale.

44 Mid

Ai indecși separați pe comenzi(client_id) și comenzi(stare). Planul pentru WHERE client_id = 42 AND stare = 'noua' arată BitmapAnd cu două Bitmap Index Scan. Ce înseamnă și când merită un index compus în loc?

Răspuns scurt: PostgreSQL citește din fiecare index lista paginilor potrivite, sub formă de bitmap, le intersectează (BitmapAnd) și citește doar paginile comune. E o soluție flexibilă, dar mai scumpă decât un singur index compus (client_id, stare) când combinația apare des.

Un bitmap scan are două faze: 1. Bitmap Index Scan: parcurge indexul și construiește în memorie un bitmap al rândurilor (sau al paginilor, dacă bitmap-ul devine prea mare pentru work_mem); 2. Bitmap Heap Scan: citește paginile tabelului în ordine fizică, o singură dată fiecare, și reverifică condiția (Recheck Cond).

Cu doi indecși, PostgreSQL poate combina bitmap-urile: BitmapAnd pentru AND, BitmapOr pentru OR. Așa, doi indecși pe câte o coloană acoperă și interogări pe combinații.

Compromisuri:

  • fiecare index se parcurge integral pentru valoarea dată: dacă stare = 'noua' are 2 milioane de rânduri, bitmap-ul lui este mare, chiar dacă intersecția are 3 rânduri;
  • rezultatul nu este ordonat, deci ORDER BY ... LIMIT cere o sortare;
  • un bitmap „lossy” (doar la nivel de pagină) duce la reverificarea tuturor rândurilor din paginile respective.

Un index compus (client_id, stare) găsește direct cele 3 rânduri și poate livra rândurile sortate dacă adaugi coloana de ordonare: (client_id, stare, creat_la).

Regula practică: pentru combinațiile frecvente și critice, index compus; pentru filtre ad-hoc pe multe coloane (ecrane de căutare cu filtre opționale), indecși pe coloane individuale, combinați prin bitmap. Iar pentru un OR pe coloane diferite (client_id = 42 OR email = 'x'), bitmap-ul este adesea singura cale de a folosi indecșii fără să rescrii interogarea cu UNION.

Ce urmărește intervievatorul: Se verifică dacă știi să citești un tip de plan frecvent și să alegi între indecși simpli și compuși.

45 Junior

Pagina „Ultimele produse” rulează SELECT * FROM produse ORDER BY creat_la DESC NULLS LAST LIMIT 20 pe 5 milioane de rânduri. Ce index creezi ca interogarea să citească doar 20 de rânduri?

Răspuns scurt: Un index B-tree pe creat_la, cu ordinea compatibilă: CREATE INDEX ON produse (creat_la DESC NULLS LAST). Planul devine Limit → Index Scan, care se oprește după 20 de rânduri, în loc să sorteze tot tabelul.

Fără index, PostgreSQL citește toate cele 5 milioane de rânduri și face o sortare de tip „top-N heapsort” ca să păstreze primele 20. E mai ieftin decât o sortare completă, dar tot citește întregul tabel.

Cu un index B-tree potrivit, rândurile vin deja în ordinea cerută:

SQL
CREATE INDEX produse_creat_la_idx ON produse (creat_la DESC NULLS LAST);
EXPLAIN SELECT * FROM produse ORDER BY creat_la DESC NULLS LAST LIMIT 20;
-- Limit -> Index Scan using produse_creat_la_idx on produse

Despre direcție și NULL-uri:

  • un B-tree poate fi parcurs în ambele sensuri, deci indexul simplu (creat_la) (ASC NULLS LAST implicit) servește și ORDER BY creat_la DESC NULLS FIRST, adică inversul exact;
  • DESC NULLS LAST nu e inversul lui ASC NULLS LAST, deci are nevoie de un index declarat explicit așa, sau de o coloană NOT NULL, caz în care problema dispare;
  • implicit, în PostgreSQL, NULL-urile sunt considerate mai mari decât orice valoare: apar la final la ASC și la început la DESC.

Cu filtru, coloanele de egalitate vin primele: pentru WHERE categorie_id = 7 ORDER BY creat_la DESC LIMIT 20, indexul potrivit este (categorie_id, creat_la DESC).

Pentru paginile următoare, evită OFFSET 100000, care citește și aruncă rândurile anterioare; folosește paginarea după cheie: WHERE creat_la < :ultima_valoare ORDER BY creat_la DESC LIMIT 20.

Ce urmărește intervievatorul: Se verifică legătura dintre indecși și sortare, inclusiv detaliul NULLS LAST pe care îl ratează multe ORM-uri.

46 Junior

Baza de date de producție folosește colația ro_RO.UTF-8. Ai indexul CREATE INDEX ON produse (cod), dar WHERE cod LIKE 'ABC%' face scanare completă, deși pe laptopul tău (colație C) folosea indexul. De ce, și cum rezolvi?

Răspuns scurt: Într-o colație de limbă, ordinea din index nu corespunde ordinii octeților, deci un prefix nu se poate traduce într-un interval în index. Cu colația C se poate. Soluția: un index cu clasa de operatori text_pattern_ops sau o colație C pe coloană.

Pentru LIKE 'ABC%', PostgreSQL rescrie condiția intern ca interval: cod >= 'ABC' AND cod < 'ABD'. Transformarea este corectă doar dacă ordinea din index este ordinea octet cu octet, ca în colația C. În ro_RO.UTF-8, regulile de sortare lingvistice (ignorarea majusculelor la primul nivel, tratarea diacriticelor, a cratimelor) fac ca intervalul să nu mai conțină exact textele care încep cu ABC. Așa că planificatorul nu folosește indexul pentru LIKE.

Variante:

SQL
CREATE INDEX produse_cod_pattern ON produse (cod text_pattern_ops);
-- sau, dacă și sortarea poate fi binară:
ALTER TABLE produse ALTER COLUMN cod TYPE text COLLATE "C";
  • text_pattern_ops compară octet cu octet, deci servește LIKE 'ABC%' și ~ '^ABC', dar nu servește ORDER BY cod sau <, > obișnuite. Dacă ai nevoie de ambele, păstrezi doi indecși;
  • pentru coduri tehnice (SKU, coduri poștale, hash-uri), colația C pe coloană este alegerea naturală: e mai rapidă și ordinea lingvistică nu contează.

Lecția mai largă: mediile de dezvoltare cu altă colație decât producția produc planuri diferite. Rulezi testele de performanță cu aceeași colație și versiune ca în producție (SELECT datcollate, datlocprovider FROM pg_database).

Pentru LIKE '%ABC%' (cu wildcard la început), niciun B-tree nu ajută; acolo e nevoie de un index trigram GIN.

Ce urmărește intervievatorul: Se verifică o diferență subtilă între medii, des întâlnită. Candidatul bun leagă problema de colație, nu de „index stricat”.

47 Senior

O interogare din aplicația Java (prepared statement prin JDBC) este rapidă la primele 5 rulări, apoi devine de 100 de ori mai lentă pentru anumite valori ale parametrului. Ce se întâmplă cu planurile generice și personalizate și ce face plan_cache_mode?

Răspuns scurt: La primele cinci execuții ale unei instrucțiuni pregătite PostgreSQL face planuri personalizate, apoi poate trece la un plan generic, independent de parametri. Pentru valori foarte rare sau foarte frecvente, planul generic poate fi dezastruos. plan_cache_mode = force_custom_plan forțează replanificarea.

Mecanismul:

  • un PREPARE (sau un statement pregătit pe server de driver; JDBC face asta după prepareThreshold, implicit 5 utilizări) parsează interogarea o singură dată;
  • la primele 5 execuții, PostgreSQL construiește un plan personalizat, folosind valorile concrete ale parametrilor și statisticile lor;
  • apoi calculează un plan generic, fără valori. Dacă costul estimat al acestuia nu e semnificativ mai mare decât media planurilor personalizate, îl păstrează și îl refolosește, economisind timpul de planificare.

Problema apare la distribuții foarte neuniforme. Exemplu: WHERE stare = $1, unde 'livrata' înseamnă 95% din tabel, iar 'eroare' 0,01%. Planul generic presupune o selectivitate medie și alege, de exemplu, un index scan care e dezastruos pentru 'livrata'.

Cum confirmi:

SQL
PREPARE q(text) AS SELECT count(*) FROM comenzi WHERE stare = $1;
EXPLAIN EXECUTE q('livrata');   -- după 5 execuții, vezi $1 în plan: e generic

Rezolvări:

  • SET plan_cache_mode = force_custom_plan pentru sesiune, rol sau funcție (PostgreSQL 12+); costul este planificarea la fiecare execuție;
  • statistici mai bune pe coloană (SET STATISTICS);
  • interogări separate pentru cazurile extreme sau un index parțial pentru valoarea rară;
  • în JDBC, prepareThreshold=0 dezactivează pregătirea pe server, cu alte compromisuri.

Același fenomen apare în funcțiile PL/pgSQL, unde interogările statice sunt pregătite automat; acolo EXECUTE dinamic forțează replanificarea.

Ce urmărește intervievatorul: Se verifică experiența cu probleme de performanță greu de reprodus, care apar doar din aplicație. Candidatul senior știe de pragul de cinci execuții.

48 Mid

Un raport pe 30 de milioane de rânduri arată în plan Gather (Workers Planned: 2, Workers Launched: 0). Ce este execuția paralelă în PostgreSQL, de ce nu s-au pornit workerii și ce împiedică paralelismul?

Răspuns scurt: PostgreSQL poate împărți scanări, JOIN-uri și agregări între mai multe procese worker, iar Gather adună rezultatele. Workers Launched: 0 înseamnă că nu au fost procese disponibile în limita max_parallel_workers sau max_worker_processes. Paralelismul e blocat și de funcții PARALLEL UNSAFE, de scrieri și de cursoare.

La o interogare mare, planificatorul poate alege un plan paralel:

Finalize Aggregate
  -> Gather  (Workers Planned: 2, Workers Launched: 2)
        -> Partial Aggregate
              -> Parallel Seq Scan on vanzari

Fiecare worker scanează o parte din tabel și face o agregare parțială, iar procesul principal combină rezultatele.

Setări relevante:

  • max_parallel_workers_per_gather (implicit 2): câți workeri primește un nod Gather;
  • max_parallel_workers (implicit 8) și max_worker_processes: limitele pe tot serverul. Dacă alte interogări le-au ocupat, primești mai puțini workeri sau niciunul, iar interogarea rulează serial, fără eroare;
  • min_parallel_table_scan_size (8 MB): sub acest prag, tabelele nu sunt scanate paralel.

Ce împiedică un plan paralel:

  • funcții marcate PARALLEL UNSAFE în interogare; funcțiile tale sunt implicit unsafe dacă nu le marchezi PARALLEL SAFE;
  • interogări care modifică date (cu excepții precum CREATE TABLE AS sau CREATE MATERIALIZED VIEW);
  • execuția printr-un cursor sau un FOR în PL/pgSQL;
  • un nivel de izolare SERIALIZABLE în versiunile mai vechi;
  • LIMIT mic, unde pornirea workerilor nu se justifică.

Paralelismul ajută rapoartele, dar pe un server OLTP cu sute de conexiuni poate consuma toate nucleele. De aceea unii administratori îl reduc pentru rolul aplicației și îl lasă activ pentru rolul de raportare.

Ce urmărește intervievatorul: Se verifică înțelegerea execuției paralele și a limitelor ei, plus citirea atentă a planului.

49 Junior

Aplicația trimite lista de id-uri ale produselor din coș ca WHERE id IN (1, 2, 3, ..., 5000), construită prin concatenare. Ce probleme are abordarea și cum o rescrii cu = ANY?

Răspuns scurt: Textul interogării diferă la fiecare număr de elemente, deci nu poate fi pregătit și refolosit, devine foarte lung și, dacă e construit prin concatenare, deschide drumul injecțiilor SQL. Trimiți un singur parametru array: WHERE id = ANY($1::bigint[]), cu același plan pentru orice număr de elemente.

Probleme cu listele construite dinamic:

  • fiecare lungime de listă produce alt text SQL, deci cache-ul de instrucțiuni pregătite al driverului și pg_stat_statements se umplu de variante;
  • protocolul PostgreSQL și driverele limitează numărul de parametri dintr-o instrucțiune (zeci de mii, în funcție de driver), iar un IN cu valori parametrizate una câte una poate depăși limita;
  • concatenarea valorilor ca text este o vulnerabilitate de tip SQL injection dacă vreo valoare vine de la utilizator.

Varianta recomandată este un singur parametru de tip array:

SQL
SELECT id, nume, pret FROM produse WHERE id = ANY ($1::bigint[]);
  • în Java: conn.createArrayOf("bigint", ids); în Node.js (pg): pur și simplu un array JavaScript ca parametru;
  • planificatorul poate folosi indexul pe id la fel ca pentru IN;
  • textul interogării este mereu același.

Pentru liste foarte mari (zeci de mii de elemente) sau când ai nevoie și de alte câmpuri per element, desfaci array-ul într-un tabel și faci JOIN:

SQL
SELECT p.* FROM produse p
JOIN unnest($1::bigint[], $2::int[]) AS c(produs_id, cantitate) ON c.produs_id = p.id;

Atenție la negație: id <> ALL($1) este echivalentul lui NOT IN, iar dacă array-ul conține un NULL, rezultatul este NULL pentru toate rândurile, exact ca la NOT IN.

Ce urmărește intervievatorul: Se verifică practici bune în codul care vorbește cu PostgreSQL: parametri, planuri refolosite și securitate.

50 Junior

În EXPLAIN ANALYZE vezi linia Index Scan using linii_comanda_idx on linii_comanda (actual time=0.012..0.015 rows=4 loops=12000). Cât timp a petrecut interogarea în acest nod și câte rânduri a produs în total? Cum rulezi în siguranță EXPLAIN ANALYZE pe un DELETE?

Răspuns scurt: actual time și rows sunt medii pe o execuție, iar loops numără execuțiile: aproximativ 0,015 ms × 12.000 = 180 ms și 48.000 de rânduri. EXPLAIN ANALYZE execută comanda, deci un DELETE îl rulezi între BEGIN și ROLLBACK.

Într-un nested loop, nodul interior se execută o dată pentru fiecare rând din exterior. PostgreSQL raportează pentru fiecare nod:

  • actual time=pornire..final: milisecunde pe o execuție, în medie;
  • rows: rânduri pe o execuție, în medie (rotunjit);
  • loops: numărul de execuții.

Deci totalul este aproximativ final × loops = 0,015 × 12.000 ≈ 180 ms și rows × loops = 48.000 de rânduri. Mulți citesc doar 0.015 și concluzionează că nodul e neglijabil, când de fapt el e partea cea mai scumpă a interogării. Uneltele de vizualizare (explain.depesz.com, explain.dalibo.com) fac înmulțirea automat.

Când loops e foarte mare, întrebarea corectă este dacă planificatorul a estimat corect numărul de rânduri din partea exterioară. Dacă a estimat 50 și au venit 12.000, un hash join ar fi fost probabil mai bun.

EXPLAIN ANALYZE execută interogarea. Pentru comenzi care modifică date:

SQL
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM comenzi WHERE creat_la < '2020-01-01';
ROLLBACK;

Chiar și așa, comanda ia blocări pe rânduri cât durează tranzacția, declanșează triggerele (inclusiv cele care trimit notificări externe prin NOTIFY sau scriu în alte tabele) și consumă valori din secvențe, care nu revin la ROLLBACK. Pe producție, rulezi asemenea teste pe o replică promovată sau pe o copie.

Ce urmărește intervievatorul: Se verifică citirea corectă a planurilor reale, unde loops este capcana clasică, și prudența cu comenzi executate pentru măsurare.

MVCC, tranzacții și blocări

51 Junior

Ce afișează SELECT ctid, xmin, xmax, * FROM produse WHERE id = 7 înainte și după un UPDATE produse SET pret = pret + 1 WHERE id = 7? Ce spune asta despre felul în care PostgreSQL face UPDATE?

Răspuns scurt: ctid (poziția fizică) și xmin (tranzacția care a creat versiunea) se schimbă după UPDATE: PostgreSQL nu modifică rândul pe loc, ci scrie o versiune nouă și o marchează pe cea veche ca ștearsă (xmax). Versiunea veche rămâne pe disc până o curăță VACUUM.

Fiecare rând (tuple) din PostgreSQL are coloane de sistem ascunse:

  • ctid: adresa fizică, (pagina, poziția), de exemplu (0,7);
  • xmin: id-ul tranzacției care a inserat această versiune;
  • xmax: id-ul tranzacției care a șters-o sau a blocat-o (0 dacă e vie și neblocată).

Un UPDATE în PostgreSQL este de fapt DELETE + INSERT: 1. versiunea veche primește xmax = tranzacția curentă; 2. se scrie o versiune nouă, cu xmin = tranzacția curentă și alt ctid, de obicei (0,8) sau într-o altă pagină; 3. versiunea veche indică spre cea nouă, ca alte tranzacții să găsească varianta curentă.

Exemplu:

 ctid  | xmin | xmax | id | pret
-------+------+------+----+------
 (0,7) |  745 |    0 |  7 | 10.00   -- înainte
 (0,8) |  746 |    0 |  7 | 11.00   -- după

Consecințe practice:

  • tranzacțiile care au pornit înainte de UPDATE pot vedea în continuare versiunea veche (de aici citirile fără blocări);
  • fiecare UPDATE lasă un rând mort, deci tabelele modificate des cresc până trece VACUUM;
  • ctid nu este un identificator stabil: se schimbă la UPDATE și la VACUUM FULL; îl folosești doar în operații de moment, cum ar fi ștergerea duplicatelor;
  • dacă versiunea nouă încape în aceeași pagină și nu se modifică nicio coloană indexată, PostgreSQL face o actualizare HOT, fără intrări noi în indecși.

Ce urmărește intervievatorul: Se verifică înțelegerea concretă a MVCC, pe coloanele de sistem pe care le poți inspecta, nu doar teoria.

52 Mid

Două sesiuni, la nivelul READ COMMITTED, rulează în același timp UPDATE produse SET stoc = stoc - 1 WHERE id = 7 AND stoc > 0, iar stocul este 1. Ce se întâmplă cu a doua sesiune? Ar fi la fel dacă aplicația ar citi stocul cu SELECT și apoi ar scrie valoarea calculată?

Răspuns scurt: A doua sesiune așteaptă blocarea rândului. După COMMIT-ul primei, PostgreSQL reevaluează condiția pe versiunea nouă: stocul e 0, deci stoc > 0 e fals și UPDATE-ul nu modifică nimic (UPDATE 0). Varianta citește-apoi-scrie din aplicație pierde actualizarea și poate vinde produsul de două ori.

În READ COMMITTED, fiecare instrucțiune vede datele confirmate la momentul pornirii ei. Pentru UPDATE și DELETE există o regulă specială: 1. sesiunea B găsește rândul cu stoc = 1, dar acesta este blocat de A, deci așteaptă; 2. A face COMMIT cu stoc = 0; 3. B nu renunță și nu suprascrie orbește: recitește ultima versiune a rândului și reevaluează WHERE pe ea. Condiția stoc > 0 nu mai e adevărată, deci rândul este sărit.

Rezultatul: A primește UPDATE 1, B primește UPDATE 0, iar aplicația știe că stocul s-a epuizat. Fără erori și fără retry.

Varianta din aplicație:

A: SELECT stoc FROM produse WHERE id = 7;   -- 1
B: SELECT stoc FROM produse WHERE id = 7;   -- 1
A: UPDATE produse SET stoc = 0 WHERE id = 7;
B: UPDATE produse SET stoc = 0 WHERE id = 7;   -- după COMMIT-ul lui A

Ambele cred că au vândut ultimul produs: este anomalia lost update. Rezolvări: modifici relativ în SQL (stoc = stoc - 1 cu condiție), blochezi rândul la citire (SELECT ... FOR UPDATE), folosești o coloană de versiune (WHERE versiune = :v, blocare optimistă) sau treci la REPEATABLE READ, unde B primește eroare și reia.

Capcana mecanismului: reevaluarea se face doar pe rândul blocat. Dacă UPDATE-ul conține subinterogări pe alte tabele, acelea nu sunt recalculate, deci logica complexă poate da rezultate surprinzătoare.

Ce urmărește intervievatorul: Se verifică înțelegerea precisă a nivelului implicit de izolare și a tiparelor sigure pentru contoare și stocuri.

53 Mid

O tranzacție REPEATABLE READ primește ERROR: could not serialize access due to concurrent update (SQLSTATE 40001) la un UPDATE. Ce s-a întâmplat? Prin ce diferă REPEATABLE READ din PostgreSQL de definiția din standardul SQL?

Răspuns scurt: Altă tranzacție a modificat și confirmat rândul după ce tranzacția ta și-a luat snapshot-ul. În loc să reevalueze ca READ COMMITTED, PostgreSQL refuză scrierea, iar aplicația reia toată tranzacția. Spre deosebire de standard, REPEATABLE READ din PostgreSQL nu permite nici citiri fantomă.

În REPEATABLE READ, tranzacția lucrează pe o fotografie (snapshot) luată la prima instrucțiune. Toate citirile văd aceeași stare, indiferent ce confirmă alții între timp.

Problema apare la scriere: 1. T1 pornește și citește produsul 7; 2. T2 modifică produsul 7 și face COMMIT; 3. T1 încearcă UPDATE produse ... WHERE id = 7. Versiunea pe care o vede T1 nu mai este cea curentă. Dacă ar suprascrie-o, ar pierde modificarea lui T2 pe baza unor date vechi.

PostgreSQL alege siguranța:

ERROR:  could not serialize access due to concurrent update

Tranzacția devine invalidă: dai ROLLBACK și reiei totul, inclusiv citirile. Codul aplicației are nevoie de o buclă de reîncercare pentru SQLSTATE 40001, eventual cu o pauză aleatoare și un număr maxim de încercări.

Față de standard:

  • standardul permite citiri fantomă la REPEATABLE READ (rânduri noi care apar la a doua citire cu același filtru); implementarea PostgreSQL, bazată pe snapshot, nu le permite;
  • READ UNCOMMITTED este tratat ca READ COMMITTED: PostgreSQL nu arată niciodată date neconfirmate;
  • anomalia rămasă la REPEATABLE READ este write skew (două tranzacții citesc aceleași date și scriu rânduri diferite, încălcând împreună o regulă). Pentru ea există SERIALIZABLE.

Utilizare tipică: rapoarte care trebuie să fie consistente între mai multe interogări, sau pg_dump, care rulează exact într-o astfel de tranzacție.

Ce urmărește intervievatorul: Se verifică înțelegerea nivelurilor de izolare din implementarea reală și pregătirea aplicației pentru erori de serializare.

54 Senior

Echipa trece tranzacțiile de rezervare la SERIALIZABLE. Ce garanții primește, cum funcționează Serializable Snapshot Isolation în PostgreSQL și ce trebuie schimbat în aplicație și în indecși ca să nu apară o avalanșă de erori 40001?

Răspuns scurt: SERIALIZABLE garantează că rezultatul este identic cu o execuție a tranzacțiilor una după alta. PostgreSQL urmărește dependențele de citire-scriere și anulează o tranzacție când detectează un tipar periculos. Aplicația trebuie să reia automat tranzacțiile eșuate, să le țină scurte, iar indecșii potriviți reduc falsele conflicte.

SSI (din PostgreSQL 9.1) pornește de la snapshot-ul folosit la REPEATABLE READ și adaugă urmărirea a ce a citit fiecare tranzacție, prin blocări de predicat (SIReadLock în pg_locks). Acestea nu blochează pe nimeni, ci doar înregistrează citirile. Când o tranzacție scrie ceva ce alta a citit, apare o dependență. Dacă se formează o structură periculoasă (două dependențe consecutive într-un ciclu posibil), una dintre tranzacții primește:

ERROR:  could not serialize access due to read/write dependencies among transactions

Astfel se prind anomalii ca write skew: doi medici își anulează simultan garda, fiecare verificând că „mai este cineva de gardă”.

Ce schimbi:

  • reîncercare automată pentru SQLSTATE 40001 (și 40P01, deadlock), cu reluarea întregii tranzacții, nu doar a ultimei instrucțiuni;
  • tranzacții scurte: cu cât stau deschise mai mult, cu atât cresc șansele de conflict;
  • indecși pe coloanele filtrate: fără index, o scanare secvențială blochează la nivel de tabel întreg și orice scriere în tabel pare conflict. Cu index, blocările de predicat sunt la nivel de pagină de index;
  • tranzacțiile doar de citire le declari READ ONLY; pentru rapoarte lungi, SERIALIZABLE READ ONLY DEFERRABLE așteaptă un snapshot sigur și apoi nu mai poate fi anulat;
  • parametrii max_pred_locks_per_transaction și max_pred_locks_per_relation controlează când blocările se agregă la nivel de pagină sau relație, ceea ce crește falsele pozitive.

Toate tranzacțiile implicate trebuie să ruleze la SERIALIZABLE; una la READ COMMITTED nu este protejată. Avantajul major: corectitudinea nu mai depinde de analiza manuală a fiecărui tipar de concurență.

Ce urmărește intervievatorul: Se verifică stăpânirea celui mai strict nivel de izolare și a costurilor lui operaționale. Diferențiază seniorii care au rulat SSI în producție.

55 Mid

Care este diferența dintre SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE și FOR KEY SHARE? De ce un UPDATE comenzi SET stare = 'platita' nu blochează inserarea de linii noi pentru acea comandă, deși linii_comanda are cheie străină spre comenzi?

Răspuns scurt: Sunt patru moduri de blocare a rândurilor, de la cel mai puternic la cel mai slab. Verificarea cheii străine ia FOR KEY SHARE, iar un UPDATE care nu atinge cheia ia FOR NO KEY UPDATE; nu se ciocnesc, deci inserările în tabelul copil continuă.

Modurile de blocare la nivel de rând:

  • FOR UPDATE: blocare exclusivă completă; este luată și de DELETE și de UPDATE-urile care modifică coloane din chei unice. Intră în conflict cu toate celelalte moduri;
  • FOR NO KEY UPDATE: luată de UPDATE-urile obișnuite, care nu ating cheile. Nu intră în conflict cu FOR KEY SHARE;
  • FOR SHARE: blocare partajată; împiedică orice modificare a rândului, dar permite altor sesiuni să-l blocheze tot partajat;
  • FOR KEY SHARE: cea mai slabă; împiedică doar ștergerea rândului și modificarea cheii. Este luată automat de verificările de chei străine.

Tabelul de conflicte pe scurt: KEY SHARE se ciocnește doar cu FOR UPDATE; SHARE cu NO KEY UPDATE și UPDATE; NO KEY UPDATE cu SHARE, NO KEY UPDATE și UPDATE.

În exemplu: inserarea în linii_comanda verifică existența comenzii cu FOR KEY SHARE pe rândul din comenzi. UPDATE-ul pe stare ia FOR NO KEY UPDATE. Nu există conflict, deci plata și adăugarea de linii merg în paralel. Înainte de PostgreSQL 9.3, verificarea FK lua FOR SHARE, iar asemenea scenarii produceau așteptări și deadlock-uri.

Practic:

  • în aplicație, pentru „citesc și apoi modific”, FOR NO KEY UPDATE este adesea suficient și reduce conflictele cu inserările în tabelele copil;
  • modificarea unei chei primare referite (sau ștergerea părintelui) rămâne operația care blochează copiii;
  • NOWAIT dă eroare imediat dacă rândul e blocat, iar SKIP LOCKED sare peste rândurile blocate.

Ce urmărește intervievatorul: Se verifică înțelegerea fină a blocărilor pe rânduri, importantă pentru diagnosticarea așteptărilor din aplicații cu chei străine.

Eroarea, în practică:FOR UPDATE is not allowed with aggregate functions

56 Mid

Aplicația „stă”: cererile către tabelul comenzi nu se mai termină. Ce interogare rulezi ca să afli cine blochează pe cine și ce faci apoi?

Răspuns scurt: Combini pg_stat_activity cu pg_blocking_pids(pid): vezi sesiunile care așteaptă, interogarea lor și sesiunea care le blochează, cu starea ei (de multe ori idle in transaction). Apoi oprești sesiunea vinovată cu pg_cancel_backend sau pg_terminate_backend și repari cauza din aplicație.

Interogarea de diagnostic:

SQL
SELECT a.pid, a.usename, a.state, a.wait_event_type, a.wait_event,
       now() - a.xact_start AS durata_tx,
       pg_blocking_pids(a.pid) AS blocat_de,
       left(a.query, 80) AS interogare
FROM pg_stat_activity a
WHERE a.datname = current_database() AND a.pid <> pg_backend_pid()
ORDER BY a.xact_start;

Ce cauți:

  • sesiuni cu wait_event_type = 'Lock' și blocat_de nevid: ele așteaptă;
  • la capătul lanțului, sesiunea care blochează. Foarte des are state = 'idle in transaction': aplicația a deschis o tranzacție, a modificat un rând și nu a mai dat COMMIT (o excepție netratată, un apel HTTP lent în mijlocul tranzacției);
  • coloana query arată ultima interogare a sesiunii, nu neapărat pe cea care a luat blocarea.

Detalii din pg_locks, pentru blocări pe tabele:

SQL
SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE NOT granted;

Acțiuni:

  • SELECT pg_cancel_backend(pid) anulează interogarea curentă; nu ajută la o sesiune idle in transaction;
  • SELECT pg_terminate_backend(pid) închide conexiunea, iar tranzacția ei face ROLLBACK.

Prevenire:

  • idle_in_transaction_session_timeout închide automat sesiunile uitate în tranzacție;
  • lock_timeout pentru migrări și operații care pot aștepta;
  • log_lock_waits = on scrie în log așteptările mai lungi de deadlock_timeout;
  • în aplicație, nicio operație externă (email, HTTP) în interiorul unei tranzacții.

Ce urmărește intervievatorul: Se verifică abilitatea de a diagnostica rapid o blocare în producție, una dintre cele mai frecvente situații de urgență.

57 Junior

Care este diferența dintre statement_timeout, lock_timeout, idle_in_transaction_session_timeout și transaction_timeout? Cum le setezi pentru rolul aplicației?

Răspuns scurt: statement_timeout limitează durata unei instrucțiuni, lock_timeout cât așteaptă o blocare, idle_in_transaction_session_timeout cât poate sta o sesiune inactivă cu tranzacția deschisă, iar transaction_timeout (PostgreSQL 17) durata totală a unei tranzacții. Le setezi cu ALTER ROLE aplicatie SET ....

Ce face fiecare:

  • statement_timeout: orice instrucțiune care depășește limita este anulată cu canceling statement due to statement timeout (SQLSTATE 57014). Protejează împotriva interogărilor scăpate de sub control;
  • lock_timeout: dacă o instrucțiune așteaptă o blocare mai mult decât limita, primește canceling statement due to lock timeout (55P03). Esențial în migrări: un ALTER TABLE care așteaptă în spatele unei tranzacții lungi blochează la rândul lui toate interogările venite după el;
  • idle_in_transaction_session_timeout: închide conexiunea dacă stă în tranzacție fără să trimită nimic. Eliberează blocările și permite VACUUM-ului să curețe;
  • transaction_timeout (PostgreSQL 17+): limitează durata întregii tranzacții, indiferent dacă e activă sau nu;
  • idle_session_timeout (14+): închide conexiunile inactive și fără tranzacție; atenție la pool-urile de conexiuni, care le țin deschise intenționat.

Setare pe niveluri:

SQL
ALTER ROLE aplicatie SET statement_timeout = '5s';
ALTER ROLE aplicatie SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE raportare SET statement_timeout = '10min';

-- într-o migrare:
SET lock_timeout = '3s';
SET statement_timeout = '15min';
ALTER TABLE comenzi ADD COLUMN sursa text;

Setările pe rol se aplică la conexiunile noi. Pe server le poți seta global, dar o limită strictă globală poate opri și operații administrative legitime (backup logic, VACUUM manual), de aceea preferi limitele pe rol.

Valorile se aleg din măsurători: un statement_timeout puțin peste cea mai lentă interogare legitimă a aplicației.

Ce urmărește intervievatorul: Se verifică instrumentele de protecție a bazei de date împotriva sesiunilor și interogărilor problematice.

58 Junior

Într-o tranzacție, o instrucțiune dă eroare, iar toate cele care urmează primesc current transaction is aborted, commands ignored until end of transaction block. De ce se comportă PostgreSQL așa și cum continui după o eroare așteptată?

Răspuns scurt: În PostgreSQL, o eroare face tranzacția întreagă invalidă: nimic nu mai rulează până la ROLLBACK. Ca să continui după o eroare, o izolezi într-un SAVEPOINT și faci ROLLBACK TO SAVEPOINT, sau eviți eroarea cu ON CONFLICT ori verificări prealabile.

Alte sisteme (MySQL, SQL Server în unele moduri) anulează doar instrucțiunea care a eșuat și lasă tranzacția să continue. PostgreSQL consideră că, după o eroare, starea tranzacției nu mai e de încredere, deci o marchează ca abandonată (SQLSTATE 25P02):

ERROR:  duplicate key value violates unique constraint "clienti_email_key"
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Chiar și un COMMIT dat în această stare face de fapt ROLLBACK (psql afișează ROLLBACK).

Variante:

SQL
BEGIN;
INSERT INTO comenzi ... ;
SAVEPOINT inainte_client;
INSERT INTO clienti (email) VALUES ('ana@x.ro');      -- poate eșua
ROLLBACK TO SAVEPOINT inainte_client;               -- doar dacă a eșuat
UPDATE comenzi SET ... ;
COMMIT;
  • un savepoint este o subtranzacție: anularea ei păstrează tot ce s-a făcut înainte;
  • în psql, \set ON_ERROR_ROLLBACK interactive creează automat un savepoint înaintea fiecărei comenzi, comod la lucrul manual;
  • unele drivere au opțiuni similare (pgjdbc autosave=conservative), dar un savepoint la fiecare instrucțiune costă;
  • pentru cazul cel mai des întâlnit, duplicatele, INSERT ... ON CONFLICT DO NOTHING evită eroarea complet.

În aplicații, mesajul 25P02 în loguri înseamnă aproape mereu că adevărata eroare a apărut mai devreme și a fost înghițită de un catch. Cauți prima eroare din tranzacție, nu pe ultima.

Ce urmărește intervievatorul: Se verifică o diferență de comportament care surprinde mulți programatori veniți din alte baze de date, plus abilitatea de a citi corect logurile.

Eroarea, în practică:current transaction is aborted, commands ignoredsavepoint does not exist

59 Mid

PostgreSQL are DDL tranzacțional. Ce înseamnă asta pentru migrări și care comenzi nu pot rula într-un bloc BEGIN ... COMMIT?

Răspuns scurt: CREATE TABLE, ALTER TABLE, DROP și majoritatea comenzilor de schemă pot fi anulate cu ROLLBACK, deci o migrare eșuată la jumătate nu lasă schema pe jumătate modificată. Excepții: CREATE INDEX CONCURRENTLY, VACUUM, CREATE DATABASE, ALTER SYSTEM, REINDEX CONCURRENTLY și alte câteva.

Exemplu:

SQL
BEGIN;
ALTER TABLE comenzi ADD COLUMN sursa text;
CREATE TABLE comenzi_surse (cod text PRIMARY KEY);
ALTER TABLE comenzi ADD CONSTRAINT fk_sursa FOREIGN KEY (sursa) REFERENCES comenzi_surse (cod);
-- o eroare aici
ROLLBACK;   -- coloana și tabelul nu există

În MySQL, fiecare instrucțiune DDL face COMMIT implicit, deci o migrare eșuată lasă schema într-o stare intermediară, reparată manual. În PostgreSQL, uneltele de migrare (Flyway, Liquibase, Alembic, Prisma) rulează de obicei fiecare migrare într-o tranzacție.

Comenzi care nu pot rula în bloc de tranzacție:

  • CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX ... CONCURRENTLY, DETACH PARTITION ... CONCURRENTLY;
  • VACUUM (dar ANALYZE poate);
  • CREATE DATABASE, DROP DATABASE, CREATE TABLESPACE;
  • ALTER SYSTEM.

Mesajul este de tipul CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Aceste comenzi trebuie puse în migrări separate, marcate să ruleze fără tranzacție.

Capcana operațională: DDL-ul ia blocări puternice (ACCESS EXCLUSIVE la majoritatea ALTER TABLE) care se păstrează până la sfârșitul tranzacției. O migrare care face un ALTER TABLE rapid și apoi un UPDATE de 10 minute în aceeași tranzacție ține tabelul blocat complet 10 minute. De aceea migrările pe tabele mari se împart în pași mici, fiecare cu lock_timeout.

Ce urmărește intervievatorul: Se verifică înțelegerea unui avantaj important al PostgreSQL și a limitelor lui practice în migrări.

Eroarea, în practică:CREATE INDEX CONCURRENTLY inside a transaction blockVACUUM cannot run inside a transaction block

60 Junior

Într-o tranzacție lungă, apelezi now() la început și după 30 de secunde. Ce valori primești? Care este diferența dintre now(), statement_timestamp() și clock_timestamp()?

Răspuns scurt: now() întoarce aceeași valoare pe toată tranzacția: momentul în care a început. statement_timestamp() dă începutul instrucțiunii curente, iar clock_timestamp() ora reală, care se schimbă chiar în interiorul aceleiași instrucțiuni.

Funcțiile de timp:

  • now(), current_timestamp și transaction_timestamp(): momentul începerii tranzacției. Rămân constante până la COMMIT;
  • statement_timestamp(): momentul în care serverul a primit instrucțiunea curentă;
  • clock_timestamp(): ora reală, la fiecare apel. Într-un INSERT ... SELECT cu un milion de rânduri, fiecare rând primește altă valoare;
  • current_date și localtimestamp: variante fără fus sau doar data, tot la începutul tranzacției.

Exemplu:

SQL
BEGIN;
SELECT now(), clock_timestamp();     -- 10:30:00.000 | 10:30:00.001
-- 30 de secunde mai târziu
SELECT now(), clock_timestamp();     -- 10:30:00.000 | 10:30:30.120
COMMIT;

De ce e util ca now() să fie constant: toate rândurile scrise în aceeași tranzacție (comanda, liniile, plata) au exact același moment de creare, deci pot fi corelate ușor.

Când ai nevoie de altceva:

  • măsurarea duratei unei operații într-o funcție PL/pgSQL: clock_timestamp();
  • jurnale de audit într-o tranzacție lungă de import, unde vrei momentul real al fiecărui pas: clock_timestamp();
  • DEFAULT now() în coloane: momentul tranzacției, de obicei ce vrei.

Capcană: o procedură care face COMMIT între pași pornește o tranzacție nouă, deci now() se schimbă după fiecare COMMIT. Iar în teste, valoarea constantă a lui now() poate ascunde bug-uri de ordonare când toate rândurile au același timestamp.

Ce urmărește intervievatorul: Se verifică atenția la detalii de timp care produc bug-uri subtile în audit și ordonare.

61 Mid

Cum funcționează LISTEN și NOTIFY în PostgreSQL? Când e potrivit pentru a anunța aplicația că s-a creat o comandă nouă și ce limitări are?

Răspuns scurt: O sesiune ascultă un canal cu LISTEN comenzi_noi, iar alta trimite NOTIFY comenzi_noi, 'payload' sau pg_notify(...). Notificarea este livrată doar la COMMIT-ul tranzacției care o trimite. E potrivit pentru semnale în timp real, nu ca o coadă durabilă: ascultătorii deconectați pierd mesajele.

Mecanismul:

SQL
-- sesiunea aplicației
LISTEN comenzi_noi;

-- un trigger pe comenzi
CREATE FUNCTION anunta_comanda() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_notify('comenzi_noi', json_build_object('id', NEW.id, 'total', NEW.total)::text);
  RETURN NEW;
END $$;
CREATE TRIGGER t_anunta AFTER INSERT ON comenzi FOR EACH ROW EXECUTE FUNCTION anunta_comanda();

Proprietăți:

  • notificările sunt tranzacționale: dacă tranzacția face ROLLBACK, nu se trimite nimic; dacă face COMMIT, ascultătorii primesc mesajul după aceea, deci pot citi deja rândul;
  • notificările identice din aceeași tranzacție sunt comasate;
  • payload-ul are maximum aproximativ 8000 de octeți: trimiți un id, nu documentul întreg;
  • se livrează doar sesiunilor conectate care ascultă în acel moment.

Limitări:

  • nu e durabil: un serviciu repornit pierde mesajele din intervalul în care a fost oprit. De aceea tiparul robust combină notificarea cu un tabel: rândul e sursa de adevăr, iar notificarea doar „trezește” consumatorul, care citește apoi tot ce e neprocesat (eventual cu FOR UPDATE SKIP LOCKED);
  • coada de notificări are o mărime fixă; un ascultător care nu citește o poate umple, iar atunci tranzacțiile care trimit NOTIFY încep să eșueze la COMMIT;
  • nu funcționează prin PgBouncer în modul transaction, pentru că LISTEN are nevoie de o conexiune dedicată.

Comparativ cu Kafka sau RabbitMQ: e suficient pentru volume mici și medii și elimină o componentă din arhitectură.

Ce urmărește intervievatorul: Se verifică dacă știi funcționalitățile PostgreSQL pentru evenimente și dacă înțelegi garanțiile lor reale.

62 Mid

Tabelul lectii(curs_id, ordine) are UNIQUE (curs_id, ordine). Comanda UPDATE lectii SET ordine = ordine + 1 WHERE curs_id = 3 eșuează cu duplicate key value violates unique constraint, deși la final valorile ar fi unice. De ce, și cum o faci să funcționeze?

Răspuns scurt: Constrângerile unice obișnuite sunt verificate imediat, rând cu rând: când lecția 1 devine 2, lecția 2 încă există. Declari constrângerea DEFERRABLE, iar verificarea se mută la sfârșitul instrucțiunii sau, cu INITIALLY DEFERRED, la COMMIT.

PostgreSQL actualizează rândurile într-o ordine fizică oarecare. Dacă primul rând procesat are ordine = 1 și devine 2, în acel moment există două rânduri cu ordine = 2. Indexul unic nedeferabil verifică imediat și oprește instrucțiunea. Standardul SQL cere verificarea la final de instrucțiune, dar PostgreSQL o face pe loc pentru constrângerile NOT DEFERRABLE (implicit), din motive de performanță.

Soluția:

SQL
ALTER TABLE lectii DROP CONSTRAINT lectii_curs_id_ordine_key;
ALTER TABLE lectii ADD CONSTRAINT lectii_curs_ordine_uq
  UNIQUE (curs_id, ordine) DEFERRABLE INITIALLY IMMEDIATE;
  • DEFERRABLE INITIALLY IMMEDIATE: verificarea se face la sfârșitul fiecărei instrucțiuni, deci UPDATE-ul de mai sus reușește;
  • pentru schimbări în mai mulți pași (interschimbarea a două lecții prin două UPDATE-uri), în tranzacție rulezi SET CONSTRAINTS lectii_curs_ordine_uq DEFERRED, iar verificarea se face la COMMIT;
  • DEFERRABLE INITIALLY DEFERRED amână mereu la COMMIT.

Același mecanism se aplică cheilor străine, util la inserarea a două rânduri care se referă reciproc.

Limitări:

  • o constrângere deferabilă nu poate fi folosită ca arbitru în INSERT ... ON CONFLICT;
  • o cheie străină nu poate referi coloanele unei constrângeri unice deferabile: ținta unei chei străine trebuie să fie o cheie unică nedeferabilă;
  • verificarea amânată costă puțin mai mult.

Alternativa fără constrângere deferabilă: actualizarea în doi pași (întâi valori negative, apoi cele finale) sau un număr de ordine cu goluri (10, 20, 30).

Ce urmărește intervievatorul: Se verifică înțelegerea momentului în care se verifică o constrângere, o problemă frecventă la reordonări.

Eroarea, în practică:duplicate key value violates unique constraint

63 Senior

După un import de 50 de milioane de rânduri, primul SELECT count(*) pe tabel generează sute de MB de scrieri pe disc și chiar WAL. De ce scrie o interogare de citire și cum previi efectul?

Răspuns scurt: Sunt hint bits: la prima citire, PostgreSQL verifică dacă tranzacția care a scris fiecare rând s-a confirmat și notează asta în rând, murdărind pagina; cu sume de control apare și WAL. Previi cu VACUUM (FREEZE, ANALYZE) sau COPY ... FREEZE.

Fiecare rând are xmin, dar ca să decidă dacă rândul e vizibil, PostgreSQL trebuie să știe dacă tranzacția xmin s-a confirmat sau a eșuat. Informația stă în pg_xact (fostul clog). Ca să nu o caute la fiecare citire, la prima verificare setează în antetul rândului niște biți de stare: hint bits (HEAP_XMIN_COMMITTED).

Consecințe:

  • prima scanare după un import mare modifică practic fiecare pagină, care trebuie apoi scrisă pe disc;
  • dacă sumele de control ale paginilor sunt activate (implicit în clusterele noi create cu PostgreSQL 18) sau wal_log_hints = on, prima modificare a unei pagini după un checkpoint scrie imaginea completă a paginii în WAL, deci apar și sute de MB de WAL, care ajung și la replici;
  • mai târziu, rândurile vor trebui oricum înghețate (freeze) de autovacuum ca să evite wraparound-ul, deci tabelul va fi rescris încă o dată.

Prevenire:

SQL
BEGIN;
CREATE TABLE import_2025 (LIKE comenzi INCLUDING DEFAULTS);
COPY import_2025 FROM STDIN WITH (FORMAT csv, FREEZE);
COMMIT;
-- sau după un import obișnuit:
VACUUM (FREEZE, ANALYZE) comenzi;
  • COPY ... FREEZE scrie rândurile deja înghețate și marcate ca vizibile, dar doar dacă tabelul a fost creat sau golit (TRUNCATE) în aceeași tranzacție;
  • un VACUUM (FREEZE, ANALYZE) controlat după import face toată munca o singură dată, la un moment ales de tine, nu la prima interogare a utilizatorilor;
  • ANALYZE adaugă și statisticile de care are nevoie planificatorul.

Acest detaliu explică de ce primul raport după o încărcare de date este neobișnuit de lent.

Ce urmărește intervievatorul: Se verifică cunoașterea internă a stocării. Puțini candidați știu de hint bits, iar cei care știu au investigat probleme reale de performanță.

64 Junior

De ce SELECT count(*) FROM comenzi durează secunde pe 50 de milioane de rânduri, deși în MySQL MyISAM era instantaneu? Cum afișezi rapid un număr aproximativ?

Răspuns scurt: Din cauza MVCC, fiecare tranzacție poate vedea alt număr de rânduri, deci PostgreSQL nu poate ține un contor global; trebuie să verifice vizibilitatea fiecărui rând. Pentru o estimare instantanee citești reltuples din pg_class, actualizat de VACUUM și ANALYZE.

MyISAM ținea un contor exact pentru tabel pentru că nu avea tranzacții. În PostgreSQL, în același moment, o tranzacție veche poate vedea 50.000.000 de rânduri, iar una nouă 50.000.120. Răspunsul depinde de snapshot, deci se calculează parcurgând rândurile (sau un index, dacă e posibil index-only scan și visibility map e la zi). Pe tabele mari, planul este un Parallel Seq Scan cu agregare parțială.

Variante rapide:

SQL
-- estimare din catalog, instantanee:
SELECT reltuples::bigint AS aproximativ FROM pg_class WHERE oid = 'public.comenzi'::regclass;

-- estimarea planificatorului pentru orice filtru:
EXPLAIN SELECT * FROM comenzi WHERE stare = 'noua';   -- rows=...
  • reltuples este actualizat de VACUUM, ANALYZE și CREATE INDEX; e -1 dacă tabelul nu a fost niciodată analizat (PostgreSQL 14+);
  • pentru paginare, interfețele mari afișează „peste 10.000 de rezultate” în loc de numărul exact, sau numără cu limită: SELECT count(*) FROM (SELECT 1 FROM comenzi WHERE ... LIMIT 10001) t.

Când chiar ai nevoie de un număr exact și frecvent (de exemplu numărul de comentarii al unui articol), ții un contor: o coloană actualizată de trigger sau de aplicație, ori un tabel de contoare per grup. Atenție la contorul unic global, actualizat de toate tranzacțiile: devine un punct de blocare, iar tiparul mai bun este cu mai multe rânduri de contor adunate la citire.

Ce urmărește intervievatorul: Se verifică legătura dintre MVCC și o problemă de performanță foarte vizibilă în interfețe cu paginare.

Funcții, proceduri și triggere PL/pgSQL

Exersează capitolul cu exerciții reale: Curs PostgreSQL online →
65 Junior

Care este diferența dintre o funcție (CREATE FUNCTION) și o procedură (CREATE PROCEDURE) în PostgreSQL? Când ai nevoie de procedură?

Răspuns scurt: O funcție întoarce o valoare, se apelează din SELECT și rulează în tranzacția apelantului. O procedură (PostgreSQL 11+) se apelează cu CALL și poate face COMMIT și ROLLBACK în interior, deci e potrivită pentru prelucrări în loturi.

Funcție:

SQL
CREATE FUNCTION pret_cu_tva(p numeric) RETURNS numeric LANGUAGE sql IMMUTABLE
RETURN round(p * 1.19, 2);
SELECT nume, pret_cu_tva(pret) FROM produse;
  • se folosește în expresii, în WHERE, în indecși (dacă e imutabilă), în valori implicite;
  • nu poate controla tranzacția: un COMMIT în interior dă invalid transaction termination.

Procedură:

SQL
CREATE PROCEDURE arhiveaza_comenzi(lot int DEFAULT 10000) LANGUAGE plpgsql AS $$
DECLARE n int;
BEGIN
  LOOP
    WITH mutate AS (
      DELETE FROM comenzi WHERE id IN (
        SELECT id FROM comenzi WHERE creat_la < now() - interval '2 years' LIMIT lot)
      RETURNING *)
    INSERT INTO comenzi_arhiva SELECT * FROM mutate;
    GET DIAGNOSTICS n = ROW_COUNT;
    COMMIT;
    EXIT WHEN n = 0;
  END LOOP;
END $$;
CALL arhiveaza_comenzi();
  • fiecare lot se confirmă separat: blocările se eliberează, WAL-ul se poate recicla, VACUUM poate curăța, iar o întrerupere nu pierde tot progresul;
  • COMMIT funcționează doar dacă CALL nu e apelat într-o tranzacție explicită deschisă de client și nici din altă funcție. Altfel apare invalid transaction termination.

Alte diferențe: procedurile nu apar în SELECT, au doar parametri IN, INOUT și OUT (OUT din PostgreSQL 14), iar rezultatul se vede ca un rând întors de CALL.

În practică, majoritatea logicii rămâne în funcții; procedurile sunt pentru joburi de întreținere și migrări de date pe loturi.

Ce urmărește intervievatorul: Se verifică cunoașterea diferenței reale, controlul tranzacțiilor, nu doar sintaxa de apel.

Eroarea, în practică:invalid transaction terminationis a procedure (use CALL)

66 Mid

Ce înseamnă marcajele IMMUTABLE, STABLE și VOLATILE ale unei funcții? Ce se poate întâmpla dacă marchezi greșit ca IMMUTABLE o funcție care citește dintr-un tabel?

Răspuns scurt: IMMUTABLE promite același rezultat pentru aceleași argumente, mereu; STABLE, în cadrul unei instrucțiuni; VOLATILE nu promite nimic. O funcție falsă IMMUTABLE poate fi evaluată o dată la planificare și dă rezultate vechi, iar indecșii pe ea devin greșiți.

Categoriile:

  • IMMUTABLE: nu citește baza de date și nu depinde de setări (fus orar, colație, search_path). Exemple: lower(text), abs. Poate fi folosită în indecși, coloane generate și chei de partiționare, iar apelurile cu constante pot fi calculate o singură dată, la planificare;
  • STABLE: poate citi din baza de date sau din setări, dar nu modifică nimic și dă același rezultat în timpul unei instrucțiuni. Exemple: now(), current_setting(), to_char pe timestamptz. Poate fi folosită în condiții de index scan (valoarea se calculează o dată la execuție);
  • VOLATILE: rezultatul se poate schimba la fiecare apel sau funcția are efecte secundare. Exemple: random(), nextval(), clock_timestamp(). Se evaluează pentru fiecare rând și blochează multe optimizări.

Ce poate merge rău cu o funcție mincinoasă:

SQL
CREATE FUNCTION curs_eur() RETURNS numeric LANGUAGE sql IMMUTABLE
AS $$ SELECT valoare FROM cursuri WHERE moneda = 'EUR' $$;
  • într-o instrucțiune pregătită sau într-un view folosit des, valoarea poate fi „înghețată” la momentul planificării, deci prețurile sunt calculate cu un curs vechi;
  • un index pe pret * curs_eur() conține valorile de la data creării și nu se mai actualizează când se schimbă cursul, deci căutările întorc rânduri greșite;
  • partition pruning pe o funcție imutabilă falsă poate exclude partițiile corecte.

Și marcajul prea pesimist costă: o funcție VOLATILE folosită în WHERE împiedică folosirea indexului și execuția paralelă. Regula: marchezi funcția cu categoria cea mai strictă care este adevărată, și adaugi PARALLEL SAFE când e cazul.

Ce urmărește intervievatorul: Se verifică înțelegerea contractului dintre funcții și planificator, sursa unor bug-uri foarte greu de găsit.

Eroarea, în practică:functions in index expression must be IMMUTABLEgeneration expression is not immutable

67 Senior

O funcție PL/pgSQL clienti_activi() care face doar un RETURN QUERY SELECT ... este mult mai lentă decât interogarea scrisă direct, când e folosită cu WHERE oras = 'Iași'. De ce, și cum ajută o funcție LANGUAGE sql?

Răspuns scurt: O funcție PL/pgSQL e o cutie neagră pentru planificator: produce tot rezultatul, apoi se aplică filtrul, fără index. O funcție simplă LANGUAGE sql poate fi inlined: corpul ei intră în interogare, iar filtrul ajunge până la indexul pe oras.

Ce se întâmplă cu PL/pgSQL:

SQL
CREATE FUNCTION clienti_activi() RETURNS SETOF clienti LANGUAGE plpgsql STABLE AS $$
BEGIN
  RETURN QUERY SELECT * FROM clienti WHERE activ;
END $$;

SELECT * FROM clienti_activi() WHERE oras = 'Iași';

Planul arată Function Scan on clienti_activi cu Filter: (oras = 'Iași'). Funcția produce toți clienții activi (posibil milioane), îi materializează, apoi se aplică filtrul. Planificatorul presupune implicit 1000 de rânduri (ROWS 1000), deci și estimările pentru restul interogării sunt greșite.

Varianta SQL:

SQL
CREATE FUNCTION clienti_activi() RETURNS SETOF clienti LANGUAGE sql STABLE AS $$
  SELECT * FROM clienti WHERE activ
$$;

Planul devine Index Scan on clienti cu oras = 'Iași' AND activ, ca și cum ai fi scris interogarea direct.

Condiții pentru inlining (pentru funcții care întorc seturi): LANGUAGE sql, o singură instrucțiune SELECT, nu VOLATILE, nu SECURITY DEFINER, fără clauze SET, fără STRICT în anumite cazuri, fără tipuri polimorfice neresolvabile. Funcțiile scalare SQL simple sunt și ele inlined în expresii.

Alternative când ai nevoie de logică procedurală:

  • un view, care este întotdeauna integrat în interogare;
  • parametri pentru filtre în funcție (clienti_activi(p_oras text)), astfel încât filtrul ajunge în interogarea din interior;
  • ROWS n și COST n în definiția funcției, pentru estimări mai bune.

Ce urmărește intervievatorul: Se verifică înțelegerea modului în care planificatorul tratează funcțiile, o cauză frecventă de lentoare în aplicații care încapsulează logica în baza de date.

68 Mid

Cum tratezi erorile în PL/pgSQL? Arată un bloc EXCEPTION, explică ce înseamnă SQLSTATE, GET STACKED DIAGNOSTICS și de ce un bloc EXCEPTION într-o buclă cu un milion de iterații încetinește mult funcția.

Răspuns scurt: Prinzi erorile cu EXCEPTION WHEN unique_violation THEN ..., după nume sau cod SQLSTATE, și citești detaliile cu GET STACKED DIAGNOSTICS. Fiecare intrare într-un bloc cu EXCEPTION creează o subtranzacție, scumpă când se repetă de milioane de ori.

Exemplu:

SQL
DO $$
DECLARE v_msg text; v_detail text; v_state text;
BEGIN
  INSERT INTO clienti (email) VALUES ('ana@x.ro');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'Clientul există deja';
  WHEN check_violation OR not_null_violation THEN
    GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT, v_detail = PG_EXCEPTION_DETAIL, v_state = RETURNED_SQLSTATE;
    RAISE WARNING 'Date invalide (%): % / %', v_state, v_msg, v_detail;
  WHEN OTHERS THEN
    RAISE;   -- retrimite eroarea
END $$;
  • fiecare eroare are un cod SQLSTATE de 5 caractere: 23505 unicitate, 23503 cheie străină, 23514 CHECK, 22012 împărțire la zero, 40001 serializare. Primele două caractere dau clasa (23 = încălcare de integritate);
  • poți prinde după nume (unique_violation) sau cod (SQLSTATE '23505');
  • RAISE EXCEPTION 'mesaj' USING ERRCODE = 'P0001', DETAIL = '...', HINT = '...' aruncă erori proprii, pe care aplicația le poate distinge după cod.

Costul: când intri într-un bloc cu EXCEPTION, PostgreSQL deschide o subtranzacție (un savepoint implicit), ca să poată anula doar ce s-a făcut în bloc. Într-o buclă cu un milion de iterații:

  • fiecare subtranzacție care scrie primește un id de tranzacție propriu, consumând id-uri rapid;
  • peste 64 de subtranzacții active într-o sesiune, informațiile ies din memoria procesului, iar verificările de vizibilitate pentru toate sesiunile încetinesc (problema „subtransaction SLRU”).

Variante mai bune: validezi înainte, folosești INSERT ... ON CONFLICT, sau procesezi setul întreg cu o singură instrucțiune SQL și tratezi erorile o dată, la nivel de lot.

Ce urmărește intervievatorul: Se verifică tratarea corectă a erorilor și cunoașterea costului ascuns al blocurilor EXCEPTION, important în proceduri de import.

Eroarea, în practică:query returned no rows

69 Junior

Care este diferența dintre un trigger BEFORE și unul AFTER, respectiv FOR EACH ROW și FOR EACH STATEMENT? Ce înseamnă când o funcție de trigger BEFORE întoarce NULL?

Răspuns scurt: Un trigger BEFORE ROW rulează înaintea scrierii rândului, poate modifica NEW sau anula scrierea întorcând NULL. Un trigger AFTER rulează după scriere, pentru efecte în alte tabele. FOR EACH STATEMENT rulează o dată pe instrucțiune.

Exemple tipice:

SQL
CREATE FUNCTION seteaza_modificat() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.modificat_la := now();
  RETURN NEW;
END $$;
CREATE TRIGGER t_modificat BEFORE UPDATE ON produse
FOR EACH ROW EXECUTE FUNCTION seteaza_modificat();

BEFORE ROW:

  • are acces la NEW (rândul care va fi scris) și OLD (la UPDATE și DELETE);
  • poate modifica NEW: normalizări, valori calculate, timestamp-uri;
  • valoarea întoarsă contează: RETURN NEW scrie rândul (eventual modificat), RETURN NULL anulează în tăcere operația pentru acel rând, iar instrucțiunea raportează mai puține rânduri (UPDATE 0). La DELETE se întoarce OLD ca operația să continue;
  • constrângerile se verifică după trigger, deci vezi valorile finale.

AFTER ROW:

  • rândul este deja scris; valoarea întoarsă este ignorată;
  • folosit pentru audit, actualizarea altor tabele, notificări;
  • o eroare ridicată aici anulează totuși întreaga instrucțiune.

FOR EACH STATEMENT: rulează o dată pe instrucțiune, chiar dacă nu a fost afectat niciun rând. Cu tabele de tranziție (REFERENCING NEW TABLE AS noi), vezi toate rândurile afectate și le procesezi ca set, mult mai eficient decât rând cu rând la operații în masă.

Mai există INSTEAD OF pentru view-uri și clauza WHEN (OLD.pret IS DISTINCT FROM NEW.pret), care evită apelarea funcției când nu e cazul. Ordinea triggerelor de același tip este alfabetică după nume.

Ce urmărește intervievatorul: Se verifică noțiunile de bază despre triggere și capcana RETURN NULL, care produce pierderi tăcute de date.

Eroarea, în practică:record "new" has no fieldtrigger functions can only be called as triggers

70 Mid

Un trigger AFTER UPDATE ON conturi actualizează tot în conturi câmpul ultima_modificare al rândului modificat. După deploy, orice UPDATE dă stack depth limit exceeded. De ce, și cum previi recursivitatea?

Răspuns scurt: UPDATE-ul din trigger declanșează din nou același trigger, care face un nou UPDATE, la infinit, până se depășește stiva. Reparația corectă este un trigger BEFORE care modifică direct NEW; alternativ, o clauză WHEN sau verificarea pg_trigger_depth().

Lanțul: 1. aplicația rulează UPDATE conturi SET sold = ... WHERE id = 7; 2. triggerul AFTER rulează UPDATE conturi SET ultima_modificare = now() WHERE id = 7; 3. acest UPDATE declanșează din nou triggerul, care face alt UPDATE, și tot așa.

PostgreSQL nu are o protecție implicită împotriva recursivității triggerelor. Bucla se oprește doar la eroarea stack depth limit exceeded (max_stack_depth), iar întreaga instrucțiune este anulată.

Soluții, în ordinea preferinței:

1. Trigger BEFORE care modifică rândul înainte de scriere, fără alt UPDATE:

SQL
CREATE FUNCTION marcheaza() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.ultima_modificare := now(); RETURN NEW; END $$;
CREATE TRIGGER t_marcheaza BEFORE UPDATE ON conturi FOR EACH ROW EXECUTE FUNCTION marcheaza();

E și mai eficient: o singură versiune nouă a rândului, nu două.

2. Clauza WHEN, ca triggerul să ruleze doar când contează:

SQL
CREATE TRIGGER t_audit AFTER UPDATE ON conturi FOR EACH ROW
WHEN (OLD.sold IS DISTINCT FROM NEW.sold) EXECUTE FUNCTION audit_sold();

3. pg_trigger_depth(), care întoarce nivelul de imbricare: IF pg_trigger_depth() > 1 THEN RETURN NULL; END IF;. Funcționează, dar ascunde problema de proiectare.

Recursivitatea indirectă apare și între tabele: triggerul de pe comenzi modifică clienti, iar cel de pe clienti modifică comenzi. De aceea merită documentat ce triggere există (\dS conturi le listează) și păstrată logica lor cât mai simplă.

Ce urmărește intervievatorul: Se verifică o greșeală clasică cu triggere și cunoașterea alternativelor curate.

71 Junior

O funcție PL/pgSQL primește numele unui tabel și o valoare de căutare și construiește interogarea prin concatenare: EXECUTE 'SELECT count(*) FROM ' || tabel || ' WHERE nume = ''' || valoare || ''''. Ce probleme are și cum o scrii corect?

Răspuns scurt: Este vulnerabilă la SQL injection prin ambii parametri, iar un nume cu majuscule sau un apostrof în valoare o strică. Folosești format() cu %I pentru identificatori și EXECUTE ... USING pentru valori, care sunt trimise ca parametri, nu ca text SQL.

Probleme:

  • dacă valoare este x' OR '1'='1, interogarea întoarce toate rândurile; cu x'; DROP TABLE clienti; -- poate face și mai mult rău, dacă rolul are drepturi;
  • dacă tabel vine de la utilizator, poate fi orice expresie SQL;
  • un nume de client legitim ca O'Brien produce o eroare de sintaxă.

Varianta corectă:

SQL
CREATE FUNCTION numara(p_tabel text, p_valoare text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I WHERE nume = $1', p_tabel)
    INTO n
    USING p_valoare;
  RETURN n;
END $$;
  • %I citează corect un identificator (pune ghilimele duble când e nevoie și dublează ghilimelele din interior). %I nu verifică însă dacă tabelul e permis, doar că este un singur identificator;
  • $1 din textul dinamic primește valoarea din USING, ca parametru real: nu se poate transforma niciodată în cod SQL;
  • %L citează o valoare ca literal; e util când trebuie s-o pui în text (de exemplu în DDL, unde parametrii nu sunt permiși), dar USING rămâne preferat pentru valori;
  • quote_ident și quote_literal sunt variantele mai vechi ale acelorași operații.

Pentru numele de tabel care vin din exterior, adaugi o listă albă: verifici că p_tabel este în ('clienti', 'furnizori') sau că există în schema permisă (to_regclass). Și dai funcției drepturile minime: o funcție dinamică într-un rol cu drepturi largi este o țintă bună.

Ce urmărește intervievatorul: Se verifică securitatea în SQL dinamic, unde chiar și programatori cu experiență fac greșeala concatenării.

Eroarea, în practică:too few arguments for format()

72 Senior

Ce face SECURITY DEFINER la o funcție și de ce documentația PostgreSQL cere să setezi search_path pentru astfel de funcții? Cum poate un utilizator fără drepturi să abuzeze de o funcție scrisă neglijent?

Răspuns scurt: O funcție SECURITY DEFINER rulează cu drepturile proprietarului, ca un sudo pentru o operație. Fără search_path fixat, un utilizator poate crea, într-o schemă pe care o controlează, un obiect cu același nume, pe care funcția privilegiată îl va executa.

Uz legitim: aplicația nu are drept de scriere în audit, dar poate apela inregistreaza_audit(), care scrie acolo cu drepturile proprietarului. Accesul e controlat prin funcție, nu prin drepturi pe tabel.

Atacul prin search_path:

SQL
CREATE FUNCTION reseteaza_parola(p_user text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  UPDATE utilizatori SET parola = genereaza_hash(p_user) WHERE nume = lower(p_user);
END $$;

Numele utilizatori, genereaza_hash și chiar lower sunt căutate în search_path-ul apelantului. Un atacator care poate crea obiecte într-o schemă din calea lui (sau în pg_temp, care se caută implicit primul pentru tabele) creează propriul utilizatori sau propria funcție genereaza_hash, care rulează apoi cu drepturile proprietarului, de exemplu un superuser.

Reguli de scriere:

SQL
CREATE FUNCTION reseteaza_parola(p_user text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
  UPDATE public.utilizatori SET parola = public.genereaza_hash(p_user) WHERE nume = lower(p_user);
END $$;
REVOKE EXECUTE ON FUNCTION reseteaza_parola(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION reseteaza_parola(text) TO rol_suport;
  • search_path fixat, cu pg_temp la final, ca să nu poată fi folosit pentru deturnare;
  • nume calificate cu schema pentru obiectele tale;
  • proprietar un rol dedicat cu drepturi minime, nu un superuser;
  • EXECUTE revocat de la PUBLIC, pentru că funcțiile noi pot fi apelate implicit de oricine.

Aceeași clasă de probleme a dus la schimbarea din PostgreSQL 15, care a retras dreptul CREATE în schema public pentru toți utilizatorii.

Ce urmărește intervievatorul: Se verifică gândirea de securitate pentru cod care rulează cu privilegii ridicate. Este o întrebare care separă seniorii.

73 Junior

În PL/pgSQL, ce se întâmplă cu SELECT pret INTO v_pret FROM produse WHERE cod = p_cod când produsul nu există sau când există două produse cu același cod? Cum schimbă comportamentul INTO STRICT și variabila FOUND?

Răspuns scurt: Fără STRICT, lipsa rândului dă NULL, iar la mai multe rânduri se ia primul, fără eroare. Cu INTO STRICT primești query returned no rows sau query returned more than one row. FOUND spune dacă instrucțiunea a găsit ceva.

Comportamentul implicit este tolerant, ceea ce ascunde erori:

SQL
SELECT pret INTO v_pret FROM produse WHERE cod = p_cod;
-- niciun rând: v_pret = NULL, FOUND = false
-- două rânduri: v_pret = pretul primului găsit (ordine nedefinită), FOUND = true

Cu STRICT, cerința „exact un rând” este verificată:

SQL
BEGIN
  SELECT pret INTO STRICT v_pret FROM produse WHERE cod = p_cod;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION 'Produsul % nu există', p_cod USING ERRCODE = 'P0002';
  WHEN TOO_MANY_ROWS THEN
    RAISE EXCEPTION 'Codul % nu este unic', p_cod;
END;

FOUND este o variabilă booleană actualizată după SELECT INTO, UPDATE, INSERT, DELETE, FETCH și RETURN QUERY:

SQL
UPDATE produse SET stoc = stoc - 1 WHERE id = p_id AND stoc > 0;
IF NOT FOUND THEN
  RAISE EXCEPTION 'Stoc insuficient pentru produsul %', p_id;
END IF;

Pentru numărul exact de rânduri afectate folosești GET DIAGNOSTICS n = ROW_COUNT.

Recomandare: când logica presupune exact un rând (căutare după cheie), folosește STRICT, iar codul eșuează zgomotos dacă presupunerea e greșită. Cu setarea plpgsql.extra_errors = 'too_many_rows', PostgreSQL tratează ca eroare și cazul cu mai multe rânduri fără STRICT, util în teste.

Ce urmărește intervievatorul: Se verifică precizia la scrierea logicii procedurale și înțelegerea comportamentelor implicite care ascund bug-uri.

Eroarea, în practică:query returned no rowsquery returned more than one row

74 Mid

Funcția de mai jos dă column reference "id" is ambiguous. De ce, și care sunt variantele de reparare?

SQL
CREATE FUNCTION comenzi_client(p_client int)
RETURNS TABLE (id int, total numeric) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT id, total FROM comenzi WHERE client_id = p_client;
END $$;

Răspuns scurt: Coloanele din RETURNS TABLE sunt de fapt variabile OUT ale funcției, deci id poate însemna și variabila, și coloana din comenzi. PostgreSQL refuză să ghicească. Repari calificând coloanele cu alias de tabel (c.id), redenumind coloanele de ieșire sau cu #variable_conflict use_column.

Mesajul complet:

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id, total FROM comenzi WHERE client_id = p_client
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

În PL/pgSQL, orice nume din interogare care coincide cu o variabilă sau un parametru poate fi înlocuit cu valoarea variabilei. RETURNS TABLE (id int, total numeric) declară implicit variabilele id și total. Implicit (plpgsql.variable_conflict = error), conflictul este o eroare, nu o alegere tăcută, și e bine așa: în versiunile foarte vechi, variabila câștiga mereu și producea bug-uri greu de găsit.

Reparații:

SQL
-- 1. alias de tabel (cea mai clară)
RETURN QUERY SELECT c.id, c.total FROM comenzi c WHERE c.client_id = p_client;

-- 2. nume de ieșire care nu se ciocnesc
RETURNS TABLE (comanda_id int, valoare numeric)

-- 3. directivă la începutul corpului
#variable_conflict use_column

Convenții care previn problema: parametrii cu prefix (p_), variabilele locale cu prefix (v_), coloanele tabelelor fără prefix și calificarea sistematică cu alias în toate interogările din funcții.

Încă o capcană din același exemplu: tipurile din RETURNS TABLE trebuie să corespundă exact cu cele din SELECT. Dacă comenzi.id este bigint, funcția eșuează la execuție cu structure of query does not match function result type, deci declari bigint sau faci conversie explicită.

Ce urmărește intervievatorul: Se verifică experiența practică cu funcții care întorc tabele, unde această eroare apare aproape la fiecare începător.

75 Mid

Trebuie să păstrezi istoricul tuturor modificărilor din tabelul preturi: cine, când, valoarea veche și cea nouă. Cum scrii un trigger de audit generic cu jsonb și cum îl faci eficient la un UPDATE pe 100.000 de rânduri?

Răspuns scurt: Un trigger AFTER INSERT OR UPDATE OR DELETE scrie într-un tabel de audit to_jsonb(OLD) și to_jsonb(NEW), cu TG_OP, TG_TABLE_NAME, current_user și now(). Pentru operații în masă, un trigger FOR EACH STATEMENT cu tabele de tranziție scrie toate rândurile printr-un singur INSERT.

Varianta pe rând:

SQL
CREATE TABLE audit (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tabel text, operatie text, utilizator text DEFAULT current_user,
  moment timestamptz DEFAULT now(), vechi jsonb, nou jsonb
);

CREATE FUNCTION f_audit() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit (tabel, operatie, vechi, nou)
  VALUES (TG_TABLE_NAME, TG_OP,
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
  RETURN NULL;   -- ignorat la AFTER
END $$;

CREATE TRIGGER t_audit AFTER INSERT OR UPDATE OR DELETE ON preturi
FOR EACH ROW EXECUTE FUNCTION f_audit();

Funcția e generică: o atașezi oricărui tabel. Diferența exactă se calculează la citire, sau salvezi doar cheile modificate.

Varianta pe instrucțiune, cu tabele de tranziție (PostgreSQL 10+):

SQL
CREATE FUNCTION f_audit_upd() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit (tabel, operatie, vechi, nou)
  SELECT TG_TABLE_NAME, 'UPDATE', to_jsonb(o), to_jsonb(n)
  FROM vechi_t o JOIN noi_t n USING (id);
  RETURN NULL;
END $$;

CREATE TRIGGER t_audit_upd AFTER UPDATE ON preturi
REFERENCING OLD TABLE AS vechi_t NEW TABLE AS noi_t
FOR EACH STATEMENT EXECUTE FUNCTION f_audit_upd();

Un UPDATE pe 100.000 de rânduri înseamnă un singur apel de funcție și un singur INSERT, nu 100.000.

Considerații: current_user arată rolul bazei de date; dacă aplicația folosește un singur rol, transmiți utilizatorul real prin SET LOCAL app.utilizator = 'ana' și îl citești cu current_setting('app.utilizator', true). Tabelul de audit crește repede, deci îl partiționezi pe luni și stabilești o politică de păstrare.

Ce urmărește intervievatorul: Se verifică abilitatea de a implementa un audit robust și cunoașterea tabelelor de tranziție, mai puțin folosite dar foarte utile.

76 Senior

Echipa discută dacă regulile de business (calculul prețului final, validarea stocului, aplicarea reducerilor) trebuie puse în funcții și triggere PostgreSQL sau în aplicație. Ce argumente aduci pentru fiecare variantă și ce recomanzi?

Răspuns scurt: În baza de date, regulile se aplică uniform, pentru orice client, lângă date. În aplicație sunt mai ușor de testat, versionat și scalat. Recomandarea uzuală: integritatea datelor în baza de date, logica de business variabilă în aplicație.

Argumente pentru baza de date:

  • regulile se aplică indiferent cine scrie: aplicația principală, un script de import, un job de raportare, un coleg în psql;
  • operațiile care ating multe rânduri sunt mult mai rapide fără drumuri dus-întors prin rețea;
  • garanțiile de concurență sunt mai ușor de obținut lângă date (constrângeri, FOR UPDATE, SERIALIZABLE);
  • constrângerile declarative (CHECK, FK, EXCLUDE, unicitate) sunt documentație care nu se desincronizează.

Argumente pentru aplicație:

  • testarea unitară, depanarea, refactorizarea și analiza statică sunt mult mai bune în Java, TypeScript sau C#;
  • versionarea și deploy-ul: o funcție PL/pgSQL schimbată trebuie migrată atent, cu compatibilitate între versiuni;
  • serverele de aplicație se scalează orizontal ușor; serverul de baze de date primar, nu;
  • triggerele sunt „magie” invizibilă: un programator nou nu știe că un INSERT declanșează cinci alte scrieri;
  • regulile depind des de servicii externe (cursuri valutare, sistem de reduceri) la care baza de date nu are acces.

Recomandarea mea:

  • în baza de date: tot ce ține de integritatea datelor (tipuri, NOT NULL, chei, CHECK, constrângeri de excludere), câmpuri tehnice (modificat_la), audit, și operațiile pe seturi mari unde performanța contează;
  • în aplicație: fluxurile de business, calculele care se schimbă des, orchestrarea, integrarea cu alte sisteme;
  • dacă alegi funcții în baza de date, le tratezi ca pe cod: în controlul versiunilor, cu migrări, cu teste (pgTAP) și review.

Răspunsul depinde de echipă: o echipă cu DBA și experiență PL/pgSQL poate duce mai multă logică în baza de date în siguranță.

Ce urmărește intervievatorul: Se verifică judecata arhitecturală și capacitatea de a argumenta echilibrat, nu dogmatic.

Roluri, drepturi și Row Level Security

77 Junior

Care este diferența dintre CREATE USER și CREATE ROLE în PostgreSQL? Ce înseamnă că un rol este membru al altui rol și ce face SET ROLE?

Răspuns scurt: În PostgreSQL există un singur concept, rolul. CREATE USER este CREATE ROLE cu atributul LOGIN. Un rol membru moștenește drepturile rolului părinte (cu INHERIT), iar SET ROLE îți schimbă temporar identitatea în rolul respectiv, dacă ești membru.

Un rol poate fi, în același timp, „utilizator” (dacă are LOGIN) și „grup” (dacă alți roluri sunt membri în el).

SQL
CREATE ROLE citire NOLOGIN;                       -- grup
GRANT USAGE ON SCHEMA magazin TO citire;
GRANT SELECT ON ALL TABLES IN SCHEMA magazin TO citire;

CREATE USER ana WITH PASSWORD '...';              -- = CREATE ROLE ana LOGIN PASSWORD '...'
GRANT citire TO ana;                              -- ana devine membră

Atribute importante ale unui rol: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, CONNECTION LIMIT, VALID UNTIL. Atributele nu se moștenesc prin apartenență; se moștenesc doar drepturile pe obiecte.

Moștenirea:

  • cu INHERIT (implicit), ana folosește automat drepturile lui citire;
  • din PostgreSQL 16, opțiunile se stabilesc per apartenență: GRANT citire TO ana WITH INHERIT FALSE, SET TRUE.

SET ROLE citire schimbă identitatea curentă: obiectele create de acum aparțin lui citire, iar verificările de drepturi se fac pentru el. RESET ROLE revine. E util când mai mulți administratori trebuie să creeze obiecte cu același proprietar (SET ROLE owner_aplicatie înainte de migrări).

Practica recomandată: drepturile se dau rolurilor-grup, iar utilizatorii reali sunt doar membri. La plecarea cuiva, îi revoci apartenența sau îi ștergi rolul, fără să cauți drepturi împrăștiate pe zeci de tabele. Verifici apartenențele cu \du sau pg_auth_members.

Ce urmărește intervievatorul: Se verifică modelul de securitate al PostgreSQL, diferit de bazele de date care separă utilizatorii de grupuri.

Eroarea, în practică:permission denied for tablerole does not exist

78 Junior

Pe un server PostgreSQL 16 nou, utilizatorul aplicației primește permission denied for schema public la CREATE TABLE. Pe vechiul server (PostgreSQL 13) același script mergea. Ce s-a schimbat și cum rezolvi corect?

Răspuns scurt: Din PostgreSQL 15, rolul PUBLIC (toată lumea) nu mai are dreptul CREATE în schema public, iar schema aparține rolului pg_database_owner. Rezolvarea corectă: faci rolul aplicației proprietarul bazei de date sau al unei scheme dedicate, sau îi dai explicit GRANT CREATE ON SCHEMA.

Până la PostgreSQL 14, orice utilizator conectat putea crea obiecte în public. Era o problemă de securitate: un utilizator cu drepturi minime putea crea în public o funcție cu același nume ca una de sistem, folosită apoi, prin search_path, de alți utilizatori, inclusiv de administratori (CVE-2018-1058).

Din PostgreSQL 15:

  • PUBLIC are doar USAGE pe schema public, nu CREATE;
  • proprietarul schemei este pg_database_owner, adică proprietarul bazei de date curente.

Clusterele actualizate cu pg_upgrade păstrează vechile drepturi; doar instalările noi au regula nouă, de unde diferența dintre servere.

Variante de rezolvare, de la cea mai curată:

SQL
-- 1. baza de date aparține rolului care rulează migrările
CREATE DATABASE magazin OWNER aplicatie_owner;

-- 2. schemă dedicată aplicației
CREATE SCHEMA magazin AUTHORIZATION aplicatie_owner;
ALTER ROLE aplicatie_owner SET search_path = magazin;

-- 3. drept explicit pe public
GRANT CREATE ON SCHEMA public TO aplicatie_owner;

Separarea recomandată: un rol proprietar pentru migrări (creează și modifică tabele) și un rol separat cu care rulează aplicația, care are doar USAGE pe schemă și SELECT, INSERT, UPDATE, DELETE pe tabele. Așa, o injecție SQL în aplicație nu poate șterge sau modifica structura tabelelor.

Varianta „rapidă” GRANT ALL ON SCHEMA public TO PUBLIC readuce vechea problemă de securitate.

Ce urmărește intervievatorul: Se verifică o schimbare recentă care strică multe scripturi de instalare și înțelegerea motivului ei.

Eroarea, în practică:permission denied for schema

79 Mid

Ai dat GRANT SELECT ON ALL TABLES IN SCHEMA magazin TO raportare. O săptămână mai târziu, rolul raportare primește permission denied for table facturi, un tabel nou. De ce, și cum previi cu ALTER DEFAULT PRIVILEGES? Care este capcana acestei comenzi?

Răspuns scurt: GRANT ... ON ALL TABLES acționează doar asupra tabelelor existente. Pentru cele viitoare folosești ALTER DEFAULT PRIVILEGES, cu capcana că regula se aplică doar obiectelor create de rolul indicat, deci specifici FOR ROLE cu rolul care rulează migrările.

GRANT SELECT ON ALL TABLES IN SCHEMA magazin este o scurtătură care face câte un GRANT pentru fiecare tabel existent. Tabelele create ulterior pornesc cu drepturile implicite: doar proprietarul are acces.

Drepturile implicite pentru obiecte viitoare:

SQL
ALTER DEFAULT PRIVILEGES FOR ROLE aplicatie_owner IN SCHEMA magazin
  GRANT SELECT ON TABLES TO raportare;
ALTER DEFAULT PRIVILEGES FOR ROLE aplicatie_owner IN SCHEMA magazin
  GRANT USAGE, SELECT ON SEQUENCES TO aplicatie;

Capcana: fără FOR ROLE, comanda se aplică doar obiectelor create de rolul care o rulează. Dacă DBA-ul o rulează ca postgres, dar migrările creează tabelele ca aplicatie_owner, regula nu se aplică niciodată. Verifici cu \ddp sau în pg_default_acl.

Alte detalii:

  • funcțiile noi primesc implicit EXECUTE pentru PUBLIC; pentru a schimba asta: ALTER DEFAULT PRIVILEGES ... REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
  • secvențele au nevoie de drepturi separate pentru coloanele serial; cu identity, drepturile pe tabel ajung;
  • rolul are nevoie și de USAGE pe schemă, altfel nu vede nimic din ea;
  • pentru acces de citire la tot, PostgreSQL 14+ are rolul predefinit pg_read_all_data, care include tabelele viitoare, dar se aplică tuturor schemelor și bazelor de date.

Pentru tabelele existente rămase fără drepturi, rulezi o dată GRANT ... ON ALL TABLES, apoi te bazezi pe regulile implicite.

Ce urmărește intervievatorul: Se verifică o problemă operațională foarte frecventă în echipe cu roluri separate pentru migrări, aplicație și raportare.

Eroarea, în practică:permission denied for table

80 Mid

Construiești o aplicație SaaS cu tabele comune pentru toți clienții (coloana tenant_id). Cum folosești Row Level Security ca un client să nu poată vedea niciodată datele altuia, chiar dacă programatorul uită un WHERE? Ce diferență este între USING și WITH CHECK?

Răspuns scurt: Activezi RLS pe tabel și creezi o politică ce compară tenant_id cu o valoare setată în sesiune (current_setting('app.tenant_id')). USING filtrează rândurile existente la citire, actualizare și ștergere, iar WITH CHECK validează rândurile noi sau modificate. Atenție: proprietarul tabelului și superuserii ocolesc implicit politicile.

Configurare:

SQL
ALTER TABLE comenzi ENABLE ROW LEVEL SECURITY;
ALTER TABLE comenzi FORCE ROW LEVEL SECURITY;      -- se aplică și proprietarului

CREATE POLICY izolare_tenant ON comenzi
  USING (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

În aplicație, la fiecare cerere, în aceeași tranzacție:

SQL
BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM comenzi;          -- doar comenzile tenantului 42
COMMIT;
  • USING: condiția adăugată automat la SELECT, UPDATE, DELETE. Rândurile care nu o îndeplinesc sunt invizibile, fără eroare;
  • WITH CHECK: verificată la INSERT și UPDATE pe rândul rezultat. Un rând cu alt tenant_id dă new row violates row-level security policy for table "comenzi";
  • dacă RLS este activat și nu există nicio politică, rolul nu vede nimic (refuz implicit).

Capcane:

  • proprietarul tabelului, superuserii și rolurile cu BYPASSRLS ignoră politicile. Rolul aplicației nu trebuie să fie proprietar, sau folosești FORCE;
  • SET LOCAL e obligatoriu cu un pool de conexiuni: un SET simplu rămâne pe conexiune și ajunge la următorul client;
  • current_setting('app.tenant_id') dă eroare dacă variabila nu e setată; cu current_setting('app.tenant_id', true) primești NULL, deci niciun rând;
  • performanța: tenant_id trebuie să fie prima coloană în indecși, iar funcțiile din politici trebuie să fie STABLE și LEAKPROOF acolo unde contează.

RLS este o plasă de siguranță, nu înlocuiește autorizarea din aplicație.

Ce urmărește intervievatorul: Se verifică stăpânirea unui mecanism esențial pentru aplicații multi-tenant și cunoașterea capcanelor reale.

Eroarea, în practică:new row violates row-level security policy

81 Senior

Un analist trebuie să vadă comenzile, dar fără coloanele cnp și telefon ale clienților. Compară drepturile pe coloane, un view simplu, un view security_barrier și un view security_invoker. Ce scurgeri de date pot apărea?

Răspuns scurt: Drepturile pe coloane ascund coloanele, dar SELECT * eșuează. Un view rulează cu drepturile proprietarului și poate ocoli RLS. security_barrier oprește scurgerile prin funcțiile apelantului, iar security_invoker (PostgreSQL 15) aplică drepturile și politicile celui care interoghează.

Drepturi pe coloane:

SQL
GRANT SELECT (id, nume, oras, creat_la) ON clienti TO analist;

Simplu și sigur, dar SELECT * dă permission denied for table clienti, deci instrumentele de raportare trebuie configurate cu coloane explicite.

View simplu:

SQL
CREATE VIEW clienti_public AS SELECT id, nume, oras FROM clienti WHERE NOT confidential;
GRANT SELECT ON clienti_public TO analist;

Implicit, view-ul accesează tabelele cu drepturile proprietarului view-ului, deci analistul nu are nevoie de drepturi pe clienti. Dar dacă proprietarul ocolește RLS, view-ul ocolește și el politicile.

Scurgerea clasică: o funcție ieftină scrisă de analist poate fi evaluată de planificator înaintea filtrului din view:

SQL
CREATE FUNCTION spion(t text) RETURNS bool LANGUAGE plpgsql COST 0.0001 AS $$
BEGIN RAISE NOTICE '%', t; RETURN true; END $$;
SELECT * FROM clienti_public WHERE spion(nume);

Funcția primește și numele clienților confidențiali, filtrați de view.

security_barrier: CREATE VIEW ... WITH (security_barrier) garantează că filtrele view-ului se aplică înaintea condițiilor utilizatorului, cu excepția funcțiilor marcate LEAKPROOF (doar superuserii le pot marca). Costul: mai puține optimizări.

security_invoker (PostgreSQL 15+): view-ul verifică drepturile și politicile RLS ale celui care îl interoghează. Util când vrei ca RLS să se aplice și prin view.

Combinația solidă pentru date personale: tabel cu RLS, view security_invoker pentru forma datelor și, unde e nevoie, mascare (left(telefon, 4) || '****').

Ce urmărește intervievatorul: Se verifică înțelegerea fină a modelului de securitate, inclusiv atacuri prin funcții. Un subiect de nivel senior și de DBA.

82 Junior

Ce sunt rolurile predefinite din PostgreSQL, ca pg_read_all_data, pg_monitor sau pg_signal_backend? Când le folosești în locul drepturilor de superuser?

Răspuns scurt: Sunt roluri create de PostgreSQL care grupează drepturi administrative uzuale. Le acorzi cu GRANT pg_monitor TO monitorizare, ca un instrument de monitorizare sau un operator să-și facă treaba fără să primească SUPERUSER, care ar permite orice.

Rolurile predefinite cele mai folosite:

  • pg_read_all_data și pg_write_all_data (PostgreSQL 14+): citire, respectiv scriere în toate tabelele, view-urile și secvențele, ca și cum rolul ar avea SELECT peste tot. Util pentru backup logic sau pentru un rol de audit;
  • pg_monitor: grupează pg_read_all_settings, pg_read_all_stats și pg_stat_scan_tables. Instrumentele de monitorizare (Prometheus postgres_exporter, Datadog) văd textul interogărilor tuturor utilizatorilor în pg_stat_activity și statisticile din pg_stat_statements;
  • pg_signal_backend: poate anula sau închide sesiunile altor utilizatori (cu excepția superuserilor), util pentru operatorul de gardă;
  • pg_maintain (PostgreSQL 17+): poate rula VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER și LOCK TABLE pe toate tabelele;
  • pg_read_server_files, pg_write_server_files, pg_execute_server_program: acces la fișiere și programe pe server prin COPY. Sunt practic echivalente cu superuser, deci le dai foarte rar;
  • pg_checkpoint (15+), pg_create_subscription (16+), pg_use_reserved_connections (16+).

Exemplu:

SQL
CREATE ROLE monitorizare LOGIN PASSWORD '...';
GRANT pg_monitor TO monitorizare;
GRANT pg_signal_backend TO operator_garda;

În serviciile gestionate (Amazon RDS, Azure, Google Cloud SQL) nu primești deloc superuser, ci un rol cu o parte din aceste drepturi (rds_superuser), deci cunoașterea rolurilor predefinite este necesară acolo.

Principiul: fiecare utilizator sau serviciu primește doar drepturile de care are nevoie. Un superuser compromis înseamnă server compromis, inclusiv execuție de comenzi în sistemul de operare.

Ce urmărește intervievatorul: Se verifică aplicarea principiului drepturilor minime în administrare.

83 Mid

Serverul are în pg_hba.conf metoda md5, iar echipa de securitate cere trecerea la scram-sha-256. Ce diferență este între ele și cum faci migrarea fără să blochezi aplicațiile?

Răspuns scurt: md5 stochează și verifică un hash MD5 slab, vulnerabil dacă hash-ul e furat. scram-sha-256 folosește un protocol cu sare și iterații, fără să trimită parola sau un echivalent reutilizabil. Migrezi setând password_encryption = 'scram-sha-256', resetând parolele, iar abia apoi schimbi metoda în pg_hba.conf.

Diferențe:

  • md5: în catalog se stochează md5(parola || utilizator). Cine obține acest hash (dintr-un backup, din pg_authid) se poate autentifica direct cu el, fără să afle parola. MD5 este și rapid de spart prin forță brută. În PostgreSQL 18, parolele MD5 sunt declarate învechite și generează avertismente;
  • scram-sha-256 (implicit pentru password_encryption din PostgreSQL 14): parola este stocată ca rezultat al unei funcții cu sare și mii de iterații. La autentificare, clientul și serverul se verifică reciproc printr-un schimb de mesaje, iar ce circulă prin rețea nu poate fi refolosit.

Migrarea:

SQL
SHOW password_encryption;                       -- trebuie să fie scram-sha-256
SELECT rolname, left(rolpassword, 6) FROM pg_authid WHERE rolpassword IS NOT NULL;
-- 'md5...' sau 'SCRAM-'
ALTER ROLE aplicatie PASSWORD '...';            -- se rescrie în format SCRAM

În pg_hba.conf, metoda md5 acceptă și parole stocate SCRAM (face automat autentificare SCRAM pentru ele), deci poți reseta parolele pe rând, fără întrerupere. Când toate sunt SCRAM-, schimbi metoda în scram-sha-256 și dai SELECT pg_reload_conf().

Verificări înainte:

  • driverele vechi pot să nu suporte SCRAM (libpq sub 10, versiuni vechi de pgjdbc, unele drivere Node.js sau PHP);
  • PgBouncer trebuie configurat și el (auth_type = scram-sha-256), iar userlist.txt conține hash-urile SCRAM;
  • conexiunile trebuie să folosească TLS (hostssl în pg_hba.conf), separat de metoda de parolă.

Ce urmărește intervievatorul: Se verifică cunoștințe practice de întărire a securității și planificarea unei schimbări fără întreruperi.

84 Mid

Un coleg pleacă din firmă, iar DROP ROLE mihai dă role "mihai" cannot be dropped because some objects depend on it. Ce înseamnă și ce pași urmezi?

Răspuns scurt: Rolul deține obiecte (tabele, funcții, scheme) sau are drepturi acordate pe obiecte din baze de date. Rulezi, în fiecare bază de date, REASSIGN OWNED BY mihai TO aplicatie_owner și apoi DROP OWNED BY mihai, care șterge restul și revocă drepturile, apoi DROP ROLE mihai.

Rolurile sunt globale pentru cluster, dar obiectele și drepturile sunt în fiecare bază de date. PostgreSQL refuză ștergerea rolului cât timp există referințe la el, iar mesajul DETAIL le listează:

ERROR:  role "mihai" cannot be dropped because some objects depend on it
DETAIL:  owner of table rapoarte.vanzari_lunare
privileges for table comenzi
privileges for schema rapoarte

Pașii, în fiecare bază de date în care rolul are obiecte sau drepturi:

SQL
REASSIGN OWNED BY mihai TO aplicatie_owner;   -- obiectele lui primesc alt proprietar
DROP OWNED BY mihai;                          -- șterge ce a rămas și revocă drepturile lui

Apoi, o singură dată:

SQL
DROP ROLE mihai;

Atenție:

  • DROP OWNED BY șterge obiectele deținute care nu au fost reasignate; de aceea REASSIGN vine primul;
  • dacă mihai a acordat el drepturi altora (GRANT ... WITH GRANT OPTION), acestea pot dispărea în cascadă;
  • verifici și joburile programate sau conexiunile care foloseau rolul: pg_stat_activity, pg_cron, configurațiile aplicațiilor;
  • înainte de ștergere, poți doar bloca rolul: ALTER ROLE mihai NOLOGIN, care oprește conexiunile noi fără să afecteze nimic altceva.

Lecția de proiectare: obiectele de producție nu trebuie deținute de oameni, ci de roluri tehnice (aplicatie_owner). Oamenii primesc apartenență la roluri și creează obiecte după SET ROLE. Atunci plecarea cuiva înseamnă doar DROP ROLE pe un rol fără obiecte.

Ce urmărește intervievatorul: Se verifică o operație administrativă comună și înțelegerea separării dintre roluri (globale) și obiecte (per bază de date).

Eroarea, în practică:role cannot be dropped because objects depend on it

Administrare și întreținere

85 Junior

Un raport rulează de 40 de minute și încetinește serverul. Cum găsești sesiunea și cum o oprești? Care este diferența dintre pg_cancel_backend și pg_terminate_backend?

Răspuns scurt: O găsești în pg_stat_activity, după durata now() - query_start și textul interogării. pg_cancel_backend(pid) anulează doar interogarea curentă, iar conexiunea rămâne deschisă; pg_terminate_backend(pid) închide toată sesiunea, cu ROLLBACK pentru tranzacția ei.

Căutarea:

SQL
SELECT pid, usename, application_name, client_addr, state,
       now() - query_start AS durata, left(query, 100) AS interogare
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

Coloane utile: state (active, idle, idle in transaction), wait_event_type și wait_event (ce așteaptă: I/O, blocare, client), backend_type (ca să nu confunzi sesiunile de aplicație cu procesele de autovacuum sau replicare).

Oprirea:

SQL
SELECT pg_cancel_backend(12345);      -- trimite SIGINT: anulează interogarea
SELECT pg_terminate_backend(12345);   -- trimite SIGTERM: închide conexiunea
  • pg_cancel_backend e prima variantă, cea blândă: aplicația primește canceling statement due to user request și poate continua pe aceeași conexiune;
  • pg_terminate_backend e pentru sesiuni care nu răspund la anulare sau pentru idle in transaction, unde nu există nicio interogare de anulat. Din PostgreSQL 14 acceptă un timeout: pg_terminate_backend(pid, 5000) așteaptă confirmarea;
  • un utilizator obișnuit își poate opri doar propriile sesiuni; pentru ale altora are nevoie de pg_signal_backend.

Ce nu faci: kill -9 pe procesul PostgreSQL din sistemul de operare. Serverul consideră că memoria partajată ar putea fi coruptă, deconectează toate sesiunile și intră în recuperare automată, adică o scurtă întrerupere totală.

După intervenție, afli de ce a durat: planul de execuție, lipsa unui index, un statement_timeout care lipsea pentru rolul de raportare.

Ce urmărește intervievatorul: Se verifică o intervenție de bază în administrare și cunoașterea diferenței dintre cele două funcții.

86 Junior

Discul serverului se umple. Scrie interogarea care afișează cele mai mari 10 tabele, cu mărimea datelor, a indecșilor și totalul. Ce diferență este între pg_relation_size, pg_table_size, pg_indexes_size și pg_total_relation_size?

Răspuns scurt: pg_relation_size dă doar fișierul principal al tabelului, pg_table_size adaugă TOAST și hărțile interne, pg_indexes_size dă toți indecșii, iar pg_total_relation_size le adună pe toate. Le folosești pe pg_class cu pg_size_pretty, ordonând după total.

Interogarea:

SQL
SELECT n.nspname || '.' || c.relname AS tabel,
       pg_size_pretty(pg_table_size(c.oid)) AS date,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indecsi,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       c.reltuples::bigint AS randuri_estimate
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;

Funcțiile:

  • pg_relation_size(t): doar fișierul principal (fork-ul main); fără TOAST, fără indecși;
  • pg_table_size(t): main + TOAST + free space map + visibility map;
  • pg_indexes_size(t): toți indecșii tabelului;
  • pg_total_relation_size(t) = pg_table_size + pg_indexes_size;
  • pg_database_size('magazin'): toată baza de date.

La tabelele partiționate (relkind = 'p'), părintele are mărimea 0; însumezi partițiile cu pg_partition_tree.

Interpretare când discul se umple:

  • indecși mai mari decât datele: indecși redundanți sau umflați (bloat), candidați pentru REINDEX CONCURRENTLY sau ștergere;
  • un tabel mult mai mare decât ar justifica numărul de rânduri: bloat din cauza VACUUM-ului blocat;
  • dacă tabelele nu explică ocuparea, verifici directorul pg_wal (WAL reținut de un slot de replicare inactiv sau de arhivarea eșuată) și fișierele temporare.

pg_size_pretty afișează în unități ușor de citit, dar sortezi după valoarea numerică, nu după text.

Ce urmărește intervievatorul: Se verifică instrumentele de bază pentru investigarea spațiului, prima reacție la o alertă de disc.

87 Mid

Interogarea pe pg_stat_user_tables arată pentru tabelul sesiuni: seq_scan = 1.204.551, idx_scan = 310, n_live_tup = 80.000, n_dead_tup = 2.400.000, last_autovacuum acum 9 zile. Ce concluzii tragi și ce faci?

Răspuns scurt: Tabelul are de 30 de ori mai multe rânduri moarte decât vii, deci e umflat, iar autovacuum nu a putut curăța de 9 zile, probabil blocat de o tranzacție veche. Scanările secvențiale numeroase arată și un index lipsă.

Citirea cifrelor:

  • n_dead_tup de 2,4 milioane la 80.000 de rânduri vii: tabelul are o rată mare de UPDATE și DELETE (tipic pentru sesiuni), iar curățarea nu ține pasul. Fiecare scanare citește și rândurile moarte, deci interogările sunt mult mai lente decât ar trebui;
  • last_autovacuum acum 9 zile: autovacuum fie nu a pornit, fie a pornit și nu a putut curăța nimic;
  • seq_scan de 1,2 milioane față de 310 scanări pe index: interogările frecvente filtrează după o coloană neindexată, iar fiecare citește tot tabelul umflat.

Pașii:

SQL
-- 1. ce oprește curățarea?
SELECT pid, state, now() - xact_start AS durata, backend_xmin
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start;
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT gid, prepared FROM pg_prepared_xacts;

-- 2. curățare manuală, după ce elimini cauza
VACUUM (VERBOSE, ANALYZE) sesiuni;

-- 3. autovacuum mai agresiv pentru acest tabel
ALTER TABLE sesiuni SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 0);

VACUUM VERBOSE raportează „dead but not yet removable”: dacă numărul e mare, cauza este o tranzacție veche, un slot sau o tranzacție pregătită care ține „orizontul xmin”.

VACUUM simplu face spațiul refolosibil, dar nu micșorează fișierul. Pentru a recupera spațiul pe disc ai nevoie de VACUUM FULL (blocare exclusivă, rescrie tabelul) sau de pg_repack (fără blocare lungă).

Apoi analizezi interogările din pg_stat_statements care ating sesiuni și adaugi indexul care lipsește. Statisticile se resetează doar la pg_stat_reset(), deci compari valori la intervale de timp, nu absolute.

Ce urmărește intervievatorul: Se verifică abilitatea de a interpreta statisticile de sistem și de a lega simptomele de cauze.

88 Mid

Tabelul jurnal este partiționat pe luni, cu 36 de partiții. Cum ștergi datele mai vechi de doi ani și de ce este mult mai bine decât DELETE FROM jurnal WHERE creat_la < ...? Ce capcane are partiția DEFAULT?

Răspuns scurt: Detașezi și ștergi partițiile vechi: ALTER TABLE jurnal DETACH PARTITION jurnal_2023_01 CONCURRENTLY; DROP TABLE jurnal_2023_01;. Operația este aproape instantanee și nu lasă rânduri moarte, spre deosebire de un DELETE pe milioane de rânduri. Partiția DEFAULT complică adăugarea de partiții noi și împiedică detașarea concurentă.

DELETE pe zeci de milioane de rânduri:

  • scrie fiecare rând șters în WAL (care ajunge și pe replici);
  • lasă rânduri moarte pe care VACUUM trebuie să le curețe;
  • ține blocări pe rânduri și poate dura ore;
  • nu eliberează spațiu pe disc.

Cu partiții:

SQL
ALTER TABLE jurnal DETACH PARTITION jurnal_2023_01 CONCURRENTLY;   -- PostgreSQL 14+
DROP TABLE jurnal_2023_01;
-- sau, pentru arhivare: pg_dump -t jurnal_2023_01, apoi DROP
  • DROP TABLE șterge fișierele: spațiul se eliberează imediat, iar în WAL apare doar o înregistrare mică;
  • DETACH ... CONCURRENTLY nu ia blocare exclusivă pe tot tabelul părinte; nu poate rula într-o tranzacție. Dacă e întreruptă, termini cu ALTER TABLE ... DETACH PARTITION ... FINALIZE;
  • varianta simplă DETACH PARTITION (fără CONCURRENTLY) ia blocare exclusivă pe părinte, scurtă dar care așteaptă după toate interogările în curs.

Partiția DEFAULT primește rândurile care nu se potrivesc nicăieri. Capcane:

  • la crearea unei partiții noi, PostgreSQL scanează partiția DEFAULT ca să verifice că nu conține rânduri pentru noul interval, sub blocare; dacă DEFAULT e mare, operația devine lentă;
  • dacă DEFAULT conține deja rânduri pentru luna nouă, crearea eșuează: updated partition constraint for default partition would be violated by some row;
  • detașarea concurentă nu e permisă cât timp există o partiție DEFAULT.

Practică bună: creezi partițiile în avans, automat (pg_partman sau un job pg_cron), și monitorizezi ca DEFAULT să rămână goală.

Ce urmărește intervievatorul: Se verifică stăpânirea principalului motiv practic pentru partiționare: gestionarea ciclului de viață al datelor.

Eroarea, în practică:no partition of relation found for row

89 Junior

Ai o vedere materializată vanzari_zilnice folosită de un dashboard. REFRESH MATERIALIZED VIEW vanzari_zilnice blochează dashboard-ul pe durata reîmprospătării. Ce schimbă CONCURRENTLY și ce cerință are?

Răspuns scurt: Fără CONCURRENTLY, refresh-ul ia blocare exclusivă și citirile așteaptă. Cu CONCURRENTLY, PostgreSQL calculează rezultatul nou separat și aplică doar diferențele, iar citirile continuă. Cerința: un index unic pe vedere, fără condiție WHERE, pe coloane simple.

O vedere materializată stochează rezultatul interogării ca pe un tabel. Datele nu se actualizează singure; le reîmprospătezi explicit.

SQL
CREATE MATERIALIZED VIEW vanzari_zilnice AS
SELECT date_trunc('day', creat_la)::date AS zi, magazin_id, sum(total) AS total, count(*) AS comenzi
FROM comenzi GROUP BY 1, 2;

CREATE UNIQUE INDEX ON vanzari_zilnice (zi, magazin_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY vanzari_zilnice;

Diferențele:

  • simplu: golește și reface conținutul sub blocare ACCESS EXCLUSIVE. Mai rapid, dar nicio interogare nu poate citi până la final;
  • CONCURRENTLY: rulează interogarea, compară rezultatul cu conținutul existent prin indexul unic și aplică INSERT, UPDATE și DELETE doar pe rândurile schimbate. Citirile merg normal; două refresh-uri concurente pe aceeași vedere nu pot rula simultan.

Fără index unic:

ERROR:  cannot refresh materialized view "public.vanzari_zilnice" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

Alte detalii:

  • CONCURRENTLY e mai lent când se schimbă aproape tot rezultatul și lasă rânduri moarte, deci vederea are nevoie de VACUUM;
  • prima populare (după WITH NO DATA) nu poate fi concurentă;
  • programezi refresh-ul cu pg_cron sau din aplicație, iar momentul ultimei actualizări îl afișezi în dashboard.

Când reîmprospătarea completă devine prea scumpă (date istorice care nu se mai schimbă), treci la un tabel de agregate actualizat incremental, doar pentru zilele recente.

Ce urmărește intervievatorul: Se verifică folosirea practică a vederilor materializate pentru rapoarte, fără să blochezi utilizatorii.

Eroarea, în practică:materialized view has not been populatedcannot refresh materialized view concurrently

90 Junior

Care este diferența dintre COPY produse FROM '/tmp/produse.csv' și \copy produse FROM 'produse.csv' în psql? De ce prima dă must be superuser or have privileges of the pg_read_server_files role sau could not open file?

Răspuns scurt: COPY ... FROM 'fișier' rulează pe server și citește fișierul de pe discul serverului, deci cere drepturi speciale. \copy este o comandă psql: citește fișierul de pe calculatorul tău și îl trimite prin conexiune, cu drepturi obișnuite.

COPY cu nume de fișier:

  • calea este pe serverul de baze de date, nu pe laptopul tău; într-un container sau un serviciu cloud fișierul nu există acolo, de unde could not open file ... No such file or directory;
  • procesul postgres trebuie să poată citi fișierul;
  • rolul are nevoie de superuser sau de pg_read_server_files (pentru scriere, pg_write_server_files). Serviciile gestionate nu permit deloc acest lucru.

\copy:

\copy produse (cod, nume, pret) FROM 'produse.csv' WITH (FORMAT csv, HEADER true)
  • psql citește fișierul local și îl trimite prin protocolul COPY FROM STDIN;
  • e la fel de rapid în practică, minus transferul prin rețea;
  • comanda trebuie scrisă pe o singură linie.

Echivalentul din aplicații: driverele expun COPY FROM STDIN (CopyManager în pgjdbc, pg-copy-streams în Node.js, copy_expert în psycopg2, copy() în psycopg 3).

Pentru încărcări mari și rapide:

  • COPY este de multe ori mai rapid decât INSERT-uri individuale sau chiar decât INSERT-uri cu mai multe rânduri;
  • încarci într-un tabel de staging fără indecși, validezi, apoi INSERT INTO ... SELECT cu ON CONFLICT;
  • creezi indecșii după încărcare, nu înainte;
  • FORMAT csv tratează corect ghilimelele și separatorii din valori; opțiunea ON_ERROR ignore (PostgreSQL 17+) sare peste rândurile cu erori de tip în loc să anuleze tot importul.

Ce urmărește intervievatorul: Se verifică o confuzie foarte frecventă între execuția pe server și cea pe client, plus practici de import.

Eroarea, în practică:missing data for column (COPY)extra data after last expected column (COPY)

91 Mid

Care este diferența dintre SET work_mem = '256MB', SET LOCAL work_mem = '256MB', ALTER ROLE raportare SET work_mem = '256MB' și ALTER SYSTEM SET work_mem = '256MB'? În ce ordine se aplică setările?

Răspuns scurt: SET ține până la sfârșitul sesiunii, SET LOCAL până la sfârșitul tranzacției, ALTER ROLE ... SET se aplică la conexiunile noi ale rolului, iar ALTER SYSTEM scrie în postgresql.auto.conf, pentru tot serverul. Nivelul cel mai specific câștigă.

Nivelurile:

  • postgresql.conf și postgresql.auto.conf (scris de ALTER SYSTEM): valori globale. Se aplică la SELECT pg_reload_conf() sau la repornire, după tipul parametrului;
  • ALTER DATABASE magazin SET ...: pentru conexiunile noi la acea bază;
  • ALTER ROLE raportare SET ...: pentru conexiunile noi ale rolului;
  • ALTER ROLE raportare IN DATABASE magazin SET ...: combinația, cea mai specifică;
  • SET în sesiune: suprascrie tot ce e mai sus, până la sfârșitul sesiunii;
  • SET LOCAL: doar până la COMMIT sau ROLLBACK;
  • clauza SET a unei funcții: doar pe durata apelului.

Vezi de unde vine valoarea curentă:

SQL
SELECT name, setting, unit, source, context FROM pg_settings WHERE name = 'work_mem';
SELECT * FROM pg_db_role_setting;

context spune cine poate schimba parametrul și când: user (oricine, oricând), superuser, sighup (doar în fișiere, cu reload), postmaster (doar cu repornire, ca shared_buffers sau max_connections).

Recomandări:

  • pentru o operație anume (un raport mare, un CREATE INDEX), SET LOCAL în tranzacție este cel mai sigur;
  • cu PgBouncer în modul transaction, un SET simplu rămâne pe conexiunea fizică și afectează alți clienți; folosești doar SET LOCAL sau setări pe rol;
  • setările pe rol sunt potrivite pentru profiluri diferite: aplicația cu statement_timeout mic, raportarea cu work_mem mai mare.

Ce urmărește intervievatorul: Se verifică înțelegerea ierarhiei de configurare, necesară pentru a regla serverul fără efecte nedorite.

Eroarea, în practică:unrecognized configuration parameterinvalid value for parameter

92 Mid

Planul unei interogări arată Sort Method: external merge Disk: 412MB, iar logurile conțin multe temporary file. Ce înseamnă, cum ajustezi work_mem și de ce nu o setezi pur și simplu la 1 GB pe tot serverul?

Răspuns scurt: Sortarea nu a încăput în work_mem și a folosit fișiere temporare pe disc. Crești work_mem pentru interogare sau rol, nu global: valoarea se aplică fiecărei sortări sau hash din fiecare sesiune și poate epuiza memoria.

work_mem (implicit 4 MB) este memoria pentru o operație de sortare, hash join, agregare cu hash sau bitmap dintr-un plan. Când datele nu încap, PostgreSQL scrie pe disc:

  • Sort Method: external merge Disk: 412MB în loc de quicksort Memory: ...;
  • la hash join, Batches: 16 în loc de 1;
  • în log, cu log_temp_files = 0, fiecare fișier temporar și mărimea lui.

De ce nu crești global: o singură interogare poate avea mai multe noduri de sortare și hash, fiecare cu propriul work_mem, iar operațiile de hash pot folosi work_mem × hash_mem_multiplier (implicit 2). Cu execuție paralelă, fiecare worker are propria alocare. La 200 de conexiuni active, 1 GB poate însemna sute de GB, iar sistemul de operare oprește procesul (OOM killer), ceea ce repornește toate sesiunile.

Abordare practică:

SQL
-- pentru o interogare
BEGIN;
SET LOCAL work_mem = '512MB';
SELECT ... ;
COMMIT;

-- pentru rolul de raportare
ALTER ROLE raportare SET work_mem = '256MB';
  • global, o valoare moderată (16–64 MB pe un server OLTP tipic), calculată din memoria disponibilă și numărul de conexiuni active;
  • verifici efectul cu EXPLAIN (ANALYZE): sortarea trebuie să devină quicksort;
  • uneori soluția mai bună este un index care livrează datele deja sortate, sau o interogare care agregă înainte de sortare;
  • maintenance_work_mem este parametrul separat pentru CREATE INDEX, VACUUM și ALTER TABLE ADD FOREIGN KEY, unde valori mai mari (1–2 GB) sunt sigure pentru că rulează puține simultan.

Statisticile cumulate ale fișierelor temporare sunt în pg_stat_database.temp_files și temp_bytes.

Ce urmărește intervievatorul: Se verifică înțelegerea gestionării memoriei pe interogare și a riscurilor reglării globale.

Eroarea, în practică:unrecognized configuration parameter

93 Junior

Ce sunt extensiile PostgreSQL? Cum vezi ce extensii sunt disponibile și instalate și care sunt cele mai folosite în practică?

Răspuns scurt: Extensiile adaugă tipuri, funcții, indecși sau unelte de monitorizare fără să modifice serverul. pg_available_extensions arată ce poți instala, \dx ce e instalat, iar CREATE EXTENSION le activează într-o bază de date. Cele mai folosite: pg_stat_statements, pgcrypto, pg_trgm, unaccent, postgis, pg_partman, pg_cron.

O extensie este un pachet de obiecte SQL (și, de multe ori, cod C) instalat pe server și activat per bază de date:

SQL
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions ORDER BY name;

CREATE EXTENSION IF NOT EXISTS pg_trgm;
ALTER EXTENSION pg_trgm UPDATE;

Extensii frecvente:

  • pg_stat_statements: statistici cumulate pe interogări normalizate; cere și shared_preload_libraries, deci repornire;
  • pgcrypto: funcții de hash și criptare (crypt(), gen_salt()); gen_random_uuid() este în nucleu din PostgreSQL 13, iar uuidv7() din 18;
  • pg_trgm: similaritate și indecși trigram pentru LIKE '%text%' și căutare tolerantă la greșeli;
  • unaccent: eliminarea diacriticelor;
  • citext: text fără sensibilitate la majuscule;
  • postgis: tipuri și funcții geografice, standardul de facto pentru date spațiale;
  • pg_partman și pg_cron: administrarea partițiilor și joburi programate în baza de date;
  • pgvector: vectori și căutare de similaritate pentru aplicații AI;
  • postgres_fdw: interogarea tabelelor din alt server PostgreSQL.

Lucruri de știut:

  • extensia trebuie să existe pe server (pachetul sistemului de operare, de exemplu postgresql-17-postgis-3), apoi o activezi în baza de date;
  • serviciile gestionate permit doar o listă aprobată de extensii; verifici înainte de a proiecta pe una;
  • extensiile cu cod C rulează în procesul serverului: un bug poate opri serverul, deci alegi extensii mature;
  • la upgrade de versiune majoră, extensiile trebuie să existe și pentru versiunea nouă, altfel pg_upgrade eșuează.

Ce urmărește intervievatorul: Se verifică familiaritatea cu ecosistemul PostgreSQL, unul dintre avantajele lui principale.

Eroarea, în practică:extension is not available

94 Senior

Aplicația începe brusc să primească nextval: reached maximum value of sequence "evenimente_id_seq" (2147483647). Ce s-a întâmplat, cum rezolvi urgent și cum monitorizezi din timp astfel de probleme?

Răspuns scurt: Coloana id e integer (de obicei serial), iar secvența a atins maximul pe 4 octeți. Nu ajută să mărești secvența: coloana trebuie trecută la bigint, cu o migrare atentă pe tabele mari. Preventiv, monitorizezi procentul folosit al secvențelor.

serial creează o coloană integer și o secvență de tip integer (din PostgreSQL 10), cu maximum 2.147.483.647. Golurile din secvență (ROLLBACK-uri, INSERT ... ON CONFLICT care consumă valori, cache de secvență) grăbesc epuizarea. La un tabel cu mii de inserări pe secundă, limita se atinge în câteva luni sau ani.

Opțiuni urgente:

  • ALTER TABLE evenimente ALTER COLUMN id TYPE bigint rescrie tabelul și indecșii sub blocare exclusivă; pe un tabel mic sau mediu, într-o fereastră de mentenanță, e cea mai simplă soluție, apoi ALTER SEQUENCE evenimente_id_seq AS bigint;
  • dacă tabelul e uriaș: o coloană nouă id_nou bigint, trigger de sincronizare, completare în loturi, index unic creat concurent, apoi schimbarea cheii primare într-o tranzacție scurtă;
  • soluție temporară foarte limitată: dacă nu există valori negative, poți reporni secvența de la -2147483648 (ALTER SEQUENCE ... MINVALUE -2147483648 RESTART WITH -2147483648 INCREMENT 1), câștigând timp. Funcționează doar dacă nimic nu presupune id-uri pozitive.

Mai rău decât eroarea de secvență este cazul în care secvența e bigint, dar coloana sau o cheie străină care o referă e integer: eroarea devine integer out of range la INSERT.

Monitorizare:

SQL
SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS procent_folosit
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY procent_folosit DESC;

Alertă la 50–70%, ca migrarea să fie planificată. Și verifici coloanele de chei străine care referă id-uri, deoarece pot fi integer chiar dacă cheia primară e deja bigint. Pentru tabele noi, folosești direct bigint GENERATED ALWAYS AS IDENTITY.

Ce urmărește intervievatorul: Se verifică experiența cu un incident real și cunoscut, care oprește complet inserările, și gândirea preventivă.

Eroarea, în practică:nextval: reached maximum value of sequence

95 Senior

VACUUM VERBOSE comenzi raportează tuples: 0 removed, 5000000 remain, 1800000 are dead but not yet removable. Ce împiedică ștergerea rândurilor moarte? Enumeră toate cauzele posibile și cum le verifici.

Răspuns scurt: VACUUM nu poate șterge versiuni pe care cineva le-ar mai putea vedea (orizontul xmin). Cauze: tranzacții vechi sau idle in transaction, sloturi de replicare rămase în urmă, tranzacții pregătite uitate, replici cu hot_standby_feedback și interogări lungi.

VACUUM poate elimina o versiune de rând doar dacă nicio tranzacție, de nicăieri, nu mai poate avea nevoie de ea. Limita se numește orizontul xmin și este minimul dintre:

1. Tranzacții active sau inactive în tranzacție:

SQL
SELECT pid, usename, state, now() - xact_start AS durata, backend_xmin
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;

Tranzacția care ține orizontul poate fi în altă bază de date din același cluster.

2. Sloturi de replicare care rămân în urmă sau sunt inactive:

SQL
SELECT slot_name, slot_type, active, xmin, catalog_xmin,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retinut
FROM pg_replication_slots;

Un slot logic abandonat (un consumator CDC oprit) reține și WAL, deci umple și discul.

3. Tranzacții pregătite (two-phase commit) uitate:

SQL
SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

Le închizi cu COMMIT PREPARED 'gid' sau ROLLBACK PREPARED 'gid'.

4. Replici cu hot_standby_feedback = on: replica trimite primarului xmin-ul interogărilor ei; un raport de 3 ore pe replică oprește curățarea pe primar. Verifici în pg_stat_replication.backend_xmin.

Rezolvare: elimini cauza (închizi sesiunea, ștergi slotul nefolosit cu pg_drop_replication_slot, finalizezi tranzacția pregătită), apoi VACUUM-ul următor curăță. Preventiv: idle_in_transaction_session_timeout, max_slot_wal_keep_size pentru sloturi, alerte pe age(backend_xmin) și pe vechimea tranzacțiilor.

Pe termen lung, același blocaj împiedică și înghețarea rândurilor, ceea ce apropie riscul de wraparound al id-urilor de tranzacție.

Ce urmărește intervievatorul: Se verifică diagnosticul complet al unei probleme de întreținere care afectează simultan performanța și siguranța serverului.

96 Junior

Ce aduce nou PostgreSQL 18 și ce ai folosi imediat într-un proiect nou?

Răspuns scurt: Printre noutăți: I/O asincron pentru citiri (io_method), funcția uuidv7(), coloane generate virtuale ca opțiune implicită, OLD și NEW în RETURNING, skip scan pe indecși B-tree compuși, constrângeri temporale WITHOUT OVERLAPS, păstrarea statisticilor la pg_upgrade și sume de control activate implicit.

Noutăți importante din PostgreSQL 18 (lansat în septembrie 2025):

  • I/O asincron: parametrul io_method (worker implicit, io_uring pe Linux) permite citiri în paralel pentru scanări secvențiale, bitmap heap scan și VACUUM; câștiguri vizibile mai ales pe stocare în cloud, cu latență mare;
  • uuidv7(): UUID-uri ordonate în timp, care se inserează mult mai prietenos în indecșii B-tree decât gen_random_uuid() (v4);
  • coloane generate VIRTUAL, calculate la citire, devin varianta implicită;
  • RETURNING cu OLD și NEW: UPDATE produse SET pret = pret * 1.1 RETURNING old.pret, new.pret arată ambele valori, inclusiv în MERGE;
  • skip scan pentru indecși B-tree compuși, când prima coloană lipsește din filtru;
  • constrângeri temporale: PRIMARY KEY (camera_id, perioada WITHOUT OVERLAPS) și chei străine cu PERIOD;
  • pg_upgrade păstrează statisticile planificatorului, deci după upgrade nu mai ai o perioadă cu planuri proaste până la ANALYZE;
  • sume de control pentru pagini activate implicit la initdb;
  • autentificare OAuth și declararea ca învechită a parolelor MD5;
  • EXPLAIN ANALYZE afișează implicit BUFFERS.

Ce aș folosi imediat: uuidv7() pentru chei publice, RETURNING old/new pentru audit simplu, constrângerile WITHOUT OVERLAPS pentru rezervări în locul unor EXCLUDE scrise manual, și verificarea parametrului io_method pe infrastructura reală.

Ca practică generală: urmărești notele de lansare, testezi upgrade-ul pe o copie a datelor și verifici secțiunea „Migration” pentru schimbări incompatibile, înainte de a actualiza producția.

Ce urmărește intervievatorul: Se verifică dacă te ții la curent cu evoluția platformei pe care lucrezi. Răspunsul nu trebuie să fie exhaustiv, dar trebuie să fie corect.

Situații din practică

Exersează capitolul cu exerciții reale: Curs PostgreSQL online →
97 Junior

Pagina cu lista de comenzi face o interogare pentru comenzi și apoi încă una pentru fiecare comandă, ca să aducă clientul (problema N+1). Cum o observi din PostgreSQL și cum o rezolvi?

Răspuns scurt: În pg_stat_statements vezi o interogare simplă după id cu un număr uriaș de apeluri, fiecare rapid, dar cu timp total mare. Rezolvi cu un JOIN, cu WHERE id = ANY($1) pentru toate id-urile o dată, sau cu încărcare anticipată în ORM.

N+1 înseamnă o interogare pentru listă și apoi N interogări, câte una pentru fiecare element. Fiecare durează poate 0,2 ms în baza de date, dar drumul dus-întors prin rețea, pregătirea și deserializarea fac ca 100 de interogări să dureze 100–300 ms.

Cum o vezi în PostgreSQL:

SQL
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 3) AS medie_ms, left(query, 80)
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Semnalul tipic: o interogare după cheie primară cu calls de ordinul milioanelor, apelată de zeci de ori mai des decât interogarea de listă. Și în logurile aplicației (logarea SQL a ORM-ului) vezi rafale de interogări identice cu parametri diferiți.

Rezolvări:

SQL
-- un singur JOIN
SELECT c.id, c.total, cl.nume FROM comenzi c JOIN clienti cl ON cl.id = c.client_id
WHERE c.creat_la > now() - interval '7 days';

-- sau două interogări în total
SELECT id, nume FROM clienti WHERE id = ANY ($1::bigint[]);

În ORM-uri: JOIN FETCH sau @EntityGraph în JPA/Hibernate, include în Prisma și Sequelize, Include în Entity Framework, select_related/prefetch_related în Django. Atenție la încărcarea anticipată a colecțiilor mari prin JOIN: produce produs cartezian; acolo varianta cu a doua interogare (IN/ANY) e mai bună.

Prevenire: teste care numără interogările per cerere și monitorizarea pg_stat_statements după fiecare lansare.

Ce urmărește intervievatorul: Se verifică legătura dintre codul aplicației și ce vede baza de date, o problemă de performanță extrem de comună.

98 Mid

Pentru cheile primare ale unui tabel nou, echipa ezită între bigint identity, UUID v4 (gen_random_uuid()) și UUID v7 (uuidv7()). Ce argumente aduci?

Răspuns scurt: bigint e compact, rapid și ordonat, dar previzibil și generat doar de baza de date. UUID v4 se generează oriunde, dar are 16 octeți și fragmentează indexul. UUID v7 e ordonat în timp, deci păstrează avantajele UUID fără fragmentare.

bigint GENERATED ALWAYS AS IDENTITY:

  • 8 octeți, comparații rapide, indecși mici;
  • valorile cresc, deci inserările merg mereu la finalul indexului, cu localitate bună în cache;
  • dezavantaje: id-urile expuse în URL-uri dezvăluie volumul (/comenzi/10452) și permit ghicirea altor resurse; generarea are nevoie de baza de date; combinarea datelor din mai multe baze produce coliziuni.

UUID v4 (gen_random_uuid()):

  • generat oriunde (aplicație, client mobil offline), fără coordonare;
  • nu dezvăluie nimic;
  • dezavantaje: 16 octeți, deci indecși și chei străine de două ori mai mari; valorile aleatoare inserate în tot arborele B-tree produc multe pagini parțial pline, mai multe scrieri în WAL și cache ineficient pe tabele mari.

UUID v7 (uuidv7() în PostgreSQL 18, sau generat în aplicație):

  • primii 48 de biți sunt momentul creării în milisecunde, restul aleatori;
  • inserările sunt aproape secvențiale, ca la bigint;
  • tot nu se poate ghici, dar dezvăluie momentul creării (uuid_extract_timestamp), uneori nedorit.

Recomandarea mea:

  • pentru tabele interne, foarte mari, cu multe chei străine: bigint;
  • pentru resurse expuse public sau generate în sisteme distribuite: UUID v7;
  • o variantă frecventă: bigint intern pentru chei străine și o coloană uuid unică pentru identificatorul public.

Tipul se stochează ca uuid, nu ca text: 16 octeți față de 37 și comparații mult mai rapide.

Ce urmărește intervievatorul: Se verifică judecata în alegerea cheilor, cu impact pe performanță și securitate, inclusiv noutățile din PostgreSQL 18.

99 Senior

Proiectezi baza de date pentru o aplicație SaaS cu 3.000 de clienți-firmă, de la foarte mici până la câțiva foarte mari. Compară trei variante: tabele comune cu tenant_id, o schemă per client și o bază de date per client. Ce alegi?

Răspuns scurt: Tabelele comune cu tenant_id și RLS se operează cel mai ușor și se potrivesc multor clienți mici. Schema per client izolează mai bine, dar multiplică obiectele și migrările. Baza de date per client se justifică doar pentru câțiva clienți mari.

Tabele comune, coloana tenant_id:

  • o singură schemă, o singură migrare, conexiuni partajate eficient prin pool;
  • izolarea depinde de aplicație și de RLS; o greșeală poate expune date între clienți;
  • un client foarte mare poate domina statisticile și planurile, iar ștergerea unui client înseamnă DELETE-uri mari;
  • tenant_id trebuie să fie prima coloană în cheile compuse și în indecși; partiționarea pe hash sau listă de tenanți ajută la clienții mari;
  • se poate scala orizontal cu Citus, unde tenant_id devine cheia de distribuție.

Schemă per client:

  • izolare logică mai bună, search_path per conexiune, ștergere simplă (DROP SCHEMA ... CASCADE), backup per client cu pg_dump -n;
  • 3.000 de scheme × 100 de tabele × indecși înseamnă un catalog uriaș: pg_dump lent, autovacuum care trece prin sute de mii de relații, cache-urile de catalog consumă memorie în fiecare sesiune;
  • migrările trebuie rulate de 3.000 de ori și pot eșua la jumătate;
  • pool-urile de conexiuni devin complicate dacă fiecare client are setări diferite.

Bază de date per client:

  • izolare maximă, restaurare și mutare per client, cerințe de rezidență a datelor;
  • conexiunile nu se pot partaja între baze, deci pooling-ul este ineficient; 3.000 de baze pe un server sunt greu de operat.

Ce aleg: tabele comune cu tenant_id și RLS pentru majoritatea clienților, cu posibilitatea de a muta clienții foarte mari sau cu cerințe speciale pe baze de date dedicate (model hibrid). Decid pe baza: numărului de clienți, distribuției mărimii lor, cerințelor de izolare și conformitate și capacității echipei de operare.

Ce urmărește intervievatorul: Se verifică gândirea arhitecturală pe termen lung și cunoașterea limitelor operaționale reale ale PostgreSQL.

100 Mid

Clientul mobil reîncearcă automat cererea de plată când conexiunea se întrerupe, iar uneori se creează două plăți. Cum faci endpoint-ul idempotent folosind PostgreSQL?

Răspuns scurt: Clientul trimite o cheie de idempotență unică pe operație. Serverul o salvează într-un tabel cu cheie unică, în aceeași tranzacție cu plata. O cerere repetată lovește constrângerea și primește răspunsul salvat, fără o plată nouă.

Schema:

SQL
CREATE TABLE cereri_idempotente (
  cheie uuid PRIMARY KEY,
  client_id bigint NOT NULL,
  creat_la timestamptz NOT NULL DEFAULT now(),
  stare text NOT NULL DEFAULT 'in_lucru',
  raspuns jsonb
);

Fluxul, într-o singură tranzacție:

SQL
BEGIN;
INSERT INTO cereri_idempotente (cheie, client_id) VALUES ($1, $2)
ON CONFLICT (cheie) DO NOTHING
RETURNING cheie;
-- dacă nu s-a întors niciun rând: cererea a mai fost primită
--   -> citești raspuns și îl trimiți din nou (sau 409 dacă e încă in_lucru)
-- altfel:
INSERT INTO plati (...) VALUES (...) RETURNING id;
UPDATE cereri_idempotente SET stare = 'gata', raspuns = $3 WHERE cheie = $1;
COMMIT;

De ce funcționează:

  • constrângerea unică este verificată atomic: dacă două cereri identice sosesc simultan, a doua așteaptă după tranzacția primei (indexul unic blochează inserarea concurentă a aceleiași chei) și apoi vede conflictul;
  • dacă prima tranzacție eșuează și face ROLLBACK, cheia dispare, iar o reîncercare poate reuși corect;
  • plata și cheia sunt confirmate împreună: nu există plată fără cheie sau invers.

Detalii:

  • verifici că a doua cerere are aceiași parametri ca prima (salvezi un hash al corpului cererii); altfel e o folosire greșită a cheii;
  • dacă plata implică un serviciu extern (procesatorul de carduri), transmiți aceeași cheie și lui, pentru că tranzacția PostgreSQL nu poate anula un apel extern;
  • ștergi periodic cheile vechi (de exemplu după 24 de ore sau 7 zile), eventual cu partiționare pe zi.

Ce urmărește intervievatorul: Se verifică felul în care folosești garanțiile bazei de date pentru corectitudinea unui sistem distribuit, o întrebare frecventă la interviuri de backend.

Întrebări de interviu pe meserie

Alte tehnologii

← Toate întrebările de interviu

Tudor Andrei Dumitrescu
Cine coordonează pregătirea de interviu

Tudor Andrei Dumitrescu

Senior Software Developer · fondatorul Academiei Programatorilor

LinkedIn
  1. 2019 Junior JavaCognizant Softvision
  2. 2021 Mid JavaCognizant Softvision
  3. 2022 Team LeaderEndava
  4. 2023 Senior Software DeveloperUniCredit
  5. Acum Senior Software DeveloperHCLTech
Certificat de absolvire Instructor / Preparator formare, Tudor Andrei Dumitrescu
Certificat de formator autorizatMinisterul Muncii și Ministerul Educației · 2022
Certificat de absolvire Instructor / Preparator formare, Tudor Andrei Dumitrescu

Certificat de formator autorizat · Ministerul Muncii și Ministerul Educației · 2022

Din 2020 construiește Academia Programatorilor, iar din 2026 și JoburiIT.ro. Întrebările de aici urmăresc ce se cere la interviurile de PostgreSQL, de la junior la mid, iar pe JoburiIT.ro îți poți certifica apoi cunoștințele.

Învață PostgreSQL scriind cod, nu doar citind

Curs PostgreSQL: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 10 lecții sunt gratuite.