Re: Vacuuming tables with BRIN index and CLUSTER ON index

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.