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