Reindex

REINDEX IN Postgres

https://www.postgresql.org/docs/9.4/sql-reindex.html

REINDEX DATABASE foo;

REINDEX TABLE foo

delete from ids where table_name='item_tag' and nextid=212870;

Get reindex status

SELECT 
  now()::TIME(0), 
  a.query, 
  p.phase, 
  p.blocks_total, 
  p.blocks_done, 
  p.tuples_total, 
  p.tuples_done,
  ai.schemaname,
  ai.relname,
  ai.indexrelname
FROM pg_stat_progress_create_index p 
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;

Cancel reindex

• https://stackoverflow.com/questions/28969919/postgresql-reindex-still-working-even-after-two-hours

SELECT query, pid FROM pg_stat_activity;
SELECT pg_cancel_backend(774);