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_tableCLUSTER 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.
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.
