PostgreSQL, MySQL & SQL

📊 Tabelle, righe, colonne

Il modello relazionale organizza i dati in tabelle (entità, es. clienti), dove ogni riga è un record e ogni colonna un attributo con un tipo definito (testo, numero, data...). Le relazioni tra tabelle (un cliente ha molti ordini) si esprimono con chiavi, non duplicando i dati — è il cuore di tutto il capitolo 4 e del JOIN (cap. 6).

⚖️ SQL vs NoSQL, in una frase

NoSQL (MongoDB, Redis, Cassandra...) sacrifica parte delle garanzie relazionali per scalare orizzontalmente o per schemi molto flessibili. Non è "meglio" o "peggio": se i tuoi dati hanno relazioni chiare e serve integrità forte (soldi, inventario, prenotazioni), un RDBMS resta la scelta di default e questa guida parla solo di quello.

🐬 PostgreSQL

Nato a Berkeley come "Postgres" (erede di Ingres), poi "Postgres95" con supporto SQL, infine PostgreSQL dal 1996. Sviluppato da una community indipendente, mai controllato da una singola azienda. Un unico motore di storage, ma esteso quasi all'infinito: JSON nativo, ricerca full-text, PostGIS per dati geografici, foreign data wrapper per interrogare altre fonti come se fossero tabelle locali.

🐬 MySQL / MariaDB

MySQL nasce nel 1995 (Widenius & Axmark), pensato da subito per essere veloce e facile da installare: è diventato lo standard de facto del web (la "M" di LAMP). Dopo l'acquisizione di Sun/MySQL da parte di Oracle (2010), gli autori originali hanno creato MariaDB come fork completamente libero, oggi il default su Debian/RHEL al posto di MySQL. Architettura a motori di storage intercambiabili: InnoDB (transazionale, il default oggi) o il vecchio MyISAM (senza transazioni, quasi mai la scelta giusta ormai).

🐬 Connettersi a PostgreSQL

# Postgres usa l'utente OS "postgres" per l'accesso amministrativo iniziale
sudo -u postgres psql

# Dentro psql: elenca database, cambia database, esci
\l
\c nomedb
\dt          # elenca le tabelle
\q

🐬 Connettersi a MySQL/MariaDB

sudo mysql                    # root via socket, nessuna password richiesta
mysql -u utente -p             # login con utente/password

# Dentro il client: elenca database, cambia database, esci
SHOW DATABASES;
USE nomedb;
SHOW TABLES;
EXIT;

🔎 Pattern matching: LIKE

SELECT * FROM clienti WHERE nome LIKE 'Mar%';   -- inizia con "Mar"
SELECT * FROM clienti WHERE email LIKE '%@gmail.com';

Su PostgreSQL LIKE è case-sensitive di default; esiste ILIKE per ignorare maiuscole/minuscole. Su MySQL/MariaDB dipende dalla collation della colonna (spesso case-insensitive di default con le collation *_ci).

📌 LIMIT / OFFSET: la paginazione

SELECT * FROM ordini
ORDER BY id
LIMIT 20 OFFSET 40;   -- "pagina 3" di 20 risultati

📋 Subquery

-- Clienti che hanno speso piu' della media
SELECT nome FROM clienti
WHERE id IN (
    SELECT cliente_id FROM ordini
    GROUP BY cliente_id
    HAVING SUM(totale) > (SELECT AVG(totale) FROM ordini)
);

📋 CTE: le subquery leggibili

WITH totali_cliente AS (
    SELECT cliente_id, SUM(totale) AS spesa
    FROM ordini GROUP BY cliente_id
)
SELECT clienti.nome, totali_cliente.spesa
FROM clienti JOIN totali_cliente
  ON clienti.id = totali_cliente.cliente_id;

Le CTE (Common Table Expression, il blocco WITH) rendono leggibile una query complessa spezzandola in passi con nome. Postgres le supporta da sempre (incluse le CTE ricorsive); MySQL e MariaDB solo dalla versione 8.0/10.2 in poi.

✅ Quando aiutano

• Colonne usate spesso in WHERE
• Colonne usate in JOIN (le foreign key, quasi sempre)
• Colonne usate in ORDER BY su tabelle grandi
• Le chiavi primarie hanno già un indice automatico, sempre

❌ Il prezzo che pagano

• Ogni INSERT/UPDATE/DELETE deve aggiornare anche gli indici — più indici, scritture più lente
• Occupano spazio su disco, a volte quanto la tabella stessa
• Un indice su una colonna quasi mai filtrata è puro costo, zero beneficio

🐬 Utenti in PostgreSQL

CREATE ROLE app_utente WITH LOGIN PASSWORD 'segreta';
GRANT CONNECT ON DATABASE negozio TO app_utente;
GRANT SELECT, INSERT, UPDATE ON ordini TO app_utente;
REVOKE DELETE ON ordini FROM app_utente;

🐬 Utenti in MySQL/MariaDB

CREATE USER 'app_utente'@'localhost' IDENTIFIED BY 'segreta';
GRANT SELECT, INSERT, UPDATE ON negozio.ordini TO 'app_utente'@'localhost';
REVOKE DELETE ON negozio.ordini FROM 'app_utente'@'localhost';
FLUSH PRIVILEGES;

💾 PostgreSQL: pg_dump

# Un solo database, formato SQL testuale
pg_dump -U postgres negozio > negozio.sql

# Formato custom (compresso, permette restore selettivo)
pg_dump -U postgres -Fc negozio > negozio.dump

# Ripristino
psql -U postgres negozio < negozio.sql
pg_restore -U postgres -d negozio negozio.dump

# Tutti i database dell'istanza in un colpo
pg_dumpall -U postgres > tutto.sql

💾 MySQL/MariaDB: mysqldump

# Un solo database
mysqldump -u root -p negozio > negozio.sql

# Tutti i database
mysqldump -u root -p --all-databases > tutto.sql

# Ripristino
mysql -u root -p negozio < negozio.sql

🔄 Manutenzione continua

• Allarme automatico se il lag (replay_lag / Seconds_Behind_Source) supera una soglia, non scoprirlo a occhio
• NTP sincronizzato su tutti i nodi: senza orologi allineati, i numeri di lag mentono
• Verifica periodica che le repliche siano davvero leggibili e coerenti (una query di conteggio periodica confrontata col primary)
• Switchover pianificato (promozione controllata, tutti sincronizzati) è sempre preferibile a un failover di emergenza (nodo sparito, dati potenzialmente non ancora replicati)

🧠 Oltre il fai-da-te

Gestire manualmente promozione, riconfigurazione degli altri nodi e redirezione dell'applicazione dopo un failover è lento ed error-prone. Strumenti come Patroni (PostgreSQL, con etcd/Consul per il consenso) o Orchestrator (MySQL/MariaDB) automatizzano rilevamento del guasto e promozione; un connection pooler davanti (PgBouncer, ProxySQL) evita di dover riconfigurare ogni applicazione client con il nuovo indirizzo.

🐬 PostgreSQL essenziale

sudo -u postgres psql
\l   \c db   \dt   \d tabella   \du
pg_dump -U postgres db > db.sql
pg_restore -d db db.dump
EXPLAIN ANALYZE SELECT ...;

🐬 MySQL/MariaDB essenziale

sudo mysql
SHOW DATABASES; USE db; SHOW TABLES;
mysqldump -u root -p db > db.sql
mysql -u root -p db < db.sql
EXPLAIN SELECT ...;

🔍 SQL essenziale

SELECT col FROM t WHERE cond ORDER BY col LIMIT n;
SELECT ... FROM a JOIN b ON a.id = b.a_id;
SELECT col, COUNT(*) FROM t GROUP BY col HAVING ...;
INSERT INTO t (col) VALUES (val);
UPDATE t SET col = val WHERE cond;
DELETE FROM t WHERE cond;

🔐 Transazioni & permessi

BEGIN; ... COMMIT; / ROLLBACK;
GRANT SELECT, INSERT ON t TO utente;
REVOKE DELETE ON t FROM utente;
CREATE INDEX idx_x ON t(colonna);

📚 Risorse

• postgresql.org/docs — documentazione ufficiale PostgreSQL, ottima
• mariadb.com/kb — MariaDB Knowledge Base
• dev.mysql.com/doc — documentazione ufficiale MySQL
• use-the-index-luke.com — il riferimento classico sugli indici SQL
• sqlzoo.net / pgexercises.com — esercizi pratici per allenarsi

🔗 Guide collegate

• WordPress e Drupal — i CMS che girano proprio su questi database
• Alta Affidabilità — quorum, fencing e failover, oltre la sola replica del database
• Redis — la cache in RAM da affiancare al database, non da sostituirlo
• Docker — far girare Postgres/MySQL in container, con volumi persistenti
• Linux Admin — systemd, dischi e il resto dell'infrastruttura sotto al database
• Gestione Dischi — dove e come vivono davvero i file del data directory
• Ansible — automatizzare installazione e provisioning dei database