Consultes útils per a diagnosticar lentitud a PostgreSQL

Quan un sistema comença a anar lent, el més habitual és culpar el maquinari o la xarxa. Però gairebé sempre el coll d’ampolla està dins del mateix PostgreSQL: consultes mal planificades, bloquejos inesperats, xequeig I/O massa agressiu o estadístiques desactualitzades. Aquesta entrada tracta sobre les consultes que hauries de córrer amb regularitat, o almenys quan detectis una baixada del rendiment.

1. Veure què està passant ara: pg_stat_activity

No hi ha millor punt de partida que veure què està corrent just en aquest instant.

SELECT pid, usename, query_start, state,
       wait_event_type, wait_event,
       LEFT(query, 200) AS snippet
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

Això revela processos actius, bloquejos o esperes. Condicions comunes:

1. state = ‘active’ amb wait_event_type = ‘IO’: indica un SELECT que carrega moltes dades sense índex, o un VACUUM costant.
2. wait_event_type = ‘Lock’: algú està esperant un lock (veure secció sobre bloquejos).
3. state = ‘active’ sense waits i que va arrencar fa hores: potser se salten LIMIT, o hi ha cursors oberts.

Fes servir això a diari si el sistema té APM extern, però fins i tot sense això, et dona context en un sol cop d’ull.

2. Plans lents: pg_stat_statements

Tenir habilitat pg_stat_statements és clau. Si no ho està, Habilita’l a postgresql.conf i reinicia:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Una consulta imprescindible:

SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows,
  shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

mean_time ajuda a detectar consultes repetides que triguen poc però es repeteixen milers de vegades: aquestes es mengen la CPU o I/O. Per exemple, un report que corre cada segon pot estar amb 30 ms, però si el cridem 200 000 vegades al dia, és un malson.

Un altre cas evident: molta lectura a disc. Si shared_blks_read està alt en proporció a shared_blks_hit, li falten índex o hi ha taules mal particionades.

3. Bloquejos: pg_locks + pg_stat_activity

Quan un procés espera un lock i genera backends en cascada, les consultes comencen a trigar, fallen o fan deadlocks. Cal identificar el que bloqueja i el que està bloquejat:

SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocking_locks.transactionid = blocked_locks.transactionid
     AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Aquest transactionid compartit és usual si hi ha UPDATE o DELETE llargs. Lo típic: un job de manteniment o ETL llarga reté registres que després la web necessita actualitzar. La solució passa per dividir els UPDATE en batch petits i col·locar un COMMIT intermedi: evita pics de locks i millora la latència en general.

4. Contenció I/O: pg_stat_io (si existeix) o vistes del sistema

PostgreSQL (12 en endavant) ofereix pg_stat_io, amb mètriques d’I/O per taula i índex. Si fas servir una versió anterior, pots recolzar-te en pg_statio_user_tables:

SELECT relname,
       heap_blks_read + heap_blks_hit AS total_blocks,
       heap_blks_read,
       heap_blks_hit,
       idx_blks_read + idx_blks_hit AS idx_blocks,
       idx_blks_read,
       idx_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

Si una taula amb un camp clau per a JOIN apareix als primers llocs i idx_blks_read és alta, l’índex no està en cache: index bloating, falta de memòria, humitat del buffer pool… o un índex fragmentat. Reindexar és lo primer. Si continua sent lent, pensar en UNLOGGED, BRIN per a taules massives, o particionar si el volum ho justifica.

5. Transaccions llargues sense buidatge: pg_stat_activity

Un parent proper a la secció de bloquejos, però centrat en transaccions. Les transaccions que queden obertes tenen dos problemes:

1. Generen bloquejos en vacuum.
2. Sostenen velles xmin i fan créixer pg_xact, augmentant la feina del GC.

La consulta:

SELECT pid, usename, backend_start, xact_start,
       now() - xact_start AS duration, LEFT(query,200) AS snippet
FROM pg_stat_activity
WHERE state = 'active'
  AND xact_start IS NOT NULL
  AND now() - xact_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

En entorns transaccionals que no requereixen sessions permanents, es poden veure moltes transaccions obertes sense raó. Una sessió oblidada per un desenvolupador en un terminal SSH que va deixar un cursor obert pot estar bloquejant VACUUM durant dies. L’impacte pot ser un simple alentiment, o un forat en l’autovacuum que després requereix un VACUUM manual molt car.

6. Reindexar i VACUUM

Mai confiïs que autovacuum sigui suficient. Hi ha taules grans, amb pocs esborrats, on sí que serveix, però en ambients analítics o ETL amb dumps massius, l’autovacuum es queda curt. Dues queries útils:

1. Veure si hi ha taules amb tupla morta acumulada i encara no buidades:

SELECT relname,
       n_dead_tup,
       last_autovacuum,
       last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 10;

1. Comprovar els índex sense fer servir:

SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan < 50
  AND idx_tup_read > 10000
ORDER BY idx_tup_read DESC
LIMIT 20;

Un índex amb idx_scan = 0 significa que potser hauríem d’esborrar-lo. Després d’aplicar canvis en codi aplicacions, després del deploy detectes que s’està escanejant l’índex vell però que mai s’ha fet servir. Esborrar-lo pot reduir la càrrega d’I/O i alliberar espai.

7. Configuracions de memòria

Moltes vegades la lentitud és una configuració per omissió, sense provar. Dos paràmetres clau:

SHOW work_mem;
SHOW shared_buffers;

Per a work_mem, si les consultes més pesades fan “Hash Aggregate” o “Sort” que acaben al disc, significa que el work_mem per defecte no arriba. Un cas: un report amb GROUP BY lent per external merge, augmentar work_mem a 15–20 MB va ser suficient, sense haver de posar més CPU o RAM.

shared_buffers es calcula de forma tradicional: 25 % de la RAM, fins a uns 32 GB. Menys que això sovint impacta les lectures freqüents d’índex o pàgines. Vegeu també les vistes d’I/O esmentades abans, perquè si hi ha molts heap_blks_read i pocs shared_blks_hit, ens confirma que el buffer és insuficient.

8. Consultes que espatllen el pla: pg_stat_statements i EXPLAIN

Després de detectar una consulta lenta, lo lògic és prendre el seu query des de pg_stat_statements i córrer EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Aquí caldrà fer un judici pràctic: un índex que no es fa servir per collation, TABINDEX confusa, predicat mal transformat, tipus de dada que no casa. La història comuna: un operador OR en clàusula WHERE que fa que no s’utilitzi l’índex, o una funció no immutable que Postgres no pot indexar.

En aquests casos es pot refer la consulta, donar un CLUSTER, o generar un índex funcional.

9. Xequeig de WAL i replicació: pg_stat_replication

Si treballes en entorns amb repliques, un lag excessiu també és símptoma de lentitud o I/O saturat. I un replica que està molt endarrerida pot generar impacte en la planificació del backup o el failover.

SELECT
  pid, usename, application_name, client_addr,
  state, sync_priority, sync_state,
  pg_size_pretty(pg_xlog_location_diff(sent_lsn, flush_lsn)) AS lag
FROM pg_stat_replication;

Si el lag està en centenars de MB o segons, pot ser que el màster estigui saturat, que la xarxa sigui lenta, o que la replica tingui una cua gran de WAL. La solució: ajustar wal_sender_timeout, reubicar discos i revisar la configuració d’I/O per garantir prioritat durant els backup.

10. Estadístiques: pg_stats

Els plans depenen d’ estadístiques. Si les dades canvien molt i no executes ANALYZE, els plans es basen en estimacions errònies.

SELECT schemaname, relname,
       n_tup_ins, n_tup_upd, n_tup_del,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE last_analyze IS NULL
   OR last_autoanalyze IS NULL
   OR last_analyze < now() - interval '7 days'
ORDER BY GREATEST(
  EXTRACT(EPOCH FROM now() - COALESCE(last_analyze, last_autoanalyze)),
  EXTRACT(EPOCH FROM now() - COALESCE(last_autoanalyze, last_analyze))
) DESC
LIMIT 20;

En entorns amb càrrega alta i moltes escriptures, un ANALYZE manual periòdic (o forçar un autovacuum més agressiu) estalvia molts problemes de rendiment. Consultes que es planegen com a seqüències i en realitat haurien de ser hash joins, per exemple.

Conclusió

Aquestes consultes són les eines inicials per a diagnosticar lentitud a PostgreSQL. No totes apliquen sempre, però permeten veure el que no arriba des d’un APM extern. El següent pas consisteix a agafar les consultes lentes, generar un pla, comparar-lo amb el teu índex i decidir si reindexar, fragmentar, canviar la consulta o augmentar el work_mem.

El que convé fer és incloure aquests xequejos dins d’una rutina o sistema de monitoratzació: un informe setmanal o mensual que revisi l’activitat actual, bloquejos, transaccions obertes, mida de buffer misses, lag de replicació i taules sense analitzar. Una sola consulta lenta és una alerta vermella. Si trobes més de 5 queries amb mitjana per sobre 100 ms, és moment d’aprofundir.

Cada entorn té els seus matisos. A OLTP és prioritari mantenir transaccions curtes i accurate índex; en entorns analítics, la memòria, taules particionades i vacuums programats pesen més. Tot i així, les consultes que hem vist aquí són el primer pas lògic.