BRIN Index in postgres

TimescaleDB and PostgreSQL
1

Hello Team,

We want to fetch 3 months old data, and the total data size is close to 370GB, wanted to have clarity.

  • Does the BRAIN index will help in this case?
    The column where I am applying BRAIN index is of type “BIGINT”, so BRAIN index will work properly with this data type?

  • Also is there any other way to retrieve data faster, we are ok to run queries on small sets, like running queries for the last 10 days, then the next last 10 days.

  • I have enabled the BRAIN index in my dev env where the data size is less than 1GB, when we fired the query it was not using the BRAIN index, instead, it was performing a sequential scan.

  • We are running only master node, there is no replica or replication configured.

Regards,
Pankaj

2

Do you mean BRIN index?

If you use hypertables, it will already map the data in chunks that are different tables, and when you filter it will just exclude the non-needed chunks.
Can you share what your schema and queries look like?