Prisma e PostgreSQL: come ottimizzare query e indici quando i dati crescono

Il progetto è in produzione. Lo schema è pulito, le migrazioni girano senza sorprese, le query si scrivono in poche righe. Poi il database si riempie e una pagina che prima caricava in un attimo inizia a metterci due secondi. Prisma non ha smesso di funzionare. Ha solo smesso di nascondere il costo di quello che gli chiediamo.

Nei due capitoli precedenti di questa serie abbiamo visto come impostare Prisma in un progetto Next.js reale e come gestire l'evoluzione del database in produzione con le migrazioni. Qui affrontiamo quello che arriva subito dopo, quando l'applicazione ha smesso di essere un prototipo e ha iniziato ad avere utenti veri. Le query sono corrette, ma sono lente, e quasi sempre il motivo sta nel modo in cui stiamo usando Prisma.

Il primo passo è vedere l'SQL che Prisma genera

Prisma è comodo perché scrivi TypeScript e ottieni dati tipizzati. Il prezzo di questa comodità è uno strato che non vedi, tra il tuo findMany e il database. Finché tutto è veloce non ti interessa. Quando qualcosa rallenta, quello strato è la prima cosa da aprire.

L'operazione più utile all'inizio di un lavoro di ottimizzazione è attivare il log delle query:

const prisma = new PrismaClient({
  log: ['query'],
})

Da questo momento la console mostra l'SQL esatto che Prisma invia a PostgreSQL. Ti accorgerai presto di due cose: spesso Prisma genera più query di quante ne immaginavi, e quelle query lavorano su molte più colonne e righe del necessario.

Il log su console riporta solo il testo SQL. Per avere anche parametri e durata devi configurare il log in modalità evento e mettere in ascolto l'evento query. Così puoi isolare le chiamate più costose senza affogare nel rumore:

const prisma = new PrismaClient({
  log: [{ emit: 'event', level: 'query' }],
})

prisma.$on('query', (e) => {
  if (e.duration > 100) {
    console.log(`${e.duration}ms`, e.query, e.params)
  }
})

Cento millisecondi è una soglia arbitraria. Il punto è smettere di guardare tutto e concentrarsi sulle query che pesano davvero.

Il problema N+1

È l'errore che quasi ogni sviluppatore commette almeno una volta con un ORM, ed è quasi invisibile a occhio nudo. Immagina di caricare una lista di post e, per ciascuno, il nome dell'autore:

const posts = await prisma.post.findMany()

for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  })
}

Con dieci post fai undici query: una per la lista, dieci per gli autori. Con mille post ne fai milleuno. Il codice sembra ragionevole e in locale i tempi sono accettabili. In produzione la pagina collassa.

La soluzione base è chiedere a Prisma di caricare la relazione insieme al dato principale, con include:

const posts = await prisma.post.findMany({
  include: { author: true },
})

Qui vale la pena capire cosa succede sotto, perché Prisma ha due strategie. Quella storica esegue query separate e ricompone i risultati in memoria: una per i post, una per tutti gli autori collegati. Evita l'N+1, ma restano comunque due viaggi verso il database. La più recente, la relation load strategy join, spinge tutto in una singola query SQL usando i lateral join di PostgreSQL:

const posts = await prisma.post.findMany({
  relationLoadStrategy: 'join',
  include: { author: true },
})

Prima di usarla controlla la versione di Prisma del progetto. La disponibilità e il comportamento di questa strategia sono cambiati tra le release, e nelle versioni in cui è ancora in preview va abilitata aggiungendo relationJoins ai previewFeatures del blocco generator nello schema.

Non esiste una scelta giusta in assoluto. Il join singolo riduce i round trip ed è spesso più veloce su relazioni semplici. Le query separate a volte reggono meglio quando le relazioni annidate sono profonde e i risultati si moltiplicano. La regola pratica è misurare entrambe sui tuoi dati reali, con il log attivo, invece di affidarti all'intuizione.

Chiedi solo i dati che ti servono

include: { author: true } risolve l'N+1 ma introduce un altro spreco: stai caricando l'intero record dell'autore, password hashata e campi che non mostrerai mai compresi, per stampare un nome. Su una lista lunga quei byte in più contano.

Con select dichiari esattamente le colonne che vuoi, sul modello principale e sulle relazioni:

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: {
      select: { name: true },
    },
  },
})

In produzione la differenza si vede. Meno colonne vuol dire meno dati letti dal disco e meno traffico tra database e applicazione, con risposte più leggere. Su una tabella con colonne di testo lungo o campi jsonb pesanti, passare dal record completo a tre campi può cambiare l'ordine di grandezza del tempo di risposta.

La disciplina da prendere è semplice: nelle query di lista usa quasi sempre select, e carica il modello completo solo nelle viste di dettaglio, dove quei campi servono davvero.

Gli indici: dove PostgreSQL fa la differenza

Un indice è una struttura dati che permette a PostgreSQL di trovare le righe che soddisfano una condizione senza scorrere l'intera tabella. Senza indice, filtrare su una colonna costringe il database a leggere tutte le righe una per una: nei piani di esecuzione lo vedrai chiamato sequential scan. Su diecimila righe non te ne accorgi. Su cinque milioni sì.

In Prisma gli indici si dichiarano nello schema. Chiave primaria e vincoli di unicità li generano in automatico:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
}

Il punto che sfugge più spesso riguarda le chiavi esterne. In PostgreSQL una foreign key non crea automaticamente un indice sulla colonna che la contiene, e Prisma non lo aggiunge per te sul campo scalare della relazione. Se filtri o fai join spesso su authorId, quella colonna va indicizzata a mano:

model Post {
  id        Int    @id @default(autoincrement())
  title     String
  authorId  Int
  author    User   @relation(fields: [authorId], references: [id])

  @@index([authorId])
}

È una delle cause più frequenti di lentezza nelle applicazioni Prisma andate in produzione senza una revisione degli indici. Le relazioni funzionano e i join restituiscono i dati giusti, ma ogni join fa un sequential scan perché manca l'indice sulla colonna di collegamento.

Indici composti e ordine delle colonne

Quando filtri o ordini su più colonne insieme servono gli indici composti, e qui l'ordine delle colonne conta:

model Order {
  id         Int      @id @default(autoincrement())
  customerId Int
  status     String
  total      Decimal
  createdAt  DateTime

  @@index([customerId, status])
}

Un indice su [customerId, status] serve bene le query che filtrano su customerId da solo oppure su customerId e status insieme, perché PostgreSQL sfrutta gli indici composti a partire dalla colonna più a sinistra.

Una query che filtra solo su status è un caso diverso. Fino a PostgreSQL 17 quell'indice le era di scarso aiuto. Dalla versione 18 il planner può usare lo skip scan sugli indici B-tree e saltare tra i valori della prima colonna, una tecnica che rende bene quando customerId ha pochi valori distinti. In una tabella ordini con migliaia di clienti il vantaggio si riduce molto, e la regola di progettazione resta quella: la colonna su cui filtri più spesso va per prima. Scegliere l'ordine guardando i pattern di accesso reali dell'applicazione è metà del lavoro.

Leggere un piano di esecuzione

Aggiungere indici a naso riempie il database di strutture che occupano spazio e rallentano le scritture senza velocizzare nulla. Il modo serio di lavorare è chiedere a PostgreSQL cosa sta facendo davvero, con EXPLAIN ANALYZE.

Prendi l'SQL che Prisma ha loggato e aprilo in psql o in un client come DBeaver o TablePlus. Nel log le condizioni compaiono con segnaposto come $1: sostituiscili con i valori presi da e.params, poi metti EXPLAIN ANALYZE davanti alla query:

EXPLAIN ANALYZE
SELECT id, title FROM "Post"
WHERE "authorId" = 42;

Nel risultato guardi due cose. La prima è il tipo di accesso: Seq Scan significa che PostgreSQL sta leggendo tutta la tabella, Index Scan o Index Only Scan che sta usando un indice. La seconda è la riga actual time, che riporta il tempo misurato e non una stima. Se dopo aver aggiunto un indice il sequential scan diventa un index scan e il tempo crolla, hai la conferma che quell'indice serve.

Se invece non cambia niente, non è detto che l'indice sia sbagliato. Su tabelle piccole, o con filtri che restituiscono gran parte delle righe, il planner preferisce il sequential scan perché leggere tutto costa meno che passare dall'indice. Ed è la scelta giusta. Prima di trarre conclusioni testa su un volume di dati realistico e assicurati che le statistiche della tabella siano aggiornate con ANALYZE.

Un'avvertenza pratica: EXPLAIN ANALYZE esegue davvero la query. Per una SELECT non è un problema, ma se stai analizzando un UPDATE o un DELETE su un database reale racchiudilo in una transazione e annullala alla fine:

BEGIN;
EXPLAIN ANALYZE
UPDATE "Order" SET status = 'archived'
WHERE "customerId" = 42;
ROLLBACK;

Il cuore dell'ottimizzazione è questo ciclo: guardi l'SQL generato, lo passi a EXPLAIN ANALYZE, correggi l'indice e rimisuri. Tutto il resto sono scorciatoie che a volte funzionano e a volte no.

Paginazione: offset contro cursore

Quasi tutte le liste sono paginate, e il modo più immediato di paginare con Prisma nasconde una trappola:

const page = await prisma.post.findMany({
  skip: 20000,
  take: 20,
  orderBy: { createdAt: 'desc' },
})

skip genera un OFFSET in SQL. Per saltare ventimila righe PostgreSQL deve comunque leggerle e poi scartarle. Alla prima pagina è istantaneo. Alla pagina mille l'utente aspetta, e il carico sul database cresce man mano che si va avanti nell'elenco.

La paginazione a cursore risolve il problema partendo dall'ultimo elemento visto invece che da un offset numerico:

const page = await prisma.post.findMany({
  take: 20,
  cursor: { id: lastSeenId },
  skip: 1,
  orderBy: { id: 'asc' },
})

Prisma traduce questa chiamata in una query che parte dalla riga del cursore, la scarta grazie a skip: 1 e prende le venti successive. Nella sostanza equivale a un WHERE id > lastSeenId ... LIMIT 20, che sfrutta l'indice sulla colonna di ordinamento e resta veloce alla pagina uno come alla pagina mille.

L'esempio ordina per id, ma un feed di solito va ordinato per data. Il cursore funziona anche lì, a una condizione: l'ordinamento deve essere univoco. Due post possono avere lo stesso createdAt, quindi serve una chiave di spareggio, e l'indice deve seguire lo stesso ordine:

model Post {
  // ...
  @@index([createdAt, id])
}

model Post {
  // ...
  @@index([createdAt, id])
}

Il compromesso è che non puoi saltare a una pagina arbitraria, cosa che per feed e scroll infiniti raramente serve. Quando il dataset è grande e la navigazione è sequenziale, il cursore è la scelta corretta.

Sposta il calcolo nel database

L'ultimo errore ricorrente è portare righe nell'applicazione per fare in JavaScript un lavoro che PostgreSQL fa meglio. Aggregare i dati è esattamente ciò per cui il database è costruito, e Prisma lo espone senza bisogno di scrivere SQL a mano. Riprendendo il modello Order di prima:

const stats = await prisma.order.groupBy({
  by: ['status'],
  _count: { id: true },
  _sum: { total: true },
})

Questa singola query restituisce conteggio e totale per ogni stato, calcolati dal database. L'alternativa, caricare tutti gli ordini e iterarli in memoria, trasferisce migliaia di righe sulla rete per produrre una manciata di numeri. Vale lo stesso per count, aggregate e per i filtri: ogni volta che stai per scrivere un .filter() o un .reduce() su un array di record appena letti dal database, chiediti se quella condizione poteva stare nel where.

Quando anche questo non basta, come succede con query analitiche complesse o funzioni specifiche di PostgreSQL che l'ORM non copre, resta il raw SQL con $queryRaw. È parametrizzato, quindi protetto dalla SQL injection, ma il tipo del risultato lo dichiari tu con un generic e Prisma non lo verifica. Se ti serve un tipo ricavato davvero dalla query, la strada è TypedSQL. In ogni caso è la porta da usare per ultima, dopo aver verificato che il problema non fosse semplicemente un indice mancante o una query scritta senza select.

Il capitolo successivo di questa storia riguarda cosa succede quando a rallentare non è più la singola query ma il numero di connessioni al database, il territorio del connection pooling e di strumenti come PgBouncer. Ma quella è un'altra puntata.

Ti piace capire perché una query è lenta e trovare il modo di renderla veloce? Da noi è lavoro di tutti i giorni, su progetti reali e con dati che crescono davvero. Se cerchi un posto dove fare questo tipo di sviluppo, contattaci.