Re: BRIN index on timestamptz

On Sat, Apr 24, 2021, 1:27 AM Mohan Radhakrishnan <
radhakrishnan(dot)mohan(at)gmail(dot)com> wrote:

> What's your question exactly? If you have confidence that correlation will
> remain high (insert only table, or occasional cluster/repack with cluster
> is done), then BRIN can be a good fit. If you do updates and deletes and
> new tuples (inserts and updates) come in and fill in those gaps left behind
> in early pages even though timestamp is high, then correlation will go down
> and brin will no longer be a good fit.
>
> Note- timestamp *with* timezone is recommended.
>
> The timestamptz isn't deleted or updated. It is only inserted. Another
> ENUM column will be updated.
> It looks like I should use brin. We also have other history tables like
> this.
>
> Thanks.
>

That's not a correct conclusion. Reply all btw.

Updating any value in the row means a new version of the row is inserted
and old one is marked expired and will be cleaned up by vacuum after no
transactions might need that row version (tuple). Research a bit about how
MVCC is implemented in Postgres.

If those updates would only be on recent rows and autovacuum is tuned
aggressively to keep the maintenance task under control, then the
correlation may remain high as only recent rows are being updated. If the
updates go back a long ways, then BRIN is unlikely to be a good fit. Maybe
it still could be if table fillfactor is lowered a bit and the enum is not
indexed so you get mostly HOT (heap only tuple) updates, but I wouldn't
count on it.

Why not use a btree index for the timestamptz column?