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