Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 08:09 PM, Jeff Janes wrote:

On Thu, Apr 11, 2019 at 11:44 AM rihad <rihad@mail.ru> wrote:

Since we dump&restore production DB daily into staging environment, the difference in size (as reported by psql's \l+) is 11GB in a freshly restored DB as opposed to 70GB in production.

Yeah, that seems like a problem.  Do you have long lived transactions/snapshots that are preventing vacuuming from removing dead tuples?  You can run a manual "vacuum verbose" and see how many dead but nonremovable tuples there were, or set log_autovacuum_min_duration to some non-negative value less than the autovac takes, and do the same.

vacuum frees tuples just fine. It's just that by the time each run finishes many more accumulate due to table update activity, ad nauseum. So this unused space constantly grows. Here's a sample autovacuum run:

2019-04-11 19:39:44.450841500 [] LOG:  automatic vacuum of table "foo.public.bar": index scans: 1
2019-04-11 19:39:44.450843500   pages: 0 removed, 472095 remain, 4 skipped due to pins, 39075 skipped frozen
2019-04-11 19:39:44.450844500   tuples: 19150 removed, 2725811 remain, 465 are dead but not yet removable
2019-04-11 19:39:44.450845500   buffer usage: 62407557 hits, 6984769 misses, 116409 dirtied
2019-04-11 19:39:44.450846500   avg read rate: 16.263 MB/s, avg write rate: 0.271 MB/s
2019-04-11 19:39:44.450847500   system usage: CPU 59.05s/115.26u sec elapsed 3355.28 sec

Also, what does pg_freespace (https://www.postgresql.org/docs/current/pgfreespacemap.html) show about the available of space in the table?  How about pgstattuple (https://www.postgresql.org/docs/current/pgstattuple.html)

Thanks, I'll try those. But as I said freshly restored DB is only 11GB in size, not 70 (only public schema is used).