Re: BRIN index on timestamptz

> Why not use a btree index for the >timestamptz column?
There are some capabilities our team lacks. Due to that autovacuum tuning
mechanisms isn't considered at all. It may be in the future.

I know about basic MVCC though. BRIN was an option as the characteristics
you describe match the requirements.

1. Only recent rows are updated. One or two ENUMS, 4-5 states per ENUM for
each timestamptz.
2.ENUMs are not indexed. Will that help too ? That is probably an
unrelated question.

Btree may be the default option.

Thanks.

On Saturday, April 24, 2021, Michael Lewis <mlewis(at)entrata(dot)com> wrote:

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