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