Sample Header Ad - 728x90

Postgres BRIN index for time-series table

1 vote
1 answer
288 views
I have multiple time-series tables with Pkey on timestamp column. I'd like to test performance improvement if I change the index to be BRIN instead of BTREE. Yet, I'd also like to have a UNIQUE constraint on the timestamp column (which as far as I know is based on BTREE). What are my options? * Should I add an additional BRIN index to BTREE? In this case which index will be chosen during searches / joins? * Should I drop the pkey and add just the UNIQUE constraint? In this case how is it going to work? My usage scenario is that I need to run FULL OUTER JOINs on a timestamp column to make queries from multiple tables all together and those queries sometimes can run for 30 minutes or more.
Asked by Miro (113 rep)
Mar 20, 2024, 04:09 PM
Last activity: Mar 21, 2024, 09:37 AM