Thread: index bloat question

On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz <mabewlun@gmail.com> wrote:
> Hi,
> just a couple of questions:
> will there be an index bloat if I have:
> - a serial column and only add rows to the table?
> - a text column and I only add rows to the table?
> For the serial column the numbers are only incremented, for the text column
> I add random strings.

With no deletes or updates, the only bloat will be from a non 100% fill factor.

Scott Marlowe wrote:
> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz <mabewlun@gmail.com> wrote:
>
>> Hi,
>> just a couple of questions:
>> will there be an index bloat if I have:
>> - a serial column and only add rows to the table?
>> - a text column and I only add rows to the table?
>> For the serial column the numbers are only incremented, for the text column
>> I add random strings.
>>
>
> With no deletes or updates, the only bloat will be from a non 100% fill factor.
>
>
Is there no index rebalancing overhead on monotonically increasing keys
compare to (decently) random.  Seems one would continuously write to the
"right-most" sub-leaf node.

On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz <mabewlun@gmail.com> wrote:
>
>
> On 17 October 2011 02:01, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz <mabewlun@gmail.com> wrote:
>> > Hi,
>> > just a couple of questions:
>> > will there be an index bloat if I have:
>> > - a serial column and only add rows to the table?
>> > - a text column and I only add rows to the table?
>> > For the serial column the numbers are only incremented, for the text
>> > column
>> > I add random strings.
>>
>> With no deletes or updates, the only bloat will be from a non 100% fill
>> factor.
>
> Hi Scott,
>
> if there is no bloat, how could you explain this:
>
> Simple test:
>
> CREATE TABLE test (
> id text primary key,
> category_id text not null
> );
>
> CREATE INDEX i_category ON test (category_id);
>
>
> I make 500k inserts in one transaction using a python script.
> For the random text I use random uuid from the function:
>
> uuid.uuid4()
>
> After those inserts I create another index:
>
> CREATE INDEX i_new ON test (category_id);
>
>
> select
> pg_size_pretty(pg_relation_size('i_category')),
> pg_size_pretty(pg_relation_size('i_new'))
> ;
>
> Results:
>
> '37 MB';'28 MB'

You didn't post your insertion script.  btree indexes are always going
to have extra space in them due to pages splitting and being only
partially filled -- insertion order over the range of your datum plays
into this (you'll get different index arrangements from random vs
ordered insertion).  for kicks, try reindexing both indexes and see
what the size is afterwords.

merlin