On Wed, Mar 29, 2017 at 4:58 PM, David G. Johnston < david.g.johns...@gmail.com> wrote:
> 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. > > 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.
