Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) index on data which is queried by id. Additionally, data will be queried by the timestamp for analysis. And for this I wanted to partition the table on a monthly or yearly base.
But since my table is append only and the timestamp will be quite continuous (maybe not to a 100% but generally I won't deliberately post items from the past) shouldn't a BRIN index basically give me the same functionality as table partitioning without the trouble of manually creating sub tables and instead of triggers? (at least that's how partitioning was described on the documentation: https://www.postgresql.org/docs/current/static/ddl-partitioning.html )
Maybe some additional context - I thought about partitioning it by year or month since that is the only predicable thing. In both cases I'll have to perform queries on this table which will not include any timestamp information so for those queries I'd still be using the btree indices and in case of a pratition postgresql wouldn't be able to infer from the query which smaller table (and index) could be used.