Scaling dei Database per Applicazioni ad Alto Traffico

Il database è quasi sempre il primo collo di bottiglia a manifestarsi quando un'applicazione cresce. L'application tier è orizzontalmente scalabile per definizione — aggiungi istanze, metti un load balancer davanti, e il problema è risolto. Il database, per sua natura, è stateful: ogni write deve atterrare in un posto coerente, e ogni read deve riflettere uno stato consistente. Questa complessità fondamentale rende lo scaling del layer dati uno degli esercizi di ingegneria più impegnativi che un team possa affrontare.

In Nexora abbiamo gestito questa transizione decine di volte: piattaforme che partono con un singolo PostgreSQL e si ritrovano a gestire migliaia di query al secondo con SLA di latenza p99 inferiori a 50ms. Questo articolo documenta le tecniche che usiamo sistematicamente, nell'ordine in cui ha senso applicarle.

Il punto di partenza: capire il profilo di carico

Prima di toccare qualsiasi architettura, bisogna rispondere a tre domande. Qual è il rapporto reads/writes? La maggior parte delle applicazioni web è read-heavy (80-95% di letture), il che apre immediatamente la strada alle read replica. Qual è la dimensione del dataset attivo? Se il dataset caldo entra interamente in RAM, il caching diventa meno critico. Qual è il pattern di accesso? Accesso uniforme per chiave, range query temporali, o query full-text? La risposta determina quale strategia di sharding ha senso.

Step 1 — PostgreSQL Streaming Replication e Read Replica

La prima leva che azionarai quasi sempre è la separazione tra write path e read path tramite streaming replication. PostgreSQL implementa la replication trasmettendo i WAL record (Write-Ahead Log) dal primary alle standby in tempo quasi reale — tipicamente con latenza inferiore al secondo su connessioni locali. Le standby applicano continuamente i record WAL, mantenendosi allineate con il primary.

-- Sul primary: crea l'utente di replication
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'str0ng_p4ss';

-- Crea un replication slot fisico per ciascuna standby
-- I slot garantiscono che il primary non elimini WAL segments
-- non ancora ricevuti dalla standby
SELECT pg_create_physical_replication_slot('standby_01_slot');
SELECT pg_create_physical_replication_slot('standby_02_slot');

-- Monitora il lag di replication in bytes
SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes,
  sync_state
FROM pg_stat_replication;

Sul server standby, la configurazione richiede di definire la connessione al primary e il replication slot da usare. Con hot_standby = on, la standby accetta query di sola lettura mentre applica i WAL record in streaming. Questo è il meccanismo che permette di distribuire il carico di lettura su più nodi.

# 1. Crea il base backup dal primary
pg_basebackup \
  -h primary.internal \
  -D /var/lib/postgresql/data \
  -U replicator \
  -P --wal-method=stream \
  -R  # Genera automaticamente standby.signal e primary_conninfo

# 2. Aggiungi configurazione al postgresql.conf della standby
cat >> /var/lib/postgresql/data/postgresql.conf << 'EOF'
primary_conninfo = 'host=primary.internal port=5432 user=replicator password=str0ng_p4ss application_name=standby_01'
primary_slot_name = 'standby_01_slot'
hot_standby = on
hot_standby_feedback = on   # Evita query cancellation sulla standby per vacuum aggressivo
max_standby_streaming_delay = 30s
EOF

# 3. Avvia la standby — si connetterà automaticamente al primary
pg_ctl start -D /var/lib/postgresql/data

Step 2 — Connection Pooling con PgBouncer

PostgreSQL crea un processo OS dedicato per ogni connessione client. Con applicazioni moderne che girano su decine di container, ognuno con un proprio connection pool interno, il numero di connessioni simultanee può esplodere rapidamente. Ogni processo PostgreSQL occupa tra 5 e 10 MB di RAM, e oltre le 200-300 connessioni attive le performance degradano sensibilmente a causa del context switching del kernel.

PgBouncer risolve questo problema interponendosi tra i client e PostgreSQL come proxy leggero. I client si connettono a PgBouncer (che gestisce facilmente migliaia di connessioni in ingresso), mentre PgBouncer mantiene un pool ristretto di connessioni reali verso PostgreSQL. La modalità transaction pooling è quella che offre il maggiore beneficio: una connessione PostgreSQL viene rilasciata nel pool non appena la transazione termina, permettendo a centinaia di client di condividere poche decine di connessioni backend.

[databases]
# Instrada il database "app" verso il primary per le write
app = host=primary.internal port=5432 dbname=app
# Instrada "app_ro" verso la standby per le read
app_ro = host=standby.internal port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling: massimo riutilizzo delle connessioni
pool_mode = transaction

# Connessioni verso PostgreSQL: mantieni basso questo numero
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Connessioni client: puoi gestirne migliaia
max_client_conn = 2000

# Timeout e salute
server_idle_timeout = 600
client_idle_timeout = 0
log_connections = 0  # Disabilita in produzione ad alto traffico

Step 3 — Caching con Redis: strategie e pattern

Il modo più efficace per scalare un database è evitare di interrogarlo. Redis, in-memory data store con latenza sub-millisecondo, è lo strumento standard per questo scopo. L'implementazione corretta del caching richiede però di scegliere la strategia giusta in base al profilo dei dati.

Cache-aside (Lazy Loading)

Il pattern cache-aside è il più comune: l'applicazione controlla prima Redis, e in caso di cache miss interroga il database e popola la cache. È flessibile e tollerante ai fault di Redis (se Redis è down, si cade silenziosamente sul DB), ma richiede gestione esplicita dell'invalidazione.

import { Redis } from "ioredis";
import { db } from "./database";

const redis = new Redis({ host: "redis.internal", port: 6379 });

async function getProductById(productId: string) {
  const cacheKey = `product:${productId}`;

  // 1. Controlla la cache
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2. Cache miss: interroga il database
  const product = await db.query(
    "SELECT * FROM products WHERE id = $1",
    [productId]
  );

  if (!product) return null;

  // 3. Popola la cache con TTL di 5 minuti
  await redis.setex(cacheKey, 300, JSON.stringify(product));

  return product;
}

// Invalidazione esplicita alla scrittura
async function updateProduct(productId: string, data: Partial<Product>) {
  await db.query(
    "UPDATE products SET name=$1, price=$2 WHERE id=$3",
    [data.name, data.price, productId]
  );

  // Invalida il record in cache
  await redis.del(`product:${productId}`);
  // Invalida anche le listing cache che potrebbero contenere questo prodotto
  await redis.del("products:listing:*");  // Pattern delete via SCAN in produzione
}

Write-through e Read-through

Nel pattern write-through ogni write aggiorna simultaneamente cache e database, garantendo che la cache sia sempre consistente. È preferibile per dati letti frequentemente e scritti di rado. Il read-through delega la logica di cache miss a un layer intermedio (es. un ORM con plugin di caching), semplificando il codice applicativo. Entrambi i pattern riducono la probabilità di servire dati stale, ma introducono latenza aggiuntiva sulle write.

Step 4 — Table Partitioning

Quando una singola tabella cresce oltre i 50-100 milioni di righe, le query rallentano anche con indici ottimali perché il planner deve esaminare statistiche su dataset enormi e gli indici stessi diventano profondi e costosi da navigare. PostgreSQL supporta il partitioning dichiarativo: la tabella è divisa in partizioni fisiche separate, ognuna con i propri indici, ma accessibile tramite un'unica tabella logica.

-- Tabella events partizionata per range di data (pattern comune per log e time-series)
CREATE TABLE events (
  id          BIGSERIAL,
  user_id     BIGINT NOT NULL,
  event_type  VARCHAR(50) NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Crea partizioni mensili
CREATE TABLE events_2025_11 PARTITION OF events
  FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

CREATE TABLE events_2025_12 PARTITION OF events
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

-- Ogni partizione ha i propri indici — molto più piccoli e veloci
CREATE INDEX idx_events_2025_12_user_id ON events_2025_12 (user_id);
CREATE INDEX idx_events_2025_12_type ON events_2025_12 (event_type, created_at DESC);

-- Il query planner usa partition pruning: una query per utente nel mese corrente
-- scansiona SOLO la partizione di dicembre, non l'intera tabella storica
EXPLAIN ANALYZE
SELECT * FROM events
WHERE user_id = 12345
  AND created_at >= '2025-12-01'
ORDER BY created_at DESC
LIMIT 50;
-- Seq Scan on events_2025_12 (rows=50) — solo la partizione corrente

Step 5 — Sharding orizzontale

Lo sharding è la tecnica più potente ma anche la più costosa in termini di complessità operativa. L'idea è distribuire le righe di una tabella su più istanze PostgreSQL distinte, ciascuna responsabile di un sottoinsieme dei dati. A differenza del partitioning (che divide i dati su un singolo server), lo sharding li distribuisce su server fisicamente separati, ciascuno con la propria RAM e CPU.

La scelta della shard key è la decisione più critica. Una buona shard key distribuisce il carico uniformemente, è presente in quasi tutte le query (evitando scatter-gather su tutti gli shard), e non cambia nel tempo. Nelle applicazioni multi-tenant, il tenant_id è spesso la shard key naturale: tutte le query sono scoped al tenant, e ogni shard contiene i dati di un sottoinsieme di tenant.

• Hash-based sharding: shard_id = hash(shard_key) % N. Distribuzione uniforme ma difficile da ribilanciare quando N cambia.
• Range-based sharding: shard assegnati per range di chiave (es. tenant 1-1000 su shard A, 1001-2000 su shard B). Semplice ma soggetto a hotspot.
• Consistent hashing: un anello di hash permette di aggiungere shard ribilanciando solo 1/N dei dati. Usato da Cassandra e Redis Cluster.
• Directory-based sharding: una tabella di lookup mappa ogni chiave allo shard corretto. Massima flessibilità, ma introduce un single point of failure se non replicato.
• Citus (ora integrato in PostgreSQL EDB): estensione che automatizza lo sharding trasparente, ideale per evitare lo sharding manuale a livello applicativo.

Quando applicare ogni tecnica

L'errore più comune è saltare i gradini intermedi e passare direttamente allo sharding quando il problema è risolvibile con read replica e caching. Lo sharding introduce complessità enormi: le join cross-shard diventano impossibili lato database, le transazioni distribuite richiedono 2PC o eventual consistency, e il ribilanciamento degli shard è un'operazione delicata che può durare giorni. La progressione corretta è: ottimizza le query e gli indici, aggiungi connection pooling, separa read e write con le replica, applica il caching aggressivo, partiziona le tabelle storiche, e solo come ultimo resort considera lo sharding.

1. 1Query optimization e indici — costo zero, sempre primo passo.
2. 2PgBouncer — risolve il connection exhaustion con configurazione minima.
3. 3Read replica — scalabilità orizzontale per le letture, operazione standard.
4. 4Redis caching — elimina la pressione dal DB per dati read-heavy.
5. 5Table partitioning — gestisci dataset storici enormi senza sharding.
6. 6Sharding orizzontale — solo quando il write throughput supera la capacità di un singolo primary.

Conclusioni

Lo scaling dei database non è un'unica soluzione ma una progressione di tecniche, ognuna con il proprio costo di complessità e il proprio beneficio atteso. PostgreSQL streaming replication con hot standby, PgBouncer in transaction mode e un layer Redis ben configurato risolvono il 90% dei problemi di scaling che incontrerai. Lo sharding è una soluzione reale, ma deve essere giustificato da numeri concreti — non da anticipazioni di crescita. Misura prima, architetta dopo.