Search Postgresql Archives

Re: BRIN index on timestamptz

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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@xxxxxxxxxxx> wrote:


On Sat, Apr 24, 2021, 1:27 AM Mohan Radhakrishnan <radhakrishnan.mohan@xxxxxxxxx> 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?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux