Vacuuming tables with BRIN index and CLUSTER ON index

From: Cherio <cherio(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Vacuuming tables with BRIN index and CLUSTER ON index
Date: 2017-03-29 20:34:19
Message-ID: CAKHqFkJLbJSo8AX-uMJ2G8QdPZgGSjiebAF=AQWHLMGm7VpiuA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2017-03-29 20:58:14 Re: Vacuuming tables with BRIN index and CLUSTER ON index
Previous Message Tony Cebzanov 2017-03-29 19:52:17 Re: Constraint exclusion-like behavior for UNION ALL views