Table Physical Size Surge

From: Ashish Kumar Singh <singh(dot)ashu79(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Table Physical Size Surge
Date: 2020-02-24 05:42:29
Message-ID: CALaESzLD8C-K8c51r5z20iZhONY=TDj8AJ-du9yeOet-jdAwgg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

We are using this version of Postgresql:

PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit

We are not able to figure out the reason of this.

--
* * * * * * * * * * * * * * * * * * * * * * *
*Thanking You and Regards *
**Ashish Kumar Singh * *
* * * * * * * * * * * * * * * * * * * * * * *

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Daniel Gustafsson 2020-02-24 07:22:41 Re: Table Physical Size Surge
Previous Message Fan Liu 2020-02-24 01:32:26 RE: [Bus error] huge_pages default value (try) not fall back