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.mo...@gmail.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 <mle...@entrata.com> wrote: > >> >> >> On Sat, Apr 24, 2021, 1:27 AM Mohan Radhakrishnan < >> radhakrishnan.mo...@gmail.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? >> >>
