State per iniziare un nuovo progetto – o per avviare una nuova azienda – e avete deciso di affidarvi a Postgres. La parte difficile (la scelta del database) è andata, e adesso inizia la parte divertente: assicurarsi di non doverci pensare più per qualche anno.

In questo post troverete alcuni consigli meno noti per configurare Postgres a lungo termine, dal pooling delle connessioni alle basi della sicurezza, fino alle estensioni e agli indici.

Configurare il pooling delle connessioni per Postgres con PGBouncer

Per impostazione predefinita, Postgres esegue il fork di un processo separato per ogni connessione client dal processo principale del sistema operativo. A bassi volumi, il tempo necessario per creare e distruggere questi processi, oltre al fatto che non vengono mai riutilizzati, non è importante. Potete impostare manualmente un numero di max_connections, ma alla fine, con lo scaling, è probabile che si verifichino dei problemi. Il pooling delle connessioni aiuta essenzialmente a “mettere in cache” questi processi e a riutilizzarli quando i client si connettono e si disconnettono dal database.

Sebbene sia possibile integrare il pooling delle connessioni nella logica dell’applicazione, molti optano per uno strumento di terze parti e nel caso di Postgres si tratta di PGBouncer. Si tratta di un connection pooler open source e leggero che si può installare o sul server del database o su quello dell’applicazione. Si può scegliere tra tre livelli di pooling:

  • Session pooling: rimane fedele al modello “le connessioni del client sono indefinite” e mantiene aperta una connessione per tutto il tempo in cui il client è connesso.
  • Transaction pooling: le connessioni durano per una singola transazione, dopodiché vengono rimandate al pool.
  • Statement pooling: le connessioni durano solo per una query, quindi se se ne ha più di una come parte di una transazione, non funzionerebbe affatto.

Molti scelgono il session pooling: è la soluzione più conservativa e meno rischiosa per l’interruzione delle connessioni, ma ogni applicazione è diversa e dovrete capire qual è la modalità giusta per i vostri vincoli.

L’impatto sulle prestazioni del pool di connessioni

La domanda da un milione di dollari, però, è: funziona davvero? Percona ha eseguito una serie di benchmark per capire l’impatto di PGBouncer sulle prestazioni. Con un numero ridotto di client contemporanei (<60), PGBouncer riduce di molto le transazioni al secondo (TPS) a causa dell’overhead del pooling. Ma quando si scala a oltre 100 client, si iniziano a vedere benefici significativi in termini di prestazioni.

PGBouncer
PGBouncer

Avete quindi bisogno di un pooler di connessioni per supportare i vostri primi utenti? Probabilmente no. Ma l’utilizzo di PGBouncer vi aiuterà una volta raggiunto un traffico anche basso/moderato.

Sicurezza di Postgres per principianti

Per le prime settimane di lavoro sul progetto, quando di solito ci sono solo uno o due sviluppatori che lavorano su un database vuoto, la sicurezza non è un aspetto fondamentale. Ma quando lancerete l’applicazione, dovrà esserlo. E con i database, a volte sembra che esistano un milione di modi diversi per bloccare tutto.

Limitare l’accesso a livello di host o di utente

Iniziamo con l’accesso. Postgres limita l’accesso in due modi:

  1. A livello di host: definendo indirizzi IP e domini con diritti di accesso
  2. A livello di utente: definendo gli utenti del database e le loro autorizzazioni

Il file pg_hba.conf nella directory PGDATA è il luogo in cui si definisce chi può connettersi a quali database. Se non c’è una voce per un client, questo non potrà accedere al database. Supponendo che il vostro application server sia in esecuzione da un’altra parte, ecco come potreste permettergli di accedere al database:

# Trust any connection via TCP/IP from this machine
host all 127.0.0.1 255.255.255.255 trust

Al di fuori del semplice “fidati di qualsiasi connessione da questa macchina”, ci sono molti modi diversi per autenticare il client con il server del database, dalla password all’ident ai certificati. Inoltre, se avete rinunciato alle grandi comodità di RDS (o Kinsta) e state eseguendo il backend sullo stesso server del database, potete connettervi tramite socket Unix invece che tramite TCP/IP.

Autorizzazione e privilegi

Una volta che il client sarà autenticato, dovrete occuparvi della questione dell’autorizzazione. Lo standard SQL definisce un sistema di privilegi e ogni oggetto di Postgres (come una tabella, una riga, ecc.) ha diversi privilegi che possono essere assegnati agli utenti: ad esempio SELECT e UPDATE, ma anche TRUNCATE, REFERENCES, TRIGGER, ecc. Si possono assegnare i privilegi agli utenti con il comando GRANT.

La prassi migliore è quella di seguire il principio del minimo privilegio, quindi l’utente del database che create per i vostri client deve poter accedere solo a ciò che gli serve.

Sicurezza a livello di riga

L’ultimo aspetto da trattare è la sicurezza a livello di riga. La sicurezza a livello di riga esiste dal punto di vista della tabella (non dell’utente) e limita l’accesso alle righe, il loro aggiornamento, ecc. Per impostazione predefinita, le tabelle non hanno l’RLS abilitato, quindi gli utenti potranno fare tutto ciò che le loro politiche di accesso impongono. Per abilitare l’RLS per una tabella, dovrete iniziare con:

ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY

E poi aggiungere un criterio. Supponiamo di voler limitare l’accesso in lettura alla tabella lightsaber_internals a persone fidate, che sono già definite nel gruppo di utenti jedi, in modo che solo il proprietario di una lightsaber (una spada laser) possa vedere i suoi dettagli interni. Ecco come fare:

ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY
CREATE POLICY jedi_only ON lightsaber_internals TO jedi
    USING (jedi = lightsaber_jedi);

Politiche RLS come questa sono utili quando si ha bisogno di sicurezza a un livello più granulare rispetto alle sole tabelle (situazioni con PII, ecc.).

Pensare in anticipo ai problemi di scalabilità

In tutte le startup in cui ho lavorato, c’è stato un certo livello di scalabilità manuale per quanto riguarda il database. Un giorno vi sveglierete e Datadog darà di matto perché il server Postgres è completamente esaurito. Indagherete, aggiornerete la pagina degli incidenti e alla fine aggiornerete le dimensioni del disco fino a quando non succederà di nuovo (anche se la prossima volta potrebbe essere un problema di RAM). Anticipare questi problemi può essere utile! Alcuni suggerimenti:

1. Impostare il monitoraggio del database

La maggior parte delle aziende in cui ho lavorato utilizza Datadog per il monitoraggio dei database. Se utilizzate un servizio di database gestito, potete probabilmente cavarvela utilizzando i loro strumenti nativi. Datadog ha pubblicato un ottimo post sul suo blog che illustra le principali metriche da tenere sotto controllo, come il throughput di lettura e scrittura, le scansioni sequenziali, i dati scritti su disco, ecc.

2. Mettere insieme le linee guida per scalare verticalmente

Quando il vostro team verrà chiamato “a rapporto” – e succederà – l’ultima cosa che vorrete è che tutti si mettano all’opera per risolvere il problema, quando nella maggior parte dei casi un semplice scaling risolverebbe il problema. È bene mettere a punto un piano di base per il team su ciò che rientra nell’ambito di applicazione quando si è a corto di spazio o di calcolo.

3. Vacuuming e messa a punto dell’auto-vacuuming

Quando si esegue un DELETE dati in Postgres o un UPDATE dei dati (che è funzionalmente equivalente all’eliminazione e all’inserimento), Postgres non li elimina subito (😱). Invece, vengono “marcati” come eliminati memorizzando l’ID della transazione dell’eliminazione in un’intestazione xmax; il motivo di questa scelta è che rende più semplice l’MVCC in Postgres. Ma se queste righe non vengono davvero cancellate, occuperanno spazio su disco e finiranno per creare problemi.

Il modo più semplice per eliminare queste righe è utilizzare il comando VACUUM . Potreste eseguire un vacuuming manuale ogni volta che si accumulano righe morte o anche solo impostarlo in modo che venga eseguito ogni x minuti, ma una strategia migliore è quella di eseguire l’auto-vacuuming in base al numero di righe morte accumulate. La regolazione dell’auto-vacuuming è un argomento complesso che esula dagli scopi di questo post: vi consiglio di leggere il post di 2ndQuadrant al riguardo.

4. Impostare una (o due) repliche di lettura

Questo è facile. Se prevedete un aumento significativo del traffico (un lancio imminente, ecc.), potete facilmente creare delle repliche di sola lettura (o almeno una); vi aiuteranno ad alleggerire un po’ il lavoro dell’istanza del DB principale.

Se optate per più repliche, otterrete l’ulteriore vantaggio di migliorare la disponibilità nel caso in cui una di esse vada in tilt per qualsiasi motivo. Aggiungere repliche è piuttosto semplice nella maggior parte dei fornitori di DBaaS, ma tenete d’occhio i costi: spesso hanno lo stesso prezzo di un’istanza DB principale, nonostante siano di sola lettura.

Aggiungere indici alle tabelle più grandi (previste)

Gli indici di database aiutano a velocizzare le query di lettura creando strutture di dati ausiliarie che rendono le scansioni più veloci. Per molti casi d’uso, l’aggiunta di un indice a una o due tabelle non è affatto un problema. In Postgres, potete creare un indice con il comando CREATE INDEX (ovvio). Quando interrogate una tabella, il database controlla se esiste un indice e, in caso affermativo, lo utilizza (potete verificare che questo avvenga con EXPLAIN, a proposito).

Il tipo di indice più diffuso in Postgres – e quello predefinito quando si utilizza CREATE INDEX – è un indice B-Tree. In sostanza, prende la colonna su cui volete creare un indice, la ordina e memorizza i puntatori alle righe ordinate. In questo modo, potete ottenere l’efficienza della ricerca binaria su qualsiasi colonna vogliate, non solo su quella su cui è ordinata la tabella attuale (ammesso che ce ne sia una). Potete leggere maggiori dettagli su come vengono implementati questi alberi nei documenti di Postgres qui.

Sebbene siano utili, gli indici non sono tutti una passeggiata da gestire: occupano spazio e, se non state attenti al numero e al tipo di indici che create, possono iniziare a peggiorare le prestazioni del database. Nessuno lo dice meglio dei documenti di Postgres:

“Gli indici sono utilizzati principalmente per migliorare le prestazioni del database (anche se un uso inappropriato può causare un rallentamento delle prestazioni)”

Quando create un indice, Postgres crea una tabella di ricerca che contiene l’indice e un puntatore al record dell’indice. Troppe tabelle di questo tipo occupano spazio su disco, allungano i tempi delle query INSERT e costringono il motore di ricerca a considerare più opzioni prima di scegliere come eseguire una query.

Bonus: aggiungere alcune estensioni di Postgres

Un aspetto che rende Postgres unico è il supporto nativo per le estensioni di terze parti. È possibile crearle con SQL e C e possono essere piccole come un paio di istruzioni o grandi come un’intera libreria di software. L’uso di estensioni disponibili pubblicamente o open source aiuta, come l’uso di un pacchetto software: perché scrivere il codice quando potete usare quello di qualcun altro? Ecco alcune delle estensioni di Postgres più popolari:

Timescale

Timescale è un’estensione di Postgres per lavorare con i dati delle serie temporali. In breve, rende le query (molto) più veloci e memorizza i dati delle serie temporali in modo molto efficiente. Potete trovare le istruzioni per l’installazione qui, oppure prendere in considerazione l’opzione cloud-hosted di Timescale se volete davvero gestire la vostra attività con i dati delle serie temporali (anche se probabilmente ne siete già a conoscenza).

PostGIS

PostGIS aggiunge a Postgres il supporto per l’archiviazione, l’indicizzazione e l’interrogazione di dati geografici (linee, poligoni, località, ecc.). Se utilizzate un provider cloud, la maggior parte di essi preinstalla PostGIS. Se invece dovete installarlo voi stessi, potete trovare le istruzioni per l’installazione qui.

pg_stat_staements

pg_stat_statements crea una vista nel database Postgres con le statistiche di ogni query eseguita sul database. potete vedere statistiche come il tempo di esecuzione della query (mean, median, average, ecc.), chi ha eseguito la query, le visite alla cache dei blocchi, il numero di blocchi scritti e molto altro (44 colonne totali in questa vista). Per installarlo, basta aggiungerlo al file .conf e riavviare il server.

pg_audit

pg_audit è utile per le aziende che potrebbero essere soggette a controlli dettagliati (ad esempio governativi, finanziari, ecc.). Potete fare in modo che Postgres registri ogni singola dichiarazione sul database impostando `log_statement=all`, ma questo non significa che le informazioni di cui avete bisogno saranno facili da cercare e trovare. pg_audit utilizza le funzioni di log interne di Postgres per rendere più facile trovare e lavorare con i log di cui un revisore potrebbe aver bisogno. Potete trovare le istruzioni per l’installazione qui.

Riepilogo

Postgres è un’opzione fantastica (e molto popolare) su cui costruire la vostra azienda e che noi di Kinsta siamo orgogliosi di supportare. Ci auguriamo che questi consigli vi aiutino a diventare operativi e pronti a scalare. Altri consigli o riflessioni sulla vostra esperienza? Fatecelo sapere qui.

Justin Gage

Justin is a technical writer and author of the popular Technically newsletter. He did his B.S. in Data Science before a stint in full-stack engineering and now focuses on making complex technical concepts accessible to everyone.