Comprovar la mida de les taules en PostgreSQL i detectar un creixement anormal

Detectar quan una taula a PostgreSQL està creixent més de l’esperat no és una cosa que puguis delegar només a la monitorització tradicional. No tots els pics d’ús són alarmes, i no tot el que creix ràpid és un problema. Però hi ha senyals clars que val la pena vigilar, i formes pràctiques d’obtenir dades precises sense convertir-lo en una càrrega diària.

Veure la mida actual de les taules no és suficient, però és el punt de partida

Quan una base comença a créixer més de l’esperat, el primer que es mira és la mida de les taules. No hauria de sorprendre que el top 10 per mida no coincideixi amb el top 10 per volum d’ ús. Ni per nombre de files.

Una de les consultes més clares per a obtenir la mida total de cada taula (incloent-hi TOAST i els índexs) és aquesta:

SELECT 
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,

    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_and_toast,
    n_live_tup AS live_rows
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Això dona una bona foto: quines taules ocupen més, quant és contingut real i quant són índexs o dades TOAST. El camp n_live_tup és una estimació de files vives basada en les estadístiques d’ANALYZE, útil com a primera orientació.

Aquest tipus de consulta s’ hauria de guardar en un script programat i executar-se amb regularitat. Guardar la seva sortida cada dia o cada setmana permet detectar augments graduals. Si només s’executa quan ja hi ha sospites, és tard.

El que se sol passar per alt: mida real vs mida inflada

Un error freqüent és assumir que la mida reportada per pg_total_relation_size() reflecteix l’ús actual. Però PostgreSQL no retorna espai al sistema operatiu quan s’esborren files, almenys no immediatament. La mida d’ una taula pot no reduir-se encara que s’ hagin eliminat milions de registres.

Això passa per com funciona el MVCC (Multi-Version Concurrency Control). En esborrar una fila, aquesta no desapareix: es marca com a morta. L’ espai queda reservat fins que es faci una neteja efectiva mitjançant VACUUM.

Per comprovar quantes dades mortes hi ha en una taula:

SELECT 
    relname AS table_name,
    n_dead_tup,
    n_live_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Una taula amb milions de n_dead_tup acumulats i sense autovacuum recent probablement estigui ocupant molt més disc del que necessita. Això pot provocar falsos positius si només es mira la mida sense més.

Convé també entendre que una taula pot estar creixent perquè l’autovacuum no està funcionant correctament, o no està activat amb la freqüència necessària. A taules amb escriptura intensiva o esborrats freqüents, els paràmetres per defecte poden no ser suficients.

Quan ajustar els paràmetres d’ autovacuum

La configuració d’autovacuum a PostgreSQL és conservadora per defecte. Això evita sobrecarregar el sistema a entorns petits, però és insuficient per a taules amb moltes escriptures.

Si una taula creix amb massa n_dead_tup durant molt de temps, probablement necessiti un ajust d’autovacuum específic. Això es fa a nivell de taula amb ALTER TABLE:

ALTER TABLE my_table SET (
    autovacuum_vacuum_threshold = 500,
    autovacuum_vacuum_scale_factor = 0.01
);

Amb això, es redueix el llindar a partir del qual PostgreSQL llança un VACUUM automàtic. Cal fer-ho amb criteri: si es baixa massa, pot generar més IO del necessari. Però a taules que generen molts registres temporals o cicles d’inserció/eliminació (per exemple, cues internes, logs, sessions), pot ser necessari.

Un altre senyal que no està fent la seva feina: el nombre de dead tuples no baixa entre execucions i no hi ha VACUUM recent registrat. Si es combina amb creixement en disc, és un problema.

Detectar creixement amb històric de mides

No hi ha forma màgica de saber si una taula ha crescut “massa” sense un punt de comparació. Registrar mides històriques permet saber si una cosa que avui pesa 12 GB fa tres dies ocupava 5 GB.

Una forma simple de fer-ho sense eines externes és guardar els resultats de pg_total_relation_size() en una taula auxiliar:

CREATE TABLE table_size_history (
    collected_at timestamp DEFAULT now(),
    table_name text,
    total_size_bytes bigint
);

Un script diari pot inserir les mides actuals:

INSERT INTO table_size_history (table_name, total_size_bytes)
SELECT 
    schemaname || '.' || relname,
    pg_total_relation_size(relid)
FROM pg_catalog.pg_statio_user_tables;

A partir d’aquí pots graficar el creixement a Grafana, Prometheus o fins i tot consultar amb SQL:

SELECT 
    table_name,
    max(total_size_bytes) - min(total_size_bytes) AS growth_last_7d
FROM table_size_history
WHERE collected_at > now() - interval '7 days'
GROUP BY table_name
ORDER BY growth_last_7d DESC
LIMIT 10;

Això revela tendències que no es detecten amb una única consulta. Pots saber si una taula creix 1 GB al dia o si va saltar a 30 GB en una nit. Ambdues coses mereixen atenció, però la forma de tractar-lo és diferent.

Compte amb els índexs: pesen més del que sembla

Un altre detall fàcil de passar per alt és quant pesen els índexs en taules molt consultades. Hi ha vegades en què la mida dels índexs supera el de la taula base.

Una forma de veure això amb detall:

SELECT 
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Això ajuda a detectar si hi ha índexs redundants, poc usats o que creixen sense control.

Per a veure l’ús d’índexs (i detectar si n’hi ha algun que no es toca):

SELECT 
    relname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

Índexs amb idx_scan = 0 que ocupen diversos GB s’ haurien de revisar. Si no estan ajudant ningú, sobren.

TOAST: el que ocupa espai sense aparèixer a les taules

Si una taula emmagatzema molts camps grans (text, bytea, jsonb, etc.), PostgreSQL pot moure aquestes dades a un emmagatzematge extern anomenat TOAST (The Oversized-Attribute Storage Technique). És invisible per a l’usuari però afecta directament la mida total.

La mida del TOAST s’ inclou a pg_total_relation_size() però no a pg_relation_size().

Per comprovar si una taula està generant molt TOAST:

SELECT 
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY toast_size DESC
LIMIT 10;

Això dona una idea clara de quines taules tenen contingut desplaçat a TOAST. En alguns casos, convé revisar si el tipus de dades o el disseny està forçant això més del necessari.

Taules amb particions: com mesurar bé la mida

Quan una taula està particionada, cal anar amb compte de com es mesura la seva mida. Moltes consultes sobre pg_stat_user_tables no inclouen les particions filles, així que les dades poden semblar incompletes si no s’ajusta la consulta.

Per veure la mida total d’una taula d’actuació, convé consultar directament a pg_class i agrupar per la taula pare:

WITH RECURSIVE partitions AS (
    SELECT oid, relname, relnamespace
    FROM pg_class
    WHERE relkind = 'r' AND relname = 'nom_de_taula'
 
    UNION ALL
 
    SELECT c.oid, c.relname, c.relnamespace
    FROM pg_inherits i
    JOIN pg_class c ON c.oid = i.inhrelid
    JOIN partitions p ON i.inhparent = p.oid
)
SELECT 
    sum(pg_total_relation_size(oid)) AS total_size_bytes,
    pg_size_pretty(sum(pg_total_relation_size(oid))) AS total_size_pretty
FROM partitions;

Això és útil per a bases on hi ha centenars de particions (per exemple, particions per dia o mes), i es vol obtenir una dada global sense llistar totes una per una.

Una altra bona pràctica és etiquetar les particions amb un esquema o un nom consistent, per facilitar recerques i agregacions.

Què considerar com a creixement anormal

No tot creixement inesperat és un problema. Però hi ha alguns senyals que, si apareixen junts, solen requerir revisió:

• Augment de més del doble de la mida en menys de 24 hores, sense canvis a l’ aplicació.
• Una taula que creix però no augmenta el seu nombre de files actives (n_live_tup).
• Índexs duplicats que ocupen més que la taula base.
• Creixement continu sense esdeveniments d’ autovacuum o sense reducció de n_dead_tup.
• Alt consum de TOAST a camps que no haurien d’ estar fent servir emmagatzematge extern.
• Taules sense ANALYZE des de fa dies, cosa que impedeix estimacions fiables.

En general, qualsevol canvi abrupte a taules que abans tenien un patró predictible (creixement per setmana o per nombre d’insercions) mereix una verificació.

Què fer quan detectes creixement real

Un cop es detecta una taula que està creixent més de l’esperat, cal evitar l’impuls de llançar un VACUUM FULL sense més. Hi ha alternatives menys intrusives:

1. Veure si hi ha espai mort acumulat

Primer comprova n_dead_tup i quan va ser l’últim VACUUM. Si hi ha milions de tuples mortes, pots llançar un VACUUM VERBOSE manual per entendre què està passant:

VACUUM (VERBOSE, ANALYZE) nom_de_taula;

Això no bloqueja la taula i pot donar-te una idea del treball pendent.

2. Forçar un autovacuum si el trigger no s’està assolint

Pots fer servir pg_stat_activity per veure si hi ha un autovacuum corrent, i si no, llançar-ne un de manual com l’anterior. També pots ajustar temporalment els paràmetres amb:

ALTER TABLE nombre_de_tabla SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

Això obliga al planificador a reaccionar més aviat.

3. Si hi ha molts índexs redundants, revisar-los abans d’eliminar-los

Fes servir pg_stat_user_indexes per veure quins índexs no es fan servir. Abans d’esborrar-ne un, convé provar a staging o clonar la base. Hi ha aplicacions que fan servir ORMs que consulten per camps sense indexar i poden afectar al rendiment si s’esborra un índex esperat.

4. Si tot falla: VACUUM FULL i reindexació

Això pot alliberar espai en disc, però requereix bloqueig exclusiu. Només es recomana a manteniments programats. Alternativament, es pot fer una còpia a taula nova i DROP + RENAME, si el sistema ho permet.

Automàtic no significa confiable: per què convé auditar

Encara que tinguis autovacuum i estadístiques activades, no sempre són suficients. Hi ha escenaris en què els processos automàtics no funcionen com haurien de:

• Taules molt grans que mai assoleixen els llindars per percentatge (scale_factor massa alt).
• Taules amb molts INSERT i DELETE que no generen activitat constant (les buiden per lots).
• Bases amb planificacions molt agressives de manteniment que desactiven o limiten el VACUUM a horaris pic.

Per això, programar revisions regulars (fins i tot setmanals) de mides i estadístiques continua sent necessari. Guardar aquestes mètriques, com ja es va explicar abans, permet veure tendències reals que no detectes amb una snapshot.

Notes finals

• La mida reportada per pg_total_relation_size() inclou índexs, TOAST i espai reservat, però no sempre reflecteix l’ús lògic. No ho interpretis com l’única dada vàlida.
• n_dead_tup pot estar desactualitzat si no hi ha hagut ANALYZE recent. No sempre és fiable si s’acaba de reiniciar la base o si es va apagar l’autovacuum.
• Si fas servir replicació física, el creixement descontrolat en una taula es replica a l’esclau tal qual. Això pot duplicar l’impacte sense que ningú ho noti fins que el disc s’omple.
• En algunes versions de PostgreSQL (especialment abans de la 13), el sistema de TOAST pot generar fragmentació significativa. Si treballes amb grans quantitats de jsonb o text, revisa aquest punt.
• Fer servir pgstattuple pot donar informació més precisa sobre la fragmentació interna, però requereix l’extensió instal·lada.

Detectar el creixement anormal a taules de PostgreSQL no es resol amb un comandament únic. És un procés que requereix combinar visibilitat, històric, interpretació d’ estadístiques i coneixement de la càrrega. El que cal és tenir bones dades de referència, mirar alguna cosa més que la mida total, i actuar amb criteri abans que sigui un problema de disc o de rendiment.

Les eines estan al sistema, no necessites solucions externes per tenir control. Però cal configurar aquesta observació com a part de la feina habitual, no com a resposta quan ja és tard.