Re: Append only tables

> On Mar 21, 2020, at 4:30 AM, Kurt Roeckx <kurt(at)roeckx(dot)be> wrote:
>
>
>> I would recommend vacuuming the table to get the existing records frozen; then I believe you should see fewer updates to the all the base files.
>
> If this is needed, why doesn't autovacuum do this for me? Or do I
> need to modify some parameters so that autovacuum does get
> triggered?
>
> I actually didn't run any select query on that table yet, it's
> still importing the data. Can an insert also trigger the freeze?
>

As Keith stated; due to lack of updates and deletes the counters are not necessarily going to trigger an auto vacuum. Have a look at your auto vacuum settings and freeze settings to determine the best approach.

You want to be more aggressive with those settings or manually vacuum the table periodically. What you don’t want to occur to an extremely large table that has never been vacuumed is have it issue a freeze wraparound vacuum. The select freezing the tuples is a good thing here as it helps advance the xmin age. Really don’t want wraparound vacuums to occur; as that would indicate not enough vacuums are occurring for your current workload/volume.

Here’s a set of queries that I use to monitor transaction id age of the database and tables.

create or replace function util.numeric_format(_percision int, _scale int)
returns varchar(100)
as $$
declare
_grpCnt int;
_decCnt int;
begin
_grpCnt := ((_percision - _scale) / 3);
_decCnt := ((_percision - _scale) % 3);

return trim(leading ',' from (repeat('9', _decCnt) || repeat('G999', _grpCnt) || 'D' || repeat('9', _scale)));
end;
$$ language plpgsql
immutable returns null on null input
;
comment on function numeric_format(int, int)
is 'Returns a format string for a numeric value given the percision.'
;

with stat as (
select datname as database
, (datfrozenxid::text)::bigint as frozen_txid
, age(datfrozenxid) as txid_age
, (2^31 - 1000000) as max_txid
, (2^31 - 1000000) - age(datfrozenxid) as txid_free
, current_setting('autovacuum_freeze_max_age'::text)::double precision as freeze_age
, case pg_is_in_recovery() when true then 0 else txid_current() end & (~(-1::bigint << 31)) as current_txid
, case pg_is_in_recovery() when true then 0 else txid_current() end as epoch_txid
from pg_database
where datname = current_database()
)
select stat.database
, to_char(stat.frozen_txid, util.numeric_format(10,0)) as frozen_txid
, to_char(stat.current_txid, util.numeric_format(10,0)) as current_txid
, to_char(stat.epoch_txid, util.numeric_format(10,0)) as epoch_txid
, to_char(txid_age, numeric_format(10, 0)) as txid_age
, to_char(txid_free, numeric_format(10, 0)) as txid_free
, to_char(txid_age / max_txid * 100, '9G999D99%') as used_txid_pct
, to_char(txid_age / freeze_age * 100, '9G999D99%') as used_freeze_pct
, now() as asof
from stat
;

select n.nspname as schema
, c.relname as table_name
, c.relpages::bigint as pages
, pg_size_pretty(c.relpages::bigint * current_setting('block_size')::bigint) as size
, to_char((c.relfrozenxid::text)::bigint, numeric_format(15, 0)) as frozen_xtid
, to_char(age(c.relfrozenxid), numeric_format(15, 0)) as txid_age
, to_char((2^31 - 1000000) - age(c.relfrozenxid), numeric_format(15, 0)) as txid_free
, to_char(age(c.relfrozenxid) / (2^31 - 1000000) * 100, '9G999D99%') as used_txid_pct
, to_char(age(c.relfrozenxid) / current_setting('autovacuum_freeze_max_age'::text)::double precision * 100, '9G999D99%') as used_freeze_pct
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'm')
order by (c.relfrozenxid::text)::bigint
, c.relpages::bigint desc
limit 50
;