> 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@entrata.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?
