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