PostgreSQL vs MariaDB per app CRUD transazionali

Quando un'app CRUD supera il prototipo

Un prototipo CRUD di solito sembra veloce perché i dati sono pochi, il team è piccolo e il traffico prevedibile. Puoi cavartela con query semplici, un paio di indici e aggiustamenti manuali dello schema. Poi l'app ottiene utenti reali, flussi di lavoro reali e scadenze reali.

La crescita cambia il carico di lavoro. Liste e dashboard vengono aperte per tutto il giorno. Più persone modificano gli stessi record. I job in background iniziano a scrivere in batch. È a quel punto che «funzionava ieri» diventa pagine lente, timeout casuali e attese di lock nelle ore di punta.

Probabilmente hai superato il limite se vedi cose come pagine elenco che rallentano dopo la pagina 20, rilasci che includono backfill dei dati (non solo nuove colonne), più campi "flessibili" per metadata e payload di integrazione, o ticket di supporto che dicono "salvare ci mette un'eternità" nei momenti di traffico.

È allora che confrontare PostgreSQL e MariaDB smette di essere una preferenza di brand e diventa una domanda pratica. Per workload CRUD transazionali, i dettagli che di solito decidono il risultato sono le opzioni di indicizzazione quando le query si complicano, la sicurezza delle migrazioni quando le tabelle sono grandi, l'archiviazione e interrogazione JSON, e le funzionalità di query che riducono il lavoro lato applicazione.

Questo pezzo si concentra su quei comportamenti del database. Non entra in profondità su dimensionamento dei server, prezzi cloud o contratti vendor. Quelle cose contano, ma spesso sono più facili da cambiare dopo rispetto a uno schema e a uno stile di query da cui il prodotto dipende.

Parti dai requisiti della tua app, non dal brand del database

Il punto di partenza migliore non è «PostgreSQL vs MariaDB». È il comportamento quotidiano della tua app: creare record, aggiornare poche colonne, elencare risultati filtrati e restare corretta quando molte persone cliccano contemporaneamente.

Annota cosa fanno le tue schermate più impegnative. Quante letture avvengono per ogni scrittura? Quando avvengono i picchi (accessi mattutini, report di fine mese, grandi import)? Cattura i filtri e gli ordinamenti esatti su cui fai affidamento, perché quelli guidano il design degli indici e i pattern di query più avanti.

Poi definisci i non negoziabili. Per molti team significa consistenza rigorosa per soldi o inventario, una traccia di audit per "chi ha cambiato cosa" e query di reporting che non si rompano ogni volta che lo schema evolve.

La realtà operativa conta tanto quanto le funzionalità. Decidi se userai un database gestito o self-hosted, quanto velocemente devi ripristinare dai backup e qual è la tua tolleranza per finestre di manutenzione.

Infine, definisci "abbastanza veloce" con alcuni obiettivi chiari. Per esempio: latenza API p95 sotto carico normale (200–400 ms), p95 sotto picco di concorrenza (magari 2x il normale), attese massime accettabili sui lock durante gli update (sotto 100 ms) e limiti di tempo per backup e restore.

Fondamentali di indicizzazione che guidano la velocità CRUD

La maggior parte delle app CRUD sembra veloce finché le tabelle non raggiungono milioni di righe e ogni schermata diventa "una lista filtrata con ordinamento". A quel punto, l'indicizzazione è la differenza tra una query da 50 ms e un timeout da 5 secondi.

Gli indici B-tree sono il cavallo di battaglia predefinito sia in PostgreSQL sia in MariaDB. Aiutano quando filtri su una colonna, fai join su chiavi e quando il tuo ORDER BY corrisponde all'ordine dell'indice. La differenza reale di prestazioni di solito dipende dalla selettività (quante righe corrispondono) e dal fatto che l'indice possa soddisfare sia il filtro sia l'ordinamento senza scandagliare righe extra.

Man mano che le app maturano, gli indici compositi contano più di quelli su singola colonna. Un pattern comune è filtro multi-tenant più uno stato più un ordinamento temporale, come (tenant_id, status, created_at). Metti prima il filtro più consistente (spesso tenant_id), poi il filtro successivo, poi la colonna su cui ordini. Tende a funzionare meglio rispetto a indici separati che l'optimizer non riesce a combinare efficacemente.

Le differenze emergono con indici "più intelligenti". PostgreSQL supporta indici parziali ed espressione, ottimi per schermate mirate (per esempio, indicizzare solo i ticket "aperti"). Sono potenti, ma possono sorprendere i team se le query non corrispondono esattamente al predicato.

Gli indici non sono gratis. Ogni insert e update deve aggiornare anche ogni indice, quindi è facile velocizzare una schermata e rallentare silenziosamente ogni scrittura.

Un modo semplice per rimanere disciplinati:

• Aggiungi un indice solo per un percorso di query reale (una schermata o una chiamata API che puoi nominare).
• Preferisci un buon indice composito rispetto a molti sovrapposti.
• Ricontrolla gli indici dopo i cambiamenti di funzionalità e rimuovi il peso morto.
• Pianifica la manutenzione: PostgreSQL ha bisogno di VACUUM/ANALYZE regolari per evitare bloat; anche MariaDB si affida a buone statistiche e qualche pulizia occasionale.
• Misura prima e dopo, invece di fidarti dell'intuito.

Indicizzazione per schermate reali: liste, ricerca e paginazione

La maggior parte delle app CRUD passa il tempo su poche schermate: una lista con filtri, una casella di ricerca e una pagina di dettaglio. La scelta del database conta meno del fatto che i tuoi indici corrispondano a quelle schermate, ma i due motori ti danno strumenti diversi quando le tabelle crescono.

Per le pagine elenco, pensa in questo ordine: prima il filtro, poi l'ordinamento, poi la paginazione. Un pattern comune è "tutti i ticket per account X, status in (open, pending), più recenti prima". Un indice composito che inizia con le colonne di filtro e termina con la colonna di ordinamento di solito vince.

La paginazione merita cura. La paginazione con offset (pagina 20 con OFFSET 380) rallenta man mano che scorri perché il database deve comunque saltare le righe precedenti. La paginazione keyset è più stabile: passi l'ultimo valore visto (come created_at e id) e chiedi i "prossimi 20 più vecchi di quello". Riduce anche duplicati e buchi quando nuove righe arrivano a metà scroll.

PostgreSQL offre un'opzione utile per le liste: indici "covering" usando INCLUDE, che possono abilitare index-only scans quando la visibility map lo permette. MariaDB può fare letture covering, ma di solito lo ottieni inserendo le colonne necessarie direttamente nella definizione dell'indice. Questo può rendere gli indici più larghi e costosi da mantenere.

Probabilmente ti servono indici migliori se un endpoint lista rallenta mentre la tabella cresce pur restituendo solo 20–50 righe, l'ordinamento diventa lento a meno che non rimuovi ORDER BY, o l'I/O salta durante filtri semplici. Le query più lunghe tendono anche ad aumentare le attese di lock nei periodi di punta.

Esempio: una schermata ordini che filtra per customer_id e status e ordina per created_at di solito beneficia di un indice che inizia con (customer_id, status, created_at). Se poi aggiungi "cerca per numero d'ordine", quello è tipicamente un indice separato, non qualcosa da apporre sull'indice della lista.

Migrazioni: mantenere i rilasci sicuri quando i dati crescono

Le migrazioni smettono presto di essere "cambiare una tabella". Una volta che ci sono utenti reali e storia reale, devi anche gestire backfill dei dati, stringere i vincoli e pulire forme di dati vecchie senza rompere l'app.

Un default sicuro è espandi, backfill, contrae. Aggiungi ciò che ti serve in modo che non interrompa il codice esistente, copia o calcola i dati in piccoli passi, poi rimuovi il percorso vecchio solo quando sei sicuro.

In pratica di solito significa aggiungere una nuova colonna nullable o una nuova tabella, popolare i dati a batch mantenendo le scritture consistenti, convalidare più tardi con vincoli come NOT NULL, chiavi esterne e regole uniche, e solo allora rimuovere colonne, indici e percorsi di codice obsoleti.

Non tutte le modifiche di schema sono uguali. Aggiungere una colonna è spesso a basso rischio. Aggiungere un indice può essere comunque costoso su tabelle grandi, quindi pianificalo per traffico basso e misura. Cambiare il tipo di una colonna è spesso il più rischioso perché può riscrivere dati o bloccare le scritture. Un pattern più sicuro comune è: crea una nuova colonna col nuovo tipo, backfilla, poi sposti letture e scritture.

I rollback cambiano significato a scala. Fare rollback dello schema a volte è facile; fare rollback dei dati spesso non lo è. Sii esplicito su quello che puoi annullare, specialmente se una migrazione include delete distruttive o trasformazioni con perdita di dati.

Supporto JSON: campi flessibili senza dolore futuro

I campi JSON sono allettanti perché ti permettono di rilasciare più in fretta: campi extra nei form, payload di integrazione, preferenze utente e note di sistemi esterni possono tutti entrare senza cambiare lo schema. La sfida è decidere cosa appartiene al JSON e cosa merita colonne reali.

In entrambi i motori, JSON funziona meglio quando viene raramente filtrato e soprattutto mostrato, conservato per debug, tenuto come blob "impostazioni" per utente o tenant, o usato per piccoli attributi opzionali che non guidano il reporting.

L'indicizzazione del JSON è dove i team si sorprendono. Interrogare una chiave JSON una volta è facile. Filtrare e ordinare su di essa su tabelle grandi è dove le prestazioni possono collassare. PostgreSQL ha ottime opzioni per indicizzare percorsi JSON, ma serve comunque disciplina: scegli poche chiavi su cui filtri davvero e indicizzale, poi tieni il resto come payload non indicizzato. MariaDB può interrogare JSON, ma pattern complessi di "cerca dentro JSON" spesso diventano fragili e più difficili da mantenere veloci.

Il JSON indebolisce anche i vincoli. È più difficile far rispettare "deve essere uno di questi valori" o "sempre presente" dentro un blob non strutturato, e gli strumenti di reporting generalmente preferiscono colonne tipizzate.

Una regola che scala: inizia con JSON per gli sconosciuti, ma normalizza in colonne o tabelle figlie quando (1) ci filtri o ordini, (2) hai bisogno di vincoli, o (3) lo vedi comparire nei dashboard ogni settimana. Salvare la risposta API completa di spedizione di un ordine come JSON spesso va bene. Campi come delivery_status e carrier di solito meritano colonne reali una volta che il supporto e il reporting ne dipendono.

Funzionalità di query che emergono nelle app mature

All'inizio, la maggior parte delle app CRUD gira su SELECT, INSERT, UPDATE e DELETE semplici. Più avanti aggiungi feed di attività, viste di audit, report amministrativi e ricerche che devono sembrare istantanee. Qui la scelta inizia ad apparire come un compromesso di funzionalità.

CTE e subquery aiutano a mantenere query complesse leggibili. Sono utili quando costruisci un risultato a passi (filtra ordini, fai join con pagamenti, calcola totali). Ma la leggibilità può nascondere il costo. Quando una query rallenta, potresti dover riscrivere una CTE come subquery o join e poi ricontrollare il piano di esecuzione.

Le funzioni finestra diventano importanti la prima volta che qualcuno chiede di "classificare i clienti per spesa", "mostrare totali progressivi" o "ultimo stato per ticket". Spesso sostituiscono loop complessi in applicazione e riducono il numero di query.

Gli upsert sono un altro requisito da adulti. Quando avvengono retry (reti mobili, job in background), gli upsert ti permettono di scrivere in sicurezza senza creare record doppi:

• PostgreSQL: INSERT ... ON CONFLICT
• MariaDB: INSERT ... ON DUPLICATE KEY UPDATE

La ricerca è la funzionalità che sorprende i team. La ricerca full-text built-in può coprire cataloghi prodotti, basi di conoscenza e note di supporto. La ricerca trigram è utile per type-ahead e tolleranza agli errori di battitura. Se la ricerca diventa centrale (ranking complesso, molti filtri, traffico elevato), uno strumento di ricerca esterno può valere il sovraccarico operativo.

Esempio: un portale ordini inizia con "elenca ordini". Un anno dopo deve "mostrare l'ultimo ordine per cliente, classificare per spesa mensile e cercare nomi digitati male". Quelle sono capacità del database, non solo lavoro UI.

Transazioni, lock e concorrenza sotto carico

Quando il traffico è basso, la maggior parte dei database sembra andare bene. Sotto carico, la differenza spesso riguarda come gestisci le modifiche concorrenti agli stessi dati, non la velocità pura. Sia PostgreSQL sia MariaDB possono eseguire un workload CRUD transazionale, ma devi comunque progettare per la contesa.

Isolamento in parole semplici

Una transazione è un gruppo di passaggi che dovrebbero riuscire insieme. L'isolamento controlla cosa possono vedere le altre sessioni mentre quei passaggi sono in esecuzione. Un isolamento più alto evita letture sorprendenti, ma può aumentare le attese. Molte app partono dai valori di default e stringono l'isolamento solo per i flussi che ne hanno davvero bisogno (per esempio, addebitare una carta e aggiornare un ordine).

Cosa causa davvero i problemi di lock

I problemi di locking nelle app CRUD di solito derivano da pochi colpevoli ricorrenti: righe "calde" che tutti aggiornano, contatori che cambiano ad ogni azione, code di job dove molti worker cercano di prendere lo stesso "prossimo job" e transazioni lunghe che tengono lock mentre passano altre operazioni (o tempo utente).

Per ridurre la contesa, tieni le transazioni corte, aggiorna solo le colonne necessarie ed evita chiamate di rete dentro una transazione.

Una buona abitudine è ritentare sui conflitti. Se due operatori salvano modifiche sullo stesso ticket in contemporanea, non fallire in silenzio. Rileva il conflitto, ricarica l'ultima riga e chiedi all'utente di riapplicare le modifiche.

Per individuare i problemi presto, monitora deadlock, transazioni a lunga esecuzione e query che passano tempo in attesa invece che in esecuzione. Fai dei log delle query lente parte della routine, specialmente dopo rilasci che aggiungono nuove schermate o job in background.

Operazioni che diventano importanti dopo il lancio

Dopo il lancio, non stai più ottimizzando solo per la velocità delle query. Ottimizzi per il ripristino, il cambiamento sicuro e prestazioni prevedibili.

Un passo comune è aggiungere una replica. Il primario gestisce le scritture e una replica può servire pagine in lettura pesante come dashboard o report. Questo cambia come pensi alla freschezza: alcune letture possono avere qualche secondo di lag, quindi la tua app deve sapere quali schermate devono leggere dal primario (per esempio, "ordine appena effettuato") e quali possono tollerare dati leggermente più vecchi (per esempio, riepiloghi settimanali).

I backup sono solo metà del lavoro. Conta se puoi ripristinare velocemente e correttamente. Pianifica test di restore regolari in un ambiente separato, poi valida le basi: l'app si connette, le tabelle chiave esistono e le query critiche restituiscono risultati attesi. I team spesso scoprono troppo tardi di aver fatto il backup della cosa sbagliata, o che il tempo di restore supera di gran lunga il loro budget di downtime.

Anche gli upgrade smettono di essere "click and hope". Pianifica una finestra di manutenzione, leggi le note di compatibilità e testa il percorso di upgrade con una copia dei dati di produzione. Anche i bump di versione minori possono cambiare piani di query o il comportamento attorno a indici e funzioni JSON.

Semplice osservabilità ripaga presto. Parti con i log di query lente e le query top per tempo totale, saturazione delle connessioni, lag di replicazione (se usi repliche), hit ratio della cache e pressione I/O, attese di lock e eventi di deadlock.

Come scegliere: un processo di valutazione pratico

Se sei bloccato, smetti di leggere liste di funzionalità e fai una piccola prova col tuo carico. Lo scopo non è un benchmark perfetto, ma evitare sorprese quando le tabelle raggiungono milioni di righe e il ciclo di release accelera.

1) Costruisci un test che assomigli alla produzione

Scegli una fetta della tua app che rappresenti il dolore reale: una o due tabelle chiave, poche schermate e i percorsi di scrittura dietro di esse. Raccogli le tue query principali (quelle dietro le liste, le pagine di dettaglio e i job in background). Carica conteggi di righe realistici (almeno 100x i dati del prototipo, con forma simile). Aggiungi gli indici che pensi di dover usare, poi esegui le stesse query con gli stessi filtri e ordinamenti e cattura i tempi. Ripeti mentre avvengono scritture (uno script semplice che inserisce e aggiorna righe è sufficiente).

Un esempio rapido è una lista "Clienti" che filtra per status, cerca per nome, ordina per ultima attività e pagina. Quella singola schermata spesso rivela se la tua indicizzazione e il comportamento del planner invecchieranno bene.

2) Prova le migrazioni come in un rilascio reale

Crea una copia staging del dataset e prova i cambiamenti che sai arriveranno: aggiungere una colonna, cambiare un tipo, backfillare dati, aggiungere un indice. Misura quanto ci mette, se blocca le scritture e cosa significa il rollback quando i dati sono già cambiati.

3) Usa una scheda di valutazione semplice

Dopo i test, valuta ogni opzione su performance per le tue query reali, correttezza e sicurezza (vincoli, transazioni, casi limite), rischio di migrazione (lock, downtime, opzioni di recovery), sforzo operativo (backup/restore, replicazione, monitoraggio) e confidenza del team.

Scegli il database che riduce il rischio per i prossimi 12 mesi, non quello che vince un micro-test.

Errori comuni e trappole

I problemi di database più costosi spesso nascono come "vittorie rapide". Entrambi i database possono eseguire un'app CRUD transazionale, ma le abitudini sbagliate danneggeranno entrambi quando traffico e dati cresceranno.

Una trappola comune è trattare il JSON come scorciatoia per tutto. Un campo "extras" flessibile va bene per dati veramente opzionali, ma campi core come status, timestamp e chiavi esterne dovrebbero rimanere colonne reali. Altrimenti finisci con filtri lenti, validazione scomoda e refactor dolorosi quando il reporting diventa prioritario.

L'indicizzazione ha la sua trappola: aggiungere un indice per ogni filtro che vedi su una schermata. Gli indici accelerano le letture, ma rallentano le scritture e rendono le migrazioni più pesanti. Indicizza ciò che gli utenti usano davvero, poi valida con carico misurato.

Le migrazioni possono mordere quando bloccano le tabelle. Cambiamenti "big-bang" come riscrivere una colonna grande, aggiungere un NOT NULL con default o creare un indice grande possono bloccare le scritture per minuti. Spezza i cambiamenti rischiosi in passi e schedulali quando l'app è tranquilla.

Non fare troppo affidamento sui valori di default dell'ORM per sempre. Quando una vista lista passa da 1.000 a 10 milioni di righe, devi leggere i piani di esecuzione, individuare indici mancanti e correggere join lenti.

Segnali d'allarme rapidi: campi JSON usati per filtrare e ordinare primariamente, numero di indici che cresce senza misurare l'impatto sulle scritture, migrazioni che riscrivono grandi tabelle in un unico deploy e paginazione senza ordinamento stabile (che porta a righe mancanti o duplicate).

Checklist rapida prima di decidere

Prima di prendere una posizione, fai un controllo di realtà basato sulle tue schermate più usate e sul tuo processo di rilascio.

• Le tue schermate principali restano veloci al picco di carico? Testa la pagina elenco più lenta con filtri, ordinamenti e paginazione reali e conferma che gli indici corrispondano esattamente a quelle query.
• Riesci a deployare cambi schema in sicurezza? Scrivi un piano espandi-backfill-contrae per il prossimo cambiamento critico.
• Hai una regola chiara per JSON vs colonne? Decidi quali chiavi JSON devono essere ricercabili o ordinabili e quali sono veramente flessibili.
• Dipendi da funzionalità specifiche di query? Controlla il comportamento di upsert, funzioni finestra, CTE e se hai bisogno di indici funzionali o parziali.
• Sai gestirlo dopo il lancio? Dimostra di poter ripristinare da backup, misura le query lente e basi la latenza e le attese di lock.

Esempio: da tracciamento ordini semplice a un portale clienti trafficato

Immagina un portale clienti che parte semplice: i clienti si loggano, vedono ordini, scaricano fatture e aprono ticket. La prima settimana quasi ogni database transazionale sembra andare bene. Le pagine sono veloci e lo schema è piccolo.

Dopo qualche mese, arrivano i momenti di crescita. I clienti chiedono filtri come "ordini spediti negli ultimi 30 giorni, pagati con carta, con rimborso parziale". Il supporto vuole esportazioni rapide in CSV per le revisioni settimanali. Finanza vuole una traccia di audit: chi ha cambiato lo stato di una fattura, quando e da cosa a cosa. I pattern di query diventano più ampi e variegati rispetto alle schermate originali.

Qui la decisione diventa su funzionalità specifiche e sul loro comportamento sotto carico reale.

Se aggiungi campi flessibili (istruzioni di consegna, attributi personalizzati, metadata dei ticket), il supporto JSON conta perché prima o poi vorrai interrogare quei campi. Sii onesto sul fatto che il tuo team dovrà indicizzare percorsi JSON, validare forme e mantenere le prestazioni prevedibili man mano che il JSON cresce.

Il reporting è un altro punto critico. Nel momento in cui fai join tra ordini, fatture, pagamenti e ticket con molti filtri, ti interesseranno indici compositi, pianificazione delle query e quanto sia facile evolvere indici senza downtime. Le migrazioni smettono di essere "esegui uno script il venerdì" e diventano parte di ogni release, perché una piccola modifica di schema può toccare milioni di righe.

Una strada pratica è scrivere cinque schermate reali e le esportazioni che prevedi tra sei mesi, includere tabelle di audit fin dall'inizio, benchmarkare con dimensioni realistiche usando le tue query più lente (non un hello-world CRUD) e documentare regole di team per uso di JSON, indicizzazione e migrazioni.

Se vuoi muoverti velocemente senza costruire a mano ogni livello, AppMaster (appmaster.io) può generare backend pronti per la produzione, web app e app mobile native da un modello visuale. Aiuta anche a trattare schermate, filtri e processi di business come carichi di query reali presto, così da individuare rischi di indicizzazione e migrazione prima che colpiscano la produzione.