SQLite vs MySQL vs PostgreSQL: un confronto tra sistemi di gestione di database relazionali

introduzione

Il modello di dati relazionale, che organizza i dati in tabelle di righe e colonne, predomina negli strumenti di gestione dei database. Oggi ci sono altri modelli di dati, tra cui rimangono dominanti per l'archiviazione e la gestione dei dati in tutto il mondo.

Questo articolo confronta e contrappone tre degli RDBMS open source più ampiamente implementati: PostgreSQL. Nello specifico, esplorerà i tipi di dati utilizzati da ciascun RDBMS, i loro vantaggi e svantaggi e le situazioni in cui sono ottimizzati al meglio.

Qualcosa sui sistemi di gestione dei database

I database sono raggruppamenti di informazioni o dati modellati logicamente. Un sistema di gestione di database (DBMS), invece, è un programma per computer che interagisce con un database. Un DBMS consente di controllare l'accesso a un database, scrivere dati, eseguire query ed eseguire qualsiasi altra attività relativa alla gestione del database.

Sebbene i sistemi di gestione dei database siano spesso indicati come database, i due termini non sono intercambiabili. Un database può essere qualsiasi raccolta di dati, non solo uno memorizzato su un computer. Al contrario, un DBMS si riferisce specificamente al software che consente di interagire con un database.

Tutti i sistemi di gestione dei database hanno un modello sottostante che struttura il modo in cui i dati vengono archiviati e vi si accede. Un sistema di gestione di database relazionali è un DBMS che utilizza il modello di dati relazionale. In questo modello relazionale, i dati sono organizzati in tabelle. Le tabelle, nel contesto degli RDBMS, sono chiamate più formalmente relazioni. Una relazione è un insieme di tuple, che sono le righe di una tabella, e ogni tupla condivide un insieme di attributi, che sono le colonne di una tabella:

La maggior parte dei database relazionali utilizza il linguaggio di query strutturato (SQL) per gestire e interrogare i dati. Tuttavia, molti RDBMS utilizzano il proprio particolare dialetto di SQL, che può avere alcune limitazioni o estensioni. Queste estensioni in genere includono funzionalità extra che consentono agli utenti di eseguire operazioni più complesse rispetto a quelle che potrebbero altrimenti fare con SQL standard.

Nota: il termine \SQL standard compare più volte in questa guida. Gli standard SQL sono mantenuti congiuntamente dalla Commissione elettrotecnica internazionale (IEC). Ogni volta che questo articolo menziona \SQL standard o \lo standard SQL, si riferisce alla versione corrente dello standard SQL pubblicato da questi organismi.

Va notato che lo standard SQL completo è ampio e complesso: la piena conformità a SQL:2011 richiede 179 funzionalità. Per questo motivo, la maggior parte degli RDBMS non supporta l'intero standard, sebbene alcuni si avvicinino maggiormente alla piena conformità rispetto ad altri.

Tipi di dati e vincoli

A ogni colonna viene assegnato un tipo di dati che determina il tipo di voci consentite in quella colonna. Diversi RDBMS implementano diversi tipi di dati, che non sono sempre direttamente intercambiabili. Alcuni tipi di dati comuni includono date, stringhe, numeri interi e valori booleani.

La memorizzazione di numeri interi in un database è più sfumata rispetto all'inserimento di numeri in una tabella. I tipi di dati numerici possono essere con segno, nel senso che possono rappresentare sia numeri positivi che negativi, o senza segno, il che significa che possono rappresentare solo numeri positivi. Ad esempio, il tipo di dati tinyint di MySQL può contenere 8 bit di dati, il che equivale a 256 possibili valori. L'intervallo con segno di questo tipo di dati va da -128 a 127, mentre l'intervallo senza segno va da 0 a 255.

Essere in grado di controllare quali dati sono ammessi in un database è importante. A volte, un amministratore di database impone un vincolo su una tabella per limitare i valori che possono essere inseriti in essa. Un vincolo si applica in genere a una particolare colonna, ma alcuni vincoli possono essere applicati anche a un'intera tabella. Di seguito sono riportati alcuni vincoli comunemente utilizzati in SQL:

• UNIQUE: l'applicazione di questo vincolo a una colonna assicura che nessuna voce in quella colonna sia identica.
• NOT NULL: questo vincolo garantisce che una colonna non contenga voci NULL.
• PRIMARY KEY: una combinazione di UNIQUE e NOT NULL, il vincolo PRIMARY KEY assicura che nessuna voce nella colonna è NULL e che ogni voce è distinta.
• FOREIGN KEY: Una FOREIGN KEY è una colonna in una tabella che fa riferimento alla PRIMARY KEY di un'altra tabella. Questo vincolo viene utilizzato per collegare insieme due tabelle. Le voci della colonna FOREIGN KEY devono già esistere nella colonna padre PRIMARY KEY affinché il processo di scrittura abbia esito positivo.
• CHECK: questo vincolo limita l'intervallo di valori che possono essere inseriti in una colonna. Ad esempio, se la tua applicazione è destinata solo ai residenti in Alaska, puoi aggiungere un vincolo CHECK su una colonna del codice postale per consentire solo voci comprese tra 99501 e 99950.

Se desideri saperne di più sui sistemi di gestione dei database, dai un'occhiata al nostro articolo su Un confronto tra sistemi e modelli di gestione dei database NoSQL.

Ora che abbiamo trattato i sistemi di gestione dei database relazionali in generale, passiamo al primo dei tre database relazionali open source che questo articolo tratterà: SQLite.

SQLite

SQLite è un RDBMS autonomo, basato su file e completamente open source noto per la sua portabilità, affidabilità e prestazioni elevate anche in ambienti con poca memoria. Le sue transazioni sono conformi ad ACID, anche nei casi in cui il sistema si blocca o subisce un'interruzione di corrente.

Il sito Web del progetto SQLite lo descrive come un database senza server. La maggior parte dei motori di database relazionali sono implementati come un processo server in cui i programmi comunicano con il server host attraverso una comunicazione tra processi che inoltra le richieste. Al contrario, SQLite consente a qualsiasi processo che acceda al database per leggere e scrivere direttamente sul file del disco del database.Ciò semplifica il processo di installazione di SQLite, poiché elimina qualsiasi necessità di configurare un processo del server.Allo stesso modo, non è necessaria alcuna configurazione per i programmi che utilizzeranno il database SQLite: tutto ciò di cui hanno bisogno è l'accesso a il disco.

SQLite è un software gratuito e open source e non è richiesta alcuna licenza speciale per utilizzarlo. Tuttavia, il progetto offre diverse estensioni, ciascuna a un costo una tantum, che aiutano con la compressione e la crittografia. Inoltre, il progetto offre vari pacchetti di supporto commerciale, ciascuno a fronte di una quota annuale.

Tipi di dati supportati da SQLite

SQLite consente una varietà di tipi di dati, organizzati nelle seguenti classi di archiviazione:

Nel contesto di SQLite, i termini \classe di archiviazione e \tipo di dati sono considerati intercambiabili. Se desideri saperne di più sui tipi di dati di SQLite e sull'affinità di tipo SQLite, consulta la documentazione ufficiale di SQLite sull'argomento.

Vantaggi di SQLite

• Ingombro ridotto: come suggerisce il nome, la libreria SQLite è molto leggera. Anche se lo spazio che utilizza varia a seconda del sistema in cui è installato, può occupare meno di 600 KiB di spazio. Inoltre, è completamente autonomo, il che significa che non ci sono dipendenze esterne che devi installare sul tuo sistema affinché SQLite funzioni.
• Facile da usare: SQLite è talvolta descritto come un database a \configurazione zero pronto per essere utilizzato immediatamente. SQLite non viene eseguito come un processo del server, il che significa che non deve mai essere interrotto, avviato , o riavviato e non viene fornito con alcun file di configurazione che deve essere gestito. Queste funzionalità aiutano a semplificare il percorso dall'installazione di SQLite all'integrazione con un'applicazione.
• Portatile: a differenza di altri sistemi di gestione dei database, che in genere memorizzano i dati come un grande batch di file separati, un intero database SQLite viene archiviato in un singolo file. Questo file può trovarsi ovunque in una gerarchia di directory e può essere condiviso tramite supporto rimovibile o protocollo di trasferimento file.

Svantaggi di SQLite

• Concorrenza limitata: sebbene più processi possano accedere e interrogare un database SQLite contemporaneamente, solo un processo alla volta può apportare modifiche al database. Ciò significa che, sebbene SQLite supporti una maggiore concorrenza rispetto alla maggior parte degli altri sistemi di gestione di database incorporati, non può supportare tanto quanto gli RDBMS client/server come MySQL o PostgreSQL.
• Nessuna gestione degli utenti: i sistemi di database sono spesso dotati di supporto per utenti o connessioni gestite con privilegi di accesso predefiniti al database e alle tabelle. Poiché SQLite legge e scrive direttamente su un normale file su disco, le uniche autorizzazioni di accesso applicabili sono le tipiche autorizzazioni di accesso del sistema operativo sottostante. Ciò rende SQLite una scelta sbagliata per le applicazioni che richiedono più utenti con autorizzazioni di accesso speciali.
• Sicurezza: un motore di database che utilizza un server può, in alcuni casi, fornire una migliore protezione dai bug nell'applicazione client rispetto a un database senza server come SQLite. Ad esempio, i puntatori vaganti in un client non possono danneggiare la memoria sul server. Inoltre, poiché un server è un singolo processo persistente, un database client-server può controllare l'accesso ai dati con maggiore precisione rispetto a un database senza server. Ciò consente un blocco più granulare e una migliore concorrenza.

Quando utilizzare SQLite

• Applicazioni incorporate: SQLite è un'ottima scelta di database per applicazioni che necessitano di portabilità e non richiedono espansioni future. Gli esempi includono applicazioni locali per utente singolo, applicazioni mobili o giochi.
• Sostituzione dell'accesso al disco: nei casi in cui un'applicazione deve leggere e scrivere file direttamente sul disco, può essere vantaggioso utilizzare SQLite per le funzionalità aggiuntive e la semplicità che deriva dall'utilizzo di SQL.
• Test: per molte applicazioni può essere eccessivo testare la loro funzionalità con un DBMS che utilizza un processo server aggiuntivo. SQLite ha una modalità in memoria che può essere utilizzata per eseguire test rapidamente senza il sovraccarico delle operazioni effettive del database, rendendolo una scelta ideale per i test.

Quando non usare SQLite

• Lavorare con molti dati: SQLite può supportare tecnicamente un database di dimensioni fino a 140 TB, a condizione che anche l'unità disco e il file system supportino i requisiti di dimensione del database. Tuttavia, il sito Web SQLite consiglia di ospitare qualsiasi database che si avvicini a 1 TB su un database client-server centralizzato, poiché un database SQLite di tali dimensioni o superiore sarebbe difficile da gestire.
• Volumi di scrittura elevati: SQLite consente l'esecuzione di una sola operazione di scrittura alla volta, il che ne limita notevolmente il throughput. Se la tua applicazione richiede molte operazioni di scrittura o più writer simultanei, SQLite potrebbe non essere adeguato alle tue esigenze.
• È richiesto l'accesso alla rete: poiché SQLite è un database senza server, non fornisce l'accesso diretto alla rete ai suoi dati. Questo accesso è integrato nell'applicazione. Se i dati in SQLite si trovano su una macchina separata dall'applicazione, richiederanno un collegamento motore-disco a larghezza di banda elevata attraverso la rete. Questa è una soluzione costosa e inefficiente e in questi casi un DBMS client-server potrebbe essere una scelta migliore.

MySQL

Secondo la comunità di sviluppatori, così come l'abbondanza di risorse online relative a MySQL.

MySQL è stato progettato per la velocità e l'affidabilità, a scapito della piena aderenza allo standard SQL. Gli sviluppatori MySQL lavorano continuamente per una maggiore aderenza allo standard SQL, ma è ancora in ritardo rispetto ad altre implementazioni SQL. Tuttavia, viene fornito con varie modalità ed estensioni SQL che lo avvicinano alla conformità.

A differenza delle applicazioni che utilizzano SQLite, le applicazioni che utilizzano un database MySQL vi accedono tramite un processo demone separato. Poiché il processo del server si trova tra il database e altre applicazioni, consente un maggiore controllo su chi ha accesso al database.

MySQL ha ispirato una vasta gamma di applicazioni, strumenti e librerie integrate di terze parti che ne estendono le funzionalità e ne facilitano l'utilizzo. Alcuni degli strumenti di terze parti più utilizzati sono HeidiSQL.

Tipi di dati supportati da MySQL

I tipi di dati di MySQL possono essere organizzati in tre grandi categorie: tipi numerici, tipi di data e ora e tipi di stringa.

Tipi numerici:

Tipi di data e ora:

Tipi di stringhe:

Vantaggi di MySQL

• Popolarità e facilità d'uso: essendo uno dei sistemi di database più famosi al mondo, non mancano gli amministratori di database che hanno esperienza di lavoro con MySQL. Allo stesso modo, c'è un'abbondanza di documentazione stampata e online su come installare e gestire un database MySQL. Ciò include una serie di strumenti di terze parti, come phpMyAdmin, che mirano a semplificare il processo di introduzione al database.
• Sicurezza: MySQL viene installato con uno script che ti aiuta a migliorare la sicurezza del tuo database impostando il livello di sicurezza della password dell'installazione, definendo una password per l'utente root, rimuovendo gli account anonimi e rimuovendo i database di prova che sono, per impostazione predefinita, , accessibile a tutti gli utenti. Inoltre, a differenza di SQLite, MySQL supporta la gestione degli utenti e consente di concedere privilegi di accesso utente per utente.
• Velocità: scegliendo di non implementare determinate funzionalità di SQL, gli sviluppatori di MySQL sono stati in grado di dare priorità alla velocità. Mentre test di benchmark più recenti mostrano che altri RDBMS come PostgreSQL possono eguagliare o almeno avvicinarsi a MySQL in termini di velocità, MySQL detiene ancora la reputazione di soluzione di database estremamente veloce.
• Replica: MySQL supporta diversi tipi di ridimensionamento orizzontale del proprio database.

Svantaggi di MySQL

• Limiti noti: poiché MySQL è stato progettato per la velocità e la facilità d'uso piuttosto che per la piena conformità SQL, presenta alcune limitazioni funzionali. Ad esempio, manca il supporto per le clausole FULL JOIN.
• Licenza e funzionalità proprietarie: MySQL è un software con doppia licenza, con un'edizione comunitaria gratuita e open source con licenza GPLv2 e diverse edizioni commerciali a pagamento rilasciate con licenze proprietarie. Per questo motivo, alcune funzionalità e plug-in sono disponibili solo per le edizioni proprietarie.
• Sviluppo rallentato: da quando il progetto MySQL è stato acquisito da Sun Microsystems nel 2008, e successivamente da Oracle Corporation nel 2009, ci sono state lamentele da parte degli utenti che il processo di sviluppo per il DBMS è rallentato in modo significativo, poiché la comunità non ha più all'agenzia di reagire rapidamente ai problemi e implementare le modifiche.

Quando usare MySQL

• Operazioni distribuite: il supporto della replica di MySQL lo rende un'ottima scelta per le configurazioni di database distribuiti come le architetture primary-primary.
• Siti Web e applicazioni Web: MySQL è alla base di molti siti Web e applicazioni su Internet. Ciò è dovuto, in gran parte, alla facilità di installazione e configurazione di un database MySQL, nonché alla sua velocità complessiva e scalabilità a lungo termine.
• Crescita futura prevista: il supporto della replica di MySQL può aiutare a facilitare il ridimensionamento orizzontale. Inoltre, è un processo relativamente semplice passare a un prodotto MySQL commerciale, come MySQL Cluster, che supporta lo sharding automatico, un altro processo di scalabilità orizzontale.

Quando non usare MySQL

• È necessaria la conformità SQL: poiché MySQL non tenta di implementare lo standard SQL completo, questo strumento non è completamente conforme a SQL. Se la conformità SQL completa o quasi completa è un must per il tuo caso d'uso, potresti voler utilizzare un DBMS più pienamente conforme.
• Concorrenza e grandi volumi di dati: sebbene MySQL generalmente funzioni bene con operazioni di lettura pesanti, le letture-scritture simultanee possono essere problematiche. Se la tua applicazione avrà molti utenti che vi scrivono dati contemporaneamente, un altro RDBMS come PostgreSQL potrebbe essere una scelta migliore per il database.

PostgreSQL

PostgreSQL, noto anche come Postgres, si autodefinisce il database relazionale open source più avanzato al mondo. È stato creato con l'obiettivo di essere altamente estensibile e conforme agli standard.PostgreSQL è un database relazionale a oggetti, il che significa che, sebbene sia principalmente un database relazionale, include anche funzionalità, come l'ereditarietà delle tabelle e l'overload delle funzioni, che sono più spesso associate a database oggetto.

Postgres è in grado di gestire in modo efficiente più attività contemporaneamente, una caratteristica nota come concorrenza. Raggiunge questo obiettivo senza blocchi di lettura grazie alla sua implementazione di Multiversion Concurrency Control (MVCC), che garantisce l'atomicità, la coerenza, l'isolamento e la durabilità delle sue transazioni, nota anche come conformità ACID.

PostgreSQL non è ampiamente utilizzato come MySQL, ma esistono ancora numerosi strumenti e librerie di terze parti progettati per semplificare il lavoro con PostgreSQL, incluso Postbird.

Tipi di dati supportati da PostgreSQL

PostgreSQL supporta tipi di dati numerici, stringhe e data e ora come MySQL. Inoltre, supporta tipi di dati per forme geometriche, indirizzi di rete, stringhe di bit, ricerche di testo e voci JSON, nonché diversi tipi di dati idiosincratici.

Tipi numerici:

Tipi di caratteri:

Tipi di data e ora:

Tipi geometrici:

Tipi di indirizzi di rete:

Tipi di stringhe di bit:

Tipi di ricerca testuale:

Tipi JSON:

Altri tipi di dati:

Vantaggi di PostgreSQL

• Conformità SQL: più di SQLite o MySQL, PostgreSQL mira ad aderire strettamente agli standard SQL. Secondo la documentazione ufficiale di PostgreSQL, PostgreSQL supporta 160 delle 179 funzionalità richieste per la piena conformità a SQL:2011, oltre a un lungo elenco di funzionalità opzionali.
• Open-source e guidato dalla comunità: un progetto completamente open-source, il codice sorgente di PostgreSQL è sviluppato da una comunità ampia e devota. Allo stesso modo, la community di Postgres mantiene e contribuisce a numerose risorse online che descrivono come lavorare con il DBMS, incluso il wiki di PostgreSQL e vari forum online.
• Estensibile: gli utenti possono estendere PostgreSQL in modo programmatico e al volo attraverso il suo caricamento dinamico. Si può designare un file di codice oggetto, come una libreria condivisa, e PostgreSQL lo caricherà se necessario.

Svantaggi di PostgreSQL

• Prestazioni della memoria: per ogni nuova connessione client, PostgreSQL esegue il fork di un nuovo processo. A ogni nuovo processo vengono allocati circa 10 MB di memoria, che possono sommarsi rapidamente per database con molte connessioni. Di conseguenza, per semplici operazioni pesanti in lettura, PostgreSQL è in genere meno performante di altri RDBMS, come MySQL.
• Popolarità: anche se più ampiamente utilizzato negli ultimi anni, PostgreSQL è storicamente rimasto indietro rispetto a MySQL in termini di popolarità. Una conseguenza di ciò è che ci sono ancora meno strumenti di terze parti che possono aiutare a gestire un database PostgreSQL. Allo stesso modo, non ci sono tanti amministratori di database con esperienza nella gestione di un database Postgres rispetto a quelli con esperienza MySQL.

Quando usare PostgreSQL

• L'integrità dei dati è importante: PostgreSQL è completamente conforme ad ACID dal 2001 e implementa il controllo della valuta multiversione per garantire che i dati rimangano coerenti, rendendolo una scelta valida per RDBMS quando l'integrità dei dati è fondamentale.
• Integrazione con altri strumenti: PostgreSQL è compatibile con un'ampia gamma di linguaggi e piattaforme di programmazione. Ciò significa che se mai dovessi migrare il tuo database su un altro sistema operativo o integrarlo con uno strumento specifico, probabilmente sarà più facile con un database PostgreSQL che con un altro DBMS.
• Operazioni complesse: Postgres supporta piani di query che possono sfruttare più CPU per rispondere alle query con maggiore velocità. Questo, insieme al suo forte supporto per più scrittori simultanei, lo rende un'ottima scelta per operazioni complesse come il data warehousing e l'elaborazione delle transazioni online.

Quando non usare PostgreSQL

• La velocità è un imperativo: a scapito della velocità, PostgreSQL è stato progettato pensando all'estensibilità e alla compatibilità. Se il tuo progetto richiede le operazioni di lettura più veloci possibili, PostgreSQL potrebbe non essere la scelta migliore di DBMS.
• Configurazioni semplici: a causa del suo ampio set di funzionalità e della forte aderenza allo standard SQL, Postgres può essere eccessivo per semplici configurazioni di database. Per le operazioni di lettura pesante in cui è richiesta velocità, MySQL è in genere una scelta più pratica.
• Replica complessa: sebbene PostgreSQL fornisca un forte supporto per la replica, è ancora una funzionalità relativamente nuova. Alcune configurazioni, come un'architettura primaria-primaria, sono possibili solo con le estensioni. La replica è una funzionalità più matura su MySQL e molti utenti ritengono che la replica di MySQL sia più facile da implementare, in particolare per coloro che non hanno la necessaria esperienza di amministrazione di database e sistemi.

Conclusione

Oggi, SQLite, MySQL e PostgreSQL sono i tre sistemi di gestione di database relazionali open source più diffusi al mondo. Ognuno ha le proprie caratteristiche e limitazioni uniche ed eccelle in scenari particolari. Ci sono parecchie variabili in gioco quando si decide su un RDBMS e la scelta raramente è così semplice come scegliere quella più veloce o quella con il maggior numero di funzionalità. La prossima volta che avrai bisogno di una soluzione di database relazionale, assicurati di ricercare approfonditamente questi e altri strumenti per trovare quello più adatto alle tue esigenze.

Se desideri saperne di più su SQL e su come utilizzarlo per gestire un database relazionale, ti invitiamo a fare riferimento al nostro Confronto tra i sistemi di gestione dei database NoSQL.

Riferimenti

• Classifiche DB-Engines
• Documentazione ufficiale di SQLite
• SQLite è senza server
• Usi appropriati per SQLite
• Documentazione ufficiale di MySQL
• Confronto tra la replica di MySQL e Postgres 9.0
• Documentazione ufficiale di PostgreSQL
• È finalmente giunto il momento per PostgreSQL?