Vacuuming tables with BRIN index and CLUSTER ON index
| От | Cherio |
|---|---|
| Тема | Vacuuming tables with BRIN index and CLUSTER ON index |
| Дата | |
| Msg-id | CAKHqFkJLbJSo8AX-uMJ2G8QdPZgGSjiebAF=AQWHLMGm7VpiuA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Vacuuming tables with BRIN index and CLUSTER ON index
|
| Список | 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!
В списке pgsql-general по дате отправления: