DBA/PostgreSQL/uuid-index-maintenance.sql

/*
 * Author: DBRE - Joe Smith
 * Date: October 2022
 * Package: index_maintenance
 * Purpose: sets up tables, functions and extensions for index maintenance when deleting on a regular basis.
*/

-- pre-requisite extensions.
create extension pg_cron;
create extension pgstattuple;

CREATE TABLE index_maintenance_configuration (
    id SERIAL primary key,
    index_name varchar(63) UNIQUE not null,
    maintenance_threshold_percent float not null,
    man_window_start time not null,
    man_window_end time not null,
    weekday_override boolean default false,
    weekdays integer[] default array[1, 2, 3, 4, 5, 6, 7] not null
);

CREATE TABLE index_maintenance (
    index_name varchar(63),
    index_iteration bigint default 0,
    maintenance_started timestamp default null,
    maintenance_done timestamp default null,
    scheduled boolean default false,
    threshold_breach_value float,
    PRIMARY KEY(index_name, index_iteration, scheduled)
);

CREATE TABLE index_maintenance_lock (
    is_locked boolean,
    lock_change_time timestamp default now(),
    index_holding_lock text
);

ALTER TABLE index_maintenance
    ADD FOREIGN KEY (index_name)
    REFERENCES index_maintenance_configuration(index_name);


/*
 * Author: DBRE - Joe Smith
 * Date: October 2022
 * Function: idx_maintenance
 * Purpose: function called by pg_cron at a regular interval.
*/

Create or replace function idx_maintenance() returns void as $$
    declare
        rec record;
        current_iteration record;
        lock_rec record;
        done boolean;
        today int;
        day_allowed boolean;
    Begin
       today = EXTRACT(isodow FROM now());

       SELECT * INTO lock_rec FROM index_maintenance_lock;
       -- if the lock is held already, we do nothing.
       if lock_rec.is_locked == true then
            raise notice 'index_man_lock_held(lock held already, skipping maintenance until lock is released)',
            exit;
      end if;

      -- if a lock is not held, we check the indexes we know about.
      for rec in select * from index_maintenance_configuration
      loop
            day_allowed = true;
            
            if rec.weekday_override == true then
                day_allowed = today=ANY(rec.weekdays);
            end if;

            -- check for existing maintenance records.
            SELECT * INTO current_iteration FROM index_maintenance
                WHERE index_name = rec.index_name;

            -- set current iteration if no records are found for this index
            -- insert record into index_maintenance table with scheduled set to false
            if current_iteration.index_iteration is null then
                insert into index_maintenance(index_name, scheduled) VALUES (rec.index_name, false) on conflict do nothing;
                -- return record back into cursor so we can be lazy and reusing the current_iteration pointer.
                SELECT * INTO current_iteration FROM index_maintenance
                    WHERE index_name = rec.index_name;
            end if;

            end if;

            done = do_idx_maintenance(current_iteration);
            raise notice 'idx_maintenance(index_name: %, maintenance_done: %)', current_iteration.index_name, done;

       end loop;
    End;
$$
Language 'plpgsql';

/*
 * Author: DBRE - Joe Smith
 * Date: October 2022
 * Function: do_idx_maintenance
 * Purpose: calls lock_and_reindex function if we are within the maintenance window of a given index
*/

Create or replace function do_idx_maintenance(r record, maintenance_percent float) returns boolean as $$
    declare
        lf float;
        is_between_man_window boolean;
    Begin

    -- we assume we are not allowed to do maintenance
    is_between_man_window = false;
    -- this avoids calculating this multiple times.
    if now()::time between r.man_window_start::time and r.man_window_end::time then
        is_between_man_window = true
    end if;

    -- scheduled, no re-index running, fragmentation determined to be greater than threshold in prior run
    if r.scheduled == true then
        if is_between_man_window == true then
            lock_and_reindex(r, r.threshold_breach_value)
            return true;
        else
            return false;
        end if;

    else
        -- not scheduled, so we check fragmentation on the index.
        select leaf_fragmentation into lf from pgstatindex(r.index_name);

        -- not scheduled, no re-index running, fragmentation is greater than our maintenance threshold.
        -- lf is passed to track the amount of fragmentation that triggered the run.
        if lf > maintenance_percent then
            if is_between_man_window == true then
                lock_and_reindex(r, lf);
                return true;
            else
                -- leaf fragmentation is above threshold
                update index_maintenance set scheduled = true where id = r.id;
                return false;
            end if;
        else
            -- leaf fragmentation is under threshold, nothing to do
            return false;
        end if;
    end if;
    End;
$$
Language 'plpgsql';

/*
 * Author: DBRE - Joe Smith
 * Date: October 2022
 * Function: lock_and_reindex
 * Purpose: sets up tables, functions and extensions for index maintenance when deleting on a regular basis.
*/

Create or replace function lock_and_reindex(r record, lf float) returns void as $$
    declare
        is_between_man_window boolean;
    Begin

        raise notice 'lock_and_reindex(index_name: %, time: %)', r.index_name, now()::text;
        -- lock the lock table to prevent duplicate operations
        update index_maintenance_lock set is_locked = true, index_holding_lock = rec.index_name where index_holding_lock is null;

        -- set the maintenance started value in the record we have
        update index_maintenance set maintenance_started = now(), threshold_breach_value = lf where id = r.id;

        -- reindex
        REINDEX INDEX CONCURRENTLY rec.index_name;

        -- set the maintenance done value in the record we have.
        update index_maintenance set maintenance_done = now() where id = r.id;

        -- add next record to table, incrementing the iteration value by 1.
        insert into index_maintenance (index_name, index_iteration) VALUES (r.index_name, index_iteration + 1);

    End;
$$
Language 'plpgsql';

/*
 * Author: DBRE - Joe Smith
 * Date: October 2022
 * Function: add_index_configuration
 * Purpose: inserts a configuration into the configuration table, can be called by internal users.
*/

Create or replace function add_index_configuration(index_name varchar(63), man_start time, man_end time, threshold float) returns void as $$
    Begin
        insert into index_maintenance_configuration(index_name, maintenance_threshold_percent, man_window_start, man_window_end) VALUES (index_name, threshold, man_start, man_end) on conflict do nothing;
        raise notice 'attempted to add config for %, manually set weekday override if needed.', index_name;
    End;
$$
Language 'plpgsql';

/*
 * Author: DBRE - Joe Smith
 * Date: October 2022
 * Function: kill_reindex
 * Purpose: end current reindex operation safely
*/

Create or replace function kill_reindex() returns void as $$
    declare
        is_locked boolean;
        idx_name varchar(63);
        proc_id int;
    Begin
        -- not scheduled, so we check fragmentation on the index.
        select index_name into idx_name from index_maintenance_lock;
        raise notice 'killing pid running reindex operation for %', index_name;

        -- this should only ever return 1 row.
        select pid into proc_id from pg_stat_activity where query like 'REINDEX INDEX %' LIMIT 1;
        raise notice 'found pid %  proc_id running reindex operation for %', proc_id::text, index_name;

        SELECT pg_cancel_backend(proc_id);
        raise notice 'killed pid %  proc_id running reindex operation for %',proc_id::text, index_name;
    End;
$$
Language 'plpgsql';