PostgreSQL Avanzato: Guida Definitiva a Performance, JSONB e Replica • Meteora Web Agency

Il database rallenta quando il traffico cresce. Le query che funzionavano su MySQL ora impiegano secondi. Oppure hai bisogno di dati semistrutturati, ricerche testuali o analisi geospaziali senza cambiare motore. Questo è il momento in cui PostgreSQL smette di essere un'alternativa e diventa la scelta obbligata.

Noi, di Meteora Web, lavoriamo ogni giorno con PostgreSQL su server di produzione. Siamo partiti da un background contabile e tecnico: ogni decisione sul database la valutiamo in termini di costi, performance e manutenibilità. In questa guida pillar ti portiamo tutto quello che abbiamo imparato in anni di gestione diretta: query avanzate, tuning, estensioni e replica.

PostgreSQL vs MySQL: Quando Scegliere Postgres

MySQL è eccellente per applicazioni semplici con carichi prevalentemente in lettura. Ma quando servono transazioni complesse, integrità referenziale spinta, dati JSON strutturati o ricerche full-text, PostgreSQL vince su tutta la linea. La differenza chiave? PostgreSQL implementa standard SQL moderni (window functions, CTE ricorsive, indici parziali) e offre estensioni come PostGIS e pgvector che MySQL non ha nativamente.

Noi vediamo questa scelta come un investimento: Postgres scala meglio in scrittura, ha un planner più intelligente e non soffre dei problemi di blocco di MyISAM. Se il tuo progetto ha una vita prevista oltre l'anno, parti con Postgres.

JSONB: Schemi Flessibili senza Perdere le Query

JSONB ti permette di archiviare documenti JSON in una colonna indicizzata nativamente. A differenza di JSON (testo), JSONB è binario e supporta indici GIN per ricerche veloci su percorsi annidati.

CREATE TABLE prodotti (
  id SERIAL PRIMARY KEY,
  nome TEXT,
  attributi JSONB
);

CREATE INDEX idx_attributi ON prodotti USING GIN (attributi);

SELECT * FROM prodotti
WHERE attributi @> '{"colore": "rosso", "taglia": "M"}';

L'operatore @> verifica se il documento JSON a sinistra contiene il documento a destra. È veloce, preciso e sostituisce le join su tabelle EAV. Noi lo usiamo per e-commerce con attributi variabili per prodotto: nessuno schema rigido, ma query performanti.

Full-Text Search con tsvector e tsquery

PostgreSQL include un motore di ricerca testuale completo senza bisogno di Elasticsearch. Si basa su tsvector (documento indicizzato) e tsquery (query).

-- Creazione colonna vettoriale e indice GIN
ALTER TABLE articoli ADD COLUMN testo_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('italian', corpo || ' ' || titolo)) STORED;

CREATE INDEX idx_fts ON articoli USING GIN (testo_vector);

-- Ricerca con ranking
SELECT titolo, ts_rank(testo_vector, query) AS rank
FROM articoli, to_tsquery('italian', 'database & performance') AS query
WHERE testo_vector @@ query
ORDER BY rank DESC;

La funzione ts_rank ordina per rilevanza. Puoi personalizzare la configurazione di lingua (italian, english, simple). Per siti con decine di migliaia di articoli, basta e avanza.

Indici Avanzati: GIN, GiST, BRIN e Partial Index

Oltre al classico B-tree, PostgreSQL offre:

GIN — per array, JSONB, full-text. Ottimo per dati multi-valore.
GiST — per dati geometrici e testuali (similitudine). Usato da PostGIS e pg_trgm.
BRIN — per dati ordinati su larga scala (time-series). Occupa poco spazio.
Partial index — indicizza solo un sottoinsieme di righe. Esempio: CREATE INDEX idx_attivi ON utenti (email) WHERE attivo = true;. Riduce dimensione e velocizza le query su dati attivi.

Noi usiamo BRIN per tabelle log su server con milioni di righe: l'indice è centinaia di volte più piccolo di B-tree.

Window Functions per Analisi Dati

Le window functions (funzioni finestra) permettono calcoli su un insieme di righe correlate senza raggruppare i risultati. Esempio classico: classifiche, medie mobili, differenze tra righe consecutive.

SELECT
  id_vendita,
  data,
  importo,
  SUM(importo) OVER (PARTITION BY cliente_id ORDER BY data) AS cumulativo,
  LAG(importo) OVER (PARTITION BY cliente_id ORDER BY data) AS importo_precedente
FROM vendite;

ROW_NUMBER(), RANK(), LEAD(), LAG() sono strumenti che in MySQL richiederebbero subquery complesse. Le window functions ti danno la risposta in una query sola.

Manutenzione: VACUUM, ANALYZE e Autovacuum

PostgreSQL non riscrive le righe aggiornate: marca quelle vecchie come morte (dead tuples). Il VACUUM libera spazio e aggiorna le statistiche. L'ANALYZE aggiorna le statistiche per il planner. L'autovacuum è abilitato di default, ma va configurato per carichi intensi.

-- Visualizzare lo stato delle tabelle
SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables;

-- Forzare VACUUM su una tabella
VACUUM (VERBOSE, ANALYZE) nome_tabella;

Parametri chiave in postgresql.conf: autovacuum_vacuum_scale_factor e autovacuum_vacuum_threshold. Per tabelle grandi, abbassa il fattore di scala a 0.01 e la soglia a 1000.

Connection Pooling con PgBouncer

Ogni connessione a PostgreSQL consuma circa 10 MB di RAM. Con PgBouncer puoi gestire migliaia di connessioni client mantenendo attive solo poche decine di connessioni al database.

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
default_pool_size = 25
max_client_conn = 1000
pool_mode = transaction

Configura il tuo applicativo per connettersi sulla porta 6432. Noi lo usiamo su tutti i server di produzione. Riduce il carico e stabilizza la latenza.

Replica Streaming e Failover

La replica streaming asincrona è nativa: un master invia i WAL (write-ahead log) a uno o più standby. Per il failover, strumenti come Patroni o repmgr gestiscono la promozione automatica.

# Sul server standby
pg_basebackup -h master -D /var/lib/postgresql/17/main -U replicatore -P

Configurare primary_conninfo in standby.signal e postgresql.auto.conf. Con una replica sincrona, garantisci zero perdita di dati (ma maggiore latenza).

Estensioni: pgvector, PostGIS, TimescaleDB

pgvector — per embedding vettoriali (AI, similarità semantica). Supporta indici IVFFlat e HNSW.
PostGIS — dati geografici: punti, poligoni, distanze, proiezioni. Standard de facto.
TimescaleDB — ottimizzato per serie temporali. Ipertabelle, compressione, retention policy.

Tutte si installano con CREATE EXTENSION nome;.

Performance Tuning: postgresql.conf e EXPLAIN ANALYZE

Prima di toccare qualsiasi parametro, usa EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) per capire dove va il tempo. Noi partiamo sempre da lì.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM ordini WHERE data > '2024-01-01';

Parametri essenziali per server dedicati (8 GB RAM, SSD):

shared_buffers = 2GB (25% RAM)
effective_cache_size = 6GB (75% RAM)
work_mem = 64MB (per operazioni di ordinamento e hash)
maintenance_work_mem = 512MB (per VACUUM e indici)
random_page_cost = 1.1 (per SSD, non 4.0)

Strumenti come pgBadger e pg_stat_statements mostrano le query più lente. Noi li attiviamo su ogni server.

In Sintesi — Cosa Fare Adesso

Valuta se PostgreSQL è il database giusto per il tuo progetto. Se hai dati semistrutturati, analisi complesse o necessità di estensioni come PostGIS o pgvector, la risposta è sì.
Configura l'autovacuum per il tuo carico. Monitora n_dead_tup settimanalmente.
Usa PgBouncer se prevedi più di 100 connessioni contemporanee.
Impara a leggere EXPLAIN ANALYZE. È il tuo migliore amico per il tuning.
Installa pg_stat_statements e pgBadger. Tieni traccia delle performance nel tempo.

Noi di Meteora Web abbiamo scelto PostgreSQL per la maggior parte dei nostri progetti. Se vuoi approfondire, leggi la nostra guida su Linux per sviluppatori e sysadmin per la configurazione del server. E per il backup su cloud, ecco la guida AWS S3.