[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Size of the table is growing abnormally in my database.
From: Raghavendra Rao J S V <raghavendrajsv () gmail ! com>
Date: 2018-08-26 5:36:55
Message-ID: CAEHH7R4a205F8QwKhth2_ka6zb61ttnM6pGTmZqm5oQ47=OfVQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Ok, thanks.
On Sun 26 Aug, 2018, 10:46 AM Paul Carlucci, <paul.carlucci@gmail.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@gmail.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.klaver@aklaver.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.klaver@aklaver.com
>>>
>>
>>
>> --
>> Regards,
>> Raghavendra Rao J S V
>> Mobile- 8861161425
>>
>
[Attachment #5 (text/html)]
<div dir="auto">Ok, thanks. </div><br><div class="gmail_quote"><div dir="ltr">On Sun \
26 Aug, 2018, 10:46 AM Paul Carlucci, <<a \
href="mailto:paul.carlucci@gmail.com">paul.carlucci@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="auto"><div>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.</div><div dir="auto"><br></div><div dir="auto">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.<br><br><div class="gmail_quote" dir="auto"><div dir="ltr">On \
Sun, Aug 26, 2018, 12:37 AM Raghavendra Rao J S V <<a \
href="mailto:raghavendrajsv@gmail.com" target="_blank" \
rel="noreferrer">raghavendrajsv@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default" \
style="font-family:verdana,sans-serif">Thank you very much for your prompt \
response.</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">Please guide me below things.</div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif">How to check rows got \
corrupted?</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">How to check table got corrupted?</div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif">How to check which row \
is occupied more space in the table?</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">Is this expected?</div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><div><img \
src="cid:ii_jlad1kqy0" alt="image.png" width="472" \
height="70"><br></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Sun, \
26 Aug 2018 at 09:46, Adrian Klaver <<a href="mailto:adrian.klaver@aklaver.com" \
rel="noreferrer noreferrer" target="_blank">adrian.klaver@aklaver.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex">On 08/25/2018 08:36 PM, Raghavendra \
Rao J S V wrote:<br> > Hi All,<br>
> <br>
> One of our database size is 50gb. Out of it one of the table has <br>
> 149444622 records. Size of that table is 14GB and its indexes size is 16GB.<br>
> Total size of the table and its indexes are 30GB. I have perfomred the <br>
> below steps on that table.<br>
> <br>
> reindex table table_name;<br>
> <br>
> vacuum full verbose analyze on table_name;<br>
> <br>
> But still the size of the table and its indexes size are not reduced. <br>
> Please guid me. How to proceed further.<br>
<br>
Rough approximation:<br>
<br>
14,000,000,000 / 150,000,000 = 93 bytes/record.<br>
<br>
I am not seeing an issue. If you want to reduce the size of the table <br>
remove rows.<br>
<br>
<br>
> <br>
> Structure of the table as below.<br>
> <br>
> Column | Type | Modifiers | \
Storage | <br> > Stats target | Description<br>
> ---------------------+------------------+-----------+---------+--------------+-------------<br>
> col1| bigint | | plain | \
|<br> > col2 | double precision | | plain | \
|<br> > col3| double precision | | plain | \
|<br> > col4| double precision | | plain | \
|<br> > col5| double precision | | plain | \
|<br> > col6date| date | | plain | \
|<br> > tkey | integer | | plain | \
|<br> > cid | integer | | plain | \
|<br> > rtypeid | integer | | plain | \
|<br> > rid | integer | | plain | \
|<br> > ckey | bigint | | plain | \
|<br> > Indexes:<br>
> "idx_tab_cid" btree (cid)<br>
> "idx_tab_ckey" btree (ckey)<br>
> "idx_tab_col6date" btree (col6date)<br>
> "idx_tab_rid" btree (rid)<br>
> "idx_tab_rtype_id" btree (rtypid)<br>
> "idx_tab_tkey" btree (tkey)<br>
> <br>
> <br>
> -- <br>
> Regards,<br>
> Raghavendra Rao J S V<br>
> Mobile- 8861161425<br>
<br>
<br>
-- <br>
Adrian Klaver<br>
<a href="mailto:adrian.klaver@aklaver.com" rel="noreferrer noreferrer" \
target="_blank">adrian.klaver@aklaver.com</a><br> </blockquote></div><br \
clear="all"><div><br></div>-- <br><div dir="ltr" \
class="m_2666408076200949592m_-8099308106568933932gmail_signature" \
data-smartmail="gmail_signature"><div dir="ltr"><div>Regards,<br>Raghavendra Rao J S \
V<br>Mobile- 8861161425</div></div></div> </blockquote></div></div></div>
</blockquote></div>
--000000000000f4c54705744fd4e2--
["image.png" (image/png)]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic