This is a viewer only at the moment see the article on how this works.
To update the preview hit Ctrl-Alt-R (or ⌘-Alt-R on Mac) or Enter to refresh. The Save icon lets you save the markdown file to disk
This is a preview from the server running through my markdig pipeline
Saturday, 06 December 2025
I dati gerarchici sono ovunque nello sviluppo del software: commenti filettati, grafici organizzativi, file system, categorie di prodotti e discussioni di forum. L'eterna domanda di "come faccio a memorizzare un albero in un database relazionale?" ha perseguitato gli sviluppatori fin dai primi giorni di SQL, e francamente, non c'è ancora nessuna risposta che renda tutti felici. per la prima volta ha scritto su questo argomento nel 2004, e le sfide fondamentali rimangono le stesse oggi.
Ecco il problema fondamentale: database relazionali pensare in set, non alberi. Come spiega Joe Celko nel suo eccellente libro Pensare in set, SQL opera su tabelle intere, non su singole righe.
Quando si scrive una query SQL, il motore del database funziona su insiemi di righe. È brillante in operazioni come "trovare tutti gli ordini oltre 100" o "unire i clienti ai loro acquisti" - queste sono operazioni impostate che si adattano naturalmente a come funzionano le tabelle. Il risultato è sempre un insieme piatto di righe.
Ma una gerarchia è intrinsecamente ricorsivo. Per trovare tutti i discendenti di un nodo, è necessario:
Questa traversata ricorsiva non mappa naturalmente per impostare le operazioni. Non puoi esprimere "dammi tutti i discendenti a qualsiasi profondità" in un'unica, semplice istruzione SQL senza:
Ogni approccio in questa serie rappresenta un diverso compromesso tra la complessità di scrittura, la complessità di lettura, e lo stoccaggio in alto. Non c'è nessun pranzo gratuito - si sta sempre scambiando un costo per un altro. Per il riferimento definitivo su questo argomento, vedere Joe Celko Alberi e gerarchie in SQL per Smarties, che copre tutti questi approcci in profondità.
Per fare i confronti concreti, useremo i commenti filettati come esempio di esecuzione - qualcosa che questo stesso blog utilizza. Un commento thread potrebbe assomigliare a questo:
flowchart TD
subgraph Post["Post: How to Deploy Docker Containers"]
C1["Comment 1: Great article!<br/>depth 0"]
C2["Comment 2: Thanks!<br/>depth 1"]
C3["Comment 3: Very helpful indeed<br/>depth 1"]
C4["Comment 4: Agreed!<br/>depth 2"]
C5["Comment 5: What about Kubernetes?<br/>depth 0"]
C6["Comment 6: That's covered in part 2<br/>depth 1"]
end
C1 --> C2
C1 --> C3
C3 --> C4
C5 --> C6
style Post stroke:#10b981,stroke-width:2px
style C1 stroke:#6366f1,stroke-width:2px
style C2 stroke:#8b5cf6,stroke-width:2px
style C3 stroke:#8b5cf6,stroke-width:2px
style C4 stroke:#a855f7,stroke-width:2px
style C5 stroke:#6366f1,stroke-width:2px
style C6 stroke:#8b5cf6,stroke-width:2px
Dobbiamo sostenere queste operazioni:
Ogni approccio è coperto in dettaglio nel suo articolo. Ecco un riassunto per aiutarvi a scegliere:
L'approccio più semplice e intuitivo. Ogni riga memorizza un riferimento al suo nodo genitore. E 'quello che la maggior parte degli sviluppatori raggiungono per primo perché mappa naturalmente a come pensiamo alle gerarchie.
Come funziona: Ogni commento ha un valore nullo ParentCommentId colonna. I commenti di radice hanno NULL, le risposte puntano al loro genitore.
Meglio per: Gerarchie poco profonde (meno di 5-6 livelli), frequenti mosse di sotto-albero, quando si vuole che le proprietà di navigazione dell'EF Core funzionino naturalmente.
Compensazioni: Ottenere antenati o discendenti richiede CTE ricorsive o domande multiple. Semplice da scrivere, potenzialmente lento a leggere alberi profondi.
Precomputa e memorizza ogni relazione antenato-discendente in una tabella separata. Invece di capire le relazioni al momento della query, le memorizziamo esplicitamente con la loro profondità.
Come funziona: Un'altra CommentClosure la tabella memorizza (antenator_id, discendente_id, profondità) per ogni coppia. Commento 4 in Commento 1 via Commento 3 avrebbe voci: (1,4,42), (3,41,0), (4,40.
Meglio per: Applicazioni leggere pesanti, quando è necessario interrogare a profondità arbitrarie, quando raramente si spostano sotto alberi.
Compensazioni: Inserti più complessi (deve aggiungere elementi di chiusura), storage cresce con profondità, subtrees in movimento richiede la ricostruzione di chiusure.
Memorizza l'ascendenza completa come una stringa delimitata. Considerala come una memorizzazione dell'indirizzo postale completo piuttosto che solo il nome della strada.
Come funziona: Ogni commento ha un Path colonna come "1/3/7" che significa "root is 1, genitore è 3, questo è 7." Gli antenati possono essere analizzati dalla stringa; discendenti trovati con query LIKE.
Meglio per: Generazione Breadcrumb, quando gli antenati sono interrogati più che discendenti, quando si desidera percorsi di debug leggibili dall'uomo.
Compensazioni: LIKE query può essere lento senza indici adeguati, stringhe di percorso hanno limiti di lunghezza, subtrees in movimento richiede l'aggiornamento di tutti i percorsi discendenti.
Assegna i numeri di contorno destro e sinistro da una traversata di profondità. Tutti i discendenti hanno valori tra I limiti del genitore.
Come funziona: Ogni commento ha Left e Right valori. Un nodo con L=4, R=7 contiene tutti i nodi dove 4 < Sinistra e Destra < 7. I discendenti si trovano con semplici query di intervallo.
Meglio per: Read-heavy, write-rarely scenari come gli alberi di categoria. Eccellente per "get intero sottoalbero in ordine di visualizzazione" query.
Compensazioni: Gli inserti richiedono l'aggiornamento di molte righe (spostare tutti i valori per fare spazio), spostare i sottoalberi è complesso.
L'estensione nativa di PostgreSQL per i dati gerarchici. Come percorsi materializzati ma con ottimizzazione a livello di database e potente corrispondenza dei pattern.
Come funziona: Usa il ltree tipo di dati con percorsi come "1.3.7." Gli indici GiST consentono richieste di antenati/discendenti efficienti utilizzando operatori come @> e <@.
Meglio per: Implementazioni solo PostgreSQL dove le prestazioni sono importanti, quando si hanno bisogno di query corrispondenti ai pattern, quando si desidera il meglio dei percorsi materializzati.
Compensazioni: Solo PostgreSQL, aggiunge dipendenza dall'estensione del database. Nota: Npgsql provider supporta le traduzioni LINQ per l'albero attraverso il LTree tipo, anche se le CTE ricorsive richiedono ancora SQL grezzo.
| Avvicinamento | Inserto | Sottoalbero di domanda | Antenati di query | Sposta sottoalbero | Stoccaggio | Supporto del nucleo dell'impronta ambientale |
|---|---|---|---|---|---|---|
| Elenco di Adjacency | O(1) | O(n) con CTE | O(d) con CTE | O(1) | Minimale | Eccellente |
| Tabella di chiusura | O(d) | O(1) | O(s x d) | O(n x d) | Good | |
| Percorso materializzato | O(1) | O(1)* | O(1) | O(s) | O(d) per nodo | Buono |
| Set nidificati | O(n) | O(1) | O(n) | O(n) | Minimal | Good |
| ltreeCity name (optional, probably does not need a translation) | O(1) | O(1) | O(s) | O(d) per nodo | Buono (via LTree tipo) |
n = nodi totali, d = profondità, s = dimensione sottoalbero *Con indice corretto
flowchart TD
Start([Start]) --> Q1{Shallow tree?<br/>< 5 levels}
Q1 -->|Yes| Q2{Need EF Core<br/>navigation properties?}
Q2 -->|Yes| AL[Adjacency List]
Q2 -->|No| Q3{Performance<br/>critical?}
Q3 -->|No| AL
Q3 -->|Yes| MP[Materialised Path]
Q1 -->|No| Q4{Read-heavy?}
Q4 -->|Yes| Q5{Writes rare?}
Q5 -->|Yes| NS[Nested Sets]
Q5 -->|No| CT[Closure Table]
Q4 -->|No| Q6{PostgreSQL only?}
Q6 -->|Yes| LT[ltree]
Q6 -->|No| Q7{Need breadcrumbs?}
Q7 -->|Yes| MP
Q7 -->|No| CT
style Start stroke:#10b981,stroke-width:2px
style AL stroke:#6366f1,stroke-width:2px
style MP stroke:#8b5cf6,stroke-width:2px
style NS stroke:#ec4899,stroke-width:2px
style CT stroke:#f59e0b,stroke-width:2px
style LT stroke:#14b8a6,stroke-width:2px
Questo blog utilizza un Tabella di chiusura l'approccio per i commenti. La logica:
Vedi Parte 1.2: Tabella di chiusura per i dettagli di attuazione completi.
© 2026 Scott Galloway — Unlicense — All content and source code on this site is free to use, copy, modify, and sell.