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?
Thanks!