On Mon, 8 Apr 2019 at 14:19, Igal Sapir <igal@lucee.org> wrote:
>
> On Sun, Apr 7, 2019 at 6:20 PM David Rowley <david.rowley@2ndquadrant.com> wrote:
>>
>> On Mon, 8 Apr 2019 at 10:09, Igal Sapir <igal@lucee.org> wrote:
>> >
>> > I have a table for which pg_relation_size() shows only 31MB, but pg_total_relation_size() shows a whopping 84GB.
>> >
>> > The database engine is running inside a Docker container, with the data mounted as a volume from a partition on
thehost's file system.
>> >
>> > When I try to run `VACUUM FULL`, the disk usage goes up until it reaches the full capacity of the partition (about
27GBof free space), at which point it fails.
>>
>> That sort of indicates that the table might not be as bloated as you
>> seem to think it is. Remember that variable length attributes can be
>> toasted and stored in the relation's toast table.
>
>
> I think that you're on to something here. The table has a JSONB column which has possibly toasted.
>
> I have deleted many rows from the table itself though, and still fail to reclaim disk space. Is there something else
Ishould do to delete the toasted data?
The toast data is part of the data. It's just stored out of line since
there's a hard limit of just under 8k per tuple and since tuples
cannot span multiple pages, PostgreSQL internally breaks them into
chunks, possibly compresses them and stores them in the toast table.
This can occur for any variable length type.
This means if you want to remove the toast data, then you'll need to
remove the data from the main table, either the form of deleting rows
or updating them to remove the toasted values.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services