PostgreSQL — Cheat Sheet

Tag: postgresql, sql, database, indexing, performance, acid

Livello: intermedio

TL;DR: Riferimento rapido su architettura relazionale di PostgreSQL, ottimizzazione query, indicizzazione e gestione concorrenza per colloqui tecnici.

Concetti chiave

Database relazionale ad oggetti conforme agli standard SQL e ACID.

Modello MVCC per garantire isolamento delle transazioni senza blocchi in lettura.

Supporto avanzato a tipi semi-strutturati tramite JSONB con indicizzazione GIN.

Indici B-Tree per confronti standard, GIN e GiST per dati complessi o full-text.

Planner ed Optimizer basati su statistiche di tabella raccolte dal processo ANALYZE.

Write-Ahead Logging (WAL) per garantire durabilità, crash recovery e replica.

Processo di autovacuum essenziale per rimuovere tuple morte e prevenire il bloat.

Connection pooling consigliato per scalare connessioni concorrenti evitando overhead di processi.

Domande tipiche e risposte pronte

Q: Come affronti una query lenta in PostgreSQL?R: Eseguo EXPLAIN (ANALYZE, BUFFERS) per verificare il piano effettivo, cerco Sequential Scan anomali e valuto la creazione di indici mirati o l'aggiornamento delle statistiche con ANALYZE.

Q: Qual è la differenza sostanziale tra JSON e JSONB?R: JSON memorizza il testo esatto preservando formattazione e duplicati, mentre JSONB memorizza una rappresentazione binaria parsata, più veloce da interrogare e compatibile con indici GIN.

Q: Perché il comando VACUUM è fondamentale in PostgreSQL?R: MVCC crea nuove versioni di riga ad ogni UPDATE o DELETE; VACUUM recupera lo spazio delle tuple non più visibili, prevenendo il bloat e il transaction ID wraparound.

Insidie e errori comuni

Mancanza di indici sulle Foreign Key che causa Sequential Scan nei JOIN e lock estesi durante DELETE.

Uso improprio di SELECT * che bypassa gli Index Only Scan e satura la banda di I/O.

Dimenticare l'opzione CONCURRENTLY durante la creazione di indici in produzione, provocando lock in scrittura sulla tabella.

Configurazione errata di work_mem che forza l'ordinamento su disco (spill to disk) degradando le performance.

Accumulo di tuple morte dovuto a un autovacuum sottodimensionato o a transazioni rimaste aperte troppo a lungo.

Snippet e comandiSQL

```-- Analisi dettagliata dell'esecuzione con hit di buffer cache

EXPLAIN (ANALYZE, BUFFERS, COSTS)

SELECT * FROM users WHERE email = 'test@example.com';

-- Creazione di un indice B-Tree senza bloccare le scritture concorrenti

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Indice GIN per ricerche efficienti dentro documenti JSONB

CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata);

-- Query per identificare le query attualmente attive o bloccate

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state

FROM pg_stat_activity

WHERE state != 'idle';

```

Da ripassare

[ ] Livelli di isolamento delle transazioni (Read Committed, Repeatable Read, Serializable)

[ ] Differenze architetturali tra B-Tree, GIN, GiST e BRIN

[ ] Meccanismo di Hot Standby e replica in streaming via WAL

[ ] Parametri chiave di tuning: shared_buffers, work_mem, maintenance_work_mem

[x] Sintassi e utilizzo standard di EXPLAIN ANALYZE

[x] Operatori e funzioni native per manipolazione di JSONB