On Thu, Apr 11, 2019 at 11:44 AM rihad <ri...@mail.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
