Search Postgresql Archives

Re: Vacuuming tables with BRIN index and CLUSTER ON index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Mar 29, 2017 at 4:58 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Wed, Mar 29, 2017 at 1:34 PM, Cherio <cherio@xxxxxxxxx> 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_table CLUSTER 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.


This one explain CLUSTER and the fact it is a one-time operation and that repeated use is required in the face of inserts and deletes.


And this one explains the difference between VACUUM and VACUUM FULL - namely only the former is a maintenance routine.

https://www.postgresql.org/docs/9.6/static/sql-vacuum.html

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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux