Re: PostgreSQL DB checkpoint error!

> On Apr 28, 2020, at 3:06 AM, Ashok kumar Mani <amani(at)accelaero(dot)com> wrote:
>
> Classification: External
>
> Dear Rui DeSousa,
>
> I would like to share the row counts of the big tables in zabbix as below.
> What would you suggest if vacuum full and reindex is not possible then, is there any way to avoid blocking issue?
>

I would not use ‘vacuum full’ for regular maintenance; only to address a specific issue. Use just vacuum and auto-vacuum.

reindex — that I do concurrently. If you are running PostgreSQL 12 ; great news! it adds ‘reindex currently’. If you are running an early version; then you have to build a new index currently and drop the old.

I only reindex tables that need to be reindexed; not just for the sake of it. I use the following function that I wrote to create a script that will reindex the table. The script creates a new index concurrently and then drops the old. It also does the same for the primary key by dropping the foreign key constraints as well and rebuilds them. It does this to minimize blocking contention for applications that normally run 24/7.

To use just execute the function with the table that you would like reindexed and the result we be a script that you can feed to psql to actually do the reindex.

i.e. select reindex_script(‘public’, ‘mytable’);

I would test it with your tables and application. I haven’t use this with a partitioned table and I don’t it would work for those tables.

p.s. looking forward to retiring this script when upgrading to version 12.

/*===========================================================================
Function: reindex_script()
Description: Returns a script to rebuild indexes concurrently
============================================================================*/
create or replace function reindex_script(_schema name, _table name, _tablespace name default null, _rename boolean default true)
returns table (sql text)
as $body$
declare
_obj_name text;
_old_iname text;
_new_iname text;
_is_pkey boolean;
_is_ukey boolean;
_sql text;
_cnt int;
_idx_oid oid;
_spath text;
begin
create temp table rebuild_indexes (pos serial not null primary key, sql text not null)
on commit drop;

-- sanitize schema input
select current_setting('search_path')
, nspname
into _spath, _schema
from pg_namespace
where nspname = _schema
;

if _schema is null then
raise exception 'Unable to identify schema';
end if
;

perform set_config('search_path', _schema, true);

insert into rebuild_indexes(sql)
values ('\set ON_ERROR_STOP on')
, (concat('set search_path=', quote_ident(_schema), ';'))
, ('\timing on')
, ('')
, (concat('\echo Vacuuming table ', quote_ident(_table), '.'))
, (concat('vacuum verbose ', quote_ident(_table), ';'))
;

for _idx_oid, _obj_name, _old_iname, _new_iname, _is_pkey, _is_ukey, _sql in
select i.indexrelid
, quote_ident(ns.nspname)
|| '.' || quote_ident(c.relname) as objname
, quote_ident((i.indexrelid::regclass)::text) as old_iname
, quote_ident(
c.relname
|| case
when substring((i.indexrelid::regclass)::text from '(_idx|_pkey|_ukey)\d{0,3}$') in ('_idx', '_pkey', '_ukey')
then
case
when i.indisprimary then '_pk'
when con.conindid is not null then '_uk'
else '_ix'
end
else
case
when i.indisprimary then '_pkey'
when con.conindid is not null then '_ukey'
else '_idx'
end
end
|| case i.indisprimary
when true then ''
else (row_number() over (partition by con.conindid is not null order by substring((i.indexrelid::regclass)::text from '(\d+)$')))::text
end
) as new_iname
, i.indisprimary as is_pkey
, con.conindid is not null as is_ukey
, regexp_replace(
lower(pg_get_indexdef(i.indexrelid))
, '( where |$)'
, coalesce(' tablespace ' || _tablespace, ' tablespace ' || its.spcname, '')
|| case
when i.indpred is null then ''
else ' where '
end
) as csql
from pg_namespace ns
join pg_class c on c.relnamespace = ns.oid
and c.relkind = 'r'
and c.relname = _table
join pg_index i on i.indrelid = c.oid
join pg_class ic on ic.oid = i.indexrelid
left join pg_tablespace its on its.oid = ic.reltablespace
left join pg_constraint con on con.conindid = i.indexrelid
and con.conrelid = c.oid
and con.contype = 'u'
where ns.nspname = _schema
order by i.indisprimary
, substring((i.indexrelid::regclass)::text from '(\d+)$')
loop
insert into rebuild_indexes(sql)
values ('')
, ('\echo Creating ' || _new_iname || ' on ' || _obj_name || '.' )
, (regexp_replace(_sql, 'index (.+) on (.+) using', 'index concurrently ' || _new_iname || ' on ' || _obj_name || ' using') || ';')
;

if _is_pkey then
insert into rebuild_indexes(sql)
values ('')
, ('\echo Swapping primary key ' || _old_iname || ' with '|| _new_iname || ' on ' || _obj_name || '.' )
, ('begin;')
, ('--Drop foreign keys to allow primary key swap')
;

insert into rebuild_indexes(sql)
select concat('alter table ', quote_ident((fk.conrelid::regclass)::text), ' drop constraint ', quote_ident(fk.conname), ';')
from pg_constraint fk
where fk.conindid = _idx_oid
and fk.contype = 'f'
;

insert into rebuild_indexes(sql)
values ('')
, ('alter table ' || _obj_name || ' drop constraint ' || _old_iname || ';')
, ('alter table ' || _obj_name || ' add primary key using index ' || _new_iname || ';')
, ('')
, ('--Rresintate foreign keys with "not valid" option; contraint is enforced just not validated')
;

insert into rebuild_indexes(sql)
select concat('alter table ', quote_ident((fk.conrelid::regclass)::text), ' add constraint ', quote_ident(fk.conname), ' ', lower(pg_get_constraintdef(fk.oid)), ' not valid;')
from pg_constraint fk
where fk.conindid = _idx_oid
and fk.contype = 'f'
;

insert into rebuild_indexes(sql)
values ('commit;')
, ('')
, ('\echo Revalidating foreign key constraints with existing records')
;

insert into rebuild_indexes(sql)
select concat('alter table ', quote_ident((fk.conrelid::regclass)::text), ' validate constraint ', quote_ident(fk.conname), ';')
from pg_constraint fk
where fk.conindid = _idx_oid
and fk.contype = 'f'
;
elsif _is_ukey then
insert into rebuild_indexes(sql)
values ('')
, ('\echo Swapping constraint ' || _old_iname || ' with '|| _new_iname || ' on ' || _obj_name || '.' )
, ('begin;')
, ('alter table ' || _obj_name || ' drop constraint ' || _old_iname || ';')
, ('alter table ' || _obj_name || ' add unique using index ' || _new_iname || ';')
, ('commit;')
;
else
insert into rebuild_indexes(sql)
values ('')
, ('\echo Dropping index ' || _old_iname || '.' )
, ('drop index concurrently ' || _old_iname || ';')
;
end if;

if _rename then
insert into rebuild_indexes(sql)
values ('')
, ('\echo Renaming index ' || _new_iname || ' to ' || _old_iname || '.' )
, ('alter index ' || _new_iname || ' rename to ' || _old_iname || ';')
;
end if
;

end loop
;

-- Reset search path incase of extended tranacation
perform set_config('search_path', _spath, true);

return query
select x.sql
from rebuild_indexes x
order by x.pos
;

end;
$body$ language plpgsql
;