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

On Thu, Apr 11, 2019 at 11:44 AM rihad <rihad(at)mail(dot)ru> wrote:

> On 04/11/2019 07:40 PM, Jeff Janes wrote:
>
>
> The disk usage doesn't reach a steady state after one or two autovacs? Or
> it does, but you are just unhappy about the ratio between the steady state
> size and the theoretical fully packed size?
>
> Cheers,
>
> Jeff
>
>
> 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.

(Indeed, those dumps you take daily might be the source of those long-lived
snapshots. How long does a dump take?)

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)

Cheers,

Jeff