Re: BRIN index on timestamptz

Isn't a btree subject to these effects ? So when I update ENUMS for each
timestamptz, btree indexes are less susceptible
to the effects than BRIN indexes ?

Thanks.

On Sat, Apr 24, 2021 at 9:05 PM Mohan Radhakrishnan <
radhakrishnan(dot)mohan(at)gmail(dot)com> wrote:

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