Re: Vacuuming tables with BRIN index and CLUSTER ON index

On Wed, Mar 29, 2017 at 4:58 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Mar 29, 2017 at 1:34 PM, Cherio <cherio(at)gmail(dot)com> wrote:
>
>> I have an insert/select only table (no update/delete expected) and a BRIN
>> index on the timestamp column as follows
>>
>> CREATE TABLE log_table (
>> id BIGSERIAL NOT NULL,
>> data TEXT,
>> created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
>> CONSTRAINT log_table__pk PRIMARY KEY(id)
>> );
>>
>> CREATE INDEX log_table__created_at__idx ON log_table USING BRIN
>> (created_at);
>>
>> As records are added both "id" and "created_at" should be stored in
>> ascending order. My concern is VACUUMING, whether it will keep physical
>> record order or not. If either VACUUM or VACUUM FULL break the existing
>> physical order I would have to enforce it with CLUSTERing on primary key
>> which I am trying to avoid considering the table is expected to grow very
>> large.
>>
>> If my concern is valid would adding
>>
>> ALTER TABLE log_table CLUSTER ON log_table__pk;
>>
>> alleviate the issue and prompt VACUUM to keep rows ordered?
>>
>>
> ​You should review the three documentation sections below. The first
> describes what "ALTER TABLE ... CLUSTER ON"​ does.
>
> https://www.postgresql.org/docs/9.6/static/sql-altertable.html
>
> This one explain CLUSTER and the fact it is a one-time operation and that
> repeated use is required in the face of inserts and deletes.
>
> https://www.postgresql.org/docs/9.6/static/sql-cluster.html
>
> And this one explains the difference between VACUUM and VACUUM FULL -
> namely only the former is a maintenance routine.
>
> https://www.postgresql.org/docs/9.6/static/sql-vacuum.html
>
> The exact interplay here with BRIN I am unfamiliar with. Given the
> natural correlation that create_at timestamp exhibits I wouldn't imagine
> that a brin index on it would degrade that quickly. But I'm getting out
> beyond my experience here.
>
> David J.
>
>
Thanks David. It is exactly the relationship between BRIN index and VACUUM
that I am concerned about. I would expect it to be covered here
https://www.postgresql.org/docs/9.6/static/brin.html however it has only a
single reference to VACUUM and it doesn't sufficiently elaborate on the
subject.