On Wed, Mar 29, 2017 at 4:58 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
I have an insert/select only table (no update/delete expected) and a BRIN index on the timestamp column as followsCREATE 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 addingALTER 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.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.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.
