Groovy DBA

 REINDEX TABLE table_name; Reasons for Reindexing You might need to reindex a table in the following scenarios:   Index Bloat:  Over time, indexes, especially B-tree indexes, can become bloated with empty or nearly-empty pages due to certain data modification patterns (e.g., frequent updates or deletions). Reindexing reclaims this wasted space. Index Corruption:  In rare cases of software bugs or hardware failures, an index might become corrupted and no longer contain valid data. Reindexing provides a recovery method. Altered Storage Parameters:  If you change an index's storage parameters (like  fillfactor ), a reindex is needed for the change to take full effect. Failed Concurrent Build:  If a  CREATE INDEX CONCURRENTLY  command fails, it leaves an "invalid" index, which can be rebuilt using  REINDEX .  

 to get a set point for vacuum and freeze:   vacuum (analyze,freeze); 

 

  SELECT   *,   pg_size_pretty(table_bytes) AS table,   pg_size_pretty(toast_bytes) AS toast,   pg_size_pretty(index_bytes) AS index,   pg_size_pretty(total_bytes) AS total FROM (   SELECT     *, total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes   FROM (     SELECT       c.oid,       n.nspname AS table_schema,       c.relname AS table_name,       c.reltuples AS row_estimate,       pct.relname AS toast_table_name,       pg_total_relation_size(c.oid) AS total_bytes,       pg_indexes_size(c.oid) AS index_bytes,       pg_total_relation_size(c.reltoastrelid) AS toast_bytes     FROM       pg_class c       JOIN pg_class pct ON (c.reltoastrelid = pct.oid)       LEFT JOIN pg_namespace n ON n.oid = c.relnamespace     WHERE c.relkind = 'r'...

  WITH y AS (     SELECT         pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl,         pg_catalog.quote_ident(a1.attname) AS referencing_column,         t.conname AS existing_fk_on_referencing_tbl,         pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl,         pg_catalog.quote_ident(a2.attname) AS referenced_column,         pg_relation_size(pg_catalog.format('%I.%I', n1.nspname, c1.relname)) AS referencing_tbl_bytes,         pg_relation_size(pg_catalog.format('%I.%I', n2.nspname, c2.relname)) AS referenced_tbl_bytes,         pg_catalog.format($$CREATE INDEX %I_idx ON %I.%I(%I);$$, t.conname, n1.nspname, c1.relname, a1.attname) AS suggestion     FROM         pg_catalog.pg_constraint t         JOIN pg_catalog.pg_attribut...

 select   now(),   query_start as started_at,   now() - query_start as query_duration,   format('[%s] %s', a.pid, a.query) as pid_and_query,   index_relid::regclass as index_name,   relid::regclass as table_name,   (pg_size_pretty(pg_relation_size(relid))) as table_size,   nullif(wait_event_type, '') || ': ' || wait_event as wait_type_and_event,   phase,   format(     '%s (%s of %s)',     coalesce((round(100 * blocks_done::numeric / nullif(blocks_total, 0), 2))::text || '%', 'N/A'),     coalesce(blocks_done::text, '?'),     coalesce(blocks_total::text, '?')   ) as blocks_progress,   format(     '%s (%s of %s)',     coalesce((round(100 * tuples_done::numeric / nullif(tuples_total, 0), 2))::text || '%', 'N/A'),     coalesce(tuples_done::text, '?'),     coalesce(tuples_total::text, '?')   ) as tuples_progress,   current_locker_pid, ...