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


[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