SQLite vs MySQL vs PostgreSQL: un confronto tra RDBMS

Introduzione

I database relazionali vengono usati da lungo tempo. Sono diventati popolari grazie ai sistemi di gestione che implementano in modo egregio il modello relazionale, che ha dimostrato di essere un ottimo sistema per operare sui dati [in particolare per le applicazioni mission-critical].

In questo articolo di DigitalOcean, cercheremo di capire le differenze fondamentali tra alcuni dei sistemi di gestione di basi di dati relazionali (RDBMS) più popolari ed utilizzati. Analizzeremo le loro differenze fondamentali in termini di caratteristiche e funzionalità, come funzionano e quando uno eccelle sull’altro, in modo da aiutare uno sviluppatore nella scelta di un RDBMS.

Glossario

1. Sistemi di gestione di basi di dati

1. Sistemi di gestione di basi di dati relazionali
   
2. Relazioni e tipi di dati
   
3. Database relazionali più diffusi ed importanti

2. SQLite

1. Tipi di dato supportati da SQLite
2. Vantaggi di SQLite
3. Svantaggi di SQLite
4. Quando usare SQLite
5. Quando non usare SQLite

3. MySQL

1. Tipi di dato supportati da MySQL
2. Vantaggi di MySQL
3. Svantaggi di MySQL
4. Quando usare MySQL
5. Quando non usare MySQL

4. PostgreSQL

1. Tipi di dato supportati da PostgreSQL
2. Vantaggi di PostgreSQL
3. Svantaggi di PostgreSQL
4. Quando usare PostgreSQL
5. Quando non usare PostgreSQL

Sistemi di gestione di basi di dati

I database sono spazi di immagazinamento modellati a livello logico per qualsiasi genere di informazione (i dati). Ogni database, rispetto a quelli senza schema, hanno un modello che fornisce una struttura per i dati che si stanno trattando. I Database Management Systems (DBMS) sono applicazioni (o librerie) che gestiscono database di vari modelli, dimensioni e tipo.

Nota: Per ulteriori informazioni sui DBMS, date un’occhiata all’articolo: Capire i database.

Sistemi di gestione di basi di dati relazionali

Gli RDBMS implementano il modello relazionale per la gestione dei dati. Il modello relazionale plasma ogni informazione da memorizzare definendola come entità relazionate che possiede attributi, tramite tabelle (cioè schemi).

Questi tipi di DBMS richiedono la definizione di strutture (per esempio una tabella) da definire per poter contenere i dati e manipolarli. Con le tabelle, ciascuna colonna (per esempio un attributo) contiene un tipo diverso (ad esempio un tipo di dato) di ogni informazione. Ogni record nel database, identificato univocamente con delle chiavi, si traduce in una riga che appartiene ad una tabella, e l’insieme di attributi di ogni riga viene rappresentato come le colonne della tabella; tutto ciò in relazione l’un l’altro, come definito all’interno del modello relazionale.

Relazioni e tipi di dato

Le relazioni possono essere considerate come insiemi matematici che contengono serie di attributi che rappresentano collettivamente il database e le informazioni che sono in esso contenute. Questo tipo di metodo di identificazione e raccolta permette ai database relazionali di funzionare come di fatto funzionano.

Quando si definisce una tabella per l’inserimento dei record, ogni elemento che forma un record (cioè un attributo) deve corrispondere al tipo di dato definito (ad esempio un intero, una data, ecc). Differenti DBMS implementano differenti tipi di dato, che non sono sempre direttamente intercambiabili.

Con i database relazionali è comune lavorare con e attraverso dei vincoli, come quello che abbiamo appena spiegato. Infatti, i vincoli costituiscono il nucleo delle relazioni.

Nota: Se avete bisogno di lavorare con informazioni davvero non correlate, rappresentate in maniera casuale (ad esempio un documento), potreste essere interessati ad utilizzare NoSQL (database senza schema). Se volete saperne di più, date un’occhiata all’articolo Un confronto tra modelli e DBMS NoSQL .

Database relazionali più diffusi ed importanti

In questo articolo, ci accingiamo ad introdurre tre principali ed importanti RDBMS open source che hanno contribuito a plasmare il mondo dello sviluppo di applicazioni.

• SQLite:

Un DBMS embedded molto potente.

• MySQL:

L’RDBMS più popolare ed utilizzato più di frequente.

• PostgreSQL:

 L’RDBMS open source ad oggetti più avanzato e conforme allo standard SQL.

Nota: Alle applicazioni open source viene data quasi sempre la libertà di percorrere qualunque direzione. Il più delle volte, è consentita anche la libertà di fare un fork del progetto (quindi utilizzarne il codice) per creare qualcosa di nuovo. Se siete interessati ai DBMS, potreste voler controllare alcuni dei progetti forkati basati su questi popolari progetti, come MariaDB .

SQLite

SQLite è una libreria sorprendente, che viene incorporata all’interno dell’applicazione che ne fa uso. Come un database autosufficiente, basato su file, SQLite offre una serie straordinaria di strumenti per gestire tutti i generi di dati con meno vincoli e più facilità rispetto ai database relazionali basati su processi (server) e che necessitano di un host.

Quando un’applicazione utilizza SQLite, l’integrazione funziona tramite chiamate funzionali e dirette apportate ad un file contenente i dati (cioè il database SQLite) invece di comunicare tramite un’interfaccia (cioè le porte,  i socket). Questo rende SQLite estremamente veloce ed efficiente ed anche potente grazie alla tecnologia della libreria sottostante.

Tipi di dato supportati da SQLite

• NULL:

Valore NULL.

• INTEGER:

Intero con segno, memorizzato in 1, 2, 3, 4, 6, o 8 byte a seconda della grandezza del valore.

• REAL:

Valore in virgola mobile, memorizzato come numero in virgola mobile IEEE ad 8 byte.

• TEXT:

Stringa di testo, memorizzata utilizzando la codifica del database (UTF-8, UTF-16BE o UTF-16LE).

• BLOB:

Un blob di dati, memorizzati esattamente com’erano in input.

Nota: per ulteriori informazioni sui tipi di dato di SQLite e le affinità dei tipi di SQLite, date un’occhiata alla documentazione ufficiale sull’argomento.

Vantaggi di SQLite

• Basato su file:

L’intero database è costituito da un singolo file sul disco, ciò lo rende estremamente portabile.

• Attento agli standard:

Anche se potrebbe sembrare come una implementazione “semplice” dei DB, SQLite utilizza SQL. Alcune caratteristiche sono omesse (le istruzioni RIGHT OUTER JOIN o FOR EACH), tuttavia ne ha alcune in più.

• Ottimo per lo sviluppo ed il testing:

Durante la fase di sviluppo della maggior parte delle applicazioni, per un gran numero di persone è molto probabile aver bisogno di una soluzione in grado di scalare per quanto riguarda la concorrenza. SQLite, con il suo ricco insieme di funzionalità, è in grado di offrire più di ciò che è necessario per lo sviluppo, con in più la semplicità di lavorare con un singolo file ed una linked library in C.

Svantaggi di SQLite

• Nessuna gestione degli utenti:

I database avanzati forniscono il supporto per gli utenti, cioè il controllo delle connessioni tramite un insieme di privilegi di accesso al database e alle tabelle. Data la finalità e la natura di SQLite (nessun alto livello di concorrenza multi-client), tale funzione non esiste.

• Mancanza della possibilità di smanettare per ottenere migliori prestazioni:

Di nuovo, per come è stato progettato, con SQLite non è possibile avere spazio di manovra per carcare di migliorare le prestazioni. La libreria è semplice da impostare e semplice da usare. Visto che ciò non è complicato, non è tecnicamente possibile renderlo più performante di quanto già, sorprendentemente lo sia.

Quando usare SQLite

• Applicazioni embedded:

In tutte le applicazioni che richiedono la portabilità, che non richiedono estensioni; ad esempio le applicazioni singolo utente in locale, le applicazioni mobili oppure i giochi.

• Alternativa all’accesso al disco:

In molti casi, le applicazioni che hanno bisogno di leggere/scrivere direttamente dei file su disco sono in grado di trarre beneficio dal passaggio a SQLite grazie alle funzionalità aggiuntive e alla semplicità che deriva dall’utilizzo dello Structured Query Language (SQL).

• Testing:

Per gran parte delle applicazioni è eccessivo utilizzare un ulteriore processo per testare la logica di funzionamento (cioè lo scopo principale dell’applicazione: la funzionalità).

Quando non usare SQLite

• Applicazioni multiutente:

Se si sta lavorando su un’applicazione in cui più client hanno bisogno di accedere ed utilizzare lo stesso database, un RDBMS completo di tutte le caratteristiche (per esempio MySQL) è probabilmente una scelta migliore rispetto a SQLite.

• Applicazioni che richiedono elevati volumi di scrittura:

Una delle limitazioni di SQLite sono le operazioni di scrittura. Questo DBMS consente un’unica singola operazione di scrittura per ogni singolo istante, consentendo dunque un throughtput limitato.

MySQL

MySQL è il più popolare di tutti i database server utilizzati su larga scala. Si tratta di un prodotto ricco di funzionalità, open source, che fa funzionare un sacco di siti web e applicazioni online. Iniziare con MySQL è relativamente facile e su internet gli sviluppatori hanno accesso ad un imponente insieme di informazioni che lo riguardano.

Nota: Va detto che, data la popolarità del prodotto, ci sono un sacco di applicazioni di terze parti, strumenti e librerie integrate che danno un notevole aiuto su molti aspetti dell’utilizzo di questo RDBMS.

Nonostante non provi ad implementare il completo standard SQL, MySQL offre un sacco di funzionalità agli utenti. Quale database server a sé stante, a differenza di SQLite, per accedere al database stesso le applicazioni dialogano con un demone MySQL.

Tipi di dato supportati da MySQL

• TINYINT:

Un numero intero molto piccolo.

• SMALLINT:

Un numero intero piccolo.

• MEDIUMINT:

Un numero intero di medie dimensioni.

• INT o INTEGER:

Un numero intero di dimensioni normali.

• BIGINT:

Un numero intero grande.

• FLOAT:

Un numero a virgola mobile piccolo (singola precisione). Non può essere senza segno.

• DOUBLE, DOUBLE PRECISION, REAL:

Un numero a virgola mobile normale (doppia precisione). Non può essere senza segno.

• DECIMAL, NUMERIC:

Un numero a virgola mobile non compresso. Non può essere senza segno.

• DATE:

Una data.

• DATETIME:

Una combinazione di data e ora.

• TIMESTAMP:

Un timestamp.

• TIME:

Un orario.

• YEAR:

Un anno in formato a  2 o 4 cifre (di default è a 4 cifre).

• CHAR:

Una stringa di lunghezza fissa che al momento della memorizzazione è sempre riempita da destra (right padded) con degli spazi, fino alla lunghezza specificata.

• VARCHAR:

Una stringa di lunghezza variabile.

• TINYBLOB, TINYTEXT:

Una colonna BLOB o TEXT con una lunghezza massima di 255 (2 ^ 8 – 1) caratteri.

• BLOB, TEXT:

Una colonna BLOB o TEXT con una lunghezza massima di 65535 (2 ^ 16 – 1) caratteri.

• MEDIUMBLOB, MEDIUMTEXT:

Una colonna BLOB o TEXT con una lunghezza massima di 16777215 (2 ^ 24 – 1) caratteri.

• LONGBLOB, LONGTEXT:

Una colonna BLOB o TEXT con una lunghezza massima di 4294967295 (2 ^ 32 – 1) caratteri.

• ENUM:

Una enumerazione.

• SET:

Un insieme.

Vantaggi di MySQL

• È facile lvoraci:

MySQL può essere installato molto facilmente. Strumenti di terze parti, tra cui quelli visuali (cioè le GUI) rendono estremamente semplice prendere dimestichezza con il database.

• Ricco di funzionalità:

MySQL supporta un sacco delle funzionalità SQL che ci si aspetta da un RDBMS, direttamente o indirettamente.

• Sicuro:

In MySQL troviamo un sacco di caratteristiche legate alla sicurezza, alcune piuttosto avanzate.

• Scalabile e potente:

MySQL è in grado di gestire una grande quantità di dati, inoltre può essere scalabile, se necessario.

• Veloce:

Rinunciare ad alcuni standard permette a MySQL di lavorare in modo molto efficiente e di “smussare gli angoli”, fornendo così dei miglioramenti nella velocità.

Svantaggi di MySQL

• Limitazioni note:

Per come è stato progettato, MySQL non intende comprendere tutte le funzionalità possibili ed alcune di quelle presenti hanno delle limitazioni. Queste funzionalità mancanti potrebbero essere richieste da alcune applicazioni di ultima generazione.

• Problemi di affidabilità:

Il modo in cui MySQL tratta certe funzionalità (ad esempio i riferimenti, le transazioni, l’auditing, ecc) lo rende un po’ meno affidabile rispetto ad altri RDBMS.

• Stasi nello sviluppo:

Anche se MySQL è ancora tecnicamente un prodotto open source, vi sono lamentele per quanto riguarda il suo processo di sviluppo, fin da quando è stato acquisito. Tuttavia, va notato che ci sono alcuni database basati su MySQL con le medesime funzionalità, che aggiungono ulteriori utilità alle classiche installazioni MySQL (ad esempio MariaDB).

Quando usare MySQL

• Operazioni distribuite:

Quando avete bisogno di più di quello che SQLite è in grado di offrire, aggiungere MySQL al vostro stack di sviluppo, proprio come qualsiasi database server a sé stante, porta un sacco di libertà operativa insieme ad alcune funzionalità avanzate.

• Elevata sicurezza:

Le caratteristiche di sicurezza di MySQL forniscono, in maniera semplice, una protezione affidabile per l’accesso (e uso) dei dati.

• Siti e applicazioni web:

Una grande maggioranza dei siti web (e delle applicazioni web) può lavorare con MySQL in maniera semplice, nonostante i vincoli. Questo strumento flessibile ed un po’ scalabile è facile da usare e facile da gestire, il che si rivela molto utile nel lungo periodo.

• Soluzioni personalizzate:

Se si sta lavorando ad una soluzione estremamente specifica e personalizzata, MySQL vi può facilmente far parte e si può basare sulle vostre regolazioni grazie alle sue ricche impostazioni di configurazione e modalità di funzionamento.

Quando non usare MySQL

• Conformità SQL:

Dal momento che MySQL non [prova ad] implementare il completo standard SQL, questo strumento non è completamente compatibile con SQL. Se si dovesse aver bisogno di passare a tali RDBMS, il trasferimento da MySQL non sarà facile.

• Concorrenza:

Anche se MySQL ed alcuni storage engine si comportano molto bene con le operazioni di lettura, operazioni di lettura-scrittura simultanee possono essere problematiche.

• Mancanza di caratteristiche:

Ancora una volta, a seconda della scelta del database engine, a MySQL possono mancare alcune funzioni, come la ricerca full-text.

PostgreSQL

PostgreSQL è l’RDBMS [ad oggetti] avanzato ed open source che ha l’obiettivo principale di essere conforme agli standard ed estensibile. PostgreSQL, o Postgres, cerca di adottare lo standard SQL ANSI/ISO assieme alle revisioni.

Rispetto ad altri RDBMS, PostgreSQL si distingue per il suo supporto alle categoriche ed integraliste funzionalità dei database orientati agli oggetti e/o relazionali, ad esempio il supporto completo per le transazioni sicure, cioè Atomicità, Consistenza, Isolamento, Durabilità (ACID).

Grazie alla potente tecnologia su cui è basato, Postgres è in grado di gestire molti compiti in modo davvero efficiente. Il supporto per la concorrenza è ottenuta senza lock di lettura grazie alla realizzazione del Controllo della Concorrenza Multiversione (Multiversion Concurrency Control,  MVCC), che assicura anche la conformità ACID.

PostgreSQL è estremamente programmabile, e quindi estensibile, con procedure personalizzate che vengono chiamate “stored procedure”. Queste funzioni possono essere create per semplificare l’esecuzione di operazioni sul database ripetute, complesse e richieste di frequente.

Anche se questo DBMS non è popolare quanto MySQL, ci sono molti formidabili strumenti e librerie di terze parti che sono stati progettati per rendere semplice lavorare con PostgreSQL, nonostante la natura potente di questo database. Oggi è possibile ottenere con facilità PostgreSQL come package, tramite il gestore dei pachetti predefinito del sistema operativo.

Tipi di dati supportati di PostgreSQL

• bigint:

  intero con segno ad otto byte

• bigserial:

intero autoincrementale ad otto byte

• bit [(n)]:

stringa di bit di lunghezza fissa

• bit varying [(n)]:

stringa di bit di lunghezza variabile

• boolean:

logica booleana (true/false)

• box:

box rettangolare su un piano

• bytea:

dati binari (“array di byte”)

• character varying [(n)]:

stringa di caratteri di lunghezza variabile

• character [(n)]:

stringa di caratteri di lunghezza fissa

• cidr:

indirizzo di rete IPv4 o IPv6

• circle:

cerchio su un piano

• date:

data di calendario (anno, mese, giorno)

• double precision:

  numero in virgola mobile in doppia precisione (8 byte)

• inet:

  indirizzo host IPv4 o IPv6

• integer:

intero con segno a quattro byte

• interval [campi] [(p)]:

arco di tempo

• line:

linea infinita su un piano

• lsef:

segmento di linea su un piano

• macaddr:

indirizzo MAC (Media Access Control)

• money:

importo in valuta

• numeric [(p, s)]:

numerico esatto di precisione selezionabile

• path:

percorso geometrico su un piano

• point:

punto geometrico su un piano

• polygon:

percorso geometrico chiuso su un piano

• real:

numero in virgola mobile in singola precisione  (4 byte)

• smallint:

intero con segno a due byte

• serial:

intero autoincrementale a quattro byte

• text:

stringa di caratteri di lunghezza variabile

• time [(p)] [senza fuso orario]:

ora del giorno (senza fuso orario)

• time [(p)] con il fuso orario:

ora del giorno, compreso il fuso orario

• timestamp [(p)] [senza fuso orario]:

data e ora (senza fuso orario)

• timestamp [(p)] con il fuso orario:

la data e l’ora, compreso il fuso orario

• tsquery:

testo della query di ricerca

• tsvector:

testo del documento di ricerca

• txid_snapshot:

ID di transazione istantaneo a livello utente

• uuid:

identificatore universale univoco

• xml:

dati XML

Vantaggi di PostgreSQL

• RDBMS open source e conforme allo standard SQL:

PostgreSQL è open source e gratuito ed anche un potente sistema di gestione di database relazionali.

• Forte comunità:

PostgreSQL è supportato da una comunità devota e competente a cui si può accedere attraverso i siti con la knowledge base e le Domande/Risposte gratuitamente, 24 ore su 24, 7 giorni su 7.

• Forte supporto di terze parti:

Indipendentemente dalle caratteristiche estremamente avanzate, PostgreSQL si fa bello grazie a diversi grandiosi strumenti open source di terze parti, per la progettazione, la gestione e l’utilizzo del sistema di gestione.

• Estensibile:

È possibile estendere la programmazione di PostgreSQL con le stored procedure, come dovrebbe essere per un RDBMS avanzato.

• Ad oggetti:

PostgreSQL non è solo un sistema di gestione di database relazionali, ma gestisce anche database ad oggetti, con il supporto per la nidificazione ed altro ancora.

Svantaggi di PostgreSQL

• Prestazioni:

Per semplici ma gravose operazioni di lettura, PostgreSQL può essere eccessivo e potrebbe apparire meno performante rispetto alle controparti, come MySQL.

• Popolarità:

Data la natura di questo strumento, non è molto popolare, nonostante la grande quantità di implementazioni;  ciò potrebbe incidere sulla facilità nell’ottenere supporto.

• Hosting:

A causa delle circostanze di cui sopra, è più difficile trovare fornitori di host o servizi che offrano database PostgreSQL gestiti da loro stessi.

Quando usare PostgreSQL

• Integrità dei dati:

Quando l’affidabilità e l’integrità dei dati sono una necessità assoluta, senza compromessi, PostgreSQL è la scelta migliore.

• Procedure complesse e personalizzate:

Se avete bisogno di un database che permetta l’esecuzione di procedure personalizzate, PostgreSQL, visto che è estensibile, è la scelta migliore.

• Integrazione:

In futuro, se ci sarà una possibilità o si presenta la necessità di una migrazione dell’intero database ad una soluzione proprietaria (ad esempio Oracle), PostgreSQL sarà il DBMS meno problematico e più facile da gestire quale punto di partenza per il passaggio.

• Design complesso:

Rispetto ad altri RDBMS open source e gratuiti, per il design di database complessi PostgreSQL offre il massimo in termini di funzionalità ed opportunità, senza dover rinunciare ad altre preziose caratteristiche.

Quando non usare PostgreSQL

• Velocità:

Se tutto ciò che vi serve sono veloci operazioni di lettura, PostgreSQL non è lo strumento da scegliere.

• Set up veloce:

A meno che non si richieda l’integrità assoluta dei dati, la conformità ACID o un design complesso, PostgreSQL potrebbe non essere adatto per un veloce set up.

• Replicazione:

A meno che non siate disposti a spendere tempo, energia e risorse, creare la replicazione con MySQL potrebbe essere più semplice per coloro che non hanno esperienza con l’amministrazione di sistemi e database.