On Tue, Jul 4, 2017 at 10:18 PM, Chris Travers <chris.travers@gmail.com> wrote:
> First, I haven't seen major problems of database bloat in a long time which
> is why I find this case strange. I wanted to ask here what may be causing
> it.
>
> Problem:
> ==========
> Database is in the 100GB to 200GB size range, running on btrfs (not my
> choice) with nodatacow enabled (which I set up to fix a performance issue).
> The workload is a very heavy batch-update workload.
>
> The database bloats linearly. I have measured this on one table (of 149M
> rows).
>
> After vacuum full this table is (including indexes): 17GB
> Every 24 hrs, seems to add its original space in size to the file system
> +/-.
>
> Bloat seems to be affecting both indexes and underlying tables.
>
> Vacuum verbose does not indicate a disproportionate number of rows being
> unremovable. So autovacuum is keeping up without too much difficulty.
That is odd. I find the linear increase in bloat alarming.
I found this thread in the context of investigating a possible
performance regression in 9.5 [1]. It's far from clear that that's
actually what's up here, but it's worth considering as a possibility.
Some questions about that:
Can you show the schema of at least one of the tables that are most
affected, its indexes, etc?
Are all indexes bloated equally?
Do you notice that unique indexes are not as badly affected as other
indexes? How does it break down, in terms of how much each individual
index grows in size?
You say that the problem is with both indexes and tables. How much of
this is table bloat, and how much is index bloat?
Thanks
[1] https://postgr.es/m/CAH2-Wz=SfAKVMv1x9Jh19EJ8am8TZn9f-yECipS9HrrRqSswnA@mail.gmail.com
--
Peter Geoghegan