Padroneggiare le prestazioni di PostgreSQL - Le pratiche di WeWard per database efficienti

In WeWard, dove ogni millisecondo conta per migliorare l'esperienza dell'utente, abbiamo intrapreso un viaggio ambizioso per trasformare PostgreSQL in un motore dalle prestazioni eccezionali. Questo articolo esplora le sfide affrontate, le soluzioni implementate e le best practice che ci hanno permesso di ottimizzare PostgreSQL in modo significativo.

🎯 Perché l'ottimizzazione del database è fondamentale?

In un mondo in cui gli utenti si aspettano tempi di risposta immediati, i database lenti possono causare frustrazione e opportunità perse. In WeWard, questa sfida si traduce in milioni di transazioni giornaliere e query complesse che richiedono prestazioni impeccabili. Le nostre sfide comprendevano query lente, carico eccessivo e un'architettura che richiedeva una maggiore scalabilità.

Ecco come abbiamo superato questi ostacoli.

⚙ Fase 1: ottimizzazione della query

La base di qualsiasi ottimizzazione di PostgreSQL inizia con l'analisi delle query. Abbiamo identificato le inefficienze studiando i piani di esecuzione delle query.

Analisi del piano di esecuzione

Utilizzo SPIEGARE e ANALIZZAREabbiamo ottenuto una visione a raggi X delle nostre richieste.

Esempio di query problematica:

SELECT level_customer.*
FROM level_customer
JOIN level ON level.id = level_customer.level_id
WHERE level_customer.customer_id = XXXXXXX
 AND level_customer.start_date <= '2024-05-07'::date
 AND level.version = 2
ORDER BY level_customer.start_date DESC, level.value DESC
LIMIT 1;


Problemi rilevati:

• Mancanza di un indice per gestire in modo efficiente l'ordinamento.
• Strategia di unione subottimale scelta dal pianificatore.

Soluzione: creazione di indici:

CREARE INDICE idx_level_customer_date_value
SU level_customer (customer_id, start_date DESC, level_id DESC);


Adeguamento del pianificatore:Abbiamo forzato PostgreSQL a usare Unioni ad anello annidate invece di quello predefinito Unisci Hash per questa specifica query.

Viste materializzate:per le query complesse, le viste materializzate riducono l'overhead di calcolo su ogni esecuzione, fornendo prestazioni notevolmente stabili.

Passo 2: Distribuzione del carico con la replica

Con milioni di interrogazioni giornaliere, abbiamo adottato un'architettura basata su repliche per distribuire il carico.

Impostazione dell'architettura:

• Nodo primario: Gestisce le operazioni di lettura e scrittura, garantendo la coerenza dei dati.
• Nodo di replica: Dedicato alle operazioni di sola lettura, riduce il carico sul nodo primario e migliora i tempi di risposta per le query ad alta intensità di lettura.

Vantaggi:

• Miglioramento delle prestazioni: L'offloading delle query di lettura sulle repliche riduce la latenza e aumenta il throughput.
• Alta disponibilità: In caso di guasto del nodo primario, è possibile promuovere le repliche, garantendo la continuità del servizio.
• Scalabilità: È possibile aggiungere più nodi di replica per scalare orizzontalmente all'aumentare del traffico.

Considerazioni tecniche:

• Latenza di replica: Potrebbe verificarsi un leggero ritardo tra il nodo primario e quello di replica.
• Eventuale coerenza: Le query di lettura sulle repliche possono restituire dati leggermente obsoleti.
• Monitoraggio: Il monitoraggio proattivo è essenziale per garantire una replica affidabile.

🗂 Passo 3: Gestione di tabelle di grandi dimensioni con il partizionamento

Perché il partizionamento?

Le tabelle contenenti miliardi di righe possono rallentare notevolmente le operazioni di lettura e scrittura. Il partizionamento divide una tabella in sottoinsiemi più piccoli, migliorando l'efficienza delle query.

Strumento: pg_partman

Vantaggi:

• Gestione automatizzata delle partizioni: Gestisce la creazione e l'eliminazione delle partizioni in base a criteri predefiniti.
• Miglioramento delle prestazioni delle query: Le query si rivolgono solo alle partizioni rilevanti.
• Manutenzione dei dati storici più semplice: Semplifica l'archiviazione e l'eliminazione senza interrompere i dati correnti.

🚦 Fase 4: Stabilizzazione della connessione

Ogni connessione PostgreSQL consuma risorse di sistema. Per evitare il sovraccarico del server:

• Limiti di connessione: max_connessioni è stato regolato in base all'hardware disponibile e alle esigenze dell'applicazione.
• Pooling delle connessioni: Il riutilizzo delle connessioni esistenti ha ridotto il carico del server e migliorato la reattività dell'applicazione.

Parametri chiave della memoria PostgreSQL:

• buffer_condivisi: Controlla l'allocazione della memoria per la memorizzazione nella cache dei dati a cui si accede di frequente.
• lavoro_mem: Definisce la memoria per l'ordinamento e le tabelle hash durante l'esecuzione delle query.

📈 Passo 5: un'architettura scalabile con Data Lake

I dati critici ("caldi") rimangono in PostgreSQL, mentre i dati a cui si accede meno frequentemente ("freddi") vengono trasferiti su Amazon S3 o Google Cloud Storage. L'analisi dei dati freddi viene eseguita con Google BigQuery.

Vantaggi:

• Scalabilità: Capacità di archiviazione illimitata su piattaforme cloud.
• Efficienza dei costi: Riduzione dei costi per l'archiviazione dei dati a freddo.
• Flessibilità: Strumenti adatti per ogni tipo di dati.

👁 Fase 6: Monitoraggio e osservabilità

WeWard utilizza strumenti di monitoraggio avanzati come:

• AWS Performance Insights: Visualizzazione delle prestazioni del database in tempo reale.
• Monitoraggio delle prestazioni delle applicazioni (APM): Tracciamento delle transazioni end-to-end.
• Monitoraggio delle prestazioni del database (DBM): Approfondimenti sulle prestazioni delle query.

Vantaggi:

• Rilevamento rapido dei problemi.
• Ottimizzazione delle prestazioni.

🏁 Conclusione: Una solida base per il futuro

Combinando una rigorosa ottimizzazione delle query, una gestione intelligente delle risorse e architetture moderne, PostgreSQL è diventato un alleato fondamentale per WeWard. Queste soluzioni sono applicabili a qualsiasi organizzazione che voglia massimizzare il potenziale del database.

💬 Quali sono le vostre strategie per ottimizzare PostgreSQL? Condividete le vostre esperienze e partecipate alla conversazione!

👉 Esplora la nostra app mobile: WeWard.

‍