Vacuuming tables with BRIN index and CLUSTER ON index

On Wed, Mar 29, 2017 at 1:34 PM, Cherio <cherio@gmail.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.

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

On Wed, Mar 29, 2017 at 1:34 PM, Cherio <cherio@gmail.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.