Re: Size of the table is growing abnormally in my database.

Ok, thanks.

On Sun 26 Aug, 2018, 10:46 AM Paul Carlucci, <paul(dot)carlucci(at)gmail(dot)com>
wrote:

> There's a handful of hidden columns like Xmin and Xmax per row that you're
> not accounting for, header info per page, reserve space, free space... The
> physical size on disk is reasonable.
>
> Otherwise you can reduce the number of rows by cleaning up and moving out
> old data, reduce the width of each row by getting rid of any unused columns
> or switching to narrower data types, or drop unused indexes. If none of
> that works for you then you're going to have to adjust your disk budget.
>
> On Sun, Aug 26, 2018, 12:37 AM Raghavendra Rao J S V <
> raghavendrajsv(at)gmail(dot)com> wrote:
>
>> Thank you very much for your prompt response.
>>
>> Please guide me below things.
>>
>> How to check rows got corrupted?
>>
>> How to check table got corrupted?
>>
>> How to check which row is occupied more space in the table?
>>
>> Is this expected?
>>
>> [image: image.png]
>>
>> On Sun, 26 Aug 2018 at 09:46, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> wrote:
>>
>>> On 08/25/2018 08:36 PM, Raghavendra Rao J S V wrote:
>>> > Hi All,
>>> >
>>> > One of our database size is 50gb. Out of it one of the table has
>>> > 149444622 records. Size of that table is 14GB and its indexes size is
>>> 16GB.
>>> > Total size of the table and its indexes are 30GB. I have perfomred the
>>> > below steps on that table.
>>> >
>>> > reindex table table_name;
>>> >
>>> > vacuum full verbose analyze on table_name;
>>> >
>>> > But still the size of the table and its indexes size are not reduced.
>>> > Please guid me. How to proceed further.
>>>
>>> Rough approximation:
>>>
>>> 14,000,000,000 / 150,000,000 = 93 bytes/record.
>>>
>>> I am not seeing an issue. If you want to reduce the size of the table
>>> remove rows.
>>>
>>>
>>> >
>>> > Structure of the table as below.
>>> >
>>> > Column | Type | Modifiers | Storage |
>>> > Stats target | Description
>>> >
>>> ---------------------+------------------+-----------+---------+--------------+-------------
>>> > col1| bigint | | plain | |
>>> > col2 | double precision | | plain | |
>>> > col3| double precision | | plain | |
>>> > col4| double precision | | plain | |
>>> > col5| double precision | | plain | |
>>> > col6date| date | | plain | |
>>> > tkey | integer | | plain | |
>>> > cid | integer | | plain | |
>>> > rtypeid | integer | | plain | |
>>> > rid | integer | | plain | |
>>> > ckey | bigint | | plain | |
>>> > Indexes:
>>> > "idx_tab_cid" btree (cid)
>>> > "idx_tab_ckey" btree (ckey)
>>> > "idx_tab_col6date" btree (col6date)
>>> > "idx_tab_rid" btree (rid)
>>> > "idx_tab_rtype_id" btree (rtypid)
>>> > "idx_tab_tkey" btree (tkey)
>>> >
>>> >
>>> > --
>>> > Regards,
>>> > Raghavendra Rao J S V
>>> > Mobile- 8861161425
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian(dot)klaver(at)aklaver(dot)com
>>>
>>
>>
>> --
>> Regards,
>> Raghavendra Rao J S V
>> Mobile- 8861161425
>>
>