Bring your towel and join one of the fastest growing data communities. Welcome to our second-generation open source orchestration platform, a completely rethought approach to dataflow automation.

Prefect Community

Hi! can we use BRIN index for timestamp instead of btree in log table? when the table gets huge, queries are getting real slow.

What's your take on why a BRIN index would improve this?

from the docs
```BRIN stands for Block Range Index. BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table.```
since logs are usually appended to storage, i think this would be a good fir for the index.

I have replaced the btree index on timestamp and used BRIN instead in the log table having around 40 million records. before it used to time out everytime. And with BRIN, query response is well below a second.

Interesting that you're seeing such a performance difference. My understanding of BRIN is that it tends to result in a much smaller index, so you get size savings, but often you get a penalty on queries until you're doing very large selects since it points to page offsets.

What does your query look like?

*And to answer your original question - you can absolutely set your own indices. As long as the name doesn't conflict with a prefect index name, you're all set

```SELECT log.id, log.created, log.updated, log.name, log.level, log.flow_run_id, log.task_run_id, log.message, log.timestamp 
FROM log 
WHERE log.level &gt;= 1::integer AND log.flow_run_id IN ('5f0ee065-7e29-41d4-8513-1327b186ddd8'::uuid) ORDER BY log.timestamp DESC 
 LIMIT 500::integer OFFSET 0::integer```