Re: Should a DB vacuum use up a lot of space ?

>That is expected as template0 is read-only and so VACUUM will not work
>on it.
Isn’t template1 the same ? I’m not seeing that behavior on that one

>> Should I suspect something fishy going on ?

> Not sure without more information.
> 1) Can you be specific about your database references? 'That database'
> is open-ended.
“That database” = the database that’s been causing wrap-around problems since yesterday. It’s called “public”

> 2) Show the actual numbers from your xid queries. Both the raw values
> and the age() transformed ones.
backend> SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database;
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid (typeid = 28, len = 4, typmod = -1, byval = t)
3: age (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "template1" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003766" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000394" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "postgres" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003765" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000395" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "1717520404" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "2146483756" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "xxxx" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003760" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000400" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "osmtest" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003762" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000398" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "template0" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3732096533" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "131907627" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "drupal_prod" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003758" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000402" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "anta" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003756" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000404" (typeid = 23, len = 4, typmod = -1, byval = t)
----

> 3) What are your configuration parameters for the variables mentioned in
> the section below?:
> https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
Should be the default values, I’ve never changed them.

backend> show vacuum_freeze_min_age
1: vacuum_freeze_min_age (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: vacuum_freeze_min_age = "50000000" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show vacuum_freeze_table_age
1: vacuum_freeze_table_age (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: vacuum_freeze_table_age = "150000000" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show autovacuum_freeze_max_age
1: autovacuum_freeze_max_age (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_freeze_max_age = "200000000" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show autovacuum_vacuum_threshold
1: autovacuum_vacuum_threshold (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_threshold = "50" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show autovacuum_max_workers
1: autovacuum_max_workers (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_max_workers = "3" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show autovacuum_vacuum_scale_factor
1: autovacuum_vacuum_scale_factor (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_scale_factor = "0.2" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show autovacuum_vacuum_cost_delay
1: autovacuum_vacuum_cost_delay (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_cost_delay = "20ms" (typeid = 25, len = -1, typmod = -1, byval = f)

backend> show autovacuum_vacuum_cost_limit
1: autovacuum_vacuum_cost_limit (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_cost_limit = "-1" (typeid = 25, len = -1, typmod = -1, byval = f)

> 4) If you want to get an idea of fast xid's are being created a quick
> and dirty way is from here:

> https://www.postgresql.org/docs/9.1/static/functions-info.html

> txid_current() bigint get current transaction ID

> Now if you do select txid_current() outside a transaction it will create
> an xid on its own, still if you repeat it over some interval of time you
> will get an idea of how fast the server is going through xid's.
Well I’m now at a point where that’s not even possible, I have consistently run into the following
• I vacuum enough table to get back a couple dozen transactions below the 1M mark
• I exit singleuser mode and relaunch the server so I keep on vacuuming the oldest table first by copying
• But I don’t even have time to launch my script : as soon as launch the server, the auto-vacuum daemon kicks in and burns through the transactions : I guess it’s not smart enough to start with the oldest tables ?

So it looks like I’m going to have to bite the bullet and really vacuum the whole database instead of just the oldest tables first which will impact our production pipelines.