Timescale database is going towards emergency autovacuum

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Timescale database is going towards emergency autovacuum
Date: 2019-03-28 18:07:53
Message-ID: CAGoODpdna2XaKOJscfS1=hptWmP25FO7y4_Q3SQ=r5DfUD4iFQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We are running timescale pg_prometheus with separating each services in
schema. So in one database we have:

> >> schema_a
> view: metrics
> tables: metrics_copy, metrics_labels, metrics_values
> >> schema_b
> view: metrics
> tables: metrics_copy, metrics_labels, metrics_values
> and so on

We have multiple databases with size:

> datname size age(datfrozenxid)
> ------------------------------------------
> postgres 8973 kB 51018138
> testdb 9165 kB 51018138
> template1 7649 kB 51018138
> template0 7473 kB 51018138
> testdb1 7781 kB 51018138
> db1 6334 MB 51018138
> db2 74 MB 51018138
> db3 9645 kB 51018138
> db4 11 MB 51018138
> db4 759 MB 51018138

We have only 25 days of data and are also using a materialized view. I am
using the following query to check my emergency autovacuum threshold:

> select ROUND(100*(max(age(datfrozenxid))/(
> ( select setting AS value FROM pg_catalog.pg_settings WHERE name =
> 'autovacuum_freeze_max_age' ))::float)
> ) as percent_towards_wraparound
> from pg_database

Now each day I can see it is growing towards the limit. Now it is showing
26%.
How can we save our system from wraparound issue?
Someone please give some suggestions.

Thanks.

Browse pgsql-general by date

  From Date Subject
Next Message Perumal Raj 2019-03-28 18:26:33 Re: software or hardware RAID?
Previous Message Karsten Hilbert 2019-03-28 17:54:37 Aw: Re: Key encryption and relational integrity