Postgres Pro
Downloads
Home   >   mailing lists

Timescale database is going towards emergency autovacuum - Mailing list pgsql-general

From AI Rumman
Subject Timescale database is going towards emergency autovacuum
Date
Msg-id CAGoODpdna2XaKOJscfS1=hptWmP25FO7y4_Q3SQ=r5DfUD4iFQ@mail.gmail.com
Whole thread Raw
List pgsql-general
Tree view
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.

pgsql-general by date:

Previous
From: "Karsten Hilbert"
Date:
Subject: Aw: Re: Key encryption and relational integrity
Next
From: Perumal Raj
Date:
Subject: Re: software or hardware RAID?