Il partitioning consente di gestire in modo efficace tabelle
di grandi dimensioni sui piu' moderni database relazionali.
Ma se il partizionamento non e' utilizzato correttamente le prestazioni anziche'
migliorare possono peggiorare anche in modo molto significativo.
In questa paginetta vediamo quali sono gli effetti dell'overpartitioning
in PostgreSQL.
Dopo una breve introduzione vedremo gli effetti dell'overpartitioning, la diagnosi, come prevenire il problema, come correggerlo e come agire nei casi clinici peggiori!
Il partizionamento di una relazione in pratica consiste nell'utilizzare strutture fisiche separate per ogni partizione sia per la tabella che per gli indici. Una tabella definita come partizionata in PostgreSQL non contiene alcun dato: i dati sono mantenuti nelle partizioni che sono normali relazioni . I vantaggi del partizionamento sono sopratutto due:
Ulteriori vantaggi si hanno nella gestione perche' le strutture dati sono piu' piccole
e quindi piu' facilmente trattabili nelle normali attivita' di manutenzione (eg. vacuum).
Il partizionamento va utilizzato con attenzione perche' vi sono limiti e controindicazioni.
Ad esempio il partizionamento e' utile solo con tabelle di grandi dimensioni
(eg. tabelle con oltre un milione di record oppure con dimensioni >>2GB),
le partizioni debbono essere in numero ragionevole (eg. tra 10 e 200) e bilanciate tra loro,
e' opportuno utilizzare uno solo livello di partizionamento o al massimo due,
le query debbono essere eseguite utilizzando sempre la chiave di partizionamento
per sfruttare il pruning
e la modalita' di svecchiamento ottimale e' il DROP o il DETACH di una partizione.
Ulteriori informazioni sul partizionamento in PostgreSQL si possono trovare nelle paginette su partizionamento nativo, partizionamento using inheritance, buoni consigli e cattivi esempi, lock con il partizionamento. Ora vedremo qualche ulteriore dettaglio per meglio comprendere l'overpartitioning.
Le query che vengono lanciate sulle tabelle partizionate
cercano di eseguire i passi dell'execution plan sul numero
minore possibile di partizioni.
Questa tecnica viene chiamata partition pruning
ed e' abilitata per default [Nda parametro enable_partition_pruning].
La verifica che il pruning venga effettuato e' molto importante e si esegue con l'EXPLAIN:
explain analyze select count(*) from measurement where logtime between '2022-01-01' and '2022-01-31';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=14.83..14.84 rows=1 width=8) (actual time=0.009..0.010 rows=1313 loops=1)
-> Bitmap Heap Scan on measurement_202201 measurement (cost=4.24..14.81 rows=9 width=0) (actual time=0.007..0.008 rows=1313 loops=1)
Recheck Cond: ((logtime >= '2022-01-01 00:00:00'::timestamp without time zone) AND (logtime <= '2022-01-31 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on measurement_202201_logtime_idx (cost=0.00..4.24 rows=9 width=0) (actual time=0.003..0.003 rows=1313 loops=1)
Index Cond: ((logtime >= '2022-01-01 00:00:00'::timestamp without time zone) AND (logtime <= '2022-01-31 00:00:00'::timestamp without time zone))
Planning Time: 0.157 ms
Execution Time: 0.045 ms
Nell'esempio la query viene eseguita su un'unica partizione perche' la condizione utilizzata consente di determinare dove sono memorizzati i dati.
E' pero' importante distinguere due casi:
In entrambe i casi le partizioni escluse non vengono fisicamente lette, tuttavia nel caso del pruning dinamico il piano contiene comunque tutte le partizioni e sono comunque elevati l'overhead di planning ed il consumo di memoria. Con il pruning dinamico, i sotto-nodi corrispondenti alle partizioni scartate a runtime vengono saltati durante l'esecuzione, ma il costo ed i lock necessari per generare l'intero albero nel piano rimane presente.
In PostgreSQL, a differenza di altri DBMS, ogni partizione e' a tutti gli effetti una tabella indipendente con i propri file su disco, catalogo di sistema, indici e metadati. L'overpartitioning in PostgreSQL puo' causare un degrado prestazionale e problemi amministrativi particolarmente significativi.
Ecco un elenco delle criticità e dei problemi tipici dell'overpartitioning, organizzati per area di impatto:
I sintomi dell'overpartitioning si manifestano sia a livello database che a livello del sistema operativo. Spesso casi le prestazioni degradano lentamente e solo su alcune query (e.g. quelle che non effettuano il pruning), ma sono frequenti anche casi in cui il degrado e' molto piu' grave ed improvviso.
Dal punto di vista dell'SQL, un indicatore immediato si ottiene confrontando il Planning Time
con l'Execution Time mediante EXPLAIN ANALYZE:
se la fase di planning richiede centinaia di millisecondi (o persino secondi) per una query che poi viene eseguita
in pochi millisecondi, si e' in presenza di overhead da overpartitioning.
Tuttavia, l'impatto piu' devastante si osserva a livello di Kernel. Analizzando il processo backend di PostgreSQL
con strace durante l'esecuzione di una query su una tabella con migliaia di partizioni,
si può notare una situazione di questo tipo:
# strace -p 424242 -c strace: Process 424242 attached ^Cstrace: Process 424242 detached % time seconds usecs/call calls errors syscall ------ ----------- ----------- --------- --------- ---------------- 99.07 9.369864 1503 6234 close 0.74 0.070419 11 6234 openat 0.19 0.017729 2 6234 lseek 0.00 0.000040 5 7 4 futex 0.00 0.000020 3 6 epoll_wait 0.00 0.000013 0 26 sendto 0.00 0.000013 1 11 3 recvfrom 0.00 0.000000 0 2 read 0.00 0.000000 0 2 write 0.00 0.000000 0 2 pwrite64 0.00 0.000000 0 6 kill 0.00 0.000000 0 2 fdatasync ------ ----------- ----------- --------- --------- ---------------- 100.00 9.458098 18766 7 total
La traccia evidenzia come il processo spenda oltre il 99% del tempo ad eseguire chiamate di sistema close() ed openat().
Questo accade perche' PostgreSQL imposta un limite di file aperti per processo (parametro max_files_per_process, default 1000).
Quando una query coinvolge centinaia o migliaia di partizioni ed indici separati, il backend supera la soglia di cache dei file descriptor aperti
ed e' costretto a continuare ad aprire, consultare e chiudere continuamente i file fisici su disco, generando un degrado prestazionale fortissimo.
In questo caso il rallentamento e' di due ordini di grandezza e l'effetto e' piu' vicino a quello di un blocco che di un semplice degrado prestazionale.
Si arriva veramente ad un crash quando si manifestano problemi di esaurimento memoria (OOM Killer),
che rappresentano l'effetto collaterale piu' subdolo e difficile da diagnosticare.
La proliferazione incontrollata di oggetti impatta la memoria allocata da ogni singolo processo:
La conseguenza e' che il consumo complessivo di RAM del server cresce in modo invisibile fino a saturare il sistema.
A quel punto interviene l'OOM Killer del sistema operativo che termina un processo PostgreSQL arbitrario o quasi.
Poiche' PostgreSQL adotta un'architettura a processi con memoria condivisa, la terminazione improvvisa (SIGKILL) di un qualsiasi backend
costringe il postmaster a riavviare immediatamente l'intera istanza ed entrare in recovery mode per garantire la consistenza dei dati.
Il risultato e' un crash generale del database che appare totalmente casuale nei log, ma la cui causa primaria risiede
esclusivamente nell'eccesso di partizioni.
E' sempre meglio prevenire che curare!
Gli effetti dell'overpartitioning possono essere improvvisi e drammatici portando praticamente al blocco della base dati:
un rallentamento di 100 volte nei tempi di risposta rende non utilizzabile un servizio, bisogna intervenire prima.
Verificare la presenza di un partizionamento eccessivo e' semplice: la presenza di partizioni vuote o di piccole dimensioni,
tabelle con piu' di 100 partizioni, piu' di 20.000 oggetti a livello di istanza, ... sono controlli semplici.
Possono essere utili le seguenti query:
SELECT r.rolname,
n.nspname,
t.relname,
COUNT(DISTINCT p.oid) + COUNT(DISTINCT c2.oid) AS total_partitions,
COUNT(DISTINCT COALESCE(c2.oid, p.oid)) FILTER (WHERE (COALESCE(c2.relkind, p.relkind) = 'r')) AS leaf_partitions,
COUNT(DISTINCT COALESCE(c2.oid, p.oid)) FILTER (WHERE (COALESCE(c2.relkind, p.relkind) = 'r')
AND COALESCE(c2.reltuples, p.reltuples, 0) <1000) AS few,
COUNT(DISTINCT COALESCE(c2.oid, p.oid)) FILTER (WHERE (COALESCE(c2.relkind, p.relkind) = 'r')
AND COALESCE(c2.relpages, p.relpages, 0) < 2) AS small,
COUNT(DISTINCT COALESCE(c2.oid, p.oid)) FILTER (WHERE (COALESCE(c2.relkind, p.relkind) = 'r')
AND COALESCE(c2.reltuples, p.reltuples, 0) = 0) AS empty,
COUNT(DISTINCT COALESCE(c2.oid, p.oid)) FILTER (WHERE (COALESCE(c2.relkind, p.relkind) = 'r')
AND COALESCE(c2.reltuples, p.reltuples, -1) < 0) AS unanalyzed
FROM pg_class t
JOIN pg_inherits i ON i.inhparent = t.oid
JOIN pg_class p ON p.oid = i.inhrelid
LEFT JOIN pg_inherits i2 ON i2.inhparent = p.oid
LEFT JOIN pg_class c2 ON c2.oid = i2.inhrelid
JOIN pg_roles r ON t.relowner = r.oid
JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE p.relkind IN ('r','p')
GROUP BY r.rolname, n.nspname, t.relname
HAVING COUNT(*) > 50
ORDER BY total_partitions DESC;
select nspname as schema_matrix, rolname as owner,
sum(case when relkind='r' THEN 1 ELSE 0 end) as table,
sum(case when relkind='i' THEN 1 ELSE 0 end) as index,
sum(case when relkind='p' THEN 1 ELSE 0 end) as p_tab,
sum(case when relkind='I' THEN 1 ELSE 0 end) as p_idx,
sum(case when relkind='v' THEN 1 ELSE 0 end) as view,
sum(case when relkind='S' THEN 1 ELSE 0 end) as seq,
sum(case when relkind='c' THEN 1 ELSE 0 end) as comp,
sum(case when relkind='f' THEN 1 ELSE 0 end) as foreing,
sum(case when relkind='t' THEN 1 ELSE 0 end) as toast,
sum(case when relkind='m' THEN 1 ELSE 0 end) as mat_view,
count(*) as TOTAL,
sum(case when relkind in ('r','p') THEN case when relispartition then 1 else 0 end else 0 end) as part,
sum(case when relkind in ('r','p') THEN case when relispartition then 0 else 1 end else 0 end) as n_part,
sum(case when relpersistence='u' THEN 1 ELSE 0 end) as unlog,
sum(case when relpersistence='t' THEN 1 ELSE 0 end) as temp
from pg_class, pg_roles, pg_namespace
where relowner=pg_roles.oid
and relnamespace=pg_namespace.oid
and rolname not in ('postgres', 'rdsadmin', 'enterprisedb')
group by rolname, nspname
order by nspname, rolname;
Quando ci si trova di fronte a una situazione di overpartitioning, la soluzione definitiva e' ridurre il numero totale di oggetti nel database consolidando le partizioni esistenti.
Prima di modificare la struttura, e' fondamentale individuare la dimensione corretta per le nuove partizioni. In PostgreSQL valgono alcune regole generali:
Per convertire, ad esempio, molte partizioni giornaliere in una singola partizione mensile senza fermare il database, la strategia migliore prevede l'uso di una tabella temporanea di staging e l'istruzione DETACH PARTITION CONCURRENTLY (introdotta in PostgreSQL 12).
I passaggi operativi sono:
measurement_2024_01).
ALTER TABLE measurement DETACH PARTITION measurement_2024_01_01 CONCURRENTLY; ALTER TABLE measurement DETACH PARTITION measurement_2024_01_02 CONCURRENTLY; -- ... per tutti i giorni del mese
ALTER TABLE measurement ATTACH PARTITION measurement_2024_01
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
DROP TABLE delle vecchie tabelle giornaliere disaccoppiate per liberare spazio e ripulire il catalogo.
In questo modo si ottiene un riallineamento drastico del catalogo di sistema, una sensibile diminuzione dei tempi di planning e la risoluzione dei problemi di memoria legati all'overpartitioning.
Ridurre il numero di partizioni tramite il consolidamento e' e rimane la soluzione primaria. Tuttavia, quando su un VLBD il volume dei dati raggiunge dimensioni enterprise (decine di Terabyte o Petabyte), mantenere centinaia di partizioni (es. da 200 fino a 1000) può diventare un requisito fisico inevitabile per garantire le finestre di manutenzione e lo svecchiamento.
In questi contesti limite, dove il partizionamento e' corretto ma spinto al massimo, un DBA deve mettere in campo accorgimenti specifici per consentire a PostgreSQL di gestire l'elevata cardinalità dell'albero delle partizioni:
Invece di creare un singolo livello con 1.000 partizioni figlie dirette sotto la tabella padre, e' preferibile distribuire l'albero su due livelli (es. Livello 1: Anno/Mese, Livello 2: Regione o Giorno).
Il planner analizza la gerarchia ad albero. Durante il pruning statico, se la query filtra per anno, il planner esclude immediatamente intere ramificazioni di sotto-partizioni senza doverle analizzare una per una, riducendo drasticamente il numero di oggetti caricati in memoria durante la pianificazione.
Attenzione: per ottenere vantaggi con il sottopartizionamento e' fondamentale che venga utilizzato il pruning statico.
Se il numero di oggetti e' fisicamente elevato, e' necessario adattare le risorse dell'istanza:
max_parallel_workers_per_gather e Parallel Append: PostgreSQL e' in grado di scansionare piu' partizioni in parallelo. Assicurarsi che il parallelismo sia ben bilanciato evita che query su piu' partizioni saturino tutti i processi worker dell'istanza.
relcache cresce con il numero di partizioni, avere centinaia di connessioni dirette porta rapidamente all'OOM. L'uso di un connection pooler rigoroso limita il numero di sessioni backend attive, contenendo il footprint di memoria complessivo dell'istanza.
Quando le partizioni sono molte, il pruning dinamico non basta a salvare la fase di planning. e' necessario "aiutare" il planner dall'applicazione:
WHERE data >= CURRENT_DATE - INTERVAL '7 days' con date calcolate lato applicazione e passate come costanti letterali (es. WHERE data >= '2026-08-17'). Questo trasforma un pruning dinamico in pruning statico a compile-time.
plan_cache_mode (es. forzando force_custom_plan o force_generic_plan a seconda del caso) se si nota che la ri-pianificazione continua consuma troppa CPU.
Dall'introduzione del partizionamento nativo con la versione 10 sono state molte le ottimizzazioni introdotte per migliorare la gestione di tabelle con un numero elevato di partizioni. L'utilizzo di una major version recente permette di sfruttare tutti i miglioramenti introdotti nel tempo.
Sulle tabelle con centinaia di partizioni, l'Autovacuum predefinito rischia di spendere troppo tempo a controllare partizioni storiche che non cambiano mai (dati read-only):
VACUUM FREEZE manuale e impostare la partizione (o le sue tabelle)
con parametri di autovacuum aggressivamente disattivati (autovacuum_enabled = false).
In questo modo il daemon Autovacuum salterà la scansione di centinaia di tabelle statiche, concentrandosi solo su quelle attive
[NdA il VACUUM FREEZE non e' solo consigliabile: va individuata una completa gestione; anche se non vengono piu' modificate le tuple invecchiano
e se raggiungono l'XID wraparound si blocca tutto].
Uno degli errori piu' insidiosi sui VLDB con centinaia di partizioni riguarda i tool di monitoraggio e gestione
(es. Telegraf, Nagios, Zabbix, o plugin dedicati).
La maggior parte dei template standard esegue periodicamente (es. ogni 1 o 5 minuti) query
che interrogano il catalogo di sistema per conteggiare la dimensione delle tabelle, il numero di indici o le statistiche di bloat.
Su una tabella overpartitioned, una semplice query di controllo sul catalogo può richiedere l'apertura e la scansione di migliaia di oggetti.
E' opportuno seguire queste indicazioni:
Con tabelle di grandissime dimensioni e centinaia di partizioni, i parametri di configurazione di default (sia di PostgreSQL che di Linux) possono risultare inadeguati. Diventa indispensabile intervenire su parametri del kernel e dell'istanza che normalmente non vengono toccati in installazioni standard:
ulimit e sysctl):
E' possibile aumentare il parametro di PostgreSQL max_files_per_process (default 1000) per evitare
la chiusura/apertura continua di file, ma vanno considerati anche i limiti imposti dall'OS.
E' necessario alzare sia i limiti utente per il servizio postgres (nofile
in /etc/security/limits.conf e la direttiva LimitNOFILE nel service di systemd)
sia il parametro globale di kernel fs.file-max, per evitare che l'attività contemporanea
di piu' backend blocchi il sistema.
vm.swappiness e vm.overcommit_memory):
Per prevenire l'OOM Killer improvviso legato alla relcache, e' critico impostare vm.swappiness a valori molto bassi (es. 1 o 10)
e configurare vm.overcommit_memory = 2 associato a un'attenta regolazione di vm.overcommit_ratio.
In questo modo Linux rifiuterà in modo ordinato le allocazioni di memoria eccessive (restituendo errore alle query)
anziche' terminare brutalmente i processi di PostgreSQL.
max_locks_per_transaction. Il valore di default (64) e' pensato per tabelle ordinarie:
una singola query su una tabella partizionata deve acquisire lock su tutte le sue sotto-partizioni ed indici;
se le partizioni sono centinaia, le transazioni falliranno immediatamente con l'errore "out of shared memory"
relativo alla tabella dei lock [NdA con max_locks_per_transaction va valutato anche max_connections perche' in realta'
il limite e' compessivo ed il messaggio out of shared memory non e' cosi' approppriato, si tratta del limite
della tabella di lock mantenuta in memoria].
Ulteriori informazioni si possono trovare
nelle pagine Partizionamento in PostgreSQL,
Lock con il partizionamento in PostgreSQL.
Un documento utile sul tuning e'
Ottimizzazione SQL in PostgreSQL.
Tutti i dettagli sul partizionamento si trovano ovviamente sulla documentazione ufficiale.
Titolo: Overpartitioning in PostgreSQL
Livello: Intermedio
Data:
14 Febbraio 2026 ❤️
Versione: 1.0.1 - 24 Agosto 2026 ☀️
Autore: mail [AT] meo.bogliolo.name