È una cattiva pratica memorizzare grandi file (10 MB) in un database?

Ragioni a favore dell'archiviazione dei file nel database:

1. Consistenza ACID incluso il rollback di un aggiornamento che è complicato quando i file vengono archiviati all'esterno del database. Questo non deve essere sorvolato leggermente. Avere i file e il database sincronizzati e in grado di partecipare alle transazioni può essere molto utile.
2. I file vanno con il database e non possono essere resi orfani da esso.
3. I backup includono automaticamente i file binari.

Motivo contro la memorizzazione di file nel database:

1. Le dimensioni di un file binario differiscono tra i database. Su SQL Server, quando non si utilizza l'oggetto FILESTREAM, ad esempio, è 2 GB. Se gli utenti hanno bisogno di archiviare file più grandi (come ad esempio un film), devi saltare attraverso i cerchi per fare in modo che avvenga quella magia.
2. Aumenta le dimensioni del database. Un concetto generale da tenere a mente: Il livello di conoscenza richiesto per mantenere un database sale in proporzione alla dimensione del database. Ie, i database di grandi dimensioni sono più complicati da gestire rispetto ai piccoli database. La memorizzazione dei file nel database può rendere il database molto più grande. Anche se fosse sufficiente un backup completo giornaliero, con una dimensione del database più ampia, potresti non essere più in grado di farlo. Potrebbe essere necessario prendere in considerazione l'inserimento dei file in un gruppo di file diverso (se il database lo supporta), modificare i backup per separare il backup dei dati dal backup dei file ecc. Nessuna di queste cose è impossibile da imparare, ma fare aggiungere complessità alla manutenzione che significa costi per l'azienda. I database più grandi consumano anche più memoria mentre cercano di inserire più dati nella memoria possibile.
3. La portabilità può essere un problema se si utilizzano funzionalità specifiche del sistema come FILESTREAM di SQL Server e si deve eseguire la migrazione a un altro sistema di database.
4. Il codice che scrive i file nel database può essere un problema. Una società per la quale ho consultato non così tante lune fa, a un certo punto, ha collegato un frontend di Microsoft Access al proprio server di database e ha utilizzato la possibilità di accedere a "qualsiasi cosa" utilizzando il proprio controllo Ole dell'oggetto. Più tardi cambiarono per usare un controllo diverso che si basava ancora su Ole. Molto più tardi qualcuno cambiò l'interfaccia per memorizzare il file binario grezzo. Estrarre quegli oggetti di Ole era un nuovo livello di inferno. Quando si archiviano i file nel file system, non è disponibile un livello aggiuntivo per avvolgere / modificare / modificare il file sorgente.
5. È più complicato pubblicare i file su un sito web. Per farlo con le colonne binarie, devi scrivere un gestore per lo streaming del file binario dal database. Puoi farlo anche se archivi i percorsi dei file ma non hai per farlo. Ancora una volta, l'aggiunta di un gestore non è impossibile ma aggiunge complessità ed è un altro punto di errore.
6. Non puoi sfruttare l'archiviazione cloud. Supponiamo che un giorno si desideri archiviare i file in un bucket Amazon S3. Se ciò che memorizzi nel database sono percorsi di file, ti viene data la possibilità di cambiarli in percorsi su S3. Per quanto ne so, non è possibile in nessuno scenario con alcun DBMS.

L'IMO, ritenendo che l'archiviazione dei file nel database non sia "cattiva" richiede ulteriori informazioni sulle circostanze e sui requisiti. Le dimensioni e / o il numero di file saranno sempre piccoli? Non ci sono piani per l'utilizzo del cloud storage? I file verranno pubblicati su un sito Web o un eseguibile binario come un'applicazione Windows?

In generale, la mia esperienza ha rilevato che la memorizzazione di percorsi è meno costosa per l'azienda, anche tenendo conto della mancanza di ACID e della possibilità di orfani. Tuttavia, ciò non significa che Internet non sia legione con storie di mancanza di controllo ACID che non funzionano correttamente con lo storage di file, ma in generale significa che la soluzione è più facile da costruire, comprendere e mantenere.

In molti casi, questa è una cattiva idea. Gonfierà i file del database e causerà diversi problemi di prestazioni. Se blocchi i blob in una tabella con un numero elevato di colonne è ancora peggio.

Tuttavia! Alcuni database, come SQL Server hanno un tipo di colonna FILESTREAM. In questo caso, i dati vengono effettivamente memorizzati in un file separato sul server del database e nella tabella viene salvato solo un ID del file. In questo caso non vedo molti motivi per non conservare i dati nel server SQL. I file vengono automaticamente inclusi come parte del backup del server e il database e i file non sono mai fuori sincrono. Il problema con il suggerimento di Tony di memorizzare i nomi dei file è che il database e il filesystem possono andare fuori sincrono. Il database rivendicherà che un file esiste quando è stato cancellato su disco. Se un processo sta modificando il database e poi si blocca, i file e il database non corrisponderanno (cioè nessun ACID con file all'esterno di un database).

Sì, è una cattiva pratica.

Impatto sulle prestazioni sul DB:

• se fai un SELECT con qualsiasi colonna BLOB, dovrai sempre fare un accesso al disco, mentre senza i BLOB hai la possibilità di ottenere dati direttamente dalla RAM (il DB ad alto throughput sarà ottimizzato per adattare le tabelle nella RAM);
• la replicazione sarà lenta, il ritardo di replica alto, in quanto dovrà spingere BLOB in slave. Un elevato ritardo di replica causerà tutti i tipi di condizioni di gara e altri problemi di sincronizzazione, a meno che tu non lo prendi esplicitamente in considerazione;
• I backup / ripristino del DB impiegheranno molto più tempo;

Vantaggio velocità - nessuno ! Mentre alcuni filesystem più datati non gestiscono bene le directory con milioni di file, la maggior parte dei moderni non ha alcun problema e utilizza infatti lo stesso tipo di strutture dati dei BD (in genere B-trees). Per esempio ext4 (file system Linux di default) usa Htree .

Conclusione: ostacolerà le prestazioni del DB e non migliorerà le prestazioni di recupero dei file.

Inoltre, dal momento che stai parlando di un'applicazione web - che serve file statici direttamente dal filesystem usando il moderno web server, che può fare sendfile() syscall è tremendo miglioramento delle prestazioni. Questo ovviamente non è possibile se stai recuperando i file dal DB. Prendi in considerazione ad esempio questo benchmark , che mostra Ngnix che fa 25K req / s con 1000 connessioni simultanee su un laptop di fascia bassa. Quel tipo di carico friggerebbe qualsiasi tipo di DB.

Sarei pragmatico al riguardo e seguo il principio "non ottimizzare ancora". Crea la soluzione che ha un senso al momento e una che hai le risorse per lo sviluppo da implementare correttamente. Ci sono molti potenziali problemi . Ma quelli non necessariamente diventano problemi reali. Per esempio. Probabilmente non sarebbe un problema se hai 100 utenti. potrebbe essere un problema se hai 100.000 o 10.000.000 di utenti. Ma in quest'ultimo caso, ci dovrebbe essere una base per maggiori risorse di sviluppo per affrontare tutti i problemi.

Ma la memorizzazione dei dati nel database ti solleva dall'affrontare altri problemi, ad es. dove dovrebbero essere archiviati i file, come dovrebbero essere sottoposti a backup, ecc. Poiché stai scrivendo un'applicazione web, sarebbe una buona idea per motivi di sicurezza assicurarsi che il processo che ospita l'applicazione non abbia accesso in scrittura al file sistema, quindi è necessario configurare il server in modo che il processo abbia accesso in lettura / scrittura alla cartella in cui sono archiviati i dati.

Personalmente sceglierei di archiviare i dati nel database, ma assicurati che i BLOB non vengano letti finché non sono realmente necessari, cioè non "SELECT * FROM ..." eseguito su quelle tabelle contenenti blog. E farei in modo che il design faciliti lo spostamento dei dati dal database, nel filesystem, se si verificano problemi di prestazioni. Ad esempio, memorizza le informazioni sul file in una tabella File separata, mantenendo così le informazioni del file lontane da altre entità aziendali.

Supponendo che tu abbia una classe File per rappresentare un file letto nel database, allora l'impatto sulla codifica di spostarlo in seguito sarà minimo.

Microsoft ha rilasciato un white paper su questo qualche anno fa. Si concentra su SqlServer, ma è possibile trovare alcune informazioni interessanti in là:

To BLOB or not to BLOB? Large Object Storage in a Database or a Filesystem?

Una versione molto concisa della loro conclusione è:

When comparing the NTFS file system and SQL Server 2005, BLOBS smaller than 256KB are more efficiently handled by SQL Server, while NTFS is more efficient for BLOBS larger than 1MB.

Ti consiglierei di scrivere alcuni piccoli test per il tuo caso d'uso particolare. Ricorda che devi fare attenzione agli effetti di memorizzazione nella cache. (Sono rimasto stupito la prima volta che ho raggiunto velocità di salvataggio su disco che sembravano avere un throughput più elevato di quanto fosse fisicamente possibile!)

La vecchia saggezza convenzionale di archiviare file al di fuori del database potrebbe non essere più valida. In linea di principio, preferirei l'integrità alla velocità e, con un moderno DBMS, puoi avere entrambi.

Tom Kyte sembra concordare :

I know of no advantages to storing data I want to keep for a long time outside of a database.

If it is in the database I can

be sure it is professionally managed

backed up

recoverable (with the rest of the data)

secured

scalable (try putting 100,000 documents in a single directory, now, put them in table - which one 'scales' - it is not the directory)

I can undelete (flashback) easily

I have locking

I have read consistency...

Sì.

Se servi un file dal tuo filesystem, il tuo server Web può usare il codice del kernel come sendfile () su BSD o Linux per copiare il file direttamente nel socket. È molto veloce e molto efficiente.

Servire i file fuori dal database significa che devi copiare i dati dal disco del server del database alla memoria del server del database, quindi dalla memoria del server db alla porta di rete del server db, quindi dalla rete al processo del server Web, quindi uscire di nuovo alla connessione di rete in uscita.

A meno che tu non abbia una buona ragione per non farlo, è sempre meglio servire file statici dal file system.

Il famoso Tom Kyte ha scritto che loro (l'Oracle) stanno usando il database Oracle come file server e funziona perfettamente bene, anche più velocemente del normale filesystem, con piena transazionalità, nessuna perdita di prestazioni e con un singolo backup.

Sì, ma nota che sono i produttori del DB Oracle e per qualsiasi altro utente ci sono problemi di costo. L'utilizzo di DB commerciali come Oracle per la memorizzazione di file è semplicemente inefficace.

Tuttavia, con PostgreSQL, ad esempio, puoi semplicemente eseguire un'altra istanza DB solo per l'archiviazione blob. Hai quindi pieno supporto transazionale. Ma la transazionalità costa spazio al DB. È necessario che il database memorizzi più istanze di blob per più transazioni simultanee. Su PostgreSQL è il più doloroso, dato che questo database memorizza i duplicati dei BLOB creati per la transazione, anche se non sono più necessari, fino a quando il processo VACUUM non viene eseguito.

Con la memorizzazione del filesystem, d'altra parte, devi essere molto attento quando qualcuno modifica il file, perché la transazione può essere ripristinata e la copia del file deve essere conservata fino a quando la vecchia versione non è più visibile.

Nel sistema in cui i file vengono solo aggiunti e cancellati e l'accesso transazionale ai file non è un problema, l'archiviazione del filesystem sarà IMHO la scelta migliore.

Di solito è meglio memorizzare BLOB di grandi dimensioni in una tabella separata e mantenere semplicemente un riferimento a chiave esterna al BLOB nella tabella principale. In questo modo, è ancora possibile recuperare il file dal database (quindi non è necessario alcun codice speciale) ed evitare i problemi relativi alle dipendenze del DB esterno (mantenendo il DB e il filesystem sincronizzati, ecc.), Ma si incorre in tale sovraccarico se ti unisci esplicitamente a quel tavolo (o fai una chiamata separata). 10 MB non è molto grande, i database commerciali più moderni non avranno problemi. L'unica ragione per cui archiviare un file nel filesystem è ridurre la larghezza di banda del database. Se il tuo database sta mischiando molti di questi file, allora potresti dover dividere il carico di lavoro e archiviare solo un descrittore di file di qualche tipo. Quindi è possibile effettuare una chiamata separata per caricare il file da un altro server, in modo da non vincolare le connessioni del database (e le connessioni di rete sul server del database) con tutti i trasferimenti di file.

Potresti incontrare alcuni di questi problemi:

• Fare un SELECT * che coinvolge la riga con il blob di grandi dimensioni richiede molto tempo, anche se non hai bisogno del blob (Naturalmente dovresti fare una selezione specifica, ma a volte le applicazioni sono scritte in questo modo)
• L'esecuzione di un backup può richiedere molto più tempo. A seconda delle esigenze, potrebbe essere necessario bloccare le tabelle per il tempo di backup, quindi è consigliabile mantenere basso il tempo di backup
• Il ripristino richiederà molto più tempo.
• Se si esaurisce lo spazio, è necessario pensare in qualche modo (magari spostando l'intero database su un nuovo server) per risolvere questo problema. Memorizzando i file sul file system è sempre possibile montare un altro disco rigido e impostare i collegamenti software.
• Cercare semplicemente un file per il debug o altre informazioni non è così facile. Questo include anche script che potrebbero non avere accesso al database ma necessitano di alcune informazioni da vari file.

Naturalmente ottieni anche alcuni vantaggi:

• Backup di dati e file in cui sono sincronizzati
• Rimuovere il file senza che il database sappia non è possibile
• Non devi leggere il file dal disco ma puoi farlo in una istruzione sql
• Puoi scaricare il database, includere il dump nel tuo ambiente di sviluppo e avere tutte le dipendenze proprio lì

Personalmente non lo faccio perché trovo i contro molto più pesanti dei professionisti. Ma come detto sopra, dipende totalmente dal tuo caso d'uso e simili.

Alcuni sistemi di gestione dei contenuti di Enterpirse, come SiteCore, stanno utilizzando un database per memorizzare i dati della pagina e un altro database per memorizzare i file. Stanno usando MS SQL Server.

Per l'implementazione pratica, ecco cosa potresti preoccupare:

Benefici:

1. Tutti i contenuti dei file sono sicuramente sincronizzati con il tuo tavolo. Come indicato sopra, il backup dei dati è assolutamente conveniente in quanto non è necessario mantenere i dati sincronizzati con il file system.
2. Dalla codifica, puoi ottenere il contenuto del file direttamente da una selezione SQL.
3. Da una query, puoi persino filtrare il contenuto del file o le sue dimensioni esplicitamente dall'istruzione SQL.

Svantaggi:

1. Confrontato con un database con la quale la struttura è semanticamente uguale ma non memorizza il contenuto del file, il database tende a consumare radicalmente più memoria quando si esegue una query.
2. Il backup automatico può causare problemi di prestazioni ma non di molto. Immaginiamo che il tuo server di database esegua il backup delle cose ogni 6 ore e quei database che hai memorizzino file da 10-MB per record. Quello scenario non è quello che vuoi.