Re: Table Physical Size Surge

> On 24 Feb 2020, at 06:42, Ashish Kumar Singh <singh.ashu79@gmail.com> wrote:
>
> I have a table :
>
> create table planner.unit_flow_section
> (
>     id                  serial,
>     title               text,
>     items               jsonb[]
> );
> There is an operation to remove a jsonb element from the items array.
> I have an operation which takes a lot of time.
> UPDATE planner.unit_flow_section SET items = array_remove(items, '{"a":"b"}'::JSONB) where id = 34;
> Few days back we encountered a surge in Database size and within an hour 10GB of data was used up.
> After debugging we found this table size increased upto 12GB. We executed this query to get the table size.
> SELECT pg_size_pretty( pg_total_relation_size('planner.unit_flow_section') );
> But when I create a copy of the table then that table size was only 66MB.

Deletion of elements in PostgreSQL doesn't immediately reclaim the diskspace
used, due to the use of MVCC.  Each row must be kept until it's not potentially
visible to any running transaction.  Periodic maintenance is required to limit
growth, see the documentation sections on VACUUM for more information:

    https://www.postgresql.org/docs/10/routine-vacuuming.html

cheers ./daniel