Domande Database — Cheat Sheet

Tag: sql, nosql, postgresql, database, indexing, transactions, acid, sharding

Livello: intermedio

TL;DR: Ripasso rapido per colloqui su database relazionali e NoSQL, transazioni ACID, indicizzazione, ottimizzazione query e strategie di scalabilita.

Concetti chiave

I database relazionali organizzano i dati in tabelle con schemi rigidi e relazioni esplicite.

I database NoSQL offrono schemi flessibili ottimizzati per modelli a documenti, chiave-valore, colonne o grafi.

Le proprieta ACID garantiscono affidabilita e consistenza nelle transazioni transazionali.

Gli indici B-Tree velocizzano ricerche per uguaglianza e range query riducendo le letture su disco.

Un indice velocizza le letture ma introduce un overhead computazionale sulle operazioni di scrittura.

La normalizzazione riduce la ridondanza dei dati a scapito di un maggior numero di join.

La denormalizzazione velocizza le query di lettura accettando dati duplicati e complessita di sincronizzazione.

Lo sharding partiziona orizzontalmente i dati su piu nodi fisici distinti.

La replica distribuisce copie del database per garantire alta disponibilita e bilanciare il traffico in lettura.

Il teorema CAP impone la scelta tra Consistency, Availability e Partition Tolerance in sistemi distribuiti.

Domande tipiche e risposte pronte

Q: Qual e la differenza principale tra SQL e NoSQL e quando scegli l'uno o l'altro?R: Scelgo SQL quando la consistenza dei dati, le transazioni ACID e le relazioni strutturate sono prioritarie, mentre punto su NoSQL quando gestisco schemi flessibili, documenti gerarchici o necessito di scalabilita orizzontale nativa per volumi enormi di scrittura.

Q: Cosa rappresentano le proprieta ACID?R: Rappresentano Atomicity, ovvero tutto o niente; Consistency, transizione solo tra stati validi; Isolation, esecuzione concorrente trasparente; Durability, persistenza permanente dei dati committati anche in caso di crash.

Q: Come funziona un indice B-Tree e quando conviene evitarlo?R: Mantiene i dati ordinati in una struttura ad albero bilanciata consentendo ricerche in tempo logaritmico; evito di indicizzare colonne a bassissima cardinalita o tabelle soggette a scritture e inserimenti massivi continui.

Q: Che differenza c'e tra clustered index e non-clustered index?R: Il clustered index definisce l'ordine fisico reale con cui i dati sono memorizzati su disco, mentre il non-clustered index mantiene una struttura separata con puntatori alla riga effettiva.

Q: Come affronti il problema della query lenta in produzione?R: Analizzo il piano di esecuzione con EXPLAIN ANALYZE per identificare sequential scan anomali, verifico la copertura degli indici, elimino letture inutili ed evito subquery non necessarie.

Q: Cosa si intende per problema N+1 nelle query e come si risolve?R: Avviene quando un'applicazione esegue una query iniziale e poi una query separata per ciascun record correlato; lo risolvo usando join espliciti o meccanismi di eager loading nel data layer.

Q: Qual e la differenza tra optimistic locking e pessimistic locking?R: Il pessimistic locking blocca fisicamente le righe durante l'intera operazione, mentre l'optimistic locking verifica la versione del record al momento del commit scatenando un rollback in caso di conflitto concorrente.

Q: Quando ha senso introdurre una cache Redis davanti al database?R: Quando rilevo carichi di lettura elevati su dati calcolati o query frequenti con bassa frequenza di mutazione, proteggendo il database da bottleneck computazionali.

Insidie e errori comuni

Usare SELECT * recuperando colonne superflue che impediscono l'uso di indici a copertura pura.

Applicare funzioni o conversioni di tipo direttamente sulle colonne indicizzate all'interno della clausola WHERE.

Dimenticare la gestione dell'invalidazione della cache introducendo disallineamenti di dati persistenti.

Scegliere chiavi primarie non sequenziali come UUID v4 provocando elevata frammentazione delle pagine dell'indice.

Utilizzare livelli di isolamento transazionale eccessivi provocando deadlock e degrado della concorrenza.

Snippet e comandiSQL

```-- Analisi del piano di esecuzione di una query

EXPLAIN ANALYZE

SELECT id, email, created_at

FROM users

WHERE status = 'active'

ORDER BY created_at DESC

LIMIT 50;

-- Creazione di un indice composto con ordinamento

CREATE INDEX idx_users_status_created

ON users (status, created_at DESC);

-- Esempio di transazione atomica con lock esplicito

BEGIN;

SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 42;

UPDATE accounts SET balance = balance + 100 WHERE id = 99;

COMMIT;

```

Da ripassare

[ ] Piani di esecuzione e interpretazione di EXPLAIN ANALYZE

[ ] Differenze tra livelli di isolamento (Read Committed, Repeatable Read, Serializable)

[ ] Strategie di partizionamento tabelle e table sharding

[x] Principi ACID e gestione delle transazioni

[x] Differenza funzionale tra Primary Key, Unique Key e Foreign Key