Hay dos tareas recurrentes en la operación de bases de datos PostgreSQL que uno toma por «automáticas»: la recolección forzada de filas con VACUUM, y la reconstrucción de índices con REINDEX.

La primera, cuando el «autovacuum» no es suficiente en una base de datos con mucho tráfico. La segunda, después de algunas migraciones, o cuando el comportamiento de las consultas sugiere que los índices tienen defectos después de una falla abrupta.

Los DBA PostgreSQL damos por descontado que siempre van a funcionar...

Algunos indicadores de instrumentación y monitoreo, combinados con «eventos» reportados por el cliente durante la semana, me obligaron a observar con más detalle el estado de una base de datos. Es una base de datos que instalé con PostgreSQL 7.4 hace más de una década. Se ha mantenido al día, tanto en el sistema operativo subyacente (siempre Debian GNU/Linux), como el propio ORDBMS, que hoy corre 11.10. Ha pasado por failover cuando falló el hardware principal, y operadores que matan procesos u obligan renicios por «razones».

Lo cierto es que necesitaba un VACUUM FULL ANALYZE para algunas tablas con «huecos», que ejecuté. Todo bien. Luego intenté un VACUUM ANALYZE para el resto de la base de datos... que falló en la tabla empresas porque encontró una clave (123456, no es chiste) duplicada.

psql> select count(*) from empresas where login = '123456'
 count
-------
     1

Eso de que hay una sola fila con la clave, y sin embargo VACUUM ANALYZE dice que está duplicada, ya me ha pasado. Se trata de un índice corrompido, no por el chavismo, sino por kill -9 o cualquier otra maniobra peligrosa. Así que

psql> reindex table empresas;
ERROR:  could not create unique index "empresas_i0"
DETAIL:  Key (login)=(123456) is duplicated.

¿Ah? Sólo encuentro una fila con la clave, pero REINDEX dice que está duplicada. La incredulidad me llevó a probar el redundante

begin;
drop index empresas_i0;
create unique index empresas_i0 on empresas (login);
commit;

que falló catastróficamente con... ¿adivinen qué? No pudo crear el índice UNIQUE porque la clave está duplicada.

El mecanismo de concurrencia de PostgreSQL mantiene unas columnas implícitas en todas las filas de todas las tablas. Esas columnas muestran la «ventana de validez» transaccional. Cuando una fila se borra o actualiza, en realidad se agrega una nueva fila con la «ventana actualizada» y se «cierra» la ventana de la fila removida o cambiada. Se supone que VACUUM recoge las filas vencidas, tanto de datos como sus referencias en los índices.

Quizás algún bug en alguna de las versiones viejas de PostgreSQL, o alguna caída (o empujón) del postmaster, ocasionó que una fila quedara inválida, transaccionalmente hablando, pero irrecuperable... VACUUM-mente hablando.

El asunto es que está el índice existente, que no puedo reconstruir. Entonces, necesito poder buscar las filas pero sin que se use ningún índice durante la consulta. No quiero que se use el índice existente, y tampoco quiero que PostgreSQL decida construir un índice temporal para la búsqueda. Entonces hice

begin;
set local enable_indexonlyscan = off;
set local enable_indexscan = off;
set local enable_bitmapscan = off;
select ctid, xmin, xmax, login
  from empresas
 where login = '123456';
rollback;

Y encontré lo que estaba buscando...

BEGIN
SET
SET
SET
   ctid   |    xmin    |    xmax    | login  
----------+------------+------------+--------
 (1621,9) | 1316877149 | 1362006894 | 123456
 (9344,2) |  977516375 |          0 | 123456
(2 rows)

ROLLBACK

Aparecieron dos filas con el mismo login, pero con ventanas transaccionales diferentes. El cero en xmax usualmente quiere decir que esa fila es la vigente («no ha sido borrada»), pero como el xmin de esa fila es menor que el rango de la otra, es mejor investigar. Para eso aproveche la columna ctid e inspeccioné cada fila, es decir

select * from empresas
 where login = '123456'
   and ctid = '(1621,9)';
rollback;
select * from empresas
 where login = '123456'
   and ctid = '(9344,2)';
rollback;

Con la información concreta del resto de las columnas, pude concluir que, en efecto, la segunda fila es la que sobra, teniendo que conservar la primera. Resultó importante no asumir nada de la ventana transaccional, al menos en este caso.

El problema se solucionó haciendo

begin;
delete from empresas
 where login = '123456'
   and ctid = '(9344,2)';
reindex table empresas;
vacuum analyze empresas;
commit;

El fragmento anterior ejecutó sin inconvenientes: eliminó la fila duplicada sobrante, reconstruyó todos los índices de la tabla, y completó el análisis estadístico necesario. Todo en orden.

Todos los conocimientos necesarios para razonar de esta forma siempre han estado detallados en la (¡magnífica!) Documentación de PostgreSQL. Las actividades administrativas están descritas en el Capítulo 24 «Routine Database Maintenance Tasks», mientras que información implícita en las tablas, en el Capítulo 5, Sección 4, «System Columns».

Es increíble lo que se puede aprender leyendo la documentación, en lugar de seguir recetas...